What does the perfect ad-hoc reporting database look like?

 

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 data from ACT! using Tableau

 

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.

STEP 1 – Choose an entity from ACT to analyse, and setup ACT appropriately

Lets start with ‘Opportunities’.  Go to the opportunities view in ACT as shown here, and then right click the column headings.  Select ‘Customise Columns’.

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.

 

 

STEP 2 – Export the opportunity list from ACT! to EXCEL

Exporting the data is as simple as right clicking anywhere on the grid, and selecting the appropriate function.

 

 

 

 

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.

STEP 3 – Now we’re ready to analyse this data using Tableau!

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’

1. Connect to the spreadsheet using the ‘Connect to Data’ option
2. Highlight ‘Total’ and ‘Stage’ in the data window to the left (use the Ctrl key to highlight them both
3. Hit ‘Show Me’ and select ‘Aligned Bar’ as in the image below

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!

What’s next?

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 at hyper-speed – using Extracts to make your analysis fly

 

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!!

How to learn Tableau Desktop

 

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

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’:

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

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:

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

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:

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.