Cube Properties

You can view and edit Cube Properties in Application>Cube>Cubes. The Cube Properties tab allows you to assign initial key settings that impact how a Cube is secured, calculated, translated, and interacts with a Workflow.

General

Inside the Cube Properties tab is a section called General.

Name: Input field to name a Cube and limited to 100 characters.

NOTE: When determining a naming convention for a Cube, it cannot be changed once it has been created. Consideration should be used when using a company name as the Cube name since company names can change either intentionally or through merger.

The Cube Name property can be accessed through the Finance Engine using a Finance Business Rule:

  • Dim objCubeInfo As CubeInfo = api.Cubes.GetCubeInfo(name)

  • Dim objCube As Cube = objCubeInfo.Cube

  • Dim sValue As String = objCube.Name

Description: Input field to provide a description for the Cube and limited to 200 characters. The Cube Description can be changed at any time being created.

The Cube Description property can be accessed through the Finance Engine using a Finance Business Rule:

  • Dim objCubeInfo As CubeInfo = api.Cubes.GetCubeInfo(name)

  • Dim objCube As Cube = objCubeInfo.Cube

  • Dim sValue As String = objCube.Description

Cube Type:This optional setting creates tags to group Cubes with similar characteristics. This is used to have different settings for Default and Constraint settings that apply to specific Dimensions and vary by Cube Type, such as Entity. The Cube Type names are random and do not have functional differences but represent various kinds of Cubes that may be created. Below are all Cube Types properties:

  • Standard: Default option for Cube Type when a new Cube is created.

  • Tax: This option is considered for a Cube that is focused on a tax process. A new Cube can be created for a tax process while using dimensions assigned to a Cube with a standard cube type.

  • Treasury: This option is considered for a Cube that is focused on a treasury process. A new Cube can be created for a treasury process while using dimensions assigned to a Cube with a standard cube type.

     

    NOTE: When using the same dimensions across Cubes with different cube types, the Constraints can be changed to allow different dimension members from existing dimension to be the constraint member by Cube Type. This applies for Tax or Treasury properties.

  • Supplemental: This option is considered for additional data for the Cube Type. Supplemental data can be part of a process captured during an Actual, Budget, or Planning process. Other factors can determine whether a new Cube is needed, and it is not necessarily driven by supplemental data, but it may be included anyway. In many cases, supplemental data is not specifically assigned to a Cube with a 'Supplemental' Cube Type and is not always a strict rule. Supplemental is an option used to group Cubes together that use the same Dimension member constraints. The characteristics of the supplemental data and how it should be constrained dictate whether 'Supplemental' is a viable option to use for Cube Type on the Cube.

  • What If: This option can be considered when forecasting different hypothetical models for a business. Cubes can be created using existing Dimensionality. However, for a What If Cube, the constraints, or defaults may differ than a Cube processing an Actual Scenario.

  • Cube Type 1…Cube Type 8: This option can be considered as grouping to align Cubes together with common characteristics compared to other Cubes using similar Dimensionality.

     

The Cube Type property can be accessed through the Finance Engine using a Finance Business Rule:

  • Dim objCubeInfo As CubeInfo = api.Cubes.GetCubeInfo(name)

  • Dim objCube As Cube = objCubeInfo.Cube

  • Dim nValue As Integer = objCube.CubeTypeId

The Cube Type is stored in a column within the dbo.Cube table as an integer (Int). Each Cube Type is assigned a unique integer value:

  • Standard = 0

  • Tax = 1

  • Treasury = 2

  • Supplemental = 3

  • What If = 4

  • Cube Type 1 = 11

  • Cube Type 2 = 12

  • Cube Type 3 = 13

  • Cube Type 4 = 14

  • Cube Type 5 = 15

  • Cube Type 6 = 16

  • Cube Type 7 = 17

  • Cube Type 8 = 18

Time Dimension Profile: This property provides a drop-down list of available Time Profiles. The Time Profile assigned to the Cube aligns accounting calendars to the Time Periods for reporting and processing for the Cube.

See Time Profiles.

Security

This section controls who maintains and has access to the Cube. It also controls data access to consolidation members at relationship levels.

