Tag Archives: metrics

Changing the value format of several metrics at the same time

Hello there,

Have you ever needed to change the formatting of several metrics to Percentage, for example? It’s a tedious process and it can be painful.

Hopefully there is a way to change them all with just a few clicks:

  1. Go to MSTR Developer;
  2. Click on Tools -> Find and Replace…
  3. Select “Metric formatting”
  4. Select the metrics using the search
  5. Click Update Summary
  6. Check: Set the formatting properties individually
  7. Check Metric Values
  8. Click Formatting
  9. Format your metrics accordingly

Hope it helps.

God bless you

Think

Hello there,

I’ve been trying to drive people in my team to think and not just do what they are asked to do. I want to do exactly the same here in this post. Show you that think is the best way.

Today, people are driven to do the tasks and sometimes they don’t find space to think on what they are doing neither better ways to do it.

When you have to create several metrics, what do you normally do? Probably you check if the project has the facts and attributes and you start building the metrics as there were no tomorrow.

Let’s think about this metric creation task. Have you ever thought that we could create metrics using the advanced metric option in MSTR Developer and that could save a huge amount of time?

Last week, I had a task to create approximately 80 metrics and I realized that I could do it in a better way. See that I used the word realized? Instead of just start doing the task, I first stopped for a moment, and thought in better ways to perform those creations. The metrics needed filters, a count function and the value format to be as a percentage.

1. Filters: do you know that filters can be created using command manager? Yes, we can do it with CM. Here is the script:

CREATE FILTER “NameOfTheFilter ” IN FOLDER “\Public Objects\Filters” EXPRESSION “Attribute@DESC= ^” value to be filtered ^” ; ON PROJECT “MicroStrategy Tutorial”;

With this script, you will save a lot of time right clicking and left clicking your mouse to create and save a new filter. Using the advanced filter in Developer, you can add the metrics/filters/transformations and MicroStrategy will create the combination of them with just a single click of your mouse. This feature will let you go home earlier that day.

2. Base formula: Think about creating a base formula for your metrics. This way you won’t need to create a new expression every time that you have to create a new metric.

In java, we are driven to reuse the classes and method. It’s not a good idea to create new line of codes if you are have them. Reuse, don’t rewrite the same piece of code multiple times. That’s the reason why Jave has methods.

The same concept applies to MicroStrategy. We are good to say that with MicroStrategy we can reuse objects. That’s not stands true if you don’t use it.

Let’s think about using base formulas to create the metrics so they will be consistent and we can reuse the formulas in multiple objects.

3. Find and replace: do you know that we can format a bulk of metrics with just a few clicks? If you go to Tools -> Find and Replace in Developer, you will see that we can easily change the format of the metrics. This way, you will also save a LOT OF TIME.

This is just an example that confirms that think and do is better than just do.

I hope I’ve encouraged you to think…

God bless you.

Converting seconds to time format

Hello there,

Today let’s talk about a quick tip: how to transform seconds to hh:mm or even hh:mm:ss.

You just have to:

  1. Create a new metric using your seconds metric and divide it by 86400.

Example of the formula would be: [metric with seconds] / 86400

2. Format the number to be like [h]:mm or [h]:mm:ss

 

That’s it. You will now see your seconds in a time format.

Hope it helps!

God bless you!

Number format in MicroStrategy

Hello there!

My friend Diogo Martins, told me a simple but powerful custom formatting to numbers that can help you so much!

Here is the tip:

Think that you have a Profit metric with a number like this: 1.304.141

And you want to divide that by 1000.

You just have to:

  1. Format the value and go to Custom
  2. Add a format like: 0,

Capturar

And now you will get your number like: 1304

The trick is the comma (,) at the end.

If you add 1 comma you will dividing by 1.000

If you add 2 commas you will dividing by 1.000.000

And so on…

So, just add commas after the 0 (for number) and you will format your number in a much better way than dividing it in your metric.

Hope it helps!

God bless you!

Adaptive Metrics

Hello there!

Today let’s talk about how to create different aggregation formula for each fact table used in a fact in Microstrategy.

Let’s say that we need to create a SUM(value) for Fact A and a COUNT(value) for Fact B. When we get data from Fact A, Microstrategy must SUM, when in Fact B, Microstrategy must COUNT.

To do that you just have to:

1. Create a new Fact;

2. Create a new expression with:

ApplySimple(“Count #0”, FIELD1) 

Only use Fact B for source tables.

3. Create a new expression with:

ApplySimple(“Sum #0”, FIELD2)

Only use Fact A for source tables.

pic

Save your Fact as: Fact_Test

4. Create a new Metric with:

ApplyAgg(“#0”, Fact_Test)

pic

5. Update your Schema.

 

That’s it.

Hope it helps

 

God bless you!

How to show the parameters set in metrics

Hello there!

Today I’m going to teach you a quick tip that can help you see the parameters that were set in a metric.

The default behavior of MicroStrategy is the hide all parameters set for your metrics. it’s much better to see everything that you are using.

To do that you just have to:

1. Open Developer/Desktop and edit a metric

2. Go to Views -> Show Function Parameters

pic

Now you will see every parameter that you metric have.

pic

 

Hope it helps!

God bless you!

Changing Metric Column Alias Name

Hello there!

Today let’s talk about changing the metric alias name in the SQL that MicroStrategy generates.

Sometimes you need to debug the query and you see something like this:

select name_pu as name, sum(salary) as #MAS01MQW from Fact_Table group by name_pu

If you have a lot of metric inside your report, it is very hard to know where is a specific metric in the query.

You can change that machine metric alias name to a human metric alias name using Metric Column Name.

To do that, you just have to:

1. Edit your metric;

2. Go to Tools;

3. Go to Advanced Settings;

4. Choose “Metric Column Option

pic2

Then, you just have to type a different name in the “Column Name used in table SQL creation“, like the image below:

pic1

Now your SQL is going to be something like this:

select name_pu as name, sum(salary) as Name_of_Metric from Fact_Table group by name_pu

That’s it.

Hope it helps!

God bless you!