Tips from Train the Trainer Sessions – Day 4

Rules

• Sometimes you need to remove carriage returns & redo return and put in extra spaces to fix validation issues. 
• For SQL rules & the time dimension, ensure you use Add Lowest instead of Add Descendants.
• Create PEL Business Rules instead of Excel formulas wherever possible.  Reusability and centralization of calculations.
• Design Time Matrix - Set filter with All checked in order to use formulas with multiple filters.
• There are no CAST or CONVERT statements in PEL.  If you want to perform math on member properties, use NativeMDX or NativeSQL or Assumption Models.
• Create a Scenario called Budget Variance, can use Budget - Actual.  Create definition rules.  Create 5 variances, % of revenue, ForEx variance, % of sales, etc.
• Definition rules are always in order of solve order.  The last one orders the calculations.  Make sure definition rule TimeDataView Calculation is at the end in order to perform YTD calcs against calculated members.
• Keep rules small.  Use only single This & Scope for simplicity.  Nest RuleSets... but don't ever go more than 1-2 down.  Performance would be degraded.
• Rule Set - 17 Rules in 3 folders - put them in 1 RuleSet, Create 1 Job that runs the 17 rules.
• Comparison to last year rule
• Useful function this = ($CurrentPeriod$.Lag(0)) - ($CurrentPeriod$.Lag(12));

Data Input

• Use a numeric signoff code column to ensure that users have reviewed the budget and are comfortable signing off.
• Include workbook checkbox - Sometimes &ref! is stored in the boxes when corrupted.  Ask users to save copy of workbook.  Performance issues with 'include workbook'.  Avoid.
• PerformancePoint includes annotations, which are different than Excel comments.  Annotations get saved in the cube.  Use View - Annotations in PerformancePoint menu option.  Orange cells have annotations.  Right-click on each orange cell to check for annotations.  Users can only see their own annotations.
• You can use data validations inside a matrix.  For signoff, you have a list for 1 - I agree as is, 2 - made changes, Excel will stop change.
• If 15 assignments are submitted to 1 contributor, separate assigments are available for rejection.  The aggregated view is also available.
• Use stored procedures to create member set out of member properties as parent/child hierarchy instead of member property.  There are limitations to member views vs. member sets. 
• Use member properties within Filters of the reports to perform cascading filters.  Eg.  Create a department member property in the employee dimension.  Assign employees to departments.  Assign filter to property.
• Ensure Reports directory is read-only for everyone but modelers.
• Create a separate section with "Suggested changes" with read/write access or use Sharepoint Library.  Create Groups for Detail & Summary reports.
• Remove blank columns and remove blank rows in the Report Properties - Matrix properties -
• Intersections - Create asymmetric reports using Row/Column interesections with multple different rows/columns.
• When copying Matrices during authoring, you will need to re-add the properties within the matrix as they get lost during a copy.

Planning - TechNet Forums