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.

Pain

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.

Revelation

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.

My Decision

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.

The best way to answer that question is with other questions. Answer them honestly. Let me preface the questions by saying that I fully expect the majority of people who take the time to read this will answer yes to most of them.

• Are you intelligent in general? You don’t have to be a Rocket Surgeon to be a good BI specialist, but it isn’t for everyone. Many aspects of this job involve solving puzzles, but some of them don’t have clear solutions. It’s always easier to solve a problem that is known to have a good answer, than one that is unknown.
• Do you love to learn new things? Notice that I did NOT say like, or are you willing, but are you really passionate about learning? This career path requires constant learning of several very different skills to achieve mastery. Also, keep in mind that your job is to understand the user’s jobs and therefore their data needs. Consequently, you spend a lot of time learning vs actually doing. Which leads us to..
• Do you have a business mindset? You need not have a Masters Degree in Business Administration, but you have to understand, or quickly learn, how disparate business models work. It’s not enough to write excellent T-SQL, or have mad skills in SSRS, you have to understand what the user needs even if they can’t articulate it.
• Are you well-spoken? Do you enjoy working with people? Working as a member of a team? What about public speaking? These are all valuable traits for a BI Specialist. A great deal of your time is likely to dedicated to client interviews. If you’re socially awkward, this may not be the career for you.
• How well do you write? Proposals, project plans, etc. are all written and how well you write influences others’ opinion of you. Those opinions determine the opportunities that will come your way.
• Do you have the mind of the teacher? When sitting in front of a CEO, there is a fine line between boring them with deep technical details and simplifying things too much and which insults their intelligence. Executive buy in is critical and you must be savvy enough to get it.
• How’s your attitude? Are you generally an upbeat person? The epitome of this is Jes Borland (Blog/Twitter), who has the most enthusiastic personality I’ve ever seen. This attitude helps with all of the other attributes. People will be more interested in talking with you, helping you, etc. This is something I struggle with as my attitude can be closer to Eeyore than Tigger sometimes.
• Are you self-managed and organized? Often the BI DBA works alone on a specific aspect of a project with little to no supervision. Will you be productive in this situation?
• Are you detail oriented? How meticulous are you in your work habits? With experience you will learn certain patterns which increase efficiency in data modeling, ETL, and report creation. However, even more importantly is how meticulously you check your work and notice details. You are not the business expert, but the person requesting the work is. The first time you deliver a report which isn’t right, users will mistrust your figures for a long time. In our business, trust is everything, and it’s just too easy to miss something. Also, documentation for BI project is crucial and I find that only the most meticulous people are disciplined enough to do it.
• Are you driven? Striving for BI mastery and staying on top of new products and techniques can be exhausting. It’s not a sprint, it’s a long distance run. Are you up for that?

Please don’t misunderstand me. You don’t need to be excellent in all of those categories, none of us are. However, if you want to excel in Business Intelligence, you’d better have a good grasp of the majority of them and diligently be working toward mastery.

What do you think? Have I missed anything?

Recently I’ve received several emails with the same basic theme. I was sought out because I am a Business Intelligence Consultant and they want to know whether the career would be good for them. Well, it’s hard to read a short synopsis of someone’s experience and recommend a life altering directional change. So, I decided to explain a bit about my history and why I made that decision.

My History

Five years ago, I had a very comfortable position working with a software product called Made2Manage, which utilizes SQL Server, hence the name of my blog. I was an expert at it and became able to solve problems very quickly. This left me with some idle time and I got comfortable. However, over time I got bored and needed challenge in my work life and realized that my procrastination to grow was costing me dearly in opportunities and money. Once you reach a point where you are comfortable, your value levels off. Also, I lived in Michigan, which was an economic black hole, so I was unlikely to earn a good living.

The first step I took was to move to a better economic area, Dallas Texas, and work for a much larger company as their Made2Manage expert. I got more involved with SQL Server and reporting and this led me to Brent Ozar. After asking some technical questions, I asked him a similar question that I’m addressing in this article. Basically, what should I do with my career? He put me in contact with experts in various disciplines, some not involving SQL Server, so I could decide my future direction. He convinced me to blog, get involved in the SQL community, and I became hooked.

Why did I choose Business Intelligence?

