Design for augmenting a dimension with statistical data
2 posters
Page 1 of 1
Design for augmenting a dimension with statistical data
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:
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
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:
- 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.
- 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
- 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
Re: Design for augmenting a dimension with statistical data
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.
Similar topics
» Data Deduplication in Dimension Design
» Dimension Design with Multiple Data Sources (ORACLE and SQL Server)
» Design Alternatives for Late Arriving/Updated Type 2 Dimension Data
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Dimension Design with intermediate tables between fact and dimension
» Dimension Design with Multiple Data Sources (ORACLE and SQL Server)
» Design Alternatives for Late Arriving/Updated Type 2 Dimension Data
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Dimension Design with intermediate tables between fact and dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|