Introduction
While building an application, you have several options to optimize performance. In this article, we will cover the largest areas where you can improve Kepion's performance.
Infrastructure
Hardware specs
Ensure your machine meets or exceeds Kepion's hardware specifications. We highly recommend solid-state drives for SQL and SSAS servers.
Multi-Server setup
Separating the three core components into their machines will often improve performance as each component will have more resources.
Model
Configuration
- Use the minimum number of dimensions required in each model.
- Utilize member lists to break up large dimensions into just the subset of members required for the model.
Separate models
- Break larger models into several smaller ones.
- Use technical models to hold data for validations or mapping.
- Create separate models just for reporting.
Partition
When tuning a partition, you must first determine its storage mode. The storage mode controls how data is stored in the cube when the application is deployed to OLAP. There are two storage modes:
- ROLAP - recommended for Writeback partition
- MOLAP - recommended for static, non-Writeback partitions
MOLAP
The MOLAP storage mode has the Partition Mode and Partition Interval settings to expedite deployment for partitions with over 20 million records.
Note: The model must have the Time dimension to use this feature as the latter allows Kepion to determine your data's start and end date.
- Partition Mode auto-generates additional partitions in OLAP with the data segmented by either months or years.
- Partition Interval determines the time interval between each generated partition, as specified in the Partition Mode.
ROLAP
The ROLAP storage mode has the Rolap Slice field to scope your records with MDX. You need to write an MDX query identifying your desired slice(s) of data, allowing OLAP to scan only what you need. For example, you can enter {[Scenario].[Scenario].[Budget]}. This query calls out Budget data, which Kepion will immediately process.
Attention: If done incorrectly, you may miss processing other data you need as Kepion will bypass the comprehensive partition scan.
Dimensions
Sizing
Select an appropriate size for your dimensions. Although you might not know the exact number of members the dimension will contain, try to make an educated guess.
Dimension Security
The Security attribute allows administrators to limit user access within a dimension. For instance, you can specify a user to have access only to finance and marketing data in a Department dimension.
To optimize performance, disable the Security attribute whenever possible.
Data Rules, SQL, and MDX
Limit MDX
MDX is one of the most common causes of poor performance. As a general rule, if a calculation can be done through data rules or SQL instead, do not use MDX. Also, limit the scope of all MDX calculations as much as possible.
Materialize data rule calculations
Action rules tend to perform better than view rules. Action rules run calculations once, and the results get stored directly in the partition. View Rules are dynamic, and the rule is executed every time the data is refreshed.
Scope action rules with parameters
Another performance benefit of action rules is that the data affected by the rule can be scoped with parameters. This means you have to recalculate only the data that has changed. View rules will recalculate all the data every time.
Minimize the data in a calculation
The less data you're moving around, the faster the calculation. Check for columns that contain only a single value; these can be temporarily removed from calculations typically by scoping action rules with parameters.
Reuse calculated data
Sometimes various calculations require the same data or calculated results. Try not to repeat the calculation step. Instead, store the data in a partition for later use or link to a previous data rule step.
Scope and selection scope
Scope context and selection context can be used as part of the parameters (@Scope, @SelectionScope) for SQL rule execution.
Your SQL rules should have Include form scope and Include form unchecked for performance considerations.
1. In the Modeler, select a SQL Rule under one of your Models.
2. Select Advanced.
3. Select Include form scope and Include form selection if checked. Leave the boxes as is if they are unchecked.
Tip: For additional rule performance analysis details, read the Rule Performance Analysis Guide.
Forms
Default filter members
Always default each member to the lowest member within a hierarchy to boost performance when a form is first opened.
Hide Empty Rows
1. Open a form and go to the General tab.
2. On the far-right of the ribbon, select the arrow next to Settings.
3. Select Hide Empty Rows if the box is unchecked. Leave as is if checked.
Keep form design simple
- Limit the number of dimensions on the rows and columns, along with the scope and size of the form.
- Shrink large forms by adding more filters.
- Disable any features that aren't necessary, or redesign the form, so features are unnecessary (e.g., annotations, drop-downs, cell attributes, increase/decrease colors).
Use multiple forms
Break large forms into multiple smaller forms and place them side-by-side in a dashboard.
Valid relationships
Build validation models and define relationships between dimension members to limit how many the form needs.
Tip: For additional form performance analysis details, read the Form Performance Analysis Guide.