MicroStrategy Pass Through Functions

12/11/2021

Good morning everyone, one more week here we are with our #BestInMicro! A space where we get together to see curiosities, share experiences and pass tips to be the coolest and most knowledgeable consultants.

This week I bring a very useful functionality in MicroStrategy: Pass-through functionalities, which allow us to add by hand a query (or, rather, part of the query) to give a little more flexibility to the query we want to create or use.

Introduction

Also known as “Apply” function is functionality that MicroStrategy has, which allows us to manually write a query in the database. It has the feature that allows us to put variables or parameters in the query, so there is even a little more flexibility when using it.

Where and what are they used for?

This depends on the function we are using (we explain each one below), but, for example, the ApplyComparison, appears mainly when defining filters, either integrated within a report or an indicator or as a filter object per se. On the other hand, ApplySimple is more related to defining indicators or attributes. Actually, it can be used anywhere where you need to do a manual injection of code to be used in a query.

To simplify the explanation, this function is used to apply non-standard SQL expressions that MicroStrategy does not support or does not support.

Types of variables

Before going into the details of the types of Apply Functions, I will point out that these functions require certain variables to be passed as parameters. That is, we will create a SQL query and pass an attribute or value that will be dynamically embedded in our query.

For example, if we do the function ApplySimple(“days_between(sysdate,#0)”,Date@ID), we will be getting the days that have passed between today and the date we are using in the date attribute… get it? We put between quotation marks the query that will be injected with its variables (which will be defined with a # and a number, which will define the order), and then, separated by commas, the fields that will fill these variables.

And here comes an important trick, so stay tuned. It is possible to use prompts instead of attributes. But, for this, instead of using the expression #n we must use #En, indicating that we are inserting an element of the attribute. Let’s go to the example:

Here I am using a function that uses the Month attribute and replaces it in the first parameter, while using a prompt for the second. A prompt returns an element, a value

This query would end up being translated as follows:

What functions are available?

Depending on the situation, we have different types of Apply functions. Here I explain which ones exist, how they are used and what they are for. To see examples of the structure and go into more detail, I recommend that you take a look at this technical note from MicroStrategy.

ApplySimple

It is used to create different MicroStrategy objects. It is used to insert non-addable values (for those there is the ApplyAgg) and to use native functions of the SQL engine we are querying.

For example, here we see how to create an indicator based on the greatest formula and pass it to different parameters.

ApplyComparison

This function is mainly used in advanced filters. Allows us to handwrite the condition that will appear inside the where. It is important to have the advanced filters enabled by going to Tools > My Preferences > Editors > Filters > Show advanced qualifications.

In this example, we use the ApplyComparison to add the filter that the description of the customer city attribute is the same as call center

ApplyOlap

This is used when analytical processing functions are required. Recall that we refer to performing sorting, aggregation, and dynamic operations.

ApplyLogic

This function is used in filters when logical operators (e.g. AND, OR, and NOT) need to be implemented. It is characterized by a Boolean response, i.e. true or false.

ApplyAgg

These functions are used when aggregate functions (e.g. Sum) can be used.

Conclusions

These functionalities are very useful in different scenarios, especially when you have some complex data to extract through MicroStrategy’s SQL engine, or when you simply have limitations when creating objects.

It is important to consider the language in which you write, as mentioned above. It is not the same to make a query that will be executed in an Oracle than in a Teradata, for example, so it is necessary to take special care in these aspects and to analyze well how the query is generated.

Anyway, I hope you found this article useful and I look forward to seeing you next week for a new article!