Dan English's BI Blog

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

Posts Tagged ‘sharepoint’

PerformancePoint Filter – Save as my defaults permissions

Posted by denglishbi on February 8, 2013

It has been a while since I did some blogs, so time to get going again.  What better way to start than with a PerformancePoint one, right?  This is a post that I have been meaning to do, particularly after I responded to a post about it in the PPS M & A forums here – PerformancePoint Filter – Save as default permissions.  I had come across this particular issue at a few client sites as well.  The ‘Save as my defaults’ option for filters was a new capability that was added with SharePoint 2010 SP1 and I posted a blog on the new features added to PerformancePoint with SP1 here PerformancePoint 2010 Cascading & Apply Filters – SP1 Features.

So, for those of you not familiar with the ‘Save as my defaults’ option here is what it looks like:


And it is part of the Apply Filters settings in the ‘Save options’ towards the bottom, you can either show or hide the check box to save the filter choices.


First off, the Apply Filters button allows users to make changes to the filters without the changes immediately refreshing the content they are connected to (similar to the View Report or Apply button for Reporting Services reports).

The ‘Save as my defaults’ check box pretty much does as it says, a user can make a selection, check the box, and then click the Apply Filters button.  This when then store the parameters for this particular dashboard page so that when the user returns to this page the selections will be restored.  So even if the user makes different selections from the list, PerformancePoint will remember the values that were saved when the check box was enabled.

In the image below I make the selections ‘Accessories’ and ‘North Amer’ and check the box to save the defaults.


I then click the Apply Filters button and the dashboard items refresh and you will notice that the check box is disabled.


If I make a change to the geography filter and select Europe and click Apply Filters the dashboard items connected to the filters will refresh.


Now if I refresh the entire page (click F5) I see that my original filters that I selected and applied when using the ‘Save as my defaults’ check box are restored.


So this is pretty cool, but how does this work for a typical user that has view-only permissions to the dashboard.


Well, it doesn’t because the option to ‘Save as my defaults’ is disabled.  Hmmm… well that doesn’t help.  So what is the secret SharePoint permission that needs to be added so that this is enabled without making the user a dashboard admin?  Good question.  Well I wasn’t able to find any documentation on this and even when these new features came out there wasn’t that much documentation.  Basically after a little trial-and-error (or guessing-and-testing) I came across the additional permission that is needed.  The permission that is needed is in the Personal Permissions section and is ‘Update Personal Web Parts – Update Web Parts to display personalized information.’


Once that is added then the check box is enabled for the user and can be used.


Pretty simple. 

NOTE: one thing I noticed with this, and I would say that it is a bug in my opinion (don’t believe this has been fixed in a CU), is that if the same filters are used on other pages and changed and you come back to the original page where you used the Apply Filters and ‘Save as my defaults’ option, the values will be overridden based on the selections on the other page.  The original defaults will not be used, so seems to be a scoping thing in my opinion as to how the filter values are saved, not working as I would expect, they are saved at the filter level versus the page.  So the defaults can actually be wiped out if the same filters are used on other pages without the Apply Filters button.  If the Apply Filters button is used on all pages then it appears you are okay.

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

Reporting Services SharePoint Large Parameter List Issue & Fix

Posted by denglishbi on November 8, 2012

UPDATE (11/13/2012): Additional modification needed to provide ability to manage the parameters in SharePoint (see the bottom of the post for the updates)

If you are using Reporting Services (SSRS) in SharePoint Integrated Mode (I saw this issue with SSRS 2008 R2 SP2 & SSRS 2012 with SharePoint 2010 running with SP1 CU Aug 2012), then something you might want to be aware of.  If you ever have to load a parameter list with lots of records (say 35K+) then you might end up receiving a message like this:image

An error (Unable to read data from the transport connection: The connection was closed.) occurred while transmitting data over the HTTP channel.
An error (Unable to read data from the transport connection: The connection was closed.) occurred while transmitting data over the HTTP channel.
Unable to read data from the transport connection: The connection was closed.


If you are running the report in BIDS or SSDT or Reporting Services Native Mode you will be fine, just an issue with SharePoint Integrated Mode.  I also noticed this with a report that had approximately nine parameters (four were hidden) and two of them had approximately 10K records.  So I think it was the combination of the parameters along with some default values getting set that caused the issue.

REPRO: For a quick example of this issue you can easily replicate this with populating a parameter with the FactFinanceKey from the AdventureWorksDW2012 database.   Deploy the report to SharePoint and try and run it:)

I have filed a Microsoft Connect Bug for this here – Reporting Services (SSRS) SharePoint Large Parameter Issue

Well after a bunch of searching and digging through the forums, blogs, BOL, and additional documentation (and even tried an old ProClarity fix) I found the fix for the issue – MaxItemsInObjectGraph….yes, that was a setting that I had to incorporate into a couple of the configuration files for Reporting in SharePoint:S

The first forum posting that pointed me to the web.config and client.config files was Issue Exporting SSRS 2012 Report to CSV file in SharePoint 2010 (MaxReceivedMessageSize property exceeded) and then after doing some additional research running live log tracing on SharePoint I tracked down that it was really related to the WCF and I came across this additional MaxItemsInObjectGraph setting in quit a few posts such as this one MaxItemsInObjectGraph and keeping references when serializing in WCF.

