BookmarkSubscribeRSS Feed
debopriya85
Calcite | Level 5

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

 

debopriya85_0-1664360357047.png

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. 

5 REPLIES 5
MarkusWeick
Barite | Level 11

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.

Please keep the community friendly.
Like posts you agree with or like. Mark helpful answers as “accepted solutions”. Generally have a look at https://communities.sas.com/t5/Getting-Started/tkb-p/community_articles
MarkusWeick
Barite | Level 11

Hi @debopriya85,

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

Please keep the community friendly.
Like posts you agree with or like. Mark helpful answers as “accepted solutions”. Generally have a look at https://communities.sas.com/t5/Getting-Started/tkb-p/community_articles
debopriya85
Calcite | Level 5
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
SAS Super FREQ

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
Calcite | Level 5
Hi Madelyn,
I am using SAS script to join the datasets and creating a new dataset.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 855 views
  • 0 likes
  • 3 in conversation