Category Archives: SQL Server

Speaking at SQL Saturday Raleigh

I am happy to announce that I will be speaking at SQL Saturday Raleigh this Saturday, April 27, 2019.  I have been part of the organizing team for this event for the past few years so have never been able to speak (those of you who are fellow organizers know why!).  This year we had a last minute cancellation of an afternoon session, so I decided to step up and fill the slot.

I will be presenting my What is Power BI? session at 2:30pm.  If you’re in Raleigh with some time on your hands, stop by and say, “Hello”, I’d love to see you.  You can also check out some other great sessions as well, we still have registration slots available.

SQL Server Management Studio Tips & Tricks From Dr Greg Low

It’s been a bit slow in the US this week due to the American 4th of July holiday, but it’s been especially so for me since I work for a manufacturing company.  Our plants typically shut down for the entire week allowing us to do any major infrastructure work that requires more down time that is typically available in our weekly maintenance windows.

This year we didn’t really have a lot on the schedule, so I’m using my “free” time to play catch up on all my technical reading.  There are so many things on my “to read” list but one I’ve been really excited to read was Dr Greg Low’s (B | T) SQL Server Management Studio Tips & Tricks ebook.  I must say I wasn’t disappointed.  I’ve been working with SQL Server for 20 years now and didn’t really think I could learn that much, but Greg has some great nuggets that will help save valuable time when I’m elbows deep in T-SQL code.

If you haven’t read his ebook (which is completely free!) I highly recommend it.  Especially if you spend any amount of time in SSMS (SQL Server Management Studio).

Getting Called Up

When baseball players are called up to the Major League from the Minor League it’s referred to as getting “Called Up”.  Today I am proud (and very humbled) to announce that I have been Called Up to PASS Summit 2018.  I will be presenting my Data Types Do Matter session.

I started speaking on a regular basis only last year which I guess makes me a Rookie*.  I never thought I would grow to love speaking as much as I have in the last fourteen months.  I used to have a major phobia of public speaking (you can read about it here).  But, with any fear if you confront it head on, in most cases, that fear becomes more manageable or even vanishes completely.  A few years ago I decided that I would confront my fear head on (you can read about that here) and look where I am today.  It’s not completely gone, but I have learned to manage it in a way I never thought I would.

I have presented 23 times on four different topics and reached 502 people in two countries.  If someone had told me two years ago that I would have these kinds of stats I would have thought they had taken a Bad Hop* to the head.

This is only my second time submitting a session to PASS Summit, so I feel very honored to have been selected.  I know others have submitted for years and still not been selected.  Maybe wiping my submission with Pine Tar* actually helped this time.

Here’s hoping I don’t Whiff* and get Sent Down*.

*Baseball References Explained Here

Using the Analysis Services Execute DDL Task

My grandfather used to say, “There’s more than one way to skin a cat.”  I always thought it was a disgusting saying, I mean who would ever want to “skin a cat”?!  Gross!  But the underlying meaning was clear, there’s always more than one way to do something.  And no my grandfather never actually skinned any cats, he was just a country boy from a small town in Iowa, where you quite often had to come up with creative ways to get things done.

There are often times when we get so focused on what we are doing, we forget to look around.  This happened to me a few weeks ago.  We were in the middle of a big upgrade at work, not only where we upgrading our ERP system, we were changing platforms as well (read this as:  no one got more than 3-4 hours of sleep per night for about two weeks, including myself).  We also have a BI solution that uses SQL Server Integration Services (SSIS) to pull data from our ERP system, stores it in a data warehouse on SQL Server then has a multi-dimensional SQL Server Analysis Services (SSAS) cube that sits on top of the warehouse.  Pretty common scenario.

Last part of our ETL process is to process the Dimensions and Facts for the newly loaded data.  I have always used the Analysis Services Process Task in SSIS to accomplish this.  Works great, it’s stable and easy to use.  Just click a few items and you are off and running.

My ETL code had been stable for so long, I hadn’t needed to make any changes since I upgraded from Visual Studio 2015 to Visual Studio 2017.  Little did I know that there was a bug in Visual Studio 2017/SSDT 15.5.2 that will not allow you to use the Analysis Services Process Task if your target version of SSIS is 2016.  You will get a couple of different errors depending on what you are doing.  If you are trying to modify an existing package that uses an Analysis Services Process Task and your target version of SSIS is 2016 you will get the following error:

Post71-EditExistingError

If you try to create a new package using an Analysis Services Process Task and your target version of SSIS is 2016 you will get the following error after it allows you to configure your processing tasks and you click OK:

Post71-NewPackageError

In both of these scenarios you will not be able to save the package.  So what the heck are you supposed to do?!  Here’s where my tunnel vision (and panic) sets in.  How was I supposed to get my SSAS objects processed?

I could always script out my processing tasks using SSMS and drop them in a SQL Agent job step.  But I have multiple environments and multiple cubes so each one would have to be hard coded.  Not a great idea, so scratch that.

Post71-ScriptProcesinSSMS

I could just create a new SSIS project, targeting SSIS 2014, just for the processing tasks.  (Thanks to Teo Lachev for the idea.  And yes, you can deploy SSIS 2014 targeted packages/projects to SSIS 2016.)  It’s better than my first idea.  I could parameterize all the connection managers so nothing would be hard coded, but now my code lives in two different projects.  Not ideal, but it would definitely be better.

