Tuesdays with Tableau: Combining Data from Different Sources

By Gail Kluepfel

I think we can all agree, Tableau looks great in a demo with the sample data stored in Excel. But the true test of a self-service analytics tool is working with real data that may reside in more than a single Excel workbook. Thankfully, Tableau has two different methods of combining data to give the user flexibility: data joining and data blending. I want to address a few potential misunderstandings and highlight a few caveats to keep in mind when using either method.

Data Joining

When you want to work with multiple tables or views within a source (e.g., tables from the same database, sheets from an Excel workbook, text files within the same directory), Tableau works well when you join them. In most cases, performance will be much better than if you use blending. 

Using joins to combine data is easy to do and performs well in Tableau when the data resides in the same source. 

In Tableau, data joining is easy because you don’t need to know SQL. Simply drag and drop to integrate multiple tables and specify the columns to join on – choose the Data > Connect to Data menu option to get started. After connecting to the data source, drop a table onto the blank surface, followed by the table to be joined. Click on the set of interlocking circles which appears between the tables to open the Join window, where you select whether you want to use an inner, left, right, or full outer join. Then add the columns you want to join on. 

Learn about each join type in this excellent Tableau support resource.
(click image to view full size)

Creating joins at the data connection level is efficient: once you join data sources in a workbook, the final data source can easily be shared with Tableau Server, because data joining happens at the data connection level, not after a query is generated within a view. But even if two databases are on the same server, you won’t be able to join tables from each using this feature. For that, you’ll want data blending.

Data Blending

(Note: I won’t cover the basics of blending here; Tableau provides helpful resources to get you started.)

Two potential limitations: First, because data blending happens at time of query, after you’ve made data connections, performance can be slow, but in many cases this is still the best option. Second, you can’t blend two cubes.

Fortunately, you can blend a single cube with other data sources, and there are many other data sources that you can blend. To start, you can combine data from:

• two different databases (same or different servers),
• a database and an Excel file,
• a cube and a database or Excel file, or
• multiple files.

Primary vs. Secondary
Blending in Tableau requires you to designate one data source as Primary, the others as Secondary. You aren’t limited to two data sources – you can blend several – but only one can be Primary.

So what does the Primary designation mean? To those familiar with SQL queries, the primary source is like the “left” or first table in a left outer join. After blending, the results will include all rows in the Primary table but only those rows in the Secondary that match on at least one column you specify. If the Secondary source doesn’t have a match in the Primary source, it won’t be included in the results.

Say you want to blend sales amounts from orders in your CRM or ERP system with returns against each order stored in another system. Consider these two tables coming from different data sources: 

(click image to view full size)

Blend the Orders table with the Returns table, and the results will contain all orders for Xerox products but only include records from the Returns table with a matching Order ID (boxed in red), the only column these two tables have in common. This is expected “left join” behavior.

Tableau’s options for combining data are easy to use.

Joining and blending enable business users to query different data sources where they reside, rather than waiting for IT to replicate and centralize data through traditional development methods. While developers may not see Tableau as a robust dedicated integration tool like Microsoft’s SQL Server Integration Services or Alteryx, and technical analysts who use Excel’s Power Query may find Tableau less flexible, it does provide the easy-to-use graphical interfaces and high-performing capabilities that enable many business users to get the data they need. 

NEXT WEEK: Tips and tricks for working with Primary and Secondary data sources.

Tuesdays with Tableau: Why I Love Tableau Server

By Gail Kluepfel

These days, who doesn’t want to have a self-service business intelligence platform? Business users benefit from generating reports without having to wait for an IT resource or centralized reporting team to create them. IT teams benefit by being able to focus on the heavy lifting of developing and integrating data across core systems rather than getting randomized by users requesting reports. But “self-service” doesn’t translate to “hands-off” for IT – someone needs to refresh the data.

A case in point: I recently caught up with a client for whom we implemented a SharePoint-based, self-service Power BI solution. Three years in, the system was still working well – almost too well! His only complaint was that his primary business user had gone wild building Power Pivot models in Excel, rather than taking the time to work with IT to deploy the changes to the initial model we built on their Analysis Server as a tabular cube. Now their IT director reports a profusion of workbook models built off the original that must each be refreshed separately, with multiple jobs for the IT team to babysit.

Is there no end to such madness? Happily making the switch to using Tableau desktop, many of our current clients are delighted to find that Tableau Server provides a more efficient way for IT to help manage self-service BI. While much has been made of Tableau as a great visualization tool (it truly is!), I want to give equal time to how Tableau Server handles data refreshes. In short, Tableau Server efficiently manages access to data sources and provides a collaborative platform for reporting, but it also reduces the burden on data sources and IT by managing refresh automation.

How is Tableau Server more efficient when refreshing data?

In self-service tools like Excel, the data models built by business users (each with unique calculations, measures for grouping, and renaming of fields for specific business purposes) reside in many different workbooks, even though they all may be hitting the same data sources. By contrast, Tableau’s data model design is based on querying data sources at run time. This means that even when users across different functional areas build vastly different data models, and save them to different workbooks, the shared data sources can still be refreshed with a single job.

The architecture of Tableau makes it a much better platform to enable business self-service reporting without the headache of supporting refreshes of hundreds or thousands of variations on the same data sources.

Tableau's tiered architecture includes a super-fast data engine that enables direct queries to data sources, along with a Data Server component to store extracted data in reports. (click image to view full size)