Now I didn’t find the magic post that just told me what to do, but with some trial-and-error I came up with the following steps to fix this:

1. Edit the web.config file in the following location C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\Reporting

NOTE: prior to modifying a config file, always make a backup of the file first and use a text editor like notepad, notepad++ to modify the file, do not use a rich text editor.

2. In the web.config file find the <behaviors> section and make the following addition:

             1) maxConcurrentCalls is set to 16(default value in .Net3.5)*Processor Count(assuming 32)
             2) maxConcurrentInstances is set to 512 since InstanceContextMode is PerCall
             3) These settings can be removed after upgrading to .Net 4.0
        <behavior name=”RSExecutionBehavior”>
        <serviceThrottling maxConcurrentCalls=”512″ maxConcurrentInstances=”512″ />
        <serviceMetadata httpGetEnabled=”true” />
        <serviceDebug includeExceptionDetailInFaults=”true” />
        <dataContractSerializer maxItemsInObjectGraph=”2147483647″ />


3. Save the file and then go and edit the client.config file, remember to make a backup copy prior to modifying it.  This file is located in the following location C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebClients\Reporting\

4. In the client.config file add the following section below the <System.ServiceModel> tag and before the <client> tag

        <behavior name=”RSExecutionBehavior”>
      <dataContractSerializer maxItemsInObjectGraph=”2147483647″ />


5. Now I also modified four of the endpoint settings in the <client> section to reference the new behavior that I added (IReportExecution and IReportStreamingAsyncClient):

<endpoint name=”https” contract=”Microsoft.ReportingServices.ServiceContract.IReportExecution” binding=”customBinding” bindingConfiguration=”https” behaviorConfiguration=”RSExecutionBehavior”/>
<endpoint name=”http” contract=”Microsoft.ReportingServices.ServiceContract.IReportExecution” binding=”customBinding” bindingConfiguration=”http”  behaviorConfiguration=”RSExecutionBehavior”/>

<endpoint name=”https” contract=”Microsoft.ReportingServices.ServiceContract.IReportStreamingAsyncClient” binding=”customBinding” bindingConfiguration=”httpsStreaming”  behaviorConfiguration=”RSExecutionBehavior”/>
<endpoint name=”http” contract=”Microsoft.ReportingServices.ServiceContract.IReportStreamingAsyncClient” binding=”customBinding” bindingConfiguration=”httpStreaming”  behaviorConfiguration=”RSExecutionBehavior”/>

6. Once that was done I had to restart the server where the Reporting Services application was running.  The web.config settings seemed to work right away, but not the client.config settings.  Once it restarted I warmed up SharePoint and got SSRS running again.  I tested out my report and it started up just fine, no error message:)


And that is the fix, can’t believe this is the first time I have come across this issue, but now there is a fix and it is documented:D  Just a word of caution, this still does not perform as well as it does in SSRS Native Mode, but at least it does work now.  It appears that it takes maybe twice as long to load in SharePoint as it does through the Report Manger (your mileage may vary though).

I have filed a Microsoft Connect Bug for this here – Reporting Services (SSRS) SharePoint Large Parameter Issue

UPDATE (11/13/2012): Additional modification needed to provide ability to manage the parameters in SharePoint –

One quick update on this – there is one more additional service & endpoint that needs to be updated in the config files as well. The reason is so that you can manage the parameters in SharePoint if needed.

Web: <service name=”Microsoft.ReportingServices.ServiceRuntime.ReportServiceManagement” behaviorConfiguration=”RSExecutionBehavior”>

Client: <endpoint name=”https” contract=”Microsoft.ReportingServices.ServiceContract.IReportServiceManagement” binding=”customBinding” bindingConfiguration=”https” behaviorConfiguration=”RSExecutionBehavior”/>

I added the behaviorConfiguration reference. Not only did I update this in the web.config file, I also updated the endpoints in the client.config file as well. It seems like it picked up on the change without a reboot this time as well, so the web.config setting seemed to work, but wanted to keep things in synch so I updated the client.config as well. This time though I did receive a valid SharePoint error message and was able to look it up in the logs to pinpoint the additional service being referenced for this operation. There are two other items in the config file – ReportServiceBackgroundProcessing and AlertManagement – and I have not had to alter these ones yet.

NOTE: Also, in a multi-server environment I had to update the client.config file on the WFE server (web.config did not exist since it wasn’t the application server) and restart that one to get the reports to behave consistently.

Posted in Reporting Services, SharePoint | Tagged: , | 5 Comments »

Presenting Power View Reports to Users

Posted by denglishbi on October 17, 2012

As you begin to use the new Power View reporting feature that is available with the Reporting Services 2012 integration in SharePoint 2010 one of the things that you will need to assess is how you are going to allow the users to access the reports.  What are the different options that you have when providing access to the Power View report files (rdlx) in SharePoint?

Here is a list of options that you could use:

  • store the Power View reports in a Document Library
  • display the Power View reports in a PowerPivot Gallery
  • use a Page Viewer Web Part to display the Power View report
  • use a Silverlight Web Part to display the Power View report

