The Microsoft PerformancePoint Team Blog

Publishing PerformancePoint Server in an Extranet - White Paper Now Available

Now available is a white paper outlining the technologies and steps required to publish PerformancePoint Monitoring Server dashboards in an Extranet.  You can download this white paper from:

http://go.microsoft.com/fwlink/?LinkId=151630

PPS Planning - How to display and launch assignments from SharePoint

SharePoint has rapidly become the preferred platform for team collaboration in the enterprise. It provides a central place for information, tasks, documents and much more of the business related content that users need to access every day.

One of the common requests we hear from customers is that they want to display PerformancePoint Planning assignments in SharePoint and allow users to launch the assignments from there. Assignments can be accessed via the Excel Add-in and email notification can be enabled for users to get notified, but there is no out of the box support for the SharePoint scenario.

We have released a code sample that demonstrates how you can display PerformancePoint assignments in SharePoint via SharePoint lists.

The sample uses the SharePoint list infrastructure to display PerformancePoint assignments in SharePoint and enable users to launch them in the Excel Add-in from there. The sample includes the source code and documentation on how to use and deploy the solution.

You can access the sample from here:  http://code.msdn.microsoft.com/PPSP2007Assignments

The sample includes 2 projects:

• A standalone console application that connects to the PerformancePoint application database (AppDB) and reads the assignments out from open cycles. Then it connects to SharePoint and creates/updates/deletes list items from a SharePoint list, securing the list items with the same security enforced by PerformancePoint Add-in for Excel.
  
  
• A web project containing an ASP.NET page that is used to launch PerformancePoint Add-in for Excel for a specific assignment. This page is used by the SharePoint list items.

This is a different approach from other solutions that I've seen to achieve this. While most solutions focus on creating a web part to show the assignments, this sample uses a regular SharePoint list for that. The good thing about this is that all the list features (like sorting, grouping, filtering, creating views on top of the list, and so on) are available for both administrators and end users to customize the way assignments are displayed. Also, the interface that users see for working with the assignments is no different than other SharePoint lists. Trying to develop this functionality in a custom web part would be a huge task.

As with any sample, there are things that can be customized and extended. Some of the ones I can think of are:

• Expose more fields: Right now, the console application only copies some of the assignment fields to SharePoint. If more fields are needed, they can easily be added.
• Convert the launch link into a list Custom Action: Right now, the link to launch the assignment is created using a link column, but with some work it can be easily converted into a SharePoint list custom action. This will make the launch link more SharePoint-like. There are many samples online on how to create custom actions.
• Add a look and feel: The ASP.NET page used to launch the assignments can be customized to match the company's look and feel (or any other desired look and feel).
• Add/Modify views: Add your own customized list views and customize the default view. Add grouping, sorting, filtering and use as many list features as you need.

Give it a shot.... I'm sure you will like it!

Pablo Barvo - MSFT

Creating PPS KPIs based on SSAS KPIs

There are two ways to create KPIs in PerformancePoint Server based on SSAS KPIs.  The first is to use the Scorecard wizard to import those KPIs into PPS.  This will create a new scorecard containing only those imported KPIs.  I wrote a post on this some time ago.

If you want to add SSAS KPIs to your existing scorecards however, you can also create individual PPS KPIs based off of SSAS KPIs through the dashboard designer as well.  It does require using some MDX expressions to create those KPIs.  First, you will want to create a new blank KPI.  Select the SSAS cube containing your KPI as your data source.

In the Data Mapping column rather than selecting a measure, you will enter an MDX expression as follows for the Actual value:

Expression:

KPIValue("Gross Margin KPI")

Screenshot:

For the Target Data Mapping, enter the following:

Expression:

KPIGoal("Gross Margin KPI")

Screenshot:

Add a new Target for Trend and map as follows:

Expression:

KPITrend("Gross Margin KPI")

NOTE: When importing your KPIs through the scorecard wizard you automatically get the correct threshold settings imported and the correct images imported for your Target and Trend.  When you create these manually, you will have to select the indicator and enter your thresholds for both the Target and Trend columns. 

To set the threshold on the Target column, select "Increasing is Better" for the scoring pattern.  Select "Band by stated score (advanced)" for the banding method.

Select the indicator you want to show. Then, when specifying the data mapping for the threshold enter the following expression and select to "Use MDX tuple formula":

