With fresh input from MicroStrategy World 2022, our business intelligence experts share their MicroStrategy schema know-how and best practices with you.
How does the MicroStrategy scheme work?
In principle, BI software does not "invent" data, but reads it from a database. To make this step easy for business users, the software "translates" the report objects into database SQL using simple drag and drop.
MicroStrategy has the schema objects for this.
Step 1) Select tables in the warehouse catalog:
The database tables are selected here and made available in the "Schema Objects>Tables" folder. The report objects are created based on these database tables.
Step 2) Create schema objects:
1. attributes - the attributes are the dimensions such as day, week, month, customer, article, region, country, city, market.
Each attribute has a separate dimension table with the ID and further information on the dimension. The month 198001 and the day of the week (Wednesday) can be stored for the TagID 19800116. The name and address can be stored for the customer ID 4712. Attributes always have a single (or composite) key (ID) and other attribute forms with additional information.
2. facts - the facts are the key figures such as turnover, costs, number of units.
These are always stored in fact tables that are related to the dimension IDs. For example, on the day 19800116, a turnover of €300 with costs of €200 for a quantity of 10 can be saved for the customer ID 4712.
3. hierarchies - Hierarchies are built up from the attributes - the attributes are set in relationships such as day to month or city to region.
This means that the information can be generated at higher levels (monthly report, country statistics).
Step 3) Create public objects:
4. metrics - Metrics are created from the facts.
Metrics are either facts with calculation functions such as SUM(sales) or AVG(costs) or calculations such as unit costs (costs/unit quantity) in order to generate further information.
5. transformations - Transformations are also necessary in order to be able to convert previous year values or accumulating totals.
The required relationship information is stored in two columns in separate transformation tables, which in turn is transferred to the metric and then replaces the day instead of 16.01.1980 with the value 16.01.1979. Or the relationship 16.01.1980 with all values from 01.01.1980 to 16.01.1980, so that the accumulating functions can take place.
6. metric functions - Of course, share values can also be calculated in the key figures.
The metric can be set so that a daily value can be divided by the monthly value to see how much turnover was generated on the individual days in relation to the month as a whole.
Step 4) Fine-tuning for automation:
Further fine-tuning steps are then required in order to really optimize the schema. The aim is for users to be able to drag and drop objects such as "Month", "Country", "Actual turnover", "Year-to-date turnover", "Actual costs" and "Year-to-date costs" onto the report template and for the SQL to be generated automatically. Other objects such as input objects or dynamic filters also need to be created. This allows the user to select the month or country or set the report so that the last 4 months are always selected dynamically.
Best Practice MicroStrategy
It has proven to be best practice to create the database tables from 1:1 views in order to be more independent of database schema changes. Object translation is also implemented in the objects, which requires a specific structure on the database. Communication between BI experts and database architects is important here in order to be able to provide high-performance systems at the end of the project. The topic of data quality can also be incorporated so that comparison reports between the source data tables and the target data tables processed by ETL can be carried out easily. This also allows the user to check plausibility and ETL problems become transparent for users to ensure confidence in the data.
To summarize: The MicroStrategy schema requires an initial effort to create. This one-off effort then simplifies ad hoc reporting enormously so that users can generate all information independently without IT support. Even the regular dispatch of your own reports or the mobile availability of information in intuitive dashboards is very easy to implement. Please feel free to contact our experts for further details.
