Data Blending in Tableau

Data Blending in Tableau Why Should You Blend When You Can Already Join In Tableau? Back Home Categories Online Courses Mock Interviews Webinars NEW Community Write for Us Categories Artificial Intelligence AI vs Machine Learning vs Deep LearningMachine Learning AlgorithmsArtificial Intelligence TutorialWhat is Deep LearningDeep Learning TutorialInstall TensorFlowDeep Learning with PythonBackpropagationTensorFlow TutorialConvolutional Neural Network TutorialVIEW ALL BI and Visualization What is TableauTableau TutorialTableau Interview QuestionsWhat is InformaticaInformatica Interview QuestionsPower BI TutorialPower BI Interview QuestionsOLTP vs OLAPQlikView TutorialAdvanced Excel Formulas TutorialVIEW ALL Big Data What is HadoopHadoop ArchitectureHadoop TutorialHadoop Interview QuestionsHadoop EcosystemData Science vs Big Data vs Data AnalyticsWhat is Big DataMapReduce TutorialPig TutorialSpark TutorialSpark Interview QuestionsBig Data TutorialHive TutorialVIEW ALL Blockchain Blockchain TutorialWhat is BlockchainHyperledger FabricWhat Is EthereumEthereum TutorialB lockchain ApplicationsSolidity TutorialBlockchain ProgrammingHow Blockchain WorksVIEW ALL Cloud Computing What is AWSAWS TutorialAWS CertificationAzure Interview QuestionsAzure TutorialWhat Is Cloud ComputingWhat Is SalesforceIoT TutorialSalesforce TutorialSalesforce Interview QuestionsVIEW ALL Cyber Security Cloud SecurityWhat is CryptographyNmap TutorialSQL Injection AttacksHow To Install Kali LinuxHow to become an Ethical Hacker?Footprinting in Ethical HackingNetwork Scanning for Ethical HackingARP SpoofingApplication SecurityVIEW ALL Data Science Python Pandas TutorialWhat is Machine LearningMachine Learning TutorialMachine Learning ProjectsMachine Learning Interview QuestionsWhat Is Data ScienceSAS TutorialR TutorialData Science ProjectsHow to become a data scientistData Science Interview QuestionsData Scientist SalaryVIEW ALL Data Warehousing and ETL What is Data WarehouseDimension Table in Data WarehousingData Warehousing Interview QuestionsData warehouse architectureTalend T utorialTalend ETL ToolTalend Interview QuestionsFact Table and its TypesInformatica TransformationsInformatica TutorialVIEW ALL Databases What is MySQLMySQL Data TypesSQL JoinsSQL Data TypesWhat is MongoDBMongoDB Interview QuestionsMySQL TutorialSQL Interview QuestionsSQL CommandsMySQL Interview QuestionsVIEW ALL DevOps What is DevOpsDevOps vs AgileDevOps ToolsDevOps TutorialHow To Become A DevOps EngineerDevOps Interview QuestionsWhat Is DockerDocker TutorialDocker Interview QuestionsWhat Is ChefWhat Is KubernetesKubernetes TutorialVIEW ALL Front End Web Development What is JavaScript â€" All You Need To Know About JavaScriptJavaScript TutorialJavaScript Interview QuestionsJavaScript FrameworksAngular TutorialAngular Interview QuestionsWhat is REST API?React TutorialReact vs AngularjQuery TutorialNode TutorialReact Interview QuestionsVIEW ALL Mobile Development Android TutorialAndroid Interview QuestionsAndroid ArchitectureAndroid SQLite DatabaseProgramming Data Science... Researc h Analyst, Tech Enthusiast, Currently working on Azure IoT Data Science with previous experience in Data Analytics Business Intelligence. Bookmark 2 / 2 Blog from Data Manipulation in Tableau Become a Certified Professional In a world which generates and consumes 2.5 quintillion bytes of data, a day, organizations are bound to look for new methods to transform and combine data in order to attain optimum efficiency. One such method of combining data is Data Blending in Tableau.Now, because this serves such an important purpose in the data cycle of any given organization, it makes for a very essential module in most Tableau Training Curriculum. In this blog, we shall discuss the following concepts:Why Do You Need Data Blending in Tableau?What is Data Blending in Tableau?How is it different from Data Joining?When to substitute Joining for Blending?Blending your Data in TableauLimitations of Data Blending in TableauWhy do you need Data Blending in Tableau?Suppose, you are a Tableau Developer who has transactional data stored in Salesforce and quota data stored in Access. The data you want to combine is stored in different databases, and the granularity of the data captured in each table is different in the two data sources, so data blending is the best way to combine this data.Data blending is useful under the following conditions:You want to combine data from different databases that are not supported by cross-database joins.Cross-database joins do not support connections to cubes (for example, Oracle Essbase) or to some extract-only connections (for example, Google Analytics). In this case, set up individual data sources for the data you want to analyze, and then use data blending to combine the data sources on a single sheet.Data is at different levels of detail.Sometimes one data set captures data using various levels of detail i.e, greater or lesser granularity than the other data set.For example, suppose you are analyzing transactional data and quota da ta. Transactional data might capture all transactions. However, quota data might aggregate transactions at the quarter level. Because the transactional values are captured at different levels of detail in each data set, you should use data blending to combine the data.What is Data Blending in Tableau?Data Blending is a very powerful feature in Tableau. It is used when there is related data in multiple data sources, which you want to analyze together in a single view. Itis a method for combining data that supplements a table of data from one data source with columns of data from another data source.Usually, you use joins to perform this kind of data combining, but there are times, depending on factors like the type of data and its granularity, when its better to use data blending.How is it Different From Data Joining?Data blending simulates a traditional left join. The main difference between the two iswhenthe join is performed with respect to aggregation.Left joinWhen you use a left join to combine data, a query is sent to the database where the join is performed. Using a left join returns all rows from the left table and any rows from the right table that has a corresponding row match in the left table. The results of the join are then sent back to and aggregated by Tableau.For example, suppose you have the following tables. If the common columns areUser ID,a left join takes all the data from the left table, as well as all the data from the right table because each row has a corresponding row match in the left table.Data BlendingWhen you use data blending to combine data, a query is sent to the database for each data source that is used on the sheet. The results of the queries, including the aggregated data, are sent back and combined by Tableau. The view uses all rows from the primary data source, the left table, and the aggregated rows from the secondary data source, the right table, based on the dimension of the linking fields.You can change the linking fi eld or add more linking fields to include different or additional rows of data from the secondary data source in the blend, changing the aggregated values.For example, suppose you have the following tables. If the linking fields areUser IDin both the tables blending your data takes all of the data from the left table, and supplements the left table with the data from the right table. In this case, not all values can be a part of the resulting table because of the following:A row in the left table does not have a corresponding row match in the right table, as indicated by the null value.There are multiple corresponding values in the rows in the right table, as indicated by the asterisk (*).Suppose you have the same tables as above, but the secondary data source contains a new field calledFines. Again, if the linking field isUser ID, blending your data takes all of the data from the left table, and supplements it with data from the right table. In this case, you see the same null valu e and asterisks in the previous example in addition to the following:Because theFinesfield is a measure, you see the row values for theFinesfield aggregated before the data in the right table is combined with the data in the left table.As with the previous example, a row in the left table does not have the corresponding row for theFinesfield, as indicated by the second null value.When to Substitute Joining forBlending1. Data needs cleaning.If your tables do not match up with each other correctly after a join, set up data sources for each table, make any necessary customizations (that is, rename columns, change column data types, create groups, use calculations, etc.), and then use data blending to combine the data.2. Joins cause duplicate data.Duplicate data after a join is a symptom of data at different levels of detail. If you notice duplicate data, instead of creating a join, use data blending to blend on a common dimension instead.3. You have lots of data.Typically joins are rec ommended for combining data from the same database. Joins are handled by the database, which allows joins to leverage some of the databases native capabilities. However, if youre working with large sets of data, joins can put a strain on the database and significantly affect performance. In this case, data blending might help. Because Tableau handles combining the data after the data is aggregated, there are fewer data to combine. When there are fewer data to combine, generally, performance improves.Blending your Data in TableauYou can use data blending when you have data in separate data sources that you want to analyze together on a single sheet. Tableau has two inbuilt data sources namedSample-superstoreandSample coffee chain.mdbwhich will be used to illustrate data blending.Step 1:Connect to your data and set up the data sourcesConnect to a set of data and set up the data source on the data source page. An inbuilt data sourceSample coffee chain.mdb,which is an MS Access database file, will be used to illustrate data blending.Go toDataNew data source, connect to the second set of data.This example uses theSample Superstoredata source. Then set up the data source.Click on the sheet tab to start building your view.Step 2:Designate a primary data sourceDrag at least one field from your primary data source into the view to designate it as the primary data source. In theDatapane, click the data source that you want to designate as the primary data source. In this example,Sample coffee chainis selected.The following screenshot shows the different tables and joins available in the file.Step 3:Designate a secondary data sourceFields used in the view from data sources that are not the primary data source or active links automatically designate subsequent data sources as the secondary data source. In this case, Sample Superstore.Step 4:Blend DataNow you can integrate the data from both the sources based on a common dimension (State, in this case). Note that a small link image appears next to the dimension State. This indicates the common dimension between the two data sources.Suppose you create a bar chart with Profit Ratioin theColumn Shelf andStatein the Row Shelf, the chart shows how the profit ratio varies for each state in both the superstore and coffee chain shops.Limitations of Data Blending in TableauThere are some data blending limitations around non-additive aggregates, such as MEDIAN, and RAWSQLAGG.Data Blending compromises the speed of Query in high Granularity.When you try to sort by a calculated field that uses blended data, the calculated field is not listed in the Field drop-down list of the Sort dialog box.Cube data sources can only be used as the primary data source for blending data in Tableau. They cannot be used as secondary data sources.I hope you all,now, have a fairidea about Data Blending in Tableau from this blog. Hungry for more knowledge? Dont worry, this video will give you a better understanding of the concept.Re commended videos for you Introduction to Pentaho BI Watch Now Data Visualization-How to Make Sense of Data Watch Now Visual Analytics with Tableau Watch Now QlikView â€" Whats Your Business Question? Watch NowRecommended blogs for you What are Excel Pivot Tables and how to create them? Read Article What is the Tableau Server and its components? Read Article Functions in Tableau and How to Use Them Read Article Power BI Architecture: How to work on Data Security Read Article Everything About Different Ways To Use Dual Axis Charts in Tableau Read Article Do Magic With Tableau! Read Article What are Sets in Tableau And How To Create Them Read Article What is VLOOKUP in Excel and How to use it? Read Article Tableau Developer Salary : Everything You Need To Know Read Article How Much Would You Make As A Tableau Developer? Read Article What are Formulas and Functions in Excel and How to Use them? Read Article Power BI Developer Salary: Insights and Trends you Need to Know Read Article Qli kView Tutorial: Understand The Power Of QlikViews Click-Visualization Read Article Why Should You Blend When You Can Already Join In Tableau? Read Article Tableau vs QlikView Which Data Visualization Tool To Choose? Read Article Needs and Benefits of Data Visualization Read Article 5 Reasons to Learn Tableau A Deep Dive into Tableau Career Opportunities! Read Article Introduction to Pentaho Data Integration Read Article Introduction to Microsoft BI Read Article Top 5 Business Intelligence Tools Read Article Comments 0 Comments Trending Courses in BI and Visualization Tableau Training and Certification26k Enrolled LearnersWeekend/WeekdayLive Class Reviews 5 (10300)