topic Re: Proc OLAP Performing Inefficient Queries to Build Dimensions?? in SAS Procedures

Proc OLAP Performing Inefficient Queries to Build Dimensions??

StephenOverton — Wed, 09 Feb 2011 14:30:07 GMT

I'm currently working to improve a very large and complex cube. Contains over 80 dimensions and over 200 measures. The star schema it builds the cube from lives in Oracle. By looking at the open cursors in Oracle, I noticed the queries SAS submits to build the levels of the dimensions seem to be very inefficient. It always performs a select * during the dimension built phase. Wouldn't it be more efficient to select just the single column for each level? I'm not exactly sure how OLAP cubes are physically built to begin with, I'm only speculating.

Re: Proc OLAP Performing Inefficient Queries to Build Dimensions??

MEnder — Thu, 10 Feb 2011 14:33:04 GMT

The cube build process does submit Prepare statements that do a "select *" on each dimension table, in order to get the table headers, but it does not execute those statements.
In your typical cube build, if you run with
OPTIONS SASTRACE=',,,ds' sastraceloc=saslog nostsuffix;
you'll see something like that:

ORACLE_61: Prepared: on connection 1
SELECT * FROM "OLAPTST1"."SDTIME"

....more proc olap statements

and later the actual data retrieval:

ORACLE_85: Prepared: on connection 1
SELECT "YEAR", "QUARTER", "MONTH", "DAY", "DAY_ID" FROM "OLAPTST1"."SDTIME"

ORACLE_86: Executed: on connection 1
SELECT statement ORACLE_85

Maybe that's what you are seeing?

Re: Proc OLAP Performing Inefficient Queries to Build Dimensions??

StephenOverton — Thu, 10 Feb 2011 14:42:35 GMT

Thanks for that tip on using the options, I'll include in my code now and see what I get. We were originally looking directly at the open cursors on the Oracle side. This will be good to see on the SAS side.