Access Group: The security group can view the object and its related contents. The default setting is Everyone. The order of security to gain access to data starts from gaining access to an application than to a cube. As there are more layers of security to gain access to data, the default setting is Everyone and additional security is assigned at lower layers. You will determine if a security group should be assigned to the Access group to control access. See Security.

Maintenance Group: The security group can view the object, create new objects in groups, edit, and delete.

Use Parent Security for Relationship Consolidation: This property controls the security model for the read and write relationship for the Consolidation Dimension members. This works with the security rights to the immediate Parent-level Entity. This property defaults to False. The default behavior is that anyone who has access to Parent-level Entities will also get access to all the Consolidation Dimension members. When this property setting is changed to True, you can have access to Local and Translated Consolidation Dimension members but no longer have access to Parent-Level Relationship Consolidation Dimension members such as Share, Elimination, OwnerPostAdj, and Top. This is determined on a Cube-by-Cube basis.

Workflow

This section controls the availability and management of Workflow Cube Root structures for Workflow Profiles for a Cube.

Is Top Level Cube for Workflow: When a new Cube is created, this property setting defaults to False. When set to False, the Cube is unable to create and maintain a Workflow structure. When set to True, the Cube can create and maintain a Workflow structure. This is determined on a Cube-by-Cube basis.

During metadata requirements and design, the number of Cubes needed are evaluated. If using Extensible Dimensionality with multiple Linked Cubes, 1 Cube will have this setting set to True, while the remaining Cubes will have this setting set to False. Only 1 Cube will be responsible for controlling and maintaining Workflow Profiles and structures through a Cube Root Profile for Workflow Profiles. See Cube Design Options and Characteristics.

Suffix for Varying Workflow by Scenario Type: This property is used to group Scenarios by Scenario Types to make available to users in the Workflow. This can be determined on a Cube-by-Cube basis. This is active only for Cubes with Top Level Cube for Workflow set to True. There is one setting for each of the Scenario Types. This property can act as a layer of security by organizing and grouping Scenarios with certain Scenario Types to the appropriate user groups who are responsible for certain processes in an organization.

By default, each Scenario Type is blank when a Cube is created. If the Is Top Level Cube for Workflow property setting is set to True, then a Workflow Profile Cube Root can be created for a Cube. When the Workflow Profile Cube Root is created, any Workflow Profile that is created will have all the Scenario Types available to load data to by default. This means the same Workflow Profiles and Workflow Profile hierarchies are used for all the Scenario Types.

While technically feasible, this design may not offer optimal efficiency for organizing Workflow Profiles and Workflow Profile hierarchy structures. For example, different users are responsible for different processes within an organization. Accounting/Finance is responsible for Financial Close & Consolidation, while Financial Planning and Analysis (FP&A) is responsible for Budget, Planning, and Forecast. To support this example of organizational responsibility, Suffix for Varying Workflow by Scenario Type configuration may display like this:

By configuring the Suffix for Varying Workflow by Scenario Type for Budget, Forecast, and Plan with PLAN, the Scenario Types are now grouped together to share the same Cube Root Workflow Profile and Workflow Profile hierarchy structure. Any Scenario assigned a Scenario Type of Budget, Forecast, or Plan can load data through the PLAN Cube Root Workflow Profiles.

Based on the configuration on the Cube, the Cube Root Workflow Profile Names would show accordingly when creating a Cube Root:

After creating the Cube Root for CompanyA_PLAN, the Scenario Types available for the PLAN Workflow Profiles would look like this:

After creating the Cube Root for CompanyA_ACT, the Scenario Types available for the ACT Workflow Profiles would look like this:

At the time of designing and configuring the Cube, it is not necessary to know what to name the suffix for each Scenario Type.

IMPORTANT: Once a Scenario is defined with a Scenario Type and the Scenario contains data, a Cube Root Workflow Profile cannot be created for that Scenario Type and the property setting field for the Scenario Type will not be considered for a new Cube Root. See Workflow for more information.

Example: For example, in the first phase of a project, which may be Financial Close and Consolidation, it is determined that a Scenario called 'ACTUAL' will be assigned the 'Actual' Scenario Type. The 'Actual' Scenario Type field in 'Suffix for Varying Workflow by Scenario Type' is set to 'ACT', but all the other Scenario Types are left blank. Once the Workflow design and build are completed, data is loaded into the 'ACTUAL' Scenario.

