THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Alberto Ferrari

Self-Service BI: BI to Excel or Excel to BI?

I spend most of my time developing complex BI solutions and, doing that, I always talk with DBA, developers, CEO, data analysts and the many kind of people that work around a BI solution. Nevertheless, in these last months, publishing posts about PowerPivot and teaching the PowerPivot course around Europe, I am keeping in touch with the vast number of people who really use Excel to solve their daily analysis problems and, with no surprise, I am learning that I am very ignorant about Excel capabilities.

This last post of mine has received many interesting comments from Excel users and the previous one got some useful comments too. Many other posts are scheduled in the next weeks, so I think I will get more interesting feedbacks. Now, I don’t have any problem admitting that I am not an Excel guru and, probably, I will never become one. That said, it is clear that a good knowledge of PowerPivot (something I am supposed to own) is not enough to create a true PowerPivot solution. A very good knowledge of Excel is necessary too, to create all the complex functions needed to automate steps in the building of intermediate tables, reports and all the fancy features of a good Excel solution.

Thus, the question I am wondering about today is: will BI professionals need to learn all the insights of Excel (i.e. BI to Excel) or are Excel guru going to learn all the complexities of building a BI solution (i.e. Excel to BI)? The truth is probably somewhere in the middle. Nevertheless, I tend to prefer the second scenario, where Excel power users will learn PowerPivot and some fundamentals of BI development against the other one. The rationale behind this opinion of mine (apart from my laziness in learning Excel) is that it will be easier for Excel gurus to learn BI development than for BI professionals to learn how to leverage all of the Excel functionalities.

Excel power users already have a strong knowledge of their real world scenario of data and will not need to read the many books about data modeling to start playing with PowerPivot. Instead, working by trials and errors, they will create simple data models that will help them to create some first reports and, one step after the other, they will learn how to build a complete BI solution, at least for their specific needs. On the other hand, whenever I try to write some VBA code to automate a process in Excel, I am lost in the vastness of its features and functions and soon spend more time trying to write the code, while my time is better spent in data modeling. Thus, I believe that the Self-Service BI road will be full of Excel people learning the basics of BI, with very few BI professionals learning Excel. But… my opinion might have been guided by the fact that I find BI easier, since I already know the topic. Sorriso

Nevertheless, if you are serious about introducing PowerPivot technology in your company, keep in mind that you will need to have both strong Excel and PowerPivot skills. Excel can no longer be considered simply a “BI client”. In the PowerPivot era, Excel is becoming more and more an active part of the BI solution with all of its programmability and functionalities.

Published Monday, January 24, 2011 12:51 PM by AlbertoFerrari



Bob Phillips said:

Hi Alberto,

As one who commented on both of those posts, it seems appropriate to comment on this one as well.

I think you are right, as I said in that last post, people moveing to PowerPivot from 'traditional' BI will need to sharpen up their Excel skills. But to put it bluntly, I think you should have done it before PowerPivot. In reality, Excel is not 'simply a BI client' for many users, Excel is their BI. Even pulling data from SQL Server tables, I know far more companies that will create pivots, tables, dashboards in Excel than I do that use SSAS and SSRS or SharePoint (indeed, many will not have licenses for SSAS or SharePoint).

But PowerPivot does give those Excel users an exciting new opportunity. Unortunately,it is not as easy as MS like to make out (it neveer is). These Excel power users will need to learn some of the fundamentals (I was very pleased to see Marco and yourself giving data modelling a big push in your book, this is a key concept IMO), and move towards the BI development paradigm.

The one thing that we do need, and need desperately, is VBA access to the PP OM. We need to be able to refresh it from  the workbook, we need to be able to build PP models, and so on.

January 24, 2011 6:15 AM

AlbertoFerrari said:


I got used to think at Excel as the "final stage" of a BI solution. In that sense, I never had the real need to create a very complex Excel workbook, since my task is that of providing data to Excel users, who then report on it with the PivotTable or any other means.

The users you speak about, who leverage Excel to solve all of their problems, are the ones I don't know (if they don't have an SSAS license, why should they know about my very existence?).

Now, in the Self Service world, Excel has become one of the building blocks of the BI solution. It is not only at the end, it can be the first step, in the middle or just at the end, probably being everywhere at the same time :).

PowerPivot seems to be the first place where Excel and the BI really marry and I am eager to see how Excel users will leverage PowerPivot. That said... I promise I'll refresh my Excel skills; since then... I trust careful readers like you to correct my mistakes. :)

January 24, 2011 8:32 AM

Bob Phillips said:

Hi Alberto,

I can assure that Excel and BI married long, long before PowerPivot. I heard a quote recently, where someone said something along the lines of, '... I have had self-service BI for years, grab the data, put it into Excel ...'

I have been delivering BI via Excel for 15 years now. Indeed, it is only in the past 3 years or so where I thought SQL was really good enough to think of something other than Excel for BI.

Anyway, I will do my best to help you develop your Excel skills. I hope that you enjoy it as much as I am enjoying learning the SQL products :-).

January 24, 2011 12:02 PM

greg kramer said:

In the 1970s and 1980s, a series of commercials was run for Reese's Peanut Butter Cups featuring situations in which two people, one eating peanut butter and one eating chocolate, collided. One person would exclaim, "You got your peanut butter in my chocolate!" and the other would exclaim, "You got your chocolate in my peanut butter!".[6] They would then sample the mixture and remark on the great taste, tying in with the slogan "Two great tastes that taste great together."

January 31, 2011 4:36 PM

Wojciech G. said:

Why "BI to Excel or Excel to BI"?

Excel is the only BI

November 14, 2011 9:02 AM
New Comments to this post are disabled

About AlbertoFerrari

Alberto Ferrari is a Business Intelligence consultant. He his interests lie in two main areas: BI development lifecycle methodologies and performance tuning of ETL and SQL code. His main activities are with SSIS and SSAS for the banking, manufacturing and statistical sectors. He is also a speaker in international conferences like European PASS Conference and PASS Summit.
Privacy Statement