Incomprehensive Intro to PBCS

The Hitchhiker’s Guide to PBCS

To start with, this document is written in R Markdown. I use markdown to organize all my notes in Obsidian, a note-taking app. The benefits of markdown is that most HTML (e.g., Oracle Docs) are formatted in the same way so copying and pasting is very simple. Also, this file can be easily converted to Word and PDF. If there is any error or anything you believe I should include in the document, please don’t hesitate to let me know (email or via Teams).

The main purpose of this document is to provide a structured introduction to Enterprise Performance Management and specifically Planning and Budgeting for people without Oracle experiences and this is NOT a comprehensive guide. This document will refer this lengthy phrase as EPM and PBCS, and we will discuss what’s EPM and PBCS later. I hope this will benefit all new hires to embark a new journey of Oracle EPM. For the best experience to navigate through the document, please always refer to the Table of Contents on the left. Welcome on board, don’t panic and enjoy your trip!

Some Useful Links

• If you are looking for a comprehensive guidebook, then Oracle Docs should definitely be your choice.
• Oracle University also offers some free courses to help you understand EPM.
• To prepare for certificates, Oracle Planning K-Group has some good study guides to help you.

Roadmap

Please refer to the Table of Contents on the left to help navigating through the document. The overall structure of the document is:

1. Section I: Basic Background. This section will include a recap of 1. Accounting 101 and an overview of 2. Oracle Cloud 101

2. Section II: EPM - Planning and Budgeting. Then, we will dive into PBCS and break it into 3 parts: 1. Essbase, 2. Planning Application, and 3. Smart View

3. Section III: In Practice - Implementation Timeline. This will include a template timeline for projects as a reference

---

Section I: Basic Background

1. Accounting 101

Before digging into the technology, a quick recap of accounting is always beneficial.

Simple Accounting Scenario

• Cash Basis Accounting (Cash Flow Statement): more focused on the actual cash situation, i.e., what actually happened in your bank account
  • Even if a client promised to pay you $100 for a service costed $50 in the next month, your this month’s cash would be -$50 and next month would be $50 ($100-$50)
  • can show a roller coaster effect: profit may swing from period to period
• Accrual Basis Accounting (Balance Sheet): record actual transactions
  • Just because you received no cash in a period does not mean you made no profit
    • Includes cash position
  • will introduce concepts like Accounts Receivable (Asset), Deferred Revenue (Liability) to balance the transactions

Basic Accounting Terminology

Balance Sheet

• represent amounts at a point in time (a snapshot)
  • Assets = Liabilities + Shareholder Equity
  • Assets: tangible and intangible items that the company owns that have value
    • Cash, Equipment, Patents, Accounts Receivable
  • Liabilities: money (or other things) that the company owes to others
    • Mortgages, Loans, Accounts Payable, Income Tax Payable
  • Equity: the portion of the total assets that the owners/stockholders of the company fully own; have paid for outright
    • Net Income
  • B/S accounts must represent a balance at a point in time
    • doesn’t make sense to talk about the amount of cash on hand during the entire month of Jan > Cash accounts can show the amount of cash at the end of Jan

Income Statement

• represent activity over a period of time
  • may also referred as Profit & Loss
  • Revenue - Expenses = Income
  • Revenue: money the company earns from its sales of products or services, and interest and dividends earned from marketable securities
  • Expense: money the company spends to produce the goods or services that it sells
  • P&L accounts must represent activity over a period of time
    • doesn’t make sense to talk about how much we spent for gas on March 3rd > gas expense account reflect the amount spent on gas during March
  • Structure
    • Operating Revenue: \(Operating Revenue = Sales - COGS\)
      • Total Operating Revenue
      • Gross Margin: \(Gross Margin = \frac{Revenue - COGS}{Revenue}\)
    • Operating Expense: SG&A, Compensations & Benefits, etc.
      • Total Operating Expense
    • EBITDA: \(EBITDA = Net Income + Interest + Tax + Depreciation + Amortization\)
      • widely used measure of core corporate profitability
    • Income Tax
    • Net Income
      • \(Profit Margin = \frac{Revenue - Cost}{Revenue}\)
• B/S and I/S should always work together
  • B/S at the end of Month 1 + I/S in Month 2 = B/S at the end of Month 2

Cash Flow Statement

• Cash from Operating Activities
  • Revenue, A/P, A/R, Interest, Income Tax, COGS, Compensation, Rent
• Cash from Investing Activities
  • Purchase securities, equipment, ST assets
• Cash from Financing Activities
  • cash-in when capital is raised and cash-out when dividend are paid

T-Accounts & Journal Entry

• T-Accounts
  • Every transaction has two parts: Debit and Credit
    • Debit on left, Credit on right
      • Debit == Credit
    • Whenever you create an transaction, at least two accounts must be impacted: one debit, one credit
      • e.g., when you sell sth and get paid, you debit Cash and credit Sales/Revenue
    • Can combine accounts on B/S and P&L
  • Debit
    • increases an asset or expense
    • decreases a liability or equity
  • Credit
    • increases a liability or equity
    • decreases an asset or expense
  • Account balances are not always the same as the normal balance, but they usually are
    • E.g., a revenue account usually has a credit balance, but it might have a debit balance for a period if you had more returns than sales
• Journal Entry
  • A balanced transaction is usually represented as journal entry
    • Description of the transaction, one line of debit, a second indented line of credit
    • Debit and credit needs to be balanced

General Ledger & Chart of Accounts

• General Ledger
  • represents the record-keeping system for a company’s financial data, with debit and credit account records.
  • It provides a record of each financial transaction that takes place during the life of an operating company and holds account information that is needed to prepare the company’s financial statements.
    • Transaction data is segregated, by type, into accounts for assets, liabilities, owners’ equity, revenues, and expenses.
• Chart of Accounts
  • an index of all of the financial accounts in a company’s general ledger.
  • each COA typically contains an identification code, name, and brief description.
  • In short, it is an organizational tool that lists by category and line item all of the financial transactions that a company conducted during a specific accounting period

Budget vs Forecast vs Actual

• Budgeting is performed once a year for the entire next fiscal year
  • Represents a best-guess and baseline expectation for the upcoming fiscal year
• Forecasting is an ongoing process throughout the current fiscal year
  • Always updated with new info, often on a monthly basis
    
  • Seeded with budget at the beginning of the year
• Budget vs. Forecast vs. Actual
  • At the beginning of the year, the Budget is just seed/copied into the Forecast. Two should be identical
  • As time goes, the Budget will remain the same. The Forecast will seed/copy the 4 months of Actuals and adjust the projections for the remaining months
    • i.e., 4+8 process = 4 mos Actual + 8 mos Forecast
    • Will continue rolling as more actual data comes
    • Keep the Budget the same to compare with the Forecast to have LBE (latest Best Estimates)

---

2. Oracle Cloud 101

