Part III: The Evolutionary Path To a High Value Low Cost BI Solution

In part II of this series, Some Words You Need to Know,  I explained some of the basic terminology required to understand the concepts which will be discussed in later blog posts for this series.

Tableau software is a data visualization tool which is very adept at providing ad hoc analysis of data contained in an Excel spreadsheet, an Access Database and many other enterprise-class database systems including Microsoft SQL Server, Oracle, DB2 and many others.

Charley Darwin had a brilliant observation, namely,  that species evolve following a non-random pattern trial and error.  Successful species adapt well to their changing environment.  Unsuccessful species die-off because they are unable to effectively adapt to a changing environment.

This concept of adaptability through non-random trail & error pays dividends when you apply it to the development of effective visual analytic systems.  When conscious intentionality is added the process, you can speed this along and a much quicker rate than Darwinian Natural Selection.

I advocate the use of spreadsheets to replicate how a real data warehouse stores data.  One doesn't have to be a database architect to develop an effective data storage solution using the spreadsheet which actually replicates how a how a data warehouse stores data.

By building a fully functional reporting system in Tableau you can validate your granular data, generate reports very quickly and build the skills and knowledge you need in order to implement a full enterprise-class data warehouse at the lowest possible cost.

The Evolutionary Approach to High Value Low Cost BI

1. Model your data in Excel replicating how a database stores data.
2. Use Tableau Software to visualize the data in your spreadsheet.
3. Build reports by attaching Tableau to your Excel spreadsheet.
4. Correct granular data problems using Tableau to identify the issues.
5. Distribute clean reports within a few weeks which will be immediately useful to your information consumers.
6. Adapt the reports as required based on feedback from the users.
7. Develop an understanding of your data quality issues through regular loading of the spreadsheets.
8. Document the transformation steps you need to employ to clean your data.
9. Provide feedback to the appropriate managers in order to eliminate the reasons for bad data in your systems.
10. Contract a data architect after you've learned enough to provide that person with design criteria for a data warehouse and the specific criteria for cleaning the inbound data from your ERP system.

In my own personal experience I was able to get through steps (1 - 3) in under 2 weeks for the fist phase of my project which was to generate sales reports for use by management and field sales staff.

Steps (4 - 6) required another couple of weeks.  At that point, my information consumers thought the project was over, they were getting all the information that they desired using Tableau's free Reader product and the Tableau Visualizations that I designed with their direct input.

Steps (7 - 10) were accomplished behind the scenes within (5) months.  At the conclusion of the project we had a fully operational data warehouse with Tableau Software providing the same visualizations which the information consumers were already familiar with using on a daily basis.

It is important to realize that I was able to accomplished this in a fairly complex, medium-sized multinational company.  In most cases, you will probably be dealing with only (1) currency, (1) language and a single ERP system.

The actual data architecture we were working with changed very little when we migrated from Excel to SQL Server 2005 in steps (7 - 10).  The primary benefit was massively more historical data.

In Part IV of this series I'll explain the details of how to create a dimensionally-conformed spreadsheet which stores your data like a database.