Using ID Attribute Form as column in SQL WHERE clause

[mrdalton]

Does anyone know how to change Microstrategy so that it uses the ID form of an attribute rather than the Description form when generating and executing the SQL?

I have attributes set up with a 'Number' or ID form (1,2,3) and a 'Description' form ('USA','Canada'). My problem is that the SQL that is generated uses the description form for the 'where' clause, causing massive table scans and slow performance. Getting Microstrategy to use the ID form in the select would be ideal for my situation.

I'm sure there is a solution to this, but I've been unable to figure it out. Anyone????

Thanks!

Mark

 

[chael]

MSTR should use the ID by default. Make sure the attributes are defined correctly, update the schema, etc. If these are heterogenous column types maybe all IDs have not been defined. Also, make sure that the elements you are joining on both have IDs defined within their tables.

Smells fishy...something somewhere is not correctly defined...or the filter qualification is using a LIKE or something of that sort.

 

[mrdalton]

Thanks for the quick reply. I agree that something smells fishy; MSTR does use the ID's by default when it does the joining, but when I add a pre-defined filter, the SQL WHERE clause uses the description. For example, I have a report that generates a simple count of users by region. My report includes country and state, which are attributes. The SQL looks like this:

select a12.country country,
a12.city city,
max(a12.region_id) region_id0,
a12.county county,
a12.postal_code postal_code,
max(a12.region_id) region_id2,
a12.state state,
max(a12.region_id) region_id3,
a12.time_zone time_zone,
a12.area_code area_code,
sum(a11.realm) WJXBFS1
from user_fact a11
join user_region a12 on (a11.region_id = a12.region_id)
group by a12.country,
a12.city,
a12.county,
a12.postal_code,
a12.state,
a12.time_zone,
a12.area_code

This is all fine and good; the region_id in both fact and dimension are my primary keys and the queries are fast. However, when I add a pre-defined filter on State, which I've created using the filter editor, MSTR uses the descriptions for the WHERE clause. I choose 2 states from the list, and the SQL turns into:

select a12.country country,
a12.city city,
max(a12.region_id) region_id0,
a12.county county,
a12.postal_code postal_code,
max(a12.region_id) region_id2,
a12.state state,
max(a12.region_id) region_id3,
a12.time_zone time_zone,
a12.area_code area_code,
sum(a11.realm) WJXBFS1
from user_fact a11
join user_region a12
on (a11.region_id = a12.region_id)
where a12.state in ('FL', 'MN')
group by a12.country,
a12.city,
a12.county,
a12.postal_code,
a12.state,
a12.time_zone,
a12.area_code

Notice the "a12.state in ('FL','MN')" in the where clause. I'd like this to use the ID's specified in my attribute, if possible. Perhaps I've set up the filter incorrectly?

Sorry for the length of this post! If anyone out there has run into similar problems, I'd love to hear from you.

Mark

 

[chael]

Set up the filter to qualify on the ID instead of the description. You'd have to know the IDs of the states but your filter explicitly asks for the descriptions.

So, qualify on ID, use the exactly operator and then key in the ID of the state.

 

[mrdalton]

Thanks again for the excellent feedback on this issue. If I understand correctly, MSTR does not make the translation from the description selected in the filter to the ID stored in the database when doing the SQL? If this assumption is correct, is it possible to display both attribute forms on the filter, so that users could see both the ID and the description (so they wouldn't have to know the ID's of the states, for example), but have the MSTR engine use the ID for the SQL generation?

Thanks,

Mark

 

[chael]

If the attribute form has both an ID and a DESC then by default the ID will be used in the SQL. Within the attribute form editor there is a display tab -- this is where you can define what appears in the browse and the report displays.

It really sounds like you don't have ID in the attribute form editor, or that ID is in a different table from the desc (which doesn't make sense). You have indicated, however that this part is OK, so my next thought is that there may be a VLDB setting established either on a project or erport basis that is tripping up the query. Check your VLDB settings just to be sure.

If you want to take this off-line and send screen shots you can email me at cchristopher(at)atlanticintelligence.com

Chael

 

[chael]

One other thing...the DESC may be accidentally modeled as an ID. Check this too.

Chael