Capitolul 3.2 Proiectarea unei baze de date Essbase Construirea Dimensiunilor

1. Capitolul 3.2 Proiectarea unei baze de date Essbase – Construirea Dimensiunilor

2. Objectives Describe data descriptor dimensions Design time dimensions Design scenario dimensions Describe outline calculations Design accounts dimensions Create member aliases Describe dimension types Create period-to-date totals Describe Dynamic Calc members Enhance accounts dimensions Business View Dimensions

3. Data Descriptor Dimensions Overview In general, data descriptor dimensions Define data in its essentials Are manually developed and maintained Incorporate variances (both value and percentage differences) Are calculation-intensive Are densely populated with data Typical data descriptor dimensions include time, accounts, and scenario dimensions.

4. Designing Time Dimensions Decisions to make: What is the lowest-level granularity of data Depends on the type of analysis required Can be very summarized (quarters ) or very detailed (15-minute intervals) How many years to track and how to separate them Generic time designs Fiscal year crossover designs

5. Generic Time Designs The generic time design requires at least two dimensions: A time dimension with generic time periods Discrete years represented in a scenario dimension or in a separate years dimension

6. Fiscal Year Crossover Designs A fiscal year crossover design requires one of the following: A continuous, single time dimension with year-specific time periods A continuous, single time dimension with duplicate names Only use a fiscal year crossover time design if you need to: Forecast prices or costs for a rolling forecast (where the forecast time horizon crosses over the fiscal year end) Calculate rolling lead or lag calculations that require a single time continuum. Calculate cumulative values over time Only use a fiscal year crossover time design if you need to: Forecast prices or costs for a rolling forecast (where the forecast time horizon crosses over the fiscal year end) Calculate rolling lead or lag calculations that require a single time continuum. Calculate cumulative values over time

7. Designing Scenario Dimensions Scenarios are the primary tool for variance analysis. They typically take one of two forms: Scenarios that track data sets Scenarios that track processes

8. Tracking Data Sets Scenarios that track data sets Are typical in financial applications Define the foundation of the planning and control process

9. Tracking Processes Scenarios that track processes Are typical in forecasting applications Define a single data set Track adjustments by distinct functional groups

10. Outline Calculations The database outline facilitates calculations two different ways: Hierarchy structure Consolidation operators Shared members Member formulas

11. Consolidation operators define how child members consolidate to parents. Valid consolidation operators: Addition (+) Subtraction (-) Multiplication (*) Division (/) Percent (%) Exclude from consolidation (~) Never consolidate (^) Consolidation Operators In addition to the structural relationships between members within dimensions, the database outline enables you to define consolidations between the members by setting their consolidation property. You set the consolidation property by assigning on of the consolidation operators: Addition (+), Subtraction (-), Multiplication (*), Division (/), Percent (%), or Ignore (~). By default, when you add a new member to the outline, the member consolidation property is set to the Addition (+) consolidation operator. The consolidation property defines how the member rolls up to its parent. For example, you may want to subtract a member from its sibling, such as subtracting COGS from Sales, to define the proper value for Margin. The Ignore (~) operator identifies the member as exempt from consolidation. For example, Actual, Budget, Variance, and Variance % are tagged as Ignore (~) so that they do not roll up into Scenario. Once your outline is created, you need to look at how the dimensions and members are ordered. Your data consolidates based on the order of dimensions and members in your outline. In addition to the structural relationships between members within dimensions, the database outline enables you to define consolidations between the members by setting their consolidation property. You set the consolidation property by assigning on of the consolidation operators: Addition (+), Subtraction (-), Multiplication (*), Division (/), Percent (%), or Ignore (~). By default, when you add a new member to the outline, the member consolidation property is set to the Addition (+) consolidation operator. The consolidation property defines how the member rolls up to its parent. For example, you may want to subtract a member from its sibling, such as subtracting COGS from Sales, to define the proper value for Margin. The Ignore (~) operator identifies the member as exempt from consolidation. For example, Actual, Budget, Variance, and Variance % are tagged as Ignore (~) so that they do not roll up into Scenario. Once your outline is created, you need to look at how the dimensions and members are ordered. Your data consolidates based on the order of dimensions and members in your outline.

12. Shared Members Do not store data Create a pointer to a stored member Are always level 0 members Are positioned after (below) the stored member in the outline

