IS IT OK TO CREATE METRICS IN BMM LAYER USING FOREIGN KEY

3219279

Hi Experts,

  I have a question on using foreign key to create metric in OBIEE. I have never seen metrics created on Key's/WID's in any of my previous implementations, even in BI Apps. Assume we have below fact and dimension.

  DW_SALES_F                                       DW_SALES_ORDER_D

  ORDER_DT_KEY (foreign Key)                      SALES_ORDER_KEY (Primary Key)

  SALES_ORDER_KEY (foreign Key)               SALES_ORDER_NUMBER

  AMOUNT                     

  Now, If I need to create a metric to get # of sales orders, I have below options:

  1) Use count distinct aggregation rule in BMM on SALES_ORDER_KEY

  2) Extend the DW_SALES_F fact to add SALES_ORDER_NUMBER as a column and then apply count distinct aggrigation on this new column in BMM

  3) Add an new LTS to the fact and get the SALES_ORDER_NUMBER  from Dimension and create metrics. (This is not preferred as DW_SALES_ORDER_D needs to be a confirmed dimension to use this metric in a report created from multiple facts)

  Which of the above option is preferred and why?

  Thanks in Advance.

Joel

You need to create a logical column in your logical fact table in the BMM layer based on your sales order key and set the aggregation to count distinct

Gianni Ceresa

Hi,

It's perfectly fine to use a key / foreign key as measure, and actually what you want to do is the main usage of keys / foreign keys in a fact table : count occurrences of things.

Now the question is more the relation between SALES_ORDER_NUMBER and SALES_ORDER_KEY : if this relationship isn't 1-to-1 then your measure will not mean the same thing if you use a count distinct on SALES_ORDER_KEY or on SALES_ORDER_NUMBER. This is a question you must answer based on your data and your design (if you added a KEY to the SALES_ORDER_D because you like to always have an official KEY, a surrogate key, instead of using directly a business key).

[Deleted User]

YES IT IS PERFECTLY OK AS LONG AS YOU STOP SHOUTING