cancel
Showing results for 
Search instead for 
Did you mean: 

Extracting from Cube using an SQL statement!

Former Member
0 Kudos

Hi,

In my client's place, I see a piece of code, which extracts data from a Cube using a single complex SQL statement with nearly 8 JOINs (5 dims, 1 fact and 2 SIDs). The performance was very bad and the extraction job started timing out 1 fine day. We checked all stats and indexes on these tables and they were fine.

Then,I suggested that this is not optimised way of getting data out of a cube and ideally we shd use an export data source or InfoSpokes as applicable.

On the very next day, the timing out extraction completed in few mins! to our surprise. Now, we check the indexes, they are corrupted/dropped! I'm still wondering what could've happened!!

My questions are

1> Is it advisable to read cube using SQL statement. What will be the performance impact if we do so. After all, even a BEX query uses SQL joins to get data out of the cube. What diff does that make??

2> Say, no. of rows read using an SQL statement on a huge cube (eg: 25 millon records), using a where clause on a smaller dimension (eg: 0CALMONTH), is less, will avoiding indexes for this SELECT benefit read performance?

I donno if I've made myself clear. Pls post if you need more details.

I would appreciate any BW experts opinion with strong DB background (I would request to refrain from very generic comments on this post pls).

btw we are on Oracle 9i.

Thanks & Regards,

Sree

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

Does the SQL Statement use the hint STAR TRANSFORMATION for which the SAP star schema is optimized for and hense used by the auto generated SQL coming from BW query tools such as Bex? If You want an star schema oriented SQL statement in stead of the one you use You could try making a Bex report that returns the result you need in the code and then copy the generated SQL statement using RSRT or just ordinary SQL trace (ST05)

Answers (3)

Answers (3)

Former Member
0 Kudos

Here's a new note that may be relevant -

Number 862809

Version 1 from 15.07.2005

Short text BW: Excessive temporary space usage/Long running

krzysztof_konitz4
Contributor
0 Kudos

Hi,

Try to use FM RSDRI_INFOPROV_READ instead of SQL. It's easier to maintain and I think it works quite efficient.

Krzys

Former Member
0 Kudos

Hi Sree,

Have you thought about using the Hub and Spoke to extract out your data? This way you do not have to worry about the SQL statement, only what data you need?

If you are on 3.5, look at using RSANWB to extract out your data.

These are only a few options for mass data extarcts, I have others if you want. Cheers! Bill

Former Member
0 Kudos

What version of 9i?

Does the query that runs have a STAR_TRANSFORMATION hint in it?

There are problems with Star transformation unless you are at 9.2.0.5 plus an Oracle patch.

If you are really extracting all / most of the data, you would probably better off without the generated Star Transformation hint.

In general, I would prefer to use the tools (e.g. InfoHub) provided so that I could fully incorapate all teh other related capabiliites, e.g. scheduling, etc, rather than kluging together a workaround. I would pursue that before I decided to go outside of the BW's capabilities.

Former Member
0 Kudos

Sorry Pizzaman,

I did'nt read Your message properly before my reply...

Former Member
0 Kudos

Thanks for your response! Here is the query. I'm not sure how to figure out if START_TRANFORMATION is used or not.

SELECT S1/BIC/EALOCO S3/BIC/ECCPROD MIN( S2~/BIC/EREPPLANT )

INTO TABLE INT_SKU_DEMD

FROM

/BIC/FKREQ_REP AS F

JOIN

/BIC/DKREQ_REP1 AS D1

ON

FKEY_KREQ_REP1 = D1DIMID

JOIN

/BIC/SEALOCO AS S1

ON

D1SID_EALOCO = S1SID

JOIN

/BIC/DKREQ_REP2 AS D2

ON

FKEY_KREQ_REP2 = D2DIMID

JOIN

/BIC/DKREQ_REP4 AS D4

ON

FKEY_KREQ_REP4 = D4DIMID

JOIN

/BIC/SEREPPLANT AS S2

ON

D4SID_EREPPLANT = S2SID

JOIN

/BIC/DKREQ_REPT AS DT

ON

FKEY_KREQ_REPT = DTDIMID

JOIN

