PerformancePoint Planning Server Configuration Notes for MCTS 70-556 Certification Exam

  


Topics: Models and Dimensions – Day 1

(see powerpoint reference below for notes from day 1) 


Dimensions – Day 1

Once you have your model, you have a cube within Analysis Services to analyze. Then you can slice your dimensions to get you the particular leaf level of information you need:

These correspond to your Excel PPS add-in report creation wizard options:

 

 


Rules – Day 2

Where the magic is for getting those pesky financial ratios calculated correctly (outside of the cube) is in the Business Rules section, found by clicking on a model name and clicking on the 3rd tab (see image below).

PerformancePoint Planning Server (PPS Planning) uses PEL expression syntax as the PPS native query language — On the up side, PEL can be converted to both Native SQL and Native MDX, which is a nice feature!

   

   

3 Types of Rules


1) ASSIGNMENT RULES – can copy and paste debug code that is output by PPS PEL into SQL Server Analysis Services’ (SSAS) 2005 Management Studio client tool or SSAS 2000 MDX Sample App client tool for assignment rules ONLY 

Example assignment type business rule – real-world PEL expression:

–comment: all rules are nested within scope (); scope statements that are set explicitly or variably within the embedded ‘THIS’ statement below.

SCOPE(

[Scenario].[All Members].[Forecast],

[Time].[YQM].[July FY2008]:[Time].[YQM].[December FY2008],

[Account].[BizCorpAccount].[20010], — external sales

[BusinessProcess].[Standard].[input],

[Entity].[BizCorpEntity].[All].leafmembers,

[TimeDataView].[All Members].[PERIODIC],

–[Currency].[All Members].[USD],

[Currency].[All Members].[Total All Currencies].LeafMembers,

[InterCompany].[All Members].[None],

[Geography].[BizCorpGeo].[ALL].leafmembers,

–[Geography].[BizCorpGeo].[NAM],

[Products].[BizCorpProducts].[All].LeafMembers,

[Version].[All Members].[Current]) ;


THIS = ([Account].[BizCorpAccount].[35300] * [Account].[BizCorpAccount].[35200]);

END SCOPE;

Here is the native MDX code for the above assignment rule to help bridge the gap between PEL and what you might be more used to when writing against multi-dimensional sources (assuming those interested in this section on MDX are OLAP developers and architects):

WITH CELL CALCULATION queryCalc


FOR


‘([Measures].[Value],


[Account].[BizCorpAccount].[Level 11].&[5001],


[Scenario].[All Members].[Scenario].&[9],


    {[Time].[YQM].[Month].&[200807] : [Time].[YQM].[Month].&[200812]},


    [BusinessProcess].[Standard].[Level 06].&[8],


    Descendants([Entity].[BizCorpEntity].[(All)].&[0], 1073741823, LEAVES),


    [TimeDataView].[All Members].[TimeDataView].&[1],


    Descendants([Currency].[All Members].[(All)].&[0], 1073741823, LEAVES),


    Descendants([Geography].[BizCorpGeo].[(All)].&[0], 1073741823, LEAVES),


    [InterCompany].[All Members].[InterCompany].&[-1],


    Descendants([Products].[BizCorpProducts].[(All)].&[0], 1073741823, LEAVES),


    [Version].[All Members].[Version].&[1])’


    AS


    (([Account].[BizCorpAccount].[Level 03].&[5090],[Measures].[Value]) * ([Account].[BizCorpAccount].[Level 03].&[5089],[Measures].[Value]))


    SELECT NON EMPTY


    ([Measures].[Value],


    [Account].[BizCorpAccount].[Level 11].&[5001],


    NonEmpty(({[Scenario].[All Members].[Scenario].&[9]},


    {{[Time].[YQM].[Month].&[200807] : [Time].[YQM].[Month].&[200812]}},


    {[BusinessProcess].[Standard].[Level 06].&[8]},


    {Descendants([Entity].[BizCorpEntity].[(All)].&[0], 1073741823, LEAVES)},


    {[TimeDataView].[All Members].[TimeDataView].&[1]},


    {Descendants([Currency].[All Members].[(All)].&[0], 1073741823, LEAVES)},


    {Descendants([Geography].[BizCorpGeo].[(All)].&[0], 1073741823, LEAVES)},

 
  {[InterCompany].[All Members].[InterCompany].&[-1]},


    {Descendants([Products].[BizCorpProducts].[(All)].&[0], 1073741823, LEAVES)},

 
    {[Version].[All Members].[Version].&[1]})))


    properties [Scenario].[All Members].Key ,


    [Time].[YQM].Key ,


    [Account].[BizCorpAccount].Key ,


    [BusinessProcess].[Standard].Key ,


    [Entity].[BizCorpEntity].Key ,


    [TimeDataView].[All Members].Key ,


    [Currency].[All Members].Key ,


    [Geography].[BizCorpGeo].Key ,

 
  [InterCompany].[All Members].Key ,

  
