×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using ID Attribute Form as column in SQL WHERE clause

Using ID Attribute Form as column in SQL WHERE clause

Using ID Attribute Form as column in SQL WHERE clause

(OP)
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

RE: Using ID Attribute Form as column in SQL WHERE clause

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.

RE: Using ID Attribute Form as column in SQL WHERE clause

(OP)
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

RE: Using ID Attribute Form as column in SQL WHERE clause

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.

RE: Using ID Attribute Form as column in SQL WHERE clause

(OP)
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

RE: Using ID Attribute Form as column in SQL WHERE clause

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

RE: Using ID Attribute Form as column in SQL WHERE clause

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

Chael

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close