If you’re working with large datasets in MicroStrategy and finding that your queries are taking too long, partitioning your data might be the solution you need. Partitioning divides your data into smaller, more manageable tables, allowing for faster query performance while still enabling you to treat the data as a single logical table. Here’s a step-by-step guide on partitioning your data by year in MicroStrategy.
Introduction to Data Partitioning
Data partitioning is a technique for splitting large tables into smaller, more manageable pieces. This can significantly improve query performance and manageability. In MicroStrategy, partitioned tables can be read as a single table, simplifying data handling and reporting.
![Data Partitioning](https://www.crmt.com/wp-content/uploads/2024/07/sales-1-1024x576.png)
![Data Partitioning](https://www.crmt.com/wp-content/uploads/2024/07/sales-2-1024x576.png)
Step-by-Step Guide to Partition Data by Year
1. Create Partition Tables
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.
![](https://www.crmt.com/wp-content/uploads/2024/07/multidata-sales.png)
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.
![](https://www.crmt.com/wp-content/uploads/2024/07/multidata-sales2.png)
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.
![](https://www.crmt.com/wp-content/uploads/2024/07/pmt_years.png)
![](https://www.crmt.com/wp-content/uploads/2024/07/chema-objects.png)
5. Define Partition Mappings
In MicroStrategy, go to Schema Objects and choose Partition Mappings. Select the newly added entry and add the attribute for the year in the Logical View. Map the PMT_YEARS to the Year attribute.
- Navigation to Schema Objects.
- Selecting Partition Mappings.
![](https://www.crmt.com/wp-content/uploads/2024/07/partition-mappings.png)
- 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.
![](https://www.crmt.com/wp-content/uploads/2024/07/warehouse.png)
- 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.
![](https://www.crmt.com/wp-content/uploads/2024/07/attribute-editor.png)
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)
7. Advanced Partitioning
- If you need to partition data further, you can add more attributes to the PMT_YEARS table and update the Partition Mappings accordingly.
![Data Partitioning](https://www.crmt.com/wp-content/uploads/2024/07/partitioning.png)
![](https://www.crmt.com/wp-content/uploads/2024/07/partitioning2.png)
Conclusion
Partitioning your data can greatly enhance query performance and efficiency in MicroStrategy. By dividing your data into smaller tables and mapping them appropriately, you can ensure that your queries run faster, and your reports are more responsive. This method is particularly useful for large datasets where query performance is critical. Implementing these steps will help you optimise your MicroStrategy environment and make the most of your data.
With these tips, you can streamline your data processing and improve the overall performance of your MicroStrategy reports. Happy partitioning!
By following these steps, you can effectively partition your data in MicroStrategy, leading to improved query performance and more efficient data handling.
If you need expert assistance in optimising your MicroStrategy setup, don’t hesitate to contact us for professional support!
Related content
MicroStrategy: The Transition from Web to Library Interface – 10 reasons why
MicroStrategy added the Library interface to its portfolio with version 10.11, released in 2019. However, the interface has yet to gain significant popularity a...
read moreExploring MicroStrategy AI: The Future of Business Intelligence
The blend of Artificial Intelligence (AI) and Business Intelligence (BI) reshapes how we understand data. Thanks to advancements in AI, specifically through Lar...
read more