Here is how each of these will look to the end-user that will be access the reports:

Document Library


Above we can see that the Power View report files are available and stored in a document library, nothing really fancy, just storing the rdlx files.

PowerPivot Gallery


The PowerPivot Gallery is a very nice option to display the Power View reports. Provides a nice visual display of the reports and users are able to see if additional views are included in the files as well.  The view I have displayed above is the default Gallery setup, but additional options are available such as just displaying the files in the document layout like the Document Library display in the first example, Theater, and Carousel.

Page Viewer Web Part


The Pager Viewer web part is a nice option to include a Power View report (or a particular view) on a page.  This is a nice option to include a Power View report into any existing page and to provide a nice controlled output of the report.  You can see that the ‘Preview Bar’ has been removed from the top of the report so a user is not able to access the ‘Edit Report’ option.  The users are still able to interact with the report and access the different views in the report as well.


How do you configure the Page Viewer web part?  Pretty basic and a bit easier than the next option that will be looked at.

  1. Create a web part page
  2. Add a Page Viewer web part to the page
  3. Specify the URL path along with additional parameters for the report
  4. Specify the height and width for the web part for the appearance

Here is what the Page Viewer web part settings look like for the example displayed above:


Here is the URL path that I have specified along with some additional parameters that I am setting to provide the desired output:


In this example I am setting the View Mode, the Report Section I want to display, and hiding the Preview Bar.  These are the core settings that need to be set to get this output.  There are additional settings that you can explore and a few of these I discuss here in the following post URL Actions with Reporting Services Power View (RTM) and as one person commented there are additional parameters available and you can see these by accessing the properties of the output in PowerPoint as discussed here Can i edit my PowerView Report exported to PowerPoint in Presentation Mode? YES YOU CAN.

If we dig into the above output for the Pager View web part we can see that the output is displayed in an iframe and granted just by removing the Preview Bar doesn’t mean that the user couldn’t track down the actual location of the report and launch it from there, so this by no means is a security option.  The user still needs the proper permissions to the Power View report file (Restricted Readers – requires the ability to Open Items and this is different compared to regular Reporting Services reports because of the Silverlight application).


Silverlight Web Part


The Silverlight web part display looks identical to the Page Viewer.  This option requires a bit more of setup and I have to thank Robert Bruckner for the details on configuring this option.

    1. Create a web part page
    2. Add a Silverlight web part to the page
    3. Specify the URL path to the Silverlight application
    4. Specify the custom initialization parameters (Other Settings)
    5. Specify the height and width for the web part for the appearance

The path to the Silverlight application is the following:


The custom initialization parameters that I used in the example above are the following:


The template Robert provided for this setup is the following for the parameters:


Here is what the Silverlight web part settings look like for my example above:


When you add the Silverlight web part to the page you will be prompted for the URL path for the Silverlight application. That setting and address is also available in the above screenshot in the Application settings when you click on the ‘Configure…’ button.

You can once again dig into the output like we did above with the IE Developer Tools (F12) and you will see the following:


A little different output here and if you dig around enough you will eventually track down the initialization parameters, so once again not an option to prevent the users from finding out the original source of the file, but it is still an option that you can use to display Power View reports in a web page.


So to wrap up this post, I just wanted to show the different options you have available in SharePoint to display and provide access to Power View reports to end-users.  The first two options allow the users to launch the reports in the web browser and access the Edit Report or go into Full Screen modes.  I would use the PowerPivot Gallery option because of the nice output and provides the users quick insight as to what is included in the report and an overview of the different views (if more than one exists).  The last two options would be something to explore if you want to include a Power View report on a web page.  This is a nice clean option where we have removed the Preview Bar, but have still allowed the user access to the report to interact and browse the different views.  This could be a nice option if say you want to include a Power View report in a PerformancePoint dashboard.

I hope you have enjoyed this quick overview and for more information on how to use Power View and creating tabular models, don’t forget to check out our book here – Visualizing Data with Microsoft Power View.

Posted in Reporting Services, SharePoint | Tagged: , , | 4 Comments »

URL Actions with Reporting Services Power View (RTM)

Posted by denglishbi on June 13, 2012

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

Yesterday we took a quick peek inside the RDLX file – Inside the Power View RDLX File.  Today we will take a step back into SharePoint and explore what we can do when we access the Power View files through the URL.

WARNING: The items that are displayed and discussed here are not documented at all by Microsoft and could potentially change at any point down the road, so use at your own risk.power view

Many of you might be familiar with Reporting Services (SSRS) and creating report files (RDL).  With these files you have a lot of flexibility with your design and you can also leverage a lot of commands and syntax when accessing these files through the URL (in Report Manager or SharePoint) – URL Access Parameter Reference.

So what is available with v1 of Power View with the RTM release of SQL Server 2012?

Unfortunately not a whole lot.  But we do have a couple of things:

  1. ViewMode
  2. ReportSection

Hmmm….they don’t look like anything we had available with previous report files.  You are absolutely correct.  These are new options that are specific to Power View files (RDLX files).  With these files we can determine how the initial viewing mode is – currently only presentation (reading mode) or edit – and then which report (view) you want to look at (assuming you have multiple views in your report files).

