Not so much a new lease on life, as a transplant of an appendage that may or may not get rejected, the source code for PerformancePoint is released.
PerformancePoint Server Planning Gets a New Lease on Life
Microsoft last week announced a reprieve, of sorts, for beleaguered users of its PerformancePoint Planning Server.
- By Stephen Swoyer
- 06/24/2009
PerformancePoint Server Planning Gets a New Lease on Life -- Enterprise Systems
Microsoft releases the source code for PerformancePoint Planning to partners and customers.
Financial Planning Accelerator
Microsoft is pleased to make available the Financial Planning Accelerator (FPA). The FPA is source code and project files derived from the PerformancePoint Server 2007 Planning module. Based on requests from customers and partners, we are making this code available on a no-cost, individual license.
This is unsupported source code that customers and partners can use to support or change PerformancePoint Server Planning functionality. Derived object code files can be distributed to end users with Microsoft SharePoint Server Enterprise Client Access Licenses. To obtain access to the FPA a license agreement between Microsoft and the customer or partner is required. After that agreement is in place, download instructions will be made available.
Please e-mail fpasupp@microsoft.com to request the agreement.
We provide free online accounting lessons that teach introduction to accounting. These accounting tutorials explain financial accounting principles in simple accounting terms. The website also has accounting exercises with solutions and accounting dictionary.
Some tricks to setting up PPS with SQL 2008.
Obligatory bing plug. The name is growing on me... Link #1 in the search results was Nick Barclay’s great post on sorting out PPS setup issues with SQL Server 2008:
"System.Web.Extensions" performancepoint "configuration error" - Bing
Useful reference linkages:
Reed Me : Bing: there it is! My PPS setup problem(s) solved.
Tableau has some interesting data visualizations and can connect to both relational and OLAP data sources, including PerformancePoint Planning. Plus it’s really easy to use if you are familiar with pivot tables and drag-drop functionality.
Check out the blog for more details and other interesting examples
MS has been dropped from Gartner’s Magic Quadrant for CPM.
Magic Quadrant for Corporate Performance
Management Suites
Common Questions Around Excel 2007 OLAP PivotTables
Today's author: Allan Folting, a program manager who works on the Excel team.
I have gotten lots of questions from customers around specific functionality in Microsoft Excel 2007 PivotTables that is missing when connected to Microsoft SQL Server 2005 Analysis Services Service Pack 2 cubes:
- Filtering individual calculated members
- Showing non-visual totals when filtering
I have also gotten lots of requests for the ability to add calculations to OLAP PivotTables using the object model, specifically:
- Adding calculated members/measures
- Adding named sets
Today I'll discuss these topics and outline current ways and workarounds to address this.
Microsoft Excel : Common Questions Around Excel 2007 OLAP PivotTables
Ten Tips for Using SharePoint Server 2007 with Excel Services
Summary: Learn ten tips to improve your use of SharePoint Server 2007 with Excel Services from the SharePoint - Excel Services forum. (15 printed pages)
Joel Krist, Akona Consulting
May 2008
Ten Tips for Using SharePoint Server 2007 with Excel Services
A PPS Monitoring server's default, out-of-the-box, behaviour is to connect to all data sources using a PPS application pool identity account.
That last sentence is important so it's worth repeating...
A PPS Monitoring server's default, out-of-the-box, behaviour is to connect to all data sources using a PPS application pool identity account.
Nick Barclay: BI-Lingual: PerformancePoint Monitoring Data Source Connection Problems
Investigating bottlenecks with 64-bit servers? Check the memory and ensure it is available.
Overview
The Microsoft Windows Dynamic Cache Service will manage the working set size of the Windows System File Cache. For 64 bit systems this service helps to address the problem of excessive cached read I/O that could eventually consume all of physical memory. Sample source code and compiled files are included in the compressed file.
Modifying the RDL of a PPS Matrix to activate functionality within the product… tedious but useful.
We've found a way of implementing global filters (we use dimension property filters) for different models... and we'd like to know pitfalls of our approach (for the moment I can guess 2 :-))
Task: Apply global "Entity - Office" filter to Operations and Salary models
Approach:
1. Create all matrices and filters needed and apply corresponding filters to matrices.
Matrix 1: Model - Operations. Columns - Entity:Business unit Leaves. Rows - Account. Dimension property filter: Entity - Office.
Matrix 2: Model - Salary. Columns - Account. Rows - Entity:Employees. Dimension property filter: Entity - Office
2. Edit RDL
2a. Find filter definitions
<ReportParameter Name="PerformancePoint_ebb03fb2_b6f8_4298_91cf_ceb94ee473ff"> ' Filter from Operations Model to be applied to Salary model
…
</ReportParameter>
<ReportParameter Name="PerformancePoint_ef581824_8178_4b6b_a743_59a26f740964"> ' Filter from Salary Model to be "skipped"
…
</ReportParameter>
2b. Replace Name and Value for QueryParameters using appropriate name of one of the filters (ReportParameter) you want to use for different models:
<QueryParameters>
<QueryParameter Name="PerformancePoint_ef581824_8178_4b6b_a743_59a26f740964"> ' to be replaced with see bellow
<QueryParameter Name="PerformancePoint_ebb03fb2_b6f8_4298_91cf_ceb94ee473ff">
<Value>=Parameters!PerformancePoint_ef581824_8178_4b6b_a743_59a26f740964.Value</Value> ' to be replaced with see bellow
<Value>=Parameters!PerformancePoint_ebb03fb2_b6f8_4298_91cf_ceb94ee473ff.Value</Value>
</QueryParameter>
</QueryParameters>
3. That's it.
If I select Office for Operations matrix, it's also applied to Salary Matrix :-)
Guessed pitfalls:
1. Forms and reports created using this approach can't be migrated to other application - this is hardcode! :-(
2. Any change to report definition will destroy our filters override - this is hardcode! :-(
Any thoughts re other difficulties we'll meet?
Regards, Vasily
Request for comment: Global filters linked to different models
Using Lizard to parse PPS Planning logs.
1. Create a header file.
DateTime Event PPS Location User Details
Note that spaces delimit the file.
2. Create a new Log Parser Lizard Group & Query for PPS.
3. Select Tab-separated and space-separated values
4. Enter Query
select top 100 *
from '\\Path_To_PPS_Server_Logs\trace log\server.log'
--where details like '%error%'
order by rownumber desc
5. In Query Properties:
iHeaderFile = <step 2 filename>
nFields = 6
iSeparator = ‘space’
FixedSep = true
Save and “Generate” (run) the query.
Click here to download Log Parser Lizard
Log Parser is a very powerful, free and versatile tool that provides universal query access to text-based data, such as log files, XML files, and CSV files, as well as key data sources on the Microsoft Windows operating system, such as the event log, IIS log, the registry, the file system, and the Active Directory services.
Because the command-line interface is not very intuitive, I have created Log Parser Lizard, a GUI tool for managing queries, exporting results to Excel, charts, etc… In addition I have added input filters for RegEx and log4net input log formats (with support for regular expressions) and SQL server T-SQL queries. There are some helpful examples included in installation package to help you start using Log Parser Lizard (and it’s SQL like syntax) for a web log analyzer and system log analyzer.
Microsoft PerformancePoint Team Blog was #81. I wonder how it’s looking this month…
The Microsoft blogs solution has a lot of great features, but an important feature that is missing is popularity metrics. I want to know what the community is most interested in and keep up on all things Microsoft. The “all posts” feed that comes OOB doesn’t do the trick. My team inherited the Microsoft blogs apps (http://blogs.msdn.com/, http://blogs.technet.com/) recently and we are in the process of looking at adding new features to support many views over the data. Until we have all the details fleshed out I will continue post the 100 most popular MSDN and TechNet blogs on a monthly basis…complete with Opml so you can subscribe to them all at once.
Jon Gallant's Blog : Most Popular Microsoft Blogs – January 09
Roll your own Assumption Model with LookupCube.
with member [PurchaseAmt] AS LOOKUPCUBE("Project Materials Cube", "([Measures].[Purchase Amount],[Fy Qtr].[" + [FY Qtr].CurrentMember.Name + "])" ) -- We need to construct a string that looks -- exactly like this… -- ( [Measures].[Purchase Amount] , [Fy Qtr].[2005 Q1]) select { [Measures].[HoursWorked], [Measures].[Total Labor], [PurchaseAmt]} on columns, [Fy Qtr].children on rows from [Project Labor Cube]
The Baker's Dozen: 13 Tips for Querying OLAP Databases with MDX
This site will soon be called www.performancepointed.com I think.
Per-for-mance-Point-ed - definition: To develop a planning & forecasting tool for the masses and bring the role of IT back into to Finance, and then communicate that you are discontinuing the tool after planning & forecasting cuts, and realizing many of the masses don't have multiple SQL Server Enterprise Edition and Sharepoint servers just laying around.
Disappointment indeed. It's now official. I can say with finality that I wasted a year in my career.
Cubegeek: Microsoft PPS: Dead.
Demand for PerformancePoint jobs in the UK experienced almost a 25% increase year over year.
If that was a stock I would probably be selling it. Nice return and unsustainable growth?
Speaking of stock...
The trendline on this doesn't look very good, though I would suspect that we have some bottoms around here. MS hasn't seen 18 bucks since the Asian crisis of 1998.
From ITJobsWatch
I guess that intangible benefits like CFO visibility and the "potential" of Windows/Office 2007/SQL Server 2008/Sharepoint license sales and adoption don't matter in an economic downturn. Or perhaps the "what-if" analysis MS built for themselves was too good for their own good? Maybe they forecasted something they didn't want to see?
I have been working with Planning since CTP2 and am a certified PPS Planning Trainer. When they demo'ed Performance Point (Business Scorecard Manager) way back in 2005 at a partner convention I went to in Redmond, there was some serious excitement there. Finally, MS would have the proper client tools for Analysis Services to compete with other OLAP vendors on, instead of some Office Web Components and a pivot table.
Planning was just a bonus. Bonuses get cut back in economic downturns... unless you're a bank, or a certain bank CEO. (Isn't MS a bank too?)
The good news is that PPS Monitoring is now free with Sharepoint (or is it Sharepoint is now free with PPS?). Now if they would only release the source code for Planning & Management Reporter (and why not toss in Proclarity Desktop too?) so we can go back to building custom-tailored planning solutions without the constraints of a packaged planning tool... and have the ability to fix some of the troublesome bugs and performance issues inherent in a V1 product.
At least give us the source code for that Excel add-in reporting tool. Lots of good possibilities there.
Nothing wrong with a little .NET code to solve some business issues. So much more fun debugging rules in Visual Studio than trying to debug and maintain rules in a client tool like the PPS-P GUI. Planning should have been a web application to begin with. Keep it in the cloud guys...
Well, I'm off to develop the "yellow cell input" feature checkbox on the Excel 2007 ribbon. The checklist for getting mine to work should be a bit shorter than the current "yellow cell input" checklist for Planning.
1. Click the 'yellow cell input' checkbox in Excel 2007 (or just format the cell as INPUT).
2. Input your values.
3. Add your commentary next to the values.
4. Publish to your database and refresh the cube.
This was probably how Biz#/Planning got started too...
I think we're on step #6 of the definition for performance at Microsoft.
per·form·ance [ pər fáwrmənss ] (plural per·form·ances)
noun
Definition:
1. artistic presentation: a presentation of an artistic work such as a play or piece of music to an audience
2. manner of functioning: the manner in which something or somebody functions, operates, or behaves 
a high-performance car
3. working effectiveness: the way in which somebody does a job, judged by its effectiveness ( often used before a noun ) performance-related pay
4. thing accomplished: something that is carried out or accomplished
5. accomplishment of something: the act of carrying out or accomplishing something such as a task or action
6. display of behavior: a public display of behavior that others find distasteful, e.g. an angry outburst that causes embarrassment ( informal )
7. linguistics language produced: the language that a speaker or writer actually produces, as distinct from his or her understanding of the language.
See also competence (sense 3) parolen (sense 5)
http://encarta.msn.com/dictionary_1861724999/performance.html
Sacha has more info.
http://blogs.adatis.co.uk/blogs/sachatomey/archive/2009/01/29/rip-performancepoint-planning.aspx
So does Ajay.
http://ppsinfo.blogspot.com/2009/01/pps-planning-is-being-discontinued-what.html
Sacha finds an interesting "upsizing" feature for Pivot tables.
Now if only it was so easy to convert from Report Wizard to Matrix to Custom MDX to Management Reporter Reporting Services... and back again.
There should be a market for PerformancePoint Planning RDL converters pretty soon...
Convert PivotTable to PerformancePoint Planning Matrix
I spotted a 'Convert to PerformancePoint Matrix' menu option on the context menu
of an Excel PivotTable recently.I'd not noticed it before - it is documented within the PPS Excel Add-In help but only once, and subtly at that. I'm glad to report, on first impressions, it appears to do a fairly decent job.
It's a one-way process that cannot be undone and it creates a 'User defined' MDX style matrix. You are therefore limited to making subsequent edits using the Report Properties window but suffice to say it's a handly little feature that I'll no doubt use more and more now I've found it.
Convert PivotTable to PerformancePoint Planning Matrix - Sacha Tomey's blog
This download will install a set of components that can be used by non-Microsoft Office applications to read data from and write data to 2007 Office system files such as Microsoft Office Access 2007 (mdb and accdb) files and Microsoft Office Excel 2007 (xls, xlsx, and xlsb) files. Connectivity to text files is also supported.
ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.
Download details: 2007 Office System Driver: Data Connectivity Components
This post doesn't have a lot to do with PerformancePoint specifically, however there are lots of possibilities to use Regular Expressions to solve some of your business requirements.
With the previously blogged-about plugin for Excel (morefunc) you have the ability to use Regular Expressions as formulas in Excel. You can also use Regular expressions in Excel with some VBA code.
What are Regular Expressions? They're anything but regular. They allow you to design expressions to apply to tasks such as searching for patterns of data, data validation, etc. Rather than just searching for the @ sign to find a "possible" email address, you could use regular expressions to find an exact interpretation of an email address with this regular expression.
\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b
You could also use this as a data validation rule to ensure that you are cleanly entering email addresses into a form for later import into the application using CSV.
These expressions look very cryptic until you get the gist of the syntax.
These types of tools could be handy for cleansing data before they enter the PerformancePoint staging database or within an input form. They could also be useful for mapping and profiling data.
For example, labels are required to be no more than 40 characters in length, member properties are restricted to 100 characters, and there are a series of restricted characters and reserved words. You could design a regular expression that checks for these values within your data before it is loaded into the system. You could build something to do this in Excel or use SSIS, or even in a stored procedure before moving the data into a label table.
Another application would be to "tokenize" or split a pattern into a comma-delimited string. (eg. Account-Dept-Location), Lexical Analysis in geekspeak terms.
http://effbot.org/zone/xml-scanner.htm
http://en.csharp-online.net/CSharp_Regular_Expression_Recipes%E2%80%94A_Better_Tokenizer
It is easy to tokenize a value with 3 hyphens without learning regular expressions, but what if you needed to parse something like this to get the numbers out?
123ab2c
1234abc
Regular expressions might be useful...
How about a search/replace, but with intelligence?
http://msdn.microsoft.com/en-us/library/system.text.regularexpressions.regex.replace.aspx
Within SSIS, you can use this to perform data profiling and to classify data into buckets.
SSIS Regex Component
SSIS Regular Expression Transformation
You can use it in SQL
SQL and Regular Expressions
Regular Expressions with PATINDEX in SQL
Regular Expressions with OACREATE in SQL
Working with crummy data in SQL
It's about as handy as knowing XSLT and MDX
Regex is as forgettable as XSLT and MDX
The most useful tools for learning Regular Expressions:
Some 3rd-party implementations of Regex:
Regular Expressions in Matlab
Regular Expressions in SAP Routines (Converting dates, thousands separator,
A deep dive into Regular Expressions.
Regular Expressions in Wikipedia.
All you ever need to know from 4guysfromrolla
I can see this one being useful to translate the XML format that PEL rules are stored in back to a readable text interpretation... without using an HTML parser.
Removing All HTML Tags from a String Using Regular Expressions
http://www.4guysfromrolla.com/webtech/073000-1.shtml
These type of statements are great for enforcing some of your business rules/validation logic using a data-driven approach.
UPDATE updateTable
SET ut.field1 = dbo.regexReplace( ut.field1, rt.regex, rt.replace, 1, 1 )
,ut.field2 = dbo.regexReplace( ut.field2, rt.regex, rt.replace, 1, 1 )
FROM updateTable AS ut
CROSS JOIN regexTable AS rt
DECLARE @strTest varchar(8000) SELECT @strTest = '\\servername\foldername99999\filename.ext' SELECT [Position]=PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @strTest), [Before]=SUBSTRING(@strTest, 1, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @strTest)-1), [After]=SUBSTRING(@strTest, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @strTest), LEN(@strTest))
Regular Expressions to decompose MDX query
Regular expressions can be useful to decompose a MDX query (with elements explicitly defined, i.e. not using MDX functions) and retrieve involved objects (i.e. dimensions, levels, members, etc.). I often used them in performance improvement processes or for MDX pre-validation purpose.
How about being able to parse your calculated measures out of the MDX script used by the cube?
http://geekswithblogs.net/darrengosbell/archive/2006/08/10/87561.aspx
Or building some pattern filtering functionality using MDX stored procedures?
http://blogs.conchango.com/christianwade/archive/2006/04/30/3896.aspx
There's nothing stopping you from using the System.Text.RegularExpressions libary in Reporting Services functions either.
http://msdn.microsoft.com/en-us/library/ms157328.aspx
Here is a good resource to better understand Regular Expressions. There are plenty of possibilities - just make sure when you get this hammer, everything doesn't look like a nail.
The Regular Expressions cheat sheet is a one-page reference sheet. It is a guide to patterns in regular expressions, and is not specific to any single language.
Regular Expressions Cheat Sheet (V2) - Cheat Sheets - Added Bytes
Once you’ve used the handy dandy Yellow Cell Checklist found here, how do you know when the data appeared in the database?
When will data appear in the cube after a submit from the PPS Planning Business Client?
Peter Eb. : When will data appear in the cube after a submit from the PPS Planning Business Client?
How to install SQL 2008 with PerformancePoint.
After 2 days I finally figured out why the pps olap charts and grids would not work in my pure 2008 environment. Also proclarity. You must install the SQL 2005 CU9 redists BEFORE installing SQL Server 2008. Here is the order of operations
- Install Windows Server 2008
- add IIS and AppServer roles (incl. CGI, ASP, IIS6 mgmt)
- Install the 4 SQL 2005 CU9 redists
- Sqlncli
- SQLServer2005_ADOMD
- SQLServer2005_ASOLEDB9
- SQLServer2005_XMO
- Install SQL Server 2008
- DB (incl. full text search for AdWorks sample), AS, RS
- Install MOSS 2007 SP1
- Create SSP, site collection
- Install PPS 2007 RTM
- do NOT run config mgr
- install PPS SP2
- run Config mgr
- Install PAS/Desktop
- install SP2
- Install Visio, Office, etc
Creating a Summary worksheet (Cube) directly in Excel.
As you move to PerformancePoint for reporting, this scenario will hopefully be less relevant.
But what if you have 25 worksheets and you need to sum up the same fields in all of them on a consolidation worksheet.
Named ranges and colons are your friend.
What is a 3D Formula ?
These are also known as cubed formulas.
A reference that refers to the same cell or range on multiple worksheets is called a 3D reference.
Using 3D formulas allows you to calculate data thorughout a workbook using multiple worksheets.
All 3D formulas are based on the syntax: Sheet1:Sheet4!A2:B5.
A 3D formula is a formula that refers to the same cell (or range of cells) on multiple worksheets.
The 3D formula "=SUM(Sheet1:Sheet4!A2)" can be used to add up the numbers in cell "A2" on 4 different worksheets.
PerformancePoint Server 2007 SP2: Data Import Wizard
Brief Description
The Data Import Wizard enables users to configure and move data from source systems into Microsoft Office PerformancePoint Server 2007.
When you run rules, there is no option to cancel in the Business Modeller. There are a few solutions for killing rules while running.
1. Kill the SQL process (sp_who2, kill ##) of the rule. (SQL implementation)
2. Kill the Analysis Services process of the rule. http://www.codeplex.com/MSFTASProdSamples.
3. IISReset.
4. SQL / SQL Analysis Services Reset.
5. (Possibly) kill w3wp.exe process.
All are brute-force methods highlighting the fact that you are probably not meant to stop rules while they are running. :)
Welcome to the PerformancePoint Planning Business Rules Development Guide
The 640+ certified PerformancePoint Specialists will soon have company.
Microsoft Top 13 Microsoft Certification Exams List:
- .html">70-285:MCSE Designing a Microsoft Exchange Server 2003 Organization
- .html">70-442:Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 is in development
- .html">MB7-517:NAV 5.0 Installation & Configuration
- .html">70-556:TS: Ms Office PerformancePoint Server 2007, Applications
- .html">70-089:Planning, Deploying, and Managing Microsoft Systems Management Server 2003
The Most Popular Microsoft Certification Exams in October 2008 | Jack’s Blog
Be sure to budget time to create variance reports for proper data validation when planning any data project.
PerformancePoint Server - Balancing Your Actuals
If like me, you are loading your actuals from your ERP system in PerformancePoint Server Planning, it helps to check your figures to make sure that they always balance. Now every good accountant will tell you that your trial balance must always balance to zero. This goes for each period and off course year to date. I have written some reports that self balance our system but in general here is the manual leg work behind it.
Financial Systems and Business Intelligence: PerformancePoint Server - Balancing Your Actuals
A couple helpful ones here…
TechNet Virtual Labs: 2007 Microsoft Office System
Get hands-on with the key features and components of the 2007 Microsoft Office system and learn the benefits of deploying it within your organization. Walk through installation, configuration and the new components around enterprise features administration, collaboration, business intelligence, and experience common deployment and integration scenarios that can help you make better choices when planning your roll-out.
Be sure to apply the “Pre-Service Pack 2 Update”
PPS 2007 SP2: Application Database Pre-Service Pack 2 Update
The Application Database Pre-Service Pack 2 Update resolves an issue that prevents PerformancePoint Server 2007 SP2 from updating the Application database.
I'm teaching a PerformancePoint Planning course this week with a colleague from my company. Here are some tips that may help implementations, some added from previous postings, others discussed in forums, others discussed in the class:
Calendar
Note that the calendar cannot be changed for an application once it is created. (Time Dimension) Plan carefully!
Model Type
As with Calendar, Model Type is not something you can change, though you could add or remove rules from other model types to make them similar...
Schedule a Job
Is really just "create a job" which could be scheduled or run manually.
Associations
Are really just hidden model rules... sometimes it might be better to roll your own rather than using the associations section.
Input Forms
http://performancepointing.blogspot.com/2008/09/why-aren-my-input-form-cells-turning.html
Why aren't my input form cells turning yellow?
A colleague of mine offers this troubleshooting checklist when you just can't get the PerformancePoint Planning Excel Add-in to recognize input boxes in an assignment.
PerformancePoint Planning - Input Forms Checklist
And a few new ones.
11. The cycle you have created uses the same model as the assignment's forms.
12. The cycle you have created starts before the current date and ends 1 day after the current date (depending on your cycle configuration.)
13. The roles that the user you are logged on as do not have read/write access turned off for the dimension members selected to input, as any role with this will override a permitting role. (similar to 6)
14. That you have refreshed the offline assignment under Work Offline - Manage offline Assignments.
Dynamic Dates in Reports
Use the Current Period Reference in the time dimension of reports to dynamically change report date ranges. Static examples from the course are not as maintainable, especially for monthly cycles.
Cycles
Only one role/user can be assigned to a cycle template. Multiple users/roles can be assigned to an instance.
Might want to name cycles like YYYY-MODEL-SCENARIO-DESCRIPTION
Security And Roles
For Business Unit security - Create a master role with read/write access to all business units. Add all users to this role. Treat it as a 'mailing/distribution list' used specifically for creating the cycle instance and assigning it to the role.
Create separate roles for each business unit contributor making an assignment. Deny access to units that you do not want them to have access to.
Create another role "Lockout" or "Disabled Users" with no access to anything. Add users to this role to disable access to models. Deny overrides any other access they may have.
Do not specify User Customized Permissions. (Performance Hit). Use increased number of roles instead.
Excel Add-In
Cut-pasting the matrix from Report-Properties may throw an error if dimension filters exist. Edit the dimension filters under report properties to include name, label syntax same as original matrix. Ensure that all properties match original matrix.
Rules
Use Active column to disable rules when performance problems exist. Test model performance without any rules. SQL Rules should be scoped very tightly, MDX rules should be scoped very broadly.
Use variables to store start and end dates for rules instead of current period reference. Additional flexibility.
PPSCMD.EXE
Creating a job of jobs. (Courtesy Jeffrey Wang, Technet forums)
Well, its not impossible, its just not documented - but i guess that also means use it with care =)
If you use .Net Reflector, you can browse into the PPSCmd itself and see that it actually has some hidden commands inside. One such magical command is the ImportXml that takes an xml file descibing the jobs you want to run.
Create a XML file with the following content in your context:
<?xml version="1.0" encoding="utf-8"?>
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/performancepoint/2007/03/MigrationDefinition">
<Applications>
<Application Id="GUID" Label="AppLabel" Name="AppName">
<ImportCommands>
<Command xsi:type="LaunchJob" Name="JobLabel">
</Command>
</ImportCommands>
</Application>
</Applications>
</Root>
Next, call it:
PPSCmd importXml /server http://server:46767 something.xml
Make sure you do not have have rules that are NativeMdx or NativeSQL implementation as this will disable the EnableNativeMdxSQLRule on the application.
Once you have it running, you can schedule this to run in SQL Agent - full scheduling to your heart's desire.
PerformancePoint Planning - Analysis Services
Use SQL Profiler to connect to Analysis Services to identify query problems and issues with caching, or just to see what it's doing in the background while it's not "rendering the matrix". You may be able to tweak aggregations using Aggregation Manager, though you will probably need to do this on every deploy. Analysis Services should be on at least one separate server in the architecture for memory and performance reasons.
PerformancePoint Exam 70-556 Outline
Proclarity 6.3 SP2 is out, and still doesn't play very well with Sharepoint 2007 on Windows 2008.. but hey, you should be scaling out right?
Attached is the Requirements.doc for the ProClarity 6.3 SP2 (build 2217) release. This document is being posted here because it will not be available via KB article for another couple of weeks. The SP2 bits are available at http://download.microsoft.com. Still to come later are the "Installing ProClarity 6.3 SP2 on Microsoft Windows Server 2008" and "ProClarity 6.3 SP2 readme.docx" files. We'll post these as soon as possible.
The Microsoft ProClarity Team Blog : ProClarity 6.3 SP2 (build 2217) Documenation
Sometimes we need to utilise dynamic dimension security in an Analysis Services solution and we also need to display dynamically the allowed members in a PerformancePoint filter.
Filtering Unneeded Dimension Members in PerformancePoint Filters - SQL Server Central
Advantages of Proclarity vs. Excel... in French.
Proclarity 6.3 et Excel 2007 permettent à leurs utilisateurs de réaliser des rapports ad-hocs fondés sur des cubes Analysis Services (SSAS).
Aurelien Koppel: Blog technique sur PerformancePoint Server (PPS)
Some sample code to get started building your own PerformancePoint Planning add-in in C#... or at least one feature of it.
Below is some really rough and ready C# code that actually does delete a dimension member property. I will improve the code and probably add it in to my PPSCMD GUI interface as a 'feature pack' bonus at some stage, however, if you are in desperate need for the code to delete a member property, and you can't wait for PPSCMD GUI v0.2 or PerformancePoint Version 2 (I'm not sure which will come first !) the code is below (Use at your own risk !!)
PerformancePoint Planning: Deleting a Custom Member Property - A Solution - Sacha Tomey's blog
So.... you want to use the PerformancePoint web service so you can extend PerformancePoint, and integrate it into bespoke applications and SharePoint.
Still very relevant.
Creating Excel and PowerPoint Documents Using PerformancePoint
Eric Friedrichsen (ericfr@microsoft.com)
Microsoft Corporation
August 2007
Applies to:
Microsoft Office PerformancePoint Server 2007
Microsoft Office Excel 2007
Microsoft Office PowerPoint 2007
Microsoft Office PerformancePoint Server is a performance management application that helps users monitor the performance of their organizations, analyze the results, and make effective business decisions.
The Microsoft Dynamics AX 2009 sample contains a sample Microsoft Dynamics AX data cube and two sample dashboards that are based on the information. One is an executive overview dashboard and the other a drill down into the General Ledger information. You can reuse these dashboards on your own Microsoft Dynamics AX cubes if you desire by following the instructions included with the sample.
It’s not the Unified Dimension Model that’s the focus at Microsoft any more… it’s the Single View Platform. It’s not just Analysis Services on top of SQL Server to centralize your data, it’s Virtual Earth and PerformancePoint, tied into Sharepoint, to offer a comprehensive “Single View” of the world.
The Single View Platform is not a new product in itself, but rather a group of existing products brought together under a banner for a specific problem domain (like CSP). For SVP it brings in Virtual Earth, SQL, SharePoint, PerformancePoint and a whole host of other products. The advantages of this approach were rather nicely summed up on another blog:
For those of us who have chosen the Microsoft platform, the good news is that we have many products from which to choose. Microsoft has an array of server and client products, as well as online services, that cover everything from collaboration to commerce, business intelligence to geographical information systems, personal productivity to accounting.
Sometimes the sheer number of choices can be daunting, so Microsoft is initiating new offerings that help us weave the various products into cohesive solutions. The goal is to bridge the gaps between product groups and provide additional documentation and code to integrate multiple Microsoft products.Very nicely put! I really hope that we see more of these frameworks from Microsoft. They allow us tackle a problem by grouping a set of our products together, and then producing documentation, guidance, demos, samples and so forth directed at solving the specific problem space.
PerformancePoint Monitoring + Silverlight: KPIs and Scorecards
PerformancePoint Monitoring doesn't have as robust a prescription as Silverlight Blueprint for SharePoint, but yes, PerformancePoint Monitoring & Silverlight can be used together.
PerformancePoint dashboards are SharePoint web part pages, so web parts or master pages using Silverlight for menus, visualization, charting, or any other thing, are right at home in (or surrounding) a PerformancePoint dashboard.
Another integration point is the web service PmService.asmx. You can learn more about this service through PerformancePoint SDK documentation of its client proxy. In this article we’ll call the GenerateView method of this service to retrieve and work with a scorecard/KPI data set, and visualize that data in Silverlight 2.
PerformancePointy : PerformancePoint Monitoring + Silverlight: KPIs and Scorecards
Details on Consolidation from the BI VPC 6.0.
How Consolidation works in Performance Point Server:
Financial model with shares consolidation: This uses financial model with shares since it requires ownership information which derives from the shares calculation. There are five rules templates for statutory consolidation in FI. They are Inter-company rule for Profit/Loss, Inter-company rule for investment, Inter-company rule for Equity and Inter-company rule for Balance Sheet. One can map a leaf account to the model property that used by the rule. The leaf account will then be used for calculating minority interest, investment and elimination related transactions. Users can also write their own rules to accommodate more statutory requirements. The financial model with shares consolidation only supports one level entity hierarchy, and shares calculation will generate all the ownership information for each entity relative to the parent entity which in term generates a flat hierarchy.
If company A owns 90% of company B, there will be two leaf entities (company B and company A) and one consolidated entity Consol. After shares calculation company A has holding consolidation method and company B has Full consolidation method. Once the method is determined for each entity within the hierarchy, we need to load fact table data for each leaf entity. Consolidation process involves the following three steps:
- Shares Calculation: To calculate the ownership and control for each sub entity.
- Reconciliation (optional): To record any difference occurred between inter companies.
- Consolidation: To record elimination for inter-company transactions based on the type of the accounts, consolidation methods and flow type (for BS accounts only).
- Currency Conversion (optional): Consolidation can also do currency conversion but it’s optional.
Financial model without shares consolidation: This uses financial model without shares since we assume 100% ownership between parent and child entities. The model property Consolidation Balance Account is used to record the elimination for each account. Non statutory consolidation supports staged hierarchy so that parent entities can have multiple levels. The 100% elimination will take place for all the intercompany transactions.
I don’t usually ask myself questions, but if I did it would probably be “Self, what is an Offset Account” if I had to work with the Financials with Shares model in PerformancePoint Planning.
You may be asking yourself, “Self, what is an Offset Account? What is a Balancing Account for that matter?” Well, when an IC reconciliation job runs it looks at the difference between the values recorded by the buyer and the seller. In this case, the seller recorded the transaction at $50.00 higher than the buyer. IC reconciliation always assumes the buyer’s value is correct and so we must reduce the seller’s value by 50 bucks. The offset account (that we set in the model properties) is considered a “seller’s account” so adding -$50.00 will bring the whole shebang into balance. But, our ancient Greek accounting rules tell us that we need to record a double entry for this transaction. Enter: the balancing account. The balancing account will always get a double-entry for the same amount and opposite sign as the offset account.
PerformancePoint Server Information by Ajay Singh: InterCompany Reconciliation
Do you know what Microsoft PerformancePoint is?
I think of Microsoft PerformancePoint as a combination "all-in-one", "do-it-yourself with a bit of help" planning, analyzing, reporting, scorecard and dashboard creating suite from Microsoft with Excel, Sharepoint, and Web functionality. It helps you to capture and analyze your data. It integrates multiple Microsoft products into a single common theme of _Business Intelligence_.
It's kind of like a swiss-army knife for BI, but with power tools instead of a fork or toenail clippers.
There are many components of PerformancePoint drawn together from creations of Microsoft's development teams, acquisitions, and licenses of 3rd-party tools. Proclarity, Business Scorecard Manager, Biz #, FRx, to name a few. Planning, Monitoring, and Analyzing.
There's a lot to cover if you want to know all there is to know about PerformancePoint.
PerformancePoint is not:
What is PerformancePoint?
I enjoy word analysis more than number-crunching, as words can be beautiful and numbers are just... well... numbers. Wordle is one of my favourite "tag cloud" tools for creating these word visualizations.
PerformancePoint, as defined by the search terms accessing this blog.
 |  |
 | |
