URGENT - Metric's Conditional Calculation across two fact tables

[ArunKKK]

Hi,

1) I have Location and Product hierarchies such as:
Location: Corp->Div->Region->Dist->Loc
Product: Dept->Class->SubClass->Prod

2) Have two fact tables : fact1 and fact2, and columns are
fact1: Week, Div, Class, Amt [Lowest level of granularity]
fact2: Week, Div, Prod, Amt [Lowest level of granularity]

"Amt" across these two facts tables can't be correctly aggregated due to business rules. Hierachies can only allow the drill to happen upwards on these individual facts tables.

3) Need to build a report, where in the default attributes are "Corp", and "Dept", and a metric M1, which is defined as
"Depending on drill level on "Corp", and "Dept", display Sum(Amt) from either fact1 or fact2". Which means, for example,
if the attribute drill levels are (keeping the parent while drilling):

Corp and/or Div Dept and/or Class -> M1:Sum(Amt) from fact1
Div and/or SubClass and/or Prod -> M1:Sum(Amt) from fact2
Anything below on Div in the Loc hierarchy, irrespective of the drill level on the Product hierarchy, M1 metric should display "Blanks". It also means that the aggrgations can only be performed on either of the fact tables - For example, with "Div" as Location attribute level, if we want to rollup from "Prod" to "SubClass", we would use the "Amt" from fact2, BUT if we want to rollup from "Prod" to "Class", or "SubClass to Class", then, "Amt" from fact1 table would be used.

We can definitely put the decode/CASE logic, but, how would we know the current attribute drill level, which is the actual basis for hitting the fact1 or the fact2 tables. And, to me, it si the root cause behind all these calculations.

Can anyone help me oue here? It's very urent - any help/hints would greatly be appreciated.

Thanks

 

[nlim]

actually, the behavior you want is the default MSTR behavior if you set up aggregation awareness correctly.

To do that, make sure that you create a fact called "amount", and that the source tables are only fact1 and fact2. Update schema.

now define your metric M1 as sum(amount).

So try these series of reports to make sure it is right.

Corp / Div M1 - the SQL should pull from fact1 because it is smaller. If you drill below DIV, you should get no data.

Corp/Div Dept/Class M1 - the SQL should pull from fact1 because it is smaller.

Div Subclass/Prod M1 - the SQL should pull from fact2 because according to your hierachy, the data is not in fact1.

Drilling from Class to Subclass - the SQL will automatically change from fact1 to fact2 because the subclass data is not found in fact1.

good luck, let us know if it works.

 

[nlim]

oh, i forgot to mention. The SQL will also automatically adjust when you drill up.