I'm new to DW/BI, and this seems like a pretty basic question, but I can't seem to find a good answer.

I have a fact table that would have more than one date associated with it, so I was thinking that I would have multiple date keys back to the same dimension table, e.g., SubmittedDateKey, ClosedDateKey. Obviously SQL Server can do this - my reservation in this is that when I designed a proof-of-concept schema this way, the client tools (i.e., Tableau, Excel), seemed to get confused about how to map the relationships. (I also found this forum post saying that Oracle's Analytic Workspace Manager explicitly forbids this, which of course implies nothing about my Microsoft world, but suggests that it's a general no-no.)

Is there a best practice in DW/BI about this? Instead of having one dimension table hooked up to each of these keys, should I have multiple identical dimension tables, e.g., SubmittedDateDimension, ClosedDateDimension?



I'd use the same dimension table. Especially for a date dimension
This is a Role Playing Dimension also here

I would not let a client tool (or an Oracle forum post!) determine my SQL Server DW design.

  • 1
    I certainly wouldn't let a random comment determine a design, it was just the only info I'd found. The client tools, on the other hand, are very important to the design - if they can't use what I build, then it's all for naught. However, the info from the Kimball website makes it clear that what I was planning is totally in keeping with the star-schema approach, and the client tools should know how to deal with it. Thanks! – nateirvin Dec 5 '14 at 16:03

Generally, downstream tools will reference your data in some kind of intermediate form, like SSAS cubes, QlikView QVD files, or simply CSV. But if your downstream tool is hitting the database directly, you can simply create a series of views, one for each roleplaying dimension. Each can be defined as SELECT * FROM Dates. Have Tableau and Excel reference these instead of the single Dates table.

That assumes you're not using foreign keys to direct the analysis tool(s). If so, I recommend you drop them, as the overhead generally isn't worthwhile for a warehouse. If you can't, you have no choice but to create multiple Dates tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.