Expression:

KPIStatus("Gross Margin KPI")

Next, don't forget to set your threshold bands, for a typical 3 state indicator it would look as follows:

Recall, that the status in an SSAS KPI will always be between 1 and -1.

To set the threshold on the new Trend Target column, select "Increasing is Better" for the scoring pattern.  Select "Band by stated score (advanced)" for the banding method.

Select the indicator you want to show. Then, when specifying the data mapping for the threshold enter the following expression and select to "Use MDX tuple formula":

Expression:

KPITrend("Gross Margin KPI")

Next, don't forget to set your threshold bands, for a typical 5 state arrow indicator would be as follows:

Also, don't forget to set the calculation column on your new Trend target to be "Default".

Alyson Powell Erwin

alysonp@microsoft.com

PPS Planning - How to run a Currency Translation Job

PPS Planning allows the user to create a Currency Translation Job in order to facilitate a model's data to be converted to any currency.

In order to accomplish this there are certain criteria that a model must adhere to.

1. The model must be a Financial type of model.  Either a Financial Model with Shares or a Financial Model without Shares
2. You need to add the Currency dimension to the model
3. The Exchange Rate Model must be linked in as an assumption model
4. The Time granularity of the Exchange Rate model must be the same as the granularity of the model that you want to convert
5. The model property Default Currency can optionally be set to one of the currency members in your model.  If it is, then this currency will be used to triangulate an exchange rate if a direct rate is not specified in your Exchange rate model for the period(s) that need to be translated.
6. Enter your exchange rate data in the Exchange Rate Model for the exchange rate members AVE (this rate is used to convert the Income Statement) and CLO (Used to convert the Balance Sheet).  You can also fill in the other rate type members if you want to customize your currency rules or if you are using the Flow dimension in your model.

 

This report shows the data in the Exchange Rate model that will be used to convert the fact data in our model for

• Scenario = Actual
• Time = Q2 2004
• Destination Currency = USD
• Source Currencies = {EUR, GBP, CAD}
• Exchange Rate Types = {AVE, CLO, OPE, HIST}

 

 

 

Now I can run a currency conversion job to convert my data.

 

Using the above job properties will have the net result of converting all of fact data for my Budget model using the following job properties

• Target Model = Budget
• Entity = Resorts (The job will convert all of the fact data for entity Resorts and all of its descendants)
• Scenario = Budget
• Reporting Currency = USD (Convert all applicable entities data to USD.  If the default currency of the desired entities is already USD then it will do nothing for those entities)
• Start Period = Q 2 2004
• End Period = (Since this is blank, then it will only convert the data for the one period Q 2 2004)
• Exchange Rate Start Period = Q 2 2004 (If this had been left blank it would use the Start Period which in our case is also Q 2 2004.  We could have used the Exchange rates from another period)
• Exchange Rate End Period = (Since this is blank it will be the same as the Exchange Rate Start Period - Q 2 2004)
• Exchange Rate Scenario = Actual (If this had been left blank, it would use the same scenario for the Exchange Rates as the parameter Scenario.  In this case it would have used Budget, but for this example we wanted to convert the Budget data using the Actual Exchange rates)

Dashboard Basics Courses Now Available

There are two dashboard basics courses now available online at Office Online for your users:

PerformancePoint Monitoring and Analytics: Dashboard basics I

PerformancePoint Monitoring and Analytics: Dashboard basics II

Please enter any feedback you have on these courses as well as any additional topics you might like to see in the future, via the Feedback link

PPS Planning - Management Reporting Consolidation Model Properties Explained

There are 3 model properties that deal with executing a consolidation for a Financial Model without Shares.  Let’s see what they are and how they are used.

Suppose we have the following entity hierarchy

North America

                                US

                                Canada

                                Mexico

 

The first model property we will talk about is called Management Consol Elim. 

If this model property is set to TRUE then any intercompany transactions between US, Canada and Mexico will be eliminated and written to the Elimination Business Process Member of the parent entity, which in this case is North America.  If this model property was set to FALSE then the elimination result will be written to the Elimination Business Process Member of the originating entity (either US, Canada or Mexico in our case).

 

The second model property we will talk about is called Consol balancing account Bal Sheet.