So let’s take a quick peek at what these options look like:


With the ViewMode option you currently have two options you can use, either Presentation (Reading) or Edit

Presentation (Reading) mode is the initial view state of Power View files that you see when you access a RDLX file in SharePoint


Edit mode is what you see when you open up the canvas and have the options to be able to alter and change the layout of the report


You can now see that by switching the ViewMode value from Presentation to Edit we go from the Reading mode to the Edit mode where the canvas is available and we can modify the layout and design of the Power View report file.


What are ReportSections? In the RDLX file layout, a ReportSection is actual the equivalent of a view in a report file.  So if you have multiple views you will have multiple ReportSection tags within the ReportSections portion of the RDL file that is embedded in the RDLX file (see previous post for information on this – Inside the Power View RDLX File).   Take the file from our previous post and we can see the XML layout as follows:


The ReportSections area is setup so that when you export into PowerPoint and access the views in say the PowerPivot Gallery it knows which view of the RDLX file to display and output.

In looking at our example up above you can see that we have 4 views included in the Power View report.  In the XML displayed here we see 4 ReportSection tags in the RDL file and the naming convention is ReportSection, ReportSection2, ReportSection3, and ReportSection4.

So with the naming convention displayed here let’s go back to our URL expressions above and expand on them and include the ReportSection into the URL address to change from the first view in the Power View report file to the second:


By using the ReportSection command we have been able to navigate from the first view (report) in our file (ReportSection=ReportSection) to the second view (ReportSection=ReportSection2).  Pretty slick!

As you can image this will work for the third and fourth views in the report file as well.

So for now this is what we have available to us, just ViewMode and ReportSection.  It does provide you the means to be able to send an end-user to a specific view within a Power View report file (assuming you have more than one view defined).

If you want to find out more about Power View and want to learn about designing Tabular models to support Power View, don’t forget to check out our book – Visualizing Data with Microsoft Power View.


Posted in Reporting Services, SharePoint | Tagged: , , | 3 Comments »

Upgrade PowerPivot SharePoint from SQL 2012 RC0 to RTM

Posted by denglishbi on May 21, 2012

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

So last week I was doing a presentation on Self-Service BI with SQL Server 2012 at the TechFuse MN technical conference and I realized that my demo virtual machine was still running SQL Server 2012 RC0.  I was thinking that I shouldn’t do my presentation and demos using RC0, so why not upgrade.  Probably not the best idea to do this just a few days out from a presentation, but I had my fingers cross and just went for it.

I found some guidance from a thread in the forums by Jen Underwood here Upgrade in place RC0 to RTM and decided to go for it.  I did the in-place upgrade and my SharePoint virtual machine with SQL 2012 was still functioning after I completed all of the steps.  Then I moved on to testing my integrated SSRS setup and realized that the Power View reports were not working and neither were my data sources.  The quick fix for that was to go into Central Administration and go into the properties of the Reporting Services application and click OK.  That completed the upgrade process for my shared service and my Power View reports functioned properly after that.

Next was to verify my PowerPivot was still working.  I opened up my PowerPivot files and uploaded them to SharePoint.  The snapshots appeared in the PowerPivot Gallery just fine, but I was unable to work with the files (the slicers did not work, I received error messages as shown below).  I also went into Central Administration and tried to checkout the PowerPivot management dashboard.  In doing this I received the same error message below:


An error occurred during an attempt to establish a connection to the external data source. The following connection failed to refresh: PowerPivot Data

I wasn’t quite sure how to proceed, but after the fix for the SSRS shared service I was thinking that maybe I needed to do the same for the PowerPivot application.  SO I decided that I would try to re-run the PowerPivot configuration tool which is new with the SQL Server 2012 installation.PowerPivot Configuration Tool

The following are the screenshots of running the PowerPivot Configuration Tool with the SQL Server 2012 RTM installation software against my virtual machine that was RC0:








Once that was done I went back into Central Administration and checked out the PowerPivot Management Dashboard again:


Everything was working as expected again and no more error messages! Victory!

That was quite the relief and I was able to do my presentation with my newly upgraded RTM release of SQL Server 2012, not bad for doing an in-place upgrade just a few days prior to the big event.  This included three instances of SSAS (multidimensional, tabular, and PowerPivot), the SQL database engine, SSRS, SSRS SharePoint integrated, SSIS, and DQS as well.

Here is some additional information if you are looking to do the upgrade for DQS – Upgrade DQS: SQL Server 2012 RC0 to SQL Server 2012 RTM.

Posted in PowerPivot, SharePoint | Tagged: , , | Leave a Comment »

Using Perspectives with PerformancePoint Services

Posted by denglishbi on March 28, 2012

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

One of the great features of using PerformancePoint Services in SharePoint Server is the ability to be able to drill down to (or cross drill) and perform additional ad-hoc analysis.  This features is built into the analytical reports, both grid and chart, as well as the decomposition tree.  Here is what the feature looks like with each of these options along with the output in an existing dashboard page in SharePoint:

Analytical Reports (Chart and Grid)

