I am looking for a best data model design to handle many-to-many relationship between the existing tables which will improve the performance of the dashboard.
How best we can tweak the existing data structure/model to improve the performance of dashboard either with Live or Extract connection? I am more concerned on the data structure in terms of performance than on visuals.
Agg- Has 15m records
Fact - 37m records
Security - 20k records
Quick Filters: 15 as drop down
Sheets: 20 sheets
Dashboard 1: For every action filter selection on individual sheets & quick filter selection, data has to drive the Sheet9 only. when user click on the # to know more details and it should take to other dashboard to show only relevant values only(125)
Dashboard 2: When user click on any SIN(22313126) then it has to show only relevant values to that SIN.
When any of the 2 tables joins are used for any extract, would lead to Cartesian results which will blow up the data.(15mx37m or 15mx20k)
Agg is joined with security as live connection(becaz, when pulled the columns from Agg/Security as a quick filter, we get all DISTINCT column values irrespective of user login, which is wrong as per row level security. Which means that user will be able to see other users values from the drop down(using cross data source filters), however there will be no data in the dashboard if user selects any other field which is not relevant to this access and which will confuse/misguide users. Fact as an extract. Currently it is taking 1-2 min to render the dashboard after implementation of partition/Index on live connection only. But I am facing some performance issues with this implementation. Thought of trying a different data model which will improve the performance.
Please find the sample data and workbook for your reference and let me know for any other details.