How to add images or logos to a PowerView dashboard

One of the strange functionality gaps in PowerView is lack of support of images.  In other words, one can easily add a text box to the dashboard, but unfortunately there is no easy way to add a company logo to the dashboard, which means that yet again we have to resort to a work around.

This solution is not elegant but it works reasonably well and it includes the following steps:

  1. Make sure that you have a URL to the desired image or add the image to an image library on your SharePoint site
  2. Create a new Linked Table in Excel that has two columns in it, one for the image name (or ID) and another for the image URL ( I usually name that Tab as Image)
  3. Make sure that this new Tab (or table) does not have relationships with any other tables in the PowerPivot model
  4. Populate the linked table with the images that you would like to use for your dashboards (logos, etc)
  5. Hit Advanced tab in PowerPivot window and mark the URL field of the table as the ‘Image URL’
  6. Save the workbook to SharePoint
  7. Bring up PowerView designer and then drag the URL field from the Image table on the canvas.  That will display the image and one can use “Send to Back” button to arrange the layout.  Image can be displayed as a Tile, Table, Matrix, or, my favorite, Card.

This is kinda clunky but every single dashboard I have designed was required to have a company logo on it and since PowerView does not let users use images for dashboard design natively, this workaround is the only way I could find to get the job done.

Scorecards in PowerView

Scorecards are not natively supported in PowerView, which is very unfortunate to say the least.. Fortunately there is a work around.

Since I am writing this on a plane, I will just provide a high level description of how to get it done…

First, one needs to implement add a calculated column that will have a score assigned to the metric being analyzed… So, 1 could mean Green, 2 yellow and 3 for red…

Second, create three icons, one for each color (graphically challenged can create one in power point and then cut and paste it into the Windows Paint and then save as a JPG into your sharepoint or any Web enabled folder)

Third, add a new dimension to the model (could be as simple as a linked table in excel). In this dimension, link each status (1 through 3) with the URL of the corresponding icon or image

Create a relationship between the calculated column and new dimension.

You are good to go to create your score cards by using the Matrix control in PowerView…

How to limit values in PivotTable filters in Excel

Excel 2010 is tightly integrated with SharePoint which allows users to author their reports in Excel and then simply save them in SharePoint so that they can be subsequently rendered to everybody else as a web page using Excel Services.  Unfortunately, not all features of Excel can be rendered; some of them generate warnings but unfortunately, some of them prevent SharePoint from rendering the excel document all together.  Data Validation drop downs are one such unfortunate features. 

I have been searching high and low for a workaround and I think I may have just found one.  OLAP PivotTable Extensions project on  CodePlex has a FilterList feature that allows one to specify what values should be available in the PivotTable filter.  I am yet to test all this myself, but the documentation claims that these changes will be available even to users who do not have the Extensions installed and also after the workbook is deployed to SharePoint.

SharePoint Chart Web Part

Here is a good introductory article for the SharePoint Chart Web part.  I have not see this web part used that much, although, there is one feature that I think makes this data visualization vehicle critical for certain types of reporting.  I am of course talking about being able to show a Sales Pipeline Funnel like this:

Few pros and cons to mention.  Starting with cons, we have yet another tool in our tool box of data visualization technologies to consider (as if the existing list is not long and/or confusing enough as it is). The positive is simple, we can build a funnel!

SQL Reporting on Azure goes GA, PowerView not available in cloud

Microsoft has just announced that Windows Azure SQL Reporting is Generally Available. 

A quick glance on the Guidelines and Limitations reveals that the released functionality is mainly based on the SQL Server 2008 R2 stack, which means that PowerView is not available in the cloud yet.

Bubble Charts in Excel and Excel Services

Creating Bubble Charts is one of the biggest challenges in Excel and Excel Services with respect to data visualization.  Microsoft made it very intuitive in PowerView and very not intuitive in Excel.  Frankly, the way this functionality is implemented in Excel makes it practically unusable.  Rumor has it that this area is getting a lot of love and attention in the new release of Microsoft Office (commonly known now as Office 15) but for now we do need some help with building this in the Excel Services supported way.

Microsoft has posted an example of how to do bubble charts in excel so that it will be suitable for Excel Services.  You can find a link to the excel file here. It took me a while to figure out how the file is set up. So to get a quick jump-start on it, take a look at all of the name ranges defined and also click the bubble chart and then on Chart Tools->Design-> Select Data button on the ribbon and then Edit on the Pop up Window to see how those named ranges are being used in the chart.

Not easy and user-friendly, but it works and will provide a much-needed relieve until the new features come out with the new release of Microsoft Office and SharePoint.

Improving performance of Excel reports against Analysis Services

I will start this post with a bit of a rant. There are two types of people in the Business Intelligence world:

  1. Those who do not like Excel
  2. Those who do

Most of the right-click-hating low tech users belong to the first category (they are also usually the same people who like bubble gum and shiny things).  I like bubble gum as much as the next guy, but along with those of us who are viewing business intelligence as something more than pretty gauges and dials, I consider Excel as the ultimate tool for just about every facet of BI.

Over the last two releases, Microsoft has added a lot of very powerful features to Excel to make it easier for a user to explore and analyze data in Analysis Services cubes.  One can connect Excel to Analysis Services by clicking on Data->From Other Sources->From Analysis Services.  That creates a connection that can then be used as a source of not only pivot tables and charts, but also a number of Cube…() functions that have been added to Excel in 2007 in order to make Excel even more robust and sophisticated reporting and analytical tool.

These functions break down the limits of pivot charts and pivot tables in terms of what’s possible with respect to the look and feel of the report.  There are however some gotchas that one needs to be mindful of and also some opportunities to improve the performance of the Excel reports that are sourcing their data from Analysis Services.  Here is a good article that suggests some ideas to optimize the performance.

PowerView in Windows 8 Release Preview

I was eagerly awaiting the new Release Preview of Windows 8 as I was hoping it would address some of the shortcomings with respect to the Microsoft BI Stack and Windows 8 that I blogged about before

One of the new features introduced in to the IE 10 was Flash support.  I have not read anything about the Silverlight support, but it only seemed natural that Microsoft would support its own technology before it supported a competing product, but unfortunately, this is not the case with the Release Preview.  Essentially, there is not much new to report that would be pertinent to the Business Intelligence stack.

Everything still pretty much works the same, or rather, not work the same.  IE 10 still does not support PowerView and Master Data Services Data Explorer (Silverlight), and PerformancePoint content is still virtually impossible to navigate using touch input.

My prognosticated speculation is that the OS is not likely to be any friendlier to the stack when it gets released, however, I am expecting a service pack for both SQL Server and SharePoint to address this incompatibility.