Joaquin Attanasio

Joaquin Attanasio

Business Intelligence Consultant | Microstrategy Expert | Data Specialist

Other Articles:

MicroStrategy Definition of Fact Tables in queries

Share on linkedin
LinkedIn
Share on facebook
Facebook
Share on twitter
Twitter
Share on whatsapp
WhatsApp

Good morning everyone again! One more week that finds us in our warm and entertaining little corner of #BestInMicro!

This week we return with a new story lived in the flesh, one of those that give you support to the experience as a consultant, one of those that you learn when you live them.

Fact Tables

In my case, it was a situation that I knew how to solve (I don’t remember if it is because it happened to me or if I learned it from reading the manuals), but it is a situation that is not common to see and I thought it was appropriate to tell you about it. This week I am going to tell you how we can control which tables will dominate the queries when creating reports in MicroStrategy.

Conclusions

Let’s start from the beginning. As we have already repeated several times, MicroStrategy is basically a tool that allows you to generate and execute queries without the need to understand SQL. That is, we drag a couple of attributes, some metrics, and some filter… and voila, we have our data. We can go to the little button that shows us the SQL query that was generated… but everything is automatic.

Logically, MicroStrategy does not invent which tables the information comes from, everything is previously defined in the semantic layer that characterizes the tool, so we know that most of the objects will have an associated table. For example, an attribute will have an ID associated with the main table (of facts) and another dimension table that will have the description of that attribute (a lookup table).

Fact Tables Snowflake

In a simple model, we have a Snowflake model… basically a table of facts and their multiple associated dimensions. But when we go into slightly larger models, we will see that there are several fact tables involved in our model….

And this is where it gets complicated. Let’s take a simple example: if we have a fact table that records purchases and another one that records sales, in both we will have the column “PRODUCT_ID”. But if we want to make a query that lists the products we sell, MicroStrategy should generate the query on the sales table… but how do we make it bring us ONLY the ones in this table, and not bring us all the products we have in the catalog? Or those on the shopping table?

I know, maybe that’s a pretty generic example, but I think you get the idea. So, here’s the question:

Conclusions

In the case of attributes, the question is simple: the table is defined as a lookup table. Easy, isn’t it?

Fact Tables Attribute Editor

But… If I only want to show the ID in the report, will it always go to the lookup table? What if we add an indicator? What if it is a heterogeneous ID? Who tells me where it would go in this case?…

Fact Tables Attribute

Well, there is no fixed answer. At least not generic, but today I will explain two ways to define an answer to this question.

1- Table logical Size

All tables have information, and the more information you have, and the less indexed it is, the more time it will take to get the information from that table. This is why each table has a certain “weight”.

If we go to the properties of a table in MicroStrategy, we will see that there is a field indicating the weight of that table. This affects the time to inquire. MicroStrategy will try to define the query using the “lightest” tables. This may even lead MicroStrategy’s Query engine to prefer to do several nested joins, jumping through different tables rather than query a single aggregate table. This note explains a bit about what factors affect the weight of each table.

Table Logical Size

Care must be taken to modify this value. When updating the schema (or restarting the IS, logically), these values are recalculated. Except selecting the checkbox below the weight, but do so with the understanding that this leads to manual maintenance of the table and may harm the overall performance.

But we already know each other, and here we like to make things scalable and a bit simpler. That is why, instead of playing with the weights of the tables, we will focus on the indicators that manage the tables. So we move on to the next point:

2- Metric Parameters

As we already know here, what really affects a query is the fact table. And what objects do they mainly read from this one? Of course, indicators! This is why we can generate an impact from the metrics editor.

Have you noticed that if you hover over the formula, an option called “Parameters” appears? Here we can define certain properties that will impact the query.

Metric Parameters

Within these options, there is one called “FactID”. En esta podemos definir qué fact nos interesa que priorice la fórmula o la consulta. Recall that a fact represents a column in a table. That is, we can indicate that we prefer to try to do the calculation based on a column of a particular table in our model.

Count Parameters

We will see this much clearer in the video, where we can see an example with different indicators and how is the difference between them, how it changes the query and the result.

What do you say? Do you know of any other way? Surely some VLDB property has something to tell around here?

Anyway, I hope this is clear. As always, I leave you several links where you can find more information, and if you have any doubts, you can ask me here below! Espero que les haya sido de utilidad, ¡y hasta la próxima!

Sources

Deja un comentario

Your email address will not be published. Required fields are marked *