Partition Mapping – Warehouse Partitioning

Application partitioning in MicroStrategy is of two types – Warehouse partitioning and Metadata partitioning. By the name it self, it defines that the partitioning is implemented in Warehouse for Warehouse partitioning and similarly for Metadata partitioning.

Warehousing Partitioning
To describe this in detail, I will take the following example – We wanted to improve the performance of the reports hitting the face table with billions/trillions of records. We decided to go with partition tables so that one table is created for every month. And based on the data required in the report, only those tables will be used.

Lets say, there are five tables created –
revenue_fact_012009
revenue_fact_022009
revenue_fact_032009
revenue_fact_042009
revenue_fact_052009
Each table has data for one month and the name indicates the month for every table. These tables are called Partition base tables (PBT)

MicroStrategy SQL should be able to hit the right table based on the months required in the report. To achieve this, we would need a Partition mapping table (PMT) which describes mapping between between month and the table name. Structure of the table should as shown below-

Table name: map_revenue_fact
MONTH_ID    PBTNAME
—————     ————-
012009          revenue_fact_012009
022009          revenue_fact_012009

Note: The name of the first column in the PMT tables should match the attribute (month) and the second column name should strictly be “PBTNAME”. This can not be changed.

Step 1: After all the above tables are available in the warehouse. Pull them into the project – Schema > Warehouse Catalog [as shown below]. It is not required to pull the PBT’s as there will not be any objects mapped directly to them.
The PMT should be with a different icon displaying the number of PBT in parenthesis

Step 2: Map the month attribute to the PMT table – map_revenue_fact

Step 3: Create a fact “revenue”.
In the fact editor (in source table), if you do not find the PMT table, simple select “All Tables” in the drop down.

On the left pane, “revenue” is displayed. Select “revenue” and check the source table as “map_revenue_fact”

Step 4: Navigate to Schema Objects\Partition Mappings, edit the PMT to add the Partition level attribute (Month). This describes that the partitioning is by month.

Step 5: Create a metric on the fact “revenue”, “Revenue”

All Done !

Create a report with Month attribute, revenue metric and a filter on month for Feb 2009 and the report SQL should look like –

The first pass is hitting the PMT and returns the name of the PBT which should be used in the SQL. The second pass generates the output data.
Metadata Partitioning will have the PMT in the metadata.