Starting from MicroStrategy 10 – you’ll see tge optimized form of Intelligent Cube based reports SQL modified as below with better optimizations.. It is similar to concept of standard report hitting the database with intermediate table concept of fetching required amount of data and merging it finally with necessary filters to improve execution performance.. Good improvement though…
SUMMARY
This document provides a brief overview of the Cube Subsetting Instruction (CSI) in MicroStrategy 10 and provides links to useful resources in case troubleshooting is needed.
Cube Subsetting Instruction (CSI):
The Cube Subsetting Instruction or CSI is generated whenever a report, document, or dashboard containing an Intelligent Cube or subset report is run. It indicates which attributes and metrics need to be brought back from the Intelligent Cube, along with any filter conditions that need to be applied.
The way CSI is structured is very similar to SQL, with the exception that the data source is an Intelligent Cube instead of a database. Basically, it will generate the followings structure:
1) SELECT statement containing the attributes (including their respective attribute forms) and metrics (including the level at which the metric will be calculated) that will go on the grid:
SELECT [Attribute1]@[Attribute1_ID],
[Attribute2]@[Attribute2_NAME],
sum([Fact1])@{[Calculation Level for Fact1]},
sum([Fact2])@{[Calculation Level for Fact2]}
2) FROM statement indicating which table/cube are used to source the metrics/attributes in the SELECT statement.
from Table1
3) Data filtering statements to validate any filtering conditions to be applied or security settings to be considered:
WHERE [Attribute1]@[Attribute1_ID] in (1, 2, 3)
Steps used by the MicroStrategy Engine to generate CSI:
1. Analyze Intelligent Cube or subset report to decide the following:
a. Attributes to include
b. Metrics to calculate
c. How to calculate the metric limit, if applicable
2. Resolve the metric levels according to the dimensionality rules
3. Process filters (Metric qualification) by creating sub-view-report
4. Decide whether the security filter can be applied as a view report filter for each metric
5. Perform tasks for the Analytical Engine and relational subsettings
CSI examples for OLAP and PRIME Cubes:
Example of CSI reading from an Intelligent Cube:
select [Customer Region]@[CUST_REGION_ID],
[Customer Region]@[CUST_REGION_NAME],
sum([Revenue])@{[Customer Region]},
sum([Profit])@{[Customer Region]}
from Table 1
where [Customer Region]@[CUST_REGION_ID] in (1, 2, 3)
Example of CSI reading from a PRIME Cube:
Tables Accessed:
Table4:Call Center, LOOKUP_TABLE
Table9: Subcategory,Quarter,Year,Region,Call Center,Category,M01, FACT_TABLE
select [Call Center]@[CALL_CTR_ID],
[Call Center]@[CENTER_NAME],
[Year]@[YEAR_ID],
sum([Table9.M01])@{[Call Center],[Year]} as [M01]
from Cube02- Prime Cube by AE
with Table Join Tree: Table9<[Year]@[YEAR_ID] in (2008.000000)>
CSI Troubleshooting:
In case the CSI needs to be analyzed, the CSI log can be activated as per the following MicroStrategy Knowledge Base Document:
TN221518 – How to activate the Cube Subsetting Instruction (CSI) Log in MicroStrategy 10.
TN221521: What is the Cube Subsetting Instruction (CSI) in MicroStrategy 10 ?