Impact of dash filter on sql-cube interaction

#1

Scenario:
Very large data table in sql, Dundas data cube that pulls from it. Dashboard with a date filter, set to today by default, that connects to that cube. So, that date is part of the query that pulls from the large table, limiting it to 1-day’s data.

If I allow users to alter that date filter (it’s currently locked from them using it) and all 10 users choose a different date, that’s going to load 10 days’ worth of data into the cube, right? Also, if one user opens 7 different days, that’s a total of 17 days’ of data loaded into Dundas now, right?

I know this sounds like a very obvious question, but I’m trying to understand the possible impacts of opening this filter up to users on system memory and application-database use.

#2

Hi Ken,

I assume the cube you are discussing is not warehoused in any way, otherwise the question would not make much sense to me (just to be sure) …

As far as I understand, a cube that is not warehoused is nothing special, it is basically just a combination of queries on your data source and calculations on that data on your Dundas server. In your case, it may only be a single SQL command carried out at your SQL server and the resulting data sets are transferred to Dundas BI for processing.

So, when 10 users choose 10 different filter values, it means that 10 different queries are carried out on your SQL server. Well, not exactly 10 different queries, more precise it would be 1 query with 10 different parameter values on that query.

So, no matter what filter values the users choose, it is always 10 queries on your SQL server with 10 data sets returned to Dundas BI for processing. As far as I understand, these data sets are computed independently anyway, so it should not make much of a difference if it is the same data in all 10 occasions or if it’s the same data all the time.

A few caveats though:
1.) If you query 10 times the same data on your SQL server that may be a lot faster as the SQL server caches these pages in memory if it can.
2.) There is some caching in Dundas BI that I have not yet experimented with, for example there is a setting on the metric set level. If there is an effective caching going on, it may reduce the number of queries in your example if the users select the same filter values. However, I am not aware how well this works.

Note: This is not inner knowledge of Dundas BI but just some experimenting I did coupled with the docs I read. There may be much more to it than I think.

1 Like