How do I use Derived Table to dynamically choose fact table

[Former Member]

How do I use the Derived Table functionality to dynamically choose a fact table?

I am using BO XI R2 querying against Genesys Datamart kept in Oracle 10g. The datamart contains aggregated fact tables at different levels (no_agg, hour, day, week, etc...) I would like to build my universe so that if the end user chooses a parameter to view reports at daily granularity, then the daily fact table is used; choose hourly granularity, then hourly fact table is used, etc....

I tried using dynamic SQL in Oracle Syntax, but Business Obljects Universe didn't like that type of coding.

The tables look something like this:

O_LOB1_NO_AGG o

inner join V_LOB1_NO_AGG v on o.object_id = v.object_id

inner join T_LOB1_NO_AGG t on v.timekey = t.timekey

Likewise, in the 'hour', 'day', 'week', etc... fact tables, the Primary Key to Foreign Key names and relationships are the same. And the columns in each O_, V_, T_ fact table is the same, or very similar (just aggregated at different levels of time).

I was thinking of going a different route and using aggregate aware; but there are many Lines of Business (20+) and multiple time dimensions (7) and I believe aggregate aware would require me to place all relevant tables in the Universe as separate objects, which would create a large Universe with multiple table objects, and not be maintenance-friendly. I also was going to dynamically choose Line of Business (LOB) in the derived tables, based on the end user choosing a parameter for LOB, but that is out-of-scope for my current question. But that information sort of points you down the train of thought I am travelling. Thanks for any help you can provide!

[former_member142702 · Accepted solution]

You can create a derived table containing a union like the following:

select a,b,c from DailyFacts where (@prompt('View'....) = 'Daily' and (<rest of your where conditions here if necessary>)
union
(select a,b,c from MonthlyFacts where (@prompt('View'....) = 'Monthly' and (<rest of your where conditions here if necessary>))
union
(select a,b,c from YearlyFacts where (@prompt('View'....) = 'Yearly' and (<rest of your where conditions here if necessary>))

I assume that you are familiar with the @prompt syntax

Regards,

Stratos