Consider Sue, a user in Marketing. She creates a workbook connecting to a CRM database serving sales data and a web metrics database serving web traffic data. She creates custom measures and aggregations appropriate to her business reporting needs, then posts this to a SharePoint site, where her IT team has enabled a daily refresh of her underlying data. Sue’s colleague Tom, in Sales, connects to the same CRM database and creates a sales pipeline and forecasting report, posting a separate workbook on the same SharePoint site. Now IT has to run another refresh job.

This scenario is very different with Tableau Server. Whenever a user saves a workbook to Tableau Server, the data connections and extracts become part of the shared repository of data sources, and any workbook connecting to a given data source updates simultaneously. This is much easier for IT teams to manage than hundreds of individual workbooks with separate refresh jobs.

Tableau Server is a Data Server

Another benefit of extracting data from source systems is that individual users neither need access to nor query source systems directly. Tableau Server can extract the report data on an automated schedule, so that users only need to have permissions to the data on Tableau Server. And, because one data source extract can be used by many workbooks, you save on server space and processing time. As a recent Tableau white paper explains, “When a workbook using a Tableau Server data source is downloaded, the data extract stays on the server, resulting in less network traffic. Finally, if a database driver is required for a connection, you only have to install the driver once, on Tableau Server, instead of multiple times, on all your users’ desktops.”

Tableau Server supports all the self-service reporting capabilities business users could want, alongside the efficient data governance that IT teams need to prevent a “wild west” of competing data models and data refresh jobs. With such great features, Tableau Server really makes Tableau Desktop a great enterprise business intelligence platform, and not just a great visualization tool.

Tuesdays with Tableau: Why Tableau Is a Powerful Visualization Tool

By Gail Kluepfel

Like most data enthusiasts, I spent years mastering the advanced features of Excel and Power Pivot. I was skeptical of Tableau at first: why invest in yet another business intelligence tool? But my current mentor and manager, Paul Ausserer, is such a persuasive evangelist that I eventually gave Tableau a try. As soon as I did, I instantly converted.

To prevent myself from becoming the guest at a party who at first seems interesting but then goes on and on about her favorite pet, in this post I’m going to limit myself to one thing I love about Tableau and think potential users should know. But bookmark this blog, because each week I’ll be featuring a new reason to love Tableau in our new Tuesdays with Tableau series. Today, it’s all about visualization.

Put a Spotlight on Data that Matters with Color, Shape, and Size

A great visualization tool makes it easy to spot or highlight important data points so you can be super-efficient in gaining insight and avoid staring at a wall of numbers in tables. Tableau outshines the competition by allowing you to use color, size, and shape in an innovative way.

With minimal clicks, you can use color, shape, and size to see multiple data points in a single view and put a spotlight on what matters, to quickly see what’s going well and what’s not.

Tableau lets you use more than one data series to spotlight high and low performers in a single view. The bar chart below shows Sales by Customers, sorted in order of highest sales. I dropped the Profit data onto the Color mark, and, voila! I see not just customers who have the most sales, but also those with highest profits – just by looking at the gradation of colors from red to green. Suddenly it’s easy to see that some of the customers with lower sales are more profitable than customers with higher sales.

click to view full size

The ability to “color” the data series generating the chart (Sales) with another data series (Profit) clearly differentiates Tableau from other tools, like Excel.

In the scatter plot below, I combined color with size and shape to visually identify high and low performers. This shows profit versus sales by region and product category, with the size for each determined by the number of items that customers have ordered. I immediately see which of our top sellers have higher profits versus sales, and in which regions. If I hover on a data point, the tool tip tells me that one of our lower volume technology items performed poorly in the south.

click to view full size

Tableau lets me add as much information as I want to the tool tip, such as customer and product names. If I want more information on any point, I am only a click away from getting the full details from the underlying data. Here it shows the customer got enough of a discount (8%) to cut into profits on a low margin item.

click to view full size

From this view, I can take action and talk to the regional manager in the south to understand discounting policy for this customer. All of this information is visualized and available in one view, with just a few clicks. It’s efficient to create and easy to see what’s important.

Tableau Has Easy-to-Use Parameters!

I was going to end this post here, but I had to mention one other outstanding Tableau feature: the power to create any kind of parameter to add greater user interactivity and control over the visualization. In this case, I created a parameter for a Profit Margin Threshold, and set it up so that any sale resulting in profit margins lower than the threshold will be colored red, and higher than threshold will turn green. I can quickly change the threshold value to see changes in the visualization.

click to view full size

There are many other ways to use parameters, along with calculated fields, to enable dynamic and user-defined ways for working with data. You can expect to hear more each week at Tuesdays with Tableau, as we share the myriad ways Tableau accelerates your ability to see and understand data.

NEXT WEEK: Managing Data and Access to Tableau Workbooks with Tableau Server

Marquis Leadership Partners with Tableau Software

 

Marquis Leadership is thrilled to announce that we have entered into a strategic alliance partnership with Tableau Software, a recognized leader in data visualization and analytics software.  The partnership speaks to the good work we’ve done for customers where we introduced Tableau as the ideal solution for highly-visual, intuitive, and interactive business intelligence for all users, whether data is presented in executive dashboards, operational reporting, or sophisticated predictive models.

 

To help business leaders take action more quickly and confidently, Marquis Leadership leverages the Tableau interface on top of robust, custom data integration solutions.  We plan to continue to showcase Tableau as an excellent platform not just for stunning visualizations but also for building forecasting models, especially for purchasing and inventory management, where Marquis Leadership has deep experience working with Microsoft’s Dynamics AX and other ERP software applications. 

 

We look forward to working with the Tableau team to bring their solution to other clients, and through it, accelerate what leaders do. Best.