I know what you are thinking, so many 101s. Before getting into our favorite EPM, I think getting to know Oracle Cloud is also important. Oracle Cloud is a Software as a Service (SaaS). The benefit of Oracle Cloud is to lower the cost of ownership as Oracle develops and maintains the data storage and the application software. Also the software can leverage the high-speed internet and performs on a pay-as-you-go basis.

This may not be your first time to hear SaaS and you probably also know SAP, OneStream, and Hyperion. The short and perhaps not-too-accurate history is this: SAP is a German software founded in 1972. However, when it was first launched, it was more focused on the domestic market than the US market. In 1977, Oracle was established and took the opportunity to grow in the US market through collaboration with companies like GT to help implementing their products and reaching out to more clients. For the EPM realm, Hyperion adopted the idea of 1. Essbase and created EPM. Hyperion was later acquired by Oracle in 2007 and formed today’s EPM we see. In 2010, people from Hyperion founded OneStream. The core difference between Oracle EPM and OneStream is that OneStream offers one integrated platform, compared to the different modules under Oracle Cloud. Also, OneStream can handle data from different ERPs better than Oracle EPM, and thus, OneStream is favored by many PE clients as their acquired companies may use various ERP systems.

Oracle Cloud Applications

Oracle has 4 core applications: Human Capital Management (HCM), Enterprise Resource Planning (ERP), Supply Chain Management (SCM), Advertising and Customer Experience. Enterprise Performance Management (EPM) is associated with ERP. Yep, Oracle tends to have a lot of three-letter acronym. Now, let’s have a quick run-down on those applications to get a gist of what they provide.

Human Capital Management (HCM)

• Human Resources
  • Core HR, benefits, work life, workforce modeling & predictions, strategic workforce planning, advanced HCM controls
• Talent Management
  • Recruiting, onboarding, learning, career development, opportunity marketplace, performance management, compensation, succession planning, dynamic skills
• Workforce Management
  • Time & labor, absence management, workforce health & safety
• Payroll
  • Payroll, payroll connect (for global offices), payroll interface, payroll core
• Employee Experience (Oracle ME)
  • HCM communicate, touchpoints, journeys, connections, HR help desk, digital assistant
  • Ownership of employee data
• Control over system
  • UX, process & workflows, analytics & planning, data model
• Infrastructure
  • Extensibility, integration, import & extracts, security, adaptive intelligence

Enterprise Resource Planning

• Report to report
  • Automatic report to report and provides a single source of truth for global financial reporting
• Financials
  • General ledger, payables & assets, receivables & collections, expense management, bill and credit management, revenue management, accounting hub, subscription management, joint venture management
• Project Management
  • Connect to Oracle Fusion Cloud, ERP, HCM, and SCM Applications
  • Cost management, billing and revenue, planning and forecasting, management and scheduling, resource management, grants management, task management, mobile task, time and progress
• EPM
  • Enterprise planning (Section II: EPM - Planning and Budgeting), profitability and cost management, narrative reporting, financial consolidation and close, account reconciliation, tax reporting, enterprise data management
• Risk Management and Compliance
  • Use AI and ML to spot potential risks
  • Secure role design, prebuilt SOD controls, data privacy controls, access certifications, payment/fraud controls, config/audit controls, SOX certifications, ERM workflow
• ERP Analytics
  • Pre-packaged use cases: predictive analysis, KPIs based on analysis and trend

Supply Chain Management

• Supply Chain is key for customer satisfaction
• Planning
  • Supply planning, sales and operations planning, supply chain collaboration, collaboration messaging framework, demand management
• Sourcing/Manufacturing
  • Purchasing, supplier qualification management, supplier portal, sourcing, procurement contracts, supplier model, spend classification, manufacturing
• Inventory/Warehouse Management
  • Inventory management, receiving, shipping, supply chain financial orchestration, product management, product recall management, cost management, warehouse management
• Fulfillment & Delivery
  • Order management, configurator, pricing, supply chain orchestration, global order promising, transportation management, global trade management
• Servicing
  • Maintenance, service logistics

Advertising and Customer Experience

• Advertising
  • Activation, audiences, OnRamp, contextual intelligence moat measurement
• Marketing
  • B2C marketing, B2B marketing, fusion marketing, marketing automation, campaign management, customer loyalty and engagement, content management, data management platform, audience segmentation, customer data platform, behavioral intelligence, testing and optimization
• Sales
  • Sales automation, sales force automation, sales planning, sales performance management, customer data management, commerce, configure, price, quote, subscription management, partner relationship management
• Service
  • Field service management, knowledge, digital customer service, service center, intelligent advisor, IoT service monitoring for connected assets

---

Section II: EPM - Planning and Budgeting

Finally, the star for the show, EPM. EPM contains many modules from Profitability and Cost Management, to Financial Consolidation and Close, to Planning and Budgeting, to Account Reconciliation, to Tax Reporting, to Narractive Reporting, to Enterprise Data Management. This document will only focus on Planning and Budgeting Cloud (PBCS).

Planning is a process that determines financial directions and visions. Most companies develop annual plans, as part of the larger 3-5 yrs strategic plans. Planning includes strategic plans, long-range plans, and annual plans. Oracle planning can load, validate and calculate data, prepare budget and revenue forecasts and review, report and analyze, and finalize budgets and plans. It can be used by accountants, finance managers, and department heads.

A basic Planning Architecture includes 3 elements:

• 1. Essbase: a database that can connect to the “outside world” and store all data

• 2. Planning Application: build on the multidimensional Essabase cubes. It can configure dimensions, build data forms, set businesss rules, create reports, and more

• 3. Smart View: the Excel plug-in that allows users to review and analysis data from PBCS in a familiar setting in Excel

1. Essbase

This part may be a little technical, but I think it is still needed to understand the structure of the data. If you just want to high-level conclusion, please consult the Table of Contents and skip to Conclusion of Essbase.

OLAP and Multidimensional Databases

For more detailed information, please consult the Oracle Docs on Essbase.

• Online analytical processing (OLAP): a multidimensional, multi-user, client-server computing environment for users who need to analyze enterprise data.
  • Hyperion, acquired by Oracle, renamed its product OLAP as Essbase. Thus, Essbase is OLAP.
    • A successful OLAP application provides information as needed; that is, it provides “just-in-time” information for effective decision-making.
    • Key to OLAP systems are multidimensional databases, which not only consolidate and calculate data; but also provide retrieval and calculation of a variety of data subsets.
• Multidimensional Database: supports multiple views of data sets for users who need to analyze the relationships between data categories.
  • Most multidimensional databases have two characteristics:
    • Data is not smoothly and uniformly distributed.
    • Data does not exist for the majority of member combinations. For example, all products may not be sold in all areas of the country.
  • Essbase maximizes performance by dividing the standard dimensions of an application into two types: dense dimensions and sparse dimensions.
    • This division allows Essbase to cope with data that is not smoothly distributed, without losing the advantages of matrix-style access to the data. Essbase speeds data retrieval while minimizing memory and disk requirements.
  • Essbase creates a data block for each unique combination of members in the dimensions
    • The data block represents all the dense dimension members for its combination of sparse dimension members.

