how to enforce DISTINCT in the sql view for Microstrategy report to remove duplicates

MSTR报表某个报表执行极慢，查看报表SQL，自动加了个distinct,而该报表数据量极大，

打开vldb属性，将之前的【使用默认集成值】勾掉，选择【无distinct,无group by】

 

By default, MicroStrategy considers that the attributes defined are the Primary Key of the respective Lookup tables. Hence, it does not generate a "Distinct" in the Select clause of the SQL.

Now, if you see that sometimes there is a "distinct" applied in the select clause, and sometimes its not,

then the reason for that can be two reasons,

1>The ID column is not set as "the Key specified is a true Warehouse Key" in MicroStrategy Logical Table. (See Logical table editor-> Logical View)

2>You have not set the actual Lookup table as the Lookup table for the attribute in the attribute editor, where you find unique values.

If you have set the Lookup table for the attribute, in which the ID column is not set as Key,

then you will have to set the VLDB property as mentioned above.

VLDB properties- > Select/Insert -> Distinct/Group By option.

By default it is set to Distinct.

Answer very specific to your question: "I need the final automatically generated sql view containing distinct(sql key word)"

------>You will have to uncheck the "The key specified is a true key in the Warehouse table" {depends on your warehouse} I think this is what you are looking for!

------>Set the lookup table of the attribute to a different table where it is not the key attribute (for eg. for Region attribute, set lookup as LU_District). { NOT Recommended. Useful in very rare situations}

 

原文出处

转载于:https://www.cnblogs.com/rambler/archive/2012/03/16/2400308.html