Drill down from one dataset(sql table 1) to another(sql table 2).

[reema]

I am fairly new to sisense.  I have this situation as below:

I have connected sisense to a "Sample Sales" SQL Server Database with 2 Tables:

Database : Sales_db

Tables :

1. tb_sales_summary :

    2.  tb_sales :

In Sisense I have done an inner join on these two tables on field "Order_year" just to keep things simple for fetching data on drill down.   [My approach may be wrong here.]

My concern is how do I drill down from tb_sales_summary to tb_sales table, say for example to get the quarterly, monthly, by week, days report with correct calculations for revenue.

Drill down like:

Year, Revenue

Quarterly, Revenue

Month, Revenue, etc

In the Sisense Dashboard I want to show Year, Revenue fields both from Summary table[tb_sales_summary] only as it will be faster that way on first glance.

Instead of showing a Pivot of details table getting the Summary[On dashboard : which can take more processing time in case of millions of records] and then drilling down further in the same table itself, Is there any way of drill down between 2 tables i.e first showing a Summary table(which will show up faster) which is then able to Drill Down into Details Table(tb_sales) as it will be more efficient that way.

I found this on sisense community where they say there are limitations with Pivot table for performing drill down between different details or views. Let me know if there is any other approach for same.

https://community.sisense.com/t5/build-analytics/what-are-your-preferred-ways-for-making-a-lot-of-de...

Any help is appreciated!

Thanks!

[HamzaJ]

Hi @reema 

I think there are multiple ways to accomplish what you want to achieve;

1. Drill down: Right click on 2018 and select Choose, search on order_date and select Year, Quarter, Month, Week or Day.

2. Drill down with a predefined hierarchy: You can also set a hierarchy for users so its more easy to select YQMWD. Instead of searching and selecting, its predefined (https://documentation.sisense.com/docs/manage-drill-hiearchy)

3. You can use Blox to create clickable buttons that changes a dimension or granularity

https://support.sisense.com/kb/en/article/change-date-granularity-of-a-widget-using-blox

https://support.sisense.com/kb/en/article/switch-multiple-rows-in-pivot-as-viewer 

Hopes this helps

Hamza

[reema]

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

[HamzaJ]

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

[reema]

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.

[HamzaJ]

@reema 

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