BookmarkSubscribeRSS Feed
metalray
Calcite | Level 5
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
5 REPLIES 5
metalray
Calcite | Level 5
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
Calcite | Level 5
Anyone?
AngelaHall
SAS Employee
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
Calcite | Level 5
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
Calcite | Level 5
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 979 views
  • 0 likes
  • 2 in conversation