During the project, a requirement may necessitate the seeding of Actual data into a 'Budget' Scenario assigned the 'Budget' Scenario Type. Once data is seeded into the 'Budget' Scenario in the Cube, the 'Budget' Scenario Type can no longer have a suffix or be renamed in the 'Suffix for Varying Workflow by Scenario Type'. A new Cube Root will not be created for the 'Budget' Scenario Type if the Budget process becomes more developed in a later phase.

Data and all processes related to the 'Budget' Scenario must be deleted to make this change. A 'Reset Scenario Data Management' step would need to run to reset the 'Budget' Scenario, and then a suffix can be added to the 'Budget' Scenario Type. See Data Management for more information.

If data has not been loaded to a Scenario/Scenario Type combination and the Scenario Type is blank, then a suffix name can be added to the Scenario Type and a new Cube Root can be created. This is independent of any other Scenario/Scenario Type combination that may have data already loaded.

Calculation

This section guides how to process, calculate, and translate data under certain conditions.

Consolidation Algorithm Type

When a Consolidation runs in OneStream, data is moved up to the Entity and Consolidation Dimensions. The Consolidation Algorithm Type on the Cube specifies how the Share and Elimination Members will be treated.

Standard (Calc-on-the-fly Share and Hierarchy Elimination): Standard (Calc-On-The-Fly Share and Hierarchy Elimination) is the most used Consolidation Algorithm Type. This Cube setting calculates an Entity’s Share amount dynamically (the value does not get stored in the application database Data Record tables). Share is a Consolidation Dimension Member defined for a specific Parent/Child relationship and is calculated as an Entity’s (translated balances + owner pre-adj journals * percent consolidation). Eliminations under Standard are calculated using OneStream’s built-in algorithms.

Stored Share (Stored Share and Hierarchy Elimination): The Stored Share Consolidation Algorithm Type stores the amounts for Share rather than calculating them dynamically as in Standard. However, the eliminations under Stored Share are still calculated as in Standard, using OneStream’s built-in algorithms. This Consolidation Algorithm Type can be used when you need different logic in calculating Share than in Standard. For example, if you had a minority interest calculation, where the Share contribution cannot be driven from the percent Consolidation.

When the Stored Share Consolidation Algorithm Type is used, the rules to calculate the value need to be written under the Finance Function Type ConsolidateShare within a Finance Business Rule.

Org-By-Period Elimination (Calc-on-the-fly Share and Org-By-Period Elimination): The Org-By-Period Elimination Algorithm Type uses the calc-on-the-fly Share – as in Standard – but has unique elimination considerations. When determining if the data cell’s IC Member is a descendant of the Entity being consolidated, this Consolidation Algorithm Type considers the position of the Entity in the hierarchy and checks the Percent Consolidation for every relationship down the hierarchy. If Percent Consolidation is zero for the relationship, the IC Member is determined not to be a descendant of the Entity.

In comparison, the standard elimination (hierarchy elimination) only considers the position of the Member in the Entity Dimension hierarchy. Standard elimination is the default approach and does not consider Percent Consolidation.

Stored Share And Org-By-Period Elimination: The Stored Share and Org-By-Period Elimination Consolidation Algorithm Type is a combination of the two settings which have been explained above.

Custom: The Custom Consolidation Algorithm Type allows for the ability to calculate Share and Elimination data intersections using logic within a Finance Business Rule. This is often used when you have custom eliminations that are different from OneStream’s built-in algorithm and org-by-period elimination logic. This could be due to having custom eliminations within a User-Defined Dimension or wanting to write eliminations to unique and specific data intersections.

There are two Finance Function Types within a Finance Business Rule in which the logic is written: FinanceFunctionType.ConsolidateShare and FinanceFunctionType.ConsolidateElimination.

Performance Considerations Using Consolidation Algorithm Types: To determine what Consolidation Algorithm Type settings are needed for the application, it is also important to be aware of performance implications. Under the Standard Consolidation Algorithm Type, OneStream does not store Share because it is dynamically calculated. When turning on any of the above Consolidation Algorithm Types which store Share, there will be performance implications.

