April 11, 2013

Preparing Data for Analysis With Tableau

When the data is properly organized, the users can concentrate on analyzing the data. There is no one “right” way for organizing the data. We discuss here several types of data configurations to enable rich analysis using Tableau. Each approach involves different levels of effort to prepare and access the data.

 

Configuration #1: Simple Export to Excel

This is the simplest method. Many systems enable exporting data to Excel. Excel then can be the source of the data for Tableau.

This method is ideal if the required measures and dimensions can be exported from the system in a simple tabular sheet.

Pros:

  • Simplicity

Cons:

  • The extract usually has to be manually re-generated and loaded into Excel
  • Additional manual effort is required if data has to modified after it is loaded into Excel

Tableau-dw-type5

Configuration #2: Single System (using SQL)

This method involves writing a SQL statement within Tableau for accessing the source system directly.

Pros:

  • Once the SQL query and data connection are set, this method requires no manual intervention

Cons:

  • Requires knowledge of SQL and the underlying database tables of the operational system
  • Requires security privileges for accessing the databaseTableau-dw-type4

 

 

Configuration #3: Excel Data Sources

This method involves data from multiple systems into multiple Excel files. Then data sources are defined for each of the Excel files and the reports’ measures and dimensions originate from more than one Excel file. If one is using this method, it is time to consider building a data warehouse or learning SQL.

Pros:

  • Can be set up by non-programmers. Requires knowledge of Excel formulas in the Data and Forumla ribbons.

Cons:

  • Becomes tedious to maintain if the Excel files have to be updated regularly
  • Inefficient when multiple people have to share the same data set (teams often duplicate the files)

Tableau-dw-type1

 Configuration #4: Querying Sources Systems Directly

This method involves accessing the data directly from the source systems. This can be accomplished by writing SQL statements or using custom connectors to the source systems. May involve the creation of specific database views that contain the data required for specific Tableau reports. Database views are like tables, and are usually created by querying multiple tables and storing the query results. Well designed views are simple to understand by business users and hide the complexities of complex table relationships.

Pros:

  • Once the SQL query and data connection are set, this method requires no manual intervention
  • With well-designed views, business users with little SQL knowledge (or some Microsoft Query knowlege) have access to even more data for analysis

Cons:

  • Requires knowledge of SQL and the underlying database tables of the operational system
  • Requires security privileges for accessing the database

Tableau-dw-type3

 

 

Configuration #5: Data Warehouse

A data warehouse is highly recommended for the flexibility that is required for ad-hoc data analysis by multiple users across the company. The need for a data warehouse primarily depends upon the complexity and diversity of the data sources. The extra effort required to build and maintain a data warehouse improves the reliability, currency and uniformity of the data. Data warehouses have many other advantages, and they can be built in phases as the need for data analysis evolves.

Pros:

  • Provides the maximum flexibility for analyzing measures with respect to many different dimensions
  • Fosters trust in the data
  • Enables the data analysts to spend time on analyzing data instead of preparing the data for analysis
  • Retains historical data and enables consistent reporting when changes are made to dimensions such as sales regions, product categories and chart of accounts

Cons:

  • Requires information technology skills to design and develop
  • Requires resources to maintain and ensure that the data remains trustworthy

Tableau-dw-type2