Tod means Fox

Supporting decisions through sound data management

Posts Tagged Quality

Data Warehousing ETL Checklist

Aug 27

Posted by Tod McKenna in Data Management

IT Business Edge writer Michael Stevens recently published a document called the “Data Warehousing ETL Checklist” (link to article with document). To get the file you’ll need to register with ITBusinessEdge (if you don’t already have an account).

The document isn’t a development checklist, but rather “a high-level checklist of the important topics”. Stevens has made a good effort to distill the various data integration topics and issues you’ll need to consider before you start your project.

Highlights and feedback

Mind scope and do not “try to boil the ocean”. Good advice. But I disagree with Stevens on how to approach one aspect of the scoping issue. He suggests that “in some cases, it may be better for the business as a whole to leave certain data sources untouched”. As I wrote here, I don’t think that scoping to the data source is a great approach. But you can decide.

I would also add that instead of looking at “Target System Content”, you should focus specifically on Business Processes — and this is regardless of the type of system your integrating into (MDM, DW, OLAP, etc.).

Data Profiling is a very important topic, and I’m ecstatic to see it on the list. But if it were up to me, I would have made it second or third. Almost every single failed or over budget ETL project can be linked to data source woes (quality, data availability, inadequate documentation, etc.). Most of these woes can be relieved by conducting good data profiles — a process that can begin during the initial requirement gathering phase of the lifecycle.

The data profile will reveal which source systems are useful. There may be redundancy, data quality, and data access issues compelling you to exclude a particular source. This isn’t a scoping issue, though. Go back to the project sponsor and to the board; report your findings. You may have other issues in your organization to resolve first.

In addition to Data profiling, I’m very happy to see that Naming Conventions made the list. Tied in closely with naming is the concept of conformity. I’ve written about this before, so please check here. In general, a label and its data must conform and be consistent throughout the entire system. And as Stevens points out, they must be agreed upon by the users.

For metadata, I also consider a third type: Process Metadata. This is technical data about all the processes (ETL, jobs, automated processes, reports, etc.). This data is then utilized directly by business users and administrators to monitor and audit the data warehouse. Is my report ready? consult the process metadata. Did that job finish? consult the process metadata.

Lastly, don’t forget to think about auditing, compliance, and lineage. If you’re making decisions based on the data retrieved from your data warehouse, you’ll need ways to trace data lineage. You’ll need to know when data was loaded, what version of the ETL system and business logic was used, and even why a particular data item might have been changed in transit.

Conclusion

This is a good high level checklist that you can use before you begin to dig deeper. It gives you a snapshot of some of the high-level decisions you’ll need to make before you get started with your next data warehousing project.

Perhaps Stevens will incorporate some of my feedback into a version 2!

Tags: , , , , , , ,

No Comments

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 Management

  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

MDM is a Capability, Not a Product

Jun 4

Posted by Tod McKenna in Business & IT Issues

I had bookmarked, and finally just read, an article by Loraine Lawson of IT Business Edge titled “Consultant: Master Data Management Can Pay off During M&As” which referred to this blog post from Evan Levy, “MDM and M&A“.

MDM is an interesting topic, and one that has a lot of relevance in my work environment. M&As are also interesting and can have a huge impact on a great many people. But while reading these articles, I was reminded of an important MDM axiom.

Even writes:

MDM provides a company the capability to link the data content from disparate systems within and across companies.

Remember that MDM is a capability and not a technology. You cannot buy MDM, but you can build a MDM strategy. This strategy will likely cross several technologies and platforms. It may consist of data warehousing elements, SOA, and SaaS applications. It will surely consist of certain disciplines such as data governance, data quality management, and data integration.

Vendors will continue to push their MDM solutions, but be careful not to trap yourself into thinking that the job is done once you’ve installed. Vendors can wrap most technologies necessary for MDM into a single package, but they cannot provide you with a strategy or the personnel to make it work for your organization.

MDM is a capability you create, and not a product you can buy.

Tags: , , , ,

2 Comments

Maintaining a DW/BI Environment

Mar 24

Posted by Tod McKenna in Data Management

