question

IoanCosminContractor-5993 avatar image
0 Votes"
IoanCosminContractor-5993 asked IoanCosminContractor-5993 commented

Tabular SSAS: dimension universe reduction

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered IoanCosminContractor-5993 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

right, the old-fashioned way would do, re-instantiating the same dimension, but pre-filtered. I thought, by now, there might have been a niftier method of doing so, in the latest and greatest SSAS tabular. thank you, Alexei.

0 Votes 0 ·
LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered IoanCosminContractor-5993 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I don't really want to use one dimension, say of 100k dimension, and link it to a fact of say 10k rows, when only say 2k employees are involved in that activity. So, normally I would instantiate another dimension, as I don't want to surface to the end user the whole 100k employees. I thought there might be another nifty construct in SSAS tabular, as of late, but I guess the traditional re-instantiation of another pre-filtered dimension would do. Thank you.

0 Votes 0 ·