In storing Share, the Consolidation Engine must perform the custom logic written in the Finance Business Rule and write the data records to the Data Record tables in the database. The size of each Data Unit will become larger because of the additional data records stored in the Data Record tables in the database.

Elimination data records are stored in the Data Record tables in the database under all Consolidation Algorithm Types. When turning on custom eliminations, performance implications will depend on the rule design and the construction of the Entity hierarchy. Additional data records may be written to the Data Record tables in the database and the engine may spend more time in the custom elimination logic than OneStream’s built-in algorithms.

Translation Algorithm Types

When a Consolidation runs in OneStream, data is moved up to the Entity and Consolidation Dimensions. The Translation Algorithm Type on the Cube specifies how data records during the Translated Consolidation Members will be treated, such as:

Standard: Standard is the most used Translation Algorithm Type. This Cube setting takes an Entity’s local currency values and translates them based on the FX Rate Types such as, Average, Closing, and more. Also, FX Rule Types, such as Periodic, Direct, and more, are assigned to the Scenario.

Standard Using Business Rules for FX Rates: Standard Using Business Rules for FX Rates is like Standard but allows the ability to use a Business Rule to specify translation rates for any given intersection. Any intersections not specified in the Finance Business Rule will translate based on the Standard translation logic. This is commonly used during the translation of Forecast, Constant Currency, and other such Scenarios.

It is also used when the rate needed for translation already exists in the FX Rate table but in another Rate Type/Time, or the rate needs to be determined dynamically. For example, consider needing to translate the Actual Scenario at the current year’s Budget rates. In this case, all the Actual data needs to be translated based on the rates which already exist in the FX Rate table. By using a Business Rule, we can dynamically determine what year needs to translate is based on, without having to copy rates that have already been entered to another Rate Type. This reduces Administrator maintenance by eliminating the need to copy or enter duplicate rates within the system.

Custom: Custom translation logic is rarely used but allows for the ability to calculate the translated values for all intersections within a Finance Business Rule. The system is flexible to modify to custom unique methods that are not common, or out of the box.

Business Rules

Business logic can be introduced at the Cube level. This can be accomplished by attaching a Finance Business Rule to the Cube in the Business Rules section. The same Finance Business Rule logic can be shared among Cubes. Eight Business Rules can be defined and processed in a logical order with member formulas. These run on Calculate, Translate, or Consolidate processes, see Consolidation.

BusinessRule1…BusinessRule8: This property uses a drop-down option for attaching a Finance Business Rule. The Business Rule must be created as a Finance Business Rule for the Business Rule to appear as a selection option.

FX Rates

This section provides the Cube logic on how to process FX Rates for specific account types and define the Cube with a default currency to leverage for reporting and during the Translate process.

Default Currency: This property is the default reporting currency for the Cube. This is used for FX rate triangulation if the Cube currency is the common currency and Intercompany Matching reporting currency.

Rate Type for Revenues and Expenses & Rate Type for Assets and Liabilities: The settings for the properties can be configured at the Cube level or overridden at the Scenario level. These properties have pre-defined selection options out of the box. The pre-defined selection options for these properties are Average Rate, Opening Rate, Closing Rate, and Historical Rate. Although a pre-defined selection list of options is available, new FX Rate Types can be created in Cube > FX Rates which will instantly make the new FX Rate Type available for selection. See FX Rates for more information.

Rule Type for Revenues and Expenses & Rule Type for Assets and Liabilities: The settings for the properties can be configured at the Cube level or overridden at the Scenario level. These properties have pre-defined selection options out of the box. The pre-defined selection options for these properties are Direct and Periodic. As a default when a Cube is created, the Rule Type for Revenues and Expenses is set to Periodic while the Rule Type for Assets and Liabilities is set to Direct.

  • Direct: Calculate direct with current value and current rate. Current Period’s Translated Value = Current Period’s Local Value * Current Period’s FX Rate.

  • Periodic: Calculate periodic value translation method. This method considers the translation rates for prior time periods and calculates the form of average. Current Period’s Translated Value = Prior Period’s Translated Value + [(Current Period’s Local Value – Prior Period’s Local Value) * (Current Period’s FX Rate)].