In this video, we will see how we can run SSH q processing as a background process. Now first thing let's try to understand that why do we want to run this Q processing as a background process? If you see at this moment, what is SSE is doing what is the task of SSIS the data is actually stored in our DBMS table. So if you see here on the screen, our data actually gets stored into this fact customer table right into our DBMS. From there, the data is printed and it is pushed into this SSIS database, right. So this project of SSIS, what we have here, then we go and we say process, this cube, right, this queue processing, what we do here actually fetches the data from the RDBMS and pushes it to the SSIS database.

Now definitely when you are on production, when your project goes live, Nobody will install this Visual Studio on production and nobody will go manually and do processing every minute or every hour, we want this process to run as the background continuously. But first, let us try to understand that if you want to process the cube in production, without SQL Server Data Tools, how will you do it? In other words, let us say that you don't have this SQL Server Data Tools and you will not have it in production environment, you will not install a development tool, right? This is a development tool. This is for you and me. Okay.

On production, what you have is only these instances what you see here. So in production, you're going to have our SQL Server, you're going to have your SSIS and your to cube is deployed over here. So now let us say, in the fact customer I go and I change this 400 to 1400. So with that what will happen the total sum is now 695 Okay. Now, this data is updated in the RDBMS. And let us say if you go to the SSIS you will not find this value, why because we have not processed the cube right.

So, if I go to SSIS and let me go and fire MDX query, remember we talked about MDX query in the previous classes when we said that MDX is nothing but it is a query language for your SSIS database. So, the way we have SQL for our DBMS in that way, for SSIS we have MDX query. So, now if I go and file an MDX query here, so, if I say new query MDX So, let me go here and say select SELECT FROM customer data warehouse right. So, if you see here the value is 4952, but in the database, the value is 6952 right. So, in other words, the process has not ran and the data has not been pulled from here, over here, right. So, now, let's say you are in production and you are supposed to go and process this cube.

So, how do you do it? So, in order to go and process the cube, what you can do is you can right click on this menu over here, so, you can go to the Object Explorer. So, you go to Object Explorer, go to that cube and say process cube. So, if I go and say process here, it pops up the same screen. So I will go and run this. So then this it has run and Once this is completed, now if I go to my MDX query, so you can see now the value of 6952 and here the values also signify So in other words, The value is now synced up between the RDBMS and the SSIS database.

But now again, the same question here is that nobody click on this process cube every minute, right? We want this process cube here, this thing what I've done here, we want it to be automated. So we want it to run every minute must be or every, you know, in the evening times or something, right. So we want this process to be automated. So in order to automate this process, the process cube method, let us understand that what exactly happens when we click on this process menu here. Now when you actually go and click on this process, and you do the processing, internally, it creates an XML a query and fires it on the SSIS cue database.

Okay, remember in one of the previous videos, I said that we have three kinds of queries which we can fire on ssps The first one is MDX. MDX helps you to To get data from SSIS The second one is DMX ino, which is used for data mining purpose. And the last one is XML a query, which is used for SSIS administrative jobs like backing up the cube or processing the cube. So XML A is a administrative query language, okay. So, so when we actually do process it actually fires that query. So if you want to see the query, what is exactly getting fired, you can click on the script here, and you can see that script action to new query window.

The time you do that, you can find this query which is in a format of XML, a XML is is brought to the query window, right? So, if you wish, you can go and fire this query from here as well. So internally, actually, this XML a query gets executed. Okay, so we can see that did actually find the query it did some certain things and we can see the results as well. Right? So what we have to do is we have to actually take this query and run it as a background process.

Now, you can see from the XML a format or the XML XML is quite complicated. And frankly, I would not advise you to learn XML, okay, until you are really administrated traitor of SSIS cube or something. So, I would advise you to really read XML it, you know, what you can do is, you know, at various points SSIS has given the script in AWS and then you can just go and say scripted, and you can bring that query onto the window and use it. So don't go too much on saying that, okay, what is this database ID, what is this object? Right, it is pretty understandable, but you don't have to learn XML especially for it. If you want to spend time you can learn MDX that is understood.

So ex MLA, don't worry too much about it. You get various points, you know, from where ssss has given the script a new query window from where you can get the XML script. So I have the script here. Now the only thing what is pending is to run the script as a background batch batch process, or as a background process so that it keeps running every minute or every 10 minutes or whatever. In order to run this SQL job as a background process, we need to use someone called as the SQL Server agent, SQL Server agent is nothing but it's a simple kind of software I will say or utility which is provided by SQL Server, you know, which helps you to run any SQL job as a process as a background process. So let's go and start this SQL Server agent.

At this moment you can see that this SQL Server agent is stopped. So you can see this red sign here indicating that it is not running. So you can either go and start from here or you can also use your services. So if you go to services dot MSC, so from the services also you can go and start SQL Server. Okay. So you can see the services, what are running in my computer are here.

So we need to go and start the SQL Server agent. Now remember that you can see that I have multiple versions of SQL Server installed like Express and 2014 and 2012. So that's why you can see that we have multiple SQL Server agents over here. So this one is actually SQL 2012. I think so. So let me go here.

It's Yes, it is. 2016 accuracy. 2014. Okay, so let me go and start the SQL Server Analysis Services for 2014 or it's already started. Okay, so looks it looks to me that this is okay. It's not started.

So we'll start this. So once so you can either go and start from here, the SQL Server agent, or you can also click and start from here as well. So I've started from there. So let's just let me refresh this you know your it should now be complete. So that SQL Server agent is running and that is good. So let us now go and create a job which will actually run this XM la query.

