Traditional web-based reporting with SAS BI tools

Slowly changing dimension?

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Slowly changing dimension?

Hi!

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

Dimension table:

sekvnrIntervallTomTid
11-10
211-20
321-max201001
421-30
531-max201002
631-40
741-max

Source table

SekvSekv_intervallTidsumma
11201201561
2220120121
3320120154
4120120154
522012018
6320120156
742012022
85201202456
9120120221
1022012025
11420120221
1252012026
13120120254
1462012032
15120120363
1622012035
1742012031
186201203362
19720120354
2082012032
2172012036
228201203247

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

Sum Of Summa
TidIntervall10
2012011-10
11-2020
21-30
21-max30
31-40
31-max
40-max
2012021-1010
11-2020
21-3040
21-max
31-40
31-max50
40-max
2012031-1010
11-2020
21-3040
21-max
31-4060
31-max
40-max70

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
TidIntervall10
2012011-10
11-2020
21-max30
2012021-1010
11-2020
21-3040
31-max50
2012031-1010
11-2020
21-3040
31-4060
40-max70

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


Accepted Solutions
Solution
‎11-29-2012 05:18 AM
Regular Contributor
Posts: 187

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.

View solution in original post


All Replies
Super User
Posts: 5,386

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
Contributor
Posts: 28

Re: Slowly changing dimension?

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

Regular Contributor
Posts: 187

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

Contributor
Posts: 28

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

Regular Contributor
Posts: 187

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

Contributor
Posts: 28

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.

Or have I missunderstood your answer?

Thanks in advance

Daniel

Regular Contributor
Posts: 187

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

Contributor
Posts: 28

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

Regular Contributor
Posts: 187

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.

Contributor
Posts: 28

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

Regular Contributor
Posts: 187

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.

Contributor
Posts: 28

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! Smiley Happy

/Daniel

Solution
‎11-29-2012 05:18 AM
Regular Contributor
Posts: 187

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.

Contributor
Posts: 28

Re: Slowly changing dimension?

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

Best regards

Daniel

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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