BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Lead By Knowing (Opens a new window)  

General Notice: No events within the next 45 days.

Using index awareness feature


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
vaidhyee
Principal Member
Principal Member



Joined: 20 Dec 2005

Posts: 125



PostPosted: Wed May 16, 2007 11:26 am 
Post subject: Using index awareness feature

Hi,

we have built a universe (BO XI R2) over a data warehouse db which is based on Star schema design.

For certain objects from dimension tables we have the primary and foreign keys defined as part of object definition properties in order to use the index awareness feature.

For e.g. we have a dimension for staff let us say DIM_STAFF with following attributes
STAFF_KEY, --> primary key and indexed in db.
STAFF_NAME, --> not indexed
STAFF_ADDRESS, --> not indexed etc ...

This table is joined with FACT table that has the STAFF_KEY as foreign key. likewise other dimensions.

In BO the attributes from DIM_STAFF are defined as objects and keys (primary and related foreign) are enabled.

Now when a query is formed the expectation is that when we place a condition in the STAFF_NAME (which is not indexed) the BO would rewirte the query by placing condition on the KEY although taking the name as input.
Is my understanding right? Do we have to enable some other parameter to make this working?

well the workaround could be indexing other important attributes but we really want to make use of the index awareness feature.
Back to top
jac
Forum Enthusiast
Forum Enthusiast



Joined: 10 May 2005

Posts: 2293
Location: London, United Kingdom


flag
PostPosted: Wed May 16, 2007 11:37 am 
Post subject: Re: Using index awareness feature

So you've gone into the object properties of the STAFF NAME object and defined the STAFF KEY for index awareness on the "Keys" tab? And ticked the "enabled" box? And in turn set up the staff key on the fact table as the foreign key?

If so, is this not working? If not, what is the error message? Or can you describe to us what is going wrong? icon_smile.gif

_________________
Miss J Middleton

Now contracting in SYDNEY, AUSTRALIA. Next availability : August 09, 2010.

Languages: Fluent (Native) English. Advanced Italian. Basic German.

BO: XIR2 & 6.1
DB: SQL Server 2005
Back to top
vaidhyee
Principal Member
Principal Member



Joined: 20 Dec 2005

Posts: 125



PostPosted: Wed May 16, 2007 12:57 pm 
Post subject: Re: Using index awareness feature

Hi,

I'm sorry should have described the problem with an example.

suppose i create a BO query based on the example 1 FACT_TABLE and 1 dimension DIM_STAFF (current set up without index awareness)

CASE-1

SELECT SUM(FACT_TABLE.COL1), DIM_STAFF.NAME
FROM FACT_TABLE, DIM_STAFF
WHERE FACT_TABLE.STAFF_KEY=DIM_STAFF.STAFF_KEY
AND DIM_STAFF.NAME IN('XYZ','ABC','PQR')

In DIM_STAFF table the records look as follows with STAFF_KEY indexed

STAFF_KEY NAME ADDRESS ..
1 XYZ ....
2 ABC ...
3 PQR ...

After the index awareness is applied my expectation is that the query would get transformed in BO internally as follows:

CASE-2
SELECT SUM(FACT_TABLE.COL1), DIM_STAFF.NAME
FROM FACT_TABLE, DIM_STAFF
WHERE FACT_TABLE.STAFF_KEY=DIM_STAFF.STAFF_KEY
AND DIM_STAFF.STAFF_KEY IN(1,2,3)

because running the above modified query and previously described query makes a lot of difference (especially when number of rows processed in FACT_TABLE is large).
The optimizer (oracle 10g cost optimizer) explain plan shows a cost which is substantially higher for CASE-1 rather than CASE-2 and it is quite expected.

The problem in our side is the queries are not pre built (fixed) but variable based on the prompt parameter. in this case NAME is given as a prompt for end user.
The example i described is fictional and in reality there are 10-15 dimensions simultaneously i need to connect accepting values from prompts.

Regards,
Vaidhy
Back to top
vaidhyee
Principal Member
Principal Member



Joined: 20 Dec 2005

Posts: 125



PostPosted: Fri May 25, 2007 5:08 pm 
Post subject: Re: Using index awareness feature

All,

an update on this issue, the first time it didn't work as we expected because of our coding method. We've used aggregate aware on the object (table column coming from different aliases) and the keys were defined directly on one table.
The query is formed fine as expected (joins based on primary key) eventhough the descriptive attributes are chosen in condition panel.

