In this video, we will try to understand what exactly is our OLAP m OLAP and edge OLAP. So, our OLAP stands for real time OLAP OLAP stands for hybrid OLAP and M OLAP stands for multi dimension OLAP all of these three labs are nothing but you know there are ways by which you can go and deploy the cube in your SSIS and depending on how you choose the type of OLAP the behavior changes to a very great extent. So, first thing allow whatever OLAP cubes we have, we have deployed in our tutorials are all of type m OLAP. If you look at associators What does SSIS do? SSIS actually goes and pulls data from the RDBMS and stores it inside the cube. So, Internally this SSRS project what you create right?

What it does is it actually goes and fetches data from our DBMS table and deploys it into the cube and from the cube, the reports are generated. Now data what is fetched by the SSRS software from the cube has two parts to it. One is the data and one is aggregations. If you remember, in one of the previous videos we discussed about aggregations you know, where, you know when you have a parent and a child hierarchy or when you have aggregated values, it does pre calculation, if you define aggregation. So, when data is fetched from the cube, there are two parts to it. One is aggregation and one is data.

Now, when you go and you say that you deploy a cube in a in an OLAP mode, both data and aggregation is fetched from The SSRS cube. So, when you go here to your SSIS project, go to the partition tab and you can see that there is something called a storage setting. So, in the storage setting, when you go and you say that you are running the cube in an M OLAP mode, that means, the data and the aggregations are both coming from the multi dimension cube okay. So, you can see that over here, in this story setting I can say that, run this cube in an M OLAP mode or run this cube in an edge OLAP mode or run this cube in our OLAP mode. So, when we say that we are going to go and run the cube in an M OLAP mode, the data and the aggregation both are stored in the cube in a multi dimensional format and when any changes happens in the our DBMS those changes are not sent to the M OLAP cube, you have to actually manually go and say that build the cube or you perform the cube processing.

So, if you see here let us let us go ahead and let us set this let us set this cube in an OLAP mode. Remember that by default it is m OLAP right. So, I was doing some experiments and I had actually said this cube to our OLAP So, I'm going to go and set this cube to M OLAP. Now, remember that this m OLAP setting what I've changed here will only get applied when I go and create a new partition, right so, let me go and delete this partition at this moment. Remember in one of the previous videos, we have also discussed partition. So, remember, in order to understand this video, you have To understand partitions, and you have to understand aggregations, okay?

So I hope that both of these videos you will go and see. So over here now I have said the students setting in M OLAP. Let me do that right and I'm going to go and create a new partition. I will take the full facts sales table I will say next also I will go and design my aggregations. So I'm going to go and design aggregations as well. I will just take default values, I will count it next.

All these things we are discussed in one of the previous videos of aggregation. So you can see that he has he's creating couple of aggregations here. So almost like Lot of aggregations have been created that is good and I will go and I will deploy the process now right. So, it is going to go and deploy and it is also going to go and process the cube right right. So, let us enter so we are currently running our cube in an M OLAP mode. So, remember that at this moment we are in an OLAP mode.

So, in M OLAP mode, as I've said that the data comes both from the cube the data and aggregation both come from the cube from the cube right. So, that means if I now try to go and fetch data here, so, you can see that I am on the UI and let us go and run the profiler. So, I'm going to go and run Both the profiler, I'm going to go and run the SQL Server profiler. And also I'm going to go and run the profiler over the SSIS cube. So you can see that I'm actually running the profiler over the SSIS cube here, the SSIS Analysis Services. So I'm just going to clear and also go Let us go and run the profiler on the database engine, right.

So you can see that this is the cube, this is the SQL servers, right. So both of them are here. So now let us go and we try to fetch data. So if I go and say, Okay, give me sales amount. Remember that I am running in OLAP mode. So you can see that in SQL Server, I don't find that he's firing any SQL.

But if I go to my cube, you can see that he has fired Some SQL fired some MDX here and he has fetch data right. So, that means that when I am running in an OLAP mode, all the data and aggregation remember even we have aggregations right. So, when we actually pull up date, we will also see some aggregations, right totaling up and totaling down. So, even those values will be fetched from the SSRS cube. So, you can see that over here you can see that there are some SQL running in the SQL Server services, but these SQL are not actually fetching data, this is the OLAP query log right. So we can neglect it, but you can see that in MDX it is actually going and firing all the queries right.

