Create a dashboard from your SharePoint list data using Excel Services and the Excel Web Access web part

If you use SharePoint in any capacity, chances are you’re storing data in SharePoint lists somewhere. Of course once you’ve got data inside lists, someone is going to want to report off of it. In this post we’re going to take a look at how you can create dashboards inside of SharePoint that pull in data from SharePoint lists using Excel Services, web part pages, and the Excel Web Access web part. Now there are several other technologies we could use like Reporting Services, PerformancePoint, PowerPivot, or Power View to create dashboards, but some of those technologies require enterprise licenses for SQL or the PowerBI functionality in Office 365. In this example I wanted to keep it simple, and use technologies that people are more likely to have. (Although you do still need to have a SharePoint enterprise license in order to use Excel Services.) Our basic approach for building this dashboard will be:

  1. Review our sample SharePoint list and test data
  2. Connect Excel to our SharePoint list and build reports
  3. Display our Excel reports via a web part page and the Excel Web Access web part

1 - Review our sample SharePoint list and test data

Let’s start off by taking a look at our SharePoint list that we’ll be pulling data from.

clip_image001

Our SalesTestList is pretty simple. It tracks the sales amount for a given client. It also tracks when the sale happened and what category the sale belongs to. The month and year columns are derived from the SalesDate column using the calculated field functionality, so users only have to enter date information once (when they pick the SalesDate). The month and year column make it easy to roll up the sales amounts for different time periods in our dashboard. The formula we used to create the calculated field columns for Month and Year are as follows:

MONTH: =TEXT(SalesDate,"mmmm")

YEAR: =TEXT(SalesDate,"yyyy")

Several other formulas for calculated columns can be found here: http://yalla.itgroove.net/2012/09/sharepoint-calculated-column-formulas/

Now that we’re familiar with our SharePoint list and test data, let’s take a look at how we can pull this data into Excel.

2 - Connect Excel to our SharePoint list and build reports

The charts we’ll be building for our dashboard will be pulling in data from our SharePoint list via the “From OData Data Feed” option in Excel. To create a single chart, that shows the sales amount by category and month, follow these steps:

  1. Open Excel 2013 and choose “Blank Workbook” as your template
  2. Click on the “Data” tab on the ribbon at the top
  3. In the “Get external data” grouping, click on the “From other Sources” button
  4. Select “From OData Data Feed” from the list of options

    clip_image002
  5. In the textbox labeled “Link or File:” you’ll need to put the URL to the listdata.svc for your site. This is done by simply taking the url to your site and appending /_vti_bin/listdata.svc to it. So for example if the SharePoint site that my list is in has a URL of https://TestSite.SharePoint.com the URL I’ll need to enter is https://TestSite.sharepoint.com/_vti_bin/listdata.svc

    clip_image003
  6. Click the “Next” button on the data connection wizard
  7. If prompted to sign in, use credentials that have access to your list
  8. Choose the list you want to connect to from the available options on the data connection wizard

    clip_image004
  9. Click the “Next” button on the data connection wizard
  10. Click the “Finish” button on the data connection wizard
  11. Choose “PivotChart” on the import data screen then click the “OK” button

    clip_image005
  12. If all has gone well, you should now be looking at a blank pivot chart in Excel. On the far right hand side you should see a window labeled “PivotChart Fields” that has a list of all the columns in the SharePoint list that you’ve connected to. To begin creating our chart, we simply need to start selecting the values that we want to display in our chart. In our example we want to show the user the sales amount by category and month, so we’ll want to select the following fields:

    SalesAmount
    CategoryValue
    Month
  13. Finally we’ll want to move (drag with mouse) the “CategoryValue” Field to the “Legend (Series)” box so that our PivotChart window in the bottom right looks as follows:

    clip_image006

Ultimately our chart should now look similar to the following:

clip_image007

