Slowly Changing Dimensions in SAS OLAP cube

[metalray]

Hi Guys,

My slowly changing dimenion is ready and finished. Star Schema is all ready set up in the Oracle Staging DB. Now, is there a specific setup that I have to do so SAS shows(reloads) the dimenion members to what they where at point xxx in time when I select point xxx from my time dimension in web report studio or excel add in. - I have not done so, its an exercise in theory to prepare me for the worst.

thanks,
Philip

[metalray]

Anyone?

[Jenn2]

Hello Metalray,
I apologize for being so slow to respond but I'm confused about what you are asking. Can you tell me what a slowly changing "dimenion" is? Also, are you upgrading to a new version of WRS? I'd love to help you in any way possible, I just need to know exactly what you need help with....

[metalray]

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

[Jenn2]

Good point about the slowly changing dimensions. You don't want to recode everything when regions change. I'll bring this up to the development team.

You wrote, "I also need that the measure name "total customers" and the region name changed if the user changes the web report language." Do you mean that you need to be notified if someone modifies a data point? Or do you need to be notified when someone changes a canned report? Not sure what you need there.

Thanks for being so patient with us.

[metalray]

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.

[ShaneGibson_OptimalBI]

There is a SCD type 2 node in DI Studio, that will nebale you to easily create your type two dimensions in the Oracle database (assuming you are creating Star Schemas?)

But the trick is getting the OLAP cubes to recognise the dimensions as SCD and therefore not aggregate the values.

The only way I have thought it ciuld be done in 9.1 is to create some custom MDX and embed it in the cube deffintion, so that it filters the data based on the dimension date.

[metalray]

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.

[ShaneGibson_OptimalBI]

I havent had a chance to play with OLAP Server in 9.2 so cant answer your question sorry, soudns liek a neat solution in Hyperion though.

Perhaps if somebody from SAS could get Michelle Wilkies to pop in and respond (if she is still the OLAP Server Product Manager?) we can find out if it is catered for or not.

[metalray]

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

[jplarios]

Hi metalray,

If you don't have DIS, then you should try an SCD algorithm with base SAS to populate your SCD. I think the Kimball book has a flow showing how to populate one, you just need to use the data step or proc sql or combo.

Currently ( 9.1 or 9.2), the SAS OLAP cube does not support SCD out of the box; I know this because we wanted to do it too. What about using a current flag that is populated everytime there is some change? In the cube you might need to create some restriction via mdx.
I don't know what interface your using, but you could do a ROLAP via an information map(IM) - SAS calls it using a relational source to the IM. that way the IM seats on top of the star schema and you could query the SCD the way Kimball explains it. Of course, depending on how much data and resources you might have, the IM might not perform as well as a cube when your data is aggregated.Also, navigating a cube vs an IM-relational is very different , so the user might need education. Message was edited by: jplarios

[metalray]

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

[metalray]

Hello again,

A colleague just confirmed that he think it is possible to tell the OLAP Cube (Studio) to interprets
a FROM/TO data column in the staging database as indicator for dimension members to include them in the
rollup or not based on expiration date (time dimension) selected.