I am doing some work around using SAS OLAP Server in ROLAP mode in SAS 9.1.3 at the moment.

We have all our Warehouse reporting data stored in Star Schema’s in an Oracle Database.

We have found that the users are accessing the Information Maps we have created via SAS AMO, which is a good thing.

But the bad thing is that when a user outs that data into a pivot table, Excel/AMO downloads the data and stores it in Excel to populate the pivot table ans they are ending up with some massive Excel file sizes.

The answer is to build some OLAP Cubes and use these as the source for the pivot table.  But of course then we strike the issues with OLAP in SAS 9.1.3 such as no incremental updates, OLAP locking etc.

So we are trialing SAS OLAP Cubes in ROLAP mode whihc means the data is retained in Oracle and only the cube structure is built in SAS, which bypasses all these issues.

But of course it is not as easy as it seems.  We run a distributed SAS Windows environment so I need the OLAP server to be able to see the oracle instance etc.

In testing I found it a pain to have to login to Excel/AMO and/or EG to test the OLAP Cube.  Also if there are any errors not a lot of detail is provide.

So instead I just use this code:

proc sql;
connect to olap (host=<host> port=5451 user=<user> pass=,pwd>);
select * from connection to olap (
SELECT
{ [Measures].[COUNTSUM] } ON COLUMNS  ,
{ [region].[region].DefaultMember.Level.Members } ON ROWS
FROM
[,olap_cube_name.]
);
disconnect from olap;
quit;

Check the logs and see any errors.