PerformancePoint and ADOMD.NET

I was recently configuring PerformancePoint in SharePoint 2013 and had deployed the SQL Server 2012 AdventureWorksDW Multidimensional cube for testing purposes. It had been a while since I configured PerformancePoint so thankfully I had written this post previously. As luck would have it I ran into pretty much every roadblock mentioned in that post……and one additional “detour” that I had almost forgot about (that also had a new twist).

If you’re in PerformancePoint Dashboard Designer and trying to create a data source that connects to SSAS, but seeing nothing show up in your Database dropdown (as seen below…..sorta) you might need to install ADOMD.NET 11.

clip_image001

Not only install it, but install it on all servers that run PerformancePoint AND you’ll need to adjust the Web.config file to tell PerformancePoint to use it.  This is of course assuming you’ve set everything else up properly. I’m taking for granted that you’ve already done stuff like

  • Associate an Unattended Service Account to the PerformancePoint Service Application and granted the appropriate users the right to use the unattended account in the Secure Store Service Application
  • Double checked that the service account you’re using for your Unattended Service Account actually has access to at least one db on the SSAS instance you’re trying to connect to

If none of that sounds familiar, check out my post on secure store for Visio Services or my posts on using data refresh with Excel Services…configuring PerformancePoint should be pretty similar to those.

Note that I wasn’t getting any error messages, but instead the database dropdown was just empty. If you’re getting any type of error on the screen shown above, your problem is most likely something else and not ADOMD.NET. Still with me? Is your database dropdown still mocking you at the very moment with its emptiness? Hopefully this will cure your PerformancePoint woes.

So….let’s do this

  1. Go download ADOMD.NET 11 already will ya?….at the time of this writing you can find it here: http://www.microsoft.com/en-us/download/details.aspx?id=29065
  2. Install it on all the first farm devices that’s running PerformancePoint. (something like next, next, finish)
  3. Once ADOMD.NET 11 is installed, navigate to \Program Files\Microsoft Office Servers\15.0\WebServices\PpsMonitoringServer
  4. Locate and make a backup copy of the web.config file found there. (Just in case you mess this all up…..I certainly don’t trust you.)
  5. Open the web.config file and scroll on down to the very bottom. Once you get there you should see something like this:
    clip_image003
  6. Change the oldVersion from 9.0.0.0 to 10.0.0.0
  7. Change the newVersion from 10.0.0.0 to 11.0.0.0
  8. Save that bad boy
  9. Repeat for every server running PerformancePoint

Completing the above had my database dropdown populating with an abundance of easily selectable db’s for as far as the eye could see. Some say it’s still populating new and wonderful connection options to this very day (results may vary).

I don’t recall having to do an iisreset or anything…although I think I might have closed Dashboard Designer and reopened it after making the changes. Hopefully this helps!

How do I add pictures to a Power View report using Power Pivot as a data source?

One of the great new features of SQL Server 2012 is Power View.  In this walkthrough I’ll be creating a very simple Power View report using Power Pivot as the datasource.  The goal of this report will be to demonstrate how you can pull in images into your report to give the user a nice visual…so let’s get started.

1.       Get your pictures ready:  I simply took some quick pictures of items laying around on my desk, then I uploaded them to a SharePoint library.  These pictures are a little less than visually stunning, but you get the idea.  Just make sure to remember the URL, as we’ll be pulling our pictures in from this library.

clip_image002

2.      Now we need to build ourselves a Power Pivot workbook.  I’m going to keep this real simple with only two tables, and we’re really only going to talk about one of them….the “Items” table shown below.  There are a few things to note about the table below.

o   Each entry in the Item column is named after one of our pictures…that makes it easy to construct what the URL to our picture is

o   The “ImageURL” column is a calculated column which is simply the URL to the document library where my pictures are located. (you can see the formula in the fx bar in the image) .  The key point here, is that you need to have the full URL to your picture somewhere in your Power Pivot book.

clip_image004

 

3.      Now that we’ve got some data with URLs to some images, we need to tell Power Pivot that our “ImageURL” columns is actually an image.  To do this we’ll want to click on our main menu in the Power Pivot window and select “Switch to Advanced Mode” as shown in the picture below.

clip_image005

This should make a new “Advanced” tab available.  From here we want to highlight our “ImageURL” column, and then check the “ImageURL” checkbox on the advanced tab as shown below highlighted in yellow.

clip_image006

4.      As a finishing touch let’s let Power Pivot know that our image column is actually a picture of the entry in our “Item” Column.  This will allow us to show both the item text and the picture in the same tile when we go to make our report in Power View.  To do this we need to click on “Table Behavior” under the “Advanced” tab (shown in the image above highlighted in yellow).  Doing so will open the “Table Behavior” window shown below.

clip_image008 

On this screen we need want to do the following:

·         For “Row identifier” select the column we want to work with, in our case this is the “Item” column.

·         For “Keep Unique Rows” select columns you don’t want to aggregate on (you don’t need to do this for our example, but it’s a good practice to get into.)  In our case, the “Item” columns is unique in our data.

·         For “Default Label” select what we want the label to be when we create our tiles.  In our case this is the actual text in the “item” column.

·         For “Default Image” select the column that the URL to our picture resides in.  This will be our  “ImageURL “ column.

Once that’s finish click OK, and the save and close the workbook. 

5.      Now that we’ve created our Power Pivot data set we just need to upload it to our Power Pivot Gallery in SharePoint, and then create a new Power View report by clicking on the Power View Link as shown highlighted in yellow below.

 

clip_image010

 

 

If I select “Item”, “description”, and “Sales Total” (sales total was the other table we didn’t talk about, but it was just a bunch of numbers I made up and related to our first table) and then click the “Tiles” button from within Power View it’ll look something like this.

clip_image011

So there we have it, a very quick/simple report example using images in Power View.  In addition to showing images in our tiles, we can also show our images in tables or on cards.  I think that about wraps this walkthrough up, hope you found it helpful.

 

 

How to Create a BISM file (SSAS Tabular mode) in SharePoint so you can create Power View reports.

So you’ve got SQL Server 2012 SSRS integrated with SharePoint, and an Analysis Services Tabular model deployed.  So how do you go about creating reports with the new Power View tool?

This walkthrough will take you through the steps, so let’s get Started.

First of all we’ll need to create a .BISM (or Business Intelligence Semantic Model) file in one of our SharePoint libraries that points to our SSAS Tabular model.  In this example I’m going to use a PowerPivot library that I’ve already got hanging around, but before we can create the .BISM file we need to add the content type to our library.  We’ll do so by opening our library and clicking on the library tab at the top of the screen.

clip_image001

From there we’ll want to look to the far right in the “Settings” group and click on “Library Settings”.

clip_image002

In our Library settings screen click on “Advanced settings” under the “General Settings” group.

clip_image003

Our objective here is to switch “Allow management of content types?”  from “No” to “Yes”.  Make it so, as shown below and then click OK.   Once you click OK you should be directed back to the main Library settings screen.

clip_image004

Now that we’re able to manage our Content Types, we’ll need to add the BISM content type to our library.  From our current screen we’ll  need to click on “Add from existing site content types”

clip_image005

We want to add the “BI Semantic Model Connection”.  Do so by locating it as shown below, highlighting it, clicking add, and then click OK at the bottom.  That should be it, our new content type should be ready to rock and roll.

clip_image006

Now we need to actually create our BISM file.  Navigate back to your library, then click on the “Documents” tab.  Under the “New Documents” dropdown, you should now see our new “BI Semantic Model Connection” content type.  Let’s go ahead and create a new document using this content type.

clip_image007

This screen should be pretty straightforward, simply name your connection and then provide the server name and database name of where your tabular model resides, then click OK.

clip_image008

And that’s it!  You should now see something similar to the following in your library.  As a final test, we can throw together a quick Power View report using the icon shown below at the top far right.

clip_image010

With very little effort we can throw together a quick report in Power View just to prove that everything is working correctly.

clip_image012

I just recently got my SharePoint 2010/SQL Server 2012 personal development environment set up,  so I haven’t spent much time with Power View yet, however I  can say I really like what I’m seeing so far.  It’s really easy to use, visually appealing, and highly interactive.  I think that probably wraps it up for this post.  Hope this helps.