Excelling at Dashbaords

SQL Server MVP Deep Dives 2, Chapter 58

Excel as a BI frontend tool – by Pedro Perfeito (Blog|Twitter)

No matter how good and sophisticated a BI tool is, business users will invariably export data to Excel. – Chapter 58, SQL Server MVP Deep Dives V2

In this chapter Pedro Perfeito presents an argument for using Excel as a dashbaording tool and then, very precisely,  steps through how to create the amazing spreadsheet pictured below.


The first half of the chapter is one of the best descriptions of what business intelligence means and how it should work that I have come across. He modifies Microsoft’s definition of BI slightly to read, “getting the right information, to the right decision makers, at the right time, at lower cost.

The lower cost is one of his primary arguments for using Excel as a BI front end. Many businesses already own a copy of Microsoft Office for all of their employees and many of them (particularly accountants for some reason) already use Excel to create simple graphs and data analysis. He also points out that Excel is not going away any time soon. Since Deep Dives 2 was published Microsoft has added even more BI and data analysis tools to Excel, making it clear that they are giving BI tools directly to the end user.

I liked the first half of the chapter because Pedro did not just focus on why the technology of Excel was a good choice, but looked at this from the business perspective. He has several reminders in this section for BI consultants about the business side of the project. The closer the understanding between the person designing the dashboard and the person using it, the better the end result. As technologists we can create amazing products, but if they are not useful then the simple bar graph that Bob in accounting hacked out for his boss will be the one that everyone keeps using.

The second half of this chapter was very detailed steps on how to create the exact dashboard pictured above – if you have an OLAP cube that matches Pedro’s design. He does give us the design for his cube and all the information needed to create his example is there, but I didn’t wish to take that much time. It would have been nice to have some files to download to quickly get to the Excel part of his example. I admit that I try to avoid using Adventureworks, but most of my examples are very simple. I can now see the appeal of using a data set that most of the readers already have or know where to get. If he had used Adventureworks as his data source I probably would have stepped through the dashboard creation and learned more about developing in Excel.

Also for his example he makes the assumption that the data is handed to the user by the IT team in an OLAP cube. If you have the cube this works for his example, but I think this piece of BI is being owned more and more by the front end users and Microsoft is adding the tools in Excel for them to do it.

Chapter Fifty-Eight SQL Server MVP:

Pedro Perfeito (Blog|Twitter) was born in 1977 in Portugal and currently works as a BI consultant at Novabase. He’s also an invited teacher in master and postgraduate BI degree programs, by IUL-ISCTE (Lisbon) and UPT (Porto), respectively. He has received the Microsoft Most Valuable Professional (MVP) award for his dedication and contribution to theoretical and practical issues in the various BI communities.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s