However the whole thing is bit inconsistent, meaning sometimes it works and sometimes it crashes the system. The universe appl hangs and has to be force quit.
Has anyone faced similar problems?

Regards,
Vaidhy
Back to top
vaidhyee
Principal Member
Principal Member



Joined: 20 Dec 2005

Posts: 125



PostPosted: Wed Jun 06, 2007 11:24 am 
Post subject: Re: Using index awareness feature

Hi,

Has any one has experienced above mentioned problem (designer/ webi crashes at times after turning this feature on)
I want to understand the following scenario while using index awareness and figure out if this is a reason.

Let us say we have the FACT_TABLE and DIM_STAFF as mentioned in example above. Suppose DIM_STAFF has the following entries

STAFF_KEY NAME DEPARTMENT
1 XYZ A
2 ABC B
3 PQR C
4 JKL A

if we run a query like (traditional manner no index awareness)

Code:
SELECT SUM(FACT_TABLE.COL1), DIM_STAFF.department
FROM FACT_TABLE, DIM_STAFF
WHERE FACT_TABLE.STAFF_KEY=DIM_STAFF.STAFF_KEY
AND DIM_STAFF.DEPARTMENT ='A'


now for department A there are more than one P-key entries, would the index awareness feature automatically transform the query as follows? (expectation)

Code:
SELECT SUM(FACT_TABLE.COL1), DIM_STAFF.department
FROM FACT_TABLE, DIM_STAFF
WHERE FACT_TABLE.STAFF_KEY=DIM_STAFF.STAFF_KEY
AND DIM_STAFF.STAFF_KEY IN(1,4)


I hit the trouble when doing similar stuff described above, are we missing some other parameter to make it work?
Back to top
vaidhyee
Principal Member
Principal Member



Joined: 20 Dec 2005

Posts: 125



PostPosted: Fri Jan 04, 2008 2:30 pm 
Post subject: Re: Using index awareness feature

I guess we figured out the issue (why universe & appln crash sometimes) and planning to report this to BO (vendor).

Like the example previously mentioned when index awareness is defined on grouping attributes the list of values are duplicated.

Does anyone have workaround for this?
Back to top
Fiona
Principal Member
Principal Member



Joined: 16 Dec 2002

Posts: 380
Location: Cornwall


flag
PostPosted: Mon Jan 07, 2008 6:28 pm 
Post subject: Re: Using index awareness feature

vaidhyee wrote:
Hi,

I'm sorry should have described the problem with an example.

suppose i create a BO query based on the example 1 FACT_TABLE and 1 dimension DIM_STAFF (current set up without index awareness)

CASE-1

SELECT SUM(FACT_TABLE.COL1), DIM_STAFF.NAME
FROM FACT_TABLE, DIM_STAFF
WHERE FACT_TABLE.STAFF_KEY=DIM_STAFF.STAFF_KEY
AND DIM_STAFF.NAME IN('XYZ','ABC','PQR')

In DIM_STAFF table the records look as follows with STAFF_KEY indexed

STAFF_KEY NAME ADDRESS ..
1 XYZ ....
2 ABC ...
3 PQR ...

After the index awareness is applied my expectation is that the query would get transformed in BO internally as follows:

CASE-2
SELECT SUM(FACT_TABLE.COL1), DIM_STAFF.NAME
FROM FACT_TABLE, DIM_STAFF
WHERE FACT_TABLE.STAFF_KEY=DIM_STAFF.STAFF_KEY
AND DIM_STAFF.STAFF_KEY IN(1,2,3)

because running the above modified query and previously described query makes a lot of difference (especially when number of rows processed in FACT_TABLE is large).
The optimizer (oracle 10g cost optimizer) explain plan shows a cost which is substantially higher for CASE-1 rather than CASE-2 and it is quite expected.

The problem in our side is the queries are not pre built (fixed) but variable based on the prompt parameter. in this case NAME is given as a prompt for end user.
The example i described is fictional and in reality there are 10-15 dimensions simultaneously i need to connect accepting values from prompts.

Regards,
Vaidhy


Index awareness does not work if you use a prompt on the key.

_________________
BO 5.1.6, 6.5, XIR2, XIR3

BO still as flakey as it was 7 years ago icon_smile.gif
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0793 seconds using 17 queries. (SQL 0.0350 Parse 0.0350 Other 0.0093)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo