Dimension Design

Design Dimensions to Create the Application Structure

Add accounts, entities, and other dimensions to support your business process.

Dimensions categorize data values. Planning includes these dimensions: Account, Entity, Scenario, Version, Period, and Years. If you plan in multiple currencies, your application also has a Currency dimension.

You can use the Custom dimension to define your own values, such as Product, Customer, or Market. You can have up to 32 total dimensions. However, the best practice recommendation is to include fewer than 12. You can add dimensions using a load file, or you can build them in Oracle Smart View for Office.

Videos

Your Goal	Watch This Video
Learn how to export and import data in the application.	Exporting and Importing Data in Oracle Planning and Budgeting Cloud
Learn how to load dimensions using a file.	Importing Metadata in Oracle Planning and Budgeting Cloud

Follow this Process to Identify Dimensions

Use this process to identify which dimension to include in the application.

Identify unique planning processes based on requirements.
Example: Marketing Planning, Sales Planning, Overhead Planning, Capital Planning, Cash flow Planning, Workforce Planning
Identify dimensions for each planning process.
Example: Product, Market, Channel, Product Segment, Customer Segment
Define how dimensions are related to each other.
Example: Product has a many-to-many relationship with Market. Product Segment and Product have a one-to-many relationship. Labor Resources and Material Resources have no relationship.
Separate dimensions into buckets for planning and reporting.
Example: Product, Market, and Channel are Planning dimensions, while Product Segment and Customer Segment are reporting dimensions.
Map planning processes to Planning Modules.
Example: Configure Marketing Planning using Projects or Financials, and configure Overhead Planning and Cash flow Planning using Financials and custom cubes.

Consider Common Use Cases for Dimensions

Review these common use cases for dimensions, and understand the guidelines for how to address them.

Standard (mandatory) dimensions
Most of the high level and common Financial Planning use cases can be addressed with standard dimensions, which can also be renamed.
Custom or optional dimensions
Extend dimensionality with custom or optional dimensions that can be added (enabled) or renamed per your requirements.
Multiple hierarchies in a dimension
Combine two or more unrelated dimensions into a single dimension to avoid inter-dimensional irrelevance.
Alternate Hierarchies for Planning or Reporting
Use alternate hierarchies when the same members can be grouped under different parents for top-down allocation or reporting purpose.
Attribute dimensions for reporting
Attribute dimensions are useful to meet reporting requirements if they are related to one dimension and the relationship between the two dimension does not change over time.
Smart Lists and ASO dimensions for reporting
Using Smart Lists and ASO dimensions for reporting is useful when the relationship between the reporting dimension and other dimensions changes with time.
Smart Lists and multi-cube BSO dimensions for planning
This strategy is useful when the planning dimension is not a primary dimension for a planning process and there is a need to break it into sub-processes.

This sample worksheet shows an example of how to plan dimensions, including indentifying dimensions and listing their use cases.

Example worksheet for designing dimensions

Review Example Design Strategies

Review these examples to understand additional design strategies for dimensions.

Using attribute dimensions for reporting

Using attribute dimensions for reporting can be useful to meet reporting requirements. The attribute is related to one dimension, and the relationship between the two dimension doesn’t change with time.

Example:

Define an attribute called Program on the Project dimension.
You can then create a hierarchy of members in the Program dimension. Top level members in the Program dimension will be automatically aggregated.
Associate each Project member to a leaf level member in the Program dimension in an Add Project Groovy rule.
This allows you to filter projects by program.
Reporting forms can show program level Expenses and Revenue.

Note that using too many attribute dimensions on a form or report may degrade performance. This should be included in the performance and load testing aspects of an implementation.

Using Smart Lists and ASO dimensions for reporting

This strategy is useful when the relationship between the reporting dimension and other dimensions changes with time.

Example:

Add a Skillset dimension in ASO and map the Skillset Smart List to the Skillset dimension.
Create a data map for moving data from BSO to the ASO cube.
The data map can be run as a batch process, or you can implement Smart Push in forms or Groovy rules.
You can use reporting forms on the ASO cube to showing the labor requirement by Skillset across projects.

Using Smart Lists and multi-cube BSO design

This strategy is useful when the planning dimension is not a primary dimension for a planning process and there is a need to break it into sub-processes.

Example:

Employee and Job are dimensions in Workforce and Smart Lists in Project Planning.
Project Planning uses line items to plan labor expenses at the Job and Employee level.

Example 2 of Smart Lists and multi-cube design

Multiple hierarchies in a dimension

You can use multiple hierarchies in a dimension. Combine two or more unrelated dimensions into a single dimension to avoid inter-dimensional irrelevance.

Example:

Jobs, Equipment, and Material are unrelated in Projects, so they are combined into a single Resource Class dimension.
Profit Centers and Cost Centers are unrelated in Financials, so they can be combined into a single Entity dimension.

Alternate hierarchies for planning and reporting

Use alternate hierarchies when the same members can be grouped under different parents for top-down allocation or reporting purposes.

