Fact, Dimension or Both? Healthcare Call Transactions and Populations...

Go down

Fact, Dimension or Both? Healthcare Call Transactions and Populations...

Post  JohnAeris on Thu Apr 10, 2014 11:13 am



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

View user profile

Back to top Go down

Re: Fact, Dimension or Both? Healthcare Call Transactions and Populations...

Post  JohnAeris on Fri Apr 11, 2014 1:55 am

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.

JohnAeris

Posts : 2
Join date : 2014-04-09

View user profile

Back to top Go down

Re: Fact, Dimension or Both? Healthcare Call Transactions and Populations...

Post  ngalemmo on Fri Apr 11, 2014 1:07 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Fact, Dimension or Both? Healthcare Call Transactions and Populations...

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum