Force Microstrategy to use specific table in join

Question

Sometimes, when I am creating reports, Microstrategy takes the wrong table for join (in case of using fields in report that appear in more than one table). For example if I have fact_table and fact_table_month, and they have the same fields, Microstrategy may take the fact_table_month for join when i need it to take tha fact_table.

i know about the possibility to create a dummy metrics and use them in the report. I know about the possibility to manually change the logical size of tables, but i am looking for an official and proper way to solve this problem in MSTR.

How can i force Microstrategy to take the table i want in join? how can I tell MSRT: ok, for this report use one table , and for that report use another?

Answer 1

MicroStrategy SQL Engine is dimensionally aware of the structure of your hierarchies. So if you have defined your attribute relationships, MicroStrategy should select the right fact table.

If your fact_table_month and fact_table have the same attributes and metrics, then it means the two tables have the same grain, so they are the same for MicroStrategy. If you think that metric A in fact_table_month is not a monthly aggregation of metric A fact_table then or the name of fact_table_month is wrong or your metrics should be two different facts and metrics.

In the past when I had a similar problem, the daily table populated from a system and the monthly from another, I solved using different metrics, unfortunately this didn't allow me to drill down easily.

Among the "tricks" to force the SQL Engine to use a specific table (beside logical size and a specific table), you can also add a specific attribute to report objects: an attribute present only in the lowest level of aggregation it's enough to hit the right table without additional dummy objects.

Answer 2

The best way always depend on your project and reporting requirements.

If you want precise control over your report then you can go for "Free Form SQL" report which requires you to manually write the SQL and use whatever joins you want.

Another way is to use the Lowest level attribute in the report object pane. Make sure that this attribute is stored at the specific level in the Fact table which you want to join.

Also you can take advantage of Metric Dimensionality i.e Filtering = None and Grouping = none whenever needed to join a specific fact table.