gsaiganesh

 Profile Answers by gsaiganesh 

• Jul 10th, 2006

 

If you need to use a specific table , we need to play with the logical size of that table. Whenever MSTR frames a query it retrieves data from the table that has the least logical size and hence when you set the logical size of your table to be least compared to the other base table, MSTR will retrieve data from this specified table.

Hope this helps

Thanks,

SaiGanesh

Phillip Green

• Sep 26th, 2006

 

I would use level metrics to force the SQL generation (remove the report level from the metric and add level on ALL the dimensions from the fact table you want to generate SQL against).  By doing this you will only affect the report that is using the metric.  If you start altering the logical table size you will affect all reports.

jpg1dmg

 Profile 

• Sep 26th, 2006

 

I would use level metrics to force the SQL generation.

• Remove the Report Level from the metric(s)
• Add Leveling to the metric(s) for each of the Deminsions/Attributes in the Fact Table you are trying to force in the SQL

By doing this you will only affect the report that is using the metric.  If you start altering the Logical Table Size, you will affect other reports.

Aashish

• Nov 21st, 2006

 

Use FACT ID in the metric

Shikha Sood

• Aug 15th, 2014

 

Changing the logical size of the table is not considered as a good practice as it can degrade the performance of your reporting...
So, its better to use FACT Id so or adding the lower level atrribute in the report object....

praveen

• Aug 20th, 2014

 

Use fact id in the metric and give filtering=none and grouping =none

praveen

• Dec 22nd, 2015

 

By using dynamic aggregation also we can direct the SQL to use a particular table

Abhay

• Oct 19th, 2016

 

For every table created in Microstrategy, there is a property called Logical Table Size. There is a formula to calculate the Logical Table size ( Level of the table in hierarchy / Total number of tables in hierarchy)*10. So in order to force the MSTR SQL engine to use a specific table, its logical size should be less than the table that it is using currently. You can edit the size of table and ensure that you lock the Logical table size. In case you dont lock the table size, when you update the schema, the logical sizes get re-calculated and the value that you entered is overwritten, resulting the SQL engine to continue using the current table itself.