Tod means Fox

Supporting decisions through sound data management

Archive for category Data Architecture

Maintaining a DW/BI Environment

Mar 24

Posted by Tod McKenna in Data Architecture

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

track baton pic.thumbnail Maintaining a DW/BI Environment Your 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).

data flowchart.thumbnail Maintaining a DW/BI Environment For 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 Architecture

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 Architecture

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).

lineage.thumbnail ETL Subsystem 29: Data Lineage and DependencyFor 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

ETL Subsystem 28: Sorting

Jan 31

Posted by Tod McKenna in Data Architecture

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.

Before you can aggregate, summarize, look up, or merge data, you will need it ordered in some way. Sorting, which changes the physical order of the underlying data, is seldom necessary. Instead, indexing, which changes the logical order of the underlying data is often all you need. The Kimball Group seems to use Sort interchangeably with Order, so I won’t deviate in this post. This may be because some databases vendors do so as well.

If you’re loading data from a mainframe, then the utility programs DFSORT or SORT will be of some use. Other utilities exist depending on the vendor, but these are the main ones. The idea is to sort the raw physical data before it hits the ETL pipeline. Doing so will save processing time. Otherwise, you can simply load raw data into your staging area and then use standard SQL to order your data.

Sometimes sorting is implicit, so be careful. In SQL Server, sorting occurs when you use ORDER BY, GROUP BY, SELECT DISTINCT, and UNION. You should, for example, avoid the SELECT DISTINCT clause whenever possible, as the additional sorting can hinder performance. Many programmers and (ehem) trained DBAs automatically add the distinct clause to their queries. Be certain that the distinct clause is necessary; otherwise performance will take a hit.

Beware that sometimes too much sorting can be a problem. Here is a general rule: Only sort when you need to! Sorting is an expensive operation and I’ve seen it overused and abused.

Clusters and Heaps

A clustered index determines the physical order of data in a table. That is, data is inserted into the table in order. Pages are linked for faster sequential access. This is in contrast to a heap table, in which data is added to the end of the table and the data pages are not linked.

Clustered indexes can make a big impact in the staging area. Consider a surrogate key lookup dimension that contains 4 columns: surrogate key, business key, valid_from, and valid_to. A clustered index on business key, valid_from, and valid_to will return the surrogate key upon lookup faster than if your lookup dimension was a heap. This is mainly because “queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=" are well suited for the clustered index structure.

This works well during surrogate key lookups where we typically want the surrogate key of some business key on a particular date. Of course INSERTs and UPDATEs take longer on clustered tables because the physical structure of the table needs to be maintained. So be sure to balance speed with need. In my experience: Maintaining a clustered lookup dimension is almost always well worth the effort, especially when the original dimension is big and wide.

Best Practices

Here are some best practices that might help you keep your sorting operations under control:

  • Create one tempdb data file for each physical processor on the machine (more)
  • Put the tempdb files on their own drive (more)
  • Make sure your data types are appropriate for the column (storing numbers in a varchar field is not very efficient)
  • Keep the columns you sort on as narrow as possible (here is where good data profiling will help)
  • Integer sorting is much faster than character sorting, so consider using integers instead of character data whenever possible (more)

 

SQL Server 2005 Integration Services (SSIS)

As I have stated, sorting is often overused. The Sort component that ships with SSIS is good for small data sets that fit entirely into memory. Performance will degrade as soon as there is overflow. One way around the problem:

  1. In the Source Adapter, set the data access mode to “The results of an SQL statement”
  2. Use and ORDER BY clause in the SQL Statement
  3. Open the “Advanced Editor” of the Source component
  4. On the Input and Output Properties tab, click the Source Output node and set the property IsSorted to True.
  5. Under Output Columns, set the SortKey for each column involved in the index (start at 1, use a negative number to denote a descending index order)

 

Hand Coding with Visual FoxPro (VFP9)

Physically sorting a table in FoxPro is not necessary. The physical order of the table is irrelevant because of how VFP uses its indexes. You can test this easily by creating a table with a few million rows containing a randomly generated integer value and another column with some other data (perhaps system time or something). Using the SORT command (or SELECT INTO with an ORDER BY) create a second table in the order of the integer column. Without creating an index, attempt to retrieve a row from the table based on some integer value. Now, create an index on the column in both tables. You should notice no performance differences.

So while you’ll need to understand indexes and rushmore in VFP, you won’t need to bother with physical sorts.