In posts Version Control and Version Migration I glossed over one of the more complex and challenging aspects of data warehousing: Once the DW/BI environment is in a production environment, how do you maintain and update it?

Some Thoughts

While I do not have a blueprint for you, I do have a few thoughts on the subject.

First, you must consider the Data Warehouse as a living and breathing organism. Not only will it be growing in size as your carefully constructed ETL packages churn, but it will also likely be growing in scope and importance (if not, then you may have to re-think your DW/BI marketing approach and/or find a new sponsor).

Second, you have to realize from the beginning that deploying a data warehouse is both an iterative and incremental process. Iterative in that you will build and rebuild as you get deeper into the project; incremental in that different parts of the warehouse will be constructed and delivered at varying rates. This is in direct contrast to the normal waterfall approach to releasing software applications and systems. It is not realistic, practical, or advised to attempt to deliver a DW/BI project in one shot. You may as well use your bullet for something else!

Passing the Baton

Pass the BatonYour DW/BI team must plan up front for the often complex handoffs between development and maintenance amid the ever-turning wheel of the DW Lifecycle (detailed here).

Those handoffs are critical. If you’re in a large organization (like me now), then you will be literally handing off the maintenance of the project to a complete different group. This group will need documentation and escalation procedures to monitor and respond to various exceptions that may occur in the production environment. While you can’t plan for every exception, you should prepare enough documentation so that all the basics are covered (for example, what should the maintenance team do if a SQL Agent Job reports a failure?). While some things will fall back on the Dev team, many of the maintenance tasks can be handled by a well trained support group.

If you’re in a small organization (like I used to be in), then you are the maintenance team. You have the added responsibility of maintaining a high-priority production system while you continue to build new pieces for the next release. This can be exhausting and stressful so be sure you automate as much of the maintenance part as you can! And be sure to allocate enough “business as usual” time in your schedule each week. Make sure your manager and/or project leader is cognizant of this added pressure and responsibility.

Back to the Start

Once you’ve passed the baton, you can go back to the beginning of the DW Lifecycle and start over. You may improve some processes (iterative development) while adding on new functionality (incremental development).

DW LifecycleFor example, in your team’s first run through the lifecycle, you implemented a single business process dimensional model (retail sales) that allowed you to also produce a series of reports for your sales department. During this first run through, you accomplished quite a bit: You installed and learned how to use the toolsets, you built the dimensional model and ETL architecture, you designed a rudimentary web portal, you created a few useful reports, and you released all the pieces to your users. So far so good.

Now, this first version is out and the business users have some feedback. An issue log and wish list is compiled (perhaps a few new reports, an updated report template, and some user-defined filters for the portal). In addition, you want to also include a new business process, inventory, so you can expand the usage of the date warehouse in your business.

You and your team get back to work. The modeler begins to construct the new dimensional model while the ETL team works on the integration packages. This process takes the longest, and may be interrupted occasionally by the production team reporting some data quality issues or some failed packages.

In a separate thread, your BI application developers are busy with implementing many of the suggestions brought forward by the sales department. When finished, they release version 1.1 of the portal. Immediately, they begin working on some new inventory reports.

This scenario repeats with each cycle for each thread lasting anywhere from 3 weeks to 3 months.

Rosy Picture?

The above scenario really paints a rosy picture of the process. In reality, it doesn’t always work smoothly. But it does work if managed correctly. And it can be quite exciting when each iteration completes and the handover goes according to plan.

I would like to talk more about this subject in future posts. For now I hope my thoughts on the matter have left you with some insight!

 

Tags: , , , , ,

1 Comment

Data Quality (For the sake of the children!)

Mar 15

Posted by Tod McKenna in Data Management

Here is a somewhat humorous example of how data quality can impact more than just your analysis or reporting. This example is good for me in particular because for so many years I have been dealing with municipal data quality, tax assessment, and billing. It alarms me how little data quality is discussed, and alarms me more when business talks about data quality because they think they need to, yet they don’t fully understand what it takes to implement and maintain good data stewardship. Anyway, perhaps a video like this can at least raise some eyebrows:

Tags: , ,

No Comments