This model property should be set to an account where the results of all intercompany eliminations for balance sheet accounts for the consolidation will be written.  This account must be a non-intercompany, leaf level balance sheet type of account.  The reason for this model property is to keep everything double sided and balanced.  If the debit/credit property of the account to be eliminated is the same as the debit/credit property of this model property, then the value in this account will be the same sign as the original data, otherwise it will be negated. 

 

The third model property is called Consol balancing account Profit Loss.

This model property is identical in functionality to the above model property except that it is used when eliminating P&L accounts during the consolidation.  This account must be a non-intercompany, leaf level non-balance sheet type of account. 

 

Displaying Dates in a Scorecard

It frequently comes up that you may want to show a date in your scorecard, either as an actual value, a target value or just as an extra piece of information such as when was this KPI last updated in the source. One way that you can display dates in your scorecard is to make use of the MDX "Properties" expression.  Note, that this will only work for data being sourced from an Analysis Services cube.  See the simplistic example below.

For this example, I will simply show a date as an extra piece of information.  In this scorecard, I will be showing my top 20 selling products and I want to see the date that the product was first introduced alongside of the sales information. First, I construct my KPI as follows.  I create the Actual and Target values as I normally would, then I add a new Target, which I will call "Intro Date".  For the intro date value, I create it using an MDX expression as follows:

Make sure and set the calculation column to either "Default" or "Source data", otherwise, nothing will appear when you first look at your results.

Note that I have a cube with an existing Member Property on the SKU level of my product dimension that is "Intro Date".

Then, when I construct my scorecard, I will place SKUs on the rows of my scorecard using a custom formula to give me the top 20 and I will need to drag the "Intro Date" metric out to the scorecard as well if it doesn't automatically appear.  This results in the following scorecard:

This easily enables me to see the intro date in my scorecard as an additional target value.

It is possible to show a date as an actual value in this same manner, but remember, the dates have to be pulled as a member property of a dimension member, this makes construction of the cube and KPI a bit more difficult.  If you did choose to create a KPI with an actual value that is a date and a target value that is a date, you would have to score the KPI using "Band by Stated Score" and your calculation expression would have to resolve to a number in order to be banded appropriately as we can only band numerically.  For more information on banding by stated score see the following post.

Hope this helps. 

Alyson Powell Erwin

alysonp@microsoft.com

PerformancePoint Server 2007 SP2 Now Available

The following Microsoft Office PerformancePoint Server 2007 SP2 products are now available on the Microsoft Downloads Center.

• PerformancePoint Server 2007 SP2 (64-bit) on MS Downloads
• PerformancePoint Server 2007 SP2 (32-bit) on MS Downloads
• PerformancePoint Server 2007 Management Reporter SP2 on MS Downloads

Note: We are also releasing a script from MS Downloads, which PerformancePoint Server Planning customers who have installed builds 3.0.3999.1 or higher are required to run before installing PerformancePoint Server 2007 SP2. PerformancePoint Server customers who are deploying SP2 with Microsoft SQL Server 2008 must also install four SQL Server 2005 CU9 redistributables, which are available for download as hotfixes from the SQL Server 2005 CU9 site. For details, see the PerformancePoint Server Planning Readme.

ProClarity 6.3 SP2 is now available

The ProClarity 6.3 SP2 is now available from the Microsoft Download center.  Attached to this blog you will also find the following documents (if you are at the blog home page, click on the posting header to see the attachment):

1. ProClarity 6.3 SP2 ReadMe
2. Requirements
3. Installing ProClarity 6.3 SP2 on Microsoft Windows Server 2008

 

You can download the SP2 files from the following locations:

ProClarity Analytics Server 6.3 SP2

ProClarity Desktop Professional 6.3 SP2

Updated Documentation: Kerberos authentication and delegation for Monitoring Server

The TechNet documentation "Kerberos authentication and delegation for Monitoring Server" has recently been updated and expanded.  Please see the updated information here:

http://technet.microsoft.com/en-us/library/bb794642.aspx

Filters for Tabular Data Sources and Tabular Filters Explained

When using a tabular data source your first thought may be to use the Tabular Values filter type in Dashboard Designer when wanting to apply a filter to your data source.  However, the Tabular Values filter, admittedly poorly named (we are working on that), should not be used when creating a filter to apply to a tabular data source.  Let's look at what happens when it is used.

First, examine my data source, a SharePoint list containing KPIs, their values and geography slices.

