calendar grain on both dimension and fact tables

[rmsranjith]

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.

[ngalemmo]

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.

[rmsranjith]

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.

[nick_white]

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

[ngalemmo]

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.

[Sponsored content]