From Here

In the next post, I’ll discuss lineage and dependency analysis. If you work in the financial sector (like me) then this topic will be of critical interest to you. If you don’t, I suspect you still care about lineage and dependencies!

Tags: , , , ,

2 Comments

ETL Subsystem 27: Workflow Monitoring

Dec 26

Posted by Tod McKenna in Data Architecture

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.

cpu load monitoring.thumbnail ETL Subsystem 27: Workflow MonitoringYou should keep a careful eye on the performance of your ETL system. It isn’t enough to simply schedule jobs and watch them run and run and run. You have got to put some effort into improving your processes so that the entire data warehousing operation works to its optimal level.

In order to discover areas of improvement, you will need to monitor as many processes as you can.

That said, I’ll skip a discussion on network, server, and operating system level performance monitoring (which includes monitoring disk access, memory usage, processor time, etc.). I am not an expert in this area and I simply do not know all the best practices. If you have fast, up-to-date servers, and you do a good job of developing ETL processes that mind their business and play nice, then there is likely not much you can uncover at this level anyway. You’ll need an expert. However, if you have some strange problems (like a load process that seems to take longer and longer over time; a package locks up mid-run; if you can’t seem to get anything else to run on the server while you load data), then exploring an application such as Performance Monitor in Windows is a good place to start.

Here are some good resources (which I had bookmarked):

 

What the ETL System Should Do

When you run a job, package, or script, you should log starting and ending times. In addition to logging these timestamps, include other details such as machine name, logged in user, process name, package versioning information, number of rows processed, number of errors, and any other useful bits of information that can be used to discover areas in which you can improve your ETL system. All of this data should be stored in your database as process metadata (more about process metadata here).

Combining this process metadata with other metrics on the server (CPU usage, disk space, memory allocation, etc.) should give you enough information to identify problem areas. The Kimball Group suggests that all this data be stored as process metadata. While I agree in principle, getting all this “extra” data into the database seems like overkill. I prefer to write to logs when I need to, and then examine the logs. This might be what the Kimball Group is actually suggesting, but it isn’t clear to me. Ideally, this logging can be switched on and off so that it does not interfere with production execution.

So what can you do with all this process metadata? You can use it to improve performance! From the Data Warehouse LifeCycle Toolkit, the following list (a bit paraphrased) will give you some things to look for:

  • Poorly indexed queries
  • SQL syntax causing wrong optimizer choice
  • Insufficient RAM
  • Sorting
  • Slow transforms
  • Excessive I/O
  • Unnecessary writes followed by reads
  • Bad aggregate management
  • CDC applied too late
  • Oportunities to run processes in parallel
  • Unnecessary transaction logging
  • Network traffic and file transfer issues

 

Planning for Performance

Now that you can see there are benefits to storing process metadata as part of an overall workflow monitoring capability in the data warehouse, you should also see that doing so is an ongoing effort. Workflow monitoring is not something you do once or twice, and then get busy on something else. I do a lot of planning along side my developing and I am very careful to allot adequate resources to this subsystem. Many managers don’t. If you are managing a group, or being managed by someone, and workflow monitoring is not on the plan, then do the necessary to get it there!

If you would like to know more about how I estimate time for this task, send me a message and I’ll be happy to discuss it with you.

SSIS and VFP

The tool you use makes little difference in this subsystem. All you need is access to the various variables (e.g. machine name, package name) and an ability to write them to the database. ETL Subsystem 6: Auditing is tied very closely to this subsystem. For details on how to implement an auditing subsystem (and hence have the ability to monitor your ETL) check out that post.

From Here

Remember that your job is not finished when your packages run without error. As well, it can be argued that poor performance should be treated like any other bug. Every effort should be made to improve the overall workflow and processing in order to squeeze every last second out of the system. You cannot do this unless you set aside ongoing resources for the life of the data warehouse system.

In my next post, I’ll talk a little about sorting, ETL Subsystem 28. We’re almost at the end of the line.

 

Tags: , , , , ,

1 Comment

ETL Subsystem 26: Version Migration

Nov 20

Posted by Tod McKenna in Data Architecture

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.

migrating birds ETL Subsystem 26: Version MigrationDepending on the lifecycle in place at your business, you will likely need to move your data warehousing project from the development environment into a production environment. I am currently using an alpha / beta / gamma system (with mixed emotions), where alpha is our development environment, beta is our integration, and gamma is production. I have also worked with systems that combined alpha and beta and had a special user acceptance environment instead.

