Tracking Visualization - No Custom Plugin

It's been a long time since I haven't posted any unique/innovative ideas but Today I come up with one interesting requirement to show the Tracking of Data like how we generally see on any Ecommerce website/app. 

Similar to below image:

There may be a scenario where you want to track the data transfer of different layers where it stored and see the quality of data for example, in Data quality Monitoring Dashboard. 

Like Below:

In this type of scenario, above visualization give a good impact on the analysis. A overall picture in one single visualization.

Now let's come to MicroStrategy, how we can have the above visualization without a need of Visualization Plugin.

Firstly, as we know any custom visualization requires coding and for above visualization also we need coding but you need only HTML and CSS code. Above visualization will not be used as a selector, because this is not a plugin.

Also, you should have a count pre-defined steps of check i.e how many layers in the tracking viz. (i.e 2, 3, 4). In my case it was 4 layers of checks I had to do.

Now let's begin with the steps to create this in MicroStrategy:

1. There are bunch of HTML and CSS code which we have to use. CSS code will be inserted in a HTML container and HTML code will be used in a Derived Metric. All the code will be available in GitHub link.
2. So, firstly you have create a MTDI cube with the sql code mentioned in the GitHub link. All the columns will be attribute and change the data type to HTML Tag.
   
3. Now, include a HTML container in dossier and add all the CSS code mentioned in the GitHub link.
4. Then, include the MTDI cube created in step2 to Dossier and create Max(attribute){~+} for all attributes.
5. For the indication i.e green and check mark if the data transfer is correct and orange and cross symbol if it is not.
   Firstly you have to create all the base/supporting metric which does this checks. i.e create a metrics for comparing the number of records and compare the max updated date (i.e if both, number of records and date are matching then green/check else orange/cross ).
   In my case I have created 3 metrics which does those check i.e from Raw layer to Staging, from Staging to MSTR source Table and MSTR source table to MSTR Cube.
6. Once you created all your supporting metric, now it's time to create the Derived Metric which create the logic to create the tracking viz. In this Derived Metric you have to use all the metrics which you have created in step4.
   
   Basically we have to build the below HTML code in Derived Metric using all metrics created in step4 and all supporting metrics created in step5:
   
   HTML Code:
   
   The highlighted ones are your main KPI values of that layer.
   
   
   
   
   
   
   
   Derived Metric Logic to build above HTML Code:
   
   Highlighted in green indicate it is HTML tag metrics created in step4 and all blue color indicate it is supporting metric created in step5. and all Meganta color indicate it is actual KPI of that layer.
   
   Concat([Max - Data Tracking Div Containe], Case(([Raw Layer - Up to Date or Not]=0), [Max - Data Tracking Matched Div], [Max - Data Tracking Not Match Div]), Case(([Raw Layer - Up to Date or Not]=0), [Max - Data Tracking Span Tag Matched], [Max - Data Tracking Span Tag Not Matched]), [Max - P Tag Start], "Raw Layer", [Max - Line Break], [Max - Default Span Tag Start], Date([Raw Layer- Max Answer Date]), [Max - Span End], [Max - Line Break], [Max - Default Span Tag Start], [Raw Layer- Interview Count], [Max - Span End], [Max - P Tag End], [Max - Div Tag End], Case(([Staging Layer vs Raw Layer Overall Status]=1), [Max - Data Tracking Matched Div], [Max - Data Tracking Not Match Div]), Case(([Staging Layer vs Raw Layer Overall Status]=1), [Max - Data Tracking Span Tag Matched], [Max - Data Tracking Span Tag Not Matched]), [Max - P Tag Start], "Staging Layer", [Max - Line Break], [Max - Default Span Tag Start], Date(staging_layer_max_answer_date), [Max - Span End], [Max - Line Break], [Max - Default Span Tag Start], staging_layer_interview_count, [Max - Span End], [Max - P Tag End], [Max - Div Tag End], Case(([MSTR Source Table vs Staging Layer Overall Status]=1), [Max - Data Tracking Matched Div], [Max - Data Tracking Not Match Div]), Case(([MSTR Source Table vs Staging Layer Overall Status]=1), [Max - Data Tracking Span Tag Matched], [Max - Data Tracking Span Tag Not Matched]), [Max - P Tag Start], "MSTR Source Table", [Max - Line Break], [Max - Default Span Tag Start], Date(mstr_source_table_max_answer_date), [Max - Span End], [Max - Line Break], [Max - Default Span Tag Start], mstr_source_table_interview_count, [Max - Span End], [Max - P Tag End], [Max - Div Tag End], Case(([MSTR Cube vs MSTR Source Table - Status]=1), [Max - Data Tracking Matched Div], [Max - Data Tracking Not Match Div]), Case(([MSTR Cube vs MSTR Source Table - Status]=1), [Max - Data Tracking Span Tag Matched], [Max - Data Tracking Span Tag Not Matched]), [Max - P Tag Start], "MSTR Cube", [Max - Line Break], [Max - Default Span Tag Start], Date(mstr_cube_max_answer_date), [Max - Span End], [Max - Line Break], [Max - Default Span Tag Start], mstr_cube_interview_count, [Max - Span End], [Max - P Tag End], [Max - Div Tag End], [Max - Div Tag End], [Max - Div Tag End], [Max - Div Tag End], [Max - Div Tag End])
   
   
   
   
7. One more thing you have to adjust in the CSS code which you added in HTML Container in step3 i.e if you have 4 layers then make the width to 25%. which means you have to evenly distribute all stages by diving 100/(count of stages)
   
   
   
   
   
8. Once you are done with all the steps mentioned above, you will see your visualization is ready.

GitHub Link: https://github.com/AmitSah/Tracking-Visualization---No-Custom-Plugin

Please like and subscribe to the channel to get more tips and tricks in MSTR and comment if you have any queries. Thanks, Amit