Tips from Train The Trainer Sessions – Day 2

More Nuggets:

• When you need faster access to data within the model, sometimes it makes sense to build relational reports directly off the Application Database using tools such as PerformancePoint M&A or Reporting Services.
• When loading staging data, you may have to run the validation stored procedure twice, if you are importing annotations.  The parameters are mutually exclusive, so you will either validate Annotations or Members.

@AnnotationOnly parameter - F - validate measures only.  T - validate annotations only.

• When validating data loads, you need to check bizsystemflag, bizvalidationstatus, bizsystemerror columns.  Any one of these columns could have error information while the other columns could be null.

These styling tips are probably the most powerful nuggets I heard from one of the experts in the field:

• Cell Styles – you can modify the pivot styles for changing the format of currency values and the look and feel of the PerformancePoint reports and data input forms. 

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.

 

• Conditional Formatting

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.

 

• Importing/Exporting Forms can only be performed from the same Application – unless you manually edit the RDL of the form.
• Use naming convention standards for objects.  Cycles should be named based on Role/Scenario/Time Range.  Eg FY09-Budget-Sales-Input
• When installing PerformancePoint, run the installation as the service account you will be using to run the service.
• 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.
• Always use roles instead of users when assigning approvers/contributors.
• Always set assignments to Move to Submitted or Not Submitted State instead of Do Nothing.  The preference is for Not Submitted, to see which planners have not completed their assignments.   Either option will close the cycle, not allowing changes to the plan.  Otherwise the Do Nothing option will allow modification of the assignment after the cycle end date.
• Never regenerate assignments during a cycle if you can help it.  These assignments are tracked by user id which may change after a regeneration, making it hard to track history of the assignment.
• Use 1 assignment for each form rather than multiple forms in an assignment.
• Performance in the Excel add-in becomes an issue sometimes after adding > 4 matrices.
• You can only fit 6 assignments on the assignment list in Excel add-in.  You need to click ‘more assignments’ and search for the assignment to check for additional assignments.  It may make sense to display assignments in a Sharepoint page or Reporting Services report.
• Always use Submit Draft for first revisions.  Submit Final is closed for review or approval.  The assignment would have to be rejected if any changes are required.  The submit final option can be performed once the budget cycle is closed.
• Add parameters to rules to zero-out data before an allocation rule.  Otherwise allocation rules will add to existing data, potentially duplicating results.  (Allocation – Add)
• Set the available time members within a rule using the special Time drop down list.  Otherwise use the current period reference and a range of members before and after current period. (lag function)
• Create referential integrity within dimension properties by assigning references to other dimensions.  Adding new properties will check data within the other dimension.

 

• Within PEL (PerformancePoint Expression Language) both MDX and SQL comment syntax is valid.

Eg. /* */, –, //

 

• Native SQL Rules can be used to run SQL Agent jobs.  This provides a tool that could be used by planners and modelers within Excel to run ETL packages, process the cubes, and perform many other tasks without requiring direct access to SQL Server.  Even if you are not using PerformancePoint functionality, this may be useful for integration of Excel with SQL Server for other purposes.
• Create a Version dimension to track what stage in the Cycle the data is at.  Save version by User-Scenario-Model. 
• Use Time.CurrentPeriod.Parent.Parent to get to higher levels in the time hierarchy for assumption models.

Planning - TechNet Forums