BO SQL Query builder (MSSQL)

[JohnLittle]

Hello everybody!

Could you anyone help me with the following? This must be interesting for eveyone, I believe, and I am probably newbie in BO not knowing that yet.

All of you knows how to create a report in BO, right? Simply using drag&drop... But when you set the query filters, there is a worry behind: using filters above very big tables may require creating a special SQL indexes in db schema.
Now the question is: is there any way how to set BO query builder/designer to replace/substitute selected filter with indexed field?

Simple example:

SELECT
a.BusinessTreeName,
b.PositionDescription,
c.CustExtAttValue
FROM
dbo.A021_InternalOrganization a,
dbo.A021_Position b,
dbo.A011_CustomerExtendedAttributes c
WHERE
a.BusinessTreeId = b.Position_BusinessTreeId
AND
( dbo.A011_CustomerExtendedAttributes.CustExtAtt_CustomerDwId=dbo.A011_Customer.CustomerDwId )
AND ( dbo.A011_Customer.CustomerDwId=dbo.A011_CustomerInternalOrganization.CustomerIntOrg_CustomerDwId )
AND ( dbo.A021_InternalOrganization.IntOrgDwId= dbo.A011_CustomerInternalOrganization.CustomerIntOrg_IntOrgDwId )
AND ( dbo.A021_Position.PositionDwId=dbo.A021_InternalOrganization.PositionDwId )
AND
(
dbo.A021_InternalOrganization.IntOrgBusinessTreeName = 'BUSNTREE01')
AND
dbo.A011_CustomerExtendedAttributes.CustExtAttDescription = 'National Key Account'
)
GROUP BY
dbo.A021_InternalOrganization.IntOrgBusinessTreeName,
dbo.A021_Position.PositionDescription,
dbo.A011_Customer.CustomerCode,
dbo.A011_CustomerExtendedAttributes.CustExtAttValue


As you can see for the query above, there are Name and Description fields used for filtering (and agregation), which slow down significantly performance of query.
Could you please advice me on it?
Thank you very much,
Jan

 

[skrandel]

In and end-user environment, you need to have more indexes. Often times these indexes will be on text fields like this.

Also, in BO XI r2, there is an index aware functionality. It exists in 6.5, but only works with client and only then when a user uses a report-based condition that does not prompt.

Steve Krandel
Symantec

 

[blom0344]

A011_CustomerExtendedAttributes sound like a dimensiontable to me. If you have to work without proper indices for the description fields, then try to get people to use the indexed primary key on this table.
You can make life easy for them by changing the LOV for the indexed field to show both the key (often meaningless) and the description.

There is probably no way to get every field indexed that might be used by casual users. Anyway, nested 'or' expressions often result in indices being skipped in any case.

Ties Blom

 

[JohnLittle]

Thank you very much both of you.
Actually I did not know about LOV. Could you point me to the documentation?
Thank you very much,
Jan

 

[blom0344]

Designer guide, search for lov.
Basics: assign a list of values on the prim key and then modify the key to show both it's value and value of a description.

A user will see both the code and description in query panel when using the values option...

Ties Blom