Can someone help?
1 of 1 people found this helpful
It looks like what you want is to UNION the two data sources together instead of blending one to another. A Tableau data blend is a variation on a left join that starts with linking dimension(s) from the primary, all fields from secondary source(s) are aggregated to the level of those linking dimension(s). In other words, a data blend will not increase the level of detail in the primary. In your case, the primary does not have all combinations of those fields available, so as you turn on the linking dimensions there is less and less matching data from the secondary.
There are a variety of techniques available to union the data together:
- copy and paste the data into the same Excel spreadsheet
- copy the data onto two worksheets in the same workbook, then use Tableau's custom SQL to UNION the data together
- start with one workbook, then use Excel's linked worksheets capability to link the worksheet from other workbook into the first workbook, then use Tableau's custom SQL to union the data together
- build a scaffold data source that has all of the unique combinations of linking dimensions and then blend the two data sources to that.
Thank you for the reply. It is helpful but I got more queries.
1. I have used excel as sample. Actual data resides in 2 different databases and volume is 33 million in db 1 and 18 million in db 2. Therefore, I can not use excel in actual scenario.
2. You are right. I need to union two data sources. How can I do it when I need to get data from 2 different data sources as mentioned in point 1?
3. Scaffold data source is not a possible solution since I have at least 5 common fields and data for 2 years. I need ability to plot graph with granularity of Daily on time axis. Meaning, the scaffold data source will have huge number of combinations to maintain. And our data is refreshed everyday midnight and we can not maintain scaffold data source.
Waiting for your inputs!
No matter what, you’re going to need to do some federation of your databases. You didn’t say what kind of databases you were using, I know that Oracle and SQL Server each have the ability to set up linked tables that point to other databases, you can use that to generate your UNION query or a scaffold solution.
The scaffold solution can be completely dynamic, it’s just a matter of getting access to all the values and then writing some queries that do the appropriate combination of unions, left joins, and/or cross products. Tableau can handle billions and billions of records, so the huge number of combinations is not a problem. However, since you have to go through the process of creating a federated database, the UNION query there would be easier to set up and maintain.
One is Netezza and other is Oracle. Therefore, the option of UNION does not seem possible.
Thus, am I left with putting both tables in same DB and doing this Union on DB/DW side?
Also just on the sidenote, we are using Qlikview and it provides ability to concatenate or union two tables from different data sources during scripting. As I understand there is no such scripting or concatenate ability in Tableau. Am I rt?
I did a quick lookup online and there are Netezza ODBC drivers, so you can definitely create the federated system approach and do the UNION on the Oracle side, I don't know about going the other direction.
I've done a couple of evals of Qlikview, the ability to UNION multiple tables from multiple data sources is one of the QV features that I wish Tableau had. I know it's "on the list" of potential development items, but don't know if/when it might be released. I thought there was an Idea out there for it, but can't find it. There might be something in Johan's Ideas Collections.