Application designers understand that, as a platform, each application must flexibly respond to growth, evolving business needs, and enhancements in MarketPlace solutions. Review the following design options to best direct the overall design and performance expectations of your application.
When designing an Application, consider these questions to identify the model Cube design best suited to your needs:
-
What is the overall size of the Entity, Account, Flow and User Defined dimensions that estimate the Data Unit size potential? A OneStream Data Unit is comprised of Cube, Entity, Parent, Consolidation, Scenario and Time.
-
What is the size and relationship between dimensions? For example, are the User Defined Dimensions large (thousand members) and are the members closely tied to a single or very few Entities (“sparsely populated”)?
-
Are dimension members more transactional in nature? Transaction members such as Employee ID, SKU and Project Code likely change frequently. While this level of detail may be needed for analysis, it can complicate a financial model. Other modeling techniques could help.
-
Is the data related to the Cube a “specialty” collection, such as Human Resources, Product Profit or Capital Expenditures?
-
Do you fully understand how the data is managed? Determine if the Legal Entity is appropriate for the Entity dimension so you can properly define the ultimate Data Unit Controlling Dimension.
-
Have you collected information how the data is assembled and used? Determine if the Cube data is integrated for different types of data such as Actual and Budget.
Standard Cube Design
Following are considerations for standard cube design.
Guidelines
-
Common design for a standard Statutory Consolidation Cube.
-
Primary metadata driven application.
-
Data Unit approximately 250k data records at higher end.
Performance Profile
-
Consolidation times for 12 periods generally range from minutes to ~2 hours on the higher end, depending on complexity and volume.
-
Performance generally impacted by metadata parent structures, alternate hierarchies, and calculations executing on parent Entity members.
Reporting Guidelines
-
Standard Cube Views with minimum number of rows and columns should perform well, rendering within a few seconds to a minute (depending on requirements).
-
Slow Cube View performance of a reasonable number of non-calculated rows and columns, rendering greater than a minute should flag a Cube View or Application design review.
-
Cube View Performance is strongly dependent on concurrent use, Cube View design and metadata involved.
Standard Large Sparse Cube Design
Following are considerations for standard large sparse cube design.
Design Guidelines
-
Common for a Statutory Consolidation Application. Contain sparse intersections due to collecting transactional members, such as SKU, Part Number or Names, or other large dimensions, such as Product or Project.
-
Data Unit size approximately 750k or more data records. Larger Data Units demand more CPU processing power and time to calculate.
Performance Profile
-
Optimize consolidation times by addressing sparse dimensions. With good extensibility you can significantly reduce the size of the Data Unit consolidating, for faster consolidation.
-
Applications that must be consolidated and contain large sparse dimension members, may have long consolidation times and lower overall performance. Review these applications designs to consider alternate design solutions that include extensibility or other Cube and Model solutions.
-
Data Unit size, number of Data Units, Business Rule best practices and other factors effect consolidation times.
Reporting Guidelines
-
Putting a “top” member such as UD2#Top for a large dimension on a Cube View , means OneStream loops through each child member and aggregates on-the-fly. This can consume large amounts of processing power and extend report processing.
-
It is best to use Sparse Data Suppression settings on Cube Views which are based on Single Data Unit.
-
You may need custom Sparse Cube View Business Rules for “top of the house” queries.
-
Quick Views may be impacted as they do not include Sparse Row Suppression settings.
Hybrid Cube Design
Following are considerations for hybrid cube design.
Guidelines
Hybrid Applications are generally designed to support Analytics and not the Consolidation model. Hybrid cubes:
-
Typically require complex calculations at base level Entities.
-
Predominately present is aggregation having simple needs at parent Entity levels. Parents do not require special calculations or logic.
-
Rely on Dynamic Business Rules attached to a dynamic member such as in UD8. This means parent Entity data is not stored. You can set the Is Consolidated property on these Entities to False.
-
Determine if parent Entity data must be exported to external system so you can understand the impact of Dynamic Business Rules.
-
Usually have a data unit size of up to one million data records at the top parent Entity level.
Performance Profile
-
Performance will vary by application size and structure.
-
Data calculation is not stored at the parent level.
-
“Entity Aggregation on the Fly” consolidation simulation rules are required. You cannot consolidate parent entities. This involves the API.Functions.
GetEntityAggregationDataCell function in a dynamic member. -
You must support the export of calculated data which could involve unique Scenarios or cubes to replicate dynamic calculations as stored values. These processes can be automated using scheduled Data Management jobs.
Reporting Guidelines
-
Apply Sparse Suppression settings on Cube Views.
-
You may need custom Sparse Cube View Business Rules for “top of the house” member queries.
-
Intermediate rules may run as Workflow-based or be driven by Dashboards or Calculation Definition settings.
-
Quick Views may be impacted because they do not include Sparse Row Suppression settings.
