dimension decisions: a guide to defining dimensions for your oracle epm solution
Post on 17-Aug-2015
32 Views
Preview:
TRANSCRIPT
Dimension Decisions:Dimension Decisions:A Guide to Defining Dimensions for Your A Guide to Defining Dimensions for Your
Oracle EPM SolutionOracle EPM Solution
November 16November 16thth, 2011, 2011
RJ LinehanRJ Linehanrj@innovuspartners.comrj@innovuspartners.com
Presentation AgendaPresentation Agenda
Presenter Biography Dimensional Modeling Concepts Oracle EPM Dimensional Requirements
Financial Management (HFM) Planning Essbase
Common Design Scenarios Questions
Presenter BiographyPresenter Biography
RJ Linehan, Innovus Partners Eleven Years experience consulting in the EPM/BI Space Expertise in functional and technical requirements gathering Deployed over 30 EPM/BI solutions Key Industry experience
Consumer Packaged Goods Retail Insurance and Financial Services
Primary Application Experience Management Reporting Applications Budget & Forecasting Solutions Workforce and Capital Asset Planning Models Customer/Product Profitability Models Executive Dashboards
Dimensional Modeling ConceptsDimensional Modeling Concepts
A single set of common data that satisfies the following user requirements Ability to make informed business decisions based on the right
data Expectation of accurate data provided at the correct grain Delivery of the data in a secure and timely fashion Capacity to change with business requirements
What is a dimensional model?(Borrowed from "The Data Warehouse Toolkit" by Ralph Kimball and Margy Ross)
Dimensional Modeling ConceptsDimensional Modeling Concepts
• A fact is a business measure that is typically numeric• A fact is the numerical data stored in an EPM application• A fact can be additive or non-additive• The list of dimensions that encompass an EPM application
defines the grain of the fact and the scope of measurement
What is a Fact?(Borrowed from "The Data Warehouse Toolkit" by Ralph Kimball and Margy Ross)
Dimensional Modeling ConceptsDimensional Modeling Concepts
• A dimension and its attributes describe the facts stored in an EPM application
• Dimension attributes define the 'By' in a report (Revenue by Quarter by Region)
• A dimension is a type of Metadata
What is a Dimension?(Borrowed from "The Data Warehouse Toolkit" by Ralph Kimball and Margy Ross)
Dimensional Modeling ConceptsDimensional Modeling Concepts
• Insist on complete functional/business requirements• Know the data that will encompass the subject area• Engage and learn from the business users• Provide detailed pre-design questions for the business
and IT• Request sample dimensions from legacy applications
Successfully Defining Dimensions During Design
Dimensional Modeling ConceptsDimensional Modeling Concepts
Existing User Reports Leverage existing user reports to ensure the design supports
reporting requirements MS Excel
Employ MS Excel to illustrate sample report layouts Outline Editor
Utilize an outline editor and the MS Excel Add-in / Smart View The business user Your ears
Design Tools Used to Define Dimensions
Dimensional Modeling ConceptsDimensional Modeling Concepts
DEMONSTRATION
Successfully Defining Dimensions During Design
Oracle EPM Dimensional RequirementsOracle EPM Dimensional Requirements
• Oracle EPM applications support standard and custom dimensions
• A dimension member is an attribute of the dimension that is typically unique to the application
• A hierarchy denotes a roll-up of dimension members within a dimension that is either aggregating or non-aggregating
• Fact data must be loaded to a member from each dimension in the EPM application
• Dimensions are often shared across EPM applications
Dimension Definitions for Oracle EPM Applications
Oracle EPM Dimensional RequirementsOracle EPM Dimensional Requirements
• A standard dimension is a required dimension in an EPM Application
• A standard dimension offers consistency• A standard dimension provides out-of-the-box
functionality for select features of an EPM Application
Standard Dimensions in an Oracle EPM Application
Oracle EPM Dimensional RequirementsOracle EPM Dimensional Requirements
• A custom dimension is typically not a required dimension in an EPM Application
• A custom dimension provides for customization to satisfy reporting requirements
• A custom dimension expands the perspective of data stored in an EPM Application
Custom Dimensions in an Oracle EPM Application
Oracle EPM Dimensional RequirementsOracle EPM Dimensional Requirements
• Standard Dimensions– Account - reflects the measures/statistics– Intercompany Partner (ICP) – dynamically built using the entity
dimension to capture intercompany transactions– Period - describes the time characteristics– Entity - represents the organizational structures– Scenario - reflects the management planning cycles– Value - provides for consolidation, elimination, adjustments, and
translations– View - provides calendar views– Year - describes the year characteristic
• Custom Dimensions– HFM supports an additional four custom dimensions
Oracle Hyperion Financial Management Dimensions (Rigid)
Oracle EPM Dimensional RequirementsOracle EPM Dimensional Requirements
Standard Dimensions Accounts - reflects the measures/statistics Periods - describes the time characteristics Entities - represents the organizational structures Scenarios - reflects the management planning cycles Versions - denotes copies of the management planning cycles Years - describes the year characteristic HSP_Rates - represents the exchange rates Currency - reflects the currencies
Custom Dimensions Planning can support up to fourteen custom dimensions
without multi-currency support
Oracle Hyperion Planning Dimensions
Oracle EPM Dimensional RequirementsOracle EPM Dimensional Requirements
• Standard dimensions do not apply to Essbase Applications
• Flexibility of Essbase offers extreme dimensional customization
• Offers support for Planning and Financial Management applications as well as most other reporting solutions
Oracle Essbase Dimensions
Common Design ScenariosCommon Design Scenarios
• Organize data sets that will be planned into groups of common dimensionality
• Avoid building one application with many dimensions to support all planned data
• Leverage a separate application to support consolidated reporting
• Consider loading data instead of deriving it when calc inputs are not dimensionally relevant
Dimensional Design for Budget & Forecasting Applications
Common Design ScenariosCommon Design Scenarios
Dimensional Design for Budget & Forecasting Applications
DEMONSTRATION
Common Design ScenariosCommon Design Scenarios
Dimensional Challenge When consolidation occurs in HFM, eliminations for
intercompany transactions are stored at the parent with the first common currency
Shared Dimensions Accounts Dimension Entities Dimension ICP Dimension Value Dimension
Other Dimensions Data Type Dimension Version Dimension
Dimensional Design for Integrating HFM Dimensionality within a Planning Application
Common Design ScenariosCommon Design Scenarios
Dimensional Design for Integrating HFM Dimensionality within a Planning Application
DEMONSTRATION
Common Design ScenariosCommon Design Scenarios
Types of Audit Trails User
Track data entry by user Entity
Track data movement across entities Scenario/Version
Track data movement across data sets such as allocations
Dimensional Design to Support Audit Trails
Common Design ScenariosCommon Design Scenarios
Dimensional Design to Support Audit Trails
DEMONSTRATION
Common Design ScenariosCommon Design Scenarios
The view dimension provides alternative perspectives of the data stored in the EPM Application
View examples Scaling Percent of Sales Utilization Conversion
Careful attention to implementation is required to avoid performance issues and/or inaccurate results
View Dimension
Common Design ScenariosCommon Design Scenarios
Standard time dimensions Years Periods Days
Calendar-based time dimensions Custom calendar dimension Date-Time Dimension
Time-Based Dimensions
Common Design ScenariosCommon Design Scenarios
Three types of currencies Functional currency Transactional currency Reporting currency
Implementing currency conversion Native vs. Custom
Design for future support of currency conversion
Dimensional Design for Currency Conversion
Common Design ScenariosCommon Design Scenarios
DEMONSTRATION
Dimensional Design for Currency Conversion
Common Design ScenariosCommon Design Scenarios
• Attribute dimensions are virtual dimensions tied to a base dimension that expand the scope of reporting for that dimension
• Direct one-to-one relationship between a member from the attribute dimension and a member from the base dimension
• Provides reporting along more than one axis in a report• Adds no additional overhead to the application
Oracle Essbase Attribute Dimensions
top related