Tabular SSAS: dimension universe reduction

Ioan, Cosmin (Contractor) 21 Reputation points
2021-03-31T13:49:11.533+00:00

hi folks,
I was wondering if there is currently another method to join smaller fact tables to common dimensions (say "Employees") whereas when looking at the smaller fact tables, the common dimension can surface a smaller universe of the particular dimension (say "Employees"), other than the old,fashioned way of instantiating another dimension, by a different name?

Reason being, I have large fact tables with the entire universe of "Employees" however also smaller ones that deal with just a fraction of the Employees universe, but would like to keep the dimension name consistent (preferably the same) for the small fact tables also.

many thanks,
Cos

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,244 questions
0 comments
Accepted answer
  1. Alexei Stoyanovsky 3,411 Reputation points
    2021-04-01T07:58:58.883+00:00

    I guess the real reason is that your customer would like a filter or a slicer in a report to contain just the employees relevant to the smaller 'fact'. This will have to be set up at the report level if you stick to the common 'dimension' table, or you'll have to instantiate another table in the model to contain the data for the smaller 'dimension'. Note that each table in a model must have a unique name, so you could theoretically split the model into two.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,816 Reputation points
    2021-04-01T06:14:24.19+00:00

    Hi,

    Per my understanding of current issue, e.g. you have one table of employees, and two fact tables. One big fact that dealing with all the employees, and a smaller one only affect by a smaller portion of the employees. Right ?

    I think for this scenario, it is not nessesary to create two dimensions, you could use only one employee dimeesion and join it with both the tables in SSAS.

    Or do you have other concerns? If so, please clarify the issue so we could help further.

    Regards,
    Lukas