I am creating a star schema model for operational analytics, trying to use SAS VA - Query Builder, Star Schema functionality.
Specifically, I have a fact table about projects (one row represents one project), but the row contains many dates (10-12 different dates). To connect this fact table with the date dimension (we have a specific fiscal year date dimension), a role-playing capability would be needed. It looks like you can not change the join in SAS VA Query Builder: Star Schema, and you can not create multiple joins from FACT to DIM table. This is not only necessary for the Date Dimension, but also Organization DIM and Location DIM.
The PROJECT_FACT record looks like this:
PROJECT_NUMBER | PROJECT_KEY | CREATED_DATE | CLOSED_DATE | GO_LIVE_DT | ....
The DATE_DIM record looks like this:
DATE | DAY | MONTH | QUARTER | FYEAR | FQUARTER | FYEAR ....
(I know the calendar year info is redundant as VA can handle calendar year hierarchy out of the box).
Alternatives for me would be to pre-build an analytical table (wide format) before loading it into SAS VA, but I really would like to avoid this as it is enormous pre-coding and pre-processing.
Does anyone have help for this scenario? Using Data Integration Studio with ETL transformations is not an option for me at the moment.
Thanks,
Juergen
In my experience, most SAS VA reporting is done with simple flat tables where dimensions have been de-normalized. The reason for this is better performance. If your data model is complicated like it sounds it is in your case then that would slow down a star schema approach even more. So unless your reporting requirements are quite simple - the more complicated the report the slower it is as well - I wouldn't recommend that approach. Note also data loading star schemas will be slower too.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.