HyperionPlanning

Chapter- 3

Creating Dimension Overview

Agenda

Describe plan typesIdentify required dimensionsIdentify user-defined dimensionsDescribe dense and sparse dimensionsDescribe data block creationDescribe aggregation, data storage, and calculation options

Plan TypesPlanning and BudgetingApplicationPlan1Plan2Plan3CAPEXWorkforceThese modules require additional expenseCustomized Plans

Plan Types contd..For each plan type a separate Analytic Services database is created.Three customizable plan typesPlan types are set for application views, dimensions and membersOne database is selected to store data. Data values stored in one database are referenced by another databases, by sharing data for those members.

Planning DimensionsRelational DatabaseEssbase cube

Planning Dimensions

Planning data is stored in both Analytic Services database and relational databases. Planning applications are organized by dimension. The dimensions in your application represent the categories of data in your organization. How user set up dimension properties affects the storage and calculation of information ,the efficiency of the database.

Identify the budget items like office expense,travel expense etc.

Identify Time period for items like current quarter or next quarter

The quarters and corresponding months are in Period dimension

Planning Dimensions

Six Required Dimensions Period Year Scenario Version Entity AccountTwo additional dimensions for multicurrency applications CurrencyHSP_Rates 14 User defined Dimensions For example: Employee Market Product

Required Dimensions contd.. Period and YearYou specify a time period and year for each value. Base time periods, such as months, are automatically rolled up to summary time periods, such as quarters and total year.

Required Dimensions contd..Scenario Scenario describes the type of data that a plan includes, such as budget, actual, or forecastVersionVersion allows for flexibility and iterative planning cycles For example, users application could have two versions, Working and Final, Target for each scenarioEntityThe Entity dimension represents the flow of Planning information through business unitsThese units could be geographic regions, departments, or divisionsAccountThe Account dimension budgeted itemsExamples of accounts are Rent Expense and Cash on HandAlias User must set up an Alias dimension if user wants to assign aliases to dimensions such as Account or EntitySmart ListsIf user wants to use Smart Lists in application, user must set up a Smart List dimension

Multi currency applicationCurrencyIt identifies one or more currencies used in applicationOne needs to set The reporting currency Currency displayed in reports and data formsTranslation of one currency into other currenciesWhen currency conversions occurHSP_RatesContains a member to store exchange rate values for each currency. Contains a member for input values and currency overrides.The system generates dimension HSP_RatesThis dimension is visible only in Analytic Services.

Dense DimensionsA dense dimension contains a high percentage of occupied data values in each combination of dimensions.For example: Accounts , Periods

Account and Period dimension

Sparse Dimensions Sparse dimensions contain a low percentage of occupied data values in each combination of dimensions. For example Entity, Version

Entity and Version dimension

Data blocksDimension YAnalytic Services stores data in data blocks.Data block are like grid or spreadsheet with the dimension members on the rows and columns.Data is stored in the cells formed by the intersection of the members of different dense dimensions. For a data block with two dense dimensions, Dimension X and Dimension Y with 4 and 5 members respectively, 20 cells are created for storing dataDimension X

Determining the Numberof Data Blocks in a DatabaseSparse DimensionsEntityHQScenario Budget Year FY08

Version Draft 1 FinalDense DimensionAccountExpense 1Expense 2

PeriodJanFebMar

Each block contains 6 cells= (2 Accounts * 3 Time Periods)

FinalJanFebMarExpense 1Expense 2

Sparse Dimension: Data Structure and Performance of Data Blocks If all dimensions are sparse, Analytic Services creates data blocks that consist of single data cells that contain single data values. An index entry is created for each data block.This configuration produces an index that requires a large amount of memory.The more index entries, the longer Analytic Services searches to find a specific block.Database with all sparse dimension

Data Structure and Performance of Data BlocksIf all dimensions are dense Analytic Services creates one index entry and one very large, very sparse block.Analytic Services needs to load the entire memory when it searches for a data value, which requires enormous amounts of memory.The number of cells in a data block grows exponentially as dense dimensions are added to the database.

Aggregation, Storage, and Calculation OptionsData aggregationData StorageFor simple Calculations within dimension For simple Calculations within dimension Order of calculations to achieve the end result

Selecting Aggregation Options Data Storage OptionsAggregation Options Aggregation helps in calculating data within a dimension using parent-child relation Proper usage of storage options improves performance remarkably

Calculating Data

Data is calculated in the following order:Account DimensionOther Dense DimensionsTime DimensionSparseDimensionMembers tagged as Two-Pass

Two Pass calculationTo calculate Margin % and Profit % correctly, Analytic Services must first aggregate the values for the Margin and Profit derived from its children Sales and COGSAfter these totals are calculated, a second pass is needed to calculate the Margin% and Profit%Two-Pass Calculation option for the Margin% and Profit%

Performance Efficiency Number of cells = Number of dense dimensionsNumber of blocks = Number of sparse dimensionsMore number of sparse dimensions, more number of different data blocks to populate data.Thus, efficiency would be less!

SummaryVarious plan typesRequired dimensionsUser-defined dimensionsDense and sparse dimensionsData block creationAggregation, data storage, and calculation option