So, when you are running in an OLAP mode, you are fetching the data and aggregations from the cube. Okay. Now, let us go and set this to our OLAP. Now when you go and add this to our lab. That means both the data and the aggregation will be fetched from our DBMS. Right.

And remember that if you want to go and apply these changes, what you need to do, you need to go and delete the partition again. Because you know, you cannot just go and change this mode and just deploy the cube that don't work. So basically, the new settings will be used in only when you create new partitions. So what I'm going to do is again, I'm going to go and delete the partitions here, when delete this partition, so this time the store is setting is our OLAP. So I will say new partition again the same process I will follow. Remember, whenever you change the settings from M OLAP to our OLAP, you need to go and create the partitions again So that is creating aggregations.

So remember that in our OLAP, both aggregations as well as the data will be fetched from our DBMS directly. So next and finish and I will say process the cube. So you can see now the storage mode is our OLAP. You can see over here, I'm moving my cursor. So it is auto lap deploying. Good.

So I can go back to my browser. So let's clear everything So, now, also let us go and create clear SQL Server and also the SSIS profiler right. So, no queries are firing here. So, now I'll say sales amount I will say boss name, I will say age range and so on. If you see now, you can see, he is now first thing very quickly, you can see that it is firing MDX queries to your SSIS but internally it is actually going and converting it to the RDB MS SQL queries, you can see that there is a select some year. So, that means that this says that he's actually fetching data from here for example, now, if I go and put date again and let us go back here, you can see that yes, he has fired some date query as well.

So this is the MDX And also for date. Let us see why he's not filing for date. Let me just go and click some more data here. And you can see now he's firing exclude SQL statements. So remember that the MDX queries will fire you can see that MDX queries are firing, but at the end of the day, they are using the RDBMS queries to fetch data. So, in other words in a when you are on the are OLAP, setting the data as well as the aggregations are both fetched real time okay.

And also, whenever there is change of data on the server, it will actually listen to this notification. Okay. So whenever changes happen on the on the on the our DBMS you know, those values will be propagated automatically to the SSA. Excuse me. You don't have to manually process them. Remember in M OLAP in M OLAP notifications are not received and you should do it, you should do the processing manually, right.

But in our OLAP the server first thing in our lab, we don't have to worry about this so called as a replication between the RDBMS and cube in a because the data is all fresh from our DBMS right, but still you can see the server will listen for notification when data changes okay. And the last thing what we have is an OLAP in H OLAP, the data is fetched from our DBMS and the aggregation is fetched from multi dimension. So, this edge OLAP is hybrid OLAP. So, basically, you know, the, the data the the, the bottom data, he will actually fetch from our DBMS The aggregated data he will fetch from your OLAP system. And whenever, you know there are changes in the RDBMS those changes will be listened and it will actually go and update your cube as well so you don't have to process the cube again and again.

So let us go ahead and see how Ecolab works. So, I have sent that to actual app again remember, if you want to go and set this edge OLAP to your partition, you need to go and create a new partition. So, let us do this whole exercise again. Count. So that I have created the partition as well as I have created the aggregation and as well as I have deployed the cube. So you can see that this partition is now with the H OLAP mode.

So in edge OLAP. Now what will happen is so let us go and clear the Both the windows. Now when I go and take aggregations means when I take sales amount for example, right you will find that it is not firing any queries in the SQL profiler, you can see that in the SQL profiler, you do not find any execute statements for it. But, over here you can find find that MDX query has been fired because this total is aggregation he is taking directly from the cube, but now, if I start putting more leaf node data for example, you can see now, I am put more detailed data over here, right. So, you can see that I've put more detailed data. Now, in this case what will happen is you will you will see that there are some queries which are getting fired in SQL as well you can see that over here there is some SQL query getting fired, right.

So, basically You can see again some SQL query getting fired over here right. So, basically, when I go to the, to the rows actual to the leaf rows or to the data rows, it starts getting data from the our DBMS. But if I just put aggregation that is some total or some kind of higher level data aggregation, he actually takes from the, from the MDX. So, remember, quickly, we have our OLAP where the data actually comes from our DBMS straightforward, right the sources our DBMS in case of OLAP. The source is our DBMS for data and for aggregation, the sources your cube and for m OLAP. Both the aggregation and the data comes from the cube.

