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

View previous topic View next topic 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

View previous topic View next topic Back to top

- Similar topics

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