Boost Your Query Performance in MicroStrategy with Data Partitioning

First, ensure your data is divided into separate tables for each year. For example, you might have tables named SALES_2018, SALES _2019, and SALES_2020.

2. Create a Mapping Table

You need a table to map each year to its corresponding partition table. Create a table named PMT_YEARS to store this information:

CREATE TABLE [multi_data].[PMT_YEARS]( 
[Year] [smallint] NULL, 
  NULL 
) ON [PRIMARY] 

Enter the attribute to filter the tables (Year) in the first column. In the second column, specify the name of the table to be read, which is called PBTNAME.

3. Create a Year Attribute View

If you don’t already have a view to filter by year, create one:

Create view [multi_data].[PMT_YEARS_ATRIBUTE_V] as 
select distinct year from [multi_data].[PMT_YEARS] 

4. Register the View in MicroStrategy

Add the PMT_YEARS view to the “MicroStrategy Warehouse Catalog”. This step makes the mapping table available for use in MicroStrategy.

• Navigation to Schema Objects.
  
• Selecting Partition Mappings.
  

• Adding attributes to Logical View.
  
  Select the newly added entry. In the ‘Logical View,’ add the attribute that will be used to select the Year.
  

• Link the PMT_YEARS to the Year attribute.
  
  When registering other attributes, remember that additional filters in the report will only work within the star or snowflake schema.
  

6. Query Examples

When filtering by a specific year, such as 2018, the query only reads from the corresponding table:

select distinct [a11].[PBTNAME] [PBTNAME] 
from [multi_data].[PMT_YEARS] [a11] 
where [a11].[Year] in (2018) 

If filtering by multiple years, such as 2018, 2019, and 2020, the query reads from the relevant tables and combines the results:

select [a11].[Year] [Year], 
[a11].[Country] [Country], 
[a11].[ST] [WJXBFS1] 
from [multi_data].[SALES_2018] [a11] 
where [a11].[Year] in (2018) 

Query returns 3 joins.

select [a11].[Year] [Year], 
[a11].[Country] [Country], 
[a11].[ST] [WJXBFS1] 
from [multi_data].[SALES_2018] [a11] 
where [a11].[Year] in (2018, 2020, 2019) 
union all 
select [a11].[Year] [Year], 
[a11].[Country] [Country], 
[a11].[ST] [WJXBFS1] 
from [multi_data].[SALES_2019] [a11] 
where [a11].[Year] in (2018, 2020, 2019) 
union all 
select [a11].[Year] [Year], 
[a11].[Country] [Country], 
[a11].[ST] [WJXBFS1] 
from [multi_data].[SALES_2020] [a11] 
where [a11].[Year] in (2018, 2020, 2019)