To a user using the explore it will just look like a normal date even if it's coming from some other table.
but I need to specify this join for multiple tables!
"why don't you just use from," I can hear you saying--I tried that, but then the dimension again shows up as a separate field for each thing I'm joining it to, defeating the purpose! EDIT: I thought about just generating the product of the cms ids and the dates, but in a world with 50k content items and 1000 dates, that ends up with a table of 50 million rows and I don't want that EDIT 2: I ended up implementing the cartesian-product-of-ids-and-dates idea, which from a query standpoint works really well.
There are two dimension role playing implementations : Modeling Date and Time dimensions There should be only one Date dimension and one Time dimension across all facts and dimension tables.
I've been working with @jfhbrook, and we've come up with a solution that appears to be efficient and avoids unnecessary joins.
And I don't know how many date dimensions should be created? It means: one row @fact_table have one FK to A, one FK to B, and one FK to C .
An update for those of you following along: This approach worked for us for a while, but is starting to cause some pretty serious performance issues.
We're in the process of just eating the added complexity of multiple date fields.
Hey @jfhbrook, Glad that taking the cartesian-product-of-ids-and-dates idea is working for you.