For reporting on multiple companies in a standardized way, we are trying to build a date dimension containing multiple company specific information in a dimensional model. The goal is using time intelligence functions. We are building this with SSIS, the fact&dims will be loaded in SSAS Tabular and front-end will be PowerBI.
The companies date dimension differences consist of:
- Companies are reporting numbers monthly or quarterly, and want their graphs to only show either month/quarters depending. -> Mostly just label work. We have made a column 'Period Label' where we can Write in 'Jan,Feb,Mar,..' when the company is Monthly, we write 'Q1, Q2,..' when the company is Quarterly.
- Companies have all different fiscal years, meaning the labels are different in the date dimension for each company. -> Also for the totalytd function, we have to be able to give the correct end of year string.
Our first idea was to fill the date dimension with the specifics for every company and their company id, when we load the facts we assign them the correct date dimension primary key (meaningless integer).
This is working fine when not using time intelligence functions. When i try to mark this date dimension as a date dimension, i need to provide a unique date field. I don't exactly have this, but because of the way the facts are linked (and only one company is allowed to be selected at a time), in the end the date dimension does have a correct unique date record. I understand powerbi/ssas tabular is complaining that it isn't unique as it doesn't know only one company will be selected at any time.
Any best practises known to get powerbi/ssas tabular to accept this dimension as a date dimension despite the 'not unique' date column? Is there a better design known to provide this kind of functionality?
Date dimension(shortened) as currently was the idea, but isn't accepted by powerbi/ssas tabular Every companyid has a record for every day.:
PK CompanyID Date Period_Label FiscYear
1 1 2017-01-01 January 2017 31/12
2 1 2017-01-02 January 2017 31/12
3 1 2017-01-03 January 2017 31/12
...
4 1 2017-02-01 February 2017 31/12
5 1 2017-02-02 February 2017 31/12
6 1 2017-02-03 February 2017 31/12
....
10 2 2017-01-01 Q2 2017 30/9
11 2 2017-01-02 Q2 2017 30/9
12 2 2017-01-03 Q2 2017 30/9
....
16 2 2017-02-01 Q2 2017 30/9
17 2 2017-02-02 Q2 2017 30/9
18 2 2017-02-03 Q2 2017 30/9
....
22 2 2017-04-01 Q3 2017 30/9
23 2 2017-04-02 Q3 2017 30/9
24 2 2017-04-03 Q3 2017 30/9
