Tableau and PowerPivot – a subversive partnership?

From the Tableau perspective, why is PowerPivot a credible data source? Since PowerPivot is a ‘user-oriented’ tool rather than an IT-oriented tool, it is possible that its complexity and offering can be overlooked. PowerPivot fits well with Tableau, which is also aimed at the business user audience. This blog offers a preview of some of the topics that I will cover at the Tableau European Customer Conference 2011, which is being held in Amsterdam. The focus of the blog is to explore how PowerPivot and Tableau can work together for business users, rather than in competition.

What is PowerPivot? PowerPivot is a database in its own right. The technical detail is that SQL Server Analysis Services Vertipaq processor which lives inside Excel. PowerPivot comes in two flavours. The first type is PowerPivot for Excel, which is a free download which allows the user to import millions of rows of data into Excel. The second, and more advanced type, is PowerPivot for Sharepoint, which adds advanced server configuration to PowerPivot. For example, if a user opens an Excel spreadsheet that contains PowerPivot data, then Sharepoint will direct the request to the Analysis Services instance that is best able to serve the request efficiently.

When you read the Microsoft literature, one key advantage in the ‘Benefits’ list is noted as its speed on providing results. I actually think that PowerPivot’s speed is important, but that the key advantage is that it is the PowerPivot user who sets the database up and mashes the data: not an IT expert. For me, this is where the real power of PowerPivot lies – in giving users a familiar interface, and the ability to configure the database how they like. PowerPivot is ‘accessible’ to the expert analyst, who may not reside in the IT department. 

Tableau and PowerPivot, together, is a potentially subversive partnership. PowerPivot is a game-changer because it places data, and data structures, into the hands of business users. Tableau is a game-changer because it places the visualisation of data back in the hands of business users. Tableau can connect to a number of different sources, such as SQL Server, Teradata, Oracle and so on. PowerPivot is unique amongst these data sources, however, because it is the user who sets up the PowerPivot database; not an IT person. However, that does not mean that it is ideal in every situation.

It is possible that PowerPivot can be underestimated in terms of its power because the presentation layer is in Excel. As Spiderman fans will know – ‘with great power, comes great responsibility’.  Since PowerPivot allows users to set up data models with a large amount of data, it is important to ensure that the PowerPivot data model is correctly set up. In my opinion, this isn’t a job for any Excel user; this is a detailed analyst job, a job for someone who really understands the data and how it should be linked together, along with a deep understanding of the business questions. In this way, I agree with Cindi Howson’s recent commentary on Self-Service Business Intelligence. One of Cindi’s insightful points is that user expectation around ‘self-service business intelligence’ needs to be managed appropriately; the power Excel users will need help to target themselves towards the most appropriate content. Those of you who have stared at at a Business Objects universe with hundreds of columns, or an Analysis Services cube with many metrics, will know what I mean!

Is PowerPivot different from Excel, and if so, how? In a nutshell, PowerPivot includes its own data model, whereas Excel does not. A data model is which is a way of organising data so that is aligned a data model can be defined as a structure of tables which are connected by links, or relationship. You may have heard the adage ‘Singing from the same hymn sheet’? Well, a data model provides a uniform format for different members of the project team to communicate properly about data. A good data model is the foundation for PowerPivot, and is a new introduction to Excel. So, PowerPivot is more than Excel on steroids. Instead, it is a way of creating a data model with minimal intervention of IT, since the expert business user can use PowerPivot to create a database, on their desktop or in Sharepoint, that they can easily use in Excel. In Excel, the user uses a dedicated PowerPivot window to import data and configure relationships. 

However, as indicated previously, the power PowerPivot users will need help to direct themselves. It is hoped, however, that, by placing the design back into the hands of the business users, that there will be some alleviation of the reliance on IT once the initial investment of time has been made. In PowerPivot, the user imports the tables of data from various sources, and sets up the relationships between them. PowerPivot offers a default wizard for configuring relationships, and the user can also manually creating and amending relationships. The power of PowerPivot is that it gives expert analysts the ability to create their own data models, in a familiar environment. However, this does not mean that it is a replacement for a data warehouse. Instead, it offers users the ability to augment existing data warehouses and data stores. PowerPivot users can even augment their existing PowerPivot model by using data which is not available in the data warehouse. Can you think how powerful this might be, in augmenting existing analyses?

Tableau can also import data from a variety of sources; so why not omit PowerPivot altogether, and go straight to Tableau? In terms of delivering Business Intelligence projects, I prefer a phased approach. So, Microsoft Business Intelligence and Sharepoint first – and then move onto the third party products, such as Tableau. The reason for this is that I prefer the ‘gently, gently, incrementally’ approach; so get the building blocks in first, and make sure everything is working – and then move onto the ‘Wow!’ factor of Tableau. This means that I can demonstrate success of previous modules, rather than work towards a project that takes months but the customer does not see any visible results. By putting in PowerPivot for Sharepoint, this gives me a number of advantages. Firstly, any data manipulations written in DAX (Data Analysis Expressions) are consumed by Tableau, so the opportunity for re-use upstream is very useful. Secondly, by using PowerPivot for Sharepoint, I have access to a number of integrated features such as the PowerPivot Gallery means that the business users can ‘mix and match’ PowerPivot elements, which could then be consumed by Tableau, or used in other Sharepoint features such as Workflows. Finally, this proffers high-grained control over the PowerPivot security elements which is then preserved in Tableau. By pushing the security upstream to PowerPivot and Sharepoint, security can be centrally managed in sharepoint. In other words, Sharepoint will take care of the security permissions when the request for data is made, rather than all of the permissions being set up in Sharepoint and then repeated in Tableau for each data source. 

