Tod means Fox

Supporting decisions through sound data management

Posts Tagged Productivity

Scoping Data Warehouse Initiatives

Aug 12

Posted by Tod McKenna in Business & IT Issues

focus Scoping Data Warehouse InitiativesData warehousing is a complex operation. From start to finish (if there is a finish), project teams are faced with many challenges. In all phases of the lifecycle, there are opportunities for derailment. The best way to mitigate potential issues and stay on time and within budget is to carefully define and manage scope. Managing scope can be an ongoing struggle (especially if requirements are not clearly defined or justified). While this is really a PM101-type of topic, I feel there are some fine points in a DW/BI environment that are not mentioned enough.

Consider the following:

Programs verses projects

I won’t get into a deep PM discussion here, but it is important to point out that data warehousing (or business intelligence, master data management, etc.) initiatives should be thought of as programs and not projects. This mindset will help in scoping.

A program (which might also be called a “project portfolio” in some circles) is basically just a set of related projects. With a program, the emphasis is on organizing, prioritizing, and allocating resources to the right projects. Program scope is more strategic, and answers long-term questions about what type of value the organization hopes to achieve from the initiative.

A project, on the other hand, is much more specific — with a set number of deliverables and goals that have a high immediate impact. The scope at the project level is therefore more tactical in nature: high impact, fast delivery. Be aware that some projects may never be given the green light (for example, if there is a low business impact or if there is a low feasibility rating because of data source or data quality complications).

What I find odd is that organizations still choose to tackle immense data warehousing initiatives in one or two shots, trying to deliver everything at once over a period of 18 or more months. This is the wrong approach (here’s why). Break this large initiative into individual projects and try to deliver functionality every 6 to 8 weeks.

The business process

The best way to break down data warehousing programs into high-impact projects is along business process lines. A business process, as defined here, is:

The complete response that a business makes to an event. A business process entails the execution of a sequence of one or more process steps. It has a clearly defined deliverable or outcome. A Business Process is defined by the business event that triggers the process, the inputs and outputs, all the operational steps required to produce the output, the sequential relationship between the process steps, the business decisions that are part of the event response, and the flow of material and/or information between process steps.

Some example of the above: inventory tracking, Internet sales, retail sales, marketing, tax assessment, tax collection, pitching, batting.

In any data warehousing environment, you can expect to have several business processes to model. Each business process you tackle will have elements touching upon different aspects of the data warehouse, including infrastructure, middleware, data modeling, ETL, business logic development, presentation elements, and so on. If you scope each project to the business process, you can deliver complete solutions in the shortest amount of time. (It should be obvious that the very first business process you implement will take the longest, as the team works out the core infrastructure. Most of this infrastructure will be reused by other business processes.)

Avoid scoping to a data source

Do not fall into the trap of scoping to a data source. Scoping to a data source is almost guaranteed to deliver mediocre outcomes. These projects typically include many unfinished or inadequate business processes all delivered at once some time in the distant future and long after the excitement over the initiative has subsided.

While it is true that only one or two data sources might exist in some organizations, it is not true that inventory, customers, sales, procurement, shipping, and other business processes need to be taken on at once. Create a single project for each business process, prioritize based on impact and feasibility, and then badabing badaboom, you deliver. Next.

Along the same lines, do not adjust your scope if the data source is unavailable, uncooperative, or lacking in quality. Instead, bring the fight to the data source (here is where a good, preferable C-Leveled, business sponsor can come in handy) and set things right. This is obviously a project risk, and also an organizational risk. If you are having problems extracting inventory data then maybe its time to put down your data warehousing gloves and get a new inventory system.

Last thoughts

Scoping the data warehouse is a difficult problem. Troubles start early on with the initial idea, it moves on through requirement gathering, and finally into the development phase of the lifecycle. There is not a lot of good advice in this area for data warehousing (if you happen to know of a good source, please send me a link or title). But I do find that if you work towards business processes, think in terms of programs and projects, and avoid the data source trap, scoping decisions will settle into the real needs of the business.

Tags: , , , , , , , , ,

1 Comment

The Three Faces of a Good ETLer

Jul 24

Posted by Tod McKenna in Business & IT Issues

Hiring a “data integration expert” or consultant for your next, greatest, data warehousing project? Don’t take it lightly. ETL personnel are critical to the success or failure of your project.

The following are what I deem to be essential technology-related aspects, or faces, of a good ETL developer and/or architect (herein referred to as an ETLer for lack of creativity). While you need to consider business and industry knowledge, personality, and experience in your team-building process, you should start by checking off the following on your interview sheet:

First Face: the technologist

