Welcome back! As every week, a new article in our little space #BestInMicro, a little corner where I share stories, experiences, learnings, secrets and techniques of the day to day life of a MicroStrategy consultant.
This week I am taking you on a journey through time and space. Sounds like a Bradbury story or a Stephen Hawking theory, doesn’t it? So today we will take our metrics, put them in the DeLorean and make them travel back in time and into other dimensions to see what value they correspond to wherever we go. So fasten your seatbelts, because today we will be looking at: Transformations.
Let me make it a little clearer… Let’s start with the transformations. Transformations are a schematic object in MicroStrategy, which allow to generate a direct link or relationship between different dimensions.
The most commonly used example, and the easiest to understand, is if we look at it in the dimension of time. The transformation generates a relationship between, for example, the previous year, or the previous month, or a set of days corresponding to a week, etc.
For example, if today is 4 January 2021, the transformation to the previous year would be 4 January 2020; the day before would be 3 January 2021, or the cumulative year (also known as Year To Date, or YTD), would be 1, 2, 3 and 4 January 2021.
Great! Now that we understand what we mean when we talk about transformations, let’s take it to where we want to go today. When you go to the metrics editor, you will have noticed that there are different options, such as the filter we apply to them, the dimensionality and there is one called transformation.
When we take a transformation and apply it to a metric, it is basically bringing that metric to the dimension to which we are transforming it. That is to say, if we apply the transformation of the previous year, it will return the value corresponding to the previous year for the row we are in.
Let move on. Once we understand what they are for and how they are used, the next step is to understand that there are different types, basically based on how we create them (but this does not mean that they cannot both be used in the same transformation).
This method consists of having a table in our warehouse, where a relationship is defined between an “origin” date and the date it refers to.
In this way, we create a “previous day” transformation for the attribute “Date” where we assign the column “previous date” (or, well, whatever relation is of interest).
It works in a similar way for cumulative values:
Here you will notice that for each day (column “date”) you have all the corresponding values accumulated, either for the month or for the year in this example (being January 😊). In this case it is important to define that the relationship in the transformation will be many-to-many (for the same value, several occurrences in the left column are related to several occurrences in the right column). For more information, here you can read more about it.) I’ll soon make a video showing examples of each case, so stay tuned!
In addition to the table, there may be scenarios where some kind of formula or calculation is required to obtain the target date, either because there is no possibility to generate the table, because it is a relatively more complex calculation, or because it is easier to generate the formula than to create the table.
The use of this type of transformation allows formulas to be created using columns from the warehouse tables, constants, arithmetic operators and mathematical functions.
The use of transformations is a very common practice when analysing evolutions and comparisons, including SCD dimension analysis (Slow changing dimensión), since, as I said at the beginning of the article, transformations are not exclusive to the time dimension, although they are the most common.
As a big difference at the macro level between both methods of transformations, we can consider that those based on an expression can generate some kind of load on the server (as it has to do one more layer of calculation, while when using a table, everything comes directly from the warehouse).
So much for this week, any doubts, questions, comments or whatever you want, without fear or shame! See you next week!
* Tu información será utilizada exclusivamente para contactarte en relación al Business Data Master. No hacemos spam ni compartimos datos con terceros.