13. Member Formulas Member formulas define calculations in the outline. Calculation script functions return sets of members or data values. Whenever possible, use consolidation operators in the outline to define natural formulas. This is generally more intuitive to end users as they can drill down to see how a member was derived. However, you can define more complex formulas for members. Analytic Services provides a comprehensive set of operators and functions that you can use to construct formula calculations on a database. For instance, you can perform common arithmetic operations or control the flow of formula executions based on the results of conditional tests. Calculation functions include boolean to provide a conditional test, mathematical, statistical, and financial to perform specialized calculations, and so on. In the example shown in the slide, Analytic Services performs the following calculations for each month: The IF statement and @ISMBR function check that the current member on the Year dimension is Jan. This step is necessary because the Opening Inventory value for Jan is an input value. If the current month is not Jan, the @PRIOR function obtains the value for the Ending Inventory for the previous month. This value is then allocated to the Opening Inventory of the current month.Whenever possible, use consolidation operators in the outline to define natural formulas. This is generally more intuitive to end users as they can drill down to see how a member was derived. However, you can define more complex formulas for members. Analytic Services provides a comprehensive set of operators and functions that you can use to construct formula calculations on a database. For instance, you can perform common arithmetic operations or control the flow of formula executions based on the results of conditional tests. Calculation functions include boolean to provide a conditional test, mathematical, statistical, and financial to perform specialized calculations, and so on. In the example shown in the slide, Analytic Services performs the following calculations for each month: The IF statement and @ISMBR function check that the current member on the Year dimension is Jan. This step is necessary because the Opening Inventory value for Jan is an input value. If the current month is not Jan, the @PRIOR function obtains the value for the Ending Inventory for the previous month. This value is then allocated to the Opening Inventory of the current month.

14. Designing Accounts Dimensions Design choices are based on input data and sample reports. Profit and loss accounts Balance sheet accounts Units, rates, prices, and other assumptions Analysis metrics

15. Consolidation Order Consolidation order is defined by the order of members in the outline. Essbase calculates data in top-down order. It is important to understand how Analytic Services calculates members with different consolidation operators. When you are using just addition and subtraction operators the order of members in the outline is irrelevant. However, when you use any other operator, you need to consider the member order and its impact on the consolidation. When siblings have different operators, Analytic Services calculates data in top-down order. The following example illustrates a top-down calculation: Sample Roll-up Parent1Member1 (+) 10Member2 (+) 20Member3 (-) 25Member4 (*) 40Member5 (%) 50Member6 (/) 60Member7 (~) 70 Analytic Services calculates Member1 through Member4 as follows: (((Member1 + Member2) + (-1)Member3) * Member4) = X(((10 + 20) + (-25)) * 40) = 200 If the result from Members 1-4 is X, then Member5 consolidates as follows: (X/Member5) * 100 = Y(200/50) * 100 = 400 If the result of Member5 is Y, then Member6 consolidates as follows: Y/Member6 = Z400/60 = 66.67 Analytic Services ignores Member7 in the consolidation. It is important to understand how Analytic Services calculates members with different consolidation operators. When you are using just addition and subtraction operators the order of members in the outline is irrelevant. However, when you use any other operator, you need to consider the member order and its impact on the consolidation. When siblings have different operators, Analytic Services calculates data in top-down order. The following example illustrates a top-down calculation: Sample Roll-up Parent1Member1 (+) 10Member2 (+) 20Member3 (-) 25Member4 (*) 40Member5 (%) 50Member6 (/) 60Member7 (~) 70 Analytic Services calculates Member1 through Member4 as follows: (((Member1 + Member2) + (-1)Member3) * Member4) = X(((10 + 20) + (-25)) * 40) = 200 If the result from Members 1-4 is X, then Member5 consolidates as follows: (X/Member5) * 100 = Y(200/50) * 100 = 400 If the result of Member5 is Y, then Member6 consolidates as follows: Y/Member6 = Z400/60 = 66.67 Analytic Services ignores Member7 in the consolidation.

16. Design Considerations

17. Creating Accounts Hierarchies

18. Creating Member Aliases Aliases are Used as reporting names Used for data source name mapping Updated in Member Properties Stored in alias tables Alias tables are Created in Outline Properties Limited to 10 per database Updated separately Displayed one at a time

19. Dimension Types

20. Creating Period-to-Date Totals Calculated members Shared member hierarchy Member formula Dynamic Time Series

21. Creating Period-to-Date Calculated Members Provides hierarchy path expansion in reports Creates more stored members Enables loading to period-to-date totals

22. Implementing Dynamic Time Series