Programming must come natural to an ETLer. Objects, logical constructs, expression construction, program flow, and the like, must be well understood. The truth is that no matter how much your vendor proclaims that their tool does it all, chances are excellent that some hand coding will be required. On top of that, ETL tools work a lot like procedural programs. Technologists are very good at putting their right foot forward, and will generally think of things to make the ETL flow perform better. They also think about logging, auditing, and exception handling; all important.

Second Face: the theorist

But a solid programming background is not enough. Knowledge of Data Integration theory and best practices are equally important. While I believe in and use Kimball’s methodologies for integrating data into a dimensional data warehouse, other methodologies exist that may be more suitable to your business and integration needs. Following a proven methodology, with slight modifications to suit your environment will get you further, faster. Having little or no theory behind what you’re doing gets you somewhere, slower. Identify your methodology, and then find someone who understands it.

Third Face: the specialist

Knowing the ins and outs of your ETL tool (SSIS, OWB, Datastage, Talend Open Studio, etc.) is essential. I would venture to guess that a solid programmer who has a great understanding of ETL theory will be able to get by using most tools with little learning curve. What I worry about (and you should too) are the nuances in the tooling that can stump even the best. These nuances (SSIS, my tool of *ehem* choice — sorry, I needed to clear my throat, has many of these nuances) can cost you many project hours and force rewrites if blocking issues are encountered. Tool knowledge is also essential to know when it is appropriate to forgo the tool because of I/O issues, or because hierarchical data is better handled elsewhere, or because business logic is best not bundled within a data flow.

About Face

While junior members of your data integration team can be one or two-faced (that came out funny), senior members and architects must have more meat on the bone.

I suppose this is why good ETLers are difficult to come by. The ETLer needs to have a healthy mix of programming talent, an approach discipline, and tool knowledge. Trained DBAs and software developers might have a lot to offer, as might a troop of certified tool jocks and method junkies, but to get your project in on time and within budget, don’t settle.

Tags: , , , , ,

No Comments

Top 7 Reasons I Wear a Suit

Aug 29

Posted by Tod McKenna in Business & IT Issues

I dislike wearing suits.

It used to be that I could code in my favorite Phish t-shirt wearing sandals. I had a key instead of a badge, and lunch usually meant a few greasy pizzas or clam cakes. In those days, my attire only meant something if there was an off-site or if clients were coming to visit “the shop” (which was a tiny building several miles from the heart of the big city). I could easily bounce back and forth between long and short hair and between full beard and cleaned-shaved. Ahh… those were the days.

Now I work in a major international city for a rather large bank. I code in a suit when I’m not in meetings, wear nice shoes, carry a badge, and eat salads and yogurt for lunch. *sigh*

To be fair, I enjoy the new challenges and the big city. And if wearing a suit on occasion is a consequence, I can live with it. So while a suit is not fully mandatory, I still wear one at times. Here’s why:

  1. It easily puts me in line with the dress code
  2. Dressing is simpler in the morning (although sometimes it takes a couple of tries to get the perfect knot in my tie)
  3. My wife tells me I look great
  4. Dressing down on Friday never felt so good
  5. I look more important than I am
  6. I feel more important than I am
  7. My jacket flaps behind me in the wind when I ride my bike to the train station, which makes me feel like a super hero with a cape

Other than those fantastic reasons, wearing a suit is a real drag.

Some man on a bike wearing a suit in Europe (I do admit, there is something rather Monty Pythonish about wearing a suit on a bike. I bet I look pretty silly to the folks driving past me. But riding my bike gives me more than 30 minutes a day of much-needed exercise, and on top of that, the price of gas here in Europe would blow your mind!)

Tags: ,

1 Comment

Business Casual

Jul 25

Posted by Tod McKenna in Business & IT Issues

So today I am wearing jeans and a faded (but once pleasant) button-down shirt. No tie, although my shoes are nice and I am wearing dark socks. A few others around me are similarly dressed, which is typical for summer Fridays. Then there are those who are in full suits, as if preparing for a job interview or some important sales meeting. Others are in suits but without tie which is in-and-of-itself a very strange practice; I call these “half-suits”.

You can draw two lines in the sand separating all three groups of Friday dressers. You have the “workers” – those actively engaged in business operations like myself; the middle management-type who are no longer “workers” and who aspire to delegate more and more activities, you know the type – it’s just like them not to wear ties with their suits; lastly, there are those who on the one end of the spectrum make only infrequent strategic decisions of the C-level type and on the other end those who work for mostly commission and must rely on their superior charisma (and sharp suits) to get ahead.

