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

Question

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.

[Deleted User] · Answer

YES IT IS PERFECTLY OK AS LONG AS YOU STOP SHOUTING

Gianni Ceresa · Answer

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).

Joel Acha · Answer

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