Finally we’ll want to give our chart a name so we can easily differentiate it from any other charts we create. To name your chart complete the following steps:

  1. Select the chart by clicking on it with your mouse
  2. Click on the “Analyze” tab on the ribbon at the top of the screen
  3. On the far left, type your chart name into the textbox labeled “Chart Name” (in my case the name will be “SalesByCategoryAndMonth”. It’s important that you give your chart a name, as we will be referencing this name later when we display our chart in a web part)

    clip_image009

From here all we need to do is save our workbook and upload it to a SharePoint document library for users to view. It is important to keep in mind that the users viewing the workbook will need to have access to view the list that the workbook is pulling the data from if they want to refresh the chart. (You can check out my full post on configuring the data refresh here) With a little more work we could throw together additional charts in Excel and have our dashboard look something like this:

clip_image011

When/if you create additional charts against the same data source, don’t create a new data source within excel for each one. All you need to do to create additional charts using the same data source is:

  1. Highlight a blank cell in Excel
  2. Click on the “Insert” tab in the top ribbon
  3. Click the “Pivot Chart” icon
  4. Select “Use an external data source” in the pivot chart wizard
  5. Click the “Choose Connection” button in the wizard
  6. Select your existing connection from the “connections in this workbook” section

    clip_image013

Once you’ve got all the charts you need for your dashboard, simply upload your workbook to a SharePoint document library.

3 - Display our Excel reports via a web part page and the Excel Web Access web part

Now that we’ve got our Excel workbook pulling in data from our SharePoint list, and the appropriate charts built, the next thing we’re going to do is build a web part page that we can use to show the charts. Some might argue that this is somewhat unnecessary, and that the user could just open the Excel workbook to view the dashboard. That solution would definitely work, but there are a few reasons why you might want to display the charts in a web part page. For starters, when you open a full workbook in Excel services, the entire browser pretty much turns into Excel, and you lose all the SharePoint navigation links. Another reason might be that you want to show other SharePoint objects on the page in addition to the charts. Whatever your reasons are, here’s how we can go about displaying our charts on their own web part page:

