Can universe support dynamic table query

[Former Member]

Hi expert,

I am using BO XI 3.0 Designer/WebIntelligence + MySQL 5.1

And, I am going to produce a dynamic universe which map to a dynamic table. The table name is not fixed, but decided by timestamp, the timestamp can be passed by WebIntellegence filter.

1. I have several monthly table to store data. The table name can be Result_2011_Jan, Result_2011_Feb...., Result_2011_Dec. The table structure are identical. Can I produce an universe which can query one of these monthly table by timestamp condition. I do not want to use @Prompt because it will prompt a dialog and I have to manually selelct a table name. Can I pass the table name silently?

2. I want to query a whole year data, so I need universe can query from all 12 months table then return yearly result. Is it feasible?

[Former Member]

Hi,

You can use drived table where you can calculate the yearly data. Use this table to get the yearly result in your report.

If possible ask you DBA to crearte Aggregate table in the database.

Hope this may help you..

[Former Member]

Hi,

Thanks for your reply. But the derived table must be generated on top of several tables which have fixed name. The month table name could change year by year, so how can I generate derived table based on them?

[Former Member]

if tables names change every year how are you doing to create the classes and dimensions in your universe? don't you tnikh you'll have to change all universe objects every year? I would suggest you to create a VIEW directly in your database, name this view with a fix name. This View should contain unions of all your dynamic tables.

Then add the View to your universe and work with the view. When table names change then go to the database to modify the VIEW code and you won't have to modify anything at universe level.

Hope this helps,

Regards

Erika