none
Reusing a dimension table in a tabular model

    Question

  • I'm new to tabular models and SSAS in general, so I'm looking for a best practice.

    I have two unrelated fact tables that reference the same dimension table (Vendors).

    What is the best way to build the cube(s) and minimize the memory footprint?  Do I have to import the Vendors twice?  Or is there a way for each fact table to reference a single instance of the Vendors?

    I know I can put both fact tables in the same cube and have them each reference the Vendors table, but that seems to create other problems when users think they can query across both fact tables.

    Thursday, April 12, 2012 8:44 PM

Answers

  • That's a tough one, Dayne. It's kind of a "yes and no and maybe". 

    I suppose you have some choices in Tabular for 2012:

    1. You could use perspectives to hide the fact tables you don't want as part of a query.  But with 30 fact tables, I think probably this is not a practical idea (30 perspectives? no thanks!).  And anyway perspectives can be circumvented by users since they choose them when making a connection--they can still choose "none of the above" and get the entire model in one pivot table.

    2. You could set all of these extra relationships as inactive, and only activate them for calculations that are needed.  But if you're providing an ad-hoc pivot table environment, maybe this won't meet your needs...beause the USERELATIONSHIP is leveraged in calcuations--not in pivot tables.  For an illustration of this see http://msbiacademy.com/Lesson.aspx?id=87.  If what you need from each of these 30 fact tables is a small number of calculations each, you could actually make this work (users wouldn't only calculated measures, and your calculations would activate the appropriate query).  But compared to using Multidimensoinal role-paying dimensions--still much more complex to implement and maintain. 

    Given your scenario, I need to ask whether you think using Tabular mode is really the best choice for you in this project.  Are you open to using Multidimensional cubes for this particular solution?  MD handles this kind of scenario very well...you would need only to add the single dimension to the dimension usage tab as many times as needed and connect each [role playing] instance of the dimension to the appropriate measure group.  You'd definitely not store the dimension data 30 times (minimizing disk storage), and you'd accomplish the objective of having no query time impact when users selected multiple measure groups.  In Excel they would use the "Measures realted to" drop-down to make sure to pair fact tables and dimension tables appropriately...  Something to consider...


    Rob Kerr SQL Server MVP CTO, BlueGranite

    • Marked as answer by Dayne Monday, April 16, 2012 5:25 PM
    Friday, April 13, 2012 8:54 PM

