Project Botticelli

How to Make Enterprise Dashboards in Excel Get Free Access Purchase this course

4 September 2013 · 4 comments · 53689 views

Modern Dashboarding in 30 Minutes

Carmel Shows a Finished Enterprise Dashboard Made in Excel

Let Carmel Gunn show you how to build a fully fledged, good-looking enterprise dashboard in Excel—in under 30 minutes. Log-in or get a free account to watch her show you Excel as an enterprise-grade BI client, in the way she uses it for her clients at Prodata SQL Centre of Excellence in Dublin, a company specialising in enterprise business intelligence.

First, you need to connect to an enterprise BI data source, such as a multidimensional cube, or a tabular model. Once a connection has been made, you are ready to create a dashboard in Excel 2013+ and to visualise data in an attractive way, for example by using recommended chart types. Conditional formatting of dashboard tables enables quick visual interpretation of business data in a way that draws attention to what one needs to know. There are a few shortcuts, like cutting and pasting pivot tables to create a new report part, that can speed up the creation of different reports from the same data source, by bypassing the requirement to connect the new report part to the source.

A powerful feature of Excel are user-defined MDX calculated measures. They let you add finer levels of business value to a dashboard, by allowing you to create your own bespoke calculations. This way, you can enhance the functionality of the underlying data model. After you have added slicers and timelines to a dashboard, they need to be linked to all the data elements on the report via report connections, to enable them to filter different report parts simultaneously, and to filter multiple items at the same time. This is a very important feature for building anything but trivial dashboards.

Overlaying multiple data sets on the same chart is a frequent reporting request. When carefully planned, adding a secondary chart axis lets you show different units, or scales, on the same chart, while still keeping the chart meaningful. Be careful, however, of mixing different units of measurement, as this technique might sometimes mislead the viewer, if, for example, different lines cross each other, yet the crossing points only depend on the choice of a scale, rather than on the data itself.

As the dashboard is nearing completion, Carmel shows you how it can be used for interactive data exploration. While exploring, you may want to further adjust the formatting, including making improvements to any secondary axes.

When you have created a dashboard, you now have your own, personal BI! In order to share it with others, and to promote it to the status of team BI, or perhaps to company-wide enterprise BI, you simply need to save it to SharePoint. Carmel shows you how easy it is with SharePoint Server. As soon as the dashboard appears in the report gallery, your team is ready to use it. Don’t miss the new Quick Explore feature of Excel which allows you to drill data using different dimensions to what the dashboard initially shows. This important, new functionality works the same way in Excel and through the browser, also referred to as the thin client, thanks to SharePoint.

One of the most demanded BI features in any company is making reports and dashboards look good on mobile devices, such as iPads, iPhones, Android and Surface. Again, all you need is the combination of Excel and SharePoint—Carmel shows how to be selective as to which parts of our newly created dashboard should be made available on mobile devices, and then she shows you how to save them, making them instantly mobile, whilst still maintaining the ability to interact with, and modify the dashboard.

If you would like to follow Carmel’s steps on your own, you will need to connect to a suitable BI data model. As this course is aimed at the end-user, you may want to ask your IT department to give you access to such a cube, like the well-know sample cube of Adventure Works. You can also use a Power Pivot with an Adventure Works tabular model for your learning, or, if you have additional IT skills and access to SQL Server Analysis Services (get a trial from here, or load SQL Server for BI from the Azure Gallery), consider loading the same data set as used by Carmel—you can download the Adventure Works cube from Codeplex.

All of the concepts presented in this video are simple to implement, and should help you build dashboards for yourself and for your organisation. If you would like to become more efficient, make sure to watch Carmel’s Excel BI: Basic Concepts video, and the remaining ones in this series.

 

Log in or register for free to access this content.

Purchase This Course or Full Access Subscription

Single Course

$200/once

Access this course for its lifetime*.
Purchase
Subscription Best Value

$480/year

Access all content on this site for 1 year.
Purchase
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.

Comments

tvburke · 14 March 2014

Hi regarding demo "How to Make Enterprise Dashboards in Excel" , what I noticed that your datasource is local , what I like to do is have a database shared by all SharePoint users that they can update any time, live , lets assume its a ticket report or task db. I like to see how many task opened how many closed of particular month , as well as historically, and other pertinent data like duration of task etc... Do I need to have Performance Point running in order to make above happen or just the Excel BI that you demo.

Rafal Lukawiecki · 25 June 2014

The data source in this video, a multidimensional cube, was running on SQL Server Analysis Services installed locally for the purposes of the demo. Normally, however, it would be a shared cube, or a shared tabular model, running on a server accessible to all the participating users. There is no need for PerformancePoint Services in this scenario, indeed, you could even do it without SharePoint. However, there are other ways how you could share such data in your company, which include using BI Semantic Model Connection Files in SharePoint (see this video around minute 56:34) or sharing them as queries using Power Query and Power BI, or just as Excel workbooks with Power Pivot. The right choice would highly depend on your use vs update scenario, user base, security concerns, audit and compliance control needs.

ness · 24 July 2014

Thank you for this demo very helpful and informative!

I have question:
Most of our reports were developed using SSRS and our sources are cubes and relational dbase.

But some of our clients want to have the look and feel of excel. One of the problem in SSRS is that we don't have the control to fit-in all the graphs and tables in one excel page when exported to excel it will most likely create additional pages especially if you have 8 pie charts and one table in each pie underneath it that have about 9 rows and 5 columns. This is hard to fit in in excel when the report is develop in SSRS.

The original report was created in excel and what our Business Analyst doing is copy & paste the data from ProClarity and put it on a separate sheet (that is hidden) and use lookup to feed in those data in the pie charts and tables. She does this every period like once in every 2 weeks and she does it for 6 different Region.

In this report each pie is coming from different cubes and one pie is an aggregated of all 4 cubes.

What you think is the best way to do it?

I am thinking of automating it using powerpivot and pivot table in Excel. I am not that good in excel since I am more of an SSRS guy. So this video is really helpful for me. Thanks for sharing!

Any thought will much be appreciated.

Thank you and Great Job!

Carmel Gunn · 26 July 2014

Hi Ness,
This is a very common scenario we see. Eg extracting data from multiple sources and “mashing” it up in excel – which can be very time consuming on reporting and get very complex with lots of lookups and macros.
There are two approaches we use to streamline this
1) Rather than have four different cubes, create a consolidated cube that contains measures from all the sources. Maybe this consolidated cube could be more aggregated than the other cubes.
2) Use a mashup tool such as the PowerPivot Add in to import and join the four sources of data. The business now has a single conformed in memory model to make reports in excel and either refresh data on the desktop in excel or from SharePoint.

Rather than use ProClarity to generate dumps of data we can either use the BI client directly in excel like in my video here, or PowerPivot allows us to directly import MDX queries.
Some users complain that excel does not have the flexibility of ProClarity. This is partially addressed by using the new features in Excel 2013 like calculated members and measures and by using add in’s that extend excel like the OLAP PivotTables extension on codeplex.
http://olappivottableextend.codeplex.com/
Should you build a consolidated MOLAP cube or use a mashup tool ? This really depends on a few factors such as:
- How wide is the distribution? MOLAP cubes are great for publishing to the enterprise whereas a mashup tool is great for extracting and joining existing data to make new models.
- Are data requirements static or dynamic? PowerPivot is much better at prototyping.
- PowerPivot models are faster and more cost effective to develop than MOLAP cubes.

I'm delighted you like the video :)
Carmel

Online Courses