As this is a blog, you're going to see terms like 'problem', 'failed', and msmdpump.dll' picked up from the search engines. I had a few "head scratching" moments myself when trying to build my first model using PerformancePoint Planning, trying to create my first Dashboard using PerformancePoint Monitoring, and trying to get Proclarity Web Professional installed.
Not to mention data integration. "createlabeltableformeasuregroup" anyone?
The purpose of this blog and my postings here, other than to capture every tidbit of internet knowledge around PerformancePoint, is to solve problems, turn failures into successes, and assist with configuring XML/A. :)
So that's how people got here. But where is Microsoft going with this?
More from Directions On Microsoft.
Even though I try to keep track of all the goings-on with PerformancePoint here, Microsoft still is the best source of information.
Top 10 Benefits of Microsoft PerformancePoint
http://www.microsoft.com/business/performancepoint/productinfo/top10benefits.aspx
PerformancePoint Forums
http://forums.microsoft.com/TechNet/default.aspx?ForumGroupID=517&SiteID=17
And mathematics can be beautiful too.
http://thedailywtf.com/Articles/Stupid-Coding-Tricks-The-TSQL-Madlebrot.aspx
Debugging SharePoint can be problematic at times, it does like to hide debugging information from you. The bain of my life recently has been “An unexpected error has occurred” with nothing written to log files, trace or the event log.
Normally I can debug the problem with a little commenting & narrowing down of the problem, but today I have managed to get rid of that error screen completely.
The solution is to change a single entry in web.config, by modifying the line…
<SafeMode MaxControls=“200“ CallStack=“false“…
to…
<SafeMode MaxControls=“200“ CallStack=“true“…
You will also need to set custom errors to 'Off' .
<customErrors mode=“Off“/>
You will no longer see the “An unexpected error has occurred” error page and instead you get a lovely ’standard ASP.Net error page’ with the stack trace and everything…development has got that little bit easier!!
If you notice this filling up the Windows Event Log, you may need to modify permissions within Component Services.
Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
User: NT AUTHORITY\NETWORK SERVICE
Description:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{3D42CCB1-4665-4620-92A3-478F47389230}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.
Here is another quick tip, Reporting Services 2008 would be fully supported using SP2 (see my previous post).
To those of you who need to use SSRS2008 today, using PPS SP1, here is a workaround:Scenario: Integrating SSRS2008
1. Set Server Mode to "Share Point Integrated"
2. Type the SSRS2008 Report Server URL
3. Set the Report name using Full Path description
It would be great to have a way to enter text into a cell as something that could be captured and referenced from either the cube or a relational table. This concept would fit well with the standard of variance explanations placed beside numbers.
After all, a number is only as useful as the story surrounding it.
Today I have decided to talk about the concept of Annotations. Annotations are a nice feature of PerformancePoint, but there are some limitations to keep in mind.
Lots of useful links here. Many I already link to on the right hand links.
Since there are a lot of you out there looking for away in, I've decided to gather all my known links and recommendations to help you take your first steps. I've decided to call it the "Junkies List":
- Taking the first steps: Adrian's Impressive Intro Tutorials (1-7):
http://blogs.technet.com/datapuzzle/archive/2008/03/21/help-how-to-guides-manuals-forums-for-microsoft-office-performancepoint-server-2007.aspx- Level 100 – 200 : PPS – Planning On-Line raining
http://www.microsoft.com/business/performancepoint/resources/training.aspx- Basics Concepts:
http://blogs.adatis.co.uk/blogs/sachatomey/archive/2008/04/28/new-microsoft-insider-pps-planning-blog.aspx- XL-Add-ON
http://blogs.msdn.com/petereb/default.aspx- Centeral Blog:
http://adriandownes.blogspot.com/- Planning Server Forum:
http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1871&SiteID=17- Paul Steynberg
http://paulsteynberg.blogspot.com- Nick Barclay
http://nickbarclay.blogspot.com/- Jason Morales
http://blogs.msdn.com/jasonmorales/default.aspx- Peter Eb
http://blogs.msdn.com/petereb/about.aspx- Sacha Tomey
http://blogs.adatis.co.uk/blogs/sachatomey/archive/2008/04/28/new-microsoft-insider-pps-planning-blog.aspx
During the coming weeks, I'll publish posts about in-depth Planning Development Issues. Have Fun and Be I,
Downloads available from Microsoft:
- PerformancePoint Server 2007 Contoso Demo Data - contains data for the Contoso application of PerformancePoint Planning and workspace of Monitoring and Analytics. This demo application was designed to illustrate real-world business scenarios on performance management areas like statutory consolidation, Planning/Budgeting/Forecasting, KPI, scorecard, dashboard etc.
- Windows Azure Tools for Microsoft Visual Studio October 2008 CTP - for Microsoft Visual Studio extend Visual Studio to enable the creation, building, debugging, running and packaging of scalable services on Windows Azure
- Windows® Azure Software Development Kit (October 2008 CTP) - provides developers with the APIs, tools, documentation, and samples needed to develop Internet-scale applications that run on Windows Azure
- Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies - a Web download that provides features for running a report server within a larger deployment of Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007.
Best news I have heard in awhile – the long-awaited SP2 for Performance Point Server 2007 is coming out… before the New Year?
Performance point server SP2 is planned to be release on December 2008.
We are currently waiting for the beta version, due in a couple of days.The dev team has relased some nuggets regarding the upcoming release:
1. The support for SQL Server 2008 has been established:
a. SSRS report view now supports the SSRS2008 connection.
b. Setup up over SQL Server 2008
2. Support for virtualization environments:
As previously mentioned by Norm's blog post:
http://blogs.msdn.com/normbi/archive/2008/10/14/performancepoint-server-sp2-hyper-v-is-supported.aspx
Performance Point Server would support virtualization (using Hyper-V),
you can read further about it at:
http://support.microsoft.com/?kbid=957006
Since Planning Server requires wide topology, this issue could change the future architecture of planning implementation.
Performance Point Server SP2 pre-release nuggets - BeI - Microsoft Business Intelligence
Some more documentation coming shortly.
The Planning Business Modeler topics are now "out of the box". These topics are organized by category rather than table of contents. The content is very good. In coming weeks, we will cross-reference between TechNet and Office Online so that customers have a better experience (less fragmented) reading our documentation.
- Overview
- Getting started
- Accessibility
- User interface
- Concepts
- How do I
- Assignments
- Jobs
- Associations
- Assumptions
- Business rules
- Business rules tasks | Business rules overview | Business rules templates | More...
- Cycles
- Dimensions
- Predefined dimensions
- Models and model sites
- Forms and reports
- Planning an application
- Process scheduling
- Security and Roles
Work Around:
You can run the following commands to install PerformancePoint making it ignore the prerequisites check, and ultimately enabling the 'Next' button.
To skip check for Planning Server:MSIEXEC /i PPLSrv.msi SKIPREQCHECK=1
To skip check for Monitoring Server:
MSIEXEC /i PSCSrv.msi SKIPREQCHECK=1
PerformancePoint Install - Failed Windows Server 2003 SP1 - Dan Lewis
It is good to know that PerformancePoint Server (PPS) Service Pack 2 will support Hyper-V. The news is located on a Microsoft server software and supported virtualization environment list.
This article discusses the support policy for running Microsoft server software in the following supported virtualization environments:
- Windows Server 2008 with Hyper-V
- Microsoft Hyper-V Server 2008
- Server Virtualization Validation Program (SVVP)
MSDN Blog Postings » Blog Archive » PerformancePoint Server SP2: Hyper-V is supported
Tip from the forums:
From Planning Business Modeler | Process Management | Cycle Instance | Form | Select the Assignment on the Tasks you can Export the Change list as CSV files. The exported file can be accessed outside the application.
Availability of the Changelist field of Submissions table. - TechNet Forums
While struggling with NAV/SQL performance issues for many years I scribbled down countless pages about various issues, learned by own experience or from colleagues, taken from numerous knowledgebase articles, whitepapers, etc. or heard in several webcasts.
In 2006 I started to put all this into a structured document, one I could use more straight in my optimization workshops - so "The NAV/SQL Performance Field Guide" was born. It's not a replacement of any official NAV/SQL documentation and requires some pre-knowledge about certain issues, but this "braindump" should give feasible advice and practical hints for the daily work with NAV and SQL.
The NAV/SQL Performance Field Guide - 3rd Edition - Dynamics NAV Performance on SQL - My Two Cents
System constants (preprocessor commands) within PerformancePoint rules are available here.
Kevin talks about the different types of business rules in PerformancePoint.
I have described the behavior of PPS rule sets below. Any rules that are added in these rule sets follows the described behavior.
Kevin White's PerformancePoint Blog : A brief overview of PerformancePoint Planning business rules
This page contains links to blogs relating to Microsoft Dynamics GP
PerformancePoint Monitoring server has a few style sheets that can be adjusted in order to modify settings in your deployed dashboards.
It is usually recommended that all rules be written in PerformancePoint Expression Language (PEL) in to ensure upgrade compatibility.
However, some rules (either throwaway or complex) need to be driven by either Native SQL or MDX code.
Here is a blog posting outlining how to write a Native SQL rule.
In some difficult situations you might want to use NativeSQL to implement a business rule. It gives you the full power of SQL Server.
First you need to create the rule in the Business Modeler to host the NativeSQL business rule. You can use parametres and even use members of dimensions as parameters. When it is time to execute the business rule the user gets a nice interface where the user is able to select the parameter in the dimension.
In the SQL it is the memberId of the dimension that is passed into the function and they are of the type int. A start of an implementation can be seen here:
create proc Calc.[sp$procedure] @entity int, @scenario int, @startTimePeriod int, @endTimePeriod int as
It looks almost as a normal stored procedure in SQL. The only difference is that the name is a preprocessor commands and the Performancepoint translate this to a real stored procedure name, when it is created by the Performancepoint in the SQL Server. The rest of the parametres is just like a normal stored procedure.
A colleague of mine offers this troubleshooting checklist when you just can't get the PerformancePoint Planning Excel Add-in to recognize input boxes in an assignment.
PerformancePoint Planning - Input Forms Checklist
Over the course of the past year I’ve had a number of conversations on what type of business rule to choose for a PerformancePoint model. The answer is always - ‘it depends’. Choosing the right type of business rule depends on the the context of your business requirements. For example, technical accuracy of a rule (i.e. Sales Volume * Product Price) can be done through any rule type - but choosing the right rule type requires you to consider how the resulting data is processed and stored in the system and how that impacts or aligns with your business requirements.
I have described the behavior of PPS rule sets below. Any rules that are added in these rule sets follows the described behavior.
MSDN Blog Postings » Blog Archive » A brief overview of PerformancePoint Planning business rules
Currency may not change that much, but how about employee or department?
Peter has a way to hack PerformancePoint Planning into using dynamic members.
Then click Validate RDL to make sure there are no syntax errors. Then click ok and the report will be refreshed. Now using the filter should give the same values as the static selection, but when a new currency member is added with a new value for the property MemberName it will automatically appear in this list. Voila! Not so simple, but not so hard.
Peter Eb. : HOWTO: Dynamic rows or columns with a dimension property filter
What would be nice is to see an example of a web-based or Excel-based data entry solution for adding members to the model.
Something like this:
http://www.oraxcel.com/projects/sqlxl/
or this
http://msdn.microsoft.com/en-us/library/aa203746(office.11).aspx
or this
http://www.perlielab.com/CallistoPages/datamanager.html
or how about creating a SQL job on the server that polls published spreadsheets or Sharepoint lists for data and loads them into the staging & app databases and processes the model?
This would allow for things like headcount planning and adding new products or customers to a model without modeler permissions.
Sounds like a lot of work for something that should really be in the add-in as a feature (member management).
Perhaps Microsoft MDM will incorporate this feature into PerformancePoint and/or Excel?
At least that's what the roadmap tells us...
Take out the cubes. Any cubes that you do not want to demo or try out,
1. From a command line (or with services console) NET STOP MSSQLSERVEROLAPSERVICE
2. Create a new directory called data2 and copy files/directories below into the directory from data directory.
3. Rename the data directory to data_backup.
4. Rename the data2 directory to data.
5. Start up Analysis Services.
Because it is not precaching anything, I noticed a huge improvement in loading the service and connecting to AS. No more all-consuming memory bloat.
This is good if you would like to use the All Up BI VPC with your own demo data and have no interest in the samples provided.
If you need any of the sample database, copy the directories and xml associated to them back in with above steps.
If you're building PerformancePoint Planning models for training purposes it should speed up the deployment immensely.
Learn, Build, Grow...the future of BI: BI VPC 6 now available
In the current release (CTP4), the only way to create a model with more than one measure is to export the application metada to a XML file, manually edit it and import the updated metada. That can be achieved following the steps listed below.
André Vala : PPS 2007 Tip #1: Creating Models with Multiple Measures
Some useful info for tracking the results of PPSCMD.exe into a log file.
Your post did point me in the right direction. However, there are two parts to a workable solution:
1) There is the *undocumented feature* for ppscmd which is using custom handle 3 for output (instead of stdout which is handle 1) so you have to use 3>&1 in you command argument. I was lucky, I guessed...
2) To implement a redirect in SSIS Execute Process task you have to use a workaround using cmd /C as your executable.
http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx
Put together, it willl look something like cmd /c ppscmd [arguments] 3>&1 >> mylog.txt.
The Business Intelligence Update - September 2008
HOT TOPICS
Register NOW for the 2nd Annual Microsoft BI Conference - click here!
25% Discount Promotion - Microsoft Office SharePoint ECAL / PerformancePoint Server CAL
*** extended through September 30, 2008
Assessment Tool for Business Intelligence
- compare your infrastructure against industry best practices and the latest IT research
Online Demo: Geospatial Business Intelligence
- by integrating Virtual Earth and PerformancePoint
- Blog: More on Virtual Earth & PPS
Business intelligence basics: case studies and job advice
Five keys to hiring (good) business intelligence professionals
Leveraging business and marketing skills for a business intelligence (BI) career
UPCOMING EVENTS
How does it all stack up?
6 part Seminar Series, sponsored by Inetium & Microsoft
Sept 18
Omaha, NE
Click here to register
Dashboard in a Day - sponsored by Quilogy
Sept 16 - Omaha, NE
Sept 18 - Chicago, IL
Click here to learn more and register
The Next Generation of Business Intelligence
Executive Roundtable Breakfast Series, sponsored by Neudesic & Microsoft
Sept 18 - Denver, CO
Sept 30 - Las Vegas, NV
Microsoft BI Conference
Oct 6-8 - Seattle, WA
Managing Performance with PerformancePoint
Planning, Financial Reporting, Monitoring and Analytics for Dynamics GP 10
Oct 8 - Mountain View, CA
Click here to register
PASS Community Summit 2008
Nov 18-21 - Seattle, WA
Click here to learn more
UPCOMING WEBCASTS
* all times listed below are for Pacific time zone
Microsoft Business Intelligence for Retail - sponsored by Solver
Sept 11, 11am
Click here to register
Using Your SharePoint Portal to Communicate Business Intelligence presented by Quilogy
Sept 11, 1 pm
Click here to register
Achieving Business Intelligence Across the Enterprise presented by Thorogood
Sept 18, 9am - Realize Fast & Effective Planning, Budgeting & Forecasting
Click here to register
NEWS LINKS & PRESS RELEASES
SQL Server 2008: First Impressions
BIScorecard Rates Eight Leading Products
An Effective Business Intelligence Strategy is the Key to Business Success
Next-generation BI at Hand, Forrester Reports
Gartner lists Microsoft India as leading vendor of BI
The Business Intelligence Model of Conflicting Interests
Is Excel a Complete BI Solution?
Pervasive business intelligence: Are organizations really ready?
Seven Steps to Successful BI Competency Centers
Business Intelligence: Distilling Raw Data into Useful Information
The Business Intelligence Education Problem
Full Circle: Decision Intelligence (DSS 2.0)
Contact Me if you'd like to receive monthly emails of Jason Morales' Microsoft BI Update
Jason Morales' Microsoft BI Update : The Business Intelligence Update - September 2008
If you're going to be coding in Excel Services, you may need to work with impersonation for the web service credentials.
This article could help.
If you're trying to do the same thing with a local account, or a domain account on a Windows 2000 or Windows XP box, you'll need the user's password to get a token for it. Where are you going to get the user's password, though? If you need to do this sort of thing, prompt the user for a password (HowToPromptForAPassword). If the user won't be present, then you'll need to store the password someplace where the machine can read it. This is really bad news because, even if you do this as carefully as possible (HowToStoreSecretsOnAMachine), root compromise of the machine eventually leads to compromise of these secrets, and you can't prevent this. This should be a major consideration in your threat model (WhatIsThreatModeling)!
Access Excel Services from any web page using Ajax and this wrapper.
I have had this on my table for a while already and have been meaning to make it available for everyone to use/modify. The idea behind this example is to wrap the Excel Web Services API so that it is available for call inside Internet Explorer. The idea is that people will be able to leverage models inside workbooks just like C# code.
The attached file (ExcelServicesAjax.js) needs to be placed inside the _layouts directory of your SharePoint server. In yout HTML files, you then need to reference the javascript file. That will give you access to the functionality.
As part of your dashboard or planning project, or separately, you may be investigating Excel Services. It does not have all of the features of Excel in the browser. Data entry isn’t supported. Charts are different. Macros aren’t supported, though UDFs are and you can call Excel Services from VBA. It’s usually hosted in Sharepoint, though Excel Services is just a web service that’s accessible via custom apps.
Supported and unsupported features when loading a workbook
It is, however, a good way of keeping one version of the truth around in a central, protected format and is easily understandable by the business. And there are some other possibilities like using the Excel Web Companion, and saving data using Excel Web Companion. The Save As feature may provide an interesting way of doing some adhoc forecasting & brainstorming online.
Spreadsheet hell comes to Sharepoint!
A common requirement when publishing a Microsoft Office Excel 2007 workbook that is connected to an external data source on Excel Services is to ensure that users always view a consistent set of values and formula results, and that the workbook uses secure and up-to-date connection information. This is sometimes called obtaining "one version of the truth".
For example, if you are reconciling budget data each quarter between different departments to provide a company-wide budget summary, you need to ensure that everyone is working with reliable and consistent data to ensure that the process goes smoothly with minimal disruption and disagreement over differing values between workbooks and the timing of formula calculations and results.
Roadmap for publishing an Excel workbook as "one version of the truth"
Using charts and PivotChart reports in Excel Services
Using named views to show/hide charts.
View a named item in Excel Services
Tying back to external systems
Using hyperlinks in Excel Services
Drilling to details in a cube/pivot table
Expand or collapse data details in Excel Services
Drilling to details with outline
Outline data in Excel Services
Custom Properties of the Web Part
Create a menu to show/hide named items.
Connect a List View Web Part to Excel Web Access
More blogs and postings
http://blogs.msdn.com/cumgranosalis/archive/category/12700.aspx
http://blogs.msdn.com/excel/archive/category/11361.aspx
http://blogs.msdn.com/luisbeonservices/
Creating Business Applications by Using Excel Services and Office Open XML Formats
Use members from an Analysis Services dimension as parameters with the Single Select web part.
Recently Sacha published links to some of the nuggets that were posted here from the training course I went on a few weeks ago. I got a lot of traffic from those links so thanks!
Some of the nuggets were straight out of my shorthand notes, which, looking at them now, may not make a lot of sense.
Will try to clarify some of the ones he pointed out to me. His comments in bold, mine in italics.
• Remove blank columns and remove blank rows in the Report Properties - Matrix properties
Apparently this improves performance. It is a setting in the Excel Add-in under Report Properties.
• Create a separate section with "Suggested changes" with read/write access or use Sharepoint Library. Create Groups for Detail & Summary reports.
This was a suggestion to add a spreadsheet with “Suggested changes” for users to add their feedback. Another suggestion was to use the Grouping features in Excel to create Summary/Detail sections with expand/collapse capabilities. Users like these simulated drilldown capabilities.
• Select False to validation will ignore unknown member data.
This was in reference to a parameter used with the Label stored procedures for staging and validatating dimension members. The parameters are mutually exclusive, that is you need to run the procedure twice with the different parameters to validate annotations & validate members.
• Always set cycle dates to at least 1 day after the current date, then manually instantiate the cycle afterwards. This solves issue with waiting for cycle to appear automatically.
You can manually instantiate a cycle even if you leave it to start on the current date. Not sure of the benefit of adding a day. You can either see if the cycle starts straight away or manually instantiate it – during the build phase I tend to reduce the polling time to around 10 seconds so you hardly ever have to wait. Just need to remember to reset it once live..
Setting the cycle date to 1 day after the current date mitigates the problem where sometimes cycles do not get instantiated. Was a suggestion from one of the attendees – reducing polling time is another good method.
• Create referential integrity within dimension properties by assigning references to other dimensions. Adding new properties will check data within the other dimension.
This was in reference to setting up dimension properties. Dimension properties can be pulled from other dimensions (or at least it is my understanding that a reference can be made to those.) In the dimension modeler, there is an automatic referential integrity check if this link is in place.
• All users have to have read access to all Account members. You have to design report without salary line or use separate models when dealing with separate information like salary. You can also create an alternate member set without Salary, and then Create a member set with Salary.
All users have to have read access to all account members - is that a statement? I'm not sure I agree.
I am not sure about this one either.
• Use fake data for IFRS - SOX requirements - when dealing with Salary information. Modeler should not have access to data.
• Create a property on Account dimension - Restricted. Assign restricted accounts. In Report Properties for Filters, Setup Filter to only show unrestricted accounts in the filter. Set True Hide & True Lock on filter.
There were some comments during the sessions about protecting restricted account data from developers. There is still not a good way of doing this in my opinion as data has to be seeded at the leaf level and available for rollups.
I've just posted a new video, and the first about PerformancePoint Server 2007. In it, I discuss just what PerformancePoint Server is and describe the three modules that make up the product. Those modules are:
- Monitoring - What is happening now?
- Analytics - Why is it happening?
- Planning - What do I want to happen?
The video describes how these pieces fit together to form a complete solution for business performance. This video runs 21 minutes.
This video, along with all others, can be found on the Videos page of the site.
For planning folks, this could probably be modified to read the metadata xml used in PerformancePoint Planning.
Microsoft Office PerformancePoint Server 2007 M&A stores all objects in it's metadata database (named PPSMonitoring in default configuration). Many of these objects are stored in SerializedXML column of the FCObjects table. So, it is very inconvenient to retrieve and search objects with criteria based od particular attributes (XPath / XQuery expressions would be necessary to perform the search).
But fortunately Nick Barclay has created great solution, which makes these operations much easier and straightforward.
This solution is called The PPS Monitor & Analyze UDF Project (MAUDF) and it is the project which delivers a set of useful views and UDF functions, which allow to access and retrieve metadata from PPSMonitoring database with using simple T-SQL queries.
Radoslaw Lebkowski's Blog: The PPS Monitor & Analyze UDF Project (MAUDF)
From Jon Crowell in the MSDN forums. How to create a forecast input form with rolling actuals.
Try this:
1. You need scenario and months on the columns.
2. Select Actuals and Forecast in scenario.
3. In Time.YQM, select the first month of the year (or whatever range you want to display) and select "Add Range" from the Time drop-down.
4. Select "from member to current month"
Your selected member should look like this: [Time].[YQM].[All].[2008].[Q1 2008].[January 2008] : Current Month + 0
5. Select the last month of the year (or whatever range you want to display) and select "Add Range" from the Time drop-down.
6. Select "from current month to member".
7. Increase the month offset to 1. (You may decrease the previous range by 1 and leave this at 0 depending on how your current period is set up.)
Your selected member should look like this: Current Month + 1 : [Time].[YQM].[All].[2008].[Q1 2008].[December 2008]
8. Click OK.
9. Select Define member intersections for columns.
10. Select "column by column".
You should see Actual lined up with your first time range and Forecast lined up with your second range.
11. Refresh your report.
12. In report options, set "merge and center headers" to false.
This approach will require you to edit the ranges at the end of each year, but will dynamically update for all other times. You can make this completely dynamic using custom MDX, but it's a little more complicated.
Sacha was kind enough to link to some of my posts from the Train the Trainer sessions in Denver.
He has published a simple GUI for the PPSCMD command.
Looking for some automation of jobs from here too and the undocumented commands.
PerformancePoint Server 2007 PPSCMD GUI
I've built a really simple GUI for a couple of commands of the PPSCMD utility. I always take far too long to work out the syntax and navigate to the appropriate directory (Yes, I ought to update the PATH environment variable) that I felt I could justify building a simple front end to help speed up the usage.
So far I've only implemented the MIGRATE and REPROCESS commands - I use these quite a lot outside of any automated SSIS package so they seemed the most sensible to implement in the GUI first. I do intend on extending it to encompass some of the other commands and I would welcome any feedback towards prioritisation, usage, features and the inevitable bugs. It's currently version 0.1 and more or less ready for 'Community Preview' - there are some omissions such as full error handling and validation that I do intend on implementing over time along with the other commands.
It's a .NET 3.5 application so you will need to deploy it to a client where you are happy to install .NET 3.5 if it's not already present.
You can download version 0.1 from here - (You will need to create a login first)
His memory settings seem very conservative but worth a shot implementing within PerformancePoint VPC.
You can decide on different values based on your workload. Keep in mind that less memory will mean less pages in memory (more IO) on SQL and potentially less space for Analysis Services to cache queries. Processing will also take a hit depending on size since it copies the cube into memory for processing.
- SQL Server Database - Set SQL to use max 40% physical memory
- Windows - Set <LimitSystemFileCacheSizeMB> to 20% of total, 65022 MB * .20 = 13004 MB. This limits Windows file system cache usage of memory.
- Analysis Services- Set SSAS to use max 35% memory, edit the msmdsrv.ini file (make backup first) and set <TotalMemoryLimit>35
Set <PreAllocate>35 (this means total percent of physical), to preallocate memory at server startup. This helps for NUMA enabled servers especially I found. More on SSAS properties can be found here and more tuning analysis Services can be found in this white paper.
If there are problems with runaway queries using too much memory, you can run ASGovernor to cancel queries if memory usage is greater than 40%. (5% past target for SSAS) It might be that this is too large and it needs to have a smaller threshold.
Rules
Data Input
Security Nuggets
Security is “different” in PerformancePoint Planning than in SQL or NTFS.
Mirror Migration
Debugging Rules
Forms Design
More nuggets:
Let's hope they get a similar update for PerformancePoint.
If you want to get ProClarity working with SQL Server 2008, you need the following hotfixes
More Nuggets:
@AnnotationOnly parameter - F - validate measures only. T - validate annotations only.
These styling tips are probably the most powerful nuggets I heard from one of the experts in the field:
In Excel 2007, click the office icon and select Excel Options. Under the popular section, choose show developer tab in the ribbon. Record a macro.
Select Home tab. Choose format as table. Select a new Pivottable style. Create the style. Each component of the style affects a different component in a PerformancePoint report/data input form.
Review the macro. Note these lines.
With ActiveWorkbook.TableStyles("PivotTable Style 1")
.ShowAsAvailablePivotTableStyle = True
.ShowAsAvailableTableStyle = False
End With
You’ll want to modify the macro so that ShowAsAvailableTableStyle is set to true and rerun.
This should allow you to create Pivot Style templates that should show up as Matrix Styles in PerformancePoint.
Name your Account members with a % in the name, then use conditional formatting to format values as percentages. This could also work with a ‘hidden’ member property. Create a member property called ‘hidden’ with a 1 or 0 and use conditional formatting to hide the rows.
Any accounts or members with ‘total’ in the name – use a conditional format to increase the size-bold the contents.
Eg. /* */, –, //
Nugget: Many of the sample labs for PerformancePoint suggest loading data from CSV files.
If the data already exists in another application database, why not try and create a Data Source connection in PerformancePoint administrator against the Staging or Application database dimension tables instead?
From Books Online:
Member Set (no mapping)
An additional way for you to manage dimensions by organizing dimension members. A flat member set has its members organized as siblings or a member set can be organized into a hierarchy as parent/child members. You must deploy your model site after defining or changing member sets to update the dimension
When you deploy a member set, you are actually deploying a copy of the dimension with a different structure. These sets can be used to set granularity of the fact data within a model/cube. You can have only one member set for each dimension assigned to a model, though you can create multiple member sets in each dimension.
For things like Exchange Rates, it may make sense to have separate global assumption models for Annual and Monthly exchange rates for granularity.
You may also want to sort the data in the dimension a different way, which is where a member set comes in handy.
Member View (no mapping)
Hierarchies of dimension members that are grouped according to member properties and they provide an alternative way to create reports on the members of a dimension using dimension member properties for analyzing groups of related members.
Member View is an essential feature of the Planning Business Modeler. It allows you to view a hierarchical order of the member properties and to get detail data related to it in a comprehensive manner. Member View is an option in Dimensions that helps to create reports, but not build models. Member View also enables you to calculate the sum of all the dimension members within the property. For instance, A car manufacturing company might have a product dimension, that has its members like sporting cars, family cars and so on. Here properties can be All Sporting Cars in Red. You can make use of the Member View to view the hierarchies of the dimension members that are grouped according to their properties.
With the application of Member View you can find the total sales of sporting cars at a particular level of dimension. Dimension members are the primary data for creating models but Member View is not used for building models. To create member views, you need to go to the Site Browser pane and select Dimension. On selecting a particular dimension, find Dimension Tasks to create a Member View. Just name the Member View in the dialog box provided and label it to identify later. The Description box should be filled with a short description of the Member View and you need to select a Member View. Add a property to the Member View and from here you can select the hierarchies for the properties. You can also select the Hide Members from View option if it is required. Finally click OK to save the Member View.
A member view is a subset or summarized version of the dimension members used in reporting. Member views are grouped by member properties. These are used to calculate sums and define hierarchies against member properties. Eg. Region - Country – City.
Welcome to the 5th issue of the Microsoft Business Intelligence Newsletter! The 'Spotlight' article for this August issue features BusinessWeek Research Services Report: "The ROI of Pervasive Performance Management." This downloadable whitepaper includes in-depth telephone interviews with executives at large and midsize companies known to be employing Performance Management and using Business Intelligence tools. And, of course, we have included great customer success stories, partner-ready tools and product news. We hope that you find the newsletter to be a valuable resource and we encourage you to share it with your colleagues!
http://www.microsoft.com/bi/newsletter/BI_Newsletter_August08.htm
Paul has mixed results with Management Reporter, and some response from the product team that performance issues will be addressed in V2.
After months of testing PerformancePoint Server Management Reporter in it's current state, I have concluded that it will not handle the volumes that our company requires.
Financial Systems and Business Intelligence
One workaround could be to allow the users to design their own reports, and then have a promotion process to translate the reports for performance and scalability using summarized queries and tools such as Reporting Services and PerformancePoint M&A.
Of course, this gets rid of the idea of ‘self-service reporting’.
Perhaps quantifying the volumes that Paul’s company has could tell us more about MR’s limits?
Nugget: pass@word1 is really tough to type with certain international keyboard layouts and Virtual PC. Use pass$word1 instead, or better yet a strong-password that isn’t readily obvious.
3. The New User dialog box appears. Enter the following information for the new account:
User name: PPSAdmin
Password: pass@word1
Establish. Execute. Evolve. ®: On the Rational Guide to Planning with PerformancePoint Server 2007
http://blogs.msdn.com/robcaron/archive/2007/01/02/1400971.aspx
Social Networking plugin integrated with PerformancePoint and Sharepoint.
Investing in the SharePoint Platform
As we look towards the future, we plan on investing further in integrating with SharePoint’s records management capabilities so that any to all blueKiwi conversations can be retained as corporate records. We will also include several Community dashboards that leverage SharePoint’s KPI Web Parts and Report Center functionality as well as the integration of our social metrics into balanced scorecards built using Microsoft PerformancePoint Server.
How We Did It: blueKiwi SharePoint Connector and OfficeAssistant | SharePointed
Here is a list of and links to our existing security documentation — organized according to our PPS book categories (Architecture and Planning, Deployment, and Operations):
Architecture and Planning
- Planning Application Business Objects (Security)
- Planning Server security
- Security Roles - definition of
- Authentication
- Authorization
- Planning Server administrative roles
- Planning Server business roles
- Predefined functional roles for workflow
- Communication security
- Kerberos delegation for Planning Server
- Secure Sockets Layer (SSL) security
- SQL Server security
- Analysis Services security
- Native SQL and MDX security
- Identify risks and define mitigation plans
- Security (implementation) considerations (Business roles, Administrative roles, Deployment accounts)
- Monitoring Server security options
- Monitoring Server account considerations
- Monitoring Server application pool accounts
- Monitoring Server security considerations (Authentication, Application security, Kerberos delegation security)
Deployment
- Deploying Planning Server securely
- Enabling Secure Sockets Layer (SSL) security for Planning Server
- Planning Administration Console access security
- Kerberos authentication and delegation for Planning Server
- Configure Planning Server prerequisites for Kerberos authentication
- Configure Planning Server for basic Kerberos authentication
- Configure Planning Server for Kerberos delegation by using a domain user account
- Configure Planning Server for Kerberos constrained delegation
- Using Planning Server roles
- Granting users permissions to access the Planning Server application database
- Configuring data source credentials
- Monitoring Server application pool identity account considerations
- Deploying Monitoring Server securely
- Enabling Secure Sockets Layer for Monitoring Server
- Kerberos authentication and delegation for Monitoring Server
- Configure Monitoring Server prerequisites for Kerberos authentication
- Configure Monitoring Server for basic Kerberos delegation
- Configure Monitoring Server for Kerberos delegation by using a domain user account
- Configure Monitoring Server for Kerberos constrained delegation
Operations
- Security roles
- About configuring staging database permissions
- Create a login
- Grant access to the database
- Grant access to database objects
- About synchronizing dimensions and models (Security)
- About loading dimensions (Security)
- About loading models (Security)
- Scriptable data integration (Security)
- Upgrading from a prerelease version of Planning Server (Security requirements)
MSDN Blog Postings » Blog Archive » PerformancePoint Server Security and Kerberos
This post originated from and is provided by the MSDN Blogs RSS feed. The original post of the article can be found here.
ProClarity (PAS) and SQL Server 2008
If you want to get ProClarity working with SQL Server 2008, you need the following hotfixes
Microsoft Business Intelligence Blog : ProClarity (PAS) and SQL Server 2008
Thanks to Dan Wilmot for this whitepaper.
Many users need the ability to run a query against an SSAS cube that includes a date range. Typically this date range consists of a “start date” and an “end date” selection. The default behavior of dimensions in the cube makes this type of selection difficult. This paper discusses how to design your cube to include a new “Start Date” and “End Date” dimension, then how to add those dimensions to the cube, and finally how to build a named set that represents your date range.
Excellent "best practices" guide to designing a Planning application in PerformancePoint.
Applies to: Microsoft Office PerformancePoint Server 2007
This document provides guidance and best practices for designing a Microsoft Office PerformancePoint Server 2007 Planning application.
http://office.microsoft.com/download/afile.aspx?AssetID=AM102830551033
Populating actuals in PerformancePoint isn't necessarily trivial.
Once you have created the staging database and synchronized tables into the database, you need to modify the contents of staging to include dimensions, hierarchies, and measures.
Usually, actuals are the first thing to go into a planning application, as variance reports are important along with seeded data.
This table holds the key to parameters for the [dbo].[bsp_DI_CreateLabelTableForMeasureGroup] stored procedure.
SELECT * FROM FK_Relationships
eg.
[dbo].[bsp_DI_CreateLabelTableForMeasureGroup] 'AppRoot:FinancialWithShares', 'MG_FinancialWithShares_MeasureGroup_default_partition'
Once this table is created:
select * from MG_FinancialWithShares_MeasureGroup_default_partition_label
This shows what rows are required. The PPS Operation document has some examples of this.
Modifying our Actual population view.
alter view vActualsByCostCentre_Unpivot
as
SELECT 'ACTUAL' as [Scenario_MemberId_Label] ,[Time_Month_Label] ,[Account_MemberId_Label] ,'INPUT' as [BusinessProcess_MemberId_Label] ,'Open Text Corporation Canada' as Entity_MemberId_Label ,'PERIODIC' as TimeDataView_MemberId_Label ,'NONE' as Flow_MemberId_Label ,[Cost Centre_MemberID_Label] ,'NONE' as Department_MemberId_Label ,'NONE' as Product_MemberId_Label ,'USD' as Currency_MemberId_Label ,[Value] ,null RowID ,200 as [BizSystemFlag]
FROM(
SELECT [CostCentre] as [Cost Centre_MemberID_Label] ,[Label] as [Account_MemberId_Label] ,[July Year2007] = [Jul07] ,[August Year2007] = [Aug07] ,[September Year2007] = [Sept07] ,[October Year2007] = [Oct07] ,[November Year2007] = [Nov07] ,[December Year2007] = [Dec07] ,[January Year2008] = [Jan08] ,[February Year2008] = [Feb08] ,[March Year2008] = [Mar08] ,[April Year2008] = [Apr08]
FROM [ActualsByCostCentre]) [Value]UNPIVOT ([Value] FOR [Time_Month_Label] in ( [July Year2007] ,[August Year2007] ,[September Year2007] ,[October Year2007] ,[November Year2007] ,[December Year2007] ,[January Year2008] ,[February Year2008] ,[March Year2008] ,[April Year2008] ) ) AS UNPVT
Then
INSERT INTO MyApp_StagingDB.dbo.MG_FinancialWithShares_MeasureGroup_default_partition_label([Scenario_MemberId_Label] ,[Time_Month_Label] ,[Account_MemberId_Label] ,[BusinessProcess_MemberId_Label] ,[Entity_MemberId_Label] ,[TimeDataView_MemberId_Label] ,[Flow_MemberId_Label] ,[Cost Centre_MemberID_Label] ,[Department_MemberId_Label] ,[Product_MemberId_Label] ,[Currency_MemberId_Label] ,[Value] ,[RowID] ,[BizSystemFlag])
SELECT [Scenario_MemberId_Label] ,[Time_Month_Label] ,[Account_MemberId_Label] ,[BusinessProcess_MemberId_Label] ,[Entity_MemberId_Label] ,[TimeDataView_MemberId_Label] ,[Flow_MemberId_Label] ,[Cost Centre_MemberID_Label] ,[Department_MemberId_Label] ,[Product_MemberId_Label] ,[Currency_MemberId_Label] ,[Value] ,[RowID] ,[BizSystemFlag] FROM [MyApp_SourceDB].[dbo].[vActualsByCostCentre_Unpivot]
The last SQL step after populating the label table?
DECLARE @return_value INT
EXEC @return_value = [dbo].[bsp_DI_ConvertLabelColumnToMemberIDForMeasureGroup] @ModelName = N'MyApp:FinancialWithShares', @MeasureGroupTableName = N'MG_FinancialWithShares_MeasureGroup_default_partition', @OverwriteExistingData = N'F', @IncludeValidation = N'T',@IncludeAnnotation = N'F'
SELECT 'Return Value' = @return_value
GO
The modeler can now go into the application and Load this data from Staging Area.
Rather than this approach, I would recommend creating an AppName_SourceDB SQL database for your application data and loading the CSV files into this source database. The cut-paste method works, linked tables from Access, or the more robust SSIS ETL or your favourite ETL tool.
After the source database is created and tables populated, they can be added to the model by creating a Source Data connection in Planning Administrator. Views in this SourceDB could be created to point directly to the source database, using linked servers and even synonyms for increased abstraction. These views can be added to the Source Data connection. The SourceDB acts as mapping warehouse and allows for ease of rerunning data and reuse for other purposes.
When the connection has been created, it can be loaded from the modeler by using the Load from Source Data
Dimensions
Load dimensions via .csv files:
The .csv file must adhere to the following format.
- Cell a1 = ! dimension = dimension name
- Cell a2 = ! hierarchy = All Members
- Cell a3 = Label (value required)
- Cell b3 = Name (value required)
- Cell c3 = Description (value optional)
Values in the Label and Name columns are required. Values in the Description column are optional, but the header is required in the template.
When dealing with importing Excel spreadsheets and cross-tab reports, UNPIVOT is your friend.
This will translate a column-based date data format to a row-based format which can be used in PerformancePoint.
create view vActualsByCostCentre_Unpivot
as
SELECT [CostCentre] as [CostCentreLabel]
,[Label] as [AccountLabel]
,[MemberID]
,[Value]
FROM
(
SELECT [CostCentre]
,[Label]
,[20070701] = [Jul07]
,[20070801] = [Aug07]
,[20070901] = [Sept07]
,[20071001] = [Oct07]
,[20071101] = [Nov07]
,[20071201] = [Dec07]
,[20080101] = [Jan08]
,[20080201] = [Feb08]
,[20080301] = [Mar08]
,[20080401] = [Apr08]
FROM [ActualsByCostCentre]) [Value]
UNPIVOT ([Value]
FOR [MemberID] in
([20070701]
,[20070801]
,[20070901]
,[20071001]
,[20071101]
,[20071201]
,[20080101]
,[20080201]
,[20080301]
,[20080401])
) AS UNPVT
Give the New PIVOT and UNPIVOT Commands in SQL Server 2005 a Whirl
How to extend the size of a virtual image (VHD)?
Let’s assume you have an EPM demo VPC with one disk and one partition of 19.53GB and you’d like to extend it to install additional software.
Download VhdResizer, open the VHD you want to update and set its new size to 25GB for instance (Dynamic type)
- Boot VHD using Windows PE (Windows Preinstallation Environment (Windows PE) 2.0 is a minimal Win32 operating system with limited services, built on the Windows Vista kernel) for instance
Go at the command line and launch DISKPART
LIST VOLUME (to identify VHD volume to extend)
SELECT VOLUME n
EXTEND- Done! (LIST VOLUME will show you the extended size)
Start the VPC and the virtual C: partition should now be 25GB (the physical VHD size should not change since it’s a dynamic disk)
MAUDF Project
The MAUDF project is a series of CLR table-valued User Defined Functions that facilitate access to metadata for elements published to a specific PerformancePoint Server 2007 Monitoring and Analytics (M&A) instance. The aim of the MAUDF project is to make otherwise hard-to-get-at metadata more accessible through these functions. Because the data exposed is tabular this provides an easier way for system administrators and developers to surface useful information contained within a particular Monitoring server using just T-SQL.UDF List
UDF
DescriptionElementMetadata
Element level metadata common to all elements e.g. name, description, display folderElementMemberships
Lists each membership domain account the assigned privileges for an elementElementCustomProperties
Lists information for all custom properties that exist on an elementKpiMetrics
Lists metadata for each actual and target metric within a kpiKpiTargetThresholds
Lists detailed threshold settings for each target metric within a kpiScorecardMetadata
High level property settings for published scorecardsScorecardKpis
List of all kpis referenced within a scorecardScorecardAnnotations
Lists all annotations and associated comments associated with a scorecardReportMetadata
High level metadata for published reportsIndicatorMetadata
High level metadata for published indicatorsIndicatorLevels
Lists properties for each level defined within an indicatorDataSourceMetadata
High level metadata for published data sourcesDashboardItems
Lists metadata for each scorecard, report and filter item referenced within a dashboard
PerformancePoint Monitor & Analyze UDF Project (MAUDF) - Home
Current Features
PerformancePoint (PPS) Publishing
Moves elements from one server to another, adjusting server specific settings for related elements automatically including:
- PPS Data Connections
- Proclarity Analytic Server Report Links
- Excel Service SharePoint Links
- Excel Service Parameters
- Add/Remove Credentials
Proclarity Analytic Server (PAS)
- Update report page connection properties to redirect to a different SSAS server.
Excel Services
- Dynamically change link to Office Data Connection file.
- Provide a seamless way to create a list of Links to Proclarity Analytic Server reports
Configurability
- Net configuration API is used, allowing flexible XML Configuration and validation.
- Command Line tool execution, Output sent to tracelistener and is configurable.
- Use MSBuild or WIX to inject properties into the Config file before execution, to truly integrate this in the build/deploy environment.
CRM Accelerators – Part I – Analytics Accelerator
Last week we introduced the CRM Accelerators program for Microsoft Dynamics CRM customers and partners, each week we will unveil more details of each accelerator. In this article we feature the Analytics Accelerator.
Analytics Accelerator – Due to be released in Q3 2008
Many customers and partners will be familiar with Analytics Foundation for Microsoft Dynamics CRM 3.0 which was a business intelligence feature pack for Microsoft Dynamics CRM 3.0. Analytics Foundation for Dynamics CRM 3.0 included Dashboards, Key Performance Indicators (KPI’s) and a suite of new reports. Analytics Accelerator for Microsoft Dynamics CRM 4.0 expands on this, and is designed to support a range of business intelligence “maturity” levels for customers and partners. The Analytics accelerator has been built to cater for differing business intelligence skill-sets and experience levels. Elements of the accelerator include:
- Sales, service and operations dashboards delivered with Microsoft SQL Server® Reporting Services (SSRS).
- Sales, service and operations dashboards and scorecards delivered with Microsoft PerformancePoint® Server.
- Additional reports delivered with SSRS.
- Two Online Analytical Processing (OLAP) cubes which are designed to allow customers to conduct in-depth analysis and key performance indicator (KPI) monitoring based on Microsoft Dynamics CRM 4.0 data. Customers can consume these cubes through Microsoft Excel®, Microsoft SharePoint® technologies and Microsoft PerformancePoint Server. The first OLAP cube is designed for simple setup and consumption, the second cube is more detailed and requires a more advanced knowledge of analytics and OLAP technologies within Microsoft SQL Server.
- Predictive analytics to provide statistical guidance in areas such as lead and sales opportunity management. There are several sample applications which ship as part of the accelerator that showcase how customers can leverage predictive analytics directly within the Microsoft Dynamics CRM 4.0 user interface.
- The analytics accelerator will work with SQL Server 2005 with some components enhanced to take advantage of SQL Server 2008.
The Analytics accelerator will be free of charge for Microsoft Dynamics CRM 4.0 customers and partners.
Microsoft Dynamics CRM Team Blog : CRM Accelerators – Part I – Analytics Accelerator
Some good links when dealing with the various complexities of PerformancePoint, SQL, IIS, and Sharepoint security.
- List and provide links to our existing security-related content.
- Solicit feedback regarding security-related content. For example:
- What are we missing?
- How can we improve what we currently have?
- Anything else you would like to share with us?
Here is a list of and links to our existing security documentation — organized according to our PPS book categories (Architecture and Planning, Deployment, and Operations):
Norm's PerformancePoint Server Blog : PerformancePoint Server Security and Kerberos
Not a lot of info on this yet.
Business Intelligence Studio (BIS) enables full utilization of all Microsoft Office PerformancePoint Server 2007 (PPS) features. It is a solution that accelerates the implementation of PPS, enables end user productivity and provides valuable budgeting/planning functionality to facilitate better Business Performance Management.
I’ve got my plane tickets booked for the August 18 – 22 training in Denver. Monitoring has received much wider adoption levels, probably due to the integration with existing Proclarity customers and product maturity. Not sure how many Biz # (predecessor to PP Planning) customers there were out there…
Planning should be a good field for extended learning as there are a lot of opportunities for improvements in this space.
PerformancePoint Planning Server training
Hi there! My name is Joanne Hendrickson and I work as a writer here at Microsoft on the PerformancePoint Server Content Publishing team. I, too, will be a regular contributor to this blog discussing all things PerformancePoint. I am passionate about getting you the clearest, most valuable content to help you solve your business problems using clean, simple approaches. I try to frame a solution answering the "Why?" and find that type of adult-learning approach can serve us all well as we tackle the challenges in front of us.
One great way to get in the know about Planning is to attend a Microsoft Office PerformancePoint Planning implementation workshop that will be offered at Microsoft's Denver offices starting in September.
These are PPS workshops, solely focused on Planning, that last 4½ days. As the course description states, this course: "walks through setup of a budgeting solution for an enterprise-level entity. Company and business scenarios are presented to the students with requirements and budget assumptions, offering a discussion on how to prepare for a Planning implementation. As part of the class deliverables, students will create an application, set up dimensions, models and load data. Profit and Loss budget input forms are built, including sub ledger detail forms such as Human Resources and Revenue modeling. Business rules are defined to further customize the input forms. Students will set up Security and define processes to replicate a real-world user experience. Reports are designed in various formats to show the end results needed to analyze the planning information." Now really, how fun is that?! Sign up! Any partners are welcome.
Here are the registration details:
- Microsoft® Office PerformancePoint™ Server 2007 Planning: Implementation Workshop I: September 8th: Denver
- Microsoft® Office PerformancePoint™ Server 2007 Planning: Implementation Workshop I: October 13th: Denver
- Microsoft® Office PerformancePoint™ Server 2007 Planning: Implementation Workshop I: November 17th: Denver
- Microsoft® Office PerformancePoint™ Server 2007 Planning: Implementation Workshop I: December 8th: Denver
In case your company has considered becoming a partner, see the Partner Program Web site.
Norm has some good info on PPS vs. Proclarity.
Here is response I found to the following questions.
- What’s in PPS and not in ProClarity ?
- What’s in ProClarity and not (natively) in PPS ?
Norm's PerformancePoint Server Blog
Another tip for speeding up the PerformancePoint VPC? Within the Planning Admin Console, there is a Planning Process Service option. This lets you set the time to poll for queue checking. The default is 60 seconds. It has been set to 4 seconds in the VPC. Decrease or increasing may slightly affect performance.
From the forums.
There is a way to see the latest data without closing and re-opening Excel.
In the Excel add-in, go to Offline->Manage Offline Assignments. Then check the box of your assignment, and click the refresh button.
One workaround for spreading across time (years to months) in PerformancePoint.
Spreading across Time
Unsupported through the excel interface. Problem is when you create an cycle - assignment, there is a concrete data collection period. You must enter data in that range AND you must enter data at the leaf level. one workaround is collect data for the year, and stick in a month in the future. For example you want to spread 12 months of data for 2008. You create a form and assignment and collect the data and put it in Jan FY09. Then you write a business rule and you scope the 12 months with the ":" operator and the this statement points to Jan FY09. then you can divide the number equally by 12. You can get more creative with your spreading algorithm too. The only thing, is you have to submit the JanFY09 number, and then run a job that runs the rule. So its a two step process.
An important component to PerformancePoint Planning is the Excel 2007 add-in. Here are a few ways to speed up Excel 2007 which may improve certain complex spreadsheets.
You can modify most slow-calculating worksheets to calculate tens, hundreds, or even thousands of times faster. The objective of this article is to review how you can speed up calculation by making the best use of the new features in Excel 2007, and by identifying, measuring, and then improving the calculation bottlenecks in your worksheets.
Tip #9001 – Full deploy of PerformancePoint model site. (from forums)
When you carry out a deploy of the model site within PBM, it only carries out an incremental deployment to AS. You can carry out a full deployment using ppcmd.exe, using the syntax:
ppscmd modelsitedeploy /full /server <planningserver url> /path <applicationlabel>.<modelsitelabel>
This will delete the AS database and re-create it. You may also find a bit more help in the PPS operations guide - http://technet.microsoft.com/en-us/library/bb838754.aspx
Callisto could be a valuable replacement for the Excel Add-in for PerformancePoint.
It supports Excel 2000, 2003, 2007.
It supports loading, extracting, and clearing data with the Planning Model directly from Excel.
It has the ability to perform driver-based forecasting, and appears to remove some limitations of the matrix control.
Worth a look.
Callisto - Excel Add-in for PerformancePoint
More on PerlieLab
Boosting the All-UP VPC Performance - Set max SQL memory to something reasonable instead of unlimited.
Modify IIS to disable logging for PerformancePoint Planning site.
Turn off performance counters.
Configure Analysis Services memory limit.
Setting MAX SQL Server Memory value - SQL Server Performance
Sean's tips on VPC performance:
Now, in order to set this post aside from some of the other "Virtual PC 2007 has been released!" postings, I thought I would share some best practices relating to the usage of Virtual PC that I have picked up from doing demos over the last year or so (as well as from an internal demo optimization doc that made its way into my inbox a few months ago):
- I'll start with the obvious… the more RAM the better. You need to have enough RAM to run your host operating system, your guest operating system(s), plus an additional 32MB of overhead for each guest OS that you will be running. From my personal experience, 2GB of RAM is a good starting point.
- A CLOSE second to RAM is your hard drive configuration. If you are running your Host OS and Virtual machines from the same hard drive, you are in for a painful experience. For good performance you will want to move the .vhd files off to the fastest secondary hard drive possible. For a secondary internal hard drive, SATA or SCSI is ideal, and for an external HD, pick up an eSATA drive. This advice is even more important if your computer is a laptop, as they often have slow 4200 RPM hard drives that will struggle with the concurrent I/O load.
- If your Antivirus allows you to exclude files or directories from realtime scans, exclude the directory that holds your images (and/or exclude the following extentions: .vhd .vud .vsv .vfd .vmc)
- Keep the host computer hard drives defragmented.
The smaller (or less fragmented) the VHD files the faster. Steps to shrink/defragment:
- In the VM run defrag (a couple of times). It might take a while (up to hours) but it is worth waiting for
Run the VM precompactor – this is an ISO image. Just attach it to the VM . The precompactor will zero all the unneeded space in the VHD
- Load the 'Virtual Disk Pre-Compactor.iso' from the Virtual Machine Additions Directory
- Answer 'Yes' to start the zeroing
- Shutdown the VPC when done
- Use Virtual PC Disk Wizard to edit and compact the pre-compacted .vhd
- Use a processor with Virtualization Technology (Intel Page, AMD Page). This is any reasonably recent processor. This will greatly speed up the OS boot time, OS installation time, and performance in non-Windows operating systems (as well as Windows installations that to not have the Virtual PC Additions loaded). On guest Windows installations that have the Virtual PC Additions loaded, performance is significantly enhanced), which brings us to:
- Load the Virtual PC Additions. These are included with Virtual PC, and will need to be loaded in the Guest operating system. This will load up drivers for the virtualized hardware, allow for folder sharing (with the host OS), drag-and-drop functionality (from the host OS), synchronize time with the host OS, and generally turn on "Teh Snappy"
Start up Virtual PC with the following option (Best way is to create a shortcut on your desktop): -usehostdiskcache From the command line reference:
Turns on host-side disk caching, which can improve performance of virtual machines running operating systems other than Windows. This parameter can be useful for resolving poor performance problems with disk intensive tasks. This parameter can be used only when starting Virtual PC.
- Do NOT use the /3GB switch on your Host machine. That switch increases the user memory space at the expense of Kernel space. Virtual machines use more kernel memory than user memory – so enabling this switch on the host will decrease the amount of virtual machines that you can launch
- I'll leave you with a tip that would seem to defy logic (and which I have not tried myself), but the word on the street is that you can increase performance by enabling NTFS compression on the directory that holds your Virtual PC image files (.vmc, .vud and .vhd). It seems that writing a big chunk to disk is slower than first compressing the chunk and then write it to disk.
Turn off what-if analysis in Excel forms and reports for improved performance.
What-is what-if?
When you set the Enable what-if analysis option to True for a matrix, PerformancePoint Add-in for Excel automatically performs what-if calculations that show the effect that the change will have on an assignment if it is submitted.
If this option is set to False, PerformancePoint Add-in for Excel does not apply any changes to the matrix. This can help improve query performance.
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.
some AddIn for Excel SP1 performance improvements details beyond the readme... - TechNet Forums
This may cause issues with PerformancePoint as we don't have control over the processing job, but let's set it to false and see.
Lazy processing allows fast updates to the dimensions. After an update, SSAS processing of aggregations and indexes is initiated in the background. As a result of lazy processing, you might see Analysis Server consuming CPU and disk I/O at times when no user activity is performed. To avoid lazy processing, you can set the ProcessAffectedObjects property as part of a dimension update processing command, causing all aggregations and indexes to be processed as part of the same transaction.
Let's bump this one up from 64 to 128. Let's not... :) Either this or the other setting maxed out the memory in the VPC...
MaxThreads
MaxThreads is a server property that controls how many threads will be spawned, at maximum for either query or process operations.
Bumping up (or tuning down?) performance.
Set this to -5 or -6 perhaps? I'm running a dual-core laptop, so this might improve things. Probably not a good idea if you're using a VPC which appears to use only 1 CPU...
CoordinatorExecutionMode
CoordinatorExecutionMode is a complex SSAS server property that controls the number of parallel jobs that Analysis Server will start. When operations (such as querying and processing) occur in parallel, performance can be significantly enhanced. However, too many parallel jobs on a server can decrease responsiveness to other operations. The CoordinatorExecutionMode property’s value has special meaning as it controls the SSAS coordinator component, as follows:
•
Positive number. Sets the absolute maximum number of parallel jobs per server.
•
Zero. Enables the server to automatically determine the maximum number of parallel operations, based on workload and system resources.
•
Negative number. Sets the absolute maximum number of parallel jobs that can occur per processor (as opposed to a positive number, which is per server).
Under the Advanced Properties of Analysis Services, set OLAP\Process\CheckDistinctRecordSortOrder to 0. (At your own risk!!! :)
CheckDistinctRecordSortOrder
CheckDistinctRecordSortOrder is a Boolean server property that dictates how record sort orders will be handled. When processing distinct counts coming from a relational data source, Analysis Services needs to have records in a specific order to ensure accurate counting.
If collations are different between Analysis Services and the relational source, an error might occur if this property is set to True. On the other hand, if collations are known to be the same, performance can be enhanced by setting the property to False, but no checking will occur.
Tool for PerformancePoint Migration
Microsoft has released the tool BI Report Automation Publisher to a wider audience. This is a command line tool with .Net library which makes life easier in moving PPS/ProClarity reports between servers.
Microsoft has built this tool mainly for PPS which can also be used for ProClarity and Excel Services reports to move to different environments. One can configure the server details as well. The tool is being enhanced by the Microsoft team add more features like
- PerformancePoint Target Location Redirection
- PPS Sql Server Reporting Services (SSRS) Report Support
- PerformancePoint Input from BWSX Files
- Deploying PAS Reports
- Deploying PPS Dashboard
- Updating Post-Deployed PPS Webparts
More details on the tool and source code is available in CodePlex . Its very interesting to see the number of hits on this site and the count on people who downloads this tool.
There's a lot of stuff on the MS BI VPC. Server operating system. SQL Servers & Analysis Services & Reporting Services. Sharepoint. IIS. PerformancePoint.
It's a wonder it even starts up at all.
I'm going to focus on a few ways to speed up the All Up VPC, besides adding more RAM, CPU, and fast disks.
First, get the latest All up BI VPC from MS downloads.
All-up BI VPC
For folks who want to play with our Business Intelligence technologies and don't want to bother with installing everything, you can download a virtual machine image that has just about everything installed you could want, including demo content.
The "All-up BI VPC" has two parts, available at:
Part One (3.4 GB)
Part Two (1.0 GB)
Yes - *giga*bytes. These are compressed, and will unzip to a vhd file for Virtual PC that's over 20GB in size. To run the virtual PC, I highly recommend at least 1GB free memory (so your PC needs at least 2GB). If you can free up more, that's better. Also try to work on a PC with two hard drives, and put this on the non-system drive.
Philo's WebLog : All-up BI VPC
Then do some reverse-engineering of queries to see what is going on.
Step 1 - SQL Profiler - Analysis Services
I noticed that the AdventureWorks_Mfg Analysis Services database uses Proactive Caching. Disabling this will help a tad, if you're not using it. You can do this by browsing to the cube in Analysis Services manager and right-clicking on the measure group partition.
Step 2 - SQL Profiler - SQL Server
I noticed that the bcp_BizSystemGetAllApps script is called all the time, and having it return the b.OriginalBMO xml column is a big drag on performance. I commented the column out of the proc's query with no ill effects (just yet... but beware.)
Step 3 - Add indexes
Add an XML index to the BizSystem table OriginalBMO column in the application database and watch the modeler fly. Actually, add XML indexes to all XML columns. Either it's in my head or this improved things dramatically. No more remote scans...
select * from sys.objects o inner join sys.columns c on c.object_id = o.object_id where c.user_type_id = 241 will get you a list. There are some in the application database and some in the PPSPlanningSystem database.
Note that any of these changes could break PerformancePoint. Try at your own risk!
Step 4 - SQL Analysis Services - Under the properties of the bi-vpc, turn off the flight recorder.
Step 5 - SQL Server - Take offline any databases you are not using. So long ProjectReal, you fake project!
Step 6 - Services - turn off Automatic Updates. Disk Defragmenter. Office Sharepoint Server Search. Office Document Conversions services, Windows Sharepoint Services Search.
A common request by form authors is to create a "start here" worksheet page where contributors can change some global filters. Then they advance to other worksheets in the workbook to do their actual data entry. Other times authors just want to link multiple matrices so that changing the filter on matrix A also changes matrix B at the same time. The way RDL is structured makes it natural for PPS to also define all filters as global filters: the add-in stores them naturally as ReportParameters in RDL which are global. Then each matrix is subscribed to the filter.
If you're not seeing any applications when connecting from the modeler, and perhaps you ran some DB upgrade scripts, chances are your applications are offline.
This can be changed by going to http://localhost:46788/Applications.aspx , connecting, clicking on applications and selecting each app to Take Online.
An interface would be good for stuff like this.
Two approaches exist for converting to label-based tables in preparation for populating PerformancePoint Server measure group tables.
- Use PPS stored procs - more of a production solution
- Use a join with source and staging and pull id's.
Here are the steps from the data integration help.
To prepare and load measure group data that have labels
Create a label-based table
For a specified measure group, use the bsp_DI_CreateLabelTableForMeasureGroup stored procedure.
For all measure group tables in a specified model, use the bsp_DI_CreateLabelTableForModel stored procedure.
Populate label-based measure group tables with data from the source database or system. This is a manual step.
Validate label-based measure group tables and correct any errors by using the bsp_DI_ValidateLabelTable stored procedure.
Repeat step 3 as required until all label-based data is valid.
Convert a label-based measure group table to a MemberID-based table
For a specified label-based measure group table, use the bsp_DI_ConvertLabelColumnToMemberIDForMeasureGroup stored procedure.
For all label-based measure group tables in a specified model, use the bsp_DI_ConvertLabelColumnToMemberIDForModel stored procedure.
About performing measure group validation in a model
You can use one of the following methods to validate measure groups before you load data from the staging database to the application database.
Validate data in a specified measure group by executing the Transact-SQL stored procedure, bsp_DI_ValidateMeasureGroup.
Validate all measure groups for a specified model by executing the Transact-SQL stored procedure, bsp_DI_ValidateMeasureGroupForModel.
Note
If you have questions about the process, don't forget to use the PerformancePoint Server forum. You can also send me a message if you are concerned about if a question is phrased correctly -I'm a writer.
PerformancePoint Server 2007 Planning Data Migration Tool
There are two primary steps to a Migration, with many tedious “sub-steps”.
The First step in the process is to Migrate the Application, this part of the process migrates:
- Security roles
- Structural metadata: all model site, model, dimension, and member set definitions
- Data Sources and Data Destinations
- Calculation rules and any associated job templates
- Form templates
- Calendar
The Second, (more tedious), step is to Migrate the Data, which accomplishes migrating the following:
- Dimension and hierarchy data
- Fact data and annotations
- Associations
The “PerformancePoint Server 2007 Planning Data Migration Tool” automates the Second set of steps, the “Data Migration” portion. The full manual process, (which requires the user to have intricate knowledge of the application), is documented in the TechNet Documentation located here:
- Preparing the Staging DB on the Source Server: http://technet.microsoft.com/en-us/library/bb838747.aspx
- Loading the Migrated Application on the Target Server: http://technet.microsoft.com/en-us/library/bb838692.aspx
Norm's PerformancePoint Server Blog : New Planning Data Migration Tool !
White paper: Performance tuning for PerformancePoint Planning Server
This white paper is for analysts and IT professionals who want guidance about PerformancePoint Planning Server scalability capabilities and the number of users that can be served. Considerations for hardware planning and software tuning are also covered.
Download this white paper as a Microsoft Office Word document (.doc).
Performance tuning for PerformancePoint Planning Server (http://go.microsoft.com/fwlink/?LinkID=100209&clcid=0x409)
White paper: Performance tuning for PerformancePoint Planning Server
Downloadable implementation plans
This guide contains detailed information about how to plan a deployment of Microsoft Office PerformancePoint Server 2007.
The PerformancePoint Server Implementation Guide consists of 12 Microsoft Office documents (templates, projects, and worksheets) that can help you plan your implementation of PerformancePoint Server. Start with the Project Plan Framework presentation for a detailed introduction to the tasks involved in implementing PerformancePoint and for guidance on how to most effectively use the templates. Included in the guide are templates for configuration planning, training, requirements gathering, and a plan for a personalized operations and maintenance plan.
Here is a useful utility to pull the tough-to-get-at PerformancePoint M&A repository data out.
Reuse & Recycle.
The PPS Monitor & Analyze UDF Project (MAUDF)
I've often wanted to be able access metadata within a monitoring server with greater ease. Querying the PPSMonitoring database is difficult because the more interesting pieces of information pertaining to a particular element are locked away within the SerializedXML column of the FCObjects table. Sure we can access the data in the SerializedXML column with XPath / XQuery but I really wanted to get stuck into some CLR stuff and figured UDFs would provide the greatest reuse, flexibility and query-ability.
I've created a series of CLR table-valued UDFs and created a home for it on Codeplex, a project that I have named MAUDF (taking a bit of inspiration from the ASSP project). Using the CLR the MAUDFs connect to the PPS Monitoring web service and extract pertinent metadata and return the results in a tabular format.
Nick Barclay: BI-Lingual: The PPS Monitor & Analyze UDF Project (MAUDF)
Radowslaw posted some good links to help with upgrading to SP1 for PerformancePoint – with one from a co-worker of mine.
After launching SP1 for PerformancePoint Server 2007, on many internet forums about PPS, I’ve found many posts describing errors while installing SP1.
For example, one of them described problems with planning applications, which disappeared in Business Modeler after upgrading Planning Server with SP1.
Installing SP1, specially for Planning Server, it is not only running SP1 installation files, but many other important steps.
So, if you omit some of them, you can receive different errors like this above.
If you are going to upgrade your PerformancePoint Server, I strongly recommend to review the following articles, before you start this procedure:
Detailed instructions on Microsoft Technet:
Updating PerformancePoint 2007 Planning Server with Service Pack 1
Updating PerformancePoint 2007 Monitoring Server with Service Pack 1
And if you need another step by step instruction with many screenshots included, read the following article:
http://performancepoint07.blogspot.com/2008/06/installing-performancepoint-planning.html
I've written a paper on Totaling in ProClarity. It's certainly not comprehensive but I do hope it answers some common questions regarding totaling. Please contact us with any feedback or questions.
Exposing ProClarity Toolbar with PPS Monitoring ProClarity Report Type
Cute & simple, How?
Excellent resource for Planning Module training notes.
Enabling NativeSQL/NativeMDX Rules
In order to enable Native SQL and Native MDX rules you must perform the following two steps:
- Enable Native SQL/MDX Rules in the Administration Console
- Have a DBA (Database Administrator) activate the rule in the back end
The above steps cannot be performed by a user who only has Business Modeler rights. In order to perform these steps the user will require Global Administrator (GA) rights.
PerformancePoint Server 2007: Enabling NativeSQL/NativeMDX Rules
.gif)
