Microstrategy partition data

18 Jun 2023

Dificulty ★★★★☆

INTRO

If database queries take too long, there is a possibility to divide the data into several tables.
In Microstrategy, however, we read them as from one table.

How to do it

Let’s take the example of dividing the table by years.

Now we need a table/view that will tell it to read these tables together. In our case, named PMT_YEARS.

CREATE TABLE [multi_data].[PMT_YEARS](
	[Leto] [smallint] NULL,
	[PBTNAME] [varchar](50) NULL
) ON [PRIMARY]

In the first column, enter the attribute by which the above tables should be filtered (Year).
In the second column, the name of the table to be read. The second column is called PBTNAME
.

If you do not yet have a view in the database through which you will filter the Year, do this before the next part and register the attribute in MicroStrategy.

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

In the MicroStrategy ‘Warehouse Catalog’, add the table/view in which you entered the link between the attribute and the table.

As you can see, it has a different shape than the other sources.
If we click on ‘view partition’, we can see through which tables it is grouped.

Under ‘Schema Objects’ chose ‘Partition Mappings’

We select the newly added entry. In ‘Logical View’ we add an attribute through which we will select the Year.

We add the PMT_YEARS to the Year attribute.

When we register the other attributes, we must be aware that additional filters in the report will only be possible in the star schema or snowflake.

Examples

If we filter year 2018. Query cheks witch tables to read.

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

Since we only need one it will read only that one.

select	[a11].[Drzava]  [Drzava],
	[a11].[Leto]  [Leto],
	[a11].[ST]  [WJXBFS1]
from	[multi_data].[PRIHODKI_2018]	[a11]
where	[a11].[Leto] in (2018)

In case we chose 3 years.

select	distinct [a11].[PBTNAME]  [PBTNAME]
from	[multi_data].[PMT_YEARS]	[a11]
where	[a11].[Leto] in (2018, 2019, 2020)

Query returns 3 joins.

select	[a11].[Drzava]  [Drzava],
	[a11].[Leto]  [Leto],
	[a11].[ST]  [WJXBFS1]
from	[multi_data].[PRIHODKI_2018]	[a11]
where	[a11].[Leto] in (2018, 2019, 2020)
union all
select	[a11].[Drzava]  [Drzava],
	[a11].[Leto]  [Leto],
	[a11].[ST]  [WJXBFS1]
from	[multi_data].[PRIHODKI_2019]	[a11]
where	[a11].[Leto] in (2018, 2019, 2020)
union all
select	[a11].[Drzava]  [Drzava],
	[a11].[Leto]  [Leto],
	[a11].[ST]  [WJXBFS1]
from	[multi_data].[PRIHODKI_2020]	[a11]
where	[a11].[Leto] in (2018, 2019, 2020)

If it is necessary to break down the data even more, this can be done with additional columns in the table/view where we added PBTNAME.

The process is the same except that we add more attributes to ‘Partition Mappings’