dimension decisions: a guide to defining dimensions for your oracle epm solution
TRANSCRIPT
Dimension Decisions: A Guide to Defining Dimensions for Your Oracle EPM Solution
September, 2013
Presentation Agenda
Profile A True Story Dimensional Modeling Concepts Oracle EPM Dimensional Requirements Common Design Scenarios Questions
Company/Presenter Profile
Innovus Partners Oracle/Hyperion Enterprise Performance
Management Oracle Gold Partner Based in the NY Tri-State Area Follow us on Twitter, Facebook, and LinkedIn
RJ Linehan Over thirteen years experience consulting in
the EPM/BI Space OAUGNYC Board Member Sleep Deprived…
A True Story Part 1
The Life of Tom Jones Works at XYZ Company for past 15 years Financial Planning & Analysis Department Leverages Oracle/Hyperion EPM tools Experienced two upgrades in past 8 years No changes to core EPM solution in that time Uses Excel to do his job
Dimensional 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)
• 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 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 Concepts
Dimensional 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 source systems
Successfully Defining Dimensions During Design
A True Story Part 2
The Life of Tom Jones Queries data against ERP system and DW
that is not available in Oracle/Hyperion EPM tools
Uses Excel to incorporate that data into his analysis
Uses Access to reorganize data Process takes 3 days to complete Publishes reports to management
Typical Dimensional Requirements
• Chart of Accounts• Currency Conversion• Calendars – Regular, Fiscal, Other• Organization• Legal Entity• Business Groups• Departments• Operating Units• Inventory Organization• HR Organization
Dimension Definitions for Oracle Financials
Oracle EPM Dimensional Requirements
• Oracle EPM applications support standard and custom dimensions
• A dimension member is an element 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
• 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 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 Requirements
Common Design Scenarios
• Avoid building one application with many dimensions to support all planned data
• Organize data sets that will be planned into groups of common dimensionality
• Leverage a separate application to support consolidated reporting
Dimensional Design for Budget & Forecasting Applications
Common 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 Scenarios
Common Design Scenarios – Currency Conversion
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 Scenarios
Common Design Scenarios - Restatements
View Dimension Provides for different perspectives of
reporting Calculation standardization Dynamic calculations to reduce overhead
Dimensional Design to Extend Reporting
Common Design Scenarios
Common Design Scenarios – View Dimension
Time-Based Dimensions
DEMONSTRATION
A True Story Part III
The Life of Tom Jones Initiative undertaken at XYZ Company to
better understand business requirements Redesign of EPM Solution warranted to
support changing business requirements 3 day process drops to 1 hour process Tom is now analyzing data and not
transforming it
Dimension Decisions: A Guide to Defining Dimensions for You Oracle EPM Solution
September, 2013