Tod means Fox

Supporting decisions through sound data management

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

10 Commandments of Data Integration

Jun 30

Posted by Tod McKenna in Data Architecture

  1. You shall compile and document all requirements and mappings; segregate the work by business process. You may have more than one of these business processes, some of which may come before others.
  2. Do not begin without first conducting a thorough data profile; otherwise, you will be punished for your inequities, as will the generations that come after you.
  3. Do not think commandments one or two are in vain, lest you will become overrun by the dead line, scope creepers, and a great exodus of people from your tribe; if this happens to you, do not swear or curse, for you have been warned.
  4. Remember that latency and timeliness are equal in importance to non-volatility and having a traceable lineage; a staging area may lead you to this promised land.
  5. Honor the rules of data conformance.
  6. Do not kill dirty data: you shall clean them, or take them back to their sources for retribution.
  7. Do not commit the worst data integration transgression of all and ignore data quality, your ignorance will not be forgiven.
  8. Do not be shy about stealing your neighbor’s work, for his trials have led to best practices that you can make equally good use of.
  9. Do not rely solely on business keys; surrogates are your friend and will permit you to engage in slowly changing your dimensions.
  10. You shall covet a proper audit and log system; for on the day of judgment, you will need proof of your compliance.

Tags: , , , , ,

No Comments

Actionable Business-IT Alignment

Jun 22

Posted by Tod McKenna in Business & IT Issues

Actionable Business IT AlignmentFor several years I’ve been following the Business-IT alignment movement. Alignment is necessary for fostering innovation and realizing greater profits as IT resources are used to their fullest to satisfy business goals and objectives.

But many organizations still struggle to make the connection.

Some suggest varying 3-, 5-, and 7-step approaches for better alignment. They typically consist of diving into new organizational structures, or implementing new frameworks and service models. Other sources say that the business is to blame, that IT is misunderstood and underutilized. While finally, some say it is a dead issue altogether.

I’m not convinced this is a dead issue, nor am I convinced that the business is to blame (especially nowadays). I’m also not convinced that 3-, 5-, and 7- step plans will magically work.

A few basic actions, initiated by IT, must happen first.

The Actions

Business has long ago recognized that better alignment with IT is essential. We can argue about how well they’ve understood IT and how it can best be utilized, but across the board, the recognition is there. IT, after many years of making the case for alignment, seems to be coming up short during this crucial period. I hope that the following actions, from an IT-perspective, can help:

  • Understand: Learn the language of the business. Plainly put, this means you need to become more financially intelligent. All businesses exist to earn a profit, and understanding how this works is the first critical step. You must understand where the estimates and assumptions are in the numbers, when you would want to depreciate or amortize, and what constitutes a capital expenditure or operating expense. Among many things, you must understand ROI, cash conversion, and how to use profitability ratios.
  • Participate: Take part in strategic and other long-term planning initiatives. IT professionals must be able to see the company’s vision and turn the vision into actionable IT initiatives. If a representative from IT is not present during steering committee and other board meetings, make this a priority. You will need to convince the business that IT is capable of playing an important role in all long-term decision making. Fortunately, businesses already realize this need, but in many cases don’t feel that IT can effectively contribute (perhaps because IT doesn’t understand the business language).
  • Contribute: Provide business with the ability to make fast and accurate decisions. This means pioneering smart business intelligence initiatives that can provide decision-makers with the tools and reports — distilled — that business can utilize. You need to prove your flexibility, agility, and ability to understand what the business really needs. Much of this is tied to understanding, and extended with participation. These initiatives also include business process improvements; tighter integration of business, meta, and master data; and managing performance.

If IT can take action, better alignment can be achieved. This isn’t to say that the business can’t do more, but before the business can make IT a full partner, understanding, participation, and contributions from IT are a must.

Tags: , , , , , , ,

2 Comments

New VFP User Group in Iraq

Jun 18

Posted by Tod McKenna in In The News

The FoxPro community has always been diverse and vibrant, with user groups spread out all over the world. Although VFP9 is the last installment we’ll get from Microsoft, I’m happy to see and re-report that a new group has been created: Iraqi Visual FoxPro Programmers Group (Iraqi.vfppg)

You can read the original announcement here on Foxite.

Founder Ammar Hadi writes:

It was my dream to build this group since about more than 4 years ago, and at that time I send an e-mail to one of the Iraqi Foxers who is a member in foxite too (we never met) and told him that I will work on creating a foxpro group in Iraq. He show his readiness to help me on that. But at that time, I was totally engaged in my Medical study to get the certificate of Neurosurgical Profession. Now it is about a year and a half since I finished my study and training and got the certificate to work as a professional neurosurgeon (with very good marks ;-D ….. ). Now I can say that I got some more time to make my dream come true.

I wish Ammar the very best!

Tags: , , ,

1 Comment

Naming Conventions and the Underscore

Jun 15

