if you have a tabular cube set up in analysis services you'll need to refresh and reprocess
that data at various times and in different ways in this video i'll walk through a couple
different ways that you can do that and then you can decide for yourself what makes the most sense
so i'm logged in here to my analysis services i have one cube called tabular demo and that's what
we're looking at here if we open the properties that's just from right clicking and selecting
properties what i'm looking at here is the last data refresh we can see this happened on the 12th
around 10 40 p.m i guess i was up working on this and what we want to do is refresh this the first
way you can do this is just manually it's very simple right click and select process database
that's going to open up this module here and you can select either default full
clear or recalc depending on what you select you'll get a definition here for what actually
is going to happen in this one full it's going to load all data into everything and basically
everything's going to get updated so for this demo we'll just do that we have one model if you had a
couple different ones you could run those specific ones and select ok success and here's a script
that ran and that's it if we refresh this database folder here it will refresh everything below it
and go back to properties and here we go we can see this now changed the last data refresh was
on the 16th that's today and we're all set that's how it's done let's say we wanted to run that as
a script instead of pushing the the manual buttons if you go back to this process database here let's
select full and select this drop down here what it will do is you can you have a couple different
options of where the script could go we'll say script the action to a new window select this
and close this out and now we can see it created this xmla query of exactly what's happening
so what you could do is take this instead and just run this it's going to execute it and succeeded
if we go back refresh and look at the properties this is now a little later than it was before when
we did it now another way you could accomplish refreshing your cube is through ssis one of the
options is analysis services processing task this is designed specifically for this purpose so let's
drag this on and see what happens we'll go into this editor not going to worry about the naming
processing settings here is where you'll need to set up a connection manager and if you don't have
one you can select new but we'll create a new one let's select edit and open up another window here
it looks like many other microsoft connection forms the name of my server and if we test this
now it's succeeded you can select a specific user if you want if we drop the catalog down
our cube shows up i'm just going to select integrated security test connection we're all set
okay so now this connection string is updated okay and we're back here all right so now processing
configuration let's add and here's where we can actually select specifically if we want to
process individual partitions or individual tables within this model you know up to you in our case
what we're going to do is do the whole thing and just process the whole model here we have
those options again you know depending on what you want to do we'll do a full one and you can
change the settings in terms of how you want it to process do you want it to go sequential meaning
one has to finish before the other or do you want it to happen in parallel you know you can spread
out the processing these are some more advanced options that i'm not going to cover in this video
but they're there if you want to tinker if you want to play around with them and adjust how
this step would run expressions we don't have any for this one so let's go ahead and select
okay now i'm going to save and let's run this i'm going to execute this task let's see what happens
okay success so now let's go back to sql server management studio we'll refresh again
and the last time i believe it was at around 6 something p.m let's see what do we what we got
now and now it's at 703 so that's a little later meaning that did work correctly we were able to
process it through that component in ssis now there's actually another task within ssis that
can help us which is this one right here called analysis services execute ddl task and that's
what we'll look at next so i've dragged this one on here and let's hop in here and see what
we got now what this is going to allow us to do is run an actual query rather than based on
what that last component was which was a little more configuring now let's go to ddl connection
we've already set this tabular connection up to our analysis services so we'll select this one
source type you know we can either put it in directly point to a file that may have that
code within it or a variable that accomplishes the same thing in our case we'll do direct input
now what we want to put in here is the the snippet that we ran
manually as well i'm going to hop back in here and here is this code that i'm going to grab
here so i'm going to copy this paste it here this is going to essentially do the same thing
in ssis that we did in management studio select okay and now this is going to directly run
this statement here again this could have been in a variable or read from a file as well
no expressions okay so now let's run this task just this task again if we remember the last
refresh it was around 703 let's make sure that this ran correctly refresh properties
706. okay so that worked as well these are two of the common components that you can
use if you wanted to include that processing task within your ssis package at any point
the last way that i'd like to show you on how to do this is through a sql server agent job
perhaps you know that's something you want to do i'm going to connect to my database engine
here for some reason i chose to name these both demo and let's add a sql server agent job here
i'm going to right click new job and we'll call this process q keep myself as the owner not too
concerned with this right now we'll go to steps new step and we'll call this process cube again
but the type we're going to select is a sql server analysis command and we'll run it as the agent
the server will be mjkpc slash demo which again is it's going to look over here and the
command we want to run is going to be that same one that we used in the ddl and the same one that
we ran manually before so i'll copy this here as well and put that in here so we want this to run
through a job instead of manually or through ssis you know it's just another way to do it this is
actually not going to pass at first but i'm going to show you show you what happens so let's go
ahead and start this job and see what happens and we can see it failed and the reason is permissions
what's happening right now is it's trying to use that service account because remember we said
you know we're running as the service account it's trying to run a process command on our
on this here but it doesn't actually have permissions there's no role there's nothing
set up for this to work and this is a critical step so what we need to do here is create a new
role and assign that that sql server agent user to have process database permissions and that
should allow it to work and the permissions is for this case we just want process database
because that's really all we want this guy to do now membership this is where we want to specify
the service account from the database server add find the name let's go back to our
database server and it should be under security logins we are concerned with this one nt service
sql agent demo and if we go back here now paste this check and it can find it so now okay
okay now we have service accounts assigned to a role that has process database permissions
so before we do anything here let's take a quick peek at the time 7 11 was the last refresh now
let's again process this cube and see if this now works okay so now it worked meaning that
user was able to get on and process the cube as we expected and if we refresh here that should
be done because it was you know obviously this is a small cube properties 7 15 so it worked
i hope you found this video helpful there's probably even more ways that you could do this
and you could get more creative with you know just the foundational things that i've
shown in this video to to automate things or you know make it a little more dynamic
totally up to you but to get you started and to get you going i hope this is helpful for you
if you did find it helpful you know please drop a like on the video leave a comment i'll
do my best to answer the questions and if you're interested in more content like this i'd love to
have you subscribe as well thanks for watching and as always i'll see you at the next video