Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Posts Tagged ‘excel services’

SQL Saturday Event Analysis using Power View and Office 2013

Posted by denglishbi on August 6, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

Updated (8/7/2012): Added screenshot at end of post of which includes data broken out by fiscal calendar (starts in July) and link to that file as well.

Updated (8/9/2012): Added some information at the bottom of the post in regards to downloading issues and more information available in the comments section.

Last week I saw a post by Sam Vanga called SQL Saturday US Map.  Needless to say I found it interesting and a great idea.  That made me think a bit and I was like hmm… why not take that same data and use it with the new Excel 2013 with PowerPivot, Power View, and maybe even a little Office 365!  I had to leverage the Microsoft stack of course:)

So I went out to the SQL Saturday site and copied down all of the event details. Needless to say a little scrubbing was required because the data was a bit incomplete to get at some of the location information.  After a little while I felt fairly good with the data and loaded the information into PowerPivot for Excel 2013.  I added a few more columns around the date information to generate a calendar and to do some sorting.  Once that was done I went ahead and started to put together a little dashboard page.  I ended up with the following:


We can see that the SQL Saturday events over the past couple of years have really grown in popularity and after 2010 they went global as well.


We can zoom in on the map and get a close up as well.  Last year we had the SQL Saturday #99 in Eden Prairie, MN.


I even added the ability to drill down from the cities to the zip code level and also some drill down on the column chart for the counts as well.



Some pretty cool stuff.

We can even upload this into the Office 365 preview and share this information as well which support PowerPivot and Power View.


You might notice that the colors for the themes don’t match up quite yet, but other than that it works out well and you can interact with the Power View visualizations and reports!  In the above screenshot in Office 365 I have the tile where the map resides filtered to 2012.  You can see that the map really opens up now for the entire globe compared to the 2010 and 2011 views.  I can only assume that next year it will grow even more:)

The data that I grabbed for this was from the SQL Saturday events page here (just copy paste and a bit of cleanup).

If you would like to download the Excel 2013 file I created you can grab it from here – SQLData.xlsx

Fiscal Year Layout

I received a request from Karla Landrum the PASS Community Evangelist to see the data setup using the PASS fiscal calendar which starts in July versus the regular calendar.  Well I decided to add in a full date table and refresh the output.  Here is the result and the new file can be downloaded here – SQLDataFiscal.xlsx


If you are experiencing any issues with the downloads take a look at the comments section of this post.  I did some testing and found some issues if you try to directly download the file from the browser and a workaround if the SQLSat Dash does not load for you.  You can download the file(s) from my Public|Blog SkyDrive folder.


Posted in PowerPivot, Reporting Services | Tagged: , , , | 15 Comments »

Using Excel Services Reports with PerformancePoint Server (PPS)

Posted by denglishbi on January 24, 2010

[tweetmeme source=”denglishbi” only_single=”false”]

UPDATE (10/1/2010): I corrected the image of the screenshot for configuring the Products parameter in the Excel file.  I originally had one displayed with DateFiscal for the setup instead of the one referencing Products.  I apologize for that.  If you wanted to do one for DateFiscal you would need to setup the named range for Sales!$B$4.

UPDATE (6/21/2011): Modified table layout of step-by-step walkthrough to be two columns instead of three. Added a downloaded PDF file as well.

One of the nice features about creating dashboards with PerformancePoint Server (PPS) is the ability to incorporate different types of reporting types like Analytical Grids/Charts, SQL Reporting Services Reports (SSRS), ProClarity Analytical Views, and Strategy Maps.  Another one is being able to leverage Excel Services.  As we all know Excel is one of the most widely used tools, so it only makes sense that at some point we will probably want to be able to integrate an existing Excel file within the organization.

Some of the reasons that you might want to use Excel as a report in a PPS dashboard could be:

  • use an Excel report that has already been developed by end-user
  • leverage some of the nice Excel 2007 conditional formatting options – data bars, color scales, and icon sets
  • want more control over the style and format of the chart or table
  • need to incorporate a pie, bar (not column), area, scatter, radar, or bubble chart into your dashboard

The first thing that you will need to do ahead of time before you are able to do this is enable Excel Services on SharePoint 2007 and configure it.  If you are unfamiliar with this process you can check out this reference – Demo: Enable Excel Services and data connections for a SharePoint team site and check the video and download it for reference.  NOTE: Excel Services is an Enterprise feature within SharePoint Server.

Once you have Excel Services configured you will then need to create the Excel file with the items that you would like to include in your dashboard.  In this example I will be using Analysis Services as the data source (the ever famous Adventure Works DW) and will create an Excel PivotTable and PivotChart.  These two Excel items will then be made available in SharePoint (Excel Services) for reference by the PPS dashboard that will then be connected to dashboard filters.  NOTE: this demonstration is using a virtual environment that is setup in a standalone configuration (meaning everything is installed on the server – SQL Server, PPS, SharePoint, etc.).

(You can click on the images below to view a larger image – the flow goes from left to right)

image image
Open up a new Excel workbook and create the data connection to the Analysis Services database Connecting to a local instance of Analysis Services
image  image
Select the AdventureWorksDW database and the Adventure Works cube Enter a name for the Office Data Connection (odc) file
 image  image
Going to create a PivotChart and Pivot table Select ‘Reseller Sales Amount’, ‘Date.Fiscal’, and ‘Geography.Geography’
image image
Only select FY 2004 in the column labels and change the chart type from Column to Pie Remove Chart Title and add Data Bars conditional formatting to the PivotTable
image image
Click in the PivotTable and in the Options enter a meaningful name and deselect the grand total options since we are only going to show one time period in this example Now select the pie and go into the Format Data Labels options and choose the options you would like to include
 image   image
Select the PivotChart and in the Properties in the Layout portion of the Ribbon give the Chart a meaningful name Changed the worksheet name and add an additional hierarchy from the field list in the Report Filter section – Product.Product Categories
 image  image
Go into the data connection properties and export the data connection to the data connection library in SharePoint (just like in the demo link provided above) Publish the Excel workbook to Excel Services
image image
Specify the path to the document library, provide a meaningful file name, and specify the Excel Services options (I just selected items in the workbook and then added the parameter in to be used with PPS) Setup the reference in Dashboard Designer to the Excel Services workbook and the PivotTable
image image
You can click on the View button to see a preview of the item you are referencing Setup the reference in Dashboard Designer to the Excel Services workbook and the PivotChart (you can copy and paste the previous report, change the name in properties, and change the item name being referenced)
image image
Create the dashboard page and link the filters to the reports Deploy the dashboard page
Test the dashboard page filter and drill into the data within the table for further analysis  

NOTE: If you don’t see the Excel Services parameter in Dashboard Designer it is most likely because in the Excel filter you have multiple values selected.  Go back into the file and make sure only one item is selected so that it will be recognized and pulled into the parameters section in Dashboard Designer.

For some additional references in regards to Excel Services checkout some of the links below:



Posted in PerformancePoint | Tagged: , , | 7 Comments »