BookmarkSubscribeRSS Feed
StephenOverton
Fluorite | Level 6
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.
2 REPLIES 2
MEnder
SAS Employee
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?
StephenOverton
Fluorite | Level 6
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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1191 views
  • 0 likes
  • 2 in conversation