I would like to create a WRS report from a cube which uses the following tables:
The basic thing is to do a report like this one:
|Sum Of Summa|
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|
I'm sorry for the long desciption, would apprichiate all help I can get. I have cube studio, infomap studio and WRS as tools.
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.
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.
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
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.
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.
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.
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.
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!
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.