Creating a dashboard using excel services data in dashboard designer: Part 1 - Creating Your Excel Data Sources

Welcome to my series on creating a dashboard, using excel services data, all within the SharePoint 2010 dashboard designer.  This is the first in a series of posts that will discuss how you can take SharePoint 2010 PerformancePoint Services and combine it with Excel Services - both very comprehensive and robust features in the SharePoint 2010 Enterprise Services.

Some Background

Where I work, we are a very Microsoft centric operation.  Our infrastructure, software, data analytics and transactions are all Microsoft based.  As such, SharePoint fits into our current situation quite nicely.  We have an analytics team that does an excellent job on creating reports in Excel, connecting to data cubes and other data sources, and providing this data to various members of our enterprise.  The problem is that.. well.. it's all Excel based.  The analytics team provides varying degrees of changes to the reports each week, and have macro's scheduled to email the reports on a periodic basis.  It's not that this situation is "bad", it's just that it's... well.. 2012.. and we can certainly do better than that, right?

Of course we can!  That's why we're here after all, to take a good thing, add a pinch of Internet, a dash of SharePoint, and make it a little better :)  With the above points in mind...

Let's get started

(This article assumes that you have already configured and enabled PerformancePoint and Excel Services within your SharePoint 2010 environment.)

Before we even start with PerformancePoint services, we want to make sure that we know where our data is.. which is within our Excel workbook, but that's not specific enough.  Excel Services allows us to create named ranges, tables, and charts within Excel, provide names for those items, and consume them on the web.  This is what we want to identify in this step.. what data do you need access to?

Here's an example of a basic chart of information, just some sales figures for some regions.  Pretty simple stuff.  Notice the last column, Variance, this is a calculated column, dividing Last Years Totals over Total (example, H3/G3).  Be sure to include that calculated column here as well.

 

 

As I said, we want to expose this data.  The good news is that the data ranges are easy, the bad news.. the sparklines aren't supported in Excel Services :(  But that's OK, given the power of the web, we can drill down into that information.. more on that in another post within this series.
So how do we get at this data?  Simple: Provide a named range for your data ranges.  To do so, simply highlight the data you want GROUPED together as a data source, move the cursor to the Name Box, provide a name, and hit Enter.

 

 

As you can see, I typed the word GROUPED in caps above.  Why?  Because if we wanted to, we could have highlighted just the NE Region Row, or just the SW Row.. or just the sales quarters 1-4.  All we're doing here is defining our data sets.

Let's publish it

OK, so let's pretend that this is all the data we wanted to expose, easy enough, right?  Now, just save your workbook item's to SharePoint.  To do so, we can publish the Excel data to SharePoint - choosing only the item's we want to expose on our SharePoint web.  At this point, you just need a document library that is a trusted location for Excel Services (http://technet.microsoft.com/en-us/library/ff191194.aspx).  Since were working with PerformancePoint, I recommend you stash your Excel document within a document library within your PerformancePoint site (that I assume you have already created).

Once you have your trusted location, within Excel, choose File -> Save and Send.  The menu will show the option Save to SharePoint. 

We'll use this option to publish our Excel data, but before doing so.. we'll want to choose what data to expose first.  To do so, click the Publish Options button to the upper right:

You can choose everything here, if you wanted to.  Why wouldn't you?  Well, the items you choose here are BROWSABLE via Excel REST services, which makes each object available via a URL.  So if you had some sensitive data that you don't want your users browsing, because they happen to know the URL where your document is stored.. and they have access to read into that library.. then don't make that data web accessible.  In our case, we'll just choose our Sales Data, and click OK.

Now, click the "Browse For Location" button, click Save As, and enter the URL to your document library with enabled excel services.  Then just click Save, and if all works well, you should have a published Excel document within your SharePoint environment:

Let's use it

We have a published workbook at this point, now let's access the data.  Access your PerformancePoint site, and start the Dashboard Designer (or, if you have already used Dashboard Designer, just enter it into your Start Menu, since it's locally installed client software).
With Dashboard Designer open, within the Workspace Browser, right click Data Connections, and choose New Data Source:

When asked for a Data Source Template, choose Excel Services and click OK.  Now, fill in the value's presented to you.  For SharePoint site, enter the URL to the site where your document library is located, NOT the path to the document library itself.. you choose the library in a separate field.  After you enter the URL, click into the Document Library field, where a drop down list will allow you to choose the appropriate library within your site.  Then, choose your Excel Workbook within the workbook field, and finally, choose your data source.  Now, you can see why you may want to name different ranges within your worksheet, as they all become individually exposed data sources. 
Here's a sample of how my data source looks when all fields are complete:

With our data selected, the last thing we have to do is define our data, so it's usable within our dashboard.  While still within our data source, choose the View tab.  Here you can see your data source.. pretty helpful, eh?

 

As I said, we have to define the data.  No worries, each column just needs a name (for display), a unique name, a type, and an aggregation.  First, do yourself a favor and click "Preview Data", this will show you the data you have available.

 

Much better..  Now, for each column of your data set, edit the properties (to the right) to provide a name, unique name, type and aggregation. 

IMPORTANT: You're working with a data source.  As such, you need to define what your "rows" are within the data source.  By default, Dashboard Designer will attempt to choose what the rows are by indicating a column type of dimension on the first item in the list.. in the case of this article, that's correct.  But be aware of what the different column types are, as they'll play a role in your usage of this data at a later point.

Here you can see that for each column, Im providing a name and unique name (keeping them the same, no reason for me to get fancy), making sure the column type is a Fact, and choosing Aggregation as None, since the figure is a flat figure and I dont need that column data aggregated. 

When all was done, my data source looked like this:

 

Finally, choose the Properties tab, enter an appropriate name, and make sure you enter a Display Folder for your data source as well.  Dashboard Designer has a relatively painless approach to letting you create\switch folders, but this is a CRITICAL step, as when you continue to create data sources, KPI's, dashboard, scorecards and reports, your workspace can get cluttered pretty fast.

And that's it!  Now you have a data source to drill into within your PerformancePoint dashboard.  In our next article, I'll walk through creating metrics against this data.