• Fascination. I love working with data, cleaning it up, and creating systems to report on it. I enjoy showing a user their data in a meaningful way and watching them get excited. Data analysis is both a science and an art. Also, I enjoy practicing a skill that many don’t understand and cannot perform.
• Variety. I don’t do well with repetitive boring tasks. As a Business Intelligence Consultant, most areas of SQL Server are involved so there are many different tasks to accomplish. This staves off boredom and insures that you are always learning.
• Challenge. Because the Business Intelligence discipline involves so many aspects of SQL Server, it’s a real challenge to gain a level of mastery. Do you think that BI is just about reporting? Think again. I have to understand data modeling, both OLTP and OLAP. I need to have a level of mastery in SSRS, SSIS, and SSAS; not to mention the new and evolving tools such as SQL Server 2012 Power View and PowerPivot. Further, you need to know how to set up SQL Servers, disaster recovery procedures, and performance tuning. In other words, my work at some point or another touches almost every aspect of SQL Server.
• Competition. Well, really the lack of competition as compared to other areas of technology. For example, I enjoy .NET software development, but I did not want to compete with outsourced labor or kids just coming out of college. Why try to compete with people who will do quality work for very low wages. Database Administration is not a topic that is typically taught in college so the practitioners tend to be more mature, by that I mean older but not necessarily in temperament. One reason why I didn’t take the Production DBA path is that it’s just too easy to outsource. Someone in a foreign land can check your backups, perform maintenance such as index and query tuning, etc. Also, Microsoft is pushing everything toward the Cloud, and this may cause issues for those with primary functions like disaster recovery, administration, and performance tuning.
• Demand. To quote Paris Hilton, “Its hot!” Microsoft is continuing to invest boatloads of money into Business Intelligence and more and more companies are investing in their BI infrastructure. In the world of SQL Server, Business Intelligence skills seem to be in the highest demand to me.
• Compensation. It’s basic supply and demand. Being a BI Professional is difficult so many people can’t do it. The demand is increasing. This disparity causes salaries to rise.
• Freedom. My skill set allows me considerable freedom as to where I work and for whom. Ideally, I would work for a company which relies on SQL Server, which is almost everyone. However, even if a company doesn’t have SQL server databases, dimensional modeling skills and the flexibility of Integration Services to connect to nearly any data source means that BI professionals are still valuable.
• Interactivity. “I’m a people person damnit!” I enjoy talking to people which is one of the reasons I’m so active in the SQL community. Business Intelligence lends itself to more interaction because you are developing your projects for users. Also, this is one of the reasons why BI is resistant to outsourcing. Does your CEO want to spend hours talking with someone he can’t understand or someone he can meet face to face and feel confident that his/her needs are being addressed?
  

• Community - The SQL Server Community is the best technical community I’ve ever seen or heard of. One of the primary reasons I went the SQL Server route is because our community is so much better than that of competing products. When I have a question and Google it, someone I know has already blogged about it. They’re friendly, helpful, generous, exceedingly knowledgeable, and make it easier to learn SQL Server than other technologies.

On Monday, I’ll answer the other question posed to me in those emails. Should you pursue a Business Intelligence Career?

We often hear of the ability of SQL Server Integration Services (SSIS) to leverage multithreading to increase performance. However, multithreading in the control flow, executing two or more packages simultaneously, is what usually comes to mind. I recently ran into an issue with a large data set that I needed to multithread in the data flow. I was dealing with a large set of addresses and the client owned a third party SSIS product which they used to parse them and the performance wasn’t stellar. At the same time the server wasn’t even breathing hard, so I suspected that multithreading would speed up this process.

Since the addresses had sequentially numbered id values, the first thing that occurred to me was to use the modulo function in SSIS. Modulo returns the remainder when one number is divided by another. Naturally, I took to the web and found this excellent article by Josef Richberg (Blog/Twitter) regarding the use of the T-SQL modulo function, but my source was a huge text file, not SQL Server, so that option was out. Ultimately, this is what I decided to go with.

I surmised that if I used a Conditional Split and checked for numbers evenly divisible by 2 (modulo 0), I could evenly split the data flow. This worked, but I felt I could increase performance further by adding a 3rd option as shown below.

For the purposes of this article, I created a Numbers or Table using code from the great Jeff Moden. I created a table of 10,000 sequential numbers (Field N) and got the following results:

The Derived Column transforms represent some kind of process, in my case a 3rd party address parser. The data flow is evenly divided into three streams and then reunited by the Union All transform.

What if you want to divide the data flow into more than 3 even streams? Well, you could insert N % 4 == 0 as the first flow, but the results are too uneven in my opinion.

Using 5 as your first option yields a more even division and the flow is divided into 4 streams.

This is great, but what do you do if you don’t have a sequential ID in your data set? Well, there are several options. Remember that the integer field need not be an ID. Any integer field, particularly if it has varied values, is a good candidate.

If you don’t have an integer type field, then you can create one using the script task. I’ve used this method several times to generate surrogate keys and it seems to have very little overhead.

Also, Tim Mitchell (Blog/Twitter) blogged about how to split your data flow alphabetically if you have a character type field with sufficient variation. I think this method is less advantageous because experimentation would be required to determine cut off values which result in evenly divided streams.

Any of you have other methods of splitting the data flow?

This is a quick script which creates a table consisting of State Names and their corresponding Abbreviations. I use this table when doing basic data quality checking and also standardizing state designations.

CREATE TABLE dbo.StateLookup
(
   StateID       INT IDENTITY (1, 1),
   StateName     VARCHAR (32),
   StateAbbrev   CHAR (2),
 
)
 
INSERT INTO StateLookup
VALUES ('Alabama', 'AL'),
       ('Alaska', 'AK'),
       ('Arizona', 'AZ'),
       ('Arkansas', 'AR'),
       ('California', 'CA'),
       ('Colorado', 'CO'),
       ('Connecticut', 'CT'),
       ('Delaware', 'DE'),
       ('District of Columbia', 'DC'),
       ('Florida', 'FL'),
       ('Georgia', 'GA'),
       ('Hawaii', 'HI'),
       ('Idaho', 'ID'),
       ('Illinois', 'IL'),
       ('Indiana', 'IN'),
       ('Iowa', 'IA'),
       ('Kansas', 'KS'),
       ('Kentucky', 'KY'),
       ('Louisiana', 'LA'),
       ('Maine', 'ME'),
       ('Maryland', 'MD'),
       ('Massachusetts', 'MA'),
       ('Michigan', 'MI'),
       ('Minnesota', 'MN'),
       ('Mississippi', 'MS'),
       ('Missouri', 'MO'),
       ('Montana', 'MT'),
       ('Nebraska', 'NE'),
       ('Nevada', 'NV'),
       ('New Hampshire', 'NH'),
       ('New Jersey', 'NJ'),
       ('New Mexico', 'NM'),
       ('New York', 'NY'),
       ('North Carolina', 'NC'),
       ('North Dakota', 'ND'),
       ('Ohio', 'OH'),
       ('Oklahoma', 'OK'),
       ('Oregon', 'OR'),
       ('Pennsylvania', 'PA'),
       ('Rhode Island', 'RI'),
       ('South Carolina', 'SC'),
       ('South Dakota', 'SD'),
       ('Tennessee', 'TN'),
       ('Texas', 'TX'),
       ('Utah', 'UT'),
       ('Vermont', 'VT'),
       ('Virginia', 'VA'),
       ('Washington', 'WA'),
       ('West Virginia', 'WV'),
       ('Wisconsin', 'WI'),
       ('Wyoming', 'WY')

Also, you can add common misspellings to the table such as ‘Pensylvania’ to further clean up your data. If your data set is international, you may want to add Canadian provinces as well. One way I use this table is with a SQL Server Integration Services (SSIS) Lookup transform and the following query.

SELECT StateName State, StateAbbrev FROM StateLookup
UNION ALL
SELECT StateAbbrev State, StateAbbrev FROM StateLookup

This standardizes everything to 2 character states abbreviations. Any records which don’t match can be handed in another branch of the data flow through fuzzy matching or another method.

Do you use anything similar?

As I’ve mentioned before, I think I’m the most avid reader of my blog. I use it to document my work so that I don’t have to constantly re-invent the wheel. In this case, I find myself repeatedly searching for these scripts, often to populate SQL Reporting Services parameter defaults, so I thought I would share them with my readers as well.

-- Midnight of Current Day
SELECT dateadd (dd, 0, datediff (dd, 0, Getdate ()))
 
