Saturday, December 1, 2012

CRM 2011 and SQL 2012 Power View

Another great new feature that comes with SQL Server 2012 is Power View. It gives people the ability to quickly and easily create data visualizations. CRM 2011 already has the ability to produce basic charts but Power View provides more options for working with and displaying the data and the results can be made available to non-CRM users.

I'm working off the virtual server I created in this post.

Start off in Excel (with PowerPivot installed) and navigate to the PowerPivot Window.

image

Grab some data from a table or view. Take note not all data types are supported with PowerPivot (see list). What does this mean for CRM? One example in the base tables there is a column - VersionNumber which is a timestamp and won't work, you will receive the following error:

OLE DB or ODBC error: Requested conversion is not supported..
The current operation was cancelled because another operation in the transaction failed.


Simple fix is it to use the Preview & Filter button and uncheck the VersionNumber column.

image
For simplicity's sake I wrote a quick query to retrieve the state and credit limit of the sample accounts.

SELECT creditlimit, address1_stateorprovince
FROM account

Save the sheet and open up SharePoint.

Upload the file to a PowerPivot Gallery.

Once uploaded click the Create Power View Report button.

image

From the fields on the right, make a selection to start displaying data. At this point we can assign a title, choose the chart type, and set the sorting.

image

Now we can go ahead and save the report back to our PowerPivot Gallery and it is ready for end user consumption.

To make sure our report data gets refreshed we should set up a schedule. We can also do this from the PowerPivot Gallery, look for the Manage Data Refresh button by the original Excel file that was uploaded.

image

Options here are fairly straight forward. Once you select the option to Enable the Data Refresh you will be able to set the intervals at which the report data gets updated.

4

5

This was a very basic example, the intent was to show just how easy it is to get started with Power View reports.

For more info on Power View:

http://www.microsoft.com/en-us/bi/Products/PowerView.aspx