Staging and Highly Coupled Columns

[TimothyR]

This is my first post, so please bare with me.

Synopsis:
For most of our Landing/Staging Tables we have a series of what I call "Highly Coupled" Columns where the first stores a date while the second stores a code which represents how that date was derived within the DW. Due to the ETL which originally updates these columns and subsequent refreshes, analysis has shown that these columns are getting out of sync for 2 to 3 percent of the rows (this percentage various from table to table), thus the Code Column no longer reflects the correct representation of the Date Column.

Questions:
1. Is there an industry term for this kind of coupling?
2. Is there an DW Principle of which I can quote or refer to, to back up my argument that we need to take action to correct this situation and improve our Data Quality.

Many thanks.

[ngalemmo]

What do you mean by "code which represents how that date was derived within the DW"? Why is the DW deriving dates?

[BoxesAndLines]

In normalization terms, this is a violation of third normal form. The pain you are are feeling is why in application databases violating 3NF is a bad idea. Fixing it is a matter of how important the data is to the business.

[TimothyR]

Within our EDW, these crucial dates are utilised for Temporisation. As all rows require a date to provide the temporal context, it maybe required to set it to a default date when the source data is unable to provide a valid date. For example, many sources contain a Business relevant date which is then used to satisfy our "Valid" Time Line. When either a date is not available or the date supplied is an invalid date then a default date is used. The Code column simply indicates whether the date was obtained from the source or a default was used.

[ngalemmo]

1. No, there isn't an industry term I can think of off hand. It is not unusual. You have a flag that goes along with the date.
2. I don't understand what you mean by 'out of sync'. Since the code can be one or the other, how would you know what is 'in sync' and what isn't? I am assuming that the 'default' date could be the system current timestamp, rather than a fixed value. If you are seeing things like nulls for what should be a valid date, it sounds like you need to debug your ETL process.

[TimothyR]

We are in the process of debugging the ETL Process and rectifying the code. This still leaves the issue of the lack of Data Accuracy in the physical Tables which hold this data. When I say "out of sync" what I mean is that in the case where the Business Date is used, the flag would indicate the Default Date was used and vice-versa. The flag doesn't reflect the "True" situation and we have a lack of Data Accuracy.

In our organisation in relation to our EDW, different people have different views of what "Data Quality" is or means. Many people believe that we should be happy with 99% or 98% Data Accuracy within the Warehouse and only then when it impacts the final presentation layer. Therefore, if you only have say 90% Data Accuracy for a particular Data Item within Landing or Staging Table, then as long as the end user reports aren't affected then we can tolerate the 90% Data Accuracy. My philosophy is, if you go to the trouble of physically storing the data for later reference, then you should be concerned about Data Accuracy and Data Quality at all stages within your Warehouse.

What I am seeking, is some principle to back up my argument, to go back and fix it all up.

[ngalemmo]

Data Quality is about correctness, not accuracy. It is addressed in the operational systems, not the data warehouse.

The data warehouse should always be 100% accurate. What this means is, if given a set of source data, it accurately reflects that data in the DW. In other words, the load process doesn't have bugs and does not distort the data it has received.

[TimothyR]

It is this very statement "The data warehouse should always be 100% accurate." which I wish to sell or convince people of. My philosophy is "Do no Harm". I am up against a lot of resistance when attempting to address these very Data Accuracy issues within our EDW. There are many aspects of our EDW where the Source System Data is distorted (or misrepresented) somehow.

I am not necessarily a Warehousing expert. My expertise comes from my many years working in the Management Information Arena. I work with a lot of people who claim to be Warehousing experts (many are contractors/consultants) and they seem to be content with the EDW having 98% accuracy (of the operational system data holdings).

I would be interested in any documented Data Warehousing Principle or Water Paper that I could use to prove my point.

[ngalemmo]

Accurate doesn't mean correct. It means that whatever the data that is being loaded, no information is lost or distorted. It would seem that that is an easily attainable goal.

Accuracy issues, if any, should be derived values, otherwise know as the application of business rules to the data. This rules may change, and may change fairly often. Or they are simply not well defined. This could lead to the appearance of inaccuracy.

The trick is to retain the original raw data with the derived values. You may not expose the raw value to end-users (you could if they are needed), but you retain them so that derived values can be restated should the business definition change.

Latency is also an issue sometimes. If the extracts from the source are performed while other update activity is taking place, the extract may miss the results of update queries running at the same time. If you were to compare the operational data to the stored data warehouse date for the same percieved moment in time, the query will see different values because the missed updates would have completed and be visible to new transactions. This would cause inaccuracy depending on the process. If you are using time based filtering (extract anything update/new since the last extract), the missing updates could be lost if there is no mechanism to recover them the next load.

There are a variety of ways to avoid this. You can use a push architecture where the operational system delivers logs or extracts. If done correctly, it would insure the data warehouse gets an accurate history (This is typical of ERP systems). The other is to lag the extract using a pull architecture. If an extract is started, it would look for transactions that completed some time in the past, like 1 minute ago. The amount of time you need to lag depends on the kind of update activity that is taking place.

[Sponsored content]