calendar grain on both dimension and fact tables

View previous topic View next topic Go down

calendar grain on both dimension and fact tables

Post  rmsranjith on Mon Feb 02, 2015 9:54 pm

I am creating semantic layer for users who are expecting a snapshot of every membership for every health plan for every month for two kind of report types.

Grain of the fact table is determined as one record for every member for every plan for every month for each report type.

Problem is in defining the grain of dimension tables. We have different dimensional tables - membership, plans, area etc. Question is how to define the grain of the dimension table for membership. Can dimension table be on member level and month level or it should be only on member level and having calendar grain on the dimension table is not encouraged? When it is just member level without calendar month field, it becomes very difficult to tie FACT to dimension table.

Right now I have cal_key present in both dimension and fact table. Appreciate any suggestions.

rmsranjith

Posts : 2
Join date : 2015-02-02

View user profile

Back to top Go down

Re: calendar grain on both dimension and fact tables

Post  ngalemmo on Mon Feb 02, 2015 10:34 pm

It would be cleaner if there was a month dimension.  In lieu of that, the key for the snapshot should reference a fixed day of the month (usually first or last) in the existing date dimension.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: calendar grain on both dimension and fact tables

Post  rmsranjith on Tue Feb 03, 2015 9:11 am

Hi ngalemmo, thanks for your reply. We do have a date dimension with date key.
Problem is there is a membership dimension table that has this date key as foreign key and so is the fact table. Below is a glimpse of the model. I have doubts if having date key as foreign key in dimension tables is a good idea or not.

rmsranjith

Posts : 2
Join date : 2015-02-02

View user profile

Back to top Go down

Re: calendar grain on both dimension and fact tables

Post  nick_white on Thu Feb 05, 2015 3:28 am

Hi - I was wondering what the Date Key on your Dim relates to: what attribute of the membership is it?

If it is there to indicate a month of membership then that design is almost certainly incorrect - this is what the fact table is for. The Membership Dim is there to hold attributes about the Membership (start date, end date, status, level of membership, etc.). Whether a membership is active in a particular month is a fact about the membership, not an attribute

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: calendar grain on both dimension and fact tables

Post  ngalemmo on Thu Feb 05, 2015 4:11 am

More to Nick's point, it's all about context.

I can't tell what the date key in the membership table represents. To paraphrase Nick's comments, it should have nothing to do with membership enrollment. Membership enrollment is represented by the fact table. Contexts that relate to the enrollment event should be referenced from that event.

So, in a dimensional model we apply contexts to events. In a clean dimensional model, all relationships are between a fact table and one or more dimension tables, because that's all that should matter.

Now, there is no reason a dimension should not have dates. It may be the first date the member enrolled. The question is, should you snowflake that dimension or not? A snowflake model adds dimension-to-dimension relationships to a star schema. At issue is, assuming this is the first enrollment date, what does the business want to know about the date? Then simply denormalize the relationship by placing those attributes into the member dimension and eliminate the foreign key. If you want to play it safe, do both. Keep the foreign key as well as add additional date attributes to the member dimension. You then control what users see through the BI layer. Normal users doing typical queries would not see or use the foreign key, while allowing access to other users who need the full collection of attributes relating to that date.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: calendar grain on both dimension and fact tables

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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