Skip to Content
author's profile photo Former Member
Former Member

How to calculate value based on 2 fact tables?

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.

efashion.png (46.9 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    Posted on Sep 30, 2015 at 02:52 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 30, 2015 at 12:11 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.