Initial Report Drill Down To menu Final Result
image image image
Sales chart displaying data by Product Category Able to Drill Down To any of the 14 dimensions that are in cube Home Appliance sales data drilled down to Machine Type – unrelated dimension to sales
image image image
Sales grid displaying data by Product Category Able to Drill Down To any of the 14 dimensions that are in cube Home Appliance sales data drilled down to Geography Region Country

Now the one thing you might have noticed when using this feature is that the list of available dimensions (entities) that are available in the list can become overwhelming at times if you are utilizing cubes that include many dimensions and fact tables (measure groups).  It is quite common that when cubes are setup this way that not all of the dimensions are related to each fact table.  Like if we look at this dimension usage design you can see that there are 14 dimensions and 7 measure groups and that there are quite a few instances where the dimensions are not related to the measure groups.  This can produce odd results based on how the measure group is setup in the cube such as the chart results above after the sales data was analyzed by machine type.


In these cases you then have to evaluate whether or not it even makes sense to include the fact tables in the same cube.  That is one option of course when designing your model, another is the ability to limit the items that you present to your users to help limit the scope.  The way you can do this is with the use of perspectives.  A perspective is a way that you can limit the scope of items that are presented to a user to help remove the clutter of items that are available.  This is subset of the cube and can then be exposed to users to select from so that they can quickly perform analysis on just the items that they need to reference.  Perspectives are not an option to use for security purposes, just an option to use to filter the items for analysis.

NOTE: In Analysis Services perspectives are only available as an Enterprise Edition feature (which is also available in the Developer and Evaluation Editions) with SQL Server (2005 through 2008 R2 versions). Now with SQL Server 2012 perspectives are also available in the new Business Intelligence Edition of the product (for both Multidimensional and Tabular) as well as in the new version of PowerPivot for Excel.

Before we explore this let’s take a quick look at the other type of visualization available for analysis that is provided in PerformancePoint that allows you to cross drill, the decomposition tree.

Decomposition Tree

Initial Report Drill Down To menu Final Result
image image image
Home Appliance sales amount broken out by Product Subcategory Able to Drill Down To only the related dimensions in the cube Washers & Dryers sales amount broken out by Channel Name

Notice anything different here? This type of report has built-in logic that only displays the related dimensions available to the measure that you are analyzing.  Pretty cool.

Ok, now back to our initial issue that we saw with the Analytical Reports.  If we look at the cube designer above and go to the perspectives tab we can see that three perspectives (IT Machine, Inventory, and Sales) have been defined for particular areas to focus in on the items that are relevant for their analysis:


So if we go back into PerformancePoint and if we setup our data source to reference the perspective instead of the cube we can then design our Analytical Reports off that data source.  In doing so we will then be able to limit the items presented to our end-users just like the decomposition tree does so that it only displays items relevant for analysis.

Here is an example of the chart initially displayed and the Drill Down To list based on a reference to the Sales perspective instead of the Operation cube in our Analysis Services database.


Now instead of being presented with all 14 dimensions we only see 11 of them.  In this case the Scenario dimension is setup in the Sales perspective and that is needed to evaluate the Sales Quota data.  We can see that in the decomposition tree that it filtered that dimension out.  Now you have the ability to control what is available for users by the use of perspectives.  These can also be used with out tools such as Report Designer, Report Builder, Excel, etc.  When users connect to your database they can select from a drop-down list and connect to a perspective if you have them defined and also if you are using a Edition of SQL Server that supports this.

As I mentioned in the Note above, in SQL Server 2012 perspectives are available now in the Business Intelligence Edition as well as Enterprise.  You can also defined perspectives now in the new release of PowerPivot for Excel as well.


When you are in the PowerPivot Window you will need to enable the Advanced Mode option in the file menu and then you will see the Perspectives button in the Advanced tab of the Ribbon.  This will only be active if you have data imported into the PowerPivot Window.  These PowerPivot files can then be utilized in PerformancePoint and I highlighted the new features in a previous posting (I did not mention perspectives though…) – Using PerformancePoint Services (PPS) with PowerPivot SQL Server 2012 RC0.


As you have seen it can be very helpful to limit the scope of items presented to a user for analysis.  As cubes get larger and more entities are added for analysis along with fact tables it can become quite overwhelming to end-users.  We can help out the users by providing perspectives which can provide a limited scope of items that are needed for analysis and that are related.  This can be very helpful and simplify things for end-users and can reduce confusion.  I would highly recommend that before you dive into creating lots of Analytical Reports that you define perspectives in your models.  The reason for this is because you cannot change the data source an Analytical Report is associated with once you have created it.

Happy analysis!

Posted in PerformancePoint | Tagged: , | 1 Comment »

Using PerformancePoint Services (PPS) with PowerPivot SQL Server 2012 RC0

Posted by denglishbi on February 9, 2012

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

Last year I did a posting on Using PowerPivot with PerformancePoint Services (PPS) 2010. I thought it would be a good idea to do an updated posting with the new release of PowerPivot that will be coming out this year to show the new features that you will be able to take advantage of now.

Before we get started I need to point out that if you setup your SharePoint environment with a fresh install of SQL Server 2012 that you will also need to download and install the Analysis Services ADOMD.NET (version 10.5) data provider from the SQL Server 2008 R2 SP1 Feature Pack.


