So, there was this tiny little report that used an element prompt, and that prompt contained a list of default answers. Nothing fancy so far. However, it has come to my colleague’s attention that this report had turned rogue… How else could we explain that it started returning result sets for attributes that weren’t even chosen by the user when answering the prompt?
Well, there is a simple explanation, though it did required a little bit of outside of the box thinking. MicroStrategy stores the prompt default answers (ID and DESC) exactly as they were the day the prompt was created or modified. But what if something untoward happens to the lookup table, something like modifying the lookup value for a certain ID?
What happens is that while you distinctly remember choosing “Books” you end up seeing the sales for “Beverages”, and that is because the SQL Engine will write the WHERE clause with its mind on the ID not on DESC field. Since you selected “Books” and this lookup value corresponded to “1001”, then the SQL will filter on “1001”. Then, in the last step it will perform a join with the lookup table and get the description of “1001”, which now is “Beverages”.
This is not only for MicroStrategy. It applies to all tool.
ReplyDeleteThen I suppose we should regard it as a feature rather than a bug :)
ReplyDeleteits quiet normal. before you change lookup table IDs/ Elements, you have to check its dependency in MSTR.
ReplyDeleteNow that I know this is the way it works I can agree that it can be thought of as normal. On the other hand, I was kind of expecting MicroStrategy to query the default prompt answers at every runtime, the same way it does for the non default values. It is not a consistent behavior, but it's the best we've got :)
ReplyDeleteThis is a know defect in MicroStrategy
ReplyDeleteTN5700-80X-2976 : Attribute ID data is modified in the warehouse table, but the modified data is not reflected in the WHERE clause of the report SQL in MicroStrategy Desktop 8.0.x