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!
Jen,
Nice article. Lu Sien Tan and I did Tableau's connectivity to PowerPivot last year and I think you did a nice job of pointing out where each product shines. If you have any questions before your talk in Amsterdam, shoot me a tweet (@austindahl) and I'll see if I can dig up some answers.
Austin Dahl
Jen,
how can I distribure report which I did in Tableau based on PowerPivot data?
My boss has got only Tableau Reader…
thank you