ETL Subsystem 29: Data Lineage and Dependency

Feb 28

Posted by Tod McKenna in Data Management

This article is part of a series discussing the Kimball Group’s “34 Subsystems of ETL“. The Subsystems are a group of “Best Practices” for delivering a BI/DW solution. In my articles, I discuss how each Subsystem can be implemented in SSIS or hand coded in Visual FoxPro.

Data Lineage and Data Dependency are closely related in that they tell you how data elements were born, how they got to their final resting places (lineage), and where those resting places are (dependency).

Data LineageFor example, you may have a report that shows the total number of sales in a given month for a particular client. The Sales metric, used to generate the total sales for a customer for a given month, also exists in 5 other reports, a few exports, your dashboard and scorecard application, a research data mart, and in 3 fact tables. Many of your data warehousing applications are therefore dependent on the sales metric. You also know that this metric is staged in on your integration server (in a 3NF relational model), and that it is derived from your front-office sales database. Not only that, because you practice good logging, version control and release management, you also happen to know exactly when the front office system gave you the sales data, when it was staged, and what version of what package processed it.

Compliance officer: Take that!

Having complete lineage and dependency trails is not an easy task, and must be planned as part of your metadata strategy from the start. If you, like me, have to operate in a heavily regulated environment than you must pay special attention to lineage and dependency. Even if you’re not bombarded with regulation and auditing, there are great benefits to building lineage and dependency metadata into your data warehouse. Consider:

  • Being able to find out quickly where and why any element changed type, value, or size (why are my sales figures rounded to two significant digits?)
  • Being able to perform a quick impact analysis if any change in type or size of an attribute or metric in the data warehouse will affect downstream reports and applications
  • Being able to focus data quality issues back to the source by showing how and why various data elements got clogged in the integration system (I often build in strict rules when dealing with potential dirty data. In some cases I outright reject the row and report back to the source the reason.)
  • Being able to accurately plan maintenance and reporting enhancements

The list can go on. But I think that you get the idea. If you want to be a good data governor, then you’ll need to pay close attention to this subsystem. If you are a good data governor, then you already have!

There and Back Again

So how can you get there? Largely, this is a documentation issue. While databases and tools can provide some metadata to help determine dependencies, transformations, and the like, it is often more useful to pull out these details into some form of central repository that you can query and write reports on. I’ll talk more about metadata in a future posting (subsystem 34, to be specific), and with that, I’ll discuss some great ways to automate this and other tedious documentation tasks. Here are a few other things you can do:

  • Keep your Bus Matrix documentation up to date. The Bus Matrix offers a great overview of your data warehouse and can often shed some light on dependency and lineage issues.
  • Publish your Logical Data Mappings (after you’ve polished it up) to your data governors, making the LDM a centerpiece for your lineage documentation.
  • Get to know your database’s dependency analysis capability, and install a third-party tool if the native capability is insufficient. For SQL Server, you can have a look at a company like ApexSQL for some inspiration.
  • Be strict with your versioning. This will allow you to tie data elements to the processes that touched them. In a compliance environment, it can be critical to know that build 345 — and not build 346 — processed the sales figures (in a future post, I’ll explain how I do this in my environment).

 

From here

While there isn’t anything specific in SSIS or VFP to make lineage and dependency analysis possible, you should focus on logging, process metadata, clear documentation, and good old due diligence.

My next subsystem topic involves problem escalation. This is a touchy subject where I work because it takes so long for the “system” to kick in (SLA times vary and every project has a priority score, that often means critical issues for you are blips on the radar for the support staff). I’ve got some thoughts and ideas on this, so I hope you don’t mind me sharing — and venting!

And lastly, I’ve been unable to commit to blogging over the past 3 months. First the holidays were a distraction, but in the start of January I discovered that my group would be moving from Brussels, Belgium to The Hague, Netherlands. This has been a major distraction and source of stress for my family and I. Do we make the move? Do we stick around in Brussels and look for another opportunity in a down market? I’ll write more about this in my “personal” category as more details come to pass. But that’s my excuse for the slow-going of late!

Tags: , , , , , , , , ,

No Comments