During the application build process, there are several performance tuning options available to consider. This article will attempt to cover the largest areas in Kepion were performance can be optimized. While each of the ideas in this article
Ensure you meet or exceed the hardware specifications for Kepion. Solid-state drives for SQL and SSAS servers are highly recommended.
Separating the three core components onto their own machines will often increase performance, as each component will have more resources to itself.
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.
Break larger models into several smaller ones. Use technical models to hold data for validations or mapping. Create separate Models just for reporting.
When performance 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. It is suggested to change the Storage Mode to MOLAP for a static non-Writeback partition.
You can also configure a partition with the following performance tuning properties:
Rolap Slice: For Rolap storage mode, you can configure the OLAP slice property of a partition to indicate the type of data stored in this partition. Setting this property can boost OLAP query performance.
Partition Mode: The Partition Mode can be specified if model uses a Time Dimension. The Partition Mode can be set to the following properties:
- By Months: Auto generate additional partitions in OLAP with the data segmented by months
- By Years: Auto generate additional partitions on OLAP with the data segmented by years
Partition Interval: This setting determines the time interval between each generated partition as specified in the Partition Mode.
Through configuring the above settings, Kepion will generate SSAS partitions automatically from your single partition into multiple partitions, and split the data by the specified groups. Kepion will also use the Time Dimension defined on the Model to determine the start and end range for the time.
This technique can help boost processing time for very large partitions. However, please ensure that the partition is set to MOLAP, as this technique is not helpful for ROLAP partitions. The partition in SQL will not be affected. This feature will only generate additional partitions in SSAS with the Time filter for each range of data.
Selecting the right size for the Dimension when designing. Make a clear definition and proper prediction for the Dimension and choose a suitable size for it.
Do not turn on Security or All Hierarchy Dimension attributes when not necessary.
The Security option will allow administrators to grant users access in the Dimension Member level. For instance, when the Department Dimension’s Security is turned on, you can specify a user to only have access to Finance and Marketing data.
Data Rules, SQL, and MDX
MDX is one of the most frequent causes of poor performance in an application. As a general rule, if an MDX calculation can be done through Data Rules or SQL instead, you should convert it. Make sure all MDX calculations are as limited in scope as possible.
Materialize Data Rule Calculations
Action Rules tend to have better performance than View Rules, as the calculation is only run a single time and the results are 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 benefit of Action Rules for performance considerations is that the data affected by the rule can be scoped with parameters. This means you only have to recalculate the data that actually changed. View Rules will always recalculate all the data, every time.
Minimize the Data in a Calculation
The less data you're moving around, the faster the calculation will be. Check for columns that only contain a single value, as these can be temporarily removed from the steps of the calculation. This is especially common when scoping Action Rules with parameters.
Re-use Calculated Data
Sometimes various calculations require the same data or calculated results. Try not to make the same calculated step multiple times. Store the data in a partition to be used later, or link to a previous Data Rule step.
Scope and Selection Scope
Scope context and selection context can be sent to as part of the parameters (@Scope, @SelectionScope) for the rule execution.
For performance consideration, Include form scope and Include form selection should be unchecked.
Default Filter Members
You should always default each member to the lowest member within a hierarchy to give better performance when a Form is first opened.
Hide Empty Rows
Turn on the Hide Empty Rows option.
Keep Form Design Simple
Limit the number of Dimensions on the rows or columns. Limit the scope and size of the Form. Shrink large Forms down with more filters. Disable any features that aren't necessary, or redesign the Form in a way that doesn't need them (annotations, drop-downs, cell attributes, increase/decrease colors).
Use Multiple Forms
Break large Forms into multiple smaller Forms, and place them side-by-side on a Dashboard.
Build validation models and define relationships between Dimension Members, to limit how many actually need to exist on the Form.