Thanks Hamza, that helps.
But I also want to know if there is any way to drill down from say a Summary/Datamart(Subset of Original Table) to the actual Original table having millions of records without compromising on the calculations in a dynamic way. But I think there must be limitations to it in case of this scenario.
As the client requirement is to Show only few records taken from a psuedo table like a summary for quick access. And then drill down to the Big Table(with millions of rows).
As when I tried to drill down from (Order_Year in Summary Table) to the (Select Order_Date(YQMWD) in Details Table) it drills down the date properly, but gets wierd values in Revenue Column(Which is taken from Summary table and not from Details Table). When Revenue column is taken from Details Table it gets proper Revenue values on Date Drill Down.
So just wanted to know if there is any dynamic way of drilling down from one small table to another big table and not compromising on the Values/Calculations. I guess it would have to be done using Jump To Dashboard feature to link to more detailed dashboards as mentioned in the links given by you. As values in both tables will vary and not connected with each other. Anyways thanks! I shall check the BLOX Options mentioned by you. As I just need to do a POC for now as to what are the different options for Optimizing the dashboard for big SQL Server Tables, with a user friendly design. And I guess these should do the job. 🙂
I would do it as you have described. Take revenue from the details-table. We have tables with >30 mio records and (unless you show every individual record) Sisense handles this fine. No long loading times. And its easy in use for users.
Using Jump to Dashboard is nice, but it does add an extra load as a new dashboard needs to be loaded.
You could also think about single-select filtering on year(or month etc) to avoid loading to many records.
As a final solution , you could check out the Expandable Pivots of the premium partners. Those support expanding and collapsing fields and also determine how many records are loaded on the first go and how many records may be loaded when the user presses the button.
Good luck
Thanks again for the different insights!
Can I know what type of datasource you connected to in Sisense?
As the loading speeds vary right with different types of Datasources. Just curious as Our's would be more than 100's of million's of records that too using Traditional SQL Server and not cloud based.
For our tables that are this big, we do not use live-query as that would put a big strain on our DB-server. We import the records into the elasticube (datasource is MySQL) and build/query it from there without any issues. In our case importing we can do via accumulative builds. Speeds things up