/BIC/DKREQ_REPP AS DP

ON

FKEY_KREQ_REPP = DPDIMID

JOIN

/BIC/SECCPROD AS S3

ON

D2SID_ECCPROD = S3SID

WHERE (str_where_clause)

GROUP BY

S1~/BIC/EALOCO

S3~/BIC/ECCPROD.

(where clause is nothin but 0CALMONTH = <last_month> )

I suggested all other possible standard solutions from SAP. But, I'm having a tough time convincing them that SAP provided functionality will be better than this query!

Regards,

Sree

Former Member
0 Kudos

Hi

It looks like a home made statement:

Here is an example of a Bex generated statement in which You can also see the star transformation hint which forces the data base to utilize the bit map indexes defined on star schema of the cuve.

*

  • QUERY STATEMENT IN OPEN-SQL

*

      • SELECT

SELECT

DU~SID_0CURRENCY AS S____014

F~KEY_1000471 AS S____008

F~KEY_1000473 AS S____361

F~KEY_1000472 AS S____672

SUM( F~AMOUNT ) AS 0AMOUNT

COUNT( * ) AS 1ROWCOUNT

      • FROM

FROM

/BIC/E100047 AS F

JOIN

/BIC/DICCOCCA91U AS DU

ON

F~KEY_ICCOCCA91U

= DU~DIMID

JOIN

/BIC/D100047P AS DP

ON

F~KEY_100047P

= DP~DIMID

JOIN

/BI0/SVTSTAT AS S1

ON

F~KEY_1000478

= S1~SID

      • WHERE

WHERE

( ( ( (

F~KEY_100047T

= 200524

) ) AND ( (

F~KEY_1000477

= 10

) ) AND ( (

DP~SID_0RECORDTP

= 0

) ) AND ( (

DP~SID_0REQUID

<= 9670

) ) AND ( (

S1~VTSTAT

= '0'

) ) ) )

      • GROUP BY

GROUP BY

DU~SID_0CURRENCY

F~KEY_1000471

F~KEY_1000473

F~KEY_1000472

      • DB-SPECIFIC HINTS

STAR_TRANSFORMATION FACT( &TABLE& ) &SUBSTITUTE LITERALS&

Former Member
0 Kudos

The home made query may perform better than the autogenerated one, the discussion can easily be ended looking at the execution plan of each query and the calculated costs. The argument for using the star schema transformation is forcing the query to utilize the very fast bitmap merge feature of the oracle database. If You need more info on the oracle star schema features You could consult following book:Oracle DBA Guide to Data Warehousing and Star Schemas by Bert Scalzo

Former Member
0 Kudos

I guess I will not be able to use Stat Transformation here due to the reason Pizzaman specified (we are on 9.2.0.4 )

Former Member
0 Kudos

My point on the Star Transormation was really just to confirm whether it is being done. You might be able to use it, but my concern had to do with some of the OSS Notes below:

717496 Wrong results with STAR_TRANSFORMATION_ENABLED=TRU 10.11.04 (This one especially)

721331 MERGE JOIN CARTESIAN IN TEMP TABLE- STAR and 25.03.04

785190 Oracle 9i: ORA-3113 and ORA-7445 with star transfo 25.10.04

811354 Use of complex Queries slows down database system 15.06.05

846364 Parse of star query may spin 18.05.05

862809 0001 0.380 BW: Excessive temporary space usage / Long running Q 21.07.05

