You might be able to get this done with a fancy Custom SQL statement if your "SQL Kung Fu" is strong enough. However, I suspect your query would run pretty slowly based on what you're talking about - no matter how good your SQL skills are and regardless of whether you run the query from Tableau or from any other tool.
Instead, I'd suggest you abandon the idea of creating one single monolithic data source. Instead, use multiple data extracts (maybe one per fact table? ) which aggregate to whatever level of granularity you need. Then, "blend" them together using Tableau Data Blending.
Hope this gives you a push in the right direction.
Thanks Russ. Do you mean creating two separate connection to the same DB such that each connection has only one fact table and all associated dimension tables?
Does 'Blending' allow different granularity in the two data sources or they have to be exactly at the same granularity? if the granularity is different, would displaying measures from the different fact tables in the same visualization give correct results?
Thanks in advance.
Hey Kamlesh -
I'll generally use my "most important" fact table in my first (and "primary) datasource, along with all related dimension.
I'll then create x more "secondary" data sources which point to the other fact tables. Next, I'll use Tableau's Fast Data Engine (extract) capability to aggregate the secondary data sources to whatever grain I need.
Then, one "blends" the various data sources together.
It probably will make more sense to you if you play around with a few data sources for a minute or two
thanks about your question, it summarize this common issue that appears in middle and big projects.
To solve it I use OLAP data source (SSAS).
For me, use SSAS has some advantage:
- Metadata are all time ready, not needed to join tables in each new viz.
- Also, user security is in database layer.
- Some tableau calc functions are not availabe (because OLAP server do aggregations)
- In the past I needed MDX expressions, now Tableau is able to do complex calculations.
Can you expand more on "Tableau's extract capability to aggregate to whatever level I need?" I've only experienced it aggregating to whatever level my data was, and if my fact table grain is different on the second schemas, then the blending didn't work as expected. I would love to learn what I may be doing wrong.
I hope this worked for you Kamlesh!