Databases, Structures, and the Damn Data Itself

ETL is the heart and soul of the data warehouse, so why does ETL job design and performance always seem to get little attention when schedules are tight? If it runs, it's fine, right? No! Follow the 'Durable Impact Top 5 Rules of ETL Design' below to develop quality ETL jobs and minimize your headaches with SQL Server Integration Services: 1. Always build jobs as modularized, stand alone components. 2. Segregate jobs based on the importance of the data. Most needed data gets loaded first, etc. 3. Build file extracts within a job and make every job restartable without having to restart the ETL process from the beginning. See #1 above. 4. Parallelize only when necessary. With SSIS 2008, don't use manual parallelization, the backend engine is much improved removing the headaches of slow performance on large data sets. 5. Use the tool best fit for the job. Example: When reading from databases, consider using the SQL ORDER BY command to presort data that must be sorted. By following the 'Durable Impact Top 5 Rules for ETL Design' you can improve your performance and cut down on your support time.

Yes it is, a great time for BI. Why is this? BI saves customers money, customers spend a little money in a BI application or dashboard but save a lot more with intelligent data and all the good things that come with it.

Thanks to everyone who attended my presentation today at SQL Saturday. It was a great crowd and I enjoyed it immensely.

Most software apps have a very strong following and drive strong opinions from the users, taking for instance Windows vs. Mac. Those who use Windows often don't understand Mac, and those who use Mac despise Windows. Data modeling is one tool where I haven't found this type of approach, almost everyone I've talked to either uses ERWin or has heard good things about it.

My friend Andy had a blog posting last week about sales guys in the software field. Selling is always an interesting part of running a business and most likely the part that causes the most headaches, but without sales there is nothing else.

What was your first thought when you read the title "A bumpy flight"? Were you confused, trying to figure out if I was talking about the economy? Or did you think I was talking (as I do) in analogies?

Hello all, I'm going to keep this short today.     DI is needing to grow our stable of consultants, and specifically I'm looking for a couple positions with the following:

Today we'll talk about the differences in insurance between working as an employee and working for yourself. Yesterday I explained the difference between contracting and consulting, but both types are considered (by the IRS) to be self-employeed unless they receive a W2, and then you're an employee

Companies began providing insurance as a benefit to attract workers after the shortages in WW2. This benefit has grown into the extreme mess we call "health care" today in the United States. The problem here is that we spend the most on heathcare of any country yet do not receive the best care. Bear in mind, there will be a lot of economics discussion today, but it's necessary to understand the point.

Employers, being the de-facto providers of health care, have a few advantages going for them. The first is adverse selection. Every company will have some people who require recurring care (cancer, diabetes, etc) and some people who require no care, so the insurance pool overall will skew towards the median care requirement being right in the middle. Thus, the portion of insurance for each person will be say, $200, of which the employer picks up $150 and the employee pays $50. (Assuming a single person here). The employee portion of $150 is considered a benefit (and a tax writeoff) to companies.

Let's take the same person under a self-employed scenario. Now, this person knows their own health. We call this moral hazard. They know their favorite foods are cheese fries and buffalo wings. They also know their family history of heart disease. They do not reveal this to the insurance company though when this person purchases insurance for $200 a month, of which the insured is responsible for the entire amount.

There is another self-employeed person who is very fit, has no history of family disease, eats healthy, but balks at paying $200 for heath insurance the person does not think he needs. This is where adverse selection comes into play, as only the sick people or people who believe they will need care buy insurance, and the healthy do not. That is why private cost plans are cost prohibitive. There are some ways around this, as credit unions and other firms try to "pool" people together to buy insurance at discounted rates, to "pull" both of the people (heathly and unhealthy) to buy insurance and average out the risk again. However, this still presents major problems for insurers, who are in the business to make a profit.

Now we enter into the political debate as to whether to force people to buy insurance on the private market, providing the pooling mechanism, or to have the insurance provided by the federal government which again provides pooling. I'm not going to get into this futher except to state that either of those options is preferable to me versus the current option of employers providing insurance.