My colleague and I are working feverishly at the moment to improve the performance of one of our BI applications: A process we hope to improve from roughly 15 minutes to about 7 minutes (so half-suit and full-suit can have more time at the cooler). In addition, I am troubleshooting a foreign key violation in one of our ETL loads, and my partner-in-crime is hunting down the results of some replication testing in our production environment. Meanwhile, a full suit is currently browsing an online golf store; the half-suits are centered around the water cooler.

This, symbolically, highlights the problems with business and IT alignment in general — especially in large organizations. I find that IT is normally of the first variety – willing to dress down whenever possible to add a little comfort to an otherwise fast-paced existence full of responsibility and accountability. Dressing down in no-way implies a dress down of activities or a dumbing down of skills.

As you can tell, this is a bit of a rant and a fallacious attempt at tossing my colleagues into generalized buckets. But one thing is very true: business and IT need to get in sync. I would like to think that my team is above average in this regard. The immediate team consists of business and IT personnel - all of which are fighting for a successful project.

‘d like to hear your thoughts on this matter….

Tags: ,

3 Comments

Cooling Down

Feb 15

Posted by Tod McKenna in Personal

It is (or should be) common knowledge that you should never send an email, write a blog or forum post, or make a phone call when you’re totally ticked off about something! You are likely to say something you don’t mean or perhaps you’ll be a little too honest.

First cool down, and then respond. Easy enough, but what if you can’t wait to cool down using traditional methods (you know like, take a long hot bath)?

The solution: Simply write your name a few times on a piece of paper using your non-dominant hand. Apparently, it will force the logical side of your brain to start working, giving your emotional side a few seconds to forget why it is so upset (or sad, or excited, etc.). For all the neurosurgeons out there who might want to debate brain lateralization, I’m not the guy for you! But this technique has worked many times for me (and it recently got my sister-in-law out of a funk).

Over the past several days, I’ve also been looking into other ways to train my brain to either help in logical tasks, management tasks, programming, motivation, etc. I stumbled upon a blog entry (from Gary’s Historical Art) that spoke of the book “Drawing on the right side of the brain“. I remember this book from my childhood and was thrilled to see it has a new addition. It contains some additional information on (a) the latest developments in brain research, and (b) information on using drawing skills for problem solving. I plan to get a copy soon.

Tags: ,

No Comments

The 80/20 Principle and Software Development

Sep 3

Posted by Tod McKenna in Software Development

In the book “The 80/20 Principle: The Secret to Success by Achieving More with Less The 80/20 Principle and Software Development“, author Richard Koch describes how a minority of “causes, inputs, or efforts” will lead to the majority of “results, outputs, or rewards”.

A great demonstration of how the 80/20 Principle (aka Pareto’s Law) works can be seen through examining source code. Clearly, a small minority of code in an application produces the vast majority of business benefit to the user. Usually, this code is well defined, tested, and performs great — after all, it is the core of what the application does. Once in production, this code is low maintenance. Any changes or enhancements are usually well scrutinized and will be tested thoroughly. Your applications are bought based on this core minority of code.

Then there’s the other code.

The other code plays more of a supporting or supplemental role: It’s the code that handles your ultra-cool menu system; It’s that extra group of reports that seldom get run; It’s that cool Calendar control that seemed like a good idea at the time; It’s all the extra features that you’ve tacked on over the years. This vast majority of code contributes least to the business needs of the application, costs you the most money to maintain, and is likely to contain the most bugs.

So what’s a developer to do?

Surely bells and whistles, gold plating, and other extras help sell the product. So I am not an advocate of stripping software down so that it is only functional. Software users expect to have a somewhat enjoyable experience behind the keyboard. Boring, functional applications would look a lot like a dos window and be the subject of many scornful conversations at the water cooler.

Instead, get the most bang for your buck. Identify the 20% and expand it. Identify the 80% and depreciate it. Revisit and repeat at each release cycle. Easier said than done? I’ll give you an example:

80/20 In Action

The volume of user reports in a mature application can get out of hand. I’ve worked on Applications in the past that have had more than 100 user reports. One day I wondered (out loud to my coworkers) which reports were actually being used. No one truly knew the answer, but we all had a hunch that the answer was “not many”. So, I added a secret logging script in my report class that logged report usage to a flat text file stored in the root directory of the application. I stored the report name, the user, and the timestamp. We retrieved the file when dialed in for support services. I found that out of 130 custom reports, only 6 were being used on a regular basis at about 100 installed locations, and a whole 50% of the reports were never run at all over the test period (11 months).

Whoa. Consider now that we spent time regression testing these reports during our latest release, that we regularly trained our users on how to run them, and that they are all included and dutifully updated in our documentation. Bad management? Poor software design? Scope bloat? Or just a perfect demonstration of how the 80/20 Principle works in software development: you decide.

