SAS OLAP Multilanguage Dimension and Member Valid_To/Valid_From

[metalray]

Hello,

1.
I have an issue with dimension translation. When creating my cube the text tells
me something about naming conventions, what are those naming conventions? I did
not find anything in the OLAP User Guide documentation.

Furthermore I thought it is more feasible to have one master "Sales channel" dimension in
which the dimension key refers to a language table rather than having to maintain x amount of "Sales channel" dimensions
for various languages.

2.I am back again on dimension member validity. After months of looking for a solution there
is still nothing in sight. If my dimension has a field for VALID_TO and VALID_FROM, how do I tell the OLAP cube
that according to the date selected in the period dimension it should only show the valid ones?
I cant be the first one in the SAS world who does that.

Thanks for any responses.

[AngelaHall]

For #2 - check out the Slowly Changing Dimension (SCD).
SAS Paper: http://www2.sas.com/proceedings/sugi29/114-29.pdf provides information on how this works, which types (1,2,3).

~ Angela

[metalray]

Hello Angela,
Thanks for your reply. You seem to be the only SAS person around in the SAS Discussion Forums.
Unfortunately, we dont use Data Integration studio because the data is already ready in a staging database and I
dont see why to pay extra license fees so that the SAS OLAP cube understands my VALID_FROM and VALID_TO columns.
I see this as being a SAS OLAP Cube feature and if it is not there, then SAS OLAP is way behind its competitors.
Thanks for refering me to the paper but it does not tell me how to use my SAS OLAP cube (cube studio) with
a SCD, a dimension that is currently in an oracle datatable (having VALID_FROM, VALID_TO) date columns.

[AngelaHall]

The SCD loader in DI Studio actually builds the dimension table for you. If you already have this table built, then you should be able to add it as a dimension table in your OLAP Cube. You will then need to add a table option for this table with a WHERE clause that analyzes the valid_from - valid_to fields in your dimension table.

When you get to the dimension designer screen, after selecting the slowly changing dimension table you will need to include a table option to retrieve only the current valid members.

This will then appear in the source PROC OLAP code as an example:

DIMENSION test
CAPTION = 'test'
SORT_ORDER = ASCENDING
DIMTBL = sashelp2.PRDSAL2(where valid_from <= datetime() <= valid_to)
DIMKEY = MONTH
FACTKEY = MONTH
HIERARCHIES = (
test
) /* HIERARCHIES */;

Hope this helps.
~ Angela

[metalray]

Hello Angela,
Many thanks for that. I head from SAS Support that on the fly dimension member name display
depending on user language in web report studio is not possible. that is very bad.
I need get those member names in their three languages in the cube as member attributes or something and
display those upon some prompt selection. maybe thats the way forward.

regarding the SCD. I am looking at the dimension designer right now but dont where to specify
the "option" to retrieve only some members. moreoever I am not sure thats what I want.
I want to load everything in the cube but the user should be able to only see dimension structures
that are valid and that on the fly. I can generate a cube for each months which means in 10 years
I have over 200 cubes just to make the user able to analyse facts when the dimension structure has changed.