Whatever you do, you’ll need to consider migrating from one environment to another.

Some things to consider:

  • Environments: How similar are your environments (both hardware and software)? Are there major differences between the development servers and the production servers? Ideally, all environments should resemble each other (integration, UAT, and production environments should really be identical). You don’t want to develop on a 64-bit Windows Server 2008 and deploy to a 32-bit Windows Server 2003 machine!
  • Security: Have you considered the different security and access requirements that exist between development and production environments? Will you, as the data warehousing team, have access to manage server jobs, file transfers, and file shares?
  • Version Control: Have you set up your version control in such a way that if an installation fails, you can revert back to a previous version? This is tricky, so be careful to set aside enough time for planning. You also need to keep in mind potential compliance issues, as it may be necessary to “pull out” an older version to see how data might have been manipulated differently than the current installation.
  • Resources: Who are the people in charge of the migration? Are they available when you need them?
  • Documentation: Does your organization require installation documents, verification plans, quality assurance sign-offs, user acceptance sign-offs, and back-out plans?
  • Protocol: What type of red tape must you cut through to get your data warehouse online? Who needs to sign-off on the deployment? What forms must be filled out? Is there an official change request procedure? ITIL and other systems can be good in theory but difficult to work with in practice. Take the time to learn what you need to do to get your system online.
  • Packaging: How will you package your software and database scripts? Will you use an installation program or will you have someone manually copy files? Will scripts be run individually? Will you use a deployment tool such as MSBuild?
  • Verification: Have you thought about how you can test the system in a way that flexes all new and updated features, while providing adequate regression to be sure you didn’t break something that was once working? Have you done a proper impact analysis on all new or changed components?
  • Data: Will you be migrating data changes as well? How will you get these data changes from one environment to the next? Has everything been scripted and tested thoroughly?

 

SQL Server 2005 Integration Services (SSIS)

SSIS makes deployment of packages from one environment to another relatively easy. You merely create a single package and supply different configurations for each environment. I am currently using XML configurations, but you might do better with database configurations, environment variables, or using a table in SQL Server. The basic premise is simple: Create variables in your package for things like file locations and connection strings. Using the Package Configurations dialog (SSIS -> Package Configurations…), enable configurations, click Add and then follow the Wizard. Check the SQL Server Development Center for details.

Remember that you can always set a configurable item (e.g. variables, connections, tasks) from the command line or in the SQL Agent Job. For the Agent Jobs, open the Job Step properties and from the General page, select “Set values”. When entering your values to configure, use the following syntax:

\Package\container.property

Example:

\Package.Variable[ServerName].value

For more insight on setting up jobs you can see my post “SSIS Packages and setting up Jobs“.

SSIS comes with a Deployment Utility that will bundle all the files necessary for deployment of the package into a single folder. Copy this folder to the destination server and run the Package Installation Wizard to install your packages. While this is a good idea, and does remove the possibility of human error during the installation process, I do not use the Deployment Utility. Call me old-school, but I prefer to do this bundling and installing myself through the dtexec command line utility — I like to see what is going on and it gives me an opportunity to catch surprises before they become trouble.

Hand Coding with Visual FoxPro (VFP9)

You can also use FoxPro to mimic the same kind of functionality you get in SSIS using package configurations. This is no different than using an ini file, a “setup” table, registry entries, etc. I would warn against using free tables if your deployment team does not have access to FoxPro. You would be better served using XML or some other plain text formatted file.

You can also automate much of your builds by utilizing VFPBuild. Find the related CodePlex page here.

From Here

As a developer, you are responsible for ensuring that the right version of your software makes it through the migration path with little or no hassle. It can be challenging enough dealing with hardware and software compatibility issues. Do your best to know the routine, package appropriately, and plan ahead!

Next post, I’ll talk about Work Flow Monitoring…

 

Tags: , , , ,

8 Comments

ETL Subsystem 25: Version Control

Nov 10

Posted by Tod McKenna in Data Architecture

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.

When I sat down at my desk and began putting together my notes on this subsystem a couple of weeks ago, I was reminded how important version control can really be. In fact, as I was looking through a few articles and books on the matter, I realized that I don’t do a great job of version control management. And unfortunately for data-warehoused businesses around the planet, I’m not alone.

What I currently do

