Hi jplarios, many thanks for your post. It seems SAS has some catching up to do to reach the OLAP capabilities of Oracle Hyperion Essbase. it suprises me, because which business scenario has only static dimensons? I need to make historic analysis of dimensions and the facts to enable a "what if" analysis of data i.e. to see how facts change when certain countries where part of the EU or not. My original plan was to feed the OLAP cube via SAS Access from a large Oracle Datawarehouse without any funny data steps or proc sql in between. Could you please explain more about your idea "What about using a current flag that is populated everytime there is some change? " ' Thanks a lot ... View more

Hi Shane, Thanks for the reply. Yes, it would be good if someone know how this works. Am I the first person to implement SCD (without DI studio)in SAS with an Oracle Staging database ? I hope not. I explain my issue again: I dont mind if SAS OLAP knows its a SCD or not, I know it is and I just need that the OLAP cube does not show users a value for Kosovo when quering in a time period before 2008 (because it was not a state then) but showing a value after 2010. The dimensions changed. I dont expect a fancy real time graphical restructure of the dimension but at least a #Missing or something to indicate that at the time e.g. 12/01/2007 there was no value for Kosovo as Level0 (only as a province of greater Jugoslawia). Now, I dont want to do this manually, considering for the other hundreds of countries to which those circumstances apply. Additionally, countries like Switzerland should show up in the dimension graphically but should not sum up to the total. The problem goes in the same direction but here it is not time dependent but structurally dependet. Thanks for any feedback, Philip ... View more

Hi Shane, Thanks for the input. I heard that DI studio can do it but I am not using DI Studio (NO DI Studio). You are right, I am creating start schemas. Its a big DWH and we are redesigning the dimensions and I want to get it right. You MDX sounds feasible but I was hopying there this would be supported by SAS OLAP 9.2 by default. If the above table is not interpreted as a slowly changing dimension, how will it deal with the member "Kosovo" that exists twice as Level 1 member. Hyperion does something called "shared members" to have the members shared under different headers/levels/parents i.e. a reference to the original member so the data is not stored twice. ... View more

Hello Jenn, Thanks for the reply. It is not urgent but at some point I need to design the Oracle staging database and I want to create a dimenson table that can be understood by SAS Cube Studio. SAS Support has confirmed that SCD can be built but there does not seem to be any documentation about this. The measure "total customers" in French would read "clientèle totale". I need to be able to display member and measure names in multiple languages to the end users. this is what I suggest as a flat table: ID¦Level2¦Level1_____¦Level0_¦LangENLevel2¦LangENLevel1¦LangENLevel0¦LangFRLevel2¦LangFRLevel1¦LangFRLevel0¦ValidFrom¦ValidTo¦Calculation 1¦_Europa¦Frankreich_¦Burgund¦Europe______¦France______¦Burgundy____¦____________¦____________¦____________¦2010_____¦_______¦ + 2¦_Europa¦Jugoslawien¦Kosovo_¦____________¦____________¦____________¦____________¦____________¦____________¦2010_____¦2008___¦ + 3¦_Europa¦Kosovo_____¦_______¦____________¦____________¦____________¦____________¦____________¦____________¦2008_____¦_______¦ + 4¦_Europa¦Deutschland¦Bayern_¦Europe______¦Germany_____¦Bavaria_____¦____________¦____________¦____________¦2010_____¦_______¦ + 5¦_Europa¦Schweiz____¦_______¦____________¦Switzerland¦¦____________¦____________¦____________¦____________¦2010_____¦_______¦~ wheraus the ~ should tell me not to add switzerland to the total customers i.e. to ignore. ... View more

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. ... View more

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 ... View more

Hi Jenn, Thanks for the reply. I have asked SAS Support how to realize Slowly Changing Dimensions SCD in SAS. They are thinking about it. A SCD is one that changes over time, slowly. A region dimension is a very slowly changing dimension. There might be the moment in time when Canada merges with the US and we have the United States of Canadian America. If you analyse your data now, its still seperated for USA and Canada but in 2087 it might be merged. The dimension Region has changed and I need to reflect this. When the user queries total customers for region = ALL the web report has to adjust accordingly (assuming I did not specify a detailed region USA in the web report). I also need that the meassure name "total customers" and the region name changed if the user changes the web report language. I hope that helps. (SCD are usually implemented by having a FROM / TO in the staging database that feeds the SAS cube) well, I know thats how it works for Oracle Hyperion OLAP and I want to know if that works in SAS - without data integration studio ... View more

Anyone? ... View more

Anyone? ... View more

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? ... View more

Hi Angela, Thanks for the info, I better be carefull to avoid the drama/"dramatically". I am aware of the different models, ROLAP, MOLAP, OLAP, since I worked quite a while with Hyperion solutions. Thanks, Philip ... View more

Thanks Angela. I was able to view the cube and after giving the "OLAP Creator Group" also permission to the parent folder they were able to rebuild the cube. ... View more

Hi Angela, Thanks for the clarification. I find the functionality of the Hyperion Block storage cube quite handy. Users can easily send back data to the cube (e.g. update an individual account) and if the members are set to dynamic calculation the roll-up is right up to date as well. assuming that my sas olap cube is fed from an oracle staging area, in that case, do I undertand correctly that the users update the staging area (data table) and then I would need to develop a little refresh procedure that pushes the new data through to the sas cube? if that is so the sas olap storage model is different from hyperions block storage model. ... View more