Fact, Dimension or Both? Healthcare Call Transactions and Populations...
2 posters
Page 1 of 1
Fact, Dimension or Both? Healthcare Call Transactions and Populations...
Hello experts!
CHALLENGE: Any thoughts on how to improve the dimensional model in the image?
The image shows part of a Dimensional Model design I am working on with a colleague.
My colleague settled on this approach because:
- CCG Population and GP Population contain numeric data that will be used to calculate “Number of calls per 1000 population”
- It contains data used in a calculation it is therefore a fact.
I feel that there is perhaps a better approach because:
- Fact Call Log is a business process, the other two fact tables are not.
- I feel that 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.
Curveball:
GP Practice Name, GP Population and CCG population all change independently of each other at different times.. Consequently, we need to track 3 changing variables – which may lead to an explosion in the number of rows (which is already huge) if everything is consolidated in a single dimension.
Any thoughts?
Tables
Fact Call Log: Every call for a medical centre referral is a transaction.
DIM GP Practice: a Slowly Changing Dimension with > 10,000 rows with Medical Centre data. Updated quarterly.
FACT CCG Population: Population is updated yearly. Sourced from another business unit.
FACT GP Population: Population is updated quarterly. Sourced from another business unit.
Terms:
A GP is a general practitioner / doctor.
A CCG consists of many GPs in a specific geographic area.
CCG population: total population in the CCG area.
GP Population: number of registered patients a GP serves.
Thanks in advance!
JohnAeris- Posts : 2
Join date : 2014-04-09
Re: Fact, Dimension or Both? Healthcare Call Transactions and Populations...
NB
1) For easy navigation, a CCG consists of many GPs and so it is a natural hierarchy.
2) DW is built with SQL Server 2008 R2. BI application is built with the BISM Tabular Model.
1) For easy navigation, a CCG consists of many GPs and so it is a natural hierarchy.
2) DW is built with SQL Server 2008 R2. BI application is built with the BISM Tabular Model.
JohnAeris- Posts : 2
Join date : 2014-04-09
Re: Fact, Dimension or Both? Healthcare Call Transactions and Populations...
CCG should be a dimension with foreign keys from all three facts.
The population fact should use GP_PRACTICE_KEY, not _CODE.
The other two tables are fact tables. They contain measures bounded by multiple dimensions. They are not attributes.
The population fact should use GP_PRACTICE_KEY, not _CODE.
The other two tables are fact tables. They contain measures bounded by multiple dimensions. They are not attributes.
Similar topics
» Call Fact Table
» Agent Dimension in Call Center DW
» Healthcare, diagnosis and Dimension model
» Healthcare billing fact grain
» Call Center calls fact table
» Agent Dimension in Call Center DW
» Healthcare, diagnosis and Dimension model
» Healthcare billing fact grain
» Call Center calls fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum