SQL Server 2012 & Project Server, Part 2– Business data visualisation with PowerView

PowerViewIn this second post looking at some of the new capabilities of SQL Server 2012 and how they can be leveraged in your Project Server farm, we are going to look at PowerView.

PowerView was originally announced as Project Crescent and has wowed pretty much anyone that has seen it. In essence, PowerView allows you to build visualisations of your data using a simple drag and drop interface that are ‘Presentation Ready’ meaning there is no nasty wiring up of data, simply drag your data onto the surface and being analyzing it.

PowerView can leverage data from two types of source, a Tabular model in SQL Server 2012, or a PowerPivot workbook stored in Excel. Of the two, the latter is by far the easiest to get up and running.

To start with I am going to assume you have set up your farm to leverage PowerPivot, this requires adding a special ‘PowerPivot’ analysis services instance to your farm and then configuring the farm to use it. The whole set up experience has gotten better in SQL Server 2012, but it’s still fiddly requiring a wizard to be run and a number of content types to be manually added to the relevant reporting library. There is a great video taking you through the whole process here , including how to set up the server side and the Excel client PowerPivot add-on.

In my case, I was keen to try out PowerView against Timesheet data mainly because I worked at an organization that used timesheets extensively and was familiar with the data model and some particular reporting use cases I wanted to try.

To start with we need to import the Project Server data into PowerPivot. To do this, open up the PowerPivot for Excel add-on and choose to import from a SQL Server Database.

Get data from SQL Server

 

A table import wizard will be shown, configure it to point at your Project Server Reporting database and click next twice, you will then be presented with a list of all the tables and views available in the database to use for reporting. As I am interested in timesheet info, I am going to pull in some timesheet only tables and a couple of the generic _userview tables.

Importing into PowerPivot

 

PowerPivot will then proceed to import the tables and this is where the really clever stuff will start, in most cases PowerPivot will be able to automatically infer the relationship between the tables, but as I have chosen to pull in the EPMProject_UserView and EPMResource_UserView tables I need to help it with the relationships.

 

PowerPivot Relationships

Creating a relationship is pretty easy, simply choose ‘Create Relationship’ from the design ribbon and select the relevant tables and joins.

 

Manually create relationships

The final step before building the PowerView was to save the PowerPivot back into the gallery in SharePoint.

PowerPivot uploaded into SharePoint

 

Once the PowerPivot Excel spreadsheet is in SharePoint we can start to build the PowerView by clicking on the PowerView icon which will open the designer.

Create Power View Report

The designer will show a design surface on the left and a list of entities on the right hand side. To start building the PowerView simply select the relevant information on the entities and drag it onto the design surface.

PowerView Designer

PowerView will automatically highlight the entities that are related from the relationships either detected automatically or manually set.

Once the entities have been dragged onto the design surface, PowerView allows the user to select the best way to visualize the data, allowing the user to change the layout and visualization method (table, graph or tiles). The PowerView can also be further refined through the use of slicers and filters.

PowerView visualisation

So in about 15 minutes I put the above timesheet analysis PowerView together, showing all resources in the organisation, and a breakdown of Planned & Billable work across the month, week and projects all with a few drag and drops.

The real power of course comes from interacting with the PowerView, so clicking on a resource name will change the data, clicking on the projects of hours breakdown will automatically cause other parts of the PowerView to filter as can be seen below.

Filtered PowerView Visualisation

There are a number of other parts to PowerView that I haven’t explored in this post, specifically things like Play Axis that allows you to see data sets changing over time, which would be interesting to wire up to some of Project Server timephased data, the ability to embed the visualisation into a PowerPoint slide, card and tile visualiations to name but a few. Luckily Microsoft have released an excellent tutorial over at TechNet that takes you through the process of building PowerView from start to finish. If you have SQL Server 2012 installed and are using Project Server 2010, I challenge you to get cracking to wire up some Project data and amaze your executives and PMO !!

Love Business Intelligence on Project Server, install the December 2011 CU now!

It’s always sound advice to try and keep your Project Server 2010 environment patched to the latest cumulative update, typically these include bug fixes or small enhancements to keep your environment running smoothly. Back in December, Microsoft released the December Project Server 2011 CU, which included a number of fixes and changes to timesheets amongst other things. As Project Server runs on top of your SharePoint environment,  it’s also necessary to patch SharePoint at the same time. With the December 2011 CU, the PerformancePoint team rolled in a number of enhancements to allow the viewing of PerformancePoint dashboards, reporting services reports and excel services on the iPad which Project Server gets to benefit from free of charge! Check out the screenshots below of the various Business Intelligence reports and dashboards rendering on an iPad.

Project Server Summary Dashboard on the iPad

 

Project Server Status Report on the iPad

 

Excel Services on the iPad

There are a number considerations for allowing your reports and scorecards to render correctly on an iPad. From my limited testing most, if not all of the the Project Server v1 demo image rendered correctly without modification, with the exception of one of the status reporting services reports. Microsoft published a technet article outlining the limitations of the type of reports that can be displayed on the iPad and tips on how to interact with the reports.

Finally, don’t forget to check Brian Smith’s and Adrian Jenkins webcast covering the contents of the December CU including the various fixes and design changes it implements, to make sure the patch is right for your farm & circumstances.

Happy Patching Smile

Project Demo Image–Excel Services problem after SP1 upgrade

