Every time I attend SQL Saturdays I am re-energized, and SQL Saturday 163 BI Edition was no different. This one was particularly special because of my precon presentation, “Data Warehousing In a Day”. I just love getting up in front of a group and teaching and it’s obvious when to the attendees.
If all of the emails I’ve received are any indication, the attendees enjoyed it as much as I did. I want to thank those who attended the precons (not just mine) and attended SQL Saturday. You folks make the PASS Community worthwhile.
I also want to thank our team of volunteers who put on the event.
As anyone who reads my blog knows, my time has been ridiculously scarce lately and I wasn’t able to do much more than get my presentations ready. It’s nice to know that if I can’t give my usual effort that everyone else picks up the slack. Everything went off great. Congrats guys.
Presenting “Dimension – It’s All Meaningless Without Them”
Anyway, as promised the following are available for download:
Once again folks, please accept my sincere apologies that I haven’t made enough time for my community work, especially blogging. I just recently severed ties with a project that was taking all my time and preventing me from blogging. That, and another event I’ll talk about soon, reminded me how important the SQL Community is to me. I will endeavor to make this the last time I need to apologize.
Anyway, coming up in future posts I’ll cover the following:
More on the Merge Statement including a wrinkle I ran into last week regarding foreign keys.
The Balanced Data Distributor Transform for SISS.
A series of articles regarding Dimensions, SCDs, and how to load them in SSIS 2008 through SSIS 2012.
This is just a quick reminder that I and two of my friends are presenting Precons tomorrow for SQL Saturday Dallas. While I am biased and think that you should sign up for mine, any of these sessions are a huge educational value. If you’re in the DFW area, do yourself a favor and attend a Precon and SQL Saturday. The abstracts are below.
Hope to see you there!
Become an Enterprise DBA by Sean McCown
Speaker Bio: Sean McCown is a Microsoft Certified Master in SQL Server 2008. He is also a SQL Server MVP with over 15yrs experience in databases. Sean is also founder and co-owner of the renowned website MidnightDBA.com where he records free SQL Server training videos and co-hosts the popular Webshow DBAs@Midnight
In addition to this training, Sean has decided to offer all of the Enterprise DBA precon attendees a FREE copy of the DBA Roadmap webinar! Which means, of course, that the precon pays for itself…or you get both for 50% off, or however you want to look at it. Bottom line: attend Sean’s precon, learn massive amounts of stuff, AND improve your career exponentially. Win-win-win.
Data Warehousing in a Day by Dave Stein
Speaker Bio: David Stein is a Senior Business Intelligence Consultant with WaveTwo in Irving, TX. He’s worked in SQL Server Business Intelligence for almost a decade. He’s designed Data Warehouses from scratch as well as saved projects in serious jeopardy. He is also very active in the community, having spoken at PASS events at all levels, including the PASS Summit, SQL Rally, and at numerous SQL Saturday events and user groups. He’s also given multi-day pre-conference seminars at third party events regarding Microsoft Business Intelligence. You can learn more about Data Warehousing, Business Intelligence, and other technical topics on his blog at Made2Mentor.com or follow him on Twitter @Made2Mentor
Attend this full day pre-con and let David be your guide through this process. He’ll save you time, money, and headaches.
Additional information about my presentation can be found on my blog at: http://www.made2mentor.com/2012/06/sql-saturday-dallas-and-pre-cons/
Real World SSIS: A Survival Guide by Tim Mitchell
Speaker Bio: Tim Mitchell is a business intelligence consultant, database developer, speaker, and trainer. He has been working with SQL Server for over 9 years, working primarily in business intelligence, ETL/SSIS, database development, and reporting. He has earned a number of industry certifications and holds a Bachelor’s Degree in Computer Science from Texas A&M at Commerce, and is a Microsoft SQL Server MVP. Tim is a business intelligence consultant for Artis Consulting in the Dallas, Texas area.
As an active member of the community, Tim has spoken at national, regional, and local venues including the SQL PASS summit, SQL Connections, numerous SQL Saturday events, and various user groups and PASS virtual chapters. He is a board member and speaker at the North Texas SQL Server User Group in Dallas, serves as the co-chair of the PASS BI Virtual Chapter, and is an active volunteer for PASS. Tim is an author and forum contributor on SQLServerCentral.com and has published dozens of SQL Server training videos on SQLShare.com.
Tim is a contributing author on the charity book project MVP Deep Dives 2, and is coauthoring SSIS Design Patterns (available July 16th).
You can visit his website and blog at TimMitchell.net or follow him on Twitter at @Tim_Mitchell.
Let me just start this post with an apology to all of my readers, well both of you. There have been a lot of changes in my life lately and I’m been overwhelmed with demands on my time. I won’t go into details but those demands will be letting up soon, and I will be able to get back to blogging, community involvement, and catching up on sleep.
However, this topic is important enough that I had to take a break from frantic schedule to jot down a few words about it. As I started to write this, I got a notification of a blog article by my friend and fellow North Texas SQL Server User Group member, Ryan Adams (Blog/Twitter). As I read his article I realized that our views were nearly identical about two candidates, Sri Sridhara (Blog/Twitter) and Allen Kinsel (Blog/Twitter). I urge you to read what he wrote because I echo each and every one of his sentiments.
However, I’d like to say just a few words about each candidate myself.
I met Allen at my first SQL Saturday of which I was an active volunteer. Allen was the first PASS member that I connected with who wasn’t in one of my local groups. The guy is tireless, has a pragmatic point of view about the community, cares about member’s opinions, and I’ve never heard anyone have a negative thing to say about him. When I have something to comment (whine about) on regarding PASS at the national level Allen is the guy I go to and I can tell you that he truly cares… or at least is good at acting like he does.
Sri is an interesting PASS leader and volunteer. I’ve worked with him for several years organizing PASS events like SQL Rally, multiple SQL Saturdays, etc. In some ways, he’s a lot like me. Some people take to him, some not so much, but what I’ve found is that people in both camps are reacting to the same thing. The man is highly driven. Nobody can question his dedication, drive, and single minded focus on pushing the community farther. Sri embodies what PASS is and should continue to be, relentlessly driven to spread the adoption of SQL Server and to raise the ability and skill set of DBAs everywhere.
He and I don’t always agree, but he’s a guy you can count on to always follow through on his commitments, to do what he says he will do. Without belaboring the point, what happened last year was shameful, yet to this day Sri hasn’t had a negative thing to say about it, even privately. Let’s not let that happen again, Vote for Sri.
Like Ryan said, I’m not commenting on the other candidates, but not because they aren’t good people. I just happen to know these two exceptionally well, and intend to vote for them. You should too.
This is just a friendly plug for my friends at RedGate regarding SQL in the City, a free series of events traveling around the country. It’s a free day of training and networking led by spectacular presenters such as Steve Jones (Blog/Twitter) and Grant Fritchey (Blog/Twitter), who will be headlining the Austin event next week.
Steve and I at my first Summit
Grant and I at SQL Sat. Chicago
I had originally planned on going myself, but I recently got a new gig (more on that in the near future) and can’t get away at the moment. However, these guys are top notch, and you really shouldn’t miss the chance to learn from them.
Hey folks, just a quick message that I’ll be presenting this evening at North Texas SQL Server User Group. I’m presenting an updated version of “Have you got the Urge to Merge?” which I gave last month in Fort Worth. I’ve made good progress with my code which generates T-SQL Merge Statements and will be able to show more of it tonight. I hope to post a beta version of the code next week. The abstract is below.
Have you got the Urge to Merge?
The T-Sql Merge statement, introduced in SQL 2008, is powerful, versatile, and performant. However, it is also one of T-SQL’s least understood statements. Most DBAs know they can use It to insert, update, and delete data in a target table in a single transaction, generally outperforming separate statements. Fewer know, however, that it can be used to load slowly changing dimensions, facilitate auditing, and for many other uses. Come to the user group meeting and learn the in and outs of T-SQL Merge. I’ll even show you how to write complex Merge statements with lightning speed.
I hope to see some of you folks in Irving tonight.
I mentioned in a previous article that many posts on T-SQL Merge read like Books Online. Speaking of Books Online, let’s start by glancing at the syntax portion of the T-SQL Merge Page. I’ll take the liberty of re-posting just the first 25% or so below.
[ WITH [,...n] ]
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] [ WITH ( ) ] [ [ AS ] table_alias ]
[ WHEN MATCHED [ AND ]
THEN ] [ ...n ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND ]
[ WHEN NOT MATCHED BY SOURCE [ AND ]
THEN ] [ ...n ]
[ OPTION ( [ ,...n ] ) ]
Simple right? Great, I guess I’m done here…. No seriously, who can readily absorb that? So, what is Merge really and how do we use it?
T-SQL Merge Basics?
In a nutshell, the Merge statement allows you to Insert, Update, or Delete data in a Table, referred to as the Target, with data from another Table called the Source. The tables are compared on Fields which uniquely identify records in each, a Join if you will. That’s not to say that the Source has to be an actual Table, a view or a Select statement may be used as a source as well.
Basically I’ve created two tables called DimCustomer and CustomerStaging with the following data in them:
Now, we’re using a CustomerStaging table for sake of simplicity. However, I could have used a T-SQL query from an OLTP database or other source as the Source object in my merge. For this article, let’s assume there are no Slowly Changing Dimensions and ignore the fields in each table to the right of Affiliation.
There are several differences between Staging and DimCustomer which should be noted:
Anakin Skywalker is no longer in the source system.
Yoda’s name was changed.
Obi-Wan Kenobi changed planets.
Darth Vader was added.
Now I realize that Anakin became Darth Vader, which could have been seen as a change in name. However, Obi Wan clearly states that Darth Vader betrayed and murdered Anakin, effectively becoming a new person. If that bothers you, then you’re a scruffy looking nerf herder. Anyway, if we used separate T-SQL statements, we’d delete Anakin, Update Yoda and Obi-Wan, and Add Darth Vader. With Merge, we can do that in one transaction. For me, the easiest way to learn something is to go through an example. We’ll break the statements into sections to make it easy to understand.
First, the Target and Source tables are specified along with the business key which identifies each record. This is the field that one would use in a join.
MERGE DimCustomer AS Target
USING CustomerStaging AS Source
ON Target.CustomerKey= Source.CustomerKey
The When Matched clause determines what will happen when records exist in the Source and Destination with the same CustomerKey. Notice the additional conditions I’ve added. Strictly speaking, they aren’t required, but every record in the target would be updated regardless of need, which wastes resources.
AND( Target.CustomerName<> Source.CustomerName-- These are optional. OR Target.Planet<> Source.PlanetOR Target.Affiliation<> Source.Affiliation)THENUPDATESET--Updates Yoda and Obi-Wan records.
The When Not Matched by Target clause specifies what should be done with records in the Source that aren’t in the destination. The typical scenario is to insert records which are new. I could have added additional conditions or only added certain new records as well.
WHENNOT MATCHED BY TARGET
THENINSERT(CustomerKey, -- Inserts Darth Vader
The When Not Matched by Source clause specifies what should be done with records in the Target that aren’t in the Source. Keep in mind that if your Staging table isn’t comprehensive, maybe the result of an incremental extraction, then you’d want to omit this portion of the statement.
WHENNOT MATCHED BY SOURCE THENDELETE; -- Deletes Anakin
Also, keep in mind that any Merge statement must be terminated in a semicolon. So, when you put your script together, it looks like the following. Go ahead and run it on your test data.
MERGE DimCustomer AS Target
USING CustomerStaging AS Source
ON Target.CustomerKey= Source.CustomerKeyWHEN MATCHED
AND( Target.CustomerName<> Source.CustomerNameOR Target.Planet<> Source.PlanetOR Target.Affiliation<> Source.Affiliation)THENUPDATESET
Target.Affiliation= Source.AffiliationWHENNOT MATCHED BY TARGET
Source.Affiliation)WHENNOT MATCHED BY SOURCE THENDELETE;
What about Null Values?
Ah, very astute of you to notice that my young padawan. You’ll notice in the When Matched portion that I also check to see if a value changed before I run my update statement. However, depending on your settings, Null values are not equal to each other. Therefore, any record with a Null value would be updated even if there were no changes. I handle this with Coalesce because it chooses the best datatype between arguments based on datatype precedence. Therefore, I can use the same basic Coalesce statement for most comparisons without throwing an error. Therefore, the When Matched portion from above becomes the following:
AND(COALESCE(TARGET.CUSTOMERNAME, '')<>COALESCE(SOURCE.CUSTOMERNAME, '')ORCOALESCE(TARGET.PLANET, '')<>COALESCE(SOURCE.PLANET, '')ORCOALESCE(TARGET.AFFILIATION, '')<>COALESCE(SOURCE.AFFILIATION, ''))
Of course, there are always limitations. The most important limitation is that both data sources need to be on the same SQL Server instance. I suppose you could use linked servers, but I wouldn’t recommend it.
Another factor that might give you some pause is that these statements are fairly complex and wordy. A long merge statement, such as merging two tables with 25 fields each, is tedious to write. Well, stay tuned because later in this series I’ll share some code which will practically write the statements for you.
In the next article we’ll discuss how to use the Output clause with Merge, and how I use it occasionally to perform auditing during data warehouse loads.
The T-Sql Merge statement, introduced in SQL 2008, is powerful, versatile, and performant. However, it is also one of T-SQL’s least understood statements. Most DBAs know they can use it to insert, update, and delete data in a target table in a single transaction, which generally outperforms separate statements. Fewer know, however, that it can be used to load slowly changing dimensions, facilitate auditing, as well as other uses. I briefly discussed how to use Merge some time ago, but I wanted to take a deeper dive into it because it is so darn usefull.
Though the statement is documented on many sites, many tend to cover it tersely or are little more than variations on Books Online, which I find difficult to read and absorb. So, in the following articles, we’ll cover the following basic topics.
I won’t bother ranting yet again about how great these events are, except to say that if you aren’t taking advantage of them, you’re making a huge mistake. You won’t find better learning and networking opportunities almost anywhere, and the events are essentially free. That being said, I have another announcement to make.
Data Warehousing In A Day – Pre-Conference Seminar
I’ve been invited to give a full day pre-conference seminar on Data Warehousing. On Friday October 12th, you can join me to learn how to build a Data Warehouse from the ground up and load it efficiently. I’ll teach you the core skills you need to succeed, and the pitfalls to avoid. I’ll share the experience I’ve gained building Data Warehouses from scratch, as well as rescuing these projects from failure. For the complete abstract, head on over to Data Warehousing In A Day and take a look.
I’m really pumped about this opportunity for several reasons. The first is that I was born to teach. I really love it and people say that it shows. Second, many people have seen some of my presentations on Data Warehousing or SQL Server Integration Services and commented how they wished I would go into more depth. When giving a regular presentation of an hour to 90 minutes, it’s hard to do that. The Pre-Con is a golden opportunity to deliver both breadth and depth.
So, for the low, low price of $75, you get a full day of example and demo focused learning on Dimensional Modeling, SSIS, and more. How can you beat that?
Sheesh! I never worked for so little, except once, and that was a very noble cause. – Miracle Max
Be sure to sign up early because the space is limited and the Pre-Con will fill up fast. Also, $75 is an early bird price. On July 15th, the price goes up to $99.
So, I hope many of you will join me for Data Warehousing In A Day on Friday, October 12th and I’m looking forward to hanging out with all of you at SQL Saturday 163!
More info about SQL Saturday 163 and my Pre-Con to come.
Everyone has heard the quote, “The apple never falls far from the tree.” As we get older, we tend to rebel against the notion that we will eventually become our parents. As we age, it seems less a notion and more a certainty. Well, in many ways I have fought against this my entire life because of some of the consequences my father has to endure. Of course, everyone has flaws, and I strive to correct and improve mine all of the time. However, there is one that is threatening to destroy me.
Like my father, I have been fat for most of my life. Not just overweight, but fat. I can remember getting into fights with neighbor kids about it. I’d come home all marked up from a fight and my father would ask me why. “Because they called you fat,” I’d say. And he’d just respond with, “David, I am fat. Who cares what they say?” Well, as a kid, you have to care as teasing and bullying can be brutally painful. Anyway, as I got older my weight continued to grow.
Being fat is painful. It affects your social life and your career. People assume that if you’re fat that you’re undisciplined and discount your intelligence and abilities. I usually don’t take it personally as it’s not malicious, it’s just natural. A few years ago a friend from the PASS community asked me to apply to their company because their team could benefit from my skills. However, the Human Resource person didn’t recommend me for a second interview. The friend asked why and the HR person stupidly admitted that I was too fat. They didn’t want to incur extra health care costs and openly doubted my skills. Keep in mind that this is a person charged with protecting employees with disabilities and also has no understand about what DBAs actually do. I was rejected simply because I was fat. I was angry, but got over it.
Dieting is painful, dealing with deprivation, and eventual failure. I’ve gained and lost tons of weight during long term diet and exercise stints. The most recent was 2 years ago when I lost over 50 lbs. However, I had to ride almost 200 miles a week on the bike to do it. This lead to terrible saddle sores which required periodic surgical procedures to remove. Your body is programmed to hold onto weight and tends to sabotage your efforts. This can be demoralizing.
As time goes on, I’ve become closer with my father. His health has been declining in the past decade and his obesity and related problems have been devastating. He suffers from diabetes and has impaired vision because of it. My father, who used to throw around 400lb tool boxes with ease can no longer walk. His knees and hips have completely eroded and he’s confined to a wheelchair or walker and in extreme pain. He’s on the most powerful pain killers available and still in agony. For the longest time surgeons refused to fix his problems because he “wouldn’t” lose weight. However, its nearly impossible to lose weight when all you can do is lay around.
A few years ago, before my last dieting and exercising stint, I had a talk with him. Although it may sound weird, I thanked him for being an example for me. My father is like the Ghost of Christmas Future for me. I have a living example of what will happen to me if I cannot fix this problem. His response was telling. Basically he apologized for what I have to go through, but that none of us avoids it. His father died of diabetes related complications and presumably his grandfather did as well. My father, who was one of the strongest men I have ever met, apologized because I was doomed to follow his path.
So far, he’s been right. Despite my best efforts I am still fat, and suffering physically from it. Two months ago I tore the meniscus in my knee when I got up off the couch. Now my knee hurts constantly. I am becoming my father.
I simply cannot accept that because it’s terrifying beyond words.
I’m having Gastric Sleeve Surgery. Basically, they are going to remove 80% of my stomach which reduces the amount of food I can eat as well as suppress my hunger. I’ll be in the hospital three days and will require some time to heal. The decision is scary and the surgery is painful, but not nearly so as being morbidly obese the rest of my life.
I made the final decision after appointments with a registered dietitian and my physician. Independently they both told me that because my metabolism was so slow, as it is with most morbidly obese people, that I would need to restrict my daily caloric intake to between 800 and 1200 calories for the rest of my life. This is the only way I can remain at a normal weight. When told this, I realized that I should have done this a decade ago. I don’t know of anyone, even my thinnest friends, who would be able to voluntarily restrict their eating that much. Heck one of my favorite meals, Prime Rib at a national chain restaurant, is at least 1700 calories. I would never be able to achieve this without the benefits of surgery.
I understand that this will permanently alter my relationship with food. The surgery is a tool which will allow me to eat less and stick to the healthy foods which will allow me to avoid this fate. The next year will be very hard, but I simply have to do it.
Why am I sharing all this on my blog? Well, the surgery is next Monday and I’m presenting at SQL Saturday Houston this weekend. I’m on the preparatory diet right now and that’s hard to conceal since I can’t eat anything but 800 calories of protein shakes a day. Since I’m still attending the networking events, I’ll stick out like a sore thumb. I thought I would just let my SQL Family know as you folks mean a lot to me.
I’m hoping that some of you who are struggling with similar issues will read this and take strength from it. If I can inspire someone else to make this commitment that would be great too. It doesn’t matter to me if people think my decision is rash or crazy.
It is what I need to do for my health so I don’t continue walking in my father’s footsteps.
This Saturday I’ll be down in Houston presenting at SQL Saturday #107. I can’t wait to hang out with some of the best minds in SQL Server, which just happen to be great friends of mine as well. The abstract for my session is below.
Data Warehousing – How to Convince “The Bobs”
I got a meeting with the Bobs in a couple of minutes...
Building your first Data Warehouse is a long, and often difficult process. How can you get your boss to approve a Data Warehouse project? What’s the best way to explain dimensional modeling and the benefits of a Data Warehouse to a business person? What are the best/most cost effective ways of learning it? What kind of materials, hardware, software, etc do you need? What’s the best way to build a proof of concept that will impress your boss, as rapidly as possible?
Come to this presentation, and I’ll answer all of these questions plus the most important question of all.
“Is this good for the company?” Absolutely.
You can download everything for the presentation here. If you’re brand new to Data Warehousing, you should my read week of Dimensional Modeling Articles which I wrote for SQL University. These will bring you up to speed to follow this fast paced presentation.
However, these are just the sessions that most interested me. You should check out the schedule for yourself. Just look at all the “names” who will be there. Besides those folks I already listed, other notables include Sean and Jenn McCown, Jack Corbett, Wendy Pastrick, Kendal Van Dyke, Tim Mitchel, Mike Hotek, Wes Brown, Kalen Delaney, and so many others.
You just can’t beat the learning and networking opportunities of SQL Saturdays. Hope to see many of you there.