BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DannyDizzy
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
FredrikE
Rhodochrosite | Level 12

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

14 REPLIES 14
LinusH
Tourmaline | Level 20

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
DannyDizzy
Fluorite | Level 6

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

FredrikE
Rhodochrosite | Level 12

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
Fluorite | Level 6

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
Rhodochrosite | Level 12

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
Fluorite | Level 6

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
Rhodochrosite | Level 12

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
Fluorite | Level 6

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
Rhodochrosite | Level 12

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
Fluorite | Level 6

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
Rhodochrosite | Level 12

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
Fluorite | Level 6

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

FredrikE
Rhodochrosite | Level 12

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
Fluorite | Level 6

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

Best regards

Daniel

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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