So I'm gonna do a Ctrl C over here. And let's go to jobs. And you can see that there are some jobs which are running here but let us go and create our own job. So I'm going to say new job and let us give a name to this saying that okay, this is SSIS job. Now, remember that when you say a job, which has to be executed as a background, a job will have a lot of steps. So it will have step one to do something then step two, step three.

So we can see that once you are given the job name, the next thing is you need to go on Add steps. At this moment, we don't have a lot of steps here, we just have only one step where we have to go and execute this XML query. So let me just click on new step here. And this step, I will just go and add that XML query. So you can see that you need to click on this new step button. So I'll say that so what is this step?

This step actually executes XM LA. So remember, you can have multiple steps if you wish. But at this moment, you know, we don't have multiple steps. So the next thing he says is that so what is this kind of SQL? Is it a T SQL? Is it a SSIS package, you know, so I say okay, this is a very simple SQL Server analysis query, okay.

And let him run inside this service account. And I will paste this query here. He also asks for the database name, the instance name, so I'm going to go and Copy the string copy the server name over here and the database on which he has to fire is the SSIS customer okay and I will just say okay so I have added the job I have added the steps now I need to go and add the shedule shedule means know when you want to run this exactly do you want to run it every five minutes? every two minutes right? So I'm going to go and say that let us run this every one minute okay. So I will say this is a one minute shedule file so this occurs this occurs daily and it occurs every one minute okay.

So it starts from here itself right. And I will just say okay and understood okay. So, once you have created the job, it will actually show you the job in the SQL Server agents things so you can see that this that is our SSH job, which will actually keep running So let us right click and test this so I'm going to go and start the job and you will see that it is working first thing or not. So you can see that it is giving some kind of error. So if I go and see the history so to see the history you can go and click on View history right click and view history and once I do a view history, you can see that it has ran multiple times because I'm telling you to run every one minute right? So let's go and see that what is the error so I'm gonna go and see this as a big part of their syntax is incorrect.

Okay? Don't be do a copy paste properly, actually able to see and Google properties Click on the step Oh, okay. So, what we have done is actually we have said that this is a SQL Server services query. That is wrong, right? It is not a query. It is actually a command a query means actually an MDX query.

Okay, so let me just select this command here and just say okay, okay, so we selected the wrong type. So I'm going to go and start the job again, and we'll see if there are problems or not. And remember that if there is a problem, we'll have a look at it. When this These tutorials are getting recorded, what we do one thing what we do here is in responders, we, if we get an error, we don't hide it out, we actually show it okay. So you can see that one more error has occurred and let us see what it is view history. So this should be the last one at the top right.

At least I should not get that syntax error. And you can see that now it is not showing me the syntax error. But it is telling me that my SQL Server agent does not have the permission to process the cube. Okay, good. So, we need to give permissions to the cube. So what I'm going to do is at this moment, this SQL Server agent if you see is running by using this account that is the SQL Server agent service account right.

So let us go here to SSIS. And let us add a new rule. All the SSIS security is in this folder of rollio. So I'm going to add a new role. So we say that this is for SSH job. We need to specify the user right so remember, for SQL service agent, let us see that What was the user name under which the service was running.

So let's just check it months, we can see lots of errors coming there, you know, because we are running every one minute remember. So we'll go here and it is saying that this empty service, SQL Server agent does not have permission. So I'm going to do a Ctrl C here. Let us go back to the role and in this role, we will say that we want to process the database and we add that user to this role check names Okay. Okay. So now that we are given the permission here in SSIS, let us right click on this job and let us just test it once manually.

So you can see that the job has started and it is executed successfully. Very nice. But so now let us go and check if our job is running or not. So what I'm gonna do is I'm going to go to the fact tables. At this moment in my associates queue, you know, we have this value 6952, right? So let me go and make this as, let me add one more thousand over the years.

So let me make this as close, right. So I expect that after a minute, automatically 1000 should get added here. So I'm going to go and execute here. So you can see that it is still executing. Remember, the job runs every one minute, okay. There it is, you can see now the value has changed.

And if you want to see the job history to see, we can see that he's actually executing in green, right. So that was a very simple example of how to execute SSIS queue processing using your SQL Server agent. So this was one way of excluding the SSIS cube. That is by using the SQL Server agent Now, a lot of times we want to build the cube in SSIS itself. So, for example, as soon as the SSIS package runs, it loads all the data into your database, you know, after that you'd like to go and execute the SSIS process cube building. So, let us see that how we can go and build SSIS queue through the SSIS package.

So, let me go on add a new SSIS package here let us name this package as SSIS queue Okay. Let me go to the toolbox. So, we have this nice component here called as Analysis Services processing tasks. So let us go and drag and drop that there is to edit. The first thing is it asks you for saying that okay, which Analysis Services you want to connect to. So let us click on new let us click on this edit connection string.

And let us give our server name. It is a Windows Authentication and SSL skill. And now he will he's also asking saying that okay, what objects you want to process? In other words, do you want to just process the dimensions? Or do you want to process the facts or you want to process both of them? So at this moment, I will say, Yes, I would like to go and process the complete queue.

That means the facts of it, and whatever the dimensions are, so I'm going to go and process the whole cube over here at this moment. Okay. And that's it. So I'll set this a startup set page as startup. So now, let us say I make Is 2000 as 3000 right so 1000 extra. So, after the SSIS package as ran we should see your 9952 right 1000 extra.

So, let us go and run this. So, that our analysis Analysis Services processing is happening through SSIS now, and has completed So, let us go back here, let us execute this and there it is 9952 that means that the SSIS package was processed. So, that brings us to the end of this video in this video we will learn two things. The first one is that how to run the SSIS queue processing as a background as a background process. And the second thing we saw is that how we can do SSH q processing using SSIS. Thank you so much.