This post really stems from another blog where I referred to Excel as a BI tool, the most used BI tool in the world in fact. I’m certainly not the first person to make this statement, and I won’t be the last.
But is Excel a BI tool? In fact what makes a BI tool a BI tool?
To answer this we probably first need to recognise that a BI solution is made up of a number of integrated parts, the part that:
- connects to the data
- transforms the data
- models the data
- presents the data in a visual and interactive form
- presents the data in detail for deeper dive analytical purposes
- allows the above plus insights and ideas to be shared with others
We could also regard the systems or databases in which the data resides as part of the BI solution, particularly if the data is stored in a dimensionally modeled relational data warehouse.
So anything we regard as a BI tool needs to perform one or more of these (lets call them roles rather than parts from now on). However, we should probably be specific about which roles a particular tool performs.
So back to Excel! What I refer to as Core Excel (spreadsheets, pivot tables and pivot charts) certainly meet 4 and 5, and in that regard Excel has to be classified as a BI tool. However it doesn’t perform the remaining 4 roles (certainly not as well as we’d like).
But when we include the Power Query and Power Pivot add-ins for Excel in the equation Excel now meets 1, 2, 3, 4 and 5. Power View for Excel also enhances Excel’s ability to perform role 4. So with these add-ins Excel is actually a pretty comprehensive BI tool. I’ve successfully used Excel and its “Power” add-ins to deliver a number of BI solutions now, and it works! particularly for small to medium size data sets.
With its Power Query, Power Pivot and Power View equivalents the same can be said of Power BI. However Power BI also gives us 6 (for the most part in the form of the Power BI cloud service portal). In this regard Power BI can be regarded as a more complete or comprehensive BI tool than Excel. Power BI is also better than Excel at handling larger data sets.
The few Excel and Power BI short comings relating to role 6 can be over come by using the extended features of Office 365 and/or SharePoint. The Meson BI Portal offered by Cloud2 is an example of this.
So what about other BI tools such as Business Objects, QlikView, Tableau etc?
I have good working knowledge of Business Objects from a few years ago, and I know that back then BO gave us 1, 3, 4 and to a point 6; However it wasn’t so hot in terms of data transformation or presenting data for deep dive analytical purposes (in fact the users I worked with used the export to Excel feature for the latter).
I have little working knowledge or many of the other mainstream BI tools that are out there, so I’d certainly be interested to hear from you on these. Does QlikView, Tableau…..etc. perform all 6 roles, or just some of them?
I avoided talking about SQL Server in the above, so as to avoid complicating and confusing things; However in a Microsoft BI / Big Data scenario we’d probably look to use SQL Server and SQL Cubes as our backend solution (parts 1 to 3). Excel, the “Power” add-ins for Excel and Power BI can all be connected to SQL databases and SQL cubes – to again give us the front end parts of the solution.