Example: Create alternate rollups in the Product dimension for planning and reporting by the Brand and Product categories.

Example of alternate hierarchies for reporting

Know the Top Ten Best Practices for Dimensions

Follow these important best practices when designing dimensions.

Dimension order should follow a modified hour glass format.
With this format, the most dense dimension is the first dimension, followed by less dense dimensions. This should be followed by sparse dimensions, with aggregating sparse dimensions before non-aggregating sparse dimensions. Within sparse dimensions, you should have the most dense sparse dimensions before the least dense sparse dimensions.

With hybrid BSO, the order is the same, except that you should have non-dynamic sparse dimensions before dynamic sparse dimensions.

Learn more about dense and sparse dimensions in this tutorial: Managing Dimensions in Cloud EPM.
Large block size has a major impact on performance.
Block size is determined by the number of dense dimensions and the stored members in those dense dimensions. Optimal block size is between 8 KB to 500 KB. Reduce the number of dense dimensions to a maximum of 3. Level 1 and above should be label only or dynamic calc for dense dimensions.
Text, Smart List, Date, and Stored Percentage type accounts should be set to Never for the consolidation property.
Aggregating these values, unless the accounts are explicitly excluded in an aggregation script, will create useless data and make the cube unnecessarily large, which likely will degrade performance.
All generation two members should be set to Ignore.
You cannot include these root members in your forms because you cannot define security for those members. So there is no point in aggregating the generation two members to the root. This will also increase the number of blocks in your application.
Long or flat dimensions will lead to an issue with the performance of aggregation.
If there are more than 200 members under a parent member, add intermediate parents.
Enabling dimension members for multiple cubes will create dynamic X-Refs and lead to performance issues.
Use the HSP_NOLINK UDA to avoid creating dynamic X-Refs. Use data maps or Smart Push for moving data between cubes.
For simple calculations, leverage outline math instead of writing member formulas.
An example of a simple calculation is Account C = Account A – Account B.
When possible, avoid single child parent members.
Single children parent members lead to implied shares or duplicate blocks and data on disk if the parent member is made Never Share.
Aggregate large dimensions in ASO instead of BSO whenever possible.
This improves performance for example during cube refresh and maintenance time.
Store historical data beyond two years in ASO instead of BSO.
If you have 5 or 10 past years of historical data, not all of the data is needed for calculations. If needed, you can have a couple of years of historical data for calculations in your BSO cube, and you can move other historical data to the ASO cube. For optimum performance, it is a best practice to keep the BSO cube light and ensure that it is focused on the calculations for data entry.

Plan the Entity Dimension

The Entity dimension represents your organizational structure, such as Cost Centers, Departments, Business Units, Divisions, and so on.

You can group Cost Centers by creating rollup members, called parents, to reflect how your organization is viewed. For example, rollups can be by business unit, division, or other functional structure. As an example, you could create Cost Centers that roll up to Business Units that roll up to Divisions.

You can also create multiple reporting structures. For example, an alternate structure could be created to support regional reporting. If you plan in multiple currencies, set the base currency of each entity.

The Entity dimension is one of the primary dimensions used for the budgeting process. Together with the Scenario and Version dimensions, the Entity dimension is used to define an approval unit, a discrete component that can be promoted or demoted for approval or review by a user’s peers. Members of all dimensions outside the approval unit are promoted and demoted along with the approval unit itself. For example, all twelve months are promoted together when an approval unit is promoted. Individual months can't be promoted independently.

A secondary dimension is also supported, as described in Approvals Design Considerations. In addition, you can include phased approvals, as described in Managing Approval Phases.

After each dimension is loaded or updated, it is a best practice to refresh the application.

Plan the Account Dimension

The Account dimension is the place for your chart of accounts. It should include the members to which you plan or forecast. It doesn't necessarily include every account in your chart.

For example, your Account dimension could include accounts for Income Statement, Balance Sheet, and Cash Flow. Or, it could include accounts for KPIs and Ratios. In some cases, your accounts may have sub accounts, but this is not typical.

The Account dimension includes financial intelligence. The following account types are supported:

Expense: Cost of doing business
Revenue: Source of income
Asset: Company resources
Liability and Equity: Residual interest or obligation to creditors
Saved assumption: Centralized planning assumptions ensuring consistency across the application

The account type settings are used to report Quarterly and Year Total values and for variance analysis.

Planning uses a hierarchical structure to create Account grouping subtotals and totals. Each account group is assigned a consolidation operator that determines how it rolls up to its parent.

Example:

Net Income = Total Revenues - Total Expenses

In this example, the consolidation operator for Total Revenues is Addition, and the consolidation operator for Total Expenses is Minus. Intelligence determines, in part, the sign in which data is loaded, entered, or calculated.

The Account dimension can be populated either by loading data or using Smart View. To load data from a file, the file format must meet specific requirements.

After each dimension is loaded or updated, it's a best practice to refresh the application.

Best practices:

