topic Re: SAS VA- Table join in SAS Visual Analytics

SAS VA- Table join

debopriya85 — Wed, 28 Sep 2022 10:25:05 GMT

*Reposting*

Hi, I am creating SAS VA dashboard in SAS Viya 3.4

For one of the dashboards, I need to calculate Rate which is "count(Store_ID with sales)/ Count(all Store_ID)".

Count Store_ID with sales is coming from fact_sales table which is having 2.7million records (around 7 GB).

and the denominator Count(All store_id) is coming from dimension table which is having 30K records

when I left join these 2 tables (left table is the Dimension table) and create another table the table size grows to 64 GB, which causes a massive slowdown.

What is the best way to achieve this. Please suggest.

In the above screenshot, if i left join for every date there will be min 30K records per day. For 2 years the the number of rows will be 730*30K.

Re: SAS VA- Table join

MarkusWeick — Wed, 28 Sep 2022 10:41:11 GMT

Hi @debopriya85,

first approach: on what subsets should the "Rate" be calculated. If you just need to calculate the one overall rate, there shoud be a solution without joining the tables.

Re: SAS VA- Table join

MarkusWeick — Wed, 28 Sep 2022 10:44:04 GMT

Hi @debopriya85,

second approach: do you join by identity of the store_id? How many other columns are in the DIM_store tabel?

Re: SAS VA- Table join

Madelyn_SAS — Wed, 28 Sep 2022 14:15:38 GMT

In general, data should be prepared outside of Visual Analytics. If you look at Considerations for Data Source Joins in the following topic, it explains why data joins can cause performance problems.

https://go.documentation.sas.com/doc/en/vacdc/8.5/vareportdata/n167hzcxe4pi2yn1b50jv5ujgtor.htm

Re: SAS VA- Table join

debopriya85 — Thu, 29 Sep 2022 01:10:17 GMT

Hi Bruehi, I will only be joining based on Store_id, there are 10 additional columns in the DIM_Store table.
The rate calculation is dynamic based on the user selection of filters

Re: SAS VA- Table join

debopriya85 — Thu, 29 Sep 2022 01:11:50 GMT

Hi Madelyn,
I am using SAS script to join the datasets and creating a new dataset.