When I create a new query in BEx Designer, All I define is what all I need from underlined InfoProvider & in what format. I really don’t decide where & in which sequence to look for data during Query execution. Often I wonder when I execute the query how does OLAP processor look for the data? Which in turn lead to many more wandering questions in mind, to state a few – 1. Wondered if I execute query first time, it takes longer than second execution immediately. 2. Will it be faster second time for me or for all the users who execute this query after my first execution? 3. How does aggregated really help in reducing the execution time? 4. Wondered that pre-calculation was there only to provide static HTML pages or had some thing to do with performance optimization as well. Answers of all these questions & more could be understood if I could understand how & in what sequence OLAP processor look for the query relevant data during execution. Understanding it has extreme implications in terms of reaching right tradeoff between “Performance Improvement” & “Resources contributed toward it” This weblog tries to explain just that --Sequence in which OLAP Processor looks for Query Relevant data--, OLAP Processor - 
1. First checks data availability in Local Cache (Which is session dependent, stores in Memory & available in All SAP BW Releases). 2. If Local Cache is NOT available then will check Global OLAP Cache (OLAP Cache buffers Query result set data in order to improve performance of subsequent query executions. It is session/user independent, stores in memory/files/tables & is available from SAP BW 3.X). 3. If Global OLAP Cache is NOT available then will look for relevant Aggregates (Aggregates are subsets of fact table data where summary data is stored as part of new, transparent InfoCubes. Aggregates have the same structure as an InfoCube, with transparent database tables surrounded by the dimensions tables). 4. If No relevant Aggregates are found then it will look into database through InfoProvider. So In order to improve the performance you need to provide the data to OLAP processor as early in Sequence as Possible But Doing this has Cost attached to it … Look at the tradeoff & Optimize Query Performance?
1. First checks data availability in Local Cache (Which is session dependent, stores in Memory & available in All SAP BW Releases). 2. If Local Cache is NOT available then will check Global OLAP Cache (OLAP Cache buffers Query result set data in order to improve performance of subsequent query executions. It is session/user independent, stores in memory/files/tables & is available from SAP BW 3.X). 3. If Global OLAP Cache is NOT available then will look for relevant Aggregates (Aggregates are subsets of fact table data where summary data is stored as part of new, transparent InfoCubes. Aggregates have the same structure as an InfoCube, with transparent database tables surrounded by the dimensions tables). 4. If No relevant Aggregates are found then it will look into database through InfoProvider. So In order to improve the performance you need to provide the data to OLAP processor as early in Sequence as Possible But Doing this has Cost attached to it … Look at the tradeoff & Optimize Query Performance?
36 Comments
