Device specific logic in Microstrategy

Sometimes you may need to set device specific logic in your microstrategy documents or reports. For example, some VI charts show different labels or different headers for iPad and you may want to hide them in iPad using a rectangular label. Or, you may not want to allow iPad users to click a certain button or link and at the same time you want to allow desktop or notebook users to click that link or button. In these cases, you can use a system prompt called “Mobile ID” to find out if the user is viewing your report from an iPad or from a desktop.

First, create a metric using this system prompt. Just put ?[Mobile ID] in your metric formula.


Let’s say this Metric’s name is “DeviceID”. Now, put this metric in a dataset. When you run this dataset, it should give you user’s mobile device id. When you run this from a PC or notebook, it will give you some default digits – “1234567890123456“.


Now you can use this metric (“DeviceID”) in conditional formatting inside your documents. You need to write something like…

If (DeviceID = 1234567890123456) then hide

else show

Hope it helps. ūüôā


Posted in Microstrategy, MSTR | Tagged , , , , , , , | Leave a comment

From SQL to Spark

When I started learning spark, I decided to learn it from the other way round. That means, rather than going through a step by step journey, I decided to learn it from BI reporting perspective. I took a table with some data. Then, I tried to answer some common reporting queries by using python+Spark. It was simple stuff but I decided to post it in my blog as it may help other people from BI/SQL background who are wondering where to start from.

My table looks like below. Each territory has some bricks and each brick has some sales and target amount.


So, first question is which territory has the highest number of bricks?

It’s a simple group by/count problem in SQL. But pySpark is quite simple as well.

df.groupby(‘Territory’).count().orderBy(‘count’, asending=False).show(1)


Second question is, which Territory has the highest target attainment? Here, we have to calculate sum of sales and sum of target for each territory and then divide sales by target to find out attainment percentage. Again, pySpark has some simple solution:


from pyspark.sql.functions import sum
from pyspark.sql.functions import format_number
df.groupby(‘Territory’).agg(format_number((sum(“BrickSales”)*100/sum(“Brick Target”)),2).alias(‘Attainment %’) ).show(1)


Finally, we will try to make a flat structured table a hierarchical one. Let’s see the input table.


We have a hierarchy here; National->Territory->Brick. We will now make this table a classic hierarchical parent-child table. So, our expected table will be like this:

Child                            Parent                        Sales

AB16                             Terr0032                   0

BA06                             Terr0003                   374.0941667

Terr0032                      40012                          XXXX

Terr002                        40012                          XXXX

To solve this problem, we will create two dataframes and then make a union of them.

from pyspark.sql.functions import sum
df_1 = df.withColumnRenamed(“Brick”,”Child”).withColumnRenamed(“Territory”,”Parent”).withColumnRenamed(“BrickSales”,”Sales”)
df_2 = df.groupby(‘Territory’,’NATIONAL’).agg(sum(“BrickSales”).alias(‘Sales’) ).withColumnRenamed(“Territory”,”Child”).withColumnRenamed(“NATIONAL”,”Parent”)


Posted in Spark, Python | Tagged , , , , , | Leave a comment

Visual Insight charts inside Microstrategy documents

Microstrategy visual insight has lots of exciting beautiful looking charts. But many of them are not available in documents. You can import them into document following the steps described below. But, do not do this unless you really have to do it. Because, those imported charts become difficult to maintain inside document. most of the time, if you change any property (or change threshold etc.) that chart turns into a mess. And, you have to import it again.

So, here are the steps to import VI charts into document:

  1. Create your desired chart in a VI dashboard and convert it into a document. Then save that document.
  2. Now open your original document where you want to show the VI chart. Now, click on ‘Layout’ available in Insert menu at top menu bar.


3. Now, you will get a file browser where you have to select the document saved in step 1.

4. Now, that document will be imported into your original document. See ‘Layout’ tab. You now have two tabs. Now, all you have to do is to go to the second tab, copy your VI chart and paste it into your first tab (‘Layout1’).


Caution: Imported layout’s dataset is also imported into your original one. You will not get any notification. Often, it creates mess.

Posted in Microstrategy, MSTR | Tagged , , , , , | Leave a comment

Metric calculation based on selection in Microstrategy

Hello guys, I am back after a long time. I was learning Microstrategy during last one year. So, here I am, writing something on mstr.

Today, I am writing about a problem that almost every mstr developer faces. Let’s say, you have two base metrics: dollars and units. Now, you like to calculate Market Share (MS) based on these two base metrics. Everything stays fine up to this point. But, problem starts at dashboard or document side. It is very likely that your client would like to have one selector in web document to choose between dollars and units. And MS should be calculated based on his selection (either dollars or units). Given below is a snapshot which will make everything clear.

Usually, this problem is solved by using panels with a panel selector. For two base metrics, you have to take two identical panels. Both panels will be exactly same from visualization point of view. But, they will be using two different metrics. One panel will use MS_Dollars and the other one will use MS_Units. That means, whenever you need to change anything, you have to do it in two places. Imagine what would happen if you have three or four base metrics? You will end up copying the same panel three or four times. In one of my projects I had six identical panels (yuuuk..)!

Following is my workaround to avoid creating multiple identical panels; although with a small glitch…

  1. Add one table called DimFlag into warehouse catalog. It will contain two records:¬† ‘Units’ and ‘Dollars’


2. Create an attribute called Flag. This attribute doesn’t have any relationship with any other table in my schema.


3. Create a metric called Flag using the attribute created in previous step.


4. Create one metric called MyMeasure. This metric holds key to our solution. This metric returns Dollar metric if Dollar is selected from flag attribute and it will return Units metric if Units is selected from flag attribute. Note that Flag ID has been used in if condition instead of flag description.


5. By the way, we have another attribute called country. We want to show each country’s sales (dollar or units) and its corresponding share (%) in our web document. We need to use level dimensionality to derive ‘market sales in units’ and ‘market sales in dollars’ first. When I say ‘market’, I mean summation of all available countries. So, our level dimensionality will make sure Country attribute is ignored as ‘filter’ and ‘GroupBy’. This level dimensionality is not related to our main solution. Our main trick is the ‘if’ condition that was demonstrated in previous step. Finally, we will create another wrapper metric called ‘Market Total’ which will contain an ‘if’ condition just like the one in ‘MyMeasure’ metric created in previous step.


6. Now, we are ready to create a dataset. Given below is a snapshot of our dataset. If you look at corresponding generated SQL, you will notice a cross-join. It’s because Flag attribute is not connected anyway to our facts. This is the small glitch I was talking about. But, we can live with a cross-join for small scale projects where data volume is not that big and generated SQL is also simple.


7. We are almost done. All you have to do is to import this dataset into your document. Create a selector from Flag attribute, create a grid table using MyMeasure and MarketTotal metric and Country attribute. Finally, make this grid a target for your Flag selector. Metrics calculation will happen based on your selection in selector as per the ‘if’ condition defined inside Metric definitions.

Hope you’ve understood my idea. Get back to me for any question.


Posted in Microstrategy, MSTR | Tagged , , , , , | Leave a comment

What I have learned in the name of unit testing of ETL, DWH, OLAP Cube and MDX

In one of our projects, we ‘somehow’ decided that every part of the system should be ‘unit test’-ed . It was a classical ETL->DWH->Cube project where web dashboard was built to show BI reports from cube (using MDX as reporting language). ¬†Off course, unit testing was nothing new to¬†our web¬†developers. But, our data warehouse developers had to figure it out.

Let’s take a simple example; we have a function in our ETL process that joins two table and returns the result set. The only way to test this function is to verify its entire output data. But, unlike a programming language where expected output of a unit test remains fixed, this simple function’s expected output cannot remain fixed. Because it depends on input data. The only way we can keep it fixed is to keep the input data fixed. Hence came the idea of ‘frozen environment’. So, all our data should be frozen as long as we are doing development and this way we can pass the unit test. Now this is where it gets difficult. Because, we can never make our system ‘go live’ keeping every data in a frozen state. ¬†It has to change every now and then. And, every time input data changes, we need to change expected output data as well.

Another thing is, it¬†hardly happens that we are changing something in our ETL flow and not expecting any data change in the system. In ETL we mostly do¬†data manipulation, isn’t it? What kind of change we can do in a lame ‘join’ where we do not expect any data change in the end? Yes, performance tweaking, CLRs, string formatters may be some¬†of those but not much other than that. So, we end up changing the unit test¬†almost¬†every time we change anything in the flow. Otherwise, unit tests will fail. So, more and more it is looking like data testing rather than unit testing.

All of above is also applicable for data warehouse, olap cubes and MDX. Actually, unit testing is something meant for programming languages. Cube or sophisticated ETL tools are more like design tools doing mostly data manipulation; therefore will not behave like a programming language. Personally, I will always call it ‘good old data testing’, not ‘unit testing’ anymore.

Posted in SQA, Testing, SQL, Database | Tagged , , , | 1 Comment

Common Latest Period in SSAS Cube

Recently I came across a situation where data was being delivered at different times for different countries and client wanted to see data for latest common month in Region/Continent level reports.¬†Data arrive on monthly basis, but it arrives at different dates for different countries. So, at one point in our data warehouse, we have Dec-2014¬† data for UK and Qatar. Rest of the countries have fallen behind them (having data for up to Nov-2014, Oct-2014 etc.). Let’s analyze in more details.

First, let’s go into the cube. We have a simple Geo hierarchy. Countries are at lowest level and its parent is Region (Europe, Mid East).


We have a simple month dimension as well. Its primary key is the digit produced by concatenating year with month. So, Dec-2014’s key will be 201412.


Let’s browse cube and see how data looks like…


Look at rows inside red colored zone. Different countries have different latest months. User¬†requirement is¬†to see latest month’s sales for¬†all geo levels.¬†It can be achieved in many ways. One way to achieve this is to create a calculated measure like this…

CREATE MEMBER CURRENTCUBE.[Measures].[Last Month Sales]

AS sum(

tail(nonempty([Dim Month].[Id].[Id],([Measures].[Sales],[Dim Geography].[Hierarchy].currentmember)),1),


,VISIBLE = 1 ;

If we drag this measure in cube browser, country level and region level sales will be like this:

CountrySales1 RegionSales1

It’s perfectly alright for country level sales . But, it will be interesting to analyze Region level sales. Actually, for regions, most of the business users would like to see common latest month’s total sales. In our data, Europe’s last month’s sales 4830 is probably giving the business user a wrong picture (of course it depends…. ), because only UK¬† data is available for Dec-2014. So showing a total for Dec-2014 may not make much sense. Latest common month for Europe is Nov-2014 and latest common month for Mid East region is Oct-2014. So, it would always make much sense if we show Nov-2014 total for Europe and Oct-2014 total for Mid East. To support this requirement of showing common latest month’s sales I have introduced another fact. This fact is nothing but a storage for country-wise latest month.

CubeStructure2 NewFact

This Fact table’s name is FactCountryMaxPeriod and declared a simple count measure called [Measures].[Fact Country Max Period Count] from it. We are almost done. Just declare a new calculated measure called [Measures].[Common Latest Month Sales] and see it¬†in cube browser.

CREATE MEMBER CURRENTCUBE.[Measures].[Common Latest Month Sales]


sum(nonempty([Dim Month].[Id].[Id], [Measures].[Fact Country Max Period Count]).item(0), [Measures].[Sales])

,VISIBLE = 1 ;

LastMonthSalesCountry LastMonthSalesRegion

This time, Europe and Mid East is showing total sales of Nov-2014 and Oct-2014 respectively.

Posted in MDX, SSAS (Analysis Service), Cube | Tagged , , , , , , , | Leave a comment

Slowly Changing Dimension in SSAS Cube


This is a simple example of SCD (Type-2) in OLAP cube.  We have a Dimension table for Employee and their Departments. This dimension is an SCD. One employee worked in different department over the course of time. EmpID is business key and SID is surrogate key.

DimEmpolyee table


We have another dimension called DimTime. It’s a very simple Month Dimension table.

Fact table looks like this:



Both business key (EmpID) and Surrogate key (EmpSID) is present in Fact table, but we will use only EmpSID key here.

Let’s have a look at the cube now. First, look at the Dimension called [Dim Employee]


And it’s attribute relationship…..


See carefully that I did not use SID in hierarchy. I used employee name (attribute called ‚ÄėEmployee‚Äô) for reporting purpose. Its key column and name column both are set to Employee column of the physical dimension table.

We are almost done. Let’s process the cube and write an MDX to see result:



You can clearly see that an employee named David sold different amounts in different months. David sold under Product Development Department for up to Aug-2013 and then he started selling under Android Apps department from Sep-2013.

Hope you will like it.

Posted in MDX, SSAS (Analysis Service), Cube | Tagged , , , , , , , | Leave a comment