- Yooo! What's up? It's Patrick from Guy in a Cube, and I was recently working with someone that followed my video, "Avoid a full refresh
using Incremental Refresh," and they were getting a
very interesting error. And in this video, I'm gonna show you how we
got around it, stay tuned! (melodious, playful music) If you find this for the very first time, be sure to hit that subscribe
button and stay up-to-date from all the videos from
both Adam and this guy. Okay, I was working with someone and he had a large volume of data. And in this video I did, "Avoid the full refresh
with incremental refresh," you actually refresh individual partitions because they just couldn't refresh it all 'cause they were getting a time out. And so when they tried to
refresh the individual partition, they actually got a
very interesting error. So you guys know how I like to do, instead of all this
talking, let's do what? Let's head over to my laptop. What they were doing was, they were trying to use the XMLA endpoint. And yes, you need Premium
Per User or Premium to follow along in this video. They went to the dataset and
then they went to the settings and then they went down
the server settings and they copied it. They went over to Management Studio and they chose Analysis Services, and they popped it into the Server name. And they clicked Connect. And after a little bit, you know, they went through the
authentication process and then finally they
landed on their databases. So these are the different datasets. And so they went to the model where they configured incremental refresh, and into Tables, found the table, right-clicked on it, and
then went to Partitions. If you haven't watched the video I did on avoiding the full refresh, you should go check out that video 'cause it'll help make a little more sense of what's going on here. Because they had hundreds of
millions of rows for partitions that are trying to do
that initial refresh, they just wanted to load the data for each partition individually. But when they came here, see, like, 2016, I don't have any data, and they clicked Process, this little window open and
made sure 2016 was selected and they click OK, they got this error: paramter name already exists. And I was like, "Hmm-Hmm." So I was actually on the phone with Holly. Holly works on the CAT team. What's up, Holly? Holly helped out a lot with this. And I was like, "Hmm, I
don't know what this is." So you know what we did? We emailed Christian Wade. But before Christian got back to us, we actually figured it out. So let me show you how we solved this. I'm gonna go ahead and get
out of this. Let's go here. Let's disconnect. I came back and I connected
to Analysis Services, the server name is there, I went to Options and I put
the actual dataset name in. 'Cause I was thinking maybe because I specified the initial catalog, I need to make sure that I'm
connecting to that database. So I clicked there, click Connect, and expand the databases, and I was already suspect because all the databases showed. But I said, "You know
what? Let's give it a try." So in here, go to Tables
and right-click here. Go to Partitions. And I tried it again.
Let's see what happens. I got all my rows and
I'm gonna choose Process. And then I'm gonna do it again. Click OK. And bam, I got the error again. That's when I'm scratching
my head and I was like, "Well, oh, Christian, can
you hurry up and email back?" 'Cause we were on the phone
(laughs) with the customer and we couldn't figure this out. So then I had another idea. Let's go ahead and do a new
analysis service connection, and let's get rid of this initial
catalog and connect again. And then we're gonna go
back to that database, go down to Tables > Partitions,
and Process and click OK. Aargh! How do you fix this? And then I remembered, Adam and I actually ran
into this problem before with another customer and
I knew exactly what to do. And I was like, "Okay, let me show you. Let's cancel everything out." And then this is the
steps you need to follow. Analysis Services, make sure you don't have
the initial catalog, go to Options, make sure
<default> is selected, click OK. I was really excited. And so you can see right
here in the Object Explorer that the initial catalog was
here, but it's not there. So then I'm gonna just drop down here. I'm gonna do a quick refresh, just to make sure
everything's up-to-date here. There we go, go to
Partitions. I was excited. I remember, even on the call, I was like, "This is gonna work." Click it and click OK. Boom! What the French toast? Why is this not working? And then we went down this rabbit hole. SSMS has a cache, and if you go into the app data and you can find Management Studio, and there's a Roaming folder
where it keeps this user file that's specific to all the
connections and different things, and mainly for Studio, we
actually had to go clear that out, but we actually didn't need to do it. Let me show you what I did.
This is what you need to do. Clear out all of these. Disconnect, disconnect,
disconnect, disconnect, disconnect. Reconnect 'cause
everything's cleared out now. There's my InsaneAmazing workspace. Go to Options. Make
sure you're on default. Click Connect, span out your database. Go to Incremental. Go to Tables. Click Database. Go to Partitions. Choose Process. Select
the partitions you want. Click OK, and it starts working. And then we'll finish up in a little bit. There's a lot of data in that partition. I don't want you guys to watch and wait, but it's gonna process, pull all the data through
and it works, okay? The short part of this
long story that I tell, I just wanna show you guys
how we admin our tinker when we go through the process. But make sure you don't
use initial catalog and make sure you have <default>
selected as the database. And then go and refresh. Make sure you've cleared
out all those connections if you tried any of the other ways, because it will persist,
it'll hold it in cache. All right? What do you guys think? Have you run into this
paramter error before? I'll love to know. Did you
solve it a different way? I love to know. Let's continue
the conversation, where? In the comments below. If it's your first time visiting
the Guy in a Cube channel, hit that subscribe button,
like my video, big thumbs up. As always, from Adam and
myself, thanks for watching. We'll see you in the next video.
