KB483294: Filter optimization for improved performance when filter expression contains an 'IS NULL' or 'IS NOT NULL' clause.

Community Admin

• Strategy

This technical article explains a filter optimization for improved performance. Staring MicroStrategy 11.0, Cube Subsetting instruction (CSI) filter will be optimized in some cases when one of the filter condition contains IS NULL or IS NOT NULL.

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Performance overhead during report execution.
CSI shows a changed or optimized filter expression.

Cube Subsetting Instruction (CSI)

What is CSI?
How do I get the CSI Log?
Query Details - Another way to get the CSI statements.

In Strategy Tutorial Project, create an In-Memory Cube by importing the following Database Tables.

ITEM_EMP_SLS
LU_ITEM
LU_CATALOG
REL_CAT_ITEM
LU_EMPLOYEE

Publish the cube
Create a dossier using the newly created cube.

Template has [Call Ctr Id], [Item Id] as attributes and has [Tot Dollar Sales] as Metrics.
Add a filter with the following conditions

Notice the CSI statements below pertaining to the filter conditions (highlighted in RED).

Current CSI

Tables Accessed:
Table49 [F_ITEM_EMP_SLS5955736B49126F2136DA5381CB5B9A1D]:
Emp Id, Item Id,
Tot Cost, Gross Dollar Sales, Row Count - ITEM_EMP_SLS, Tot Unit Sales, Tot Dollar Sales, FACT_TABLE
Table53 [F_LU_EMPLOYEE22271EFD4AA2CFCF14F9DE84163537DD]:

Fte Flag, Country Id, Dist Ctr Id, Manager Id, Call Ctr Id, Location Id, Emp First Name, Birth Date,
Hire Date, Emp Id, Emp Last Name, Emp Ssn, Row Count - LU_EMPLOYEE, Salary, FACT_TABLE

select    [Call Ctr Id]@[CALL_CTR_ID],
                [Item Id]@[ITEM_ID],
                sum([[F_ITEM_EMP_SLS5955736B49126F2136DA5381CB5B9A1D].Tot Dollar Sales])
               @{[Call Ctr Id],[Item Id]} as [Tot Dollar Sales]
from      In_Memory_ITEM_EMP_SLS LU_ITEM LU_CATALOG (3 tables)
with Table Join Tree:       [F_ITEM_EMP_SLS5955736B49126F2136DA5381CB5B9A1D]

<([Item Id]@[ITEM_ID] in (2)
and [Item Id]@[ITEM_ID] in (3)
and [Item Id]@[ITEM_ID] is not null)>

Join (Set of distinct Tuple([Emp Id]@[EMP_ID], [Call Ctr Id]@[CALL_CTR_ID]) where Tuple([Emp Id]@[EMP_ID], [Call Ctr Id]@[CALL_CTR_ID]) in [F_LU_EMPLOYEE22271EFD4AA2CFCF14F9DE84163537DD]) with output level Tuple([Emp Id]@[EMP_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID])

Optimized CSI:

Tables Accessed:
Table49 [F_ITEM_EMP_SLS5955736B49126F2136DA5381CB5B9A1D]:
        Emp Id, Item Id,
Tot Cost, Gross Dollar Sales, Row Count - ITEM_EMP_SLS, Tot Unit Sales, Tot Dollar Sales,
         FACT_TABLE
Table53 [F_LU_EMPLOYEE22271EFD4AA2CFCF14F9DE84163537DD]:
         Fte Flag, Country Id, Dist Ctr Id, Manager Id, Call Ctr Id, Location Id, Emp First Name, Birth Date,
         Hire Date, Emp Id, Emp Last Name, Emp Ssn, Row Count - LU_EMPLOYEE, Salary, FACT_TABLE

select [Call Ctr Id]@[CALL_CTR_ID],
             [Item Id]@[ITEM_ID],
             sum([[F_ITEM_EMP_SLS5955736B49126F2136DA5381CB5B9A1D].Tot Dollar Sales])
             @{[Call Ctr Id],[Item Id]} as [Tot Dollar Sales]
from      In_Memory_ITEM_EMP_SLS LU_ITEM LU_CATALOG (3 tables)
with Table Join Tree:       [F_ITEM_EMP_SLS5955736B49126F2136DA5381CB5B9A1D]
<(0 and [Item Id]@[ITEM_ID] is not null)>

Join (Set of distinct Tuple([Emp Id]@[EMP_ID], [Call Ctr Id]@[CALL_CTR_ID]) where Tuple([Emp Id]@[EMP_ID], [Call Ctr Id]@[CALL_CTR_ID]) in [F_LU_EMPLOYEE22271EFD4AA2CFCF14F9DE84163537DD]) with output level Tuple([Emp Id]@[EMP_ID], [Call Ctr Id]@[CALL_CTR_ID], [Item Id]@[ITEM_ID])

Note that Filter has been optimized from

This enhancement is a part of Strategy 11.0 and above.
If you are upgrading to Strategy 11.0 or above, to pick up the fix, enable project level setting "Data Engine Version" to 11.
This property can only be enabled through Workstation. For more details on enabling the setting, refer to Change Data Engine Version.

Comment

0 comments

Details

Knowledge Article

Published:

May 16, 2019

Last Updated:

March 21, 2024