Slowly changing dimension?

[DannyDizzy]

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

[LinusH]

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

[DannyDizzy]

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

[FredrikE]

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

[DannyDizzy]

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

[FredrikE]

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

[DannyDizzy]

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.

Or have I missunderstood your answer?

Thanks in advance

Daniel

[FredrikE]

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

[DannyDizzy]

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

[FredrikE]

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.

[DannyDizzy]

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

[FredrikE]

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.

[DannyDizzy]

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

[FredrikE · Accepted solution]

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.

[DannyDizzy]

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

Best regards

Daniel