Also, I talked about that in edge OLAP On OLAP if you go to h OLAP or our lab you can see that it says that the server listens for notification when data changes. In other words, basically, if you go and change data over here, for example, now, let us go here to the database. So, let's go to sales lab. Now, let us go and go here and say for example, I have some recording 2002 let us say I go and add more 1020 like this. The time I do that, you will see that in the SQL profiler in the SQL profiler, you can see that he has sent some event notification here to the Analysis Services saying that something had changed please update yourself. And you can see that I did not build the cube at this moment.

But if you see in my SOC s profiler, you can see that he has built a cube himself so he has process and build a cube himself. In other words, this 10 value which I have added here should be displayed right over here. So, if I reload this remember it is a value from 2002. So, if I go and refresh this not even reload actually, okay. So, let us see what value we have change let us go and put your wise data so, that we can understand what is changing right that is one clear this let me do like this, let me put the sales amount and let me just put the year so, that I can understand that data has changed. So, that it is so 2002 The record is 320 So, 2002 it is 320 right.

Now, let me go and make it 90, right. So, the time I make it 90, I go here and I do refresh, you can see this 90, why? Because internally edge OLAP sends a notification to the cube saying that the data has changed and the cube then starts building from scratch. So, remember that in edge OLAP and our OLAP we have notifications. So, once the data changes in the RDBMS these notifications will be will be sent and the data will be refreshed in the cube, but in M lab, we do not have this notification. You can also see that there are different flavors of M OLAP.

For example, one m OLAP is where the notifications are not sent, but the data is fetched from the data and aggregations are fetched from the cube, we have sheduled m OLAP in should you should use an M OLAP. The data comes the data and the aggregation comes from the cube. But you know, in this case, you know the processing is automatically performed every 24 hours. So every 24 hours automatically, the Mo Lab the shuttle MRP Go and process the queue, but remember that no notifications are sent in this case as well right. Then we have automatic m OLAP in automatic m m OLAP. Again the data is fetched from the cube, all the data and the aggregations are fetched from the cube, but the server will send notification and will perform the build right.

And then we have medium Latin cm OLAP in media medium Latin cm OLAP what happens is the process the processing is performed every four hours, right. The server will send will listen for the notification as well as the latency is done for every four hours in shedule m OLAP. It was for 24 hours. In low latency, the processing is done every 30 minutes. So basically here it is 30 minutes here it is four hours and here it is 24 hours. So basically, you know, depending on which one you choose from here will define your latency, right.

So if you choose m OLAP, then remember that the latency will be very high. In other words, when some changes happens in the source, it will take a lot of time to reflect to your Mo Lab, because you do process the cube, but the data will be fetched faster. If I'm in real, our OLAP Yes, the latency will be less. In other words, as soon as my source changes, my data will be reflected. But remember that because you're fetching from our DBMS, the queries will become very, very slow, the aggregations will become very, very slow. While actual app is a heart is a hybrid in a wherein, again, we have a latency here.

So this has a bit more latency as compared to an OLAP but has less latency as compared to a bit more bit more latency bit less less latency as compared to M OLAP. Bit more latency as compared to our OLAP. But here the data is fetched from the RDBMS and from the cube, the aggregations are fetched, right. So now the next question comes is that, when should we use what? Now, choosing between our OLAP edge OLAP and M OLAP depends at least on two important things. First one is latency.

If you want less latency between your source and destination that means between your RDBMS and your cube, then definitely you should think about our OLAP or edge OLAP. Because the time you go for m OLAP, you know, there is a processing happening and that processing will increase latency. But if you say that you want to get your aggregation as fast as possible, then definitely m OLAP is the way to go because the whole point about OLAP and SSIS is that they store pre calculated aggregations and pre calculated data which makes the fair Very fast, right? So take these two points in your head when you're actually selecting any one of these options. And also my personal opinion here, my personal thought here, that switching back to our OLAP should be the last option. Because if you wanted to fetch data from our OLAP, why did you go and design the whole cube?

Right? So basically, I can understand you're on edge OLAP, or I can understand you are on M OLAP. But it will switching back to our OLAP forever, is not a good idea. So sometimes I understand you want less latency, because some of the users really want to fetch the report as fast as possible. They want to see the real time data. Yes, our OLAP is good.

But if you're talking about fast retrieval of aggregations, fast retrieval of data, then there is no replacement for m OLAP. As a best practice, I always stick to M OLAP. Until you know the customer starts complaining saying that no, I want to see the real time data. Then only I go and fall back to our lap. So I hope that you enjoyed this video. In this video we were trying to understand what exactly is edge OLAP m OLAP and our OLAP and we also discussed about that in which situation we should use them.

Thank you very much