For those who accept the risks of self-employment and need insurance, there are multiple things you can do. First is to buy a full plan on the open market, which is very cost prohibitive. There is something called a HSA (Health savings account) which when linked with a high-deductible plan, allows people to retain insurance for catastrophic occurences but also has high deductibles to discourage the overuse of the insurance. (If you're interested, this is what I do). If you're married to a partner who has corporate insurance, maybe the best option is to use that person's plan.

If you're going to consider the cost of health insurance in your criteria, factor in the full cost. Employee contibution is $50 every two weeks, and private insurance is $100 a month. Therefore, the premium expense is equal. There are other costs to be factored in here, such as office visit deductibles, but remember that cash is king, and while on corporate insurance I had a prescription once that was a $37 co-pay, but only $21 cash price.

If you're thinking of becoming self-employeed understand that there are options available to you, and with the looming discussion of health-care reform, the options may be more attractive and remove it as a key decision making point in the future.

Before we go any further in the "Employee vs. Consultant" discussion, I want to clarify a few things. There is a difference between consulting and contracting. Many times the words are used interchangably but this is not correct. I'm certain others will have a differing point of view, but here's mine.

Contracting: hired to do specific tasks, not always a specific range of subject matter expertise needed, for a given duration, very little overview

Consulting: hired specifically for expertise, asked to advise on processes and output, may do some tasks, but work closely with management and employees to instruct and guide

When I first started out I was contracting, but then moved to consulting. Consulting is where you can really make a difference, contracting is more like being a temp employee. Just today I was participating in a conference call with a client, discussing the way a dimensional model is built. One of the attendees asked me why I put both the surrogate key and business key in the fact table, and I replied that I did it in case the surrogate keys were corruputed, to aid in rebuilding them and also for research. Another voice popped up and said that was a good standard for moving forward, and now it looks like that client will be using that process in the future.

Contracting is different though, it's like signing a contract to run a couple thousand test plans against a new piece of software. That requires experience to execute the plans and report results. The difference is that during the requirements gathering and design review, a consultant who had worked with other systems in the industry was brought in to review and provide thoughts for product enhancement. It should be clear that there is a difference in executing a script and advising business process owners and management on features.

There are key differences between contracting and consulting, and it's good to keep them in mind when reading my posts.

It's been about a year ago when I wrote my first post about the differences and trade-offs between working for someone and working for yourself. My conclusion then was that you should add up the costs and benefits of everything and then make the best decision. The problem is that the true costs and benefits are obscured and it's hard to make an informed decision. I decided to expand on the topic and have a couple different posts exploring different dimensions of making the best decision for you.

In December 2008 I received a phone call from a friend, the type of call that you never want to receive. He informed me that he had been let go from his corporate job. He's not too far from retirement and almost had his house paid off and was well thought of at his job, but the management had to make a decision that I'm sure was not easy. He was shell-shocked, but quickly recovered and found something with little overall loss in pay and benefits. His statement to me "I thought it would be my last job" was telling. In his mind, he was relatively safe, having traded some dollars in pay (though not much, he was doing quite well) for perceived stability. All of my clients have let people go since last year. I wonder how many of them believed they were safe too. More to the point, I wonder how many believed they were safe and traded a decent amount of salary for that security which they did not receive?

What would you do if you have the choice of two jobs that
- Company A: $78,000 a year, history of mass layoffs and hire/fire shop
- Company B: $70,000 a year, history of never having a layoff
Consider all other benefits as being equal. What would your decision be?

This is a true life scenario of another acquantance of mine. She picked company B, and two years later the company, well known for it's stability (both good and bad) was sold and everyone was laid off. In that two years, she traded $16,000 of salary for perceived stability. After taxes, you have a new Ford Focus. To be fair, there is no guarantee that she would have made two years at company A, but the economy was growing and during that time A did not have any known layoffs.

The point of my exercise, in case you missed it, was to try and remove stability from the equation as much as possible, as stability is a factor that is so far outside of an employees' control. Look at local Tampa company WellCare, a stable company that just did a massive layoff due to some fraud charges. How many DBA or business analysts had any control over that?

Consulting hasn't been too much better over the past year. Many projects have been cut, cutback, delayed, etc and some consultants and contractors have been enjoying time at home. A consultant I know who lost a project was quite concerned and told me he only could survive 2 weeks without a steady gig, if that. Without knowing the details or being judgemental, that's not a good position to be in any way, but in that case being an employee might be a better route as generally layoffs include some kind of package or unemployement, where self-employed does not. (In case you're curious, he went exactly two weeks between projects).

In conclusion, I recommend completely ignoring any discussion or talk about job stability and focusing on other aspects before you make your decision.

Coming up next: Health care, the most dreaded expense.

Today I want to write about my server management policy and philosophy. This is one cost area that doesn't offer a competitive advantage for us, so it's best to go with the cheapest options and minimize expenses.

My company's policy on server management is that anything production-capable will be managed by someone else. That is, we host everything with a professional provider. We're in the business of consulting and data management, not hardware providers. I don't feel like we can adequately procure and maintain the hardware as well as a company that is dedicated to this task. The inspiration for this decision was David Ricardo, a famous British economist who is most commonly known for the theory of comparative advantage. That theory goes like this: if China is best at making clothes and the United States is best at making computers, then China should make clothes and trade them to the United States for computers. That's a very simple way to describe it, but we all know what we're best at and I want to focus the business on consulting and not hardware management, so we outsource that area.

Hosted servers are expensive on a month-to-month basis but much cheaper in the long run versus buying hardware, paying for a COLO setup, and buying software licenses. I'm certain that the costs will continue to go down with the introduction of Hyper-V on Windows Server 2008. Durable Impact works with both Unix and Windows-based environments, but our hosted servers are running Windows Server 2003. At some point we will move to Windows Server 2008, but only once our hosts do.

While working at a client site the other day, one of the people I was talking with had a memo from 10/13/1988 hanging up at his desk. This memo concerned how to use the office lighting system (which must not have changed since then), and was yellowed with age, but still readable. I joked with him about the memo and we got into a discussion of the days when "there was one modem for every 10 people". That reminded me of my first PC.....

....to tell the entire story, my parents purchased a Tandy 1000HX system in November 1987. I don't remember much about that computer, but we only had it for a while before my dad traded it in for a Tandy 1000SX. We bought it at a Radio Shack store in their "Tandy Computer Center". It had a 40 MB hard drive, 512 KB of Ram, and 5.25" and 3.5" floppy drives. It ran MS-DOS and my brother and I had a bad habit of just turning it off, which caused the hard drive to crash, thus we got a hard scolding to type C:\PARK to park the heads before shutting it down. We used this computer for years for games and general Word Perfect 5.1 applications and with work and high school I didn't use it for a long time until I got my own computer.

It was a Packard Bell desktop purchased from WalMart for $1399 in 1996. It had a 15" screen and a blazing-fast Pentium 75 with a whole 8 MB of ram and a 640 MB of hard drive. I saved up my own money and purchased a Canon BBJ-4100, one of the first color bubblejet printers for a cool sum of $318 after taxes. I still have both of the receipts, but I recall that printer cost an entire week of work. When I arrived at college shortly thereafter, I discovered that all my dorm mates loved the printer and cartridges rarely lasted a month, and no one could afford to chip in to replace it. I also realized that Packard Bell wasn't so hot, and by summer of 1998 I sold it for $350.

My next machine was a step back, but really a step forward. It was a used 486DX/66 that my dad bought at a garage sale. I loaded it with a cool 20 MB of ram, a 4.3 GB hard drive (which seemed entirely huge at the time), and a decent video card. The only thing it didn't do well was play games but it was remakably snappy for being a 486. Being at the university I had accesss to 10 Base T ethernet and enjoyed the web in high speed.

In spring 1999 I built a new machine using my mothers' credit card (thanks, Mom although I know you weren't happy at the time). It was a Celeron 300A with 128 MB Ram and an actual legal copy of Windows 98. If you remember, the 300A was the unlocked processor that was highly sought after for builders, and I picked one up cheap. I was able to get it to run at 550 Mhz at one point, but I generally kept it at 450 Mhz using my Abit BH7 motherboard. Throw in a 10 GB hard drive and a 17" flat CRT monitor (pretty good size for that time), and I was computing in style.

That computer lasted until 2002 when, after multiple hard drive crashes (now I know why OEM drives were so cheap online), I purchased a Dell Dimension 8200 desktop with Windows XP home edition. It was a P4 2.53 with 256 MB of RAMBUS ram. The computer was a great deal ($750 incl 17" CRT) but only because Dell was trying to liquidate their RAMBUS ram because it was so expensive and DDR had just come out as a competing technology. I paid $200 just to put 512 MB of ram in the computer, but by the time I sold it in 2007 for $350 I had added a 19" LCD (again, when I bought it that was HUGE and rare), a super nice 4xAGP 256 MB Radeon video card, and a 160 GB hard drive. That was the last (and best) desktop I owned and now I'm only using laptops.

In 2003 I picked up a Dell Inspiron 1100 laptop, Celeron 2.0Ghz, 512 MB ram, 40 GB HD, Intel 945 graphics card. It was cheap ($800) but had a 15" screen (1024x768). I could never get used to the screen resolution as everything was too big, and the computer was a beast, but worked well and survived two years of being totally abused in my backpack being trucked to work and back. Somehow I was able to avoid the problems that plauged that lineup (especially the 5100 and 5500) models that used full Pentium chips, somehow the CPU cooling ducts were on the bottom and would suck in dust and clog up baking the CPU. I never had any issues, and the computer worked great until the day I sold it.

In 2005 I picked up a Dell Inspiron 9300 P-M 2.0, Windows XP pro, 1 GB ram, Nvidia 6800 256MB, and 80 GB HD. This is the computer you will still see my using often as it has proven to be a true workhorse. It was one of the first 17" laptops on the market and I'm very thankful to have it, as it's been worth every penny and them some. I just used it today to do a client presentation. The only complaint I have is that the screen resolution is only 1440x900, but starting with the 9300 Dell discontinued the anti-glare HD screen and if you're read this blog before you know my opinions on that matter. I've been watching ebay and I can pick up a matte HD screen for around $200 but it doesn't seem worth it.

I won't even write about the mistake of an HP laptop purchase in 2008, but I also purchased the Apple MacBook Pro 17 in 2008. This is by far the nicest and best spec'ed out machine I've every owned, although it will be hard pressed to beat the Dell 9300 overall. It has a C2D 2.0, 4 GB ram, 320 GB HD, Nvidia 8400 GS 256 MB, and HD matte screen. I'm not crazy about the apple software, accessories, or fashion statement component that came with the purchase, but it's hard to argue with the form and design of the machine. We'll see how long I keep this one, but so far I think it was a good deal overall.

Well, that's about it for my trip down memory lane, now it's time to get back to work. Hope you enjoyed it and think about how you got started, comment on it below. Let's see who has the oldest rig still in daily service. (2005 here)

The other day I mentioned that the economy was having an effect on the rates consultants can charge for products and services. In most cases, the effect is a negative one. This will be a good topic for today because the rates are always the most important part of a project.