Do Tableau And MDS Make Strange Bedfellows?

January 24, 2014   //   Business Intelligence, , , , , ,

If you’ve ever spent time reading Tableau marketing material or went to a Tableau launch party you know that Tableau is all about ease of access to data! Nearly every data source you can think of has a native connector built right into the product. You’d be hard pressed to find a data source that Tableau isn’t able to integrate with either natively or via an ODBC driver. But there is a data problem that can’t be solved with another new connector. What’s a Tableau user to do if the data doesn’t exist, or a relationship is missing, or the data requires manipulation? You do have some fairly robust options within Tableau Desktop to make data corrections. Just last week I used that feature to fix some geography data right in Tableau Desktop. But what if those corrections need to get back to the source system? Or what if lots of users are fixing data quality right from Tableau? That’s certainly a data governance issue for many organizations to be concerned with don’t you think?

Enter SQL Server Master Data Services

Lately we seem to be finding lots of good use cases for our customers who are on SQL Server 2012 to leverage Master Data Services in many new and creative ways. What’s been particularly interesting is the usage of MDS to augment a Tableau project by providing those missing data sources or by enabling a missing relationship between multiple sources.

Tableau Master Data Services Spreadsheet Snapshot

So let’s talk about the missing data source problem first. Missing data is a classic Business Intelligence problem that needs to get solved. In the past, we would solve the missing data issue by creating a surrogate data source managed by some entity (user, team, or process). The data source oftentimes would be an Excel spreadsheet or in better situations, an actual table on SQL Server. The surrogate approach works functionally but there’s a huge downside due to the lack of process, governance, quality or all of the above. Now that Master Data Services has the Excel plugin, we can build models and entities to fill the missing data gap, users interact with the model via Excel, and business rules and MDS workflows ensure the quality of that data. In our BI demo environment at SWC, we use MDS to fill the data gap for campaign management. Our fictitious company didn’t have the money to pony up for a campaign management system so we built the campaign model in MDS. This approach works! If you were doing something like this in Excel or MS Access, you really need to think about a migration to MDS.

This leads me to the second problem we can solve for BI with MDS and that is the ability to manage and insert the missing relationships. Certainly there are plenty of examples where a savvy ETL developer can use logic to discover and infer the missing relationships in the data warehouse but there are situations where human intellect and decision making are necessary. If there are data sources you want to leverage in Tableau but lack the ability to blend them to the desired level of granularity, MDS could be the answer. On several projects we’ve wrestled with these data relationship problems and found the creation of a mapping entity in MDS was the cleanest, surest way to get the problem solved successfully.

Because we’re using MDS, not only do we benefit by having the missing relationship filled in but we also have a complete audit trail see what happened:

  1. Who setup the relationship?
  2. When was the relationship established?
  3. How many change iterations have occurred?
  4. Is the record valid based on business rules established by the business?

And there are many more benefits.

So back to Tableau. Many Tableau users love the ease of use of the tool and the ability to quickly bring ideas to life or answer questions with a compelling visualization. In situations where the data doesn’t exist, is incomplete, or is not blend-able via the Tableau data blending feature, we now have another data weapon at our disposal that can quickly be put into place to solve issues on the model side just like Tableau does on the visualization side.

Click on the image below to watch Chad’s demonstration of how Tableau and Microsoft Master Data Services work together.
SWC Blog Video Player
In this project (and in many others we have completed), the success of the Tableau dashboard we built was made possible by having a tool like Master Data Services to fill in some of the data gaps or incorrect data coming from source systems. In this demo we are using a fictitious company on Yammer to analyze data from social media using an algorithm to score social media updates as positive, negative or neutral. Using this tool, we can better understand the social sentiment of our product and company on various social media sites such as Facebook and Twitter.

To learn more about BI and all of the exciting new features SWC has for the business intelligence community; please join us for our next informative Business Intelligence event.

If you enjoyed this post from Chad, please check out a few of our past posts on Tableau and business intelligence:

If They Only Had Tableau
Ask SWC: What’s So Great About Tableau?
Technology Meetups Make A Difference
My Search For The Business Intelligence Chupacabra
OAuth 2.0 – Google API Business Intelligence Implementation
An Agile Approach to Business Intelligence
How to Fast Track Business Intelligence
Can’t afford BI? Try the BI Analytics Tools in Everyday Software
How to Break Business Intelligence Users’ Excel Addiction
Ask SWC: What Is A New Technology That You Find Interesting?
Agile BI Software Solution
SWC’s Virtual Database Administration (VDBA) Solution