Accumulating Fact Table Dates as Role Play Dimension and Descriptions

Dear friends,

I have a data modeling issue that I want to share with you

I have a candidate accumulating fact table with more than 20 dates per order… I just need to explore through a dimension date (role play) 2 or 3 of them. What you suggest to do with the remain dates that should be available as descriptions? Create one dimension (junk) with all encountered descritions dates? And do the calculation lags under the ETL process and store in the accumulating fact table?

Regards and thanks!
Pedro

I would build the other dates as date dimension fk's as well. You don't have to build the fk index. Always keep the lag metrics in the fact table.

Make any sense for you to create around 20 extra dimensions that the end-user just want to read date column and not always?
For me doesnt make any sense create these dimensions

I build to anticipate customer requirements. Next month, when the customer suddenly wants to do lag reporting to one of the other "not needed" milestones, I'm good to go. You, on the other hand, will have to redesign and reload your fact table.

antecipate adding 20 extra dimensions?
Dont forget that the main dimensional model goal is to provide a simple model with the less dimensions as possible to enable ad-hoc analysis.

If that requirements arise, I just need to remove a date from a dimension attributes to a FK key in fact... doing that the user start from a simple model.

just my opinion based on my experience.

Hi - for an accumulating fact you would need a Date Dim FK on the Fact table for every step in the process that your are accumulating. If you don't need to know when a step in the process occurred (and be able to analyse by the date attributes of when this occurred) then I would suggest it is not a step that you should be accumulating into your fact.
If you have other dates that are just attributes (i.e. you may want to group/filter by them but you don't need to know which month/quarter/year etc. they refer to) then put them in the relevant dimension. Putting them in a junk dimension doesn't sound correct as dates must refer to (or be in the context of) something else, and that 'something else' presumably exists in a dimension?

Alternatively, if these extra dates are not actually needed for analytical purposes then leave them out of your model. Just because there are attributes in your source system doesn't mean you must have them all in your dimensional model

I guess I don't see how this would be adding 20 additional dimensions. There will be 1 date dimension. Your accumulating fact table will FK to it 20, or so, times. Not sure where you see complexity to the end users (it's sure not on the data warehouse side).

You cannot see the diference from having a model with 5 dimensions or 25 dimensions?!

I think I'm finally following what you're trying to accomplish here. The way that you stated the question was confusing. What you have is an accumulating snapshot with 2 or 3 event dates and corresponding lag metrics. You also have 20 other columns that happen to dates. The fact that they are dates is irrelevant as they hold no analytical value. You can either store them in an order dimension which will be 1-1 with your fact table. This approach will work for awhile but it will not perform well once the volume of data becomes significant. The other option, as you noted, is a junk dimension. You might even need two or three junk dimensions to get low cardinality as dates tend to be high cardinality columns which increases the distinct count of rows. I would play around with the junk dimension combination until I found the ideal combination of columns that give the best results.