How can I build a star schema in SAS VA 7.3 with role-playing dimension?

[jstegmair]

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

[SASKiwi]

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.