Using Planning User variables in Data Forms POVs

1. Using Planning User variables in Data Forms POVs A better solution to an old problem

2. Small Team of senior DW/BI consultants with typically 15 + years in the industry • Oracle/Hyperion BI /Cognos / MS-BI • Data Warehousing Architects and Modellers • Platform agnostic • Agile Project Management • Feel free to ping us for advice at ndc@new-dimensions.biz • Come stump us!! New Dimensions Consulting

3. Planning is done in Data Forms which mimic spreadsheet layouts. • Real estate in the data form is limited. Any dimension modeled as row can become unwieldy to use • Example: Planning costs and income for apartments in an apartment complex. • A typical layout might be the apartment units as rows and the driver accounts as columns • Admins and those with a larger amount of properties could have 1000s of rows to budget/plan to • Depending on the hierarchy of dimension in question, there is no easy way to filter rows to a manageable size The Problem

4. Use Planning Security to limit rows • Using security settings and suppressing N/A and #missing rows, row count is reduced to a manageable size per user • Does not apply to admin or regional/manager level users • Desired security grouping is often not conducive for proper hierarchical suppression. Typical Solutions, part 1

5. Split a dimension • Split the row dimension into two separate hierarchies (e.g., apartment units & apartment complex) • Have one new dimension be flat (e.g., Apartment Complex) • Data is loaded at intersection of units belonging to a complex (i.e., using P-C to load to two dimensions). • Flat dimension becomes POV filter Typical Solutions, Part2

6. Creates sparse cube because “correct” data only at specific apartment/complex intersections • Allows for accidental entry of nonsense data (i.e., at apartments not physically in complex data was entered for). • Since suppress is used, apartments w/out data are not visible in form • Bad design because it splits two dimensions Typical Solutions, Part2, cont.

7. New in System 9 • New to associate with Forms in System 11 • Not the same as Global Variables (BR Prompts), but used in conjunction with. • Seemed redundant or useless like “Dynamic Calc & Store” • Set in user preferences for each user. Planning User Variables

8. Create a Planning User Variable (E.g., MyBuilding, MyProject) • End-User will need to assign a value to variable in Planning Preferences (tab: “User Variable Options”) at first-time use. • Associated Planning User Variable with the Form of interest in the POV tab (enable Dynamic User Variables). • To use seamlessly in Business Rules, create Global Variable (BR Prompt) PROJUser in EAS and enter default value &MyProject(reference Planning Variable in Global Variable). • Rows return are always 0-level of select value. • May select any node in hierarchy. • User preference is stored to reflect last selected value; this is user specific. • Business Rule can be run on save and will pass value in so user is not prompted again. How To

9. Form loads faster because only children of selected member are loaded. Suppression of #missing loads form with all rows and then filters. • More correctly models reality and therefore prevents possibility of nonsense data and/or sparse cubes. • Allows viewing of meta data (e.g., apartments) without data loaded to them • Can be used seamlessly with Business Rules. Additional Benefits

10. Now I show you how it looks like … Demo

11. Questions?