Differ from relational database, dimensional database has two types of forms: fact table and dimension table. They are usually structured as star schemas. The benefits of dimensional database are 1) allow you to connect data from different data sources. 2) With dimensional data models, performance is increased and response time is decreased due to denormalization and fewer joins between relations in comparison to relational data models.

Cubes

• Cubes tab under Application can view details of an existing cube or create a new cube
  • Standard application can have up to 7 cubes
    
  • After a cube is added, it cannot be renamed or deleted

BSO Cube (Block Storage Option)

• mostly used, integrated calculation
  • FIN Cube: holds financial data (income statement, balance sheet)
  • Workforce Cube, Capital Cube, SKU Cube
• Essbase requires the developer to tag dimensions as “dense” or “sparse”. The system then arranges data to represent the hypercube into “blocks”, where each block comprises a multi-dimensional array made up of “dense” dimensions, and space is allocated for every potential cell in that block. Sparsity is exploited because the system only creates blocks when required.

ASO Cube (Aggregate Storage Option)

• While BSO effectively minimizes storage requirements without impacting retrieval time, it has limitations in its treatment of aggregate data in large applications. Thus, ASO cube is created.
  • 1 ASO cube is automatically deployed in every Planning application
  • fast and aggregated for reporting
  • can handle larger numbers of dimensions and members and is optimized for sparser dataset
  • In practice, the RPT Cube is used only for reporting if the BSO cube has become too large
• Following a data load, Essbase ASO does not store any aggregate values, but instead calculates them on demand.
  • For large databases, where the time required to generate these values may become inconvenient, the database can materialize one or more aggregate “views”, made up of one aggregate level from each dimension (for example, the database may calculate all combinations of the fifth generation of Product with the third generation of Customer), and these views are then used to generate other aggregate values where possible.
• ASO CANNOT be treated for calculation purposes as a single large hypercube, because aggregate values cannot be directly controlled

Data Management

