Topics: Models and Dimensions – 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…
2 keys with your dimensions – 1) 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 keys with your business rules — when making changes or adding new assignments &/or associations, PPS Planning requires you 1) refresh and reprocess model data and 2) redploy rules
Musings on ML, Deep Learning & AI 