locked
Linking Multiple Fact Table at different grains to same dimensions RRS feed

  • Question

  • Hello Folks,

    I am seeking your advice on dimensional modelling problem I have.

    I am modelling two fact tables of Actuals and Budget which are at different granularity, Actuals are at day, customer and product sub category level. Budgets are at month, Region and Product catgeory level.Month, Region and Product Category is present in Date, Region and Procut Category dimension respectively. I have only three dimensions as Customer, Product and Date. Linking those dimensions to Actual Fact table is not an issue, what is the best way and options are there to link budget fact table to those three dimensions.

    Please advise.

    Regards,
    Haroon

    Wednesday, July 15, 2015 11:26 AM

Answers

All replies

  • Will this be for Tabular or Multi-Dimensional cubes? I'm doing tabular and I handled the dates for budget by assigning it to the last day of the month. (The rest of my grain matched though) Then I wrote a DAX function on all the budget columns to project it over the month. With tabular, you could snowflake the other dimensions to achieve the results tying the budget to the higher level dimension. For Multi-Dimensional, I don't know how to handle this scenario.
    Wednesday, July 15, 2015 12:44 PM

  • Hi Haroon8,

    According to your description, you have two fact tables at different grain. Right?

    Generally, in Analysis Services, the snowflake schema is the most efficient way to populate an Analysis Services cube. In this scenario, I suggest you snowflake the dimension into two separate dimension like DimProductCategory and DimProductSubCategory in AdventureWorks sample database. Please refer to links below:

    Best Practices for Business Intelligence Using the Microsoft Data Warehousing Framework

    Understanding Star and Snowflake Schemas

    Regards,


    Simon Hou
    TechNet Community Support



    Thursday, July 16, 2015 12:04 PM
  • How is this even remotely close to the original question?

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.
    My blog

    Tuesday, February 28, 2017 1:04 PM
  • Using product categories as an example, after snowflaking the Subcategory and Category dimensions off the existing Product dimension (funny how it must've related to the Actuals at non-grain Subcategory level), you get regular relationships Actuals-Subcategory and Budget-Category, and referenced Actuals-Category. A pity we never heard from the OP how it worked out for them...


    Expect me to help you solve your problems, not to solve your problems for you.

    Tuesday, February 28, 2017 3:49 PM
  • Using product categories as an example, after snowflaking the Subcategory and Category dimensions off the existing Product dimension (funny how it must've related to the Actuals at non-grain Subcategory level), you get regular relationships Actuals-Subcategory and Budget-Category, and referenced Actuals-Category. A pity we never heard from the OP how it worked out for them...


    Expect me to help you solve your problems, not to solve your problems for you.

    Ah ok, now I get it :)


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.
    My blog

    Tuesday, February 28, 2017 8:02 PM
  • Hello Friend, could you show me how you resolved it pls?

    saludos


    Tuesday, June 13, 2017 1:28 PM