I have now established The Information Lab – a Business Intelligence consultancy focussed on delivery of high value projects in days, not weeks or months.
This blog has therefore moved to www.theinformationlab.co.uk\blog
I look forward to seeing you there!
Tom
There is a really huge amount of data made available by the GLA’s Data Management and Analysis group (DMAG) relating to London boroughs. There is clearly much to learn about London in this data set, but I was surprised to find I could extend the length of my life by as much as 9 years – simply by moving from Greenwich to Kensington.
Actually this would be likely to extend the life of my children, as the data shown is based on birth dates, but still a real benefit.
Of course, you could argue that simply moving isn’t enough, and that there may be other factors involved in determining the length of life – but I have already thought of that, and I also have a plan to grow a floppy hair cut and buy some bright red jeans. I have it all covered.
Source – Office of National Statistics – the data used in these visualisations was sourced from this page
I have read a number of posts lately detailing approaches for handling mapping postcode data in the UK, so I thought I would write another one. I think this is the fastest way to achieve it.
To map postcodes in Tableau, you somehow need to get the postcode
converted to a latitude and longitude. There are many ways to do this, but I think this has the least steps.
Follow these steps to map the OUTCODE (the left hand side of a post code) to a Lat/Long. The assumption is that you already have a dataset which includes POST CODE
Minutes 1 and 2 – Add a csv file to your copy of Tableau desktop, this tells Tableau the Lat/Long of the outcodes
You’ll need a csv file of outcodes, so I have posted one here. Copy this file and save it.
Add it to tableau LIKE THIS… Choosing the csv file from your save location.
Minute 3 – Create a calculated field which grabs the outcode from the postcode. The function for the calculated field is something like this:
left([Postcode], find([Postcode], " ")-1)
This finds the space in the postcode, and returns the left portion which is equivalent to the outcode in the csv file.
Minute 4 – Change the ‘geographic role’ of your calculated field to be outcode. Outcode is now available as a geographic role because of the csv file added
Minute 5 – Now you should be able to plot the outcode against a measure. Select the outcode field, select ‘number of records’ (for example) and show me should now offer you a map. Hey pesto, postcode mapping!
Happy mapping.
Tom
So your boss asks you for a forecast – data driven of course, and he wants it in five minutes. When’s that project going to finish, how many staff are we going to employ in a years time, what will be the final cost of the office move program.
AND I expect your boss wants a chart showing the EXACT date, headcount, cost, whatever. AND he wants it in five minutes.
Don’t panic. Crank out Tableau, and your done.
Lets forecast headcount based on the numbers for the last 24 months or so… we’re looking for a prediction of the next 12 months, and we have 5 minutes to get it done…
Here’s a sample of our data set:
So, its simple enough to plot this out in Tableau, I’m simply going to cut and paste the data right in as its the fastest way…
The first visualisation of this data is simply the headcount plotted against the date, thus:
Nothing very interesting here right, and hardly a forecast.
But with a few simply changes, we can get some decent information.
Firstly, using the drop down from the date field, select ALL VALUES.
Remove all other dimensions from the column shelf:
And then add a trend line…
Now by changing the scale on the X axis, to extend the reach of the trend line, we can start to make simple data driven estimates of future headcount. In my example, I had to edit the Y axis too, as the headcount was growing way past the current data set. The result is as follows.
In order to reflect the increasing rate of change of headcount, I’ll edit the trend line as below.
This gives us the following chart, which is pretty close to the finished article:
But with a little formatting, and using point markers, we can polish the chart to look like this:
This example uses banded reference lines, point markers (which get a dynamic value by dragging the arrow end), and a line end marker to show the current headcount value.
Enjoy.
Tom
Can Yammer (or other enterprise microblogging tools) be used to replace some of the volume of email traffic sent within an organisation?
Why even try to do this? Email is awesome.
I guess if you think email is awesome, then this isn’t for you. But if, like I do, you share the opinion that private communication through email misses a HUGE opportunity to engage people, then read on!
Email is a CLOSED communication method. You choose who should read your thoughts and send them then email. This is the electronic equivalent of passing notes around at school. Imagine just how many notes you would have to pass around to keep everyone in the loop.
Microblogging tools can be used to facilitate OPEN communication. Questions and thoughts previously shared between small groups (unless you’re commonly using the dreaded ALL email) can now be seen by larger communities making connections previously unexplored.
There are other benefits of course:
There are so many use cases for Yammer, but I’ll stick to those which replace functionality normally reserved for email. I’ll also detail how to read yammer as this approach could dramatically increase the amount of yammer activity.
This is an easy one. Just post your thoughts to Yammer. If you need to post an attachment which is only relevant to the message (such as a picture) then attach it to the yam. Else, file the document on SharePoint (or similar) and include a link to the file.
Benefits?
Possibly the most common form of email – and the primary use case for Yammer. A very simple request, to one or two people. For example -
“Dave, have you got any experience with #Fuzzpot software, I need some notes for a proposal”
This is better handled as OPEN communication, and in Yammer this would be represented as:
@Dave - have you got any experience with #Fuzzpot software, I need some notes for a proposal”
This style of message will cause the message to be directed to Dave (by the use of the @ symbol) but will also display the message to anyone following the message author (and in the company feed)
Benefits?
Some communication should remain private (probably less than you think) – and of course this can be handled by Yammer.
TO:Dave What are you up to tonight?
Will send a private message to Dave which is not seen by others.
Benefits?
It’s very common to send an email to teams using distribution groups and of course, these groups can be replicated using Yammer. You can then send to these groups using both the @ and the to: syntax.
Benefits?
Imagine if everyone in your organisation started using Yammer in this way. Wouldn’t you have thousands of messages flying around and no time to read it all.
This is easy to handle – Twitter has 100 million users and solves this problem – it can’t be hard within a company.
Firstly you have to accept that Yammer provides a stream of information, it is very conversational in fact. In every conversation there will be things of interest, others less so – don’t panic if not everything is exactly relevant.
Try these approaches to get what you want…
Easy. Go into the Yammer web application, follow the link to members and check the FOLLOW ALL box (top right). Now your feed will contain all posts. When this gets too much for you, try one of the other approaches.
Don’t use follow all. Select the individuals you are interested and follow them only. You’ll still see messages which are directed at you via @ and to: but will not be overloaded with idle chatter.
Hey, you can follow groups too (if the group admin allows it)
You can also follow tags. For example, following #yammer will alert you when anyone mentions yammer (even someone you are not following)
That should do it for now! I hope this helps shoulder some of your email burden!
So you have Tableau or another reporting tool which can read from a live database? But your database has crazy field names, and loads of fields which are holding up your analysis.
What attributes does the perfect data source for Ad-hoc reporting have?
I came up with these – I’d love to know if you can think of more!
1. The data source should be well isolated in the database, so that users can find the correct data source immediately
2. There should be no requirement to create joins to other tables
3. The fields in the data set should be returned in an appropriate order – typically in order of importance, with some grouping determined by the purpose of the data
4. The field names should be semantically correct, using business terms and have a consistent naming convention
5. Fields should be of the correct type so they are interpreted correctly by the reporting tool (date, numbers strings etc)
6. The data source should be high performance (< 2 second response time)
7. Any geo spatial data should be at an appropriate level of zoom – particularly important for postcode data for example (outcode is most often appropriate, rather than the full postcode)
8. The data set should contain no ID fields, except for the primary entity in the data set (i.e. the list of properties SHOULD contain the PropertyID field, but no others)
9. The data set should have the minimum number of fields possible. It is better to handle requests for additional data items than to flood the data set with fields which are not used and confuse the end user)
10. Date fields should be complete dates, not part of dates like year or month. A good ad-hoc reporting tool will handle this easily.
Enjoy!! And please let mw know of any more!
Tom
Analysing CRM data is a great use of Tableau, and ACT from Sage is one of the most commonly used CRM applications. This posts hopes to explain how to get started analysing data from ACT! databases using Tableau.
This is a ‘getting started’ post! I intend to explain how to analyse data from ACT! which has first been extracted into Excel, rather than by making a direct connection to the database.
Lets start with ‘Opportunities’. Go to the opportunities view 
In the dialog presented, select all the available fields and move them to the list box on the right.
This will allow extraction of all the fields into an EXCEL spreadsheet which can later be analysed using Tableau.
NOTE – The EXCEL file created by ACT! has two additional sheets which cause problems with Tableau and must be deleted, delete the ‘Opportunities Pivot Chart’ sheet, and ‘Opportunities Pivot’ sheets.
SAVE the EXECL sheet.
This post is not intended to be a training course on Tableau however, there is another post here which should help. But here are some simple steps to get started analysing opportunities from ACT!
PIPELINE DISTRIBUTION by ‘Stage’
4. Swap the axis and rank the categories using the toolbar buttons in the image below
And there you have it, a ranked bar by sales stage of data from ACT!
This is obviously the first part of a longer story. There are endless ways to rapidly analyse data using Tableau, and many other entities in ACT! which can be analysed.
In addition, the next stage is also to connect Tableau directly into the ACT! database to remove the need for the EXCEL step – this will be the focus of a post for another day!
Tableau is so fast for development of data visualisations and ad-hoc analysis of data sets that any performance problems coming from your databases are immediately obvious and can take the edge of the promise of truly fast analysis.
No problem, Tableau thought of this too, and provided ‘Extracts’ to help solve this problem.
Extracts are ONLY available for relational or flat data sources, not for multi-dimensional sources such as Analysis Services.
Suppose you have a data set which is taking 10 seconds to respond – you will experience this 10 second delay every time you change a filter, move a dimension etc – anything you do will cause this 10 second wait (approximately!).
By taking an extract of your data, this delay almost always drops to almost zero, and you can fly through your analysis at light speed. Refreshing the data is a one click operation.
To create your first extract, simply use the data menu inside Tableau Desktop:
You are able to specify attributes of the extract such as filters, percentage of data set to extract, and Top n rows etc.
You will have to specify a local file location for the extract file – which of course means you are now able to work OFFLINE!!
Tableau is super easy to learn, but in the end, it doesn’t come for free. You’ll have to put a little effort in, but Tableau have also done a great job of making the trip up the learning curve an enjoyable and easy one.
Here’s how to get from beginner to Jedi in a few days.
STEP 1: Start by downloading the free trial, unless your boss has been kind enough to purchase the software for you.
Download from HERE.
STEP 2: Watch the product tour. This gives a great overview of the product in under 10 minutes and should leave you wanting to know more! You can find the product tour HERE.
STEP 3: Start using one of the data sources provided within the Tableau installation files – Coffee Store, or Superstore sales to produce visualisations. These data sources are relatively rich and contain interesting data which allow you to evaluate the mapping features for example.
STEP 4: Use the on-demand videos to continue your training – the top level index for these can be found HERE. These appear to be long (3 hours +), but in reality they are broken up into a series of short clips which are very goal orientated.
Start with the introductory videos, and move into advanced and Jedi topics.
Treat the Advanced and Jedi topics as reference manuals – you do not need to remember everything in these sections, just knowing that these sections exist is enough – come back to them when the need arises.
STEP 5: To achieve true Jedi status, you’ll need to work with many different visualisations and many different data sources. Tableau have done a great job of making this possible by providing a suite of examples. You can find them HERE.
These examples are ‘Packaged Workbooks’ which means both the data and the visualisations are included in the file. If you download these workbooks and open them into Tableau Desktop, you’ll be able to see how they were built.
I recommend trying to recreate each page (without using ‘Duplicate Sheet’!!) – if you can recreate them all (there are nearly 100), you can truly call yourself a member of the Tableau Jedi.
Cheers
Tom
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’:
It is only possible save files as packaged workbooks if the data source chosen is suitable. This means one of the following:
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
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:
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:
Happy pasting
Tom
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:
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:
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:
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:
Enjoy
Tom.
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.
I have also create a Tableau workbook which shows this in action.
If you get this right, you’ll end up with something like this:
Best of luck.
Tom
