Design for augmenting a dimension with statistical data

View previous topic View next topic Go down

Design for augmenting a dimension with statistical data

Post  jdyson on Wed Sep 04, 2013 11:12 am

I am hoping the group can help with a design dilemma that I am not sure how to solve properly.  As a matter of background, this data will be primarily exposed via Analysis Services (2012), but I want to make sure the underlying dimensional model is proper regardless of the tool used in the OLAP layer.

I have a number of fact tables that reference a Facility dimension (hospitals, physician practices, etc.).  I want to supplement the information in the "Facility" dimension with statistical information that is updated annually (i.e., Total # of discharges).  I need to be able to use some of these statistics to create measures derived from the fact table and the statistical values associated with the Facility dimension.  I expect the Facility dimension to support SCD Type II changes in the future so for design purposes I am assuming it is SCD Type II now (the dimension load treats everything as Type I for now).

Here is an example of the tables involved:

FactReferrals
ReferralDateKey
ReferringFacilityKey
ReferralCount
...

DimFacility
FacilityKey (Surrogate Key)
FacilityCode (Business Key)
FacilityName
...
RowIsCurrent
RowStartDate
RowEndDate

I am hoping to avoid implementing the full SCD Type II support in the ETL for the Facility dimension for now.  The reason for this is that SCD Type II support on the facility dimension is not high enough value other than this statistical data to implement now if we can avoid it.

I'm having trouble discerning whether "Total # of discharges" is a dimension attribute or a fact.

Options I have considered include:

  1. Creating a separate "Facility Statistics" dimension and joining it directly to the fact table.  This would require joining it to every fact table where we want to do this type of analysis.
  2. Treating the statistical values as Type II attributes on the Facility dimension.  Aggregations might get tricky for calculations based on fact table values and the dimension attributes
  3. Creating a factless fact table (possibly collapsing into a employee transaction dimension as illustrated in the Kimball books) and exposing both fact tables in Analysis Services in separate measure groups related by FacilityKey and Date.


Do you have any other options?  Have you had similar challenges and how did you resolve them?

Thank you in advance for your consideration.

Regards,
Jesse Dyson
Advanced Home Care

jdyson

Posts : 1
Join date : 2013-01-11

View user profile

Back to top Go down

Re: Design for augmenting a dimension with statistical data

Post  ngalemmo on Wed Sep 04, 2013 1:20 pm

Technically, its a measure and should be in an aggregate fact table. There are probably a handful of other year-end measures you would probably put there as well.
avatar
ngalemmo

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

View user profile http://aginity.com

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