1

I am new to MSTR.

We are working on migrating from Essbase to Microstrategy 10.2. After migration, we expect business users to be able to create report on top of MSTR cube and play around with the data similar to the way they have been doing using Essbase and Excel. I need help to design data model for given scenario:

FactTb:

Subcategory Revenue

1   100

2   200

3   300


DimensionTb:

Category    Subcategory

A   1

A   2

B   1

B   2

B   3

C   2

C   3

User wants to see revenue by category or subcategory.

FactTb has 3 rows. Assuming size of each row as 10 bytes, size of FactTb is 30 bytes.

If it is joined with DimensionTb there will 7 rows and size will grow (approximately) to 70 bytes.

Is there any way to restrict size of Cube?

Mapping of Category and Subcategory is static and there is no need to maintain a table for it.

Can I create/define DimensionTb out of Cube (Store it in report, create derived element using Subcategory)?

We want to restrict size of cube to maintain it in memory and ensure that report will always hit cube over database.

Swapnil
  • 424
  • 2
  • 13

1 Answers1

0

A cube is just the result of a SQL query, copied in memory for faster access. As you cannot imagine the result of a query split in two, the same is for a cube.

In memory cubes are compressed by MicroStrategy using multiple algorithms (to use the best compression depending on column data types and value distributions), but cubes contains also internal indexes (to speed up data access) created automatically depending on the queries used for the cube.

A VLDB setting can help reducing the size of the cube. If you check the technote TN32540: Intelligent Cube Population methods in MicroStrategy 9.x, you will see different options, for my experience the last setting (Direct loading of dimensional data and filtered fact data.) is quete helpful in speed up cube loading and reduce the size, but you can also try the other ones (Normalize Intelligent Cube data in the Database).

With this approach the values from Dimension tables will be stored in memory, but separated from the fact, saving space.

Finally to be sure that your users alway use the cube, allow/teach them to create reports and dashboards clicking directly on the cube (or selecting it).

This is the safe way, MicroStrategy offers also a dynamic way to map reports to cubes (when conditions are satisfied), but users can surprise even the most thorough designer.

mucio
  • 7,014
  • 1
  • 21
  • 33