Posted by Tod McKenna in Software Development

I’ve seen and worked with a lot of naming conventions. When I start a new development project, I always — without exception — document how I intend to name the new items I create, whether they are physical objects such as tables and fields in a database, or names used in code for objects, variables, and the like. This document is shared with the team, adjustments are made where necessary, and adopted as standard for the project or group of projects.

The important thing in this effort is consistency, and not the technique we adopt. If all single-part surrogate key fields are to end in “Key”, and all single-part varchar business key fields are to end in “ID”, then the rule must be obeyed by all developers and DBAs.

A major point of contention that seems to crop up every single time naming conventions are discussed is how and when to use an underscore. Should the field be “CustomerID” or “Customer_ID”? “ProductKey” or “Product_Key”? “SSNumber” or “SS_Number”?

CamelCase

boy and camel 150x150 Naming Conventions and the UnderscoreI have a general rule: If the container (database, property window, etc) can remember case, then only use an underscore when combining an acronym with additional information, when that information comes after the acronym. This is because you can use “CamelCase“, which I find more readable.

Here are a few examples, showing how I would nornally handle underscores when case is remembered:

  • CustomerID and not Customer_ID
  • ProductKey and not Product_Key
  • PhoneNumber and not Phone_Number
  • SS_Number and not SSNumber
  • ISO_Date and not ISODate
  • WP_Theme and not WPTheme
  • ValueEPS and not Value_EPS

And while I’m at it, avoid redundancy: VIN (Vehicle Identification Number) is not VIN_Number; GICS (Global Industry Classification Standard) is not GICS_Classification; and DG (Disease Group) is not DG_Group!

No Case Support?

If the container does not remember case, then always use an underscore to separate the parts of a name. Examples:

  • customer_id and not customerid
  • product_key and not productKey
  • phone_number and not phonenumber
  • ss_number and not ssnumber
  • iso_date and not isodate
  • wp_theme and not wptheme
  • value_eps and not valueeps

If you can be strict about naming, project members and future generations will have an easier time understanding your code, objects, and documentation. I find that the underscore — even within a good naming system — is often used inconsistently. I also find that the underscore can make a major visual impact if used correctly. So it pays to pay special attention to ASCII character 95!

As an aside, naming conventions also play an important role in data warehouse conformity. So while it is important to have good standards, you will also need good governance to be sure that your names are meaningful and consistent.

Tags: , , , ,

6 Comments

Innovation Interrupted?

Jun 5

Posted by Tod McKenna in Business & IT Issues

Michael Mandel writes in his June 3rd cover story for Business Week that “During the past decade, innovation has stumbled. And that may help explain America’s economic woes”. From Facebook to flat-screen televisions, we’ve been lead to believe that we’ve entered into a new age of innovation unmatched by the past.

But like Mandel, I disagree. I think we’re spinning our collective wheels. Biotech, alternative energy, health care, and in almost every other industry, has produced little impact on the innovation front. While we’re all still enamored with the Internet, social networking, and cloud computing, old and inefficient systems in other key industries have remained in stubborn place. The US government owns 60% of GM as a result. This is one example in a sea of many sad cases.

Then there is the problem of rebranding. While I believe that corporate America has had opportunities to make real impact, they’ve taken the straighter path to profits by rebranding old models and technology. Cereal, automobiles, and financial products come to mind.

Perhaps motivation and risk tolerance have taken a hit. The dot-com burst, 9/11, Enron, and the housing crisis are all black-swan type of events that have the ability to change the way people think and do business.

  • After the dot-com bubble burst, investors steered clear of companies that had no product, no customer base, and no real ROI plan (ok, this is a good thing). Where would the speculators place their efforts next? Housing?

  • After 9/11, US foreign policy became a sticking point for many foreign investors, who perhaps don’t like to be told “us or them”.

  • Enron led to Sarbanes-Oxley, an innovation choker that had (and has) the potential to punish organizations for taking R&D risks. But Enron and other failures of its time exposed some serious flaws in how US companies are allowed to run their finance departments.

  • And while people were making money in the late 90s and in the early part of this decade, banks were lending to anything with a heartbeat. The Fed was a major facilitator, keeping interest rates too low. The resulting foreclosure rate today is unbelievable (how many on average per minute?). Investors — including banks — will be far tighter with credit, stifling innovation even more.

Moving Forward

I would like to see the US — and other nations — create an Office of Innovation. I would like to see companies (private and public) free to explore new R&D projects. I would like to see universities continue to get large grants for scientific study and research. And I would like to see more kids enter the fields of science and mathematics.

I would also like to be alive when quantum computing becomes reality.

Things are generally slower in mainland Europe (as I can attest), so the EU has to double their efforts on the innovation front. While the European Commission has a good innovation policy, and has recognized that the EU has great potential, there is still a lot of ground to cover.

I hope to talk more about this in the coming months.

Tags: , , , ,

No Comments