I have a scenario I’m working through that looks something like this:
I have 2 tables (in practice way more than 2 tables) that entail GA site analytics that are both joined with a content items dimensions table and each with a date dimension. One fact table, with aggregated metrics, looks like this:
cms_id | date | metric | value
123abc | yesterday | 'pageviews' | 10000
and another fact table, which deals with traffic source metrics specifically, looks like this:
cms_id | date | source | medium | campaign | pageviews
123abc | yesterday | twitter | socialmarketing | SF | 1200
Treating cms_id as a consolidated dimension works great because I can specify that both of these tables join with my content items table on that cms_id, and that’s fine. But I don’t have a corresponding dimension table for dates! So right now, in my explore there are 2 date dimensions and you need to know coming in which dimension to use depending on which table you’re working with!
What’s the right way to model this, so that I can select date in one spot in my explore and have it do the right thing on both of these tables, without creating a dates dimension table?