I had previously tried to reference PowerPivot SQL Server 2012 in the “Denali” CTP3 and in RC0 release and received the following error message when trying to configure the connection string in the PerformancePoint data source setup:

An error occurred connecting to this data source. Please check the data source for any unsaved changes and click on Test Data Source button to confirm connection to the data source.

I was glad that after reporting this that a resolution was found and that simply installing the older version of the ADOMD.NET provider was able to bridge the gap to the new version of the PowerPivot files with PerformancePoint – You Can Use SQL Server “Denali” PowerPivot Models as PerformancePoint 2010 Data Sources.  After you install the version 10.5 release of the data provider you will need to perform a iisreset after the installation to get this working properly in your environment so that you can create the data source in Dashboard Designer.  The previous version of the ADOMD.NET data provider was not needed to connect to the new Tabular version of SSAS, just for PowerPivot.

What’s New?

Okay, so now that we got that covered what are we able to do with the new release of PowerPivot?  Let’s take a look and see what has changed.

New Features Available in PowerPivot (disclaimer: this is based on the SQL Server 2012 RC0 release of PowerPivot) to use with PerformancePoint:

  • Hierarchies! – yes, now we can define hierarchies in our data model such as Calendar with Year, Quarter, Month, and Date levels or Products with Category, Subcategory, and Product levels. In order to add hierarchies you have to use the new Diagram View that is another new feature to see a visual representation of your data model.  In addition to this you can also setup Parent-Child hierarchies as well with some of the new DAX capabilities with the PATH and PATHITEM functions to setup your levels and get at the relationships.
  • Measures and formatting – measures are defined in the PowerPivot Window and part of our model. Another very nice feature that we can do now is set the format for measures that we define in the data model. So if we want to display the sales or profit we can do this in currency or if we want to display gross profit margin we can do that as a percent and we can specify the comma separator and number of decimals as well.
  • Column sorting – in the R2 release of PowerPivot you might have noticed that everything was sorting alphabetically. So if you worked with say days of the week or month names you would have had to come up with some alternate naming conventions to get the values to display in the correct order that we are used to seeing them. Well in the 2012 release we now have the ability to base the sort order of a column off another columns, just like we can do in the Multidimensional Analysis Services development. So if we have a column for month number of year we can use that to properly sort the month names and we no longer have to incorporate the month number into the description that we display in our reports.
  • Key Performance Indicators (KPIs) – you can define these now in your data model, but unfortunately at the time of this post you are not able to import KPIs from Tabular data models.  There is currently an open bug on connect related to this issue – can’t make PerformancePoint scorecard by importing tabular KPIs (feel free to vote this one up).
  • Drillthrough (show details) – there is now drillthrough support for Tabular data models, so you can use the show details options on measures in your Analytical Reports. Granted you don’t have any abilities to customize the drillthrough output in PowerPivot models (Tabular will have this thanks to Greg Galloway and BIDS Helper), but you can see the detail data in the table that makes up the number.


In the new Diagram View we can now right-click on a column in one of our tables and select the context menu option ‘Create Hierarchy’


We can then provide a meaningful name to the hierarchy (something besides Hierarchy1) and drag additional columns down into the hierarchy to add levels for users to provide the navigation path.


Now in this example I already used the RELATED function to pull in the product category and subcategory names into the product table.  I have also hidden the ProductSubcategory and ProductCategory tables from client tools so they will not be visible to the users when the data model is deployed (in the first image you can see that those tables are dimmed out, more transparent than the product table).  Now I might want to go ahead and rename the last item in my hierarchy here so that it is a little more cleaner.


In doing so you can see that in parenthesis I can still see the actual column in my data that it is referencing.  Now I could go ahead and hide the individual source columns that are being represented in our hierarchy.  This would force the users of the model to have to use the hierarchy to navigate the data.  That would be fine in most cases, but if you are going to use this data model with Power View you will want to keep them in the model since the initial release of Power View in SQL Server 2012 will not support hierarchies.  So if you hide them then you will not be able to see those columns or the hierarchy in your model and the users would not be able to properly analyze and report on the data.

In addition to the typical user defined hierarchies I also mentioned there is support for parent-child hierarchies.  So if you have a self-referencing table such as an Employee table that contains EmployeeKey and ParentEmployeeKey columns you can utilize DAX to establish the hierarchy path for each row and then defined the levels like the following:


=PATH(Employee[EmployeeKey], Employee[ParentEmployeeKey])

And once you have that setup you can setup additional calculated columns for each of the levels in the path that can then be used back in the Diagram View to establish the hierarchy just like the Products one above.


=LOOKUPVALUE(Employee[EmployeeName],Employee[EmployeeKey], PATHITEM(Employee[EmployeePath], 1,1))


Measures and Formatting

Another nice feature is the ability to designate the format output of the measures you define.  In the R2 release of PowerPivot you had to do this in Excel where you also created the measures and it was not actually part of the data model.  Well in this release not only is this capability part of the data model, but so are the measures.

First of all you have the ability to create and define explicit measures in your data model now.  There is a new portion in each table in our PowerPivot Window called the Calculation Area.  This is where we can define measures that will then become part of our data model.  Granted measures can still be created in Excel, but in doing this they will not get added back into the data model and exposed in the PowerPivot Window.