Although Tableau and PowerPivot could be considered competing technologies, they could usefully be applied to work in tandem. PowerPivot can consume data from places where Tableau currently does not, such as RSS/Atom feeds, and use very complex calculations. Tableau, on the other hand, beats PowerPivot hands-down when it comes to visualising the data. Here is one of the sample PowerPivot data visualisations, which is provided by Microsoft in their sample PowerPivot workbooks, which are available from download here:

Sometimes, the seeing is believing! I won’t say what’s not right with this pie chart. I will look forward to reading the comments posted as part of this blog, and will collate them for a later blog. 

There are synergies, of course. Both Tableau and PowerPivot take data from the Windows Azure Marketplace, where businesses can access ‘data as a service’ from Microsoft via commercial data subscriptions. I have been playing with Azure, and I must say that I’m falling in love with it, and I can see its potential. For me, the key thing is that businesses don’t have to obtain and maintain this data themselves; instead, they can simply rent it from Microsoft as a manageable subscription rather than take care of the data in-house. 

To summarise, PowerPivot and Tableau are potentially very subversive technologies, in the hands of savvy business users. If business users can access the Marketplace data as well, then they will have access to very powerful analyses tools in PowerPivot, Tableau and Azure!

SQLBits: Follow-Up from Data Visualisation Sessions

I recently helped out at SQLBits by delivering a pre-conference day on Data Visualisation in SQL Server Reporting Services, a shorter one-hour session on Data Visualisation on Reporting Services, spoke briefly at two SQLBits sponsor sessions for Attunity ( blog | twitter ), and a ten-minute Lightning Talk on 3D graphs. So, I’ve been a busy girl, but it was great fun. Before we start, I’d like to thank the SQLBits Committee members for their support and hard work on putting on an outstanding SQL Server event for the community.
The purpose of this blog is to clear up some of the general questions that I received, provide a pointer to my slides, and to provide a forum for anyone to directly ask questions or provide comments or feedback on my sessions. 
Data Visualisation Precon and Abbreviated Version
For the Data Visualisation sessions, I had a great crowd at each session who asked some brilliant questions. More detailed questions will be answered over the course of my next blogs, since they require more detail than I’ve been able to provide here. In the meantime, you can download my slides from the SlideShare website. 
Every Friday at 1pm EST – that’s 6pm for us here in the UK – Howard Dresner ( blog | twitter ) and the BIWisdom team use Twitter in order to chair a discussion on business intelligence topics. The team throw out a question every Friday, and then the Business Intelligence twitterati join in. For example, the session on 15th April 2011 focused on SaaS and its future in Business Intelligence. For example, is it at tipping point? If not, when is the tipping point reached?
If you’re interested in joining in, or just lurking on the conversation, the hashtag is #BIWisdom. For me, this is the highlight of my Twitter week and I learn so much from my peers since the conversation is extremely thought-provoking.
This week, the main contributors were Howard Dresner ( site | Twitter ) and the BI Wisdom ( Twitter ) team, along with Flying Binary (site | twitter ), and Warren Hart (site | twitter).
Book References
I thought it worthwhile noting some useful book references to support my commentary on data visualisation. I hope that these links are useful as a starting point for people who would like more information.
Stephen Few References
Any book by Stephen Few is an excellent, informative discussion on data visualisation. I usually recommend ‘Now You See It‘ because I believe it is well-written and instructive. Best of all, it’s one of these books where you learn something, without realising that you’re learning. Stephen Few seems to have the knack for writing in such a way that the reader is intaking the information, without realising that they are learning. For me, that’s the art of a true teacher and it is my ambition to be able to write in the same way. 
I’d also recommend his blog for a great read. 
Edward Tufte References
Tufte’s book ‘The Visual Display of Quantitative Information‘ is another one of these books where the reader learns by stealth, reading and not realising that they are taking in complex information. This book covers good and bad examples of design, and cogently argues the case for good visualisation. 
Nathan Yau References
I love the Flowing Data site, and Nathan Yau’s book is being released in the UK in the near future. His book is entitled ‘Visualise This: The Flowing Data Guide to Design, Visualization and Statistics‘. I will be obtaining my copy as soon as it is released in July 2011!
SQLBits Feedback
I’m obsessed with my SQLBits speaker feedback; I like to visualise it, analyse it, and then work out what the data is telling me so I can improve for next time. So far, I’ve found kind comments on my Lightning Talk on ‘3D or not to 3D’ Data Visualisation, from Luke Merrett ( blog | twitter ), who summarised it nicely; I was particularly glad of this because it told me that I managed to get the main point across in the tight 10-minute slot.

I hope that helps!