The solution in this example was simple: We stopped supporting the unused reports (we continued to monitor the usage logs) and would not add any new reports without careful consideration. It felt good to trim the fat and in the end, we saved ourselves a considerable amount of work.

80/20 Analysis

During the different release periods of the software life cycle, an 80/20 Analysis should be done to both identify the core minority and the excess majority. Start with actual features (User Reports, Ad hoc Reports, Custom Query Engine, the Internet Backup Utility, Etc.) and then drill down into each feature and look for low hanging fruit. Once identified, monetize its impact on your engineering, quality, and client services teams. Also, place a value (using real sales dollars if available) on the feature itself.

This kind of analysis should reveal some interesting things about the software. If there are gray areas, for example if you are unsure of what is being utilized, consider doing something like I did to track report usage. Coverage profiling engines will allow you to identify seldom run or never run lines of code. These areas might give you clues as to what code will buy your house or pull you under. You can create a simple but professional survey on your website and ask your users what features they find most helpful and which features they don’t use. Not only would this survey tell you what is not being used, but it could also help you identify areas where you can increase the 20%.

For more information on the 80/20 Principle, I invite you to pick up Richard Koch’s book at Amazon. Have a read, and share with me your thoughts!

Buy The 80/20 Principle: The Secret to Success by Achieving More with Less The 80/20 Principle and Software Development now at Amazon.

Tags: , ,

2 Comments

Ezelsbruggen

Aug 14

Posted by Tod McKenna in Personal

Or as I like to say, my “easel’s broken”, because it helps me remember how to pronounce it. Which is funny because ezelsbruggen, in dutch (and German too, I think), translates into “donkey bridges” (according to the Internet and my wife). A donkey bridge is a memory technique for creating visual bridges between disparate words. In English, the translation is “memory aid”, or “mnemonic”.

Whether you have a rat in separate, your princiPAL is your PAL, or your committee does nothing but mutter mutter, talk talk, and eat eat, donkey bridges are quite handy. They help you remember little bits and pieces of information, like the form instantiation order in FoxPro (the lovely Lisa G.). Oftentimes donkey bridges are illogical, arbitrary, and downright silly — but they always seem to work. Who is this Lisa G. anyway?

So, here is a list of some of the mnemonics — Ezelsbruggen — that I have learned over the years. If you have any fun or interesting ones, please share!

  • LISA G.
    Load, Init, Show, Activate, GotFocus
  • Lucky Cows Drink Milk
    The order of Roman numerals (ascending): LCDM
  • Please Excuse My Dear Aunt Sally
    Parentheses, Exponents, Multiplication, Division, Addition, Subtraction.
  • My Very Educated Mother Just Served Us Nachos
    There are only eight planets now! Sad… but true.
  • Better Be Right Or Your Great Big Van Goes West
    Ohms value for the color bands of resistors (Black, brown, red, orange, yellow, green, blue, violet, gray, white).
  • Silly People Drive Fast
    Spectroscopic notation, after F, the rest is alphabetic

So here I am in a canoe in the middle of the Atlantic Ocean, drinking the salt water out of a coffee filter. Smoke from my hibachi is getting in my eyes as my veggie burgers begin to burn. Although I’m ankle deep in plums, I manage to toss Ben and Jerry overboard so they can fix the motor, which is nothing more than a dust buster turned from suck to blow.

That, my friends, was yesterday’s shopping list. Poland Springs, coffee filters, veggie burgers, plums, ice cream, and a new hand vacuum. If I don’t create an absolutely absurd story, I’m liable to forget something without a list.

Method of Loki

Another great way to remember sequences and lists is employing the Method of Loki. In its most basic form, you simply find a location you are familiar with in your head (your bedroom, office, kitchen, etc), and begin placing things inside the room. It’s a method many use for giving paperless speeches or for remembering meeting agendas. For example, in my office, I may see the head of the research department typing away at my computer, a spinning globe just off to her right, and a bowing shelf of paper documents within arm’s reach. This will remind me to discuss the new research project, segue into our International strategy, and finally end by discussing a much needed content management solution.

These and other such techniques for remembering items, spellings, sequences, etc, have been a big help for me. In the health care industry, I’m often required to remember many acronyms, processes, and procedures that are quite foreign (I have no medical background whatsoever — unless you count years of watching ER). The same might be true for you as well.

Now if you’ll excuse me, I need to see the quill from behind the tall man who’s sitting on a beanbag chair fiddling with a rubix cube.

Tags:

4 Comments