Now, when I create a scorecard off of this data source and want to apply a geography filter in a dashboard I can see there are multiple filter types to choose from.  Again, your first thought might be to create a Tabular Values filter type.  But, what you should select is the Member Selection filter type.

I placed 2 copies of my scorecard into the following dashboard. With the scorecard on the left I applied a Tabular Values filter pointing to the Geography column in my data source.  With the scorecard on the right I created a Member Selection filter also pointing to the members in the Geography column of my data source.  Note, that in order for the filters to not produce error messages, I had to set up the filter link settings as follows:

For the Tabular Values filter:

For the Member Selection filter:

In my published dashboard, first notice first that the Tabular Values filter will list each member multiple times in the filter selection dialog.

Additionally, the Tabular Values filter ignores aggregations - therefore, it will not aggregate the values correctly, in fact what it will do is add the KPI values together across all geographies regardless of which member I select in the filter.  The Member Selection filter will work as expected and only show the 4 members once in the filter selection.

Therefore, when using Tabular data sources, you almost always create Member Selection filters to apply to those tabular data sources in order to get the expected behavior.

So, what purpose does the Tabular Values filter serve?  For this, let's consider the following scenario.

Suppose we have 2 different cubes or 2 different data sources that have the same members, but different hierarchy paths or dimension names.  The Tabular Values filter can be used to construct a mapping table to map members to their appropriate fully qualified names and then one filter can be used to pass the correct values to 2 different report views in your dashboard, each report view being sourced from a different data source. 

Consider the following example (note, I am using 2 unrelated cubes but hopefully you can extrapolate to the usefulness of this feature if you have related cubes with 2 different hierarchies or member names).

I will use the Adventure Works DB which has a Customer.Geography hierarchy of the following nature:

All Customers

  Country

    State-Province

      City

        Etc..

I will also use the Project REAL cube which has a Customer.By Geography hierarchy of the following nature:

All Customers

   Country

    State

      City

        Customer Name

Now, if I want to include some views being sourced from each cube in a single dashboard page and have only one Geography filter, I could create a Tabular Values filter to accomplish this goal.  This would first require that I create a data source for my Tabular Values filter in the following form.  One column for the name of the member I want in the filter, one column for the parent member name, one column for the Member UniqueName of that member in the Adventure Works cube and one column for the Member UniqueName of that member in the Project REAL cube.  I will only add a few members here to show the pattern:

I will now, create my Tabular Values filter as follows:

1. Create a new filter - select Tabular Values as the type.

2. Name it and select your Tabular data source that contains your filter values as the data source for the filter.

3. Click through the Preview screen and then set the key and parent settings as follows:

a. Set the Key to the column that contains your member names.

b. Set the Parent key to the column that contains your parent member names.

c. Set the Display value to the column that contains your member names.

4. Select Tree as the type and then click Finish.

Now, link the filter to each of the different views in your dashboard by the appropriate column name.  In my case, I link as follows:

For the scorecard view from the Adventure Works data source I link as follows:

For the analytic grid view from the Project REAL data source I link as follows:

Now, when viewing my dashboard I see the following:

Note, that my filter is a tree and that my members are now passing to each of the different views from different data sources:

NOTE: this scenario will NOT work when you have KPIs in a single scorecard coming from 2 different data sources because you can only pass one filter link into a single object in the dashboard.

 

Alyson Powell Erwin  (Microsoft)

alysonp@microsoft.com

Extending the Business Intelligence Conversation with Twitter

In an effort to coalesce all of the discussions that are taking place across the Social Network about PerformancePoint Server 2007 and our other Business Intelligence applications and platforms, I’ve created a Twitter alias: microsoftbi. I’ll be linking to relevant blog posts, videos, Web pages, and forum threads that offer new or interesting takes on Microsoft’s various Business Intelligence platforms and applications in an effort to direct more traffic to those links as well as to provide a communications channel for our customers that will go directly to our User Assistance team of writers.

Twitter is all about having a conversation, so feel free to “tweet” your feedback on PerformancePoint Server 2007 to me by inserting @microsoftbi at the start of your tweet. If you’d like to send me a private message, begin your tweet with “d microsoftbi” and fire away – just be sure you don’t exceed the 140 character maximum. Here are a few examples:

1. Ping me with a tweet that everyone on Twitter can see:

“@microsoftbi Love the product but need more scenarios on what’s possible report-wise.”

2. Ping me with a private message:

“d microsoftbi How about creating a user scenario featuring variance reporting?”

You don’t have to limit your questions or comments to PerformancePoint Server either. Any communication related to Microsoft’s Business Intelligence products is welcome and encouraged. All you have to do is join Twitter and “follow” @microsoftbi.

Mapping Dashboard Filters to Analytic Charts and Grids

Been asked about this capability a bit recently, so thought I would create  post specifically around this topic.

When creating  Analytic Charts and Grids you have 2 ways to create these.  First, using the Analytic View Designer (AVD - design tab) or second, by manually entering MDX on the Query tab.

Analytic View Designer Queries

These are created by dragging and dropping the hierarchies and measures that you want applied to the query out to the layout area at the bottom of the chart or grid. Then select the items you want from the hierarchies.  Each of the hierarchies placed in the layout area of the view will be available as a filter endpoint.

When linking a filter, you will see the Item By Category hierarchy, the Time Calendar hierarchy, the Measures hierarchy and the Store District hierarchy.

Custom MDX Queries

You can also create an analytic chart or grid by manually entering your MDX expression on the Query  tab of the view. If you manually enter MDX you have to manually insert your parameters using the <<paramname>> syntax in your actual MDX expression, this will make the paramname a dashboard endpoint item that you can map a dashboard filter to.  Notice that in the below query, I manually inserted the Parameter <<geo>> into the place in the query where I want to apply the filter.  The filter will apply using an MDX overwrite of the parameter token that you have assigned.  Only those tokens that you have inserted will be available as a filter endpoint when constructing your dashboard.

For these views, only those parameter tokens are available as a filter endpoint.

I recommend creating all the views that you can using the AVD simply because those views will enable users to navigate on the hierarchies within the view, performing drill down, drill up, cross drill operations etc.  When creating a view using an MDX query, you will lose the ability to navigate the hierarchies. 

Alyson Powell Erwin

alysonp@microsoft.com

Get Model meta data for a model site from Planning database

While most of Planning system’s meta data is stored in native relational tables in the Planning database, there are some meta data is stored in xml format in the database, for example, model meta data. You may need to get model meta data from the Planning databases. Here is a sample SQL query to get the model meta data for a specific model site from the Planning databases . The following example uses model site label of “Corporate”. Please note that this is only valid for the current released version of Planning system, the future release may change the way how these meta data is stored.

DECLARE @xmlblob xml

SELECT @xmlblob = CAST(CAST ( ba.xmlblob AS varbinary(MAX)) as xml)

FROM BizAppNodes ba

WHERE VersionEndDateTime = '9999-12-31 00:00:00.000'

AND BizAppNodeLabel = 'Corporate'

 

SELECT DISTINCT

tab.col.value('@Id', 'varchar(30)') as ModelId,

tab.col.value('@Label', 'varchar(40)') as ModelLabel

FROM @xmlblob.nodes ('/BizModelSite/Models/ArrayOfBizModel/BizModel') as tab(col)

Get CurrentPeriod value for each model inside a model site from Planning DB

While most of Planning system’s meta data is stored in native relational tables in the Planning database, there are some meta data is stored in xml format in the database, for example, Current Period for each model inside a specific model site. You may need to get CurrentPeriod value for the model from the Planning databases. Here is a sample SQL query to get the CurrentPeriod for each model inside a specific model site. The following example uses model site label of “Corporate”. Please note that this is only valid for the current released version of Planning system, the future release may change the way how these meta data is stored.

DECLARE @xmlblob xml

SELECT @xmlblob = CAST(CAST ( ba.xmlblob AS varbinary(MAX)) as xml)

FROM BizAppNodes ba

WHERE VersionEndDateTime = '9999-12-31 00:00:00.000'

AND BizAppNodeLabel = 'Corporate'

 

SELECT DISTINCT

tab.col.value('../../@Label', 'varchar(30)') as ModelLabel,

tab.col.value('@CurrentPeriodId', 'varchar(30)') as CurrentPeriodId

FROM @xmlblob.nodes ('/BizModelSite/Models/ArrayOfBizModel/BizModel/EffectiveDatedCurrentPeriods/EffectiveDatedCurrentPeriod') as tab(col)