AI/BI Dashboard Caching: 100K Rows, Filters vs. Parameters

0:04Hey folks, today I have the pleasure to host Andrea Picasso for the first time in the channel. Hi Andrea. >> HiF, how are you doing? >> Awesome. Awesome. Can you tell us more about yourself? >> Yeah, it be super short. First of all, nice to meet you all in the channel. I'm Andrea Picasso. I'm a solution architect for Databicks based in Italy, specifically in Milan. handle mostly

0:26governor uh government companies and now I'm here and today I'm here to talk to you about AIBI which is a strong focus I'm having on datics so specifically AIBI focus >> and you're right and today's topic is a very interesting and important one caching mechanism in the AIBI dashboards and you all know that dashboards are at

0:49the heart of the BI today but nothing really nothing destroys trust in analy ICS faster than a slow laggy dashboard. >> Yeah, you're right. What what we see on the customer is that they always people are always focusing and thinking on I want to build the fanciest dashboard possible. I want to have the beautiful visual. But really little people think about a what about optimizing for

1:13performances? What about focusing on caching? While caching is really the key part for you. If you want to have people happy and using your dashboard, you want them to be fast, not only fancy and cool to see. >> That that makes sense. Can you break down the core pieces that matter most like data sets, filters, parameters, and how each of them impacts the caching?

1:36>> Yeah, there are three key elements that you need to consider when you build a dashboard. Let me share my screen and show you something. Okay, so first of all, first element you need to consider are data sets. What are data sets? Data sets are built from queries are starting from the data you have on unity catalog or you can also add data coming from external sources using the car federation data sets are the basics the

1:59place from where all your visualization and all your dashboards will use and we start using data. So it's really important to understand and to use properly data sets. Other two important elements that you need to consider when you build a dashboard is how to filter the data that you are visualizing. First is using filters. Filters they work directly on the browser cache. So they

2:22are really effective and they give you results in instant. While on the other side you have parameters. And when you use parameters you have a parameter directly into the query. So every time you run or change one specific parameter, the query is sent back again to the back end. So you can get either a result from the back end query cache or otherwise you execute the query. So

2:44those are a little bit more slow. We will see later what this means. But it's really important to consider those three elements. So stressing again data set the place from where your data starts and comes and then filters against parameters. So just to summarize filters are local and very fast while parameters will go and look at the back end to run

3:07the query back again. And for data sets, if you can minimize and like avoid the famous select star, select the right columns and maybe add filters on the top of your data tables, that would be that's still going to be good. That's right. >> That's right. That that's exactly the approach you need to consider. So parameters are not local while directly to the back end while filters are local.

3:29>> And can you dig a little bit deeper on how does this mechanism of caching work in AIBI? >> Yeah, let me show you a chart to do that. Okay, in this image you can see how the caching works inside the ABI. You need to consider that there are two level of cache. The first one is in the browser. So the browser cache and the browser cache can keep up to thous uh 10

3:53100 thousand of rows. That's really important to consider this because every time you run a uh you change something in the dashboard and everything is executed inside the browser cache, you get an instant response. While every time you go back to the workspace, so to the back end, the query either is taken from the query cache or otherwise

4:15executed on the warehouse. And in that case, the t the time and the time you wait for the query could be a little bit longer. We're talking about hundreds of milliseconds or a few seconds in case of complex queries. So it's really important to consider every time possible to keep your visualization running in the browser cache because that's the place where you will get instant answers >> just to be sure when I whenever I use a

4:38parameter does it each time hit the query again or or it will bring back the caching the the results from the caching mechanism. >> Okay. So without going too much into details into the cache that you have in the workspace because that one also is divided into different layers but let's consider it as only one layer. Every time you tune and you change one parameter, what's happening is that the

5:00query goes into the back end check. If the same users has executed the same query in the past 24 hours, it will get the result directly from query cache. And this is super fast. While if the same user hasn't executed the same query in the last 24 hour, in that case it will go directly to the warehouse and will be executed. One important element

5:22to consider here is also the way you manage configuration of the security of a specific dashboard because you can have credential which are embedded or credential per users. So if you have embedded credential, every user running on the dashboard will use the same credential which is really useful for caching because every query executed

5:44from the different users will go directly in the same cache and this is super useful. On the same on the other side if you need to have separation of duties or you need to have specific row filters in that case you need to leave the users permission. So if you use user permission, you're going to be super precise in the way you split the visibility on rows and columns, but at

6:06the same time you will lose some flexibility on the cache. >> That makes sense. So basically basically you're saying that we need to like when you share dashboard whenever embed credentials we will be sharing the cache which means we will benefit from this uh this performance improvement. In addition, prefer filters over parameters. We need to make sure that

6:28the data is lower than 100,000 rows. >> Rows, >> right? >> Yes. Correct. And consider that one other element I want to show you because till now we have been just discussing. I want to show you also the difference in practice. Let me share the screen. >> Yeah, that's right. >> Let me share the difference in practice. Okay. So, what we have here? Here we have a dashboard with different visual.

6:51All those visual are based on the same data set. So again coming back to the first element the data set are is the data behind those visualization and the department in this case is a filter. So every time I change this we will see that the answer is instant. As you can see the visualization changes without any query being executed in the back end. Super quick. On the other

7:14side, if you want to understand when a specific execution is executed into the browser cache or into the back end, you can easily see from here. If for example here I have a parameter and I change it, you can see that there are some spinning wheels super quick. In this case, I can't even see them because the queries executed lightning fast. But you see some spinning wheels here. You can see

7:38it again. While if you go directly in browser cache for example with filters you see that there are no spinning wheels and the result is instant even faster than the one before. >> Yeah that makes sense. And so I do understand from what you're saying that the caching mechanism is not only a detail. This is really the core core of the performance of the AIBI dashboard in

8:00addition to the uh tweakings you can do by for example bringing I don't know doing filters reduce the number of data you're joining or like getting rid of all the columns that you may not want. And this and it's not about just building fancy dash fancy graphs that you manage but also build something performant and fast and costefficient.

8:22>> Yeah, that's correct. If you want to summarize my uh key findings and my three suggestion for for the people using ABI dashboards are first try always to keep data set below the threshold that we described. So 100 megabyte or 100,000 rows per data set really important. Try as long as you can to embed credential in the dashboard. In

8:45this way, the caching will be uh shared among the users. That's really really important. Again, last element but not least for importance, try to use filter as much as possible to avoid going back to the back end but work directly into the browser cache. And also when you need to use parameter, they are not a nightmare. You can use them, but use them strategically to try to reduce the

9:07size of your data set to keep everything in cache. Really important. >> Awesome. Thank you so much, Andrea. And I think we're gonna see you very soon for a second video on the per performance optimization on the top of AIBI dashboard. >> Thank you. It was a pleasure to be