[Products].[BizCorpProducts].Key ,


 [Version].[All Members].Key

  
ON COLUMNS

  
FROM [Forecast]


2) Definition RULES – same as assignment except calculated at run time and is not stored within the cube.

Sample expression for a definition rule for calculating variance to budget:

scope (

[TimeDataView].[All Members].[PERIODIC],

[BusinessProcess].[Standard].[INPUT],

[Account].[BizCorpAccount].[20320].leafmembers,

[Scenario].[All Members].[VarBudget_pct]) ;

This=(([Scenario].[All Members].[Budget]-[Scenario].[All Members].[Actuals])

/

[Scenario].[All Members].[Budget]

);

end scope;

3) INTERCOMPANY RECONCILE RULE – when one department is the receivable and the other is a payable – this rule balances your balance sheets

   



Report Properties – Day 3

Properties within Reports:

  • Under options:
    • Capture changes before workflow action(writes to SQL before workflow action – false by default, and best practice due to amount of data sent).
  • Capture design time formulas – allows for writable cells in Excel reports – default is true
  • Inherit design time formulas – on the excel side – true by default – can slow things down…Put formula at the All members so that the leafs inherit the formulas
  • Options – should use NO spread for most instances

          

Security within PPS – Day 3

Windows AD or LDAP only

If you have write access but NOT read, you have nothing – READ MUST BE IN THERE 

 

Users can be assigned to many roles

If READ ONLY, at the ALL MEMBERS

IF WRITE, at the leaf level

Use same service account as SSIS  (or other ETL tools) instead of PPS service accounts – ensure your login not associated as an owner;    

Should be the same account as the SSIS package "Data import" service account – it will pass through the credential details of the account; not the same account as the ADMIN account or anything requiring ADMIN rites on SSAS…

   

    



Cycles, Jobs and Assignments (work flow) all found under Process Management Link


   

Cycles 

A cycle is a time period is for 1 scenario (Actual, Budget, Forecast) and 1 time range

    Check Define a recurrence for forecast if it happens every year

   

  • Dates start and end are for non recurrence entered next screen – first and last days to input in the forecast before close out
    • For recurring, select App Calendar, the period of recurrence (Month is typical)
    • For each repetition of the forecast, enter the time here for when assignments can be made and forecast can be edited. This is th e ‘open’ books period.

   

Summary slide for assigning cycles

   

        

Remember: Forecast cycles – outside container of time for all activities

Only 1 Cycle is 1 scenario x 1 model x 1 range of time

           

 


Workflow / Assignments: see powerpoint files (please email me if you want these)

   

Assigned to user in Excel – submit and submit draft actually hit db; otherwise, local saved only

   

Once submitted, it is closed!
   

Status of submission:

Pending status first – waiting in queue to be picked up by PPS service

Wait Process – services picked it up
Partial / Submitted – Data is in the Fact tables 

 


Associations

Between models, you can associate dimensions – especially important between forecast and corporate models when different time scenarios are used, or different accounts from the general ledger are part of the cube requirements, something harder than usual for an OLAP developer to get right. (the 1st test pass at least)


Appendix

Please email me for the actual files from the list of options below…lauragibbons@scorecardstreet.com.


 

   

Remember…

  1. 2 keys with your dimensions1) to check-in your model before you proceed to any of the offered ‘Available Actions’ and 2) you deploy and redeploy model after changes are made to the structure where you want to refresh your cube (fact) data;

  2. 2 keys with your business ruleswhen making changes or adding new assignments &/or associations, PPS Planning requires you 1) refresh and reprocess model data and 2) redploy rules

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s