databasedata-warehousemicrostrategy

In MicroStrategy, how can i create a Metric over 2 Attributes which have the same LookUp table?


I have 2 attributes A and B which have the same lookup table. I want to count the number of occurrences of each value for each attribute. The report and DWH would look something like this :

enter image description here

Metric A would count only occurrences for Attr A and Metric B for Attr B.

I believe there are solutions that would require modifying the DWH structure (creating a fact table for each attribute and a column for each value) or creating this report in a document in which each row or metric is an underlying report for each attribute. The problem is these solutions have drawbacks.

My question is if it would be possible to create this report just playing with metrics and custom groups.

Thanks in advance.


Solution

  • Assuming that you Know how to create a count Attribute metric...in the metric editor highlight the word count and right click the mouse. In the menu select Count Parameters. the second option down is "FactID". using the drop down menu, select the fact table where the attribute resides. here is a link to a TN that will help. https://community.microstrategy.com/s/article/KB41812-What-do-each-of-the-Count-parameters-control-in-MicroStrategy-Metric-Editor

    this looks like a classic Airport arrival/departure city layout. Actually in these cases I create a table alias to reduce the risk of confusion and MSTR engine mistakes.

    Hopefully I understood the question and that this is helpful