CAS: STAR SCHEMA doesn't run

marcoNinfa — Mon, 09 Nov 2020 14:14:37 GMT

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:

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
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;

Re: STAR SCHEMA doen't run

ChrisNZ — Sun, 08 Nov 2020 21:42:19 GMT

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

Re: STAR SCHEMA doen't run

ChrisHemedinger — Mon, 09 Nov 2020 14:16:23 GMT

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.

Re: STAR SCHEMA doen't run

Bogdan_Teleuca — Mon, 11 Jan 2021 01:00:45 GMT

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).

topic Re: STAR SCHEMA doen't run in SAS Viya

CAS: STAR SCHEMA doesn't run

Re: STAR SCHEMA doen't run

Re: STAR SCHEMA doen't run

Re: STAR SCHEMA doen't run