Hello experts!
The image shows part of a Dimensional Model design a colleague and I are working on. We are building a data warehouse and BI application using SQL Server and SSAS Tabular Model respectively.
Intro
My colleague settled on the above design because:
CCG Population and GP Population contain numeric data that will be used to calculate “Number of calls per 1000 population”
CCG & GP Population tables contains numeric data used in a measures and should be considered as fact tables.
This method avoids a monster dimension
I feel that there is perhaps a better approach because:
Fact Call Log is a business process, the other two fact tables are not.
GP Population and CCG Population should become dimensional attributes within DIM GP Practice.
In terms of super-simple user interface, I wish to have a single dimension holding this data.
The Challenge
DIM GP Practice is a dimension with many rows.
GP Practice Name, GP Population and CCG population all change independently of each other at different times and frequencies.
Consequently, we need to
track 3 changing variables in a single dimension – which may lead to an explosion in the number of rows in DIM GP Practice.
We also need to
keep track of history, so the measure uses population figures in 2012 when viewing 2012 data and population figures for 2014 when viewing 2014 data and so forth.
Any thoughts on how best to refine this dimensional model?
Priorities are very easy user interface i.e. end-users have the smallest number of dimensions that they slice and dice by and ease of development to ensure rapid updates and changes going forward with minimal messing around with DAX statements.
Looking forward to your feedback!
Cheers!