dimension decisions: a guide to defining dimensions for your oracle epm solution
TRANSCRIPT
Dimension Decisions: A Guide to Defining Dimensions for You Oracle EPM Solution
March, 2012
Presentation Agenda
Profile A True Story Dimensional Modeling Concepts Oracle Financials Dimensional
Requirements Oracle EPM Dimensional Requirements Common Design Scenarios A Happy Ending Questions
Company/Presenter Profile
Innovus Partners Oracle/Hyperion Enterprise Performance Management Oracle Gold Partner Certified Consultants with average of six years of
consulting experience Based in the Tri-State Area Follow us on Twitter, Facebook, and LinkedIn We have knowledge to share
RJ Linehan Thirteen years experience consulting in the EPM/BI
Space Successfully deployed over 35 EPM/BI solutions Key Industry experience: CPG, Retail, Insurance and
Financial Services
A True Story Part 1
The Life of Tom Jones aka “Master Analyzer” 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 Has secret tools 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 aka “Master Analyzer” 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
Oracle Financials Dimensional Requirements
• Chart of Accounts– Types of Segments
• Natural Account• Balancing Segment• Cost Center• Other Segments
• Currency Conversion– Transaction Currency– Primary Currency– Reporting Currency
• Calendars – Regular, Fiscal, Other
Dimension Definitions for Oracle Financials
• Organization– Legal Structure– Management Organization– Functional Organization
• Legal Entity• Business Groups• Departments• Operating Units• Inventory Organization• HR Organization
Dimension Definitions for Oracle Financials
Oracle Financials Dimensional Requirements
Oracle 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
• 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
• 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
Dimensional Design for Budget & Forecasting Applications
DEMONSTRATION
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
DEMONSTRATION
Dimensional Design for Currency Conversion
Common 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 Scenarios
Dimensional Design to Support Audit Trails
DEMONSTRATION
Common Design Scenarios
Time-Based Dimensions
DEMONSTRATION
A True Story – Happy Ending
The Life of Tom Jones aka “Master Analyzer” Initiative undertaken at XYZ Company to
better understand business requirements Secret tools are identified 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
March, 2012