SAS VA- Table join

[debopriya85]

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

[MarkusWeick]

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.

[MarkusWeick]

Hi @debopriya85,

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

[debopriya85]

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

[Madelyn_SAS]

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 

[debopriya85]

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