CAS: STAR SCHEMA doesn't run

[marcoNinfa]

Hi everybody,

I'm trying to create a star schema using:

- fact table -> media table: 4Mln rows x 48 cols

- dim1 -> visite table: 1 Mln rows x 17 cols

- dim2-> ana_geog: 30K rows  x 7 cols

- dim3 -> calendario: 1 Mln rows x 13  cols

- dim4 -> ana_utenti: 1.5 Mln rows x 7cols

I've used the procedure explained in this link Query-Performance-Use-a-CAS-Star-Schema-in-SAS-Viya-3-5 but I've 2 big problems:

1.  The complete schema doesn't give  any output: it stars running but in the practice SAS Viya is doing nothing -> what can be the cause of my problem? Too many keys? Missing values? I don't know what I've to change
2. When I run the code without the first join with the ana_cust table, the code run in 0.05 seconds and I obtain a star schema as output, but when I use this schema in a report it doesn't contain any rows. 

I attach the code below, please  help me to understand if I do something wrong.

proc cas;
table.view / caslib='public' name='schema_media' promote = true
tables={
{caslib='datamart' name='dm_media_star',
varlist={"actions_n" "categoria" "consolidato" "content_item" "flg_entry_media" "flg_exit_media" "flg_utente_reg" "following_media" "load_timestamp" "ma_eof" "ma_init" "ma_midroll_n"
"ma_midroll_time" "ma_pausa_n" "ma_pause_time" "ma_play_n" "ma_postroll_n" "ma_postroll_time"
"ma_preroll_n" "ma_preroll_time" "ma_seek_avanti_n" "ma_seek_avanti_time" "ma_seek_indietro_n"
"ma_seek_indietro_time" "ma_seek_n" "ma_seek_time" "ma_start_content" "ma_start_content_300"
"ma_stop_content" "media_duration" "media_name" "media_order" "media_times" "media_views_300"
"media_views_play" "mute" "preceding_media" "quantity" "run_time" "second_following_media"
"second_preceding_media" "volume" "request_id"}, as='m'}
,
{keys={'m_customer_id = u_customer_id'},
caslib='datamart' name='ana_utenti',
varlist={"flg_prof_editoriale" "flg_prof_pub" "flg_utente_log" "anno_nascita" "genere" "data_reg"},
as='u'}
,
{keys={'m_key_geog = g_key_geog'},
caslib='datamart' name= 'ana_geog',
varlist = {"nazione" "citta" "continente" "regione" "zona" "provincia" },
as='g'}
,
{keys={'m_times = cal_times_dig'},
caslib = 'datamart' name= 'calendario',
varlist={"times_tv" "year_dig" "year_tv" "month_dig" "month_tv" "day_dig" "day_tv" "hour_dig"
"hour_tv" "minute" "week_dig" "week_tv"}, as='cal'}
,
{keys={'m_sid = v_sid' },
caslib = 'datamart' name = 'DM_VISITE_STAR',
varlist={"os" "browser" "organization" "resolution" "device_class" "mobile_client" "visit_duration"},
as= 'v'}
};
table.tableinfo / caslib='public' name='schema_media';

quit;

[ChrisNZ]

@ChrisHemedinger I still think there should be a CAS community for this kind of questions 🙂

[ChrisHemedinger]

We do have a more generic SAS Viya community (under Solutions in the menu) so I've moved this topic.  Maybe @Bogdan_Teleuca (who wrote the article) can advise.

[Bogdan_Teleuca]

The way I would eliminate the issues, always best to use a bottom-up approach. Add complexity when the simple works.

1. sample a few lines from your fact and dimensions that are supposed to join

2. choose just one fact (some column types might pose issues in a view)

3. make sure the dimensions and facts are public before creating the view

4. create the view and query it

5. try in a VA report and using code (the query might be failing).

6. add more columns to the view

7. add more data (yes, empty or null keys I think will create issues).