First things first, let’s create a new web part page by following these steps:

  1. Navigate to the /pages directory of our SharePoint site (for instance if your site is https://TestSite.sharepoint.com your pages directory should be at https://TestSite.sharepoint.com/Pages)
  2. Click on the “Files” tab on the top ribbon
  3. Click the “New Document” dropdown
  4. Click “Page”

    clip_image014
  5. Give your page a Title, Description, and URL Name in the available text boxes
  6. Select “(Welcome Page) Blank Web Part page” as the “Page Layout”

    clip_image016
  7. Click the “Create” button

Now that we’ve create our web part page, we need to navigate to it (https://TestSite.sharepoint.com/Pages, then click on the link for the page you just created) and edit the page to add the Excel web part. To do all of that, follow these steps:

  1. With your new web part page open, click the “Gear” icon at the top right hand corner of the page
  2. Select “Edit Page” from the dropdown

    clip_image017
  3. On the new ribbon at the top of the screen click the “Insert” tab
  4. Click the “Web Part” button
  5. Select “Business Data” from the Categories window
  6. Select “Excel Web Access” from the Parts window
  7. Click the “Add” button on the far right of the window

    clip_image019

Once we click the Add button, we should see our new “Excel Web Access” web part on the screen as shown below.

clip_image021

To finish configuring the web part to show the Excel chart we created earlier, complete the following steps:

  1. In the Excel web part, click the link labeled “Click here to open the tool pane”
  2. In the new menu on the right hand side of the screen, locate the textbox labeled “Workbook” at the top of the screen. Use the adjacent browse button to select your uploaded Excel workbook
  3. In the “Named Item” textbox, type the name for your specific chart (naming your chart in Excel was covered in section 2 of this walkthrough). It’s my experience that using the browse button for this textbox usually doesn’t work. You just have to type the name of your chart in manually, so be careful and make sure you type it correctly.

    clip_image022
  4. Scroll to the bottom and click the “Apply” and then the “OK” Button

    clip_image024

With any luck, you should now be seeing your chart displayed in the web part. Keep in mind that there are a ton of options in the window we used to configure the web part to point to our chart. You can control what controls show up for the user, the size, the title etc. I’ll leave it to you to play around with all of those options. To get the options window back, simply click on the web part and look in the far right hand corner for a dropdown triangle, click it, then select “Edit web part”.

Next we’ll want to save and publish our web part so everyone can see it. To do that, follow these steps:

  1. In the upper right hand corner of the screen, click the “Save” button
  2. Once the page is saved, there will most likely be a yellow alert bar near the top of your screen, click the “Check it in” link
  3. Add any comments you want to on the “check in” window then click the “Continue” button
  4. Now you’ll most likely have a yellow alert bar near the top of your screen telling you the page is not published. Click the “Publish it” link in the yellow bar

That’s it! You’re showing SharePoint list data on your web part page. Now if you wanted to get a little more elaborate you could add the rest of your charts and mess with some of the size controls to get something like this:

clip_image026

Or even crazier still you could start to add filters/parameters to the screen, but that’s another post entirely. At any rate, we can now share our SharePoint list data in chart form. Hope this was helpful, and happy reporting/dashboarding!

How to create filters for external data columns in SharePoint lists

If you have a custom SharePoint list that uses columns that are of the type “External Data”, you may have noticed that when you’re adding a new item to the list (and more specifically when you’re picking the external data to associate with your new item), that there’s a filter dropdown when you’re browsing your external data. By default the dropdown only contains <Select Filter> and if we click on the dropdown button, we’ll see that no filters are actually available.

clip_image001

If you only have a few values available in your external content type (as seen in the image above) not having a filter isn’t really that big of a deal. However if your external content type contains a large amount of data, not having filters available can really be an inconvenience. In this walkthrough, we’ll take a look at how you can create filters for your external content types so your users are able to quickly locate the data that they need. In this particular example we’ll be using SharePoint 2013 and SharePoint Designer 2013.

This walkthrough assumes you’re already somewhat familiar with how to create an external content types using SharePoint Designer. If you’re not all that familiar with external content types you might want to check out my previous walkthrough covering the basics. You can find that walkthrough here: http://tavislovell.com/using-bcs-and-external-content-types-in-sharepoint-to-update-sql-tables/

The first thing that we’ll want to do is open the site that contains our external content type in SharePoint designer. To do that follow these steps:

  1. With SharePoint Designer open, click the “Open Site” button

    clip_image002
  2. In the “Site Name” textbox, type the full URL to your site and click “Open”. In my case I’m going to create the external list on a sub-site named “TavisBIDemo”

    clip_image003

Once connected you should see a screen similar to the following:

clip_image005

Now that we’ve got our site open in SharePoint designer, the next thing we want to do is edit the “Read List” operation for our external content type. The read list operation is where we’ll actually need to add our filter. To edit the read list operation and add a filter complete the following steps:

  1. In SharePoint Designer, click on “External Content Types” under the “Site Objects” window
  2. Double click the existing External Content Type that you want to add the filter to
  3. Open the existing “Read List” operation by double clicking on it under the “External Content Type Operations” window

    clip_image006
  4. On the “Operations Properties” screen of the wizard that comes up, click next
  5. You should now be on the “Filter Parameters” screen. This is where we’re going to be configuring our filters. In our case, we’re going to make a filter for the “First Name” column that exists in our ECT dataset. Click the “Add Filter Parameters” button

    clip_image008
  6. In the “Properties” section on the right hand side of the screen. Use the “Data Source Element” dropdown to choose the column you want to create the filter for (in my case, “FirstName”)

    clip_image010
  7. In the same properties window, click the “(Click to Add”) link next to the “Filter:” label
  8. In the “New Filter” textbox, type a name for you new filter (This will be the text that the users see when choosing the filter, so you’ll want to name it something meaningful
  9. You can adjust the additional “Filter type” and “Operator” dropdowns to suit your particular need. In my case I will also be checking the “Ignore filter if Value is:” option and selecting “NULL”

     clip_image011
  10. Click “OK” on the “Filter Configuration” window
  11. Since I chose the “Ignore filter if Value is:” option and “NULL” for my filter, I’m also going to set the default value for my filter to NULL. This will cause all possible values to show up if the filter has not been applied

    clip_image013
  12. Click the “Next” Button on the “Filter Parameters” window
  13. Click the “Finish” Button on the “Return Parameters” window
  14. Save the save button at the top left hand corner of SharePoint Designer to save your changes

That should do the trick. If we go back to our list with the ECT (refresh the browser) and bring up the picker for our ECT data we should see our “First Name” filter available. Not only that but all values should be listed since we said to ignore the filter if the value was null, and the default value is null. If we type a value into the filter and click the search button, we should see our results filtered as seen below.

clip_image014

That should do it. Congratulations you’ve created a filter for your users to quickly locate the data they need when picking from an ECT! Hope this helps.

Why can’t I sort or filter the external columns in my list?

The scenario I’ve seen a few times is that you have a list that you’ve added a few “external data” columns to. Everything is looking great until you click on the column header of one of the external columns to sort or filter it, and……nothing. The menu doesn’t come up when you click on the external column, and the header doesn’t even highlight when you scroll over it.  (click picture for detail)

clip_image002

The good news is that this can be fixed. The problem seems to be that the “Type” property of the external column is set to “Note” instead of “Text”. The bad news is that the only way I know of changing the type property for the external columns involves downloading and running some software on your SharePoint server.

The program you’ll need to download is called “SharePoint Manager” and can be downloaded for free from the codeplex website at http://spm.codeplex.com/

Once you run the program you should be presented with a screen that looks like this:

clip_image004

I must caution you to be very careful in SharePoint manager and not start randomly changing things as you will more than likely break something.

What we need to do is navigate to the particular list and columns that are not filtering and sorting. In my case the list exists on a sub-site and drill down path looks something similar to this:

[Farm] » [Services] » [SPWebServices] » [Web Applications] » [your web application] » [Sites] » [your site address] » [AllWebs] » [Your subsite)] » [Lists] » [Your List] » [Fields] » [the field not filtering/sorting]

With the problematic field highlighted, locate the “Type” property in the Properties window. It will most likely be set to “Note”. Change it to “Text”.

clip_image006

Repeat that process for any additional ECT columns that aren’t sorting, then click the save icon up at the top left hand corner.

*Note, do not change the primary ECT column (usually has a “Type” of “Invalid”). If you change that value to text, it will break your list…or at least it did for me when testing.

After making the changes, you should now be able to filter on the ECT columns that were previously not working.

2014-07-17 14_59_36-Windows 7 x64 - VMware Workstation

That’s all for this post, hope this saves someone some time.

The date and time in my external content type, BDC/BCS list are incorrect (off by x hours)

I’ve had a few customers have problems with the time being incorrectly listed when displayed in an external list. Typically the date and time in the SQL table they’re connecting to will display one time, however the external list will display the time as + or - a few hours from what it actually is in the SQL Table. This could potentially cause the wrong date to be displayed if the time stored in SQL Server is set to 00:00:00.0000 and your time is being adjust -1 hour. The reason behind this problem is that the time is being converted to Coordinated Universal Time or UTC. So the ultimate solution is to prevent that conversion to UTC from happening.

So for example our scenario is this:

I have a datetime field in my SQL Server table that has a value of ‘2014-06-03 00:00:00.000’

clip_image001

My External list in SharePoint however is showing a value of ‘6/2/2014 8:00 PM’

clip_image002

To correct this problem we’ll need to perform the following actions: (you will need access to Central administration to perform this fix)

  1. Open the SharePoint site that has the External Content Type in SharePoint Designer
  2. Navigate to “External Content Types” in the Site objects
  3. Highlight the external content type you’re having problems with by clicking on it (don’t click the actual text though)
  4. Click the “Export BDC Model” button at the top of the screen

    clip_image004
  5. Download the file to your desktop (name it the same name as your external content type)
  6. Once you’ve confirmed that bdc model file has been saved, delete the existing problematic external content type by clicking the “Delete” button located right next to the export button you clicked in step 4 (I realize this is somewhat a leap of faith, but we’re going to re-import the bdc model in a minute, and if the model already exists you’ll get an error.
  7. Make a 2nd copy of the BDCM file (as we’re about to alter it, and you just deleted the original version from SharePoint, and I don’t know you well enough to trust that you’ll get this right the first time) :P Seriously though, make a 2nd copy in case you mess up.
  8. Open the BDCM file in Notepad
  9. Do a find (CTRL + F) for the date column you were having problems with (in my case it was CreatedDate)

    clip_image005
  10. A few lines down from where you find your column, you should see something that looks similar to the above xml. We need to change the “UTC” part (highlighted yellow in the XML above) to be “Local” (highlighted yellow in the XML below)

    clip_image006
  11. You will most likely need to make this change multiple places in the XML (depending on how many operations there are in the model) so you might just want to do a search for “UTC” through the whole document to make sure you get them all changed to “Local”
  12. Save the file
  13. Open SharePoint Central Administration
  14. Click on the “Manage service applications” link under the “Application Management” section
  15. Click on the “Business Data Connectivity” Service Application to open its properties window
  16. Click the Import button at the top left hand corner of the screen

    clip_image007
  17. Browse to your modified BDCM file and then click the “Import” button

    clip_image009
  18. If all has gone well you should see a message similar to the image below, Click the “OK” button

    clip_image011
  19. Navigate to your SharePoint external list that was showing the incorrect date/time and refresh your browser. You might have to wait a minute or so, and go through a few refreshes before the changes take place. I immediately went to the list and refreshed it as I was making this tutorial and I was confronted with an error at first, but I waited a minute and then refreshed it again, and everything was working, and the created date column was showing the correct date/time.

Hopefully this save someone some time and frustration.

Using BCS and External Content Types in SharePoint to Update SQL Tables

Occasionally I get a request to setup an external list in SharePoint that links back to a SQL Server table. Depending on the scenario, the goal might be to just display data from a SQL table in a SharePoint list so (so users can only view the data) or they might want the ability to edit the data in the list and have those edits pushed back to the SQL table. In either case, setting up an external list that pulls/edits data from a single table isn’t all that difficult, and in this walkthrough we’ll take a look at how it’s done.

This walkthrough uses SQL Server 2012, SharePoint 2013, and SharePoint Designer 2013. If you don’t have SharePoint Designer, you can download it here: http://www.microsoft.com/en-us/download/details.aspx?id=35491

Almost all of the work we’ll be doing will be inside SharePoint Designer. With that in mind let’s open up the designer and connect to the site we want to add our external list to. To open your site in SharePoint Designer, follow these steps:

  1. With SharePoint Designer open, click the “Open Site” button

    clip_image001
  2. In the “Site Name” textbox, type the full URL to your site and click “Open”. In my case I’m going to create the external list on a sub-site named “TavisBIDemo”

    clip_image002

Once connected you should see a screen similar to the following:

clip_image004

Now that we’ve got our site open in SharePoint Designer, we’re ready to start creating our external list. Creating an external list boils down into 4 main steps as follows:

  1. Create an external Content type
  2. Create a data source for the external content type
  3. Create operations against the data source (read/edit etc)
  4. Create the list and forms

STEP 1 - Create an external Content type

The first thing we’ll need to do is to create a new external content type in our site. To do that, follow these steps:

  1. In the Site Objects pane on the far left, click on “External Content Types”

    clip_image005
  2. On the ribbon at the top of the screen, click the “External Content Type” button

    clip_image006
  3. You should now have multiple new sections in the center of your screen. Locate the section titled “External Content Type Information” and click on the “New external content type” link next to “Name” and enter a new name for your external content type. (I’ve named mine “TavisTestBCS” in the image below)

    clip_image007

Our external content type has now been created, although SharePoint designer won’t allow us to save it until we define operations in step 3. That’s ok though, just don’t close down SharePoint designer

STEP 2 - Create a data source for the external content type

The next thing we need to do is create a data source that points to the SQL Server table that we’re wanting to surface via our external list. To create the data source follow these steps:

  1. Click the link titled “Click here to discover external data sources and define operations” (shown at the very bottom of the previous image)

    Note, after you click the link in step 4 your screen will change. This is the “Operations Design View” screen. To toggle between this screen and the previous screen we were on, you can use the “Summary View” and “Operations Design view” buttons located at the top left corner of SharePoint Designer.
  2. Click the “Add Connection” button
  3. In the External Data Source Type Selection window choose “SQL Server” as the Data Source Type
  4. Enter the Database Server name for the SQL Instance you want to connect to
  5. Enter the Database Name for the SQL database that contains the table you want to connect to
  6. In the “Name (optional)” textbox, enter a name for this datasource
  7. Choose “Connect with Impersonated Windows Identity” from the available authentication options
  8. Enter the Secure Store Application ID of an unattended account that has access to the database and table you want to connect to.

    To complete step 8, you need to make sure that your Secure Store Service Application in SharePoint has been configured, and has an unattended service account that has the appropriate permissions to access the database and table you’re trying to connect to. The secure store is accessed through the SharePoint Central Administration tool, so you may need to contact your SharePoint administrator. Once that account is in place, you just need to grab the application ID for that account inside of the Secure Store.

    clip_image008
  9. Click “OK”

Once the data source has been created, you should see it in the “Data Source Explorer” window as shown in the image below.

clip_image009

STEP 3 - Create operations against the data source (read/edit etc)

Now that we’ve got our data source create, it’s time to create some operations for our external content type. There are several operations available, however in the interest of simplicity for this example we’re going to create them all at once accepting most of the default options. To create the necessary operations follow these steps:

  1. In the “Data Source Explorer” window, click the “+” to see available folders
  2. Click the “+” on the “Tables” folder to see the available tables
  3. Right click on the table you want to show/edit in your list and then click “Create All Operations” to start the operations wizard

    clip_image011
  4. Click “Next” on the initial screen

     clip_image013
  5. Click next on the “Parameters” window

     clip_image015

    At least one of the columns in your table should be a marked as “Map to Identifier” as shown in the image above. If the table you’re creating the operations on already has a primary key set in SQL Server, then that primary key column will most likely already be listed as the identifier. If not, you will need to determine which column in your data should be the unique identifier/primary key and mark it as the identifier by placing a check in the “Map to identifier” field.
  6. Click the finish button on the “Filter Parameters” window

     clip_image017

The filter parameters window can be used to allow users to filter the data shown in the list. In our simple example we’re not using any filters/parameters, but it is highly recommended if your table contains a large amount of data.

Once you click finish, you should be able to see all the operations that were created under the “External Content Type Operations” section of the screen. (Shown highlighted in yellow below)

clip_image019

STEP 4 - Create the list and forms

Now we’re in the home stretch! All that’s left to do is create the actual list and list forms. To finish creating your external list, follow these steps:

  1. In the ribbon at the top of your screen, click the “Create Lists & Form” button

     clip_image021
  2. If asked to save your external content type, click yes
  3. In the “List Name” textbox type in whatever you want your list to be called
  4. Place a checkmark in the box labeled “Create InfoPath Form”

    clip_image022
  5. Click the “OK” button

That should do it! If you browse out to your SharePoint Site and look under the “Site Contents” you should now see your new external list.

clip_image023

For the sake of brevity we skipped over some of the more advanced stuff like filters, or showing data elements from multiple tables, or even calling SQL Stored procedures to update, or insert rows into your table(s). So there’s still more to learn, but this walkthrough get you up and running with a simple external content type connected to a single SQL Server table. Hope you found it helpful. Let me know in the comments.

Using PerformancePoint 2013 and SSAS roles without Kerberos (configuring EffectiveUserName)

If you’ve spent any time with PerformancePoint, you’re probably at least somewhat familiar with the different authentication options that are available when creating a data source. Any time you create a new data source in PerformancePoint Dashboard Designer, you’re confronted with 3 choices.

  1. Unattended Service Account
  2. Use a stored account
  3. Per-user Identity

clip_image001

With the first two, there is also an option to pass the authenticated users name in the connection string using the CustomData property. I’ve covered how to use that option previously here: http://tavislovell.com/how-to-configure-dynamic-security-in-analysis-services-and-performance-point-2010-without-kerberos/

What most people REALLY want is the ability to create and leverage roles in their Analysis Services cube for different sets of users. If you want different users to authenticate to different roles within your SSAS cube, you’ll need to use the “Per-user Identity” option when creating your data source. The bad news is that the “Per-user Identity” option comes with its own set of obstacles you need to overcome before it works.

The main problem with using “Per-user Identity” is an issue called “The Double Hop” issue. If you are unfamiliar with the double hop issue, I highly suggest you watch the following video where it is explained in very simple terms: http://go.microsoft.com/fwlink/?LinkId=218209, but the short version is simply that by default, credentials can’t be delegated more than one server away (one hop).

There are a few ways to get around the double hop issue. Most commonly, an administrator will configure Kerberos in the environment, which allows the delegation of credentials between computers more than one hop away. This is a great option if you have the appropriate security credentials (I believe you need domain admin rights) and know how to configure Kerberos. For the uninitiated, Kerberos is not an easy setup.

HOWEVER, if configuring Kerberos in your environment sounds more than you’re ready to take on, there’s good news! In PerformancePoint 2013 we can leverage the new “EffectiveUserName” functionality that will allows us to use the “Per-user Identity” option without having to configure Kerberos! AND it’s pretty easy to setup. In order to use the EffectiveUserName functionality we’ll need to accomplish the following:

  1. In SharePoint Central Administration, you need to enable the “use the EffectiveUserName connection string property instead of windows delegation” option for the PerformancePoint service application.
  2. The application pool account that is running PerformancePoint will need to be given admin rights on the SSAS instance you want to connect to.
  3. The “Claims to Windows Token Services” will need to be running in SharePoint Central Administration.
  4. Create a roll in your cube that gives your test user(s) the correct access to the cube….otherwise they’ll just be denied access.
  5. When creating your PerformancePoint data sources, choose the “Per-user Identity” option.

For those still needing a little more guidance, the remainder of this post will walk through accomplishing the 5 requirements above and ensuring that the EffectiveUserName functionality is working in our environment.

STEP 1 - Enable the “Use the EffectiveUserName connection string property instead of Windows delegation” option.

To enable this property, you’ll need to perform the following actions:

  1. Open SharePoint Central Administration
  2. Click on the “Manage service applications” link (under the “Application Management” heading)
  3. Locate and click on the PerformancePoint service application
  4. Click on the “PerformancePoint Service Application Settings” link
  5. Locate the checkbox labeled “Use the EffectiveUserName connection string property instead of Windows delegation” and place a check in it.
  6. Scroll down to the bottom of the screen and click the “OK” button

STEP 2 - grant the PPS application pool account admin rights on the SSAS instance

In this step we need to first find out what application pool the PerformancePoint service application is using, and what account that application pool is running under. Once we know what account is being used, we simply need to add it as an administrator on the SSAS instance we’re trying to connect to. First let’s find what app pool and account is being used by following the steps below:

  1. Open SharePoint Central Administration
  2. Click on the “Manage service applications” link (under the “Application Management” heading)
  3. Locate and highlight (do not click on the actual link/text) the PerformancePoint service application
  4. Click the “Properties” button on the ribbon at the top of the screen
  5. Scroll down to the bottom of the page until you see the “Application Pool” Section
  6. Make note of the application pool being used, we’ll need to remember this name while determining what account this app pool is running as
  7. Click the “Cancel” button at the bottom of the screen

clip_image003

Now that we know what application pool PPS is using, next up we’re going to determine what account the app pool is being run as. Typically we can’t just look in IIS because the actual name of the App pool is probably a GUID rather than the name we saw listed previously. There are a few different ways we can still see which account the app pool is using, by issuing a PowerShell command, or by using Central Administration. To use PowerShell, simply open up a terminal window and issue the following commands.

Add-PSSNapin microsoft.sharepoint.powershell

Get-SPServiceApplicationPool

PowerShell should return the name of each application pool and a related “ProcessAccountName”. It’s the “ProcessAccountName” that you’ll want to add as an administrator to your SSAS instance. If Powershell is a bit too much for you, then you can find the account being used by following these steps in Central Administration.

  1. Open SharePoint Central Administration
  2. Click the “Configure service accounts” link (under the “Security” heading)
  3. Locate the application pool in the first dropdown (*note the application pool name may be prefixed by “Service Application Pool - “)

    clip_image004
  4. Make note of the Account listed at the bottom of the screen
  5. Click the “Cancel” button at the bottom of the screen

Now that we have our account, all we need to do is add it to our SSAS instance as an administrator. To do that complete the following steps:

  1. Open SQL Server Management Studio
  2. Connect to your Alalysis Services (SSAS) instance
  3. In the Object Explorer, right click on the instance and select properties to open the “Analysis Server Properties” window
  4. Select the Security page
  5. Use the “Add” button to add the app pool account
  6. Click “OK”

STEP 3 - Ensure that “Claims to Windows Token Services” (c2wts) is running

If you’re getting errors similar to the following:

“Dashboard Designer could not connect to the specified data source. Verify that either the current user or Unattended Service Account has read permissions to the data source, depending on your security configuration. Also verify that all required connection information is provided and correct.”

Chances are c2wts isn’t running in your environment. Either way, we’ll need it to be running for our EffectiveUserName to work. To ensure c2wts is running, follow these steps:

  1. Open SharePoint Central Administration
  2. Click on the “Application Management” link heading
  3. Click on the “Manage services on server” link
  4. Locate the “Claims to Windows Token Service” service, and ensure that the status is “Started”. If it is not started click “Start” button under.
  5. If you had to start the service, run an iisreset or reboot the server

STEP 4 - Create an SSAS role for your test user

Now that our SharePoint farm and datasource are configured to use the EffectiveUserName, you’ll have to grant users access to your cube by creating a role in the SSAS database and adding your users to that role. In our case we’re going to add the user “contosoo\test” to the role. If you don’t have a test account to use, either create one or have your Active Directory administrator create one. To create a simple role with read access follow these steps:

  1. Open SQL Server Management Studio
  2. Connect to your SSAS instance
  3. Click the “+” next to the SSAS database that you want to give users access to
  4. Right click on the “Roles” folder and select “New Role…”
  5. Select the “Membership” page on the “Create Role” screen
  6. Use the “Add” button to add your users/test user

    clip_image006
  7. Select the “Cubes” page on the “Create Role” screen
  8. Locate the cube you want to grant the user access to and select “Read” for the “Access” Dropdown

    clip_image008
  9. Click the “OK” Button

STEP 5 - Set your PerformancePoint Datasources to use the “Per-user Identity” option

Now that we’ve everything configured, and our roles created, all that’s left to do is set our PerformancePoint data sources to use the “Per-user Identity” option when connecting to our cube. Complete the following steps to configure your data sources:

  1. In SharePoint, open the “PerformancePoint Dashboard Designer”
  2. Note who you are logged into the Dashboard Designer as. This information is displayed at the very bottom left hand corner of the dashboard designer. This account must be in the SSAS role we create earlier in order to be able to authenticate to the cube.

    clip_image009
  3. Create a new “Analysis Services” data source, or open an existing one
  4. Enter the appropriate server, database, and cube information
  5. Choose “Per-user Identity” as the Authentication method

    clip_image010
  6. Click the “Test Data Source” button

If all has gone well, you should see that your connection has been successful.

clip_image012

That’s really all there is to it. Now you can add additional SSAS roles and have all your AD users/group(s) authenticate into the proper role when they visit your PPS dashboard. Quite an easy setup when compared to Kerberos. Good luck and happy reporting!