Now that we have some measures defined we can go ahead and add formatting to each of these by selecting the measure and using the Formatting section in the Home tab of the Ribbon or right-clicking on the measure and selecting Format… to open up the Formatting dialog box.

NOTE: when using PowerPivot with tools like PerformancePoint, Report Builder, and Report Designer (BIDS) you will need to define explicit measures.  Implicit measures will work in Excel with PowerPivot and in Power View, but not with these other tools.


The Calculation Area is a free form area and you can enter measures in any of the cells and the syntax you use is Measure Name:=[DAX Expression]

In the first screenshot above where I have the Reseller Total Sales measure I could actually take that and enter that in the Calculation Area in another table if I wanted.  In doing so I would have to alter the DAX a bit and make a direct reference to the table name like such – Reseller Total Sales:=SUM(ResellerSales[SalesAmount])

Column Sorting

In the v2 release of PowerPivot we now have the ability to define the way a column is sorted based on another column.  So for example in my Date table if I have a column called EnglishDayNameOfWeek with values Sunday, Monday, Tuesday, etc. and I have another column called DayNumberOfWeek with values 1, 2, 3, etc. I can set the sort order of the first column to be based on the second column.  If we did not do this then the names would simply be sorted alphabetically like they were in v1 of PowerPivot.  In v1 of PowerPivot you could simply sort the values in Ascending order in a PivotTable to correct this, but in Slicers you did not have that ability and would have to typically setup a calculated column to sort the values properly like 1 Sun, 2 Mon, 3 Tue, etc.


Once you have the column selected you can then select the Sort by Column option in the Ribbon.  This will then open up the Sort by Column dialog box where you can specify the column to be sorted and then which column defines how it should be sorted.


You can use this to not only get typical items sorted properly like the days of the week and month names, but you could also use this to define custom report sorting that users would like to see.  So say you have predefined names you use to categorize your customers based on their sales like Platinum, Gold, Silver, Bronze, etc. and this is how you want them displayed on your report (in this particular order).  Well now you can be defining another column in the table that has the values 1, 2, 3, 4, etc. respectively and setup the Sort by Column.

Key Performance Indicators (KPIs)

Defining KPIs in the PowerPivot models is a new feature that is available.  As I mentioned though, currently you are not able to import these into PerformancePoint to create an Analysis Services scorecard like you can with Multidimensional Analysis Services.  As an example though, here is what you can do if you want to set this up so that you can use them in Excel (which could then be displayed with Excel Services in your dashboard) or with Reporting Services (which could also be displayed in your dashboard).

First you need to define an explicit measure like we already did above with Reseller Total Sales.  Once you have this selected you can then click on the Create KPI option available in the Measures section of the Home tab in the Ribbon or you can right-click on the measure and select the option to Create KPI…  This will then open up the Key Performance Indicator (KPI) dialog box.


In the dialog box you can then specify the target to compare the measure to and this can reference another measure in the data model or an absolute value.  Once you have that defined you can then adjust the thresholds and select your icon styles.  Make sure you pick an appropriate icon style that not only has a color option, but also a shape to differentiate it because as I have noted before, there are people that are color blind (so do not just go with the default first icon style selection).

The other thing that you can do is specify some descriptions for the KPI (descriptions are another new feature and these are available on other items such as tables, columns, and measures).  The descriptions are then displayed to the end-user in say Excel when they are working with the PowerPivot field list and hovering over the items.


Once you have this setup you will notice a new icon next to the measure in the Calculation area (and in the Diagram View) which signifies that it is now configured as a KPI.


Just remember, you cannot import these currently into PerformancePoint, so you will still have to manually setup the KPIs.  Hopefully this will be resolved, just watch the Connect item link I provided above and feel free to vote it up as well.

Drillthrough (Show Details)

Not much to say here other than the fact that you can now get to the detail records that make up a particular measure value when displayed in Excel and in PerformancePoint.  So in PerformancePoint if you have the KPIs setup with the Data value calculation instead of the Default or you are displaying the measures in an Analytical Report you will now be able to select the Show Details option and actually display the individual rows that are in that table that make up the value.

The End Result – Dashboard in PPS

Okay, so now we know what the features are we can use, what do they look like in PerformancePoint.  Let’s take a look at that.


And last but not least….


Pretty sweet and I am looking forward to the new feature support.

If you are looking for the downloadable sample files for SQL Server 2012 take a look here SQL Server 2012 Samples ReadMe.

Posted in PerformancePoint, PowerPivot, SharePoint | Tagged: , , , | 10 Comments »

Using Database Images in Tabular BI Semantic Models with Power View

Posted by denglishbi on December 9, 2011

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

Now that the SQL Server 2012 RC0 release is out I wanted to do a follow up posting to my Using Images in Tabular BI Semantic Models with Crescent (now officially known as Power View).  With this release there is now full support for using images stored in the database – binary data type support has now been added to model development in PowerPivot and Tabular Analysis Services projects.

In this example I will use the AdventureWorksDW database sample again that I used in the previous posting.  First I will load the data into a model using SQL Server Data Tools (formerly known as Business Intelligence Development Studio – BIDS) inside a Tabular Analysis Services project.


