Recently, I've had pleasure to hold a presentation on Microsoft Sinergija 2008 Conference. This lecture was a try to inform others about our experience regarding implementation of PPS in one of our recent projects. Presentation title is "Developing enterprise performance management solutions with PerformancePoint Server 2007".
You can find presentation here.
Monday, October 20, 2008
Wednesday, August 6, 2008
New whitepaper on PPS design
Microsoft announced new whitepaper by Slalom Consulting: "PerformancePoint Planning Design Basics". Here is short description:
"This white paper provides guidance and best practices for the decision makers who are involved in designing and building a Microsoft PerformancePoint Server 2007 Planning application. The authors of this white paper review the methodology and structure of the fundamentals but do not provide step-by-step guidance on implementing them."
Excellent guide for all of us involved in working with PPS Planning. You can download it from the following address:
Whitepaper: PerformancePoint Planning Design Basics
"This white paper provides guidance and best practices for the decision makers who are involved in designing and building a Microsoft PerformancePoint Server 2007 Planning application. The authors of this white paper review the methodology and structure of the fundamentals but do not provide step-by-step guidance on implementing them."
Excellent guide for all of us involved in working with PPS Planning. You can download it from the following address:
Whitepaper: PerformancePoint Planning Design Basics
Thursday, July 10, 2008
What's new for Excel add-in in PPS SP1
Peter Eb. has excelent post in Technet forums regarding SP1 improvements for Excel add-in:
"Here's a quick highlight of the changes our team made in SP1 to improve performance. The main scenarios addressed are "matrix rendering/what-if" and "assignment opening".
1. There is an option to clear the changelist after submission per form. This is for scenarios where large volumes of data are submitted at once, and there is no risk of validation errors. (avoids having the contributor clear the cache manually) This is also useful for long running assignments that may accumulate a large number of cells changed over multiple submissions
2. There is an option to turn off what-if per matrix. This is for scenarios where multiple matrices are being used that reference the writeback model, but might not have aggregated values that are affected by the what-if. (note this is also handy for shares input cases, where the value cannot be what-if'd by Analysis Services)
3. Formula handling. The formula scan is now much more efficient and the matching of formulas to the mdx location is also vastly improved.
4. Auto-recalc. For data entry scenarios when auto-recalc is enabled (remember spreading forces this mode on) we determined that most times the matrix is statically sized. (aka no grow/shrink). So in this case the addin just renders the data grid, formatting and the row/column headers are not touched. It's not quite Excel calculation fast, but definitely for smaller matrices the experience is much improved for contributors.
Mainly assignment opening benefits from the rendering improvements. But specifically in some scenarios the clear changelist after submission is a useful new option. There are some other changes as well (like the where clause change I've talked about frequently), but I think these are the visible changes that have not yet been identified. (I'll go back over the changes later and provide more details if I missed anything)
Some informal (but real world) feedback from internal deployments and some preview customers tell us that common refresh/what-if operations in SP1 are 50% of the time in RTM or better. In one extreme case which could take advantage of the option to turn of what-if the time was even 5%, but I don't think that will be typical. Opening assignments sounds like SP1 is 50% to 33% of the RTM time. So far I haven't seen any negative performance feedback, knock wood, cross fingers, and any other anti-jinx superstitions.
I can't guarantee that those performance numbers will match your customers experience, but am optimistic that formula users and spreading users will see improvements. (Localized builds will follow and will see the same perf. improvements)"
You can find complete discussion on:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3445168&SiteID=17
"Here's a quick highlight of the changes our team made in SP1 to improve performance. The main scenarios addressed are "matrix rendering/what-if" and "assignment opening".
1. There is an option to clear the changelist after submission per form. This is for scenarios where large volumes of data are submitted at once, and there is no risk of validation errors. (avoids having the contributor clear the cache manually) This is also useful for long running assignments that may accumulate a large number of cells changed over multiple submissions
2. There is an option to turn off what-if per matrix. This is for scenarios where multiple matrices are being used that reference the writeback model, but might not have aggregated values that are affected by the what-if. (note this is also handy for shares input cases, where the value cannot be what-if'd by Analysis Services)
3. Formula handling. The formula scan is now much more efficient and the matching of formulas to the mdx location is also vastly improved.
4. Auto-recalc. For data entry scenarios when auto-recalc is enabled (remember spreading forces this mode on) we determined that most times the matrix is statically sized. (aka no grow/shrink). So in this case the addin just renders the data grid, formatting and the row/column headers are not touched. It's not quite Excel calculation fast, but definitely for smaller matrices the experience is much improved for contributors.
Mainly assignment opening benefits from the rendering improvements. But specifically in some scenarios the clear changelist after submission is a useful new option. There are some other changes as well (like the where clause change I've talked about frequently), but I think these are the visible changes that have not yet been identified. (I'll go back over the changes later and provide more details if I missed anything)
Some informal (but real world) feedback from internal deployments and some preview customers tell us that common refresh/what-if operations in SP1 are 50% of the time in RTM or better. In one extreme case which could take advantage of the option to turn of what-if the time was even 5%, but I don't think that will be typical. Opening assignments sounds like SP1 is 50% to 33% of the RTM time. So far I haven't seen any negative performance feedback, knock wood, cross fingers, and any other anti-jinx superstitions.
I can't guarantee that those performance numbers will match your customers experience, but am optimistic that formula users and spreading users will see improvements. (Localized builds will follow and will see the same perf. improvements)"
You can find complete discussion on:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3445168&SiteID=17
Wednesday, July 9, 2008
PPS SP1 Released
Altough I'm late with the announcement, it's important to notice - Microsoft released PPS SP1!
Here is what's new..
What's New for PerformancePoint Planning Server SP1
• Windows Server 2008 Support
• Excel add-in Performance Improvements - The Performance of opening reports and opening and refreshing workflow assignments is improved significantly.
• New workbook option Clear Changes after workflow action - When you set this option to True, it automatically clears any changes after the workflow action occurs when the form is used in an assignment. This improves performance of subsequent queries. However, if submission fails with this option set the user will be unable to resubmit the changes and the changes will be lost.
• New workbook option Enable what-if analysis - When you set this option to True for a matrix, the add-in automatically performs what-if calculations that show the effect the change will have on an assignment if submitted. If False, no changes are applied to the matrix, this can help improve query performance.
• Annotations - Forms and Reports can now be created for models that have annotations disabled.)
• Workflow Assignments - Contributors can now use the discard action after submitting an assignment to clear the version of the form template they recently submitted allowing the most recently published version of the form template to be retrieved.
• User Dimension - Allows creation of dimension properties that link to the user dimension.
• Kerberos Support - The PPS Planning Service can now reside on the same IIS server as other IIS sites that also require Kerberos authentication.
• Data Import Wizard is decoupled from Microsoft Dynamics AX and works with any data provider - The data import wizard is now a generic data integration wizard that allows customers and partners to develop their own data providers to integrate source financial data. The PerformancePoint Server Data Integration Toolkit, provided by Microsoft, is a companion tool that provides a data integration framework. You can use this framework to create your own data providers.
What's New for PerformancePoint Monitoring Server SP1
• Microsoft Windows Server 2008 Support
• Default Display Conditions can be Created - It is now possible to add a default display condition report view (see related blog post also posted today)
• Multi-select Filter Enhancements - Multi-select Monitoring server filters now pass to multi-select SQL Server Reporting Services report parameters and Strategy Map views
• Time Intelligence Post Filter formula date corrections
o In some instances filters were passing a one day offset, this is resolved
o Date format of filter will now honor localized formats
• Negative numbers will not cause a text wrap in scorecards
• Dashboard filter performance improvements
• Expand/collapse functions work properly with hidden parent KPI members
• Tabular filter enhancements
• Limit of 1,000 characters in an MDX expression for KPIs has been removed
• No longer blocked from using SQL Server 2008 as a data source (NOTE: full support for 2008 is coming in SP2, we just won't prevent you from running on it with SP1)
You can download them from the following locations:
PerformancePoint Server 2007 Service Pack 1 (x86): http://www.microsoft.com/downloads/details.aspx?FamilyId=28B1F86B-B7F2-4215-8BC9-8F8507FF8831&displaylang=en
PerformancePoint Server 2007 Service Pack 1 (x64): http://www.microsoft.com/downloads/details.aspx?FamilyId=6245C354-9191-4C4D-8C0C-C10D6C778AF8&displaylang=en
PerformancePoint Server 2007 Evaluation Version (x86): http://www.microsoft.com/downloads/details.aspx?FamilyId=6372C24F-67DD-42DD-B034-748907B23420&displaylang=en
PerformancePoint Server 2007 Evaluation Version (x64): http://www.microsoft.com/downloads/details.aspx?FamilyId=3ADAC793-DEF0-4BA8-A9AB-228979B8DB40&displaylang=en
Here is what's new..
What's New for PerformancePoint Planning Server SP1
• Windows Server 2008 Support
• Excel add-in Performance Improvements - The Performance of opening reports and opening and refreshing workflow assignments is improved significantly.
• New workbook option Clear Changes after workflow action - When you set this option to True, it automatically clears any changes after the workflow action occurs when the form is used in an assignment. This improves performance of subsequent queries. However, if submission fails with this option set the user will be unable to resubmit the changes and the changes will be lost.
• New workbook option Enable what-if analysis - When you set this option to True for a matrix, the add-in automatically performs what-if calculations that show the effect the change will have on an assignment if submitted. If False, no changes are applied to the matrix, this can help improve query performance.
• Annotations - Forms and Reports can now be created for models that have annotations disabled.)
• Workflow Assignments - Contributors can now use the discard action after submitting an assignment to clear the version of the form template they recently submitted allowing the most recently published version of the form template to be retrieved.
• User Dimension - Allows creation of dimension properties that link to the user dimension.
• Kerberos Support - The PPS Planning Service can now reside on the same IIS server as other IIS sites that also require Kerberos authentication.
• Data Import Wizard is decoupled from Microsoft Dynamics AX and works with any data provider - The data import wizard is now a generic data integration wizard that allows customers and partners to develop their own data providers to integrate source financial data. The PerformancePoint Server Data Integration Toolkit, provided by Microsoft, is a companion tool that provides a data integration framework. You can use this framework to create your own data providers.
What's New for PerformancePoint Monitoring Server SP1
• Microsoft Windows Server 2008 Support
• Default Display Conditions can be Created - It is now possible to add a default display condition report view (see related blog post also posted today)
• Multi-select Filter Enhancements - Multi-select Monitoring server filters now pass to multi-select SQL Server Reporting Services report parameters and Strategy Map views
• Time Intelligence Post Filter formula date corrections
o In some instances filters were passing a one day offset, this is resolved
o Date format of filter will now honor localized formats
• Negative numbers will not cause a text wrap in scorecards
• Dashboard filter performance improvements
• Expand/collapse functions work properly with hidden parent KPI members
• Tabular filter enhancements
• Limit of 1,000 characters in an MDX expression for KPIs has been removed
• No longer blocked from using SQL Server 2008 as a data source (NOTE: full support for 2008 is coming in SP2, we just won't prevent you from running on it with SP1)
You can download them from the following locations:
PerformancePoint Server 2007 Service Pack 1 (x86): http://www.microsoft.com/downloads/details.aspx?FamilyId=28B1F86B-B7F2-4215-8BC9-8F8507FF8831&displaylang=en
PerformancePoint Server 2007 Service Pack 1 (x64): http://www.microsoft.com/downloads/details.aspx?FamilyId=6245C354-9191-4C4D-8C0C-C10D6C778AF8&displaylang=en
PerformancePoint Server 2007 Evaluation Version (x86): http://www.microsoft.com/downloads/details.aspx?FamilyId=6372C24F-67DD-42DD-B034-748907B23420&displaylang=en
PerformancePoint Server 2007 Evaluation Version (x64): http://www.microsoft.com/downloads/details.aspx?FamilyId=3ADAC793-DEF0-4BA8-A9AB-228979B8DB40&displaylang=en
Tuesday, June 17, 2008
Model Deployment fails - Error code: PerformancePoint_1050200270
A couple of days ago, after installing PPS2007 SP1, I've created simple application called UserTest and tried to deploy model site. It failed with following error:
"The attempt to deploy, UsersTest, to the model or model site failed. (Error code: PerformancePoint_1050200270)
Failed generate and process of Olap object(s): Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.; 28000; Cannot open database "UsersTest_App_Db" requested by the login. The login failed.; 42000; Invalid connection string attribute; 01S00.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'feec5192-c4eb-41c4-8b38-8a9ee2cbedc0', Name of 'AppDB'.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Time', Name of 'Time' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'MemberDescription' attribute of the 'Time' dimension from the 'UsersTest_UsersTest' database was being processed."
Hmm, strange..Yesterday everything worked smooth (argh, how many times I'm saying this). So I've made a little investigation and here is the result: Network Service must be granted enough privilegies under SQL Server (both relational and analytical engine!). For example, situation with this account in our database was that it was defined as public role. So I've granted administrator privilegies and everything worked again. Please note, that you have to grant enough privilegies both under relational engine and SSAS!
"The attempt to deploy, UsersTest, to the model or model site failed. (Error code: PerformancePoint_1050200270)
Failed generate and process of Olap object(s): Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.; 28000; Cannot open database "UsersTest_App_Db" requested by the login. The login failed.; 42000; Invalid connection string attribute; 01S00.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'feec5192-c4eb-41c4-8b38-8a9ee2cbedc0', Name of 'AppDB'.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Time', Name of 'Time' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'MemberDescription' attribute of the 'Time' dimension from the 'UsersTest_UsersTest' database was being processed."
Hmm, strange..Yesterday everything worked smooth (argh, how many times I'm saying this). So I've made a little investigation and here is the result: Network Service must be granted enough privilegies under SQL Server (both relational and analytical engine!). For example, situation with this account in our database was that it was defined as public role. So I've granted administrator privilegies and everything worked again. Please note, that you have to grant enough privilegies both under relational engine and SSAS!
Displaying desired dimension property in Excel Add-in
Let's consider that we have dimension with following properties: Label, Name, Description. We'll call this dimension Department and we'll populate it as follows:
Label Name Description
1001 1001 CEO
1002 1002 VP
etc.
So, what happens when we select this dimension in Excel Add-in? Add-in will select Label as default property and you would get something like this:
As you can see, Labels are displayed instead of some meaningful text. What we can do? Go to PerformancePoint pane in Excel, select dimension and from the drop-down list choose option "Select Properties":
In "Select Dimension Properties" dialog box, select properties you would like to be displayed (e.g. in our case we want to display Description property):
Close the dialog and click Refresh in the PerformancePoint pane. Voila, we get what we wanted :)
Label Name Description
1001 1001 CEO
1002 1002 VP
etc.
So, what happens when we select this dimension in Excel Add-in? Add-in will select Label as default property and you would get something like this:
As you can see, Labels are displayed instead of some meaningful text. What we can do? Go to PerformancePoint pane in Excel, select dimension and from the drop-down list choose option "Select Properties":
In "Select Dimension Properties" dialog box, select properties you would like to be displayed (e.g. in our case we want to display Description property):
Close the dialog and click Refresh in the PerformancePoint pane. Voila, we get what we wanted :)
Submit Draft problem
I had very intriguing situation last week, while developing PoC application using PPS2007. We set up everything (including models, forms, assignments etc.) and tried to enter some data using Excel add-in. Everything went fine, until one of the users selected "Submit Draft" action from the Actions drop-down. When he submitted data, he couldn't get "Submit Final" option anymore!
After searching a while, we have found several solutions:
"An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 19. Could not obtain information about Windows NT group/user 'domain\user_name', error code 0x5."
where domain\user_name is a domain account under which SQL Server is working. Actually, this problem is not an issue of PPS but of SQL Server. We set up SQL Server to run under Network Service account. If any SQL Server entity needs to access Active Directory domain controller (e.g. from stored procedure or via Service Broker like PPS does), SQL Server must be run under domain account! So we changed SQL Server to run under domain account and everything went fine.
This is was very interesting for me, since I've tried to follow all recommendations from Deployment Guide and this topic wasn't mentioned.
After searching a while, we have found several solutions:
- Stop Planning Service and IIS and and do an ALTER application database enabling broker.
- Configure Service Broker.
- Set up Planning Service to use another domain account.
"An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 19. Could not obtain information about Windows NT group/user 'domain\user_name', error code 0x5."
where domain\user_name is a domain account under which SQL Server is working. Actually, this problem is not an issue of PPS but of SQL Server. We set up SQL Server to run under Network Service account. If any SQL Server entity needs to access Active Directory domain controller (e.g. from stored procedure or via Service Broker like PPS does), SQL Server must be run under domain account! So we changed SQL Server to run under domain account and everything went fine.
This is was very interesting for me, since I've tried to follow all recommendations from Deployment Guide and this topic wasn't mentioned.
Subscribe to:
Posts (Atom)
Posts
