How to calculate value based on 2 fact tables?

[Former Member]

Hi everyone!

I really need your help.

Let's take eFashion universe, we have 2 fact tables in it:

• Shop_facts
• product_promotion_facts

There is NO connections between fact tables and I can't do it.

I created 2 measures, based on different fact tables

• Shop_facts.Quantity_sold
• product_promotion_facts.Promotion_cost

Then I made a measure with formula

@Select(Measures\Quantity_sold) / @Select(Measures\Promotion_cost)

but it give an error:

How can i make such calculation on the universe level?

I have a universe with similar structure that is used in SAP Design Studio and I can't calculate such measures there, that's why I have to find the way to calculate it in IDT.

I can create view in MS SQL and collect both fact and plan tables, but I have 6 fact tables and some of the measures used in the calculation of other measures in different tables, so I think it is not the best idea.

Maybe there are other options?

Any advice would be really helpful.

Thanks.

[former_member285534 · Accepted solution]

Hi Timur,

Combining facts from more than one fact table is something called Drilling Across in multidimensional design theory.  The best practice to implement that is to merge the fact tables on common dimensional attributes and then compute the ratio.

From that point of view I believe that creating MS SQL views is indeed a good idea.

Let's build on the e-Fashion universe case.  Notice that shop_facts and product_promotion_facts common dimensions are Article_id and Week_id.  Notice also that the only practical way to join these fact tables obtaining consistent data is precisely using Article_id, Week_id or both as join keys.

So my advice is: use SQL to build a view in the database that

1. Aggregates shop facts to the Article_id + Week_id level

2. Aggregates product promotion facts to the Article_id + Week_id level

3. Join the two sets obtained previously on Article_id + Week_id keys

4. Obtain the calculated measure on the set of step #3

This view can be added to the universe (on a separate context) joined to the Article and Calendar_year lookup tables

A derived table from a query is an equivalent alternative to the SQLServer level view.

Depending on the data volumes you might need to index the fact tables on the common dimensions to get the best performance.

Best Regards,

Fernando

[Former Member · Accepted solution]

Excelent response. I would suggest to create an dervied table that calculates that value.

[Former Member]

Hi Timur,

If items are incompatible, it generally means that they don't logically belong together.

If two fact tables have different levels of granularity then unless you are using only conformed dimensions (those common to both facts), you won't be able to work with them properly.

What may be an option is to create views or derived tables to aggregate the two underlying tables to only have the common dimensions.

There are many ways to crack this but if possible I always try and push it back to the database for performance reasons.