The other SQL stmts that were posted are in the SAP's "Open SQL" which then gets translated to the target DB's proper syntax. I prefer to look the actual SQL stmt that Oracle will run (maybe I'm just an old dog not wanting to learn a new trick) which you'll see in RSRT (or in ST04 for stmts already run) when you check the Display Run Schedule box. You'll see an Oracle SQL stmt like shown below with the STAR hint:

SELECT /+ STAR_TRANSFORMATION FACT(T_00 )/

T_01 . "SID_0CURRENCY" "S____007" ,

T_02 . "SID_0CMMT_ITEM" "S____005" ,

T_04 . "COORDER" "K____040" ,

COUNT(*) "1ROWCOUNT" ,

SUM( T_00 . "/BIC/ZACT_EXP" ) "ZACT_EXP" ,

SUM( T_00 . "/BIC/ZBUD_AMT" ) "ZBUD_AMT" ,

SUM( T_00 . "/BIC/ZCOMMIT" ) "ZCOMMIT" ,

SUM( T_00 . "/BIC/ZPARK_FI" ) "ZPARK_FI" ,

SUM( T_00 . "/BIC/ZPRE_COMM" ) "ZPRE_COMM" ,

SUM( T_00 . "/BIC/ZPUR_REQ" ) "ZPUR_REQ" ,

SUM( T_00 . "/BIC/ZREVENUE" ) "ZREVENUE" FROM "/BIC/EZGR_C52" T_00 , "/BIC/DZGR_C52U" T_01 ,

"/BIC/DZGR_C525" T_02 ,"/BIC/DZGR_C524" T_03 ,

"/BI0/SCOORDER" T_04 , "/BIC/DZGR_C522" T_05 ,

"/BIC/DZGR_C523" T_06 , "/BIC/DZGR_C52T" T_07 ,

"/BIC/DZGR_C521" T_08 , "/BIC/DZGR_C52P" T_09

WHERE

( T_00 . "KEY_ZGR_C52U" = T_01 . "DIMID" ) AND

( T_00 . "KEY_ZGR_C525" = T_02 . "DIMID" ) AND

( T_00 . "KEY_ZGR_C524" = T_03 . "DIMID" ) AND

( T_03 . "SID_0COORDER" = T_04 . "SID" ) AND

( T_00 . "KEY_ZGR_C522" = T_05 . "DIMID" ) AND

( T_00 . "KEY_ZGR_C523" = T_06 . "DIMID" ) AND

( T_00 . "KEY_ZGR_C52T" = T_07 . "DIMID" ) AND

( T_00 . "KEY_ZGR_C521" = T_08 . "DIMID" ) AND

( T_00 . "KEY_ZGR_C52P" = T_09 . "DIMID" ) AND

T_05 . "SID_0BUS_AREA" = 26 AND

T_06 . "SID_0CHRT_ACCTS" = 1 AND

T_06 . "SID_0CO_AREA" = 1 AND

T_07 . "SID_0FISCPER" <= 102005001 AND

T_07 . "SID_0FISCVARNT" = 10 AND

T_06 . "SID_0FM_AREA" = 1 AND

T_08 . "SID_0FUND" = 10715 AND

T_09 . "SID_0RECORDTP" = 0 AND

T_09 . "SID_0REQUID" <= 896164

GROUP BY

T_01 . "SID_0CURRENCY" ,

T_02 . "SID_0CMMT_ITEM" ,

T_04 . "COORDER"#

The Star Transformation, as already mentioned, forces the merging of the indexes first to create a temporary index that is then used to access the fact table. This works great when you have multiple predicates from multiple dimensions, resulting in only needing to retrieve small % of the rows, but if you are running a query that must read all ( e.g. a reconciliation query ) or almost all the rows in the fact table, a full scan of the fact table will probably be faster. You can evaluate the query cost with the Star hint, and then remove the hint and see the cost. Unfortunately, there is no way I have found ( and I have discussed with some SAP folks) to suppress the Star hint for a particular query.

So if you are extracting all the rows, you might want to run your own SQL stmt without the Oracle Hint, or you might want to test with the Oracle hint, making sure you do not run into any of the problems referenced in the OSS Notes. If you code your own SQL stmt, you might be able to exclude joins to some of the dimension tables that the BW adds.

I would really look at the explain plan. You might find that some additional indices on dimension or master data tables would also help.

As also mentioned, make sure your DB stats are current, and you may want to review what is gathered. I know in our environment, on large fact tables, we only sample 1% of the rows, which in my opinion is far too small, and I go around with the DBAs in our shop on this. I would like to see 5% or 10% sampling. Their focus is R3, and none have any prior data warehouse experience prior to BW, so I'm always nudging them along (having managed a large Oracle data warehouse for several years in a non-SAP BW environment in a previous job).

Again, my preference would be to work within the vendors toolset whenever possible, but sometimes, you can't.