Using two facts of two different star schemas and conformed dimensions

[Former Member]

Hi,

I've been working as developer and database designer for years and I'm new to Business Objects. Some people says you can not use two facts of two different star schemas in the same query because of conformed dimensions and loop problems in BO.

For example I have a CUSTOMER_SALE_fACT table containing customer_id and date_id as FK, and some other business metrics about sales. And there is another fact table CUSTOMER_CAMPAIGN_FACT which also contains customer_id and date_id as FK, and some other business metrics about customer campaigns. SO I have two stars like below:

DIM_TIME -- SALE_FACT -- DIM_CUSTOMER

DIM_TIME -- CAMPAIGN_FACT -- DIM_CUSTOMER

Business metrics are loaded into fact tables and facts can be used together along conformed dimensions . This is one of the fundamentals of the dimensional modeling. Is it really impossible to use SALE_FACT and CAMPAIGN_FACT together? If the answer is No, what is the solution?

Saying "you cannot do that because of loops" is very interesting.

Thank you..

[Former Member]

It depends on what you mean by "use together" to be honest.

Yes, you can place both facts and their associated dimension tables in the same universe, create the joins, and resolve the loops with contexts. That's a standard design process and has been supported in Designer for, well, forever.

But you cannot use the two facts in the same SQL statement, because that would generate invalid results. That's true no matter what the platform is. Business Objects does not fix that issue because it's a SQL issue, not a platform issue.

[Former Member]

I mean using two facts in the same sql statement.

Suppose that SALES_FACT and CAMPAIGN_FACT contains daily sales metrics and daily campaign metrics of customers. And I want to get the list of customers that I sold something yesterday and total campaign usage is bigger than 10. Why the results would be invalid? Could you please give me an example?

I have sales fact and campaing fact in my hand. If business needs to report something that is related with sales and campaign metrics, do i have to create a new fact?

[Former Member]

When you join two facts together with a common dimension you have created what is called a "chasm trap" which leads to invalid results because of the way SQL is processed. The query rows are first retrieved and then aggregated. Since sales fact and campaign fact have no direct relationship, the rows coming from either side can end up as a product join.

Suppose a customer has 3 sales fact rows and 2 campaign fact rows. The result set will have six rows before any aggregation is performed. That would mean that sales measures are doubled and campaign measures are tripled.

You can report on them together, using multiple SQL passes, but you can't query them together. Does that distinction make sense?

[Henry_Banks]

Superb answer Dave. Nice to have you over from the BOB forums

Regards,

H