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: 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? I think I understand, but just to clarify: Are you trying to get the dates in both tables to work as a single unit?It sounds like maybe you're looking to ensure that when someone filters on one of these dates, it will apply to both.We should be able to do this using templated filters, but let me know if I've missed the mark here. Though, I don't want to have to pick an arbitrary table as having the "canonical" date, since I'm not always querying that table, so I'm not sure a templated filter is what I want either.
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.Time will tell if this is sustainable from a data size standpoint.Hey @jfhbrook, Glad that taking the cartesian-product-of-ids-and-dates idea is working for you.
This is great from a query standpoint, but I totally understand your concern with this method from a data size standpoint.
If this becomes a problem in the future, you could go back to using the generating_series function.