I use version control software for all packages, database scripts, and configurations. Although not the greatest of the bunch, MS Visual SourceSafe does the job (we’ll be switching to Serena Dimensions soon, though). From reading various articles and from a few first-hand experiences I can tell you that many teams don’t even do this much. Ouch.

I tag each package, job, and source with a version number and relate these versions to the database and BI application versions. I will also label the source code in VSS to match. For example: version 4.0 of the database aligns with version 3.1 of the data integration systems which supports version 1.7 of the BI Portal. I currently coordinate this using a spreadsheet in Excel, with high hopes to get this into the database in the form of metadata that can be queried and used for planning and deployment purposes.

I should mention that versioning is particularly important in compliance-laden environments. If it is important to show an auditor how data got from point A to point B, then you’ll need to dig out the exact versions of the software that loaded the data at the time. This is why including version numbers in your logging and auditing routines is very important. I wrote earlier this year on this topic in my article “Auditing Your Warehouse for Sarbanes-Oxley Compliance“. Take a look if you want more information and to see how I implemented this using SSIS.

At this time, I could segue into impact analysis; I’ll leave that for another post.

In addition to source control software and versioning, I use an LDM (Logical Data Mapping) that is stored as “business” metadata in the database. This metadata explains all source-to-target mappings and is sensitive to start and end dates. For example, if a vendor adds a column to one of our input files, I add the new column to the LDM and set the begin/end dates appropriately so that the historical mappings are left in tact. In this scenario, I can load an old file or a new file with little effort. When time permits, I will build logic into my SSIS packages so that, based on the date of the file being processed, the appropriate child package will be loaded.

What I don’t do yet

So far so good, but this is where I fall short. I have source control. I use major and minor versions and connect them to all other elements in the data warehouse environment. I even have a time-sensitive, table-driven LDM system. But I am lacking in the “control” area which has me worried. I have a lot of to-do items and half implementations.

What I need is a (fully automated, metadata-driven) system to coordinate different releases on different schedules and allow the packages to work (or not work) under certain database versions and environments. It would be nice to have some indicator in the ETL system that tell me if a certain version of a certain input file will load with a certain version of the database. To complicate matters more, much of what I do is based on certain mathematical models that sometimes change (yep, they have versions too). The question becomes, what version of what package works with models X, Y, Z for what database. Normally (99% of the time), everything is simple: the latest version of the package works for the latest version of each model on the current version of the data warehouse. That other 1% of the time is what makes my job interesting!

Assuming that you already use source control…

SQL Server 2005 Integration Services (SSIS)

Some elements of version control are simple using SSIS. For starters, you are able to set the VersionMajor and VersionMinor properties to your ETL version. When you make a major change to the package — so that it will no longer work with historical data, for example — then you can also update the package ID (see property “ID” under the Identification group in the Properties window). As a general rule, you should update this ID if the package works fundamentally different than it did before.

When I get around to implementing the system for selecting the correct version of the package that matches the input file version, I’ll be sure to write about it. For now, it’s merely a figment of my imagination and needs some fleshing out.

What I have done, though, is create a custom Task which examines the input source file against the LDM. If a column name or type is out of place, the file is rejected. This prevents loading a file that might have an extra column suddenly or if columns were swapped for some reason. We deal with input data that is (mostly) trustworthy, but sometimes dirty. As a result, this extra checking has become necessary. If anyone is interested, I would be happy to share the code and logic of this custom Task in a future post. Just let me know!

The above is not a scenario that exists in every environment. For me, detecting the file version is very important. For you, it might not be a challenging issue.

I have also linked the package’s ID to the LDM and my logging system so that each package — as represented by its GUID — can relate to one or more versions of my mappings. This allows me to modify packages, but keeps the modifications tied to a particular mapping.

Hand Coding with Visual FoxPro (VFP9)

Good developers will appropriately version their software. This should be part of How to Write Software 101, but is surprising to me how many times I’ve seen poor or non-existent versioning schemes. Hand-coding ETL is also a software development project and deserves the same versioning-attention you would give to any other good software.

In addition to versioning and source control, you should also consider versioning your metadata as I discussed above. I have written about and given presentations on this topic in the past with respect to the Logical Data Mappings. Check the following for more details:

The LDM is not the only metadata to add to your version control, but this should give you a real good start!

From Here

So you have a good version control system in place. Now you need to migrate your current versions from your development environment into production. That’s what Subsystem 26 is all about. And lucky enough, that’s next on my list!

Tags: , , , , , ,

3 Comments