Creating Derived Metrics and Attributes in MicroStrategy

Audio Duration: 6:36

0:00

Hello there. Welcome back to another Data in the Wild episodes hosted by the Data Meaning. Before we begin, don’t forget to subscribe to the channel below and click the bell to turn on notifications to be the first to know every time we upload a new video.

 

0:32

Today, we are covering how to create derived attributes and metrics in MicroStrategy. Let’s get started. Sometimes you don’t have all the columns that are necessary for creating your visualizations. MicroStrategy can help you with that, with the creation of derived attributes and metrics.

 

0:50

You can create them in a report, document and dossier. If you create them in a report, you can reuse them in every document and dossier that you used that report. But if we create them directly in a document or dossier, they will only live in those objects, you cannot reuse them in other documents or dossiers.

 

The Create Metric Tool

0:50

In this example, I’ll show you how to create them in a dossier using MicroStrategy workstation. Let’s check all the attributes and metrics that we have available in my custom data set here. I have month, cost, and revenue. As you can see, we only have the month as integers, the cost and their revenue values. Let’s create derived metrics. We can do it in three different ways.

 

1:40

The easiest one is to right click on the metric header name, go to shortcut metric, and use one of these options here. For example, we can use percentage to total, and just say by columns, and now we have the percentage that these guy represents of the total. We can also right click on the revenue. And we can go to calculation. And we can just say, revenue minus cost. If we click OK, now we can use these guy revenue minus cost, that will be our profit, we can, of course, rename this to profit.

 

2:25

And the third way is the one that you can right click here on the any attribute, or you can also click here and these three dots and click on Create Metric. In this function editor, we can provide a metric name, a description, you can change the aggregation function. And you can also select here the metric that you want to aggregate, maybe using a max function. Let’s say we want to see the max of the cost by a specific level. So you can change at the visualization level from that to by columns for example. You can also add a filter. And you can switch to the formula editor where you can see everything that you did in the function editor.

 

3:19

It represents integral expression here in this window. If we just say max cost or the name of this metric, and click Save. Now if we double click, we’ll see the maximum cost that we have available in this visualization. So if we just saw cost descending, we see that 533 is the max cost that we have in our dataset. So that wraps up the way that we have to create derived metrics.

 

3:56

And now let’s create a derived attribute. We can do the same way as we did for the derived metric. You can right click on any object that you have in your dataset and create attribute or you can use this three dots here and select Create Attribute. In this window, we can define the attribute forms for a derived attribute. We can have maybe two attribute forms, three as many as you want here. In my example, I will just use the ID. That’s the required one. And we want to change the month integer to say the month name so we can use the case function and just say month at ID equals to one. Let’s say January. We can do keep adding here conditions, February. So we can keep adding here, but I have the formula ready for us. Let’s just copy and paste.

 

5:09

So for every single month number, I’m going to change from the number to the month name. So month equals to 1 January until December, let’s say month name, or the attribute name. And now, you see that we have a new object here same as the derived metric, we have fx, but now it’s this green icon here. And that if we double click, now we see the month name, instead of the month number, we can remove the month number. And now we have something that makes more sense. And it’s easier for the user to visualize and analyze our visualization. So those are the ways that we have to create the right metrics and the right attributes in MicroStrategy.

 

6:06

If you have any questions or suggestions for future videos, please comment below. Don’t forget to subscribe to know when future videos are posted. Thank you for watching.