Nothing new here except for the fact you will notice some changes in the Properties area for the column compared to what it looked like in the CTP3 release where now we have two sections – Basic and Reporting Properties.

If we just go ahead and deploy the project as-is without making any changes what will happen?  Will we be able to start using the LargePhoto column?  Let’s take a look.  If we deploy the project and then setup a BI Semantic Model Connection in SharePoint


And then launch Power View


We will see that the LargePhoto column is not available in our Field List to select from


And yes, now the field list is in alphabetical order! Awesome.

So how do we make this column available?  Let’s go back into the model design.  In order to make this column available we need to do two things.

First we need to set the row identifier property on the Product table and we will do this using the ProductKey column and set that property to True.


Next we need to set the default image property on the LargePhoto column and change this value to True.


Now we deploy our model again to our server and refresh the browser to reconnect to our model connection.  Now what do we see in the field list?


We now see the LargePhoto column and we can use this in our visualizations that we add to the Power View canvas.



Now how do we do this in PowerPivot with the RC0 add-in?  Well this has changed a bit as well now as far as the UI.

First you need to switch to Advanced Mode in the PowerPivot Window


Now with the Product table selected switch to the Advanced tab in the Ribbon and click on the new Table Behavior option in the Reporting Properties section


Now we will get a new dialog box where we will need to set the row identifier and default image properties for our Product Table


Now that we have this set we can upload this to our PowerPivot Gallery and launch Power View


And now we should see the exact same list and options like we had with our Tabular project



And that is the quick update I wanted to post in regards to the change in RC0 for support for database images and to show you how some of the property and menu options have changed.

Enjoy and don’t forget to get your copy of SQL Server 2012 RC0 so you can start testing it out and planning your roadmap change to the next release of SQL Server.

If you are looking for resources and information to the RC0 release check out this great list that Nick MacKechnie put together here – SQL Server 2012 RC News.

Posted in Reporting Services, SQL Server | Tagged: , , , | 1 Comment »

Twin Cities ScarePoint Saturday Follow Up

Posted by denglishbi on November 2, 2011

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

I attended my very first SharePoint Saturday event in Minnesota.  I was extremely impressed with the organization of the event, speaker dinner was fabulous, and the location was very nice.  I was curious to speak to a SharePoint audience versus my typical SQL Server and Business Intelligence audiences.  I was impressed to find out that over half the audience that attended my presentation knew what PerformancePoint was and quite a few were using either PerformancePoint 2007, 2010, or even ProClarity.scarepoint sat

The presentation was going pretty good and we were going through the content at a good pace until all of a sudden out of the blue it was as if someone pulled the plug on my laptop.  My laptop decided to go into hibernation mode and I was not able to wake it back up.  I tried to disconnect and reconnect the projector, unplug the power, etc. and in the end I had to simply shut it down.  This was not a good thing because I had my virtual environment up and running all set to go for my demo as well.  Needless to say this generated some downtime, so I was fielding questions while I tried to get my laptop and environment up and running again to resume my presentation.  Pretty much threw my game off and I would have liked to have spent some more time with my demo to cover all of the features and provide more explanations.

I definitely apologize to the attendees of my session.  I was just in shock and couldn’t believe it.  I was very bummed out and felt awful, just something that you don’t plan on happening when presenting.

I think I probably spent too much time on the history and overview and should have done more demos and gone over maybe the setup of how PerformancePoint is enabled.  Just goes to show you that based on the SharePoint audience there are items that are of more interest and I should have focused on that versus what I am used to doing for a SQL Server and Business Intelligence group.

As with all presentations you get the feedback and learn on how to build off the experience to adjust and make the next one better.

Posted in PerformancePoint, SharePoint, Training | Tagged: , , , | Leave a Comment »

SharePoint 2010 Cumulative Update October 2011 is available

Posted by denglishbi on October 30, 2011

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

UPDATE (11/3/2011): Apparently the documentation that was released is incorrect and should be updated shortly for PerformancePoint Services.  The issue with the context menu is going to be part of the December CU update, so stay tuned.

Just this past week another SharePoint 2010 CU was released.  The one thing that I wanted to point out is that there is a update for PerformancePoint Services (PPS) included in this CU.  What has fixed with PPS?  Additional support for the iPad devices!  There has been a long outstanding issue with being able to utilize the right-click content menu in Analytical Reports on iPad.SharePoint2010 (issue resolution not part of this CU, wait till Dec)

PerformancePoint Services hotfix (KB 2596590)
Issues that this hotfix package fixes

This hotfix package contains several fixes that enable PerformancePoint Dashboard on Apple iPad devices. These fixes provide the following improvements:

  • The fixes enable the “tap and hold” feature so that right-click to open context menus can be accessed. (issue resolution not part of this CU, wait till Dec)
  • The fixes provide several HTML and CSS changes to accommodate inconsistencies in the way that Apple Safari displays dashboards on iPad devices.

This is build 14.0.6112.5000 of the cumulative update package.

So if you have people trying to leverage iPad devices with your PerformancePoint dashboards in SharePoint 2010 go ahead and download the new CU to provide them the fully benefits of the analytical functionality.

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