23. Dynamic Calc Members Advantages: Reduce batch database calculation time Reduce disk storage usage Reduce database restructure time Reduce time required to perform a backup Understanding Dynamic Calculation Members When you design the overall database calculation, it may be more efficient to calculate some member combinations when you retrieve their data, instead of pre-calculating the member combinations during a batch database calculation. In Analytic Services you specify dynamic calculations on a per-member basis. You can define a member in the database outline as one of two types of a dynamically calculated member: ? Dynamic Calc ? Dynamic Calc and Store Dynamic Calc When a member is set to Dynamic Calc, Analytic Services does not calculate the value for that member until a user requests it. After the user views it, Analytic Services does not store the value for that member. Dynamic Calc and Store Analytic Services calculates the data value for a member that is tagged as Dynamic Calc and Store when you retrieve the data, in the same way as for a Dynamic Calc member. For a Dynamic Calc and Store member, however, Analytic Services stores the data value that is calculated dynamically. Subsequent retrievals of that data value do not require recalculation, unless Analytic Services detects that the value needs recalculating. Understanding Dynamic Calculation Members When you design the overall database calculation, it may be more efficient to calculate some member combinations when you retrieve their data, instead of pre-calculating the member combinations during a batch database calculation. In Analytic Services you specify dynamic calculations on a per-member basis. You can define a member in the database outline as one of two types of a dynamically calculated member: ? Dynamic Calc ? Dynamic Calc and Store Dynamic Calc When a member is set to Dynamic Calc, Analytic Services does not calculate the value for that member until a user requests it. After the user views it, Analytic Services does not store the value for that member. Dynamic Calc and Store Analytic Services calculates the data value for a member that is tagged as Dynamic Calc and Store when you retrieve the data, in the same way as for a Dynamic Calc member. For a Dynamic Calc and Store member, however, Analytic Services stores the data value that is calculated dynamically. Subsequent retrievals of that data value do not require recalculation, unless Analytic Services detects that the value needs recalculating.

24. Enhancing Accounts Dimensions Time balance accounting Expense reporting

25. Time Balance Reporting Time balance reporting defines consolidation of members in the accounts dimension across the time dimension. Skip options determine how Essbase treats zero and missing values. Similarly to the Variance reporting expense property, time balance properties apply only to members of the Accounts dimension. Time balance lets you control the consolidation of balance sheet items for different time periods. Essbase enables you to set one of the following values for the Time balance property: None. When a member in the Accounts dimension represents a value that is irrespective of a time period, the corresponding parent in the Time dimension is calculated based on the consolidation and formulas of its children. For example, the Time balance property for the Additions member is set to the default value, None. This tells Essbase to calculate the Qtr1 member in the Time dimension as the sum of its children (Jan, Feb, and Mar). TB First. When a member in the Accounts dimension represents the value at the beginning of the time period and you want to carry this value to the parent in the Time dimension, set the Time balance property of that member to TB First. For example, the Opening Inventory member represents the inventory at the beginning of the time period. If the time period is Qtr1, then Opening Inventory represents the inventory at the beginning of Jan; that is, the Opening Inventory for Qtr1 is the same as the Opening Inventory for Jan. Similarly, Year Opening inventory is equal to Qtr1 Opening inventory. TB Last. When a member in the Accounts dimension represents the value at the end of a time period and you want to carry this value to the parent in the Time dimension, set the Time balance property of that member to TB Last. For example, the Ending inventory represents the inventory at the end of the time period. If the time period is Qtr1, then Ending Inventory represents the inventory at the end of Mar; that is the Ending Inventory for Qtr1 is the same as the Ending Inventory for Mar. Year Total Ending inventory is equal to Qtr 4 Ending inventory. TB Average. When you want that the parent value in the Time dimension to represent the average value of a time period for a member in the Accounts dimension, set the Time balance property of that member to TB Average. Setting Skip Properties If you set the time balance as TB First, TB Last, or TB Average, you must set the skip property to tell Essbase what to do when it encounters missing values or values of 0. The following table describes how each setting determines what Analytic Services does when it encounters a missing or zero value. None: Zeros and missing values are considered when parent values are calculated. This is the default setting. Missing: #MISSING values are excluded when parent values are calculated. Zeros: Zero values are excluded when parent values are calculated. Missing and Zeros: #MISSING values and zero values are excluded when parent values are calculated. Similarly to the Variance reporting expense property, time balance properties apply only to members of the Accounts dimension. Time balance lets you control the consolidation of balance sheet items for different time periods. Essbase enables you to set one of the following values for the Time balance property: None. When a member in the Accounts dimension represents a value that is irrespective of a time period, the corresponding parent in the Time dimension is calculated based on the consolidation and formulas of its children. For example, the Time balance property for the Additions member is set to the default value, None. This tells Essbase to calculate the Qtr1 member in the Time dimension as the sum of its children (Jan, Feb, and Mar). TB First. When a member in the Accounts dimension represents the value at the beginning of the time period and you want to carry this value to the parent in the Time dimension, set the Time balance property of that member to TB First. For example, the Opening Inventory member represents the inventory at the beginning of the time period. If the time period is Qtr1, then Opening Inventory represents the inventory at the beginning of Jan; that is, the Opening Inventory for Qtr1 is the same as the Opening Inventory for Jan. Similarly, Year Opening inventory is equal to Qtr1 Opening inventory. TB Last. When a member in the Accounts dimension represents the value at the end of a time period and you want to carry this value to the parent in the Time dimension, set the Time balance property of that member to TB Last. For example, the Ending inventory represents the inventory at the end of the time period. If the time period is Qtr1, then Ending Inventory represents the inventory at the end of Mar; that is the Ending Inventory for Qtr1 is the same as the Ending Inventory for Mar. Year Total Ending inventory is equal to Qtr 4 Ending inventory. TB Average. When you want that the parent value in the Time dimension to represent the average value of a time period for a member in the Accounts dimension, set the Time balance property of that member to TB Average. Setting Skip Properties If you set the time balance as TB First, TB Last, or TB Average, you must set the skip property to tell Essbase what to do when it encounters missing values or values of 0. The following table describes how each setting determines what Analytic Services does when it encounters a missing or zero value. None: Zeros and missing values are considered when parent values are calculated. This is the default setting. Missing: #MISSING values are excluded when parent values are calculated. Zeros: Zero values are excluded when parent values are calculated. Missing and Zeros: #MISSING values and zero values are excluded when parent values are calculated.