Then Gerhard Brueckl pointed me to the Analysis Services Execute DDL Task.

Post71-ASDDLTask

I did some quick research on it here and turns out, this was exactly what I needed to “skin this cat”.  It would allow me to keep my SSAS processing packages in the same project as the rest of the ETL and I could parameterize it to my hearts content (queue beams of light from above and singing angels).  Why hadn’t I thought of this before?!  To be honest I had never paid attention to this task.  It had the term DDL in it, and I wasn’t writing DDL, so why would I ever need it, especially since I was dealing with SSAS.  Yes, I know, need I remind you about my lack of sleep and my tunnel vision.

Post71-ASDDLTaskOptions

The cool thing about the Analysis Services Execute DDL Task is that you have a choice when it comes to your source for the DDL.  I opted for the variable because I needed to parameterize it with the SSAS database name.

Post71-SourceTypes

I was able to script my processing tasks, dump that code into a variable that I built with expressions so that it could be parameterized.  Now one thing to keep in mind is that the XMLA that is produced for SSAS processing tasks has double quotes in it.  You will have to either a) escape the double quotes or b) create a variable for the double quote to use in the XMLA in your expression to build your variable.  I opted for b, create a variable to hold the double quote, it seemed cleaner to me.  Here’s a good post on StackOverFlow on how to do both.

As you can see below, it looks very intimidating with all the XMLA & expressions, but it creates a very pretty XMLA processing task in the end.

Post71-VariableExpression

I created one task for processing dimensions and one task for processing facts.  Mostly because I want to be able to track performance respectively, but you could create one task to do it all.

I was able to add these new packages to my SSIS project and deploy it without issue.  They’ve been running ever since with no problems.

I hope this post helps someone else break out of their tunnel vision.

PSA:  No cats were harmed in the writing of this blog post.

MVP Data Platform

Holy Cow!  I still can’t believe it, I was awarded the Microsoft MVP – Data Platform today.  I guess I really am starting the New Year off with a Bang!

I am so honored and humbled by this.  I wanted to Thank everyone for their support over the last several years, there’s no way this would have happened without all of you.  To be nominated by your peers is the greatest feeling.  #SQLFamily is awesome!

THANK YOU ALL!!!

Speaking at SQL Saturday Spartanburg (#681)

I am so happy to announce (and honored to be selected) that I will be speaking at SQL Saturday Spartanburg (#681) on September 23, 2017.  I will be presenting my What is Power BI? session at 8:30am.  I know it’s early, but I’d love to see you if you are in the area.

I’ve only been to Spartanburg once and that was a few years ago.  I attended the US Masters South Carolina LCM State Championships, where I walked away with a second place medal in my age group for overall meet performance.  It was an exhausting two days, but so worth it.  I’m hoping my SQL Saturday trip will be just as successful.

Hope to see you there!

Don’t Forget the Keys

I was recently given the nod to upgrade my monitoring server from SQL Server 2012 (SP 3) to SQL Server 2016.  This came none too soon as SQL Server 2012 (SP3) goes out of mainstream support on July 11, 2017.

We decided to go with a brand new box since the existing one was on Windows Server 2012 (not R2) and had been having issues lately.  So my SysAdmin guy stood up a brand new shiny Windows 2016 Server box for me.  This will be the first in our domain.  I get to be the guinea pig – WooHoo!

I got SQL Server 2016 installed on the new box without issue.  This box is used as my monitoring server and my personal sandbox, so it has Reporting Services (SSRS) installed on it as well as the database engine.  Since we are using a brand new box, there is no need to shut the old one off before we turned this one on, which is nice.  I can migrate things when I have time.

My first order of business was to migrate my SSRS databases to the new box and get it configured.  Now, it’s been a while since I’ve migrated an instance of SSRS, so of course I forgot something.  Otherwise you would not be reading this post.

I remembered to backup BOTH databases and the encryption key.  Once I had the databases restored on the new server I started the Reporting Services Configuration Manager so I could restore the encryption key so I wouldn’t lose all my credentials and other security sensitive information (BTW – Here’s a great reference for migrating SSRS).  After I restored my encryption key, I wanted to generate a new key, but the Backup button was not enabled on the Encryption key tab.  I didn’t think much of it, I just restarted SSRS and figured it would be available after restarting.  Of course it wasn’t.  I tried navigating to the URL for the SSRS Web Portal (replaces Report Manager) and I got an error saying Reporting Services was not configured correctly.  Interesting.  It took me about 15 minutes to realize/remember the last step in restoring the encryption key.  Do you remember what it is?  Without Googling it?

Okay, I’ll tell you since you’ve made it this far in the post.  Now I will tell you that I was not the one that installed/configured SSRS on the old server, so I was not aware that whoever did, configured the database for a scaled out deployment, even though it was not being used in a scaled out deployment <sigh>.  When you have a database that has been configured for scaled out deployment, you have to clean up the entries in the Keys table in the ReportServer database (the link above has a note about this very thing about three quarters of the way down).

Lesson learned:  Always look at the existing configuration very closely.  I totally didn’t notice (and hadn’t noticed for 3+ years) that the database had been configured for scaled out deployment, shame on me!

My shiny new SSRS 2016 instance is up and running now.