Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
Description:
Sometimes when running a View Report against an Intelligent Cube it can be seen that it consumes far more memory than the Intelligent Cube is within memory. This behavior comes from the de-normalization of the Intelligent Cube into a Tabular Dataset for calculation of Derived Elements within the Analytical Engine.
An Intelligent Cube is a multi-dimensional dataset that stores as specific grouping of information within memory of the Intelligence Server. This allows for quick access of information by Users when running Reports or Dossiers as information does not have to be transferred between a Database and the Intelligence Server. As part of generating an Intelligent Cube Strategy uses a normalized data structure similar to a Relational Database with a series of Fact and Lookup tables.
Since an Intelligent Cube is structured similarly to a Relational Database the method of retrieving data from the Intelligent Cube, Cube Subsetting Instructions, is very similar to SQL in nature and reads data out of the distinct tables within the Intelligent Cube through standardized statements.
https://community.strategy.com/article/KB221521-What-is-the-Cube-Subsetting-Instruction-CSI-in
When selecting data from a Relational Database, or similar structure such as an Intelligent Cube, the information returned is formed within a Tabular Dataset.
Memory Utilization:
Depending on the Data Model for the information being selected the Tabular Dataset may represent a large amount of total memory being consumed that is not initially expected based on the size of the Intelligent Cube. For example in an Intelligent Cube that has 50 Attributes and 10 million rows as a spread of varchar(32) Strings and int column aliases as the whole set of information stored. This set of Attributes then describes five Years worth of data across the 10 million rows.
Inside of the Intelligent Cube or other normalized data storage schema there will exist 5 instances, one for each year, of the Year description form and when data is selected from the normalized set a join is conducted between the Fact table and Lookup table to bring the Year description form into the final dataset. This gives us an initial cost of the Year description form as 160 bytes, but the Tabular Dataset that is returned and has the Year description form on every row out of the 10 million rows has a cost of .29 gigabytes.
In a Data Model for the Intelligent Cube that has a large number of rows, columns, and multiple low cardinality Attributes this can cause a discrepancy between the published Intelligent Cube size and the size in memory of the de-normalized Tabular Dataset if a strict View Report is being run against the Intelligent Cube. A straight View Report requires grabbing every element of the Intelligent Cube instead of a targeted set of elements that are particular to the creation of an insight.
This behavior is one reason why it is only recommended to use an intermediate Report off an Intelligent Cube as a dataset rather then the Intelligent Cube itself if a feature, such as prompting, is not available for the Intelligent Cube. If prompting is not a requirement for this dataset then the best method to improve performance is to directly utilize the Intelligent Cube within the Dossier to reduce both the memory footprint of the Report Instance and the computation time within the Analytical Engine.
The de-normalization of the dataset is integral to later operations that allow for the dynamic calculation of Smart Metrics, Derived Elements, and more once the data has been retrieved from the source.
Recommendations:
If an intermediate Intelligent Cube Report needs to be utilized, such as for prompting, then there may be methods of reducing the impact of the additional Report Instance on the Intelligence Server utilization:
- Change the definition of the intermediate Intelligent Cube Report to a Subset Report rather then a View Report. A View Report will bring back all of the information contained within the Intelligent Cube which could include a large amount of data that is not necessary to communicate the Business Intelligence in the final Dossier. A Subset Report makes use of Filters and Prompts, as well as Attribute Selection to reduce the total amount of data being selected. By limiting the information to only a portion of the Intelligent Cube which is needed to the final insight then the inflation from de-normalization can be mitigated.
- Utilize Intelligent Cube Report Caching. A new feature added in Strategy 10.9 allows for the caching of an Intelligent Cube Report in the same manner as caching of a normal Report that runs against the Data Warehouse. By caching the Intelligent Cube Report the various prompts can be filled out through subscriptions and in-memory caches can be accessed instead of directly running the Cube Subsetting Instructions against the Intelligent Cube.
- Separate the Intelligent Cube into multiple smaller Intelligent Cubes. The main reason for data inflation from the intermediate Intelligent Cube Report is from the de-normalization of the dataset containing low cardinality Attributes. If I take the example Intelligent Cube from above and place dates in a stand-alone Intelligent Cube from the date based transactions I can then utilize data blending within the Dossier to merge the additional information onto the final insight. Depending on the implementation this can have many benefits as joining can be more efficient then removing rows through a WHERE clause, and a reduced Tabular Dataset helps the memory footprint and can help to limit Analytical Engine calculations to only the data that is absolutely necessary to lessen CPU utilization and possibly improve return time.
Since this method involves larger changes, the exact improvements that will be seen can vary depending on the amount of data, type of information stored, and overall model. This approach should be tested before implementing in a Production environment to ensure the desired results are achieved.
