Traditional web-based reporting with SAS BI tools

OLAP Hierarchies from Oracle DB

Reply
Regular Contributor
Posts: 207

OLAP Hierarchies from Oracle DB

Hello,

I have created a few small cubes now and wonder if it is possible to load new hierarchies from a table rather than specifying them manually.
Large customer account hierarchies with 5000 members in various hierarchies are a bit tedious to define. I remember Hyperion does it with Parent/Child dimension tables. How is this done in SAS?

Thanks,
Philip
Regular Contributor
Posts: 207

Re: OLAP Hierarchies from Oracle DB

Additionally, the members have names in 3 different languages. All in the data table.I would need 3 sets of dimension and hierarchies for each language?
Regular Contributor
Posts: 207

Re: OLAP Hierarchies from Oracle DB

Anyone?
SAS Employee
Posts: 238

Re: OLAP Hierarchies from Oracle DB

Phillip,
Not quite sure what you are looking to do here - but maybe a strategy is to create the first cube, grab the SAS OLAP Cube code, then code a mechanism to programmatically modify the OLAP Cube code using the tabular sources from Oracle.

Several program ideas -
1. Create a macro and execute that through a data step to pass information from a tabular source into the macro.
2. Create a data null call execute routine to utilize the tabular source in some of the execute statements.

~ Angela
Regular Contributor
Posts: 207

Re: OLAP Hierarchies from Oracle DB

Hi Angela,
that goes in the right direction.
I made some progress and figured out that a SAS cube expects a flat table (levels) rather than parent/child format (there is a tool fir data integration studio but I am not using DI). the issue is, if levels change, the hierachy changes a lot. the product "christmas bear" might not be there anymore in 2011 christmas and I dont want to sit there december 2011 to change the staging database that feeds the sas cube to redesign the levels. the advantage of a parent/child table is that I dont go wide horizontally i.e. the table structure never changes.
if I cant use a parent/child table the issue of dynamically loading hierachies is not there anymore.

regards,
philip Message was edited by: metalray
Regular Contributor
Posts: 207

Re: OLAP Hierarchies from Oracle DB

I dont want to talk about slowly changing dimension here, thats part of another post by me (http://support.sas.com/forums/thread.jspa?threadID=10957)
I just wanted to see if I can write a nice OLAp cube build script that helps me to load the dimension without specifiying every single level if the underlying table format has changed. It would be great if there is a way to make SAS (base code olap creation script) loop through the table and add a level for each column in the oracle table that is prefixed with "LEVLE". some form of automation to pull through dimension changes.
Ask a Question
Discussion stats
  • 5 replies
  • 303 views
  • 0 likes
  • 2 in conversation