Fluorite | Level 6

## Slowly changing dimension?

Hi!

I would like to create a WRS report from a cube which uses the following tables:

Dimension table:

 sekvnr Intervall TomTid 1 1-10 2 11-20 3 21-max 201001 4 21-30 5 31-max 201002 6 31-40 7 41-max

Source table

 Sekv Sekv_intervall Tid summa 1 1 201201 561 2 2 201201 21 3 3 201201 54 4 1 201201 54 5 2 201201 8 6 3 201201 56 7 4 201202 2 8 5 201202 456 9 1 201202 21 10 2 201202 5 11 4 201202 21 12 5 201202 6 13 1 201202 54 14 6 201203 2 15 1 201203 63 16 2 201203 5 17 4 201203 1 18 6 201203 362 19 7 201203 54 20 8 201203 2 21 7 201203 6 22 8 201203 247

The basic thing is to do a report like this one:

 Sum Of Summa Tid Intervall 10 201201 1-10 11-20 20 21-30 21-max 30 31-40 31-max 40-max 201202 1-10 10 11-20 20 21-30 40 21-max 31-40 31-max 50 40-max 201203 1-10 10 11-20 20 21-30 40 21-max 31-40 60 31-max 40-max 70

Now what i would like to do is to limit the intervals shown in the report by the "tomtid" in the dimension table, I would lika to create the following report (WITHOUT USING "hide dimension values with no measures date" in wrs"):

 Sum Of Summa Tid Intervall 10 201201 1-10 11-20 20 21-max 30 201202 1-10 10 11-20 20 21-30 40 31-max 50 201203 1-10 10 11-20 20 21-30 40 31-40 60 40-max 70

I'm sorry for the long desciption, would apprichiate all help I can get. I have cube studio, infomap studio and WRS as tools.

BR

Daniel

1 ACCEPTED SOLUTION

Accepted Solutions
Rhodochrosite | Level 12

## Re: Slowly changing dimension?

Ok, is it an option to create a "de-nomalized" table where you create all the combinations you want in the cube? In that case I would go for that solution. You can create a view (sql or better Data-step) so no extra storage i needed.

14 REPLIES 14
Tourmaline | Level 20

## Re: Slowly changing dimension?

Maybe this is too obvious for assuming that you haven't tried it, but here goes:filter on tomtid is not null?

Data never sleeps
Fluorite | Level 6

## Re: Slowly changing dimension?

Hi, not quite the same thing. then the interval 21-max would never show in my example.

Rhodochrosite | Level 12

## Re: Slowly changing dimension?

Hi!

We often use surrogate keys created from id and timestamp (date) and then do an ordinary join when createing the cube....

Dimension table contains fromDate and toDate, fact contains date. When picking key from dimension id and fromDate <= factDate <= toDate is used.

//Fredrik

Fluorite | Level 6

## Re: Slowly changing dimension?

Hi Fredrik!

Thanks, but how can a get a dynamic fact date when building the cube in SAS Olap Cube studio? When setting the formula for a choosen dimension, I can only use column names which are present in the current dimension table, not the fact table.

Thanks again

BR

Daniel

Rhodochrosite | Level 12

## Re: Slowly changing dimension?

You will need to add a surrogate key to the dimension and the fact table and use that key to join tables when you define the star-schema in cube studio.

I think it will do the job for you....

//Fredrik

Fluorite | Level 6

## Re: Slowly changing dimension?

I'm not quite sure I understand. I already have a key between source and dimension. Joining the two tables will not do the trick. My problem is that I want to show dimension values which are not populated and still be able to filter out dimension values which are expired. If I join the fact table with the dimension table and use the filter there then I will loose all dimension values which are not populated.

Daniel

Rhodochrosite | Level 12

## Re: Slowly changing dimension?

If I have understood you correct...:-) then you problem is that you want to join the fact table with the dimension table over time.

In that case you need to att time to the key. By doing that you will only get the corresponding dimension items for each row in the fact table.
You can then use a filter or the option to only keep members with value to trim the output.

//Fredrik

Fluorite | Level 6

## Re: Slowly changing dimension?

Oki, I think I understand now. Basically what one does is to create a "special" dimension which tells what intervalls are allowed for each month?

//Daniel

Rhodochrosite | Level 12

## Re: Slowly changing dimension?

I think the thing for you is to only add dimension key in fact table for those rows tha correspond to key and period. In your example the key "3" and "5" in the fact table should never been added, the keys should be missing or set to an invalid value.

You need to add fromDate as well in the dimension table as I mentioned in my earlier post.

Fluorite | Level 6

## Re: Slowly changing dimension?

Oki, but I still need to show the "old" intervalls with key 3 and 5. I need to show them for all data in the source table until the "toDate". I don't want to change historical data in the report. An incremental build to the cube is not an option.

/Daniel

Rhodochrosite | Level 12

## Re: Slowly changing dimension?

Yes and by using from/todate in the dimension together with date in fact table you will only get dimension keys that are valid for the dates that exist in the fact table, otherwise the dimension key in the fact table will be missing, and you can easily remove them.

Fluorite | Level 6

## Re: Slowly changing dimension?

Aah, but then dimensionvalues which have no data in fact table will not show in the report. That is one of the demans for the report, empty dimensionvalues should be shown as long as the date is valid.

Sorry for the stubbornness hehe. Your help is much appricheated!

/Daniel

Rhodochrosite | Level 12

## Re: Slowly changing dimension?

Ok, is it an option to create a "de-nomalized" table where you create all the combinations you want in the cube? In that case I would go for that solution. You can create a view (sql or better Data-step) so no extra storage i needed.

Fluorite | Level 6

## Re: Slowly changing dimension?

Thanks Fredrik! That was our "ugly-solution", so I'll guess we'll go with that.

Best regards

Daniel

Discussion stats
• 14 replies
• 2260 views
• 7 likes
• 3 in conversation