Excel Services ErrorAnother Update: Thanks to SeaMonkey76 in the MSDN forums pointing out that this issue is fixed in the October 2011 SharePoint CU (http://support.microsoft.com/kb/2596582), so get patching :)

Update: A couple of readers have pointed out that this behaviour is is the norm where there isn’t a root site and it’s best practice to implement one. I totally agree and have run into situations where things like InfoPath have ceased to work due to this. The purpose of this post is more for those people that run into this error in the demo image, after an upgrade and can’t figure out why. It took me a little while to track it down, so I just through I would share.

I am posting this in case anyone else runs into a similar problem in Excel Services and to give some search engine index bait.

Following upgrading my IW demo image to SP1 + June CU, I noticed that Excel Services components in the Business Intelligence centre were no longer rendering and consistently showing the loading graphic.

Excel Services - Loading Graphic

On closer inspection, the browser was showing the following error:

Message: Syntax error
Line: 1
Char: 1
Code: 0
URI: http://project.contoso.com/_layouts/EwaStringsHandler.ashx/en-US?rev=V5cXKzVnvQRpcOxdnGD5cQ%3D%3D&flh=4sHCQGJUaMtQCBbbV6EmZw==

Checking the ULS logs showed these error messages:

An internal error occurred.    at Microsoft.Office.Excel.Server.MossHost.MossHost.Microsoft.Office.Excel.Server.Host. IEwaHost.IsSecureConnection()     at Microsoft.Office.Excel.WebUI.EwaCUIDataSource.EnsureDocument()     at Microsoft.Web.CommandUI.CUIDataSource.RunQuery(UIQuery query)     at Microsoft.Office.Excel.WebUI.EwaRibbon.QueryRibbonDataSource(CultureInfo uiCulture, String clientID, Boolean denormalizeImareUri)     at Microsoft.Office.Excel.WebUI.EwaStringsHandler.ProcessRequest(HttpContext context)

and

Watson bucket parameters: SharePoint Server 2010, ULSException14, 5f9be61a “excel services application”, 0e00178d “14.0.6029.0”, f5b5c9d6 “microsoft.office.excel.server.mosshost”, 0e001785 “14.0.6021.0”, 4d65e5e7 “wed feb 23 21:00:23 2011″, 000002d3 “000002d3″, MISSING, 4a6d3421 “nullreferenceexception”, 66326e39 “f2n9″

It seems that Excel Services now needs a site collection to be present in the root of the a web application or it will throw the above error. I am not sure when this behaviour changed, but given the IW Demo Image and Project Demo and Evaluation pack does not install a site at http://project.contoso.com it is necessary to create a blank site collection via Central Admin manually.

Once this is done, the Business Intelligence Centre should start to render the Excel Services components once again.

Mobile Project Server Business Intelligence with Windows Phone 7

Out of the box, Project Server 2010 is arguably the most business intelligence heavy application that Microsoft build on the SharePoint platform. It’s a bold statement, but when you think about it, a default installation of Project Server will implement a dedicated relational reporting database and provides the capability for numerous analysis services instances, each containing up to 14 OLAP cubes, all configurable from within the tool.

With the reliance on SharePoint 2010 Enterprise, a number of new and improved capabilities are made available, including PerformancePoint Services & Visio Services which provide new and improved ways of visualising project data to quickly expose trends and possible issues so they can be corrected. Unfortunately whilst the story is fantastic if you are using a PC, it’s a little less impressive if you are using a true mobile device such as a phone. Whilst people may have laptops, wireless and mobile broadband, starting up a laptop & connecting is way less convenient than quickly viewing your data on a mobile device, pretty much instantly.

So I started to wonder what the options were for mobile business intelligence, specifically how could I see the status of my project portfolio, risks, issues & financial information directly from my mobile phone? As my personal phone is an iPhone I started by checking out the various options, including Roambi & PushBI. Both looked pretty good on paper, but either required you to buy licences, were not real time or required you to install propriety software on your farm / servers.

Then thanks to a twitter from JJTotal, I was directed to an application called Blue-Granite Nitro. Nitro is a free Windows Phone 7 app that renders XML feeds of information and produces some very impressive visualisations of data using the WP7 metro interface for rendering.

Example Visualisations from Blue-Granite Nitro

In order to generate the XML feeds that Nitro uses, all that is required is a SQL Server Reporting Services instance running one report. So as Project Server requires SQL Server, this should be readily available. Being based on Reporting Services, developing the report is incredibly simple, either through Visual Studio, or my preferred report tool, Report Builder v3.0.  The reporting services instance needs to be exposed externally and set to allow basic authentication which may cause some network admin headaches. The Nitro application itself then just needs to be set up to connect to the reporting services instance where it will run the report, generate the feed and render the dashboards in real time.

The Blue Granite web site provides a full breakdown of the structure required by the application and a number of run throughs and demo’s. But of course, I thought I would have a go myself wiring up some data in the Project Server Reporting database and OLAP cubes, and whilst I was at it, why not film it Smile

So here are my attempts, best viewed in HD, the first one is a run through of how to create a feed from scratch and then render it in Nitro.

Click to be taken to the video

The second one shows a slightly more advanced scenario with some different visualisations and connecting to both relational and cube based data.

Click to be taken to the video

On reflection, building the report up was incredibly simple, you just need a bit of knowledge of the Project Server Reporting DB schema and what’s available in the cubes (which is documented in the Project 2010 SDK available from here). Where information is not available from these sources, such as extended risk, issue or SharePoint list data, additional tools such as the iPMO data miner or SLAM could be leveraged to make copies for reporting purposes without directly querying the contentdb. As you saw in the video configuring the Nitro app itself was just as easy.

Of course this solution is not just limited to Project Server data, any information you have stored in SQL and could report on using Reporting Services can be queried and rendered through Nitro on a Windows Phone 7. Given the price point, the ease of configuration and the fact it leverages your in place technology without the need to install any new servers / software, why wouldn’t you install it and start to empower your organisation today?

Finally, as I don’t have a Windows Phone 7 of my own to play with (I will gladly accept any donations though Winking smile), I would like to thank Glenn Wilson for lending me his HTC Mozart to build the screencast.