Advanced Cube Rules

1. Advanced Cube Rules By Greg Wilson & Manny Perez Product Management & Engineering

2. Topics I. -What’s New with Rules in 8.0 • Subsets in Rules • Continue Function • Rules Tracer II. - Dynamic Subset can benefit from Rules III. -Dealing with Time issues with Rules IV. -Conditional Feeders V. -Q&A

3. I. Subsets for Rules TM1 8.0 now permits writing a subset explicitly as a list of elements in place of an element name. For example, we can apply the same rule to elem1, elem2, elem3 by writing: […, {‘elem1’, ’elem2’, ’elem3’}, …] = …; Which is equivalent to writing: […, ‘elem1’, …] = …; […, ‘elem2’, …] = …; […, ‘elem3’, …] = …;

4. Subsets Rules Example

5. Continue Function Normally only the first rule calculation statement that applies to a cell is executed. The continue function now allows skipping statements so that the next rule statement that applies to the cell, if any, will be executed. For example: [‘x’] = if(!dim2 @= ‘a’,5,continue); [‘x’] = 4; Will assign a value of 5 to all cells identified by ‘x’ and by ‘a’, and 4 to all other cells identified by ‘x’.

6. Continue Example

7. Rules Tracer • Rules Tracer • Traces underlying Rules Calculation • Evaluates Feeding patterns.

8. II. Dynamic Subsets • Rules can drive attributes • Dynamic subsets can be derived from such. • Dynamic Subsets cannot be used in rules. But Dynamic subsets can derive advantage from rules.

9. Dynamic Months Example

10. Rule Toolbox- NOW, MONTH & ATTRN • Today- returns the current date in yy-mm-dd format. • Syntax: TODAY • Month- MONTH returns a numeric value for the month in a given date string. • Syntax: MONTH(date) requires string in YY:MM:DD format. • ATTRN-returns a numeric attribute for a specified element of a dimension. • Syntax: ATTRN(dimension, element, attribute)

11. Month Attribute Cube Rules

12. III. Tracking Inventory over Time • Tracking Inventory over time via rules • Dealing with Month and Year Dimension • Handling first time period • Addressing recursive issues.

13. Inventory Tracking Inventory Tracking over Time Need To Calculate Closing Number

14. Calculating Closing ['closing']=['start']+['in']-['out'];

15. Last Month Closing is this Months Start • We need find a way to base the current months start on last month close number.

16. Rule Toolbox- Dimnm, Dimix • DIMNM- returns the element of a dimension that corresponds to the Index argument. • Syntax: DIMNM(dimension, index) • Example: DIMNM(‘month’,3) returns ‘mar’ • DIMIX- returns the index number of an element within a dimension. • Syntax: DIMIX(dimension, element) • Example: DIMIX(‘month’,’mar’) returns 3 • Together DIMIX and DIMNM can be use to return previous element • Example: DIMNM(‘month', DIMIX(‘month', !month)-1) returns ‘feb’ (when pointing at ‘mar’)

17. Start equals last closing rule ['start']=DB('Inventory','closing',!Year, DIMNM('month', DIMIX('Month', !Month)-1)); ['closing']=['start']+['in']-['out'];

18. January Start Pulls from Previous Year December

19. Jan pulls from Dec Rule • Create Rule that pulls from previous December Closing. ['start','Jan']=DB('Inventory','closing', DIMNM('year', DIMIX(‘year', !year)-1),'Dec'); ['start']=DB('Inventory','closing',!Year, DIMNM('month', DIMIX('Month', !Month)-1)); ['closing']=['start']+['in']-['out'];

20. Need To Handle First January • The example has no year 1999, so we need to address starting point. • Use of STET function will void rule for starting point. ['start','2000','Jan']=stet; ['start','Jan']=DB('Inventory','closing', DIMNM('year', DIMIX(‘year', !year)-1),'Dec'); ['start']=DB('Inventory','closing',!Year,DIMNM('month', DIMIX('Month', !Month)-1)); ['closing']=['start']+['in']-['out'];

21. Test and Display Rule

22. Recursive Calculations Issue • Such Rules can have an issue with Recursive Calculations exceeding TM1 capacity to track. • Around 900 recursive calculations can currently be tracked in 8.0 • Rules Tracer can demonstrate the issue.

23. Modify Rules to create “Rest Stops” • Replace ['start','Jan']=DB('Inventory','closing', DIMNM('year', DIMIX('Year', !Year)-1),'Dec'); • With ['start','Jan']= DB('Inventory','start',DIMNM('year', DIMIX('Year', !Year)-1),'Jan') +DB('Inventory','in',DIMNM('year', DIMIX('Year', !Year)-1),'Year') -DB('Inventory','out',DIMNM('year', DIMIX('Year', !Year)-1),'Year'); • Allows rules to skip over month detail.

24. New Form of Inventory Rules • Via the Rules Tracer we can see this eliminates the Recursion issue.

25. IV. Conditional Feeding • There can be cases where one does not want to feed when the calculation would be zero. Take the following rules. SkipCheck; [‘Commission] = [‘Sale’] * [‘Commission Rate’]; Feeders; [‘Commission Rate’] => db(‘CommissionCube‘,’Commission’,!product,!month) • Lets say that for some product sales there is no commission schedule, so the Commission and Commission Rate would be zero. • We can take advantage of this fact when setting up the Feeders for this rule. Why bother to feed when the commission rate is zero?

26. Conditional Feeder Rule Need to replace Feeder line with conditional Feeder. SkipCheck; [‘Commission] = [‘Sale’] * [‘Commission Rate’]; Feeders; [‘Commission Rate’] => db(‘CommissionCube‘,’Commission’,!product,!month) Below is a better “Conditional” Feeder with “IF” statement that returns a blank cube name when the Commission rate equal Zero. [‘Commission Rate’] => db(IF([‘Commission Rate’]=0),‘’,‘CommissionCube‘) ,!commission,!product,!month) A Blank Cube name will not Feed nor cause an Error.

27. Q&A • Any questions on use of Rules and/or Skipcheck and Feeders?