• The Source
  • Data can be uploaded through a flat file or direct connections. Usually, the General Ledger from ERP acts as the source of truth for EPM. Note that for PBCS, the source of truth can also be the General Ledger after financial consolidation (review at General Ledger & Chart of Accounts).
  • For Oracle ERP, EPM can conduct a direct connection through URL
    
  • Flat file can be downloaded from other ERP systems and feed into the data management
    • EPM Automatics can automate the download - save - upload process
  • Statistical data (e.g., # of customers, # of tickets sold) may not be included in the General Ledger, but can be manually added to PBCS through Dimensions
• Data Mapping
  • If there is any differences in dimensions, hierarchies or any details, instructions should be given in terms of how to map the data

Conclusion of Essbase

• EPM uses a Multidimensional Database, called Essbase. We usually refer to it as Cubes
  • Note that, not all Oracle products store data via Essbase ERP still uses a relational database, since it handles transaction data.
• For Essbase to function properly, Dimensions must be specified to import or retrieve data
• BSO cubes are most often used in projects for data retrieval, calculation, and reports. ASO cubes contains aggregated information and is seldomly used in projects

2. Planning Application

Oracle EPM PBCS includes modules from:

• Dimensions
• Metadata
• Data Forms
• Business Rules
• Reports
• Security
• Approval

Dimensions

Dimensions Overview

• 1. Essbase has two types of Dimensions
  • Standard Dimensions: represent the core components of a business plan and often relate to departmental functions.
    • Typical standard dimensions: Time, Accounts, Product Line, Market, and Division.
  • Attribute Dimensions: a special type of dimension that are associated with a standard dimension and contain members that can be used to classify members of another, associated dimension. Attribute dimensions describe characteristics of data
    • e.g., add location/state attributes for the employee dimension
    • Navigator > Create and Manage > Dimensions > Custom Attributes
      • Through attribute dimensions, you group and analyze members of standard dimensions based on the member attributes (characteristics).
        • For example, you can compare the profitability of noncaffeinated products that are packaged in glass to the profitability of noncaffeinated products packaged in cans.
  • Each cube has a unique set of dimensions. For cubes that share dimensions, they may have different members
• Dimensions: data categories to organize and analyze business data
  • Dimensions will mimic the client’s Chart of Accounts (review at General Ledger & Chart of Accounts)
  • Members are the components of dimensions
  • Access Dimensions: Application > Overview > Dimensions
  • Precedence of dimensions is critical for the structure and performance of an application
    • The order determines how the data calculations will perform
    • Set evaluation order of dimensions to avoid data type conflicts
  • Can filter dimensions by cube
  • Usually 13 -15 dimensions per cube

Dimension Hierarchies

• Dimension Hierarchies
  • Define structural and mathematical relationships
    
  • Define consolidation between members
    
  • Define calculation via aggregation options
    • How child member values aggregate to parent members, whether they add, subtract, multiply, divide, or ignored
• Dimensions are made up of Members, which are organized into hierarchies
  • Members have parent/child relationships
    • There must be a1:nrelationship between a parent and children. A parent can have one or more children, but a child can have only one parent.
  • Members with a shared parents are siblings
    
  • Any member beneath a parent is a descendant of the parent
    • For non-immediate child
      
  • Any member above another member is an ancestor of the lower-level member
    • For non-immediate parent
      
  • Members without children are level-0/base-level members
    • Data can only be entered to level-0 members
    • level-0 may have siblings with non-level-0

Managing Dimensions

• Multidimensionality: put multiple dimensions together allows users to ask very specific questions
  • e.g., In May 2017, what was the bestselling clothing items in the Northeast market?
• Interdimensional Irrelevance: occurs when many members of a dimension are irrelevant across other dimensions
  • Essbase defines irrelevant data as data that Essbase stores only at the summary (dimension) level. In such a situation, you may be able to remove a dimension from the database and add its members to another dimension or split the model into separate databases.
  • e.g., checking I/S on an employee level
• Required Dimensions
  • Period: the months and quarters
    
  • Years: the fiscal years
    
  • Scenario: contains “Budget”, “Forecast”, and “Actuals”
    
  • Version: have only Version member called “Working”
    
  • Account: the I/S and B/S accounts from client’s CoA
    
  • Entity: business units or companies by which a client organizes itself
• Costumed Dimensions
  • Source: the layers of data entered into the system
    • The final estimate for an account made include base inputs + topside adjustments + overlays + allocations, etc.
  • Cost Center: could be called as Department. Groups within the organization that absorb expenses and revenue
  • Product: represent specific product lines or product groupings. Common for Retails
  • Currency: GT has a custom-built currency conversion solution to help convert foreign currencies into USD (not Oracle one). Common for international firms

Sparse and Dense Dimensions

• Essbase maximizes performance by dividing the standard dimensions of an application into two types: Dense Dimensions and Sparse dimensions
• Sparse Dimension: with a low percentage of available data positions filled
  • They lack data values for the majority of member combinations
  • Most multidimensional databases are inherently sparse
    • e.g., Product, Market
• Dense Dimension: has a high probability that one or more cells is occupied in every combination of dimensions
  • e.g., Account, Period, Year, Scenario
  • at lease 1 dense dimension is required for each application
  • Dense Dimension is dynamic: all data will be aggregated automatically
• Identify Sparse and Dense Dimensions is important to optimize the system performance
  • use Navigator > Monitor and Explore > Application Diagnosis to identify system performance

Dynamic Dimension

• Dynamic Calculation: When you design the overall database calculation, it may be more efficient to calculate some member combinations when you retrieve their data, instead of precalculating the member combinations during a batch database calculation
• Member Types
  • Dynamic Calc Members: calculate upon retrieval
  • Dynamic Calc and Store Members: similar but does not re-calculate with every retrieval, only re-calculate when Essbase detects the need
• Create
  • Dimensions > Create New Child/Sibling > on Member Properties, under Data Storage, select Dynamic Cal > Member Formula will appear, and enter the corresponding formulas

Metadata

• Metadata can be understood as members of Dimensions

Metadata Properties

• Navigator > Create and Manage > Dimensions > Custom Attributes

• Member Properties

  • Name: Is required and must be unique across the entire application
  • Description, Alias: Not required, but must be unique across the entire application
  • Data Storage: There are 3 common options in this application
    • Dynamic Calc – This member has a formula, or automatically aggregates its descendants based on the descendant’s aggregation setting
    • Never Share – Used for base level members where data can be entered
    • Label Only – No data can be stored here as this member is used for labeling purposes only
  • Plan Type: Select the cube(s) in which a member is to be available.
    • NOTE: If a member is NOT available in a cube, none of its descendants will be available in said cube
  • Aggregation Setting: The drop down next to the Plan Type checkbox to determine how data aggregates (Addition, Subtraction, Ignore)
  • Account Type: Accounts’ time balance (how values flow over time) and determines accounts’ sign behavior for variance reporting with member formulas
    • Revenue
    • Expense
    • Saved Assumption
  • Variance Reporting: Determines whether it is treated as an expense when used in member formulas:
    • Expense: \(Var = Budget - Actual\)
    • Non-Expense: \(Var = Actual - Budget\)’$
  • Time Balance: How values flow over time i.e. Flow for IS accounts and Balance for BS accounts.
    • Defaults based on the Account Type except for Saved Assumptions.
  • Data Type: Currency, Non-Currency (used for HC), Percentage, Smart List (aka drop-down menu), Date, Text

• User Defined Attribute (UDA)

  • All base level IS and BS accounts are set up with the AVG (average) or EOM (end of month) UDA
  • Since there is no Balance Sheet being loaded or planned, only the “AVG” UDA is being utilized

• Standard Properties

  • After any metadata changes are made to the application a refresh must take place.
    • During a refresh, all users are locked from being able to run rules, open forms/Smart View. At the time of print, the average run time for a refresh is ~20 seconds.
    • To execute a refresh, navigate to the dimension library and click the Refresh button.
  • Users can also apply security to members of dimensions that are eligible for security by highlighting the member security should be added to and then clicking the security button

Data Forms

• Static templates that allow users to enter data
  • Usually would define many dimensions to make sure correct data entered into the right intersections
    • e.g., restrict to certain year, from the X entity
    • End users cannot change the structure
  • May be used as read-only/review forms
    • Usually would use Reports
• A data form is created by an admin or a power user
  • Point-of-View (POV): show Dimensions.
    • cannot be changed by end users
  • Page: list Dimension Members that end users can select from
    
  • Grid: made of rows and columns
  • Data Cell Color
    • White: enter data, or see data that has been saved in the cube
    • Yellow: change the value, but not yet saved
      
    • Grey: can see but not change value
      
    • Teal: has conditions behind the cell

Create Forms

• Navigator > Create and Manage > Forms
• Now, the default is to create simple forms. Composite forms are removed from Oracle with the intention to promote use on dashboards and infolets
• Form Folder: The structure of the folder is related to Security

Properties

• Each form must have a name
• Forms are cube specific, must be connected to FIN or other cubes, cannot be connected to two cubes
  • Selected cubes cannot be changed once the form is saved and finished
  • For forms connected through right clicks, two different forms can be connected with different cubes

Layout

• At least one member from each dimension must be selected to either POV, Page, Rows, or Columns
  • POV usually contains Version
    • POV can only select ONE member
  • Page usually includes Entity, Cost Center
    • Under situations when client want to show combined members from different/same parent, can use Alternate Hierarchy in Dimensions to create that relationship to be aggregated or show.
  • Rows are usually Account
  • Columns are usually Period, Year, Scenario
    • At least one dimension must be set to row and column
  • All members in POV, page and grids must be bottom-level/Level 0 members to allow data entry
• Attribute Dimensions (rarely used)
  • more detailed information on the dimension members. e.g., the states where each employee stays
  • avoid the need to create a separate dimension
  • often used in Business Rules
• Grid Properties
  • Overall changes to the form
  • can use Suppress missing blocks, missing data-Rows, invalid data -Rows to make the form more clean
  • seldom Suppress Columns to show the change across time
• Segment Properties
  • Individual changes. Can pinpoint to each row and column. Click on row number or column names to call out
  • Hide Column: can be used to hide Actuals while entering Budget, so that the suppressed rows for Actuals will stay to avoid the extra step to suppress data
• Display Properties
  • Overall changes to the form
  • Make from read-only: most often used for review data
    • need to have higher level members for Page and Grids
  • Hide Forms: usually used on right-click forms
    • Those forms don’t make sense to appear in the folder. e.g., SG&A Budget Data has right click to Add SG&A Data
• Substitution Variables
  • act as placeholders for information that changes regularly, usually Scenario, Period, Year
  • usually used when a value reference a member name across multiple forms or reports
  • Form Design: enter &Name
  • Navigator > Tools > Variable > Substitution Variable
    • Action > Add > select Cube > enter Name and Value
    • use All Cubes for dimension members across cubes
    • need to a 1:1 relation

Other Options

• Precision
  • usually set Currency and Non-currency Values with min and max at 0, Percentage allows for 2
• Context Menus
  • The right-click associated forms
  • If undefined, the associated forms can be access through right clicks everywhere on the form
    • With selected Dimensions, the associated forms will also show the selected results
  • Action Menu
    • can pre-set how the right click would display (restrict the order)
    • Navigator > Create and Manage > Action Menus
• Dynamic User Variable
  • User Variable
    • act as filters in forms, enabling users to focus only on certain members
    • Navigator > Tools > Variable > User Variable
      • could be a 1:n relation
    • can be shown both on the Page and Row to show more detailed information

Business Rule

• The order of the rules selected determine the order to be run
  • usually run aggregations at the end
• select Run after Save, Use Members on Form, Hide Prompt
  • Use Members on Form: If a business rule has runtime prompts, to match the default member selection on the runtime prompt window to the current members in the page and POV axes of the open data form
  • Hide Prompt: to hide the runtime prompt value from the user

Best Practice

• Keep the order of POV and Page consistent across forms
• Double check the Dimension > Scenario, the start and end period for Actual and Budget. Change when needed to ensure the inputs can be made
• For Input forms, the Page is usually set to ILvl0Descendants(). For Review forms, can be set to IDescendants()
  • Just to make sure all inputs are to the level 0 members
• For Review forms, Make form read-only in Display Properties. For rows or columns to be read only, select row/column to call out Segment Properties and select Read-only
• Expand or not: if want to have part of the rows expanded and part not, select Start expanded in Dimension Properties and for the rows not to be expanded, select all members need to be showed (do not use IDescendants)
• Action Menu: can directly add a whole list of action menus built before to Other Options > Context Menus

Enter Data

• Supporting Details: functionally similar to the right click, but the difference is that supporting details are added to cells, and thus no new dimensions are needed to create a separate form
• Lock cells: for cells locked and after the form is saved, even when spreading data, data in the locked cells won’t be changed
• Adjust: can high level adjust the data in the cell
• Spread: has option for evenly split or proportionally spread

Business Rules

• Two ways to calculate data
  • Dimensions: Member Formula
    • Dynamic Cal (Dimensions) will calculate data on each retrieval
    • Syntax: put quotation marks on the members and end with semicolon
  • Cal Manager

Calc Manager

• A tool for developing and administering rules for Planning and Essbase
  • Navigator > Create and Manage > Rules
  • Business Rules are put into rules folders based on Admin or Users should execute the rules Security
• You can design, validate, deploy, and administer business rules that are in a central repository and reuse business rules components within multiple applications
• Work with predefined and custom templates with a wizard
• Organize rules into rulesets so that you can launch several rules in a specific sequence
• Rules often used to calculate data across large segments of the database. Common business rules include:
  • Aggregate and consolidate data
  • Allocate data
  • Clear data
  • Copy data
  • Seed data
  • Perform driver-based calculations

Write Rules

• Layout
  • New Objects: Pre-defined objects you can drag into the main rule layout
  • Business Rule menu bar: available actions for the rule like Save, Save As, Validate, Deploy, Launch
  • Existing Objects: access saved objects and pre-defined templates here
  • Main Layout: visual data flow of the business rule (graphical/designed view)
  • Details section: displays details for selected component in main layout
  • Properties: context-sensitive information panel to define properties for selected component
• Graphical/Designer Mode:
  • Suited for those less experienced in scripts
  • Templates and wizards provided
  • Visual/step-by-step guidance/flow of rule
  • Steps
    • Drag a “Script” into the Rules Designer and place it in between the Begin and End buttons
    • Scripts can be dragged from the New Objects window
    • The script can be edited in the Script tab
• Script mode
  • Pure calculation scripting
  • Some Basic Syntax
    • End statements with a semi-colon;
    • Put quotes around member names with spaces – but recommend for all member names
    • Not case sensitive – but recommend being consistent
    • Comments in scripts enclosed by /* comments */
    • Cross-dimensional Operators – used to specify intersections in scripts (can override FIX)
      • “LoB Targets Flag”-> “BegBalance” -> “No Account” -> “NoFunctionalArea“
    • Try to focus script on as few intersections as necessary
• Calculation Function
  • Function List
  • FIX() – ENDFIX: where to focus the calculation on
    • insert cross-dimensional member in the FIX; order doesn’t make a difference
    • For dimensions doesn’t included in FIX, calculation will operation on every intersections
    • functionally similar to POV
    • Use nested structure to reduce cal time as each FIX looks into a data block within the nested, use scare dimensions
  • @Relative: where in the chain you want to pull
    • can be based on Level or Generation
    • @Relative (X,0): look at member X and pull Lvl0
  • DATACOPY: create data block
    • DATACOPY a to b: a and b need to be members from same dimension
  • SET Commands: usually need to have it
    • Recommend to include in beginning of scripts
    • SET UPDATECALC  -Turns Intelligent Calculation on or off.
      • SET UPDATECALC ON | OFF;
    • SET AGGMISSG - Specifies whether Essbase consolidates #MISSING values in the database.
      • SET AGGMISSG ON | OFF ;
    • SET EMPTYMEMBERSETS ON | OFF;
      • stops the calculation within a FIX…ENDFIX command if the FIX evaluates to an empty member set.
  • Fixed Loop: mainly used for rolling balance
    • Loop (#of time) – ENDLOOP
  • IF,ELSE,ELSIF,ENDIF: Performs conditional tests within a formula.
    • Using the IF statement, you can define a Boolean test, as well as formulas to be calculated if the test returns either a TRUE or FALSE value.
    • IF( condition) statement ; [ …statement ; ] [ ELSEIF…statement | ELSE…statement] ENDIF;
    • For example:
      Profit (
      IF (Sales > 100)
      Profit = (Sales - COGS) * 2;
      ELSE
      Profit = (Sales - COGS) * 1.5;
      ENDIF;);
      
• Rules must be validated, saved and deployed
• Substitution Variables
  • Make rules dynamic by utilizing Substitution Variables in Rules
  • Can be used in the Global Range/Fix and within scripts
  • The &Variable denotes a substitution variable
• Run-Time Prompts/Variable Designer
  • Calc Manager Variables – placeholders in a business rule for a specific purpose
    • Makes rule more dynamic
    • Runs rule only for members entered in prompts/forms
  • Can be defined at global, app, cube or business rule level
  • Often used to pick up members from forms or other prompts – “Run time Prompts” or RTPS
  • In Scripts, RTPS have {} around them
  • You can use Runtime prompts in calculations to dynamically pass FIX statements from data forms
• RuleSets
  • You create a business ruleset by combining business rules (or business rulesets) that can be launched simultaneously or sequentially.
    • Rules from multiple cubes
  • RTPs can be applied to all rules in ruleset or can prompt separately by rule

Reports

• The three types of artifacts are: Reports, Books, and Bursting Definitions
  • Report: Reports provides a user-friendly, robust report development framework along with an enriched report viewer experience
  • Book: Books provide the ability to group together one or more reports, Books, and other documents, to generate a single PDF output
  • Bursting Definition: Bursting enables you to run a single report or book for more than one member of a single dimension for one data source and publish a PDF output for each member

Build Reports

• Design Reports through three parts:
  • Report Components
    • Header—An area where you can display text on the top of each page of a report. You can also add images to a header.
    • Report Body—The main area of the report where you can insert and display grids, charts, images and text boxes.
    • Footer—An area where you can display text on the bottom of each printed page of a report. You can also add images to a footer.
  • Report Objects
    • Grid—A report object in which you retrieve data in the rows and columns.
    • Chart—A report object that displays data from a grid. Charts are graphical representations of the data in a grid
    • Text Box—A report object that can contain text, or functions that retrieve data such as report settings, data values, point of view (POV) values, or dimension members.
    • Image—A report object that contains a graphic or an image file. You can add images to the body of the report and to a header or footer.
  • Grid Components
    • Row—A horizontal display of information in a grid. A row can contain text, data, or derived data from a calculation. You can format individual rows in grids.
    • Column—A vertical display of information in a grid. A column can contain text, data, or derived data from a calculation. You can format individual columns in grids.
    • Cell—An intersection of a row, column, page, and Point of View (POV) for a grid. You can format individual cells in a grid.
• Report POV
  • A report that contains the grid has a global Point of View (POV) that applies to all report objects in the report that display data. Grids also have a local point of view (POV) which contains all of the dimensions from a data source that are not on the grid rows or columns, and which determines the data that gets displayed in a grid.
  • Default Setting
    • If you do not make any member selections or set any options for a POV dimension, that dimension is set to Default while you are in report edit mode.
    • When you preview a report, you can select any members in a dimension that you have security access to in the data source. Members that you have recently selected are displayed so that you can select from them more conveniently.
  • Selecting Multiple Members
    • While you are in edit mode, you can select multiple members for a dimension, and you can also use functions (for example, Children of Total Entities) to define a list of members for the report viewer to choose from.
    • You can also set Display Suggestions Only to restrict viewers to select only from the list of members that you choose.
    • When multiple members are selected for a POV dimension, you can set the dimension to Print All Selections. When you preview the report in PDF mode, each member selection will be rendered on a different page.
  • Locking or Limiting User Selections
    • When you define members in a POV dimension, you control what a report viewer can select from.
    • If only one member is selected for each dimension, you can also hide the POV from report viewers.
• Grid Properties
  • General Properties
    • General properties affect the entire grid. Use general properties to edit some of the following types of items:
    • Title: Set a title to be displayed above the grid
    • Data Source: View or change the grid’s data source
    • Zoom: Define the default grid behaviors for row and column sizing, row banding, zoom and drill to source.
    • Heading: Set the member heading properties
    • Text Option: Define the text replacement labels for zero, missing and error data cells
    • Suppression: Enable suppression
• Column and Row
  • Column Properties
    • Column properties affect an entire column. You must click in a column header to select the column that you want to view the properties for.
    • Use column properties to override the default grid settings for:
      • Column width
      • Repeated headings
      • Suppression settings
  • Row Properties
    • Row properties affect an entire row. You must click in a row header to select the row that you want to view the properties for.
    • Use row properties to override the default grid settings for:
      • Row height
      • Repeated headings
      • Suppression settings
  • You insert rows or columns into a grid to add content, such as data, formulas, text, and Notes to the grid. Types of rows and columns include:
    • Data—Members of dimensions that are retrieved from a database connection. The member in a data row or column defaults to the top-level member of the dimension.
      • A # symbol in the cell, row, or column indicates that it is a data cell, row, or column.
    • Formula—Values based on formulas. Commonly used to calculate totals, averages, and variances on data rows or columns.
      • An = # symbol in the cell, row, or column indicates that it is a formula cell, row, or column.
      • Formula row or column headings, which display the row number or column letter, display a symbol formula indicator as an indicator.
    • Text—Text typed into the cells or returned dynamically through a text function.
      • Text rows, columns, or cells in grids do not contain symbols when added to a grid. They are initially blank until populated with text.
      • Text row or column headings, which display the row number or column letter, display a symbol text indicator as an indicator.
    • Separator—Inserts a blank row or column.
• Cell Properties
  • Cell properties affect the selected range of cells in a grid.
  • Use cell properties to change the way cell text is formatted.
    • For example, you can change the way positive and negative numbers are displayed, and you can change the font, color, and size of text within a cell.
    • You can add borders or shading to a cell range and specify zoom and indent options.
    • You can also replace text in a cell if you would like the text to appear different than the current selection
• Text Boxes
  • Text boxes are report objects that can contain text and text functions. You can enter a label, description, or a function that retrieves the current date in a text box.
  • You can also enter multiple paragraphs of text. Like other report objects, you can move, resize, or reposition the area containing the text.
• Images
  • You can add images to the header, footer, or body of a report. After you add an image to a report, you can format the image.
  • The following image formats can be inserted into a report: .png, .gif, .jpg
• Charts
  • A chart is a graphical representation of report data from a grid on a report.
  • You can use the different chart types to illustrate trends and tendencies or to highlight differences and improvements.
  • Types of Charts: Bar, Line, Area, Combination, Waterfall, Pie, Pyramid, Polar, Radar, Scatter, Bubble, Tree-map, Sunburst, Gauge

PBCSS Reports Functionality

• Managing Folders and Reports
  • Reports are managed on the Reports listing page with different view options available.
  • Under Documents, you can access and maintain 3rd party documents used and generated with Books and Bursting; for example, MS Word and PDF documents inserted in Books and .CSV files used in a bursting definition, along with PDF files generated by a bursting definition.
• Reports Books and Bursting
  • Reports includes books and bursting functionality.
  • Books provide the ability to group together one or more reports, books, and other documents, to generate a single PDF output.
  • Bursting enables you to run a single report or book for more than one member of a single dimension for one data source and publish a PDF output for each member. You can schedule a bursting definition as a scheduled job. 

Infolets and Dashboards

• Infolets
  • An infolet is a self-contained, interactive box-shaped container used to display information using text and charts
    • Provide answers to most critical questions
      
    • Promote essential, easily consumable info
      
    • Visually rich presentation
      
    • Progressively display essential details and actions
  • Infolets are grouped under infolets page and are rearranged depending on the size of the browser and the size of the infolets
  • Three views
    • Front view: required. Provide a quick look at high level information that has a direct effect on your work
    • Back view: optional. Presents analytical information
    • Expended view: optional. Presents more detailed information
• Dashboards
  • Provide a summarized data
  • Planners can chart, evaluate, highlight, comment on, and even change key business data using dashboards
  • Build interactive dashboards with rich data visualizations using real-time data
  • Cascading effect to visualize impact on components in dashboard
  • Dirll down to further details
  • Features
    • Drag and drop form and charts to create dashboards
      
    • Supports inclusion of maximum 6 forms
      
    • Include up to 9 charts or tiles
      
    • Include user variables in the global POV bar and the local POV
    • Include commentary

Security

• Security determines what artifacts end users can interact with inside the Planning application
• Determined by 2 processes
  • System Roles
  • PBCS Security Assigned

System Role

• Create Accounts and Assign Roles in Oracle Cloud Infrastructure
  • Identity Domain Administrator: creates and manages user accounts and their access to applications within the domain​
  • System Roles
    • Viewer: can only view data; not often used
      • May use on people who are not involved in planning, such as CEO
      • Even given write access in PBCS, will only be able to view
    • User: can enter data, open data forms, view reports, and run rules; most common role
    • Power User: User + edit data forms and configure data management integrations, Controls the approvals process
      • Cannot create rules
    • Admin: complete access to the entire application

Application Security

• Manage Groups and Users
  • Navigator > Tools > Access Control
  • Only System Admin can add or manage groups
  • Groups are assigned roles on objects throughout the application​. Users are then granted application roles by being added to groups​
  • Other features in Access Control
    • Assign Application Roles: Additional specific Application roles can be added to groups/users​
      • usually just use Ad Hoc Read Only User
    • Role Assignment Report: See which roles are assigned to users​
    • User Login Report: See recent user logins​
• User Group Report: Lists the direct or indirect membership of users assigned to groups
• Dimensions
  • Navigator > Create and Manage > Dimensions
  • The highest level dimensions need to set up for security
    • Edit dimension property > check Apply Security
  • Groups/Users get assigned Read/Write/None access to members/dimension segments​
    • None is to forbid people from seeing anything; seldom used
  • Applies to Smart View Adhoc pulls as well as forms​
  • do NOT need to add security for every dimensions
    • usually focus on Company, Cost Center. Accounts usually do not set security.
    • With the Essbase, if other dimensions do not grant access, users cannot see the data
• Forms
  • Navigator -> Create and Manage -> Forms​
  • Groups/Users get assigned Read/Write access to form folders/individual forms​
    • Read: view and input data; mostly used
    • Write: edit the form design
      • Must be assigned to Power User or higher to work
    • None: No access to form/folder​
• Rules
  • Navigator -> Create and Manage -> Rules Security​
  • Rules Security controls which rules users can Launch/No Launch​
    • usually just use Launch
  • Reversed order: create folders with different security > assign rules to the folders
    • New rules must be moved into folders which have security applied​
• Security Report
  • Navigator -> Monitor and Explore -> Access Control​​
• Order of Evaluation
  • Assigned permissions take precedence over inherited permissions
  • If one member belongs to two groups with different permissions assigned, the least restrictive permission takes precedence. ​If an individual is assigned to multiple groups, the group with the highest access permission takes precedence.​
    • if one group assigns the member Read permission and another group assigns the same member Write permission, Write takes precedence.​
  • None takes precedence over Read and Write.​
• Best Practices
  • If you do NOT people to have access, you can just not assign securities to the object
  • Groups: Define security by Groups rather than by individuals​
  • Dimensions: Avoid assigning securities to Lvl0. Set securities to higher level and allow for Descendants (inclusive)
  • Forms: Assign access to folders instead of individual forms
  • Rules: Remember to move the rules into the corresponding folders, otherwise rules cannot be run
  • Check Security: set up the test account to test out the security setting

Approval

Note: EPM Approval is seldom used in projects because the process is too length and complex for most clients. But questions about approval may be included in the certificate exam. The content is from Oracle University.

• About Approval
  • Approvals structure the workflow and formalize authority levels
    • Review and approve planning data
    • Track budget process
      
    • Identify issues during review
      
    • Track and review comments
    • View promotional path
    • Plan data meets validation rules
  • Approval Units
    • Plans are tracked and managed through Approval Units
    • An approval unit is basic unit for preparing, annotating, reviewing, and approving plan data
    • Submit plan data for approval corresponding to a particular scenario, version and entity
    • Support for planning unit annotations
    • Approval units started by Administrator are part of approval process
  • Prepare for an Approval Process
    • Administrator set up Approval Units -> designate who reviews and approves plan data -> start each Approval Unit
    • When the approval unit starts, you can only change data when you are the owner of the unit
  • Approval Process
    • Administrator starts Approval Unit -> Owner enters data, and submit for review -> Participants in the Promotional Path reviews and approvals -> Plan approved and Approval Unit is promoted according to the promotional path -> All reviewers approve all AU, the planning cycle is complete
• Approval Unit Actions and States
  • AU States
    • Not Started: initial state of all AU. An Admin begins review process
    • First Pass: beginning state of AUs selected for budgeting process
      • No specific owners. People have write permission can enter data and then promote the AU
    • Under Review: occur after Submit or Promote action and implies someone is reviewing AU
      • Only the owner and Admin can change data
    • Frozen: all related data in descendent AU is read only
      • Owner or people above the owner can unfreeze the AU
    • Distributed: multiple people are reviewing the budget
    • Not Signed Off: occurs when Admin selects Reject
      • Only the owner and Admin can change data
    • Approved: occurs when Approve is selected
      • The last person of the promotional path or the owner becomes the owner of AU
  • AU Actions
    • Exclude: stop the AU and delete all history. Change the state to Not Started
    • Originate: Bottom Up: move the AU to the first in the promotional path, and the AU moves to Under Review
    • Promote: Under Review
      • In free form template, move the AU to anyone in the list.
      • For the Bottom Up template, promote moves the AU to the next position in the promotional path
    • Delegate: delegate the AU to a newly selected owner and show Under Review
    • Take Ownership: take the ownership away from the current owner. The state is Under Review
    • Return: return ownership back to a group. State is Under Review
    • Pass: works when the reviewer is a group not an individual. Passes ownership to another person within the group. State is Under Review
    • Distribute: pass AU ownership to multiple people. State is distributed
      • Distribute Children and Distribute Owner
    • Submit: move the AU to the next level. State is Under Review
      • Submit to Top: gives ownership to the top person defined in the hierarchy (the last in the promotional path). State is Under Review
    • Reopen: reopen the AU. State is Under Review
  • Validating and Changing the status of an Approval Unit
    • Validate AU
      • Owner promotes AU -> go through the planning process and validation rules -> update Promotional Path OR stop promotion
    • Change Status
      • Approvals -> select AU -> select Action

Action	New AU State
Start	First Pass Under Review
Exclude	Not started
Promote	Under Review
Reject	Not Signed Off
Sign Off	Signed Off
Delegate	Under Review
Take Ownership	Under Review
Return	Under Review
Pass	Under Review
Freeze	Frozen
Unfreeze	Under Review
Distribute Distribute Children Distribute Owner	Distributed
Submit	Under Review
Submit to Top	Under Review
Approve	Approved
Reopen	Under Review

3. Smart View

• Excel add-in to allow users to interact with the application
  • Open data forms and submit data
  • Create Ad hoc Grids to analyze data and build reports

Setting Up Smart View

• Smart View can connect to EPM, Analytics Cloud, ERP, on-premise applications (EPM, BI, Fusion ERP)
• Connections to database
  • Shared
    • Set up by admins
      
    • Access either from a central server or via an XML file
    • Set up for one or more EPM Cloud providers
  • Private
    • Single-provider connections
    • Created either by saving a shared connection locally, by using a wizard, or by entering a URL directly
      • Via wizard: Excel > Smart View > Panel > Private Connections > Create new connection > select provider type > enter URL > login into Oracle Cloud > select cube > click Ad hoc analysis
        • If error message showed up, Smart View > Options > Data Options > unclick Suppress Missing Blocks
• View Extensions
  • Options > Extensions
    
  • Admin control what extensions you can download and update

Ad Hoc Options

• Control how Ad Hoc actions perform and how Ad Hoc Grids display
• Two types of options
  • Global options: apply to the entire current workbook and any workbooks and worksheets you will create
    • Include advanced, cell styles, and extensions
  • Sheet options: specified for worksheets and are the default option for new worksheets in the current and future workbooks. Changes in sheet options do NOT affect existing worksheets
    • Include member options, data options and formatting
• Smart View > Options
• Member Options
  • Zoom In Level: determine how far into a dimension you drill at a time
  • Member Name Display: choose whether to show member names or member aliases
  • Indentation: choose to indent descendants or not
  • Ancestor Position: choose if parents show beneath or above descendants
  • Include Selection: choose to keep or remove parent members as you drill into hierarchies
• Data Options
  • Suppress Rows: suppress rows without data or with 0
  • Suppress Columns: suppress columns without data or with 0
  • NoData/Missing Label: how cells without data are displayed
  • NoAccess Label: how cells which you don’t have access are displayed
• Advanced Options
  • Shred Connections URL: enter Smart View URL for an application or environment you want to access
  • Number of Undo Actions: how many recent steps Smart View will remember

    • 20 steps can slow down Smart View

  • Disable Smart View in Outlook: prevent Outlook from loading Smart View
    • tend to make Outlook load slowly
• Formatting Options
  • Use Thousands Separator: data cells have commas to separate thousands
  • Use Cell Styles: use default PBCSS cell colors
    • Dimensions are blue, data is gray or yellow
  • Use Excel Formatting: retain custom formatting on a report after refreshing
  • Decimal Places: how many decimal places to show on data cells (0-8)

Data Retrieval

• Drilling into Dimensions
  • Planning Ad Hoc > Zoom In
    • Next Level: double click, and the default Zoom In
    • All Levels: expand and open all possible levels; use with caution with large datasets
    • Bottom Level: only show the Level-0; helpful to see details
    • Same Level: similar to Sibling Levels
    • Same Generation: different from Level which is calculated bottom-up, generation is calculated up-down
  • Planning Ad Hoc > Zoom Out
• Pivot
  • Pivot from rows to columns (and vice versa)
    • Planning Ad Hoc > Pivot
    • Hold right-click and drag
  • Pivot dimensions from the analysis to POV
    • Planning Ad Hoc > Pivot’s arrow > Pivot To POV
• Filtering
  • Planning Ad Hoc > Keep Only/Remove Only
  • Member Selection: shows the entire dimension hierarchy
  • Free-form Typing: if familiar with the members in dimension, can directly type in Excel
• Other Functions
  • under Planning Ad Hoc
    • Preserve Format: keep Excel cell formatting upon zooming into a member or dimension
    • Change Alias: Transition between viewing Member Names (None) and Aliases (Default)
      • It is an all-or-none option: cannot just have some be names and some be aliases
    • Cascade: create copies of the current ad-hoc analysis for other members of a dimension in POV
    • Insert Attributes: add attribute dimensions to the analysis
    • Refresh: update the data to reflect changes to the ad-hoc analysis outline or display the most recent values in the database
    • POV: toggles the POV between a dialog box and the first row of the ad-hoc analysis
  • under Smart View
    • Panel: brings up the Smart View Panel with Shared, Private, and Recent connections
    • Connections: view active and recent connections
    • Undo/Redo: for Smart View conditions only; ctrl+Z only works for Excel changes
    • Copy/Paste: copy/paste data points from Word, PPT, or Outlook
    • Refresh: update the data to reflect changes to the ad-hoc analysis outline or display the most recent values in the database
    • Options: set preferences for members, data, formatting, and advanced settings
    • Help: open Oracle Smart View User Guide; view details on the current Smart View version
    • Sheet Info: View info about the current worksheet, including connection details (cube, and last connection time)

Data Analysis

• Calculate Variances
  • For Revenue: \(Variance = Actual - Budget\)
  • For Expense: \(Variance = Budget - Actual\)
  • \(Variance% = Variance/Budget\)
• Remember to dig into it a little more: check different periods, different cost centers, different entity
  • Focus on FIN cube to get all financial data, use other cubes for more data granularity

Data Forms

• Retrieve Forms: Smart View > Panel > Library
• It’s NOT recommended to use Smart View to submit data
  • more likely to change data in the wrong intersection without set POV
  • business rules cannot be automatically run in Smart View

Dimensions

• Smart View > Panel > Dimensions
  • can change/add dimensions here

Reports

• When create reports in Smart View, remember to make sure it can be Refreshed
  • Smart View can read everything on the sheet to retrieve data
  • For titles that might include dimension members, insert text box to avoid system confusion
  • For headers, can use _Name to avoid confusion with Smart View
  • Use hide rows or group data, to hide the dimensions
  • May use white text if needed

---

Section III: In Practice - Implementation Timeline

• General Process
  • Design Session -> Build -> User Testing -> Deploy & Train
  • Not necessarily follows this linearly, may go back and forth

Design Session

• ~ 2-4 days
• Activities + Client Inputs = Result
  • Activities
    • Review budget/forecast process with client
    • Metadata and data requirements
      • Metadata: ask about Dimensionality
      • Data: how many prior years needed; what are some statistical accounts we need
    • Calculation requirements
      • Driver-based planning: could be just revenue = unit price * quantity
    • Reporting requirements
    • Security requirements
  • Client Inputs
    • Provide guidance on budget/forecast process
    • Provide chart of accounts
    • Provide existing budget and forecast templates
  • Result
    • Application dimensionality
    • Data form outlines
    • List of business rules to be created
    • Plan for ERP connection and load Actuals

Build

• ~10 weeks
• Activities
  • Create cubes
  • Build application dimensionality
  • Create data forms
  • Write business rules
  • Build integrations
  • Load and validate data
  • Create reports
• Client Inputs
  • Feedback and guidance on data forms, business rules, reports, etc.
  • Perform final data validation for any integrations
• Results
  • Working planning application that meets client specifications

User Testing

• ~1 week
• May also be called as UAT, User Acceptance Testing
• Activities
  • Create test scripts and procedures for end users
  • Provide support on issues and questions
  • Modify and update artifacts that need fixes
• Client Inputs
  • Perform end-to-end system validation
  • Walk through test scripts
  • Perform a condensed mock-budget and mock-forecast
  • Final validation and approval of application
• Result
  • System validation
  • User acceptance and approval Client sign-off for go-live

Deploy & Train

• ~1 week
• Train-to-Train process: we would train 8-10 users and provide them with materials so that they could train their own teams
• Will continue providing support afterwards
• Activities
  • Deliver Admin guide to client administrators
  • Provide end-user and administrator training
  • Deliver training materials to clients
  • Migrate application from TEST to PROD environment
• Client Inputs
  • Attend training sessions
  • Study Admin Guide and training materials
• Result
  • Live planning application
  • Client has full ownership of application