26. Expense Reporting In applications where revenues and expenses are not loaded with credit and debit balances: Members that represent expense must have the Expense Reporting tag. Essbase provides two variance reporting functions: @VAR(mbrName1, mbrName2) @VARPER(mbrName1, mbrName2) One of the typical analytic requirements is the ability to perform variance reporting on actual versus budget data. When you are budgeting expenses for a time period, you want the actual expenses to be lower then the budget. When actual expenses are greater than budget, the variance is negative. On the other hand, when you are budgeting non-expense items, such as sales, you want the actual sales to come in a higher than the budget. When actual sales are less the budget, the variance is negative. Essbase variance reporting calculation requires that any outline member that represents an expense to the company must have the Expense Reporting tag. For example, Inventory members, Total Expense members, and the COGS member each receive the Expense Reporting tag for variance reporting. You assign the Expensive Reporting tag to a member by setting its Variance reporting expense property to true. The default value of the Variance reporting expense property is false. Note: Expense reporting can only be tagged on members within the Accounts dimension. To perform variance reporting calculation, Essbase provides two functions: @VAR and @VARPER. In the example shown in the slide, Sales is a non-expense member, and the variance reports a negative number. However, COGS is tagged as an expense member, and the variance reports a positive number. Because it is an expense reporting item, the sign is flipped on the variance calculation. The actual formulas used in the relevant outline are as follows: @VAR(Actual, Budget); @VARPER(Actual, Budget); These formulas trigger Essbase to check if a member has the Expense Reporting tag and does a sign flip if it finds the member does. When you use the subtract operator in the member formula, Essbase does not check the Expense Reporting tag for that member.One of the typical analytic requirements is the ability to perform variance reporting on actual versus budget data. When you are budgeting expenses for a time period, you want the actual expenses to be lower then the budget. When actual expenses are greater than budget, the variance is negative. On the other hand, when you are budgeting non-expense items, such as sales, you want the actual sales to come in a higher than the budget. When actual sales are less the budget, the variance is negative. Essbase variance reporting calculation requires that any outline member that represents an expense to the company must have the Expense Reporting tag. For example, Inventory members, Total Expense members, and the COGS member each receive the Expense Reporting tag for variance reporting. You assign the Expensive Reporting tag to a member by setting its Variance reporting expense property to true. The default value of the Variance reporting expense property is false. Note: Expense reporting can only be tagged on members within the Accounts dimension. To perform variance reporting calculation, Essbase provides two functions: @VAR and @VARPER. In the example shown in the slide, Sales is a non-expense member, and the variance reports a negative number. However, COGS is tagged as an expense member, and the variance reports a positive number. Because it is an expense reporting item, the sign is flipped on the variance calculation. The actual formulas used in the relevant outline are as follows: @VAR(Actual, Budget); @VARPER(Actual, Budget); These formulas trigger Essbase to check if a member has the Expense Reporting tag and does a sign flip if it finds the member does. When you use the subtract operator in the member formula, Essbase does not check the Expense Reporting tag for that member.

27. Optimizing Data Storage Shared data: Label only members Implied shares

28. Label Only Members Do not store data Group members to ease navigation and reporting Create a pointer to the first child

29. Implied Shares An implied share is an automatic internal optimization created by Essbase in the following circumstances: A parent has only one child. A parent has only one child that consolidates to the parent. To prevent implied sharing, set the storage property of the parent to Never Share.

30. Business View Dimensions Overview Business view dimensions Define a specific cut of the data Are developed and maintained through automatic processes Incorporate alternate rollups using shared members Are not calculation-intensive Are sparsely populated with data The choice of business view dimensions is substantially driven by the company’s industry and business practices.

31. Combining Business Views Consider combining several business views in one hierarchy Consider using attribute dimensions

32. Planning Dimensions with Label Outlines

33. Designing Primary Hierarchies

34. Designing Secondary Hierarchies