Forum Discussion

reema's avatar
reema
Data Storage
05-19-2022

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

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-details/m-p/1381#M67

 

Any help is appreciated!

Thanks!

 

 

 

 

 

dashboards & reporting

5 Replies

Replies have been turned off for this discussion
  • HamzaJ's avatar
    HamzaJ
    Data Integration
    05-19-2022

    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's avatar
      reema
      Data Storage
      05-20-2022

      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's avatar
        HamzaJ
        Data Integration
        05-20-2022

        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