Monthly Archives: March 2018

Speaking at SQL Saturday Atlanta (#733)

I am so honored and humbled to have been selected to speak at SQL Saturday Atlanta (#733) on May 19, 2018.  I will be presenting my Data Types Do Matter session.  This will be my second time speaking for this event in as many years.  I was so thrilled to be selected last year, but to be selected again is truly amazing.  For those that aren’t familiar with SQL Saturday Atlanta, it’s a HUGE event with speakers from all over the world.  The organizers do a great job of selecting a mix of speakers, from first timers to MVPs that have been around since almost the beginning of the MVP program.

If you’re in the Atlanta area on May 19, 2018, check it out, you won’t be disappointed.  They also have some great pre-cons on Friday, May 18, 2018, from Tim Radney (B|T), Jonathon Stewart (B|T), Joey D’Antoni (B|T) and Robert Cain (B|T).

See you in Atlanta!

SQL Saturday Iceland–What an Adventure!

Two Saturdays ago, I was in Reykjavik, Iceland, speaking at my very first international SQL Saturday.  WOW!  Is all I can say.  While this SQL Saturday is one of the smaller events I’ve spoken at, it definitely made an impression and will most likely go down as one of my all time favorites.  Why?  There are so many reasons…

First, it’s in Iceland.  I mean really, is there any place more beautiful?!  Even in Winter it’s beautiful.  Best part about Iceland – no trees to obstruct your view!  I know some of you might think I’m crazy, but coming from the desert of Arizona where you can see for miles in every direction, it makes a huge difference.  I moved to North Carolina 11 years ago and there are lots of trees, I mean LOTS of trees EVERYWHERE.  You can’t see anything, it almost makes me feel claustrophobic at times.  So for me, no trees is a huge plus!  I got to take in some of the major tourist sites via the Golden Circle and saw some truly amazing things.  There is so much history in Iceland it’s hard to comprehend at times.  From the Vikings to the Summit between super powers that brought about the start of end of the Cold War to being the first country to have an openly gay female Prime Minister.  So much history.

Second, the people of Iceland.  Everyone one I met from the time my plane landed until my plane departed five days later, everyone was so nice!  This goes doubly for the organizers of SQL Saturday Iceland.  Asgeir Gunnarsson and his team did an incredible job of making me feel welcome and offered assistance with anything I needed.  I couldn’t have asked for better hosts for my first international SQL Saturday.

Third, the speakers.  OMG, the speakers at this event were so awesome.  It was a huge melting pot of international speakers, there were folks from the UK, Ireland, Belguim, Portugal, Germany, Sweden, the list just goes on and on.  Every single one of them were so friendly.  Being a fairly new speaker and being surrounded by such tremendous speakers was an experience I will never forget.

Fourth, the event itself.  I mentioned that this was one of the smaller events I’ve spoken at.  This meant that it was very personal, you could talk with attendees, organizers and sponsors and not feel overwhelmed.  You felt like you were talking with neighbors not strangers.  They also had two of the best raffle prizes of any event I’ve ever been to.  The first was an oversized “typical” Icelandic chocolate Easter Egg.  Holy cow, I’ve never seen so much chocolate.  I would have had to purchase another suitcase just to get this thing home if I had won!  Then there was the case of beer (2 actually).  Not sure if we could get away with that in the US, but it sure made an impression on me.

Fifth, my session.  I had a good mix of attendees in my Data Types Do Matter session.  Lots of interested faces, but not too many questions until I started bribing them with Girl Scout Cookies.  Let me tell you, nothing breaks the ice like a Thin Mint or Caramel Delite!

Lastly, it’s in Iceland!  Yes, I know I said this already, but it was so beautiful.  So beautiful in fact, I’ll leave you with a few of my photos so you can see for yourself.

Enjoy!

SQL Saturday Richmond

SQL Saturday Richmond is just 5 days away and this will be the second time I have been selected to speak at this event.  SQL Saturday Richmond holds a special place in my heart as do the event organizers.  They gave me my first opportunity to speak at a SQL Saturday.

Last year at this time I was so stressed out and worried that I didn’t sleep well leading up to the event.  I had been sick in the weeks leading up to the event and was worried my voice wouldn’t hold out for the entire session.  And this was my first ever SQL Saturday, so no pressure at all.  Turns out my voice made it through the entire session and I didn’t hyperventilate or pass out.  I did forget to touch on a few of my talking points, but the crowd seemed pleased and I got very good feedback so I put it down as a win.

This year I was honored to be selected to speak again, and I am giving two sessions, Data Types Do Matter and Intro to Integration Services (SSIS).  These are both beginner level sessions and I really love both of them.  They are both things that I am passionate about and am a firm believer that if you start with a good foundation, it only makes you a stronger DBA/Developer/Analyst.

If you are in the Richmond, VA area this weekend (Mar 23 & 24) stop by and say “Hi”, I’d love to see you.  Also, they do have some great pre-cons this year, one with Grant Fritchey and the other with Andy Leonard and still have seats available, so check it out!

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.