OLAP Hierarchies from Oracle DB

[metalray]

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

[metalray]

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?

[metalray]

Anyone?

[AngelaHall]

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

[metalray]

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

[metalray]

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.