Wednesday 25 November 2009

What’s Tableau Reader?

 

Tableau reader is a free desktop application provided by Tableau.  It is required to read and interact with Tableau Packaged Workbooks.

You can download your copy of Tableau Reader here.

Tableau reader retains the ability to interact with visualisations created in Tableau desktop, but will not allow connections to data which can be refreshed.  Hence it is useful for reporting data when a snapshot of the data can be created – such as end of month reporting to your clients for example.

To create packaged files which can be distributed to users of Tableau Reader, you simply use ‘Save As’ from Tableau Desktop – and select ‘Packaged Workbook’:

reader

It is only possible save files as packaged workbooks if the data source chosen is suitable.  This means one of the following:

  • Excel
  • CSV
  • Tableau data extract

You cannot use multi dimensional sources (analysis services for example). 

I’ll post another time on using extracts – but that’s the way to make packaged workbooks when you are using data sources such as SQL server.

Good luck

Tom

Monday 23 November 2009

Did you know you can copy and paste data into Tableau?

 

A new feature in Tableau 5.0 desktop allows you to cut and paste data direct into Tableau for the fastest possible analysis.

Simply find some data, from a spreadsheet, or table in a web page – and copy the data.

Tableau then offers a paste feature on the EDIT menu:

paste

You’ll probably want to clear the sheet immediately after pasting as Tableau typically predicts the visualisation you’d like, and unusually for Tableau – it tends to get it wrong.

Clear the sheet using the toolbar button:

clear

Happy pasting

Tom

Where is my data? How to quickly determine where the important data lives in a SQL server database.

 

Every time I start analysing a database, I want to know which are the key tables.  Typically this means knowing which tables have the most records in them – this is simple to find out, and if you then visualise the data, its possible to immediately see how the data is clustered throughout your database.

How to???  You should be able to do this in 2 minutes…

Start by running this query against your database – in SQL server:

select * from INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE <> 'VIEW'

order by table_name

This gives the following result from Microsoft’s Dynamics CRM database:

query

Now copy the contents of the table name column, and paste into the first column of an Excel spreadsheet.

Then add the following function to column B of the same spreadsheet, and fill the cells next to the table names by dragging the function down the Excel column.

=CONCATENATE("select '",A1,"' AS TableName ,count(*) AS RecordCount from ", A1, " UNION")

Your EXCEL sheet should now look something like this:

excel

Now copy the contents of column B back to SQL management studio – removing the UNION keyword from the last statement and hit F5.

This should provide a list of tables with their record counts, like this:

result

You might choose to stop here, but I like to cut and paste the data into Tableau, and then use a horizontal ranked bar chart to really see where the data is – you should end up with something like this:

tableau

Enjoy

Tom.

Sunday 22 November 2009

Using UK postcode data in Tableau

 

The Tableau data visualisation product produces geographic data visualisations in seconds if you are looking at data which includes references to US locations such as states, cities or zip codes.  but this is not the case for the rest of the world.  I guess to do so would have involved a lot of localisation for Tableau and probably added 100GB to the download size of the Tableau product.

I’m regularly asked how to plot data on Tableau map based visualisations using UK postcode data (or other geographical data for that matter).  Its a simple solution, and one that works the world over. 

You simply have to translate your geographic data into Latitude and Longitude information, and then Tableau will happily plot the information on a map of the UK. 

I have created two files to explain this – firstly an EXCEL spreadsheet which contains sample sales data – this includes postcode data, and a VLOOKUP is used to link the postcode and the lat/long on the second sheet.  Note that the match is made only on the first part of the postcode (the outcode) thus giving a lower level of resolution than you would get from a full postcode match.  This is done as there are 17 million postcodes, and EXCEL would struggle.

GET EXCEL FILE

I have also create a Tableau workbook which shows this in action.

GET TABLEAU WORKBOOK

If you get this right, you’ll end up with something like this:

postcode

Best of luck.

Tom