Designing a data staging area for multi-source data warehouse integration

Data Warehousing Text

• Chapters 7,9, 13

Exercise #3-pg 161 Chapter 7 -write

As a senior analyst responsible for data staging, you are responsible for the design of the data staging area. If your data warehouse gets input from several legacy systems on multiple platforms, and also regular feeds from two external sources, how will you organize your data staging area? Describe the data repositories you will have for data staging.

Since the data staging area is a critical part of the functionality of the entire system, it is important to take into account the needs of the system from the staging area forward as well as the limitations and performance characteristics. Also, since the integration of data is the centerpiece of the system and the final product of the entire system, it is important to successfully integrate and assemble data in a way that makes sense to the user. Since more and more staging areas, which are responsible for the "assembly" of information coming from the legacy systems, are becoming relational databases, which in fact means that the data is stored in these places for longer than it had been before when the staging areas were mostly made up of flat files in formats which were readily accessible and usable to the system further downstream. A data warehouse or staging area today would likely consist of this more modern style of storage. However, since these files are stored for longer periods of time, the overhead within the storage system also increases to a point where index creation and data migration from the source systems begins to become a factor in the speed and efficiency of the entire system, not just the staging area itself. In integrated staging area would need to take into account the needs of the system, and in doing so, align itself with either a flat file storage format or a more relational format where data is stored for longer periods of time.

An efficient data staging and intermediary storage area also includes multiple data marts, all configured for best operation relative to the operating system or context of the information being collected and the downstream requirements of this data. As far as repositories go, this portion of the architecture could vary depending on a few variables that the user would need to evaluate prior to setting up the system's overall architecture. These variables include system security, data archiving, refresh considerations, load increments, and backup and recovery capabilities. Each system, depending on how it is designed and implemented, varies in these aspects and the considerations of which of these aspects takes precedence over others would have to be made by the user or customer. The staging area would be responsible for many functions within the system, and the design would also have to take into account the consolidation of datasets and the creation of flat files for loading through DBMS utilities.

An efficient repository not only effectively organizes and stores data, but it also delivers information effectively and efficiently. This means that the information delivery systems' strength and robustness comes as a direct result of the data warehouse architecture. The flexibility of the system is also related to this architecture, but depends where data is summarized and stored as multidimensional cubes of information. The delivery portion of this repository architecture includes OLAP, data mining, and the report/query system where temporary result sets live and where standard reporting data stores make up the primary functionality of this key architectural component. The later is also a function of the architecture of independent data marts, storing information in a secondary "staging area" to be delivery by the information delivery system(s).

The data staging area then should be organized to help keep the data coming in from the two sources free from pollution while adequately and accurately storing it on a system that is compatible with the different legacy systems. This task is easier said than done and involves a survey of the older legacy systems to identify points where data could become polluted or bottlenecked. As far as an administrator is concerned, having accurate and adequate data storage is also dependent on the architecture of the system in that adequate data marts and delivery systems must be implemented or else the entire system could be slowed down or rendered useless for the customer (end-user).

Exercise #2-pg. 337 Chapter 13 - write 2 pages

Assume that you are the data quality expert on the data warehouse project team for a large financial institution with many legacy systems dating back to the 1970's. Review the types of data quality problems you are likely to have and make suggestions on how to deal with those.

Since poor data quality within a system often results in poor business decisions being made from this data, it is very important that each administrator or system architect look at each customer or end-user differently, in their own unique light. Since each end-user is different, and the needs of the customer often stem from the warehouse's ability to accurately store quality information, a system that dates back to the 1970's will likely have quite a few issues relative to functionality and compatibility with modern data warehousing and information delivery systems. A data warehouse will become obsolete if customers and end-users cannot trust that the data being stored is not "dirty." It is therefore important to start with the assumption that the data stored within a warehouse that leads back to legacy systems from the 1970's is corrupt.

It is also necessary, in these early stages, to ascertain the level of data corruption and allow enough time for engineers, administrators, and employees to being to both understand the scope of the potential corruption and successfully deal with it. The plan for correction the polluted data must be comprehensive enough to take the end-user's requirements as well as the entire system's capabilities and limitations into account. This plan should also take a few qualities of an effective warehouse into account, assigning value to each of these qualities: accuracy, domain integrity, data type, consistency, redundancy, completeness, duplication, conformance to business rules, structural definiteness, data anomaly, clarity, timeliness, usefulness, and adherence to data integrity rules.

Since there are many sources of data pollution, it is important to identify the sources and rectify the problems presented by these sources of pollution. Most commonly, the sources are related to system upgrades and compatibility, data aging, and heterogeneous system integration, to name a few. Once these sources are identified, it is just as important to put together a set of data controls or quality controls to keep the data from being polluted in the future. This can be a function of deciding which data to cleanse and where and how to cleanse it. These factors will help decide the future structure of data and the extent to which quality controls are implemented within that system. The warehouse administrator, whether responsible for the pollution or not, is often blamed for the pollution or the system's inability to properly function, and it is important that the administrator understands his or her role as intermediary between the customer (end-user) and the data storage and delivery systems themselves.

This means that the administrator needs to establish early on the importance of clean, quality data, and stick to this policy or effort throughout the performance of job duties. This could include adopting an MDM approach that will require certain specifications of data entry and storage and a protocol for upgrading the legacy systems from such an old architecture, as new technologies and system requirements emerge. Such a system, while sometimes costly to initially adopt due the sheer scope of the data cleansing process, is a time and money saving implementation in the future for many administrators and end-users. Once the data is cleaned, and the process for new data input is cleansed and established, the new data entering the system and supply chains should be clean from the beginning. The costs of keeping the data clean and error free will lower as the MDM's implementation fades further and further into the past.