Upper level members should be set to Dynamic Calc or Label Only.
For member formulas used to calculate Ratios and other types of KPIs or percentages, set them to Dynamic Calc, Two Pass. The Two Pass setting properly calculates Percentages at upper levels.

Plan the Version Dimension

You can use versions to preserve different iterations of the planning process. Versions are also useful for controlling data access to Read or Write.

These two types of versions are available:

Standard Target: Input data can be entered to upper levels.
Standard Bottom Up: Input data can be entered to level 0 only.

Approvals and workflow functionality can be enabled only for Bottom Up versions.

As a best practice, these versions are recommended:

Working: Where users perform their tasks, including reviewing Actual Results and developing Plan and Forecast.
1st Pass: If you want to maintain multiple iterations of your Plan, you can preserve a pass of it in this version. You can create other members if you require more than one saved iteration. You can leverage the Copy Data functionality to move data to this version. Copy data copies data and textual input.
What If: Provides a placeholder where users can change assumptions and analyze the outcome.

After each dimension is loaded or updated in the build process, it's a best practice to refresh the application.

Plan the Currency Dimension

If you enabled multiple currencies for your application, you can add the currencies you use to plan and report.

You can then define exchange rates by scenario and year to be used in conversions. A calculation script is created that enables you to perform currency conversion. To enter exchange rates, follow the process in Specifying Exchange Rates in Administering Planning.

Best practices:

Limit the number of reporting currencies. Typically, customers have only one.
Enter exchange rates for each valid scenario and year combination.
From this point on, currency conversion can be calculated by running the Calculate Currencies business rule that is associated by default with each form.

Run the currency conversion calc script prior to:

Reviewing any updated local data in reporting currencies
Running certain calculations that may be dependent on reporting currency data

Plan Exchange Rates

Each application has a default currency that you specify when creating the application. When you set up exchange rate tables, you enter exchange rates from all source currencies to the default. Triangulation is used to convert to all other Reporting currencies.

Exchange rates are set by Scenario by year for Average and Ending Rates.

Plan the Period Dimension

Use the Period dimension to establish the calendar’s range within a given year, for example, by month.

Best practices:

Use substitution variables for this dimension to support reporting and calculations. Potential substitution variables are: CurrMo, CurrQtr, and PriorMo. These variables must be updated on a monthly basis.
To use time period calculations such as Year to Date (Y-T-D) or Quarter to Date, select the dynamic time series icon in the Period dimension. You can then select which time period calculations you need to support your process.
Summary time periods such as quarter totals and a year total should be set to dynamic calculate to reduce calculation time.
After each dimension is loaded or updated, refresh the application.

Plan Years and Substitution Variables

Years are incorporated into the application in many places, including forms, calculations, reports, and Smart View. Because you'll use the application for many years into the future, the best practice to referencing this dimension is by using a substitution variable.

Substitution variables act as global placeholders for information that changes regularly. The variable and value correspond to the year, and the value can be changed at any time.

The value of the substitution variable is displayed on forms and reports as a placeholder. This reduces maintenance for the application.

As a best practice, create substitution variables for each year that is included in your process. For example:

CurrY, Current Year
NextYr, Budget (Plan) Year
PriorYr, Prior Year

Design Custom Dimensions

You can use a custom dimension to further categorize your data. For example, custom dimensions might include Product or Markets.

Keep in mind that access permissions can't be granted at the dimension level, also called generation one. For example, access permissions can't be assigned directly to the Product member for all descendants. If you enable security for your custom dimension, it is recommended that you design generation two for all custom dimensions to which security will be applied with security access assignments in mind.

After each dimension is loaded or updated, it is a best practice to refresh the application.

Additional Best Practices

Complete these tasks after you add or update dimensions.

Refresh the application.
You must refresh the application whenever you change the application structure.

Changes made to the application are not reflected to users performing data entry and approvals tasks until the application is refreshed.

For example, when you modify properties of an Entity member or add a Scenario, these changes are reflected to users after you refresh the application.
Load historical data.
After you load all of your structures, such as accounts and entities, you can load historical data. This can include data from prior year actual results and current year plan and budget.

Loading historical data provides users a way to analyze results, review trends, and make meaningful comparisons.

This also helps verify the structures that you have built into your application. For example, you can verify that data ties to previously created reports. If the data doesn't reconcile, you must verify if this is caused by a data issue or if there is an issue with the structures.

Create an aggregation rule to see consolidated data in your application.
Plan valid intersections.
Valid intersections let Service Administrators define rules called valid intersection rules that filter dimensional intersections for users when they enter data or select runtime prompts. For example, you can specify that certain programs are valid only for specific departments. Leverage valid intersections to control data entry only for valid intersections.

During form design, keep these points in mind for valid intersections.
- If dimensions with valid intersections are on the Page, the user will only be presented with valid combinations in the member selector.
- If dimensions with valid intersections are on the column or row, the form designer can completely suppress invalid intersections. When the suppression option is not selected, invalid intersections are set to read only.