All replies

  • How would you ideally like to present this in a pivot table?  Do you envision having two dimensions listed with different names--one with a relationship to the first MG and the other with a relationship to the second MG?

    If so, this is a role-playing dimension, and not something tabular supports in this version.  I think you will have to import the table two times in the model and give each tab a different name, and set the relationships appropriately as you fear.

    The memory footprint of doing this may not be as severe as you might think...the compression will be excellent, especially if your vendors table has columns with a lot of repeating values (state, country, city, etc.).  Reduce the columns to just what's needed of course to reduce memory footprint.  I'd suggest to build a tabular model with only the vendors table, process it and just see how big it becomes (actually pulling into PowerPivot Excel would be a quick way). 


    Rob Kerr SQL Server MVP CTO, BlueGranite

    Friday, April 13, 2012 1:14 AM
  • Hi Dayne,

    There is no need to import the Vendors twice, just open the diagram tab, then create the relationship between fact tables and dimension table. Besides, I would recommend you follow this tutorial, which provides lessons on how to create a SQL Server 2012 Analysis Services tabular model running in Cached (tabular) mode by using SQL Server Data Tools (SSDT) http://msdn.microsoft.com/en-us/library/hh231691.aspx

    Thanks,


    Challen Fu

    TechNet Community Support

    Friday, April 13, 2012 2:28 AM
    Moderator
  • Dayne,

    Challen is absolutely right that you can relate a dimension to two fact tables that are unrelated. I didn't mean to say you can't do that.

    I may have misunderstood your question.  I thought you meant you didn't want users to use the same dimension to slice the two different fact tables. When you wrote "...that seems to create other problems when users think they can query across both fact tables." what kinds of problems does this create for you?


    Rob Kerr SQL Server MVP CTO, BlueGranite

    Friday, April 13, 2012 2:33 AM
  • Rob - You understood correctly.  If I relate both fact tables to the same dimension table, my users may include measures from both fact tables in the same query.  When they do that, the query runs forever, server memory usage grows dramatically, and the query usually fails.

    It sounds like my best option is to store two different copies of the dimension table.

    My biggest worry is with other scenarios where I have maybe 30 different fact tables that each relate to an employee dimension.  The employee dimension has 600,000 records and many columns.  It sounds like I'm going to have to duplicate that data 30 times.  Is that correct?

    Friday, April 13, 2012 6:30 PM
  • That's a tough one, Dayne. It's kind of a "yes and no and maybe". 

    I suppose you have some choices in Tabular for 2012:

    1. You could use perspectives to hide the fact tables you don't want as part of a query.  But with 30 fact tables, I think probably this is not a practical idea (30 perspectives? no thanks!).  And anyway perspectives can be circumvented by users since they choose them when making a connection--they can still choose "none of the above" and get the entire model in one pivot table.

    2. You could set all of these extra relationships as inactive, and only activate them for calculations that are needed.  But if you're providing an ad-hoc pivot table environment, maybe this won't meet your needs...beause the USERELATIONSHIP is leveraged in calcuations--not in pivot tables.  For an illustration of this see http://msbiacademy.com/Lesson.aspx?id=87.  If what you need from each of these 30 fact tables is a small number of calculations each, you could actually make this work (users wouldn't only calculated measures, and your calculations would activate the appropriate query).  But compared to using Multidimensoinal role-paying dimensions--still much more complex to implement and maintain. 

    Given your scenario, I need to ask whether you think using Tabular mode is really the best choice for you in this project.  Are you open to using Multidimensional cubes for this particular solution?  MD handles this kind of scenario very well...you would need only to add the single dimension to the dimension usage tab as many times as needed and connect each [role playing] instance of the dimension to the appropriate measure group.  You'd definitely not store the dimension data 30 times (minimizing disk storage), and you'd accomplish the objective of having no query time impact when users selected multiple measure groups.  In Excel they would use the "Measures realted to" drop-down to make sure to pair fact tables and dimension tables appropriately...  Something to consider...


    Rob Kerr SQL Server MVP CTO, BlueGranite

    • Marked as answer by Dayne Monday, April 16, 2012 5:25 PM
    Friday, April 13, 2012 8:54 PM
  • Rob,

    Thanks for the suggestions.  That gives me some good guidance to start from.

    I have several scenarios like this that make me wonder why Microsoft is so eager to phase out report models.  It seems like tabular models need a little more time to mature first.

    Monday, April 16, 2012 5:39 PM
  • No problem, Dayne.  And good luck on your project!

    Maybe in a future version tabular will support role-playing dimensions more directly--after all role-playing dimensions weren't supported in the first release of SSAS Multidimensional either!

    And honestly I don't think Microsoft is "eager" to phase out multidimensional models, in fact they have said many times they think both technologies are important and both are part of the roadmap.  There are still a few things that multidimensional inherently does better than tabular (and vice-versa).  As can be expected they're very proud of this new Tabular technology they added to the product and talk about it more often and with more excitement than the established technology :-).


    Rob Kerr SQL Server MVP CTO, BlueGranite

    Monday, April 16, 2012 7:11 PM
  • Yeah, I didn't mean multidimensional models.  I meant report models.

    We have a lot of report models that the business users love.  They use Report Builder against the models, and it's been relatively easy for them to pick them up and become productive.

    I'm trying to transition them to tabular models, but now it looks like I'm going to have to include multidimensional models in the mix.  It's becoming a lot for them to learn, not to mention our small IT team that needs to support these three approaches.

    On top of that, I don't think you can use DAX in Report Builder, even though you can in Excel.  So now our users may have to learn MDX and/or DAX along with the two expression languages they already learned for Report Builder.  (Report model query expressions and VB expressions in the report.)

    We used to have a pretty simple reporting solution with easy entry for new users, and it's becoming too complicated to expect a business user to be productive.

    Monday, April 16, 2012 7:35 PM
  • Hi Robb,

    What if i have a single fact table with order date and ship date which i want to extract into an Excel pivot and find the number of orders where i have order months along the row label area and ship month along the Column label area?? Will inactive relationship in tabular help me do the same and How?? or do i have to load the date dimension twice??

    Tuesday, December 10, 2013 9:12 AM