-- Midnight of last Friday (or whichever day).
/*The day of the week of date entered determines the result. Pick
a Friday, any Friday, and you will get last Friday.
Replace the - 0 with any increment of 7 to get previous weeks.*/
 
SELECT DATEADD (day,((DATEDIFF (day, '20120203', CURRENT_TIMESTAMP) / 7) * 7) - 0,'20120203')
 
--Day Number of Current year
-- Removed because the query was faulty. See comments. 
 
 
--First Day of Month
SELECT DATEADD (mm, DATEDIFF (mm, 0, GetDate ()), 0)
 
--Last Day of Month
 
SELECT DATEADD (s, -1, DATEADD (mm, DATEDIFF (m, 0, GETDATE ()) + 1, 0))
 
--First Day of Current Year
 
SELECT DATEADD (yy, DATEDIFF (yy, 0, getdate ()), 0)
 
--First Day of Previous Years
--Substitute '-1' with whichever number of years to reverse
SELECT DATEADD (yy, DATEDIFF (yy, 0, getdate ()) - 1, 0)
 
--First Day of Next Year
SELECT DATEADD (yy, DATEDIFF (yy, 0, getdate ()) + 1, 0)
 
--Past 30 days.
SELECT DATEADD (dd, 0, DATEDIFF (dd, 30, GETDATE ()))
 
--Add in beginning and end of prior quarter.
SELECT DATEADD (qq, DATEDIFF (qq, 0, getdate ()), 0)
 
--First Day of Prior Quarter
SELECT DATEADD (qq, DATEDIFF (qq, 0, getdate ()) - 1, 0)

I’m not taking credit for these scripts as I’ve stolen borrowed these scripts from various places, most recently this excellent post.

Do you have any queries that you’d like to add?

While performing some SQL programming work for a client they mentioned a problem they had with a vendor. I won’t name the vendor but I will say that it rhymes with Made2Damage. Anyway, the client spent most of the day trying to fix a problem with the help of telephone support. Hours were spent on the phone and at various attempted fixes. Now, I’m not blaming the vendor because the support representative can’t know this client’s setup as intimately as I do so they had to address every possible cause of the problem.

I asked him to briefly describe the problem. I then responded to him, “let me guess, the problem turned out to be the….” Of course, it was the same solution that he got through support, but it took five minutes to fix versus six or seven hours.

I asked why the client didn’t simply call me. He sighed and said that the vendor support was “free” (yearly paid support) while I would charge him per hour.

So, he wasted most of the day… but at least he saved fifty bucks.

Recently I worked on a project where every data type was user defined. In case you weren’t aware, SQL Server supports the use of User Defined Data Types. These are custom data types which are based on the standard types. For example, if your data always uses a two character string for states, you may want to create a data type called UDT_State.

However, this project had 40 UDTs and their names were fairly ambiguous. So, I created a “cheat sheet” script to refer to when working in that environment.

First, let’s create some user defined data types.

use tempdb
go
CREATE TYPE [dbo].[UDT_State] FROM char(2) NOT NULL;
GO
CREATE TYPE [dbo].[UDT_User_Id] FROM Int NOT NULL;
GO
CREATE TYPE [dbo].[UDT_Cost] FROM decimal(10, 3) NULL;
GO

The following script is compatible with SQL 2005 through 2008 R2. I believe it also works on SQL 2000 but don’t have access to a server to test it.

SELECT ST.[name] AS UDTName,
       CASE
          WHEN ST1.[name] LIKE '%char'
          THEN
             ST1.[name] + '(' + cast (ST.max_length AS VARCHAR (5)) + ')'
          WHEN ST1.[name] = 'numeric' OR ST1.[name] = 'decimal'
          THEN
               ST1.[name]
             + '(' + cast (ST.[precision] AS VARCHAR (5))
             + ',' + Cast (ST.scale AS VARCHAR (5)) + ')'
          ELSE
             ST1.[name]
       END AS BaseType,
       CASE ST.is_nullable WHEN 1 THEN 'NULL' ELSE 'NOT NULL' END AS Nullable
  FROM    sys.types ST
       JOIN
          sys.types ST1
       ON ST1.user_type_id = ST.system_type_id
 WHERE ST.is_user_defined = 1

Special thanks to Jeff Rush (Blog/Twitter) for the idea to write this script.