Hash Signatures in Data warehousing in detail

Introduction

This document intends to describe the process of using Hash values (also known as Hash Signatures) in Data warehousing in detail.

Slowly Changing Dimensions and Change data Capture

One of the biggest concerns that arise while working on slowly changing dimensions is to figure out an efficient method for capturing changed data. This issue becomes more critical if the source system does not have a timestamp or any kind of flag to indicate the changed data. One of the easiest methods available is using a brute force technique to identify the changed data. But this would result in the increase of time and cost of maintaining the data warehouse dramatically.

One solution for this is creating unique signature for every record that is to be inserted and store in the target table. The signature should be created based on the data in each record; therefore no two rows with a different set of data will have the same signature. Now compare the signature created from the source tables’ records with the signature stored in the target tables’ records. If there is a match then it means the same record has arrived again and we can simply reject it.

We can use either a CRC or a Hash function to generate these signatures. In this book we will discuss the steps to implement Hash signatures in a Data warehouse.

Concept of Hashing

We can implement Hashing in various databases. In our case we will use Oracle DB and Informatica – ETL tool to illustrate the concept.

In order to illustrate the concept we will use the following components in our example.

Source Table

 

 

 

Target Table: TYPE -2 Dimension

 

Get_sign () – Hash Function

Let’s call the user defined Hash function that we use here as get_sign (). This function should be created in the database. The steps to create this function are discussed later in this article. The input to this function will be all the fields from the source concatenated into one long string and its output will be a string which contains the Hash value for the input string.

We will begin with loading one row from the source to the target table. We will use 3 different cases to illustrate the process of Hashing.

Case- I

Consider the case when a new record (as shown below) arrives from the source.

 

 

 

First we need to concatenate all the fields (fields required to check for changed data) in a record into one string. Then pass this concatenated string into the get_sign () function.  The output of the get_sign function contains the Hash value for the record.

E.g. the query to fetch records from the source will look something like this.

Select Student_id, Student_Name, Marks1, Marks2, get_sign (Student_id|| Student_Name|| Marks1|| Marks2) from Student_Marks;

The output of the above query is shown below.

 

 

 

Now we have the Hash value of the record. To make sure that exactly a similar record is not present in the database already we need to compare the above generated hash value with the hash values of the records in the target. Since this is a new record and there are no records in the target table at this point of time, this record will be inserted.

 

 

Case- II

Consider the case when the above record which was inserted into the target is arriving again in the next load.

The same record arrives again as shown below.

 

 

 

 

We generate the Hash value for this record as well using the same procedure as in CASE - I

 

 

 

Now, Compare the Hash value of this record with the hash values of the records in the target. This time there will be a match as the same record was inserted earlier and hence we simply need to reject the record.

Case- III

In this case the same Student_Id arrives with a different set of values in the other fields (different marks).

 

 

 

We generate the Hash value for this record.

 

 

 

 Now when we try to match the Hash value of this record with the other records, there will not be a match. Since it is a TYPE – 2 dimension we insert this record and update the Meta data of the previous record of the same Student_Id.

 

 

 

 

Generating Hash Value using Oracle functions

The OUTLN_EDIT_PKG in oracle has the generate_signature () function.  The generate_signature () function takes one string as input and outputs the corresponding hash signature for the input string.

It is a good practice to wrap the generate_signature () function into a Deterministic user defined function get_sign () (the one which we used in our examples).  A deterministic function is one which produces the same output for a specific input. Hence Oracle can buffer the records for further use.

Also, we may want to replace any NULL values with a string of our choice because the generate_signature () function generates a hash value for NULL as well.

 

Example of a wrapping function:

CREATE OR REPLACE

FUNCTION get_sign (text VARCHAR2)

RETURN RAW

DETERMINISTIC

AS

signature RAW (32);

text2 VARCHAR2 (32767);

BEGIN

text2:=text;

IF text2 IS NULL THEN

text2:='1';

END IF;

sys.outln_edit_pkg.generate_signature (text2, signature);

RETURN signature;

END;

 

Checklist for Data Warehouse Testing

1. Validate schemas of both source and targets of data warehouse

2. Ensure key constraints for targets are in sync with specifications given to you

3. Query on source and targets and try to break transformation logic written in informatica. This is key and important since data sits in target based on transformation logic written and also check for mapplets if any.

4. Try to break/check update rules attached to transformation

5. Check for surrogate keys

6. Write SOL queries for sources and check whether they are meaningful to targets and vice-verse

7. Check for scheduling jobs.

8. Create SQ L queries with different logic's and check for data consistencies for different conditions

“Given a scenario where a single file is parsed into more than say 1000 columns in different tables say more than 10.If the person testing this would need to be able to code some scripts quickly to run a comparison of values between source file and relational staging table columns. Can it be done say by using macros in Excel??"

Here are a couple possible solutions:
 
1) Depending on your query tool, you could write a looping construct to query both the spreadsheet using one connection, and the staging database using another connection. Compare each value upon retrieval.

2) You could replicate the model in above using MS-Access and some VBA.

3) You could use VBScript or other language to accomplish above.

Steps
need to validate that data from our data warehouse is properly loaded to our datamart. Would like to create baseline files using 'db_execute_query' and then 'db_write_records', and then compare the datamart result set with the baseline using file_compare (or a separate diff utility).

Strategies for Testing Data Warehouse Applications

Businesses are increasingly focusing on the collection and organization of data for strategic decision-making. The ability to review historical trends and monitor near real-time operational data has become a key competitive advantage.
This article provides practical recommendations for testing extract, transform and load (ETL) applications based on years of experience testing data warehouses in the financial services and consumer retailing areas. Every attempt has been made to keep this article tool-agnostic so as to be applicable to any organization attempting to build or improve on an existing data warehouse.

Testing Goals

There is an exponentially increasing cost associated with finding software defects later in the development lifecycle. In data warehousing, this is compounded because of the additional business costs of using incorrect data to make critical business decisions. Given the importance of early detection of software defects, let's first review some general goals of testing an ETL application:

• Data completeness. Ensures that all expected data is loaded.
• Data transformation. Ensures that all data is transformed correctly according to business rules and/or design specifications.
• Data quality. Ensures that the ETL application correctly rejects, substitutes default values, corrects or ignores and reports invalid data.
• Performance and scalability. Ensures that data loads and queries perform within expected time frames and that the technical architecture is scalable.
• Integration testing. Ensures that the ETL process functions well with other upstream and downstream processes.
• User-acceptance testing. Ensures the solution meets users' current expectations and anticipates their future expectations.
• Regression testing. Ensures existing functionality remains intact each time a new release of code is completed.

Data Completeness

One of the most basic tests of data completeness is to verify that all expected data loads into the data warehouse. This includes validating that all records, all fields and the full contents of each field are loaded. Strategies to consider include:

• Comparing record counts between source data, data loaded to the warehouse and rejected records.
• Comparing unique values of key fields between source data and data loaded to the warehouse. This is a valuable technique that points out a variety of possible data errors without doing a full validation on all fields.
• Utilizing a data profiling tool that shows the range and value distributions of fields in a data set. This can be used during testing and in production to compare source and target data sets and point out any data anomalies from source systems that may be missed even when the data movement is correct.
• Populating the full contents of each field to validate that no truncation occurs at any step in the process. For example, if the source data field is a string(30) make sure to test it with 30 characters.
• Testing the boundaries of each field to find any database limitations. For example, for a decimal (3) field include values of -99 and 999, and for date fields include the entire range of dates expected. Depending on the type of database and how it is indexed, it is possible that the range of values the database accepts is too small.

Data Transformation

Validating that data is transformed correctly based on business rules can be the most complex part of testing an ETL application with significant transformation logic. One typical method is to pick some sample records and "stare and compare" to validate data transformations manually. This can be useful but requires manual testing steps and testers who understand the ETL logic. A combination of automated data profiling and automated data movement validations is a better long-term strategy. Here are some simple automated data movement techniques:

• Create a spreadsheet of scenarios of input data and expected results and validate these with the business customer. This is a good requirements elicitation exercise during design and can also be used during testing.
• Create test data that includes all scenarios. Elicit the help of an ETL developer to automate the process of populating data sets with the scenario spreadsheet to allow for flexibility because scenarios will change.
• Utilize data profiling results to compare range and distribution of values in each field between source and target data.
• Validate correct processing of ETL-generated fields such as surrogate keys.
• Validate that data types in the warehouse are as specified in the design and/or the data model.
• Set up data scenarios that test referential integrity between tables. For example, what happens when the data contains foreign key values not in the parent table?
• Validate parent-to-child relationships in the data. Set up data scenarios that test how orphaned child records are handled.

Data Quality

For the purposes of this discussion, data quality is defined as "how the ETL system handles data rejection, substitution, correction and notification without modifying data." To ensure success in testing data quality, include as many data scenarios as possible. Typically, data quality rules are defined during design, for example:

• Reject the record if a certain decimal field has nonnumeric data.
• Substitute null if a certain decimal field has nonnumeric data.
• Validate and correct the state field if necessary based on the ZIP code.
• Compare product code to values in a lookup table, and if there is no match load anyway but report to users.

Depending on the data quality rules of the application being tested, scenarios to test might include null key values, duplicate records in source data and invalid data types in fields (e.g., alphabetic characters in a decimal field). Review the detailed test scenarios with business users and technical designers to ensure that all are on the same page. Data quality rules applied to the data will usually be invisible to the users once the application is in production; users will only see what's loaded to the database. For this reason, it is important to ensure that what is done with invalid data is reported to the users. These data quality reports present valuable data that sometimes reveals systematic issues with source data. In some cases, it may be beneficial to populate the "before" data in the database for users to view.

Performance and Scalability

As the volume of data in a data warehouse grows, ETL load times can be expected to increase and performance of queries can be expected to degrade. This can be mitigated by having a solid technical architecture and good ETL design. The aim of the performance testing is to point out any potential weaknesses in the ETL design, such as reading a file multiple times or creating unnecessary intermediate files. The following strategies will help discover performance issues:

• Load the database with peak expected production volumes to ensure that this volume of data can be loaded by the ETL process within the agreed-upon window.
• Compare these ETL loading times to loads performed with a smaller amount of data to anticipate scalability issues. Compare the ETL processing times component by component to point out any areas of weakness.
• Monitor the timing of the reject process and consider how large volumes of rejected data will be handled.
• Perform simple and multiple join queries to validate query performance on large database volumes. Work with business users to develop sample queries and acceptable performance criteria for each query.

Integration Testing

Typically, system testing only includes testing within the ETL application. The endpoints for system testing are the input and output of the ETL code being tested. Integration testing shows how the application fits into the overall flow of all upstream and downstream applications. When creating integration test scenarios, consider how the overall process can break and focus on touchpoints between applications rather than within one application. Consider how process failures at each step would be handled and how data would be recovered or deleted if necessary.
Most issues found during integration testing are either data related to or resulting from false assumptions about the design of another application. Therefore, it is important to integration test with production-like data. Real production data is ideal, but depending on the contents of the data, there could be privacy or security concerns that require certain fields to be randomized before using it in a test environment. As always, don't forget the importance of good communication between the testing and design teams of all systems involved. To help bridge this communication gap, gather team members from all systems together to formulate test scenarios and discuss what could go wrong in production. Run the overall process from end to end in the same order and with the same dependencies as in production. Integration testing should be a combined effort and not the responsibility solely of the team testing the ETL application.

User-Acceptance Testing

The main reason for building a data warehouse application is to make data available to business users. Users know the data best, and their participation in the testing effort is a key component to the success of a data warehouse implementation. User-acceptance testing (UAT) typically focuses on data loaded to the data warehouse and any views that have been created on top of the tables, not the mechanics of how the ETL application works. Consider the following strategies:

• Use data that is either from production or as near to production data as possible. Users typically find issues once they see the "real" data, sometimes leading to design changes.
• Test database views comparing view contents to what is expected. It is important that users sign off and clearly understand how the views are created.
• Plan for the system test team to support users during UAT. The users will likely have questions about how the data is populated and need to understand details of how the ETL works.
• Consider how the users would require the data loaded during UAT and negotiate how often the data will be refreshed.

Regression Testing

Regression testing is re validation of existing functionality with each new release of code. When building test cases, remember that they will likely be executed multiple times as new releases are created due to defect fixes, enhancements or upstream systems changes. Building automation during system testing will make the process of regression testing much smoother. Test cases should be prioritized by risk in order to help determine which need to be rerun for each new release. A simple but effective and efficient strategy to retest basic functionality is to store source data sets and results from successful runs of the code and compare new test results with previous runs. When doing a regression test, it is much quicker to compare results to a previous execution than to do an entire data validation again.
Taking these considerations into account during the design and testing portions of building a data warehouse will ensure that a quality product is produced and prevent costly mistakes from being discovered in production.  

Informatica Development FAQs

The following pages summarize some of the questions that typically arise during development and suggest potential resolutions.

Q: How does source format affect performance? (i.e., is it more efficient to source from a flat file rather than a database?)

In general, a flat file that is located on the server machine loads faster than a database located on the server machine. Fixed-width files are faster than delimited files because delimited files require extra parsing. However, if there is an intent to perform intricate transformations before loading to target, it may be advisable to first load the flat file into a relational database, which allows the PowerCenter mappings to access the data in an optimized fashion by using filters and custom SQL SELECTs where appropriate.

Q: What are some considerations when designing the mapping? (i.e. what is the impact of having multiple targets populated by a single map?)

With PowerCenter, it is possible to design a mapping with multiple targets. You can then load the targets in a specific order using Target Load Ordering. The recommendation is to limit the amount of complex logic in a mapping. Not only is it easier to debug a mapping with a limited number of objects, but they can also be run concurrently and make use of more system resources. When using multiple output files (targets), consider writing to multiple disks or file systems simultaneously. This minimizes disk seeks and applies to a session writing to multiple targets, and to multiple sessions running simultaneously.

Q: What are some considerations for determining how many objects and transformations to include in a single mapping?

There are several items to consider when building a mapping. The business requirement is always the first consideration, regardless of the number of objects it takes to fulfill the requirement. The most expensive use of the DTM is passing unnecessary data through the mapping. It is best to use filters as early as possible in the mapping to remove rows of data that are not needed. This is the SQL equivalent of the WHERE clause. Using the filter condition in the Source Qualifier to filter out the rows at the database level is a good way to increase the performance of the mapping.

Log File Organization

Q: Where is the best place to maintain Session Logs?

One often-recommended location is the default "SessLogs" folder in the Informatica directory, keeping all log files in the same directory.

Q: What documentation is available for the error codes that appear within the error log files?

Log file errors and descriptions appear in Appendix C of the PowerCenter TroubleShooting Guide. Error information also appears in the PowerCenter Help File within the PowerCenter client applications. For other database-specific errors, consult your Database User Guide.

Scheduling Techniques

Q: What are the benefits of using workflows with multiple tasks rather than a workflow with a stand-alone session?

Using a workflow to group logical sessions minimizes the number of objects that must be managed to successfully load the warehouse. For example, a hundred individual sessions can be logically grouped into twenty workflows. The Operations group can then work with twenty workflows to load the warehouse, which simplifies the operations tasks associated with loading the targets.

Workflows can be created to run sequentially or concurrently or have tasks in different paths doing either.

·         A sequential workflow runs sessions and tasks one at a time, in a linear sequence. Sequential workflows help ensure that dependencies are met as needed. For example, a sequential workflow ensures that session1 runs before session2 when session2 is dependent on the load of session1, and so on. It's also possible to set up conditions to run the next session only if the previous session was successful, or to stop on errors, etc.

·         A concurrent workflow groups logical sessions and tasks together, like a sequential workflow, but runs all the tasks at one time. This can reduce the load times into the warehouse, taking advantage of hardware platforms' Symmetric Multi-Processing (SMP) architecture.

Other workflow options, such as nesting worklets within workflows, can further reduce the complexity of loading the warehouse. However, this capability allows for the creation of very complex and flexible workflow streams without the use of a third-party scheduler.

Q: Assuming a workflow failure, does PowerCenter allow restart from the point of failure?

No. When a workflow fails, you can choose to start a workflow from a particular task but not from the point of failure.  It is possible, however, to create tasks and flows based on error handling assumptions.

Q: What guidelines exist regarding the execution of multiple concurrent sessions / workflows within or across applications?

Workflow Execution needs to be planned around two main constraints:

·         Available system resources

·         Memory and processors

The number of sessions that can run at one time depends on the number of processors available on the server. The load manager is always running as a process. As a general rule, a session will be compute-bound, meaning its throughput is limited by the availability of CPU cycles. Most sessions are transformation intensive, so the DTM always runs. Also, some sessions require more I/O, so they use less processor time. Generally, a session needs about 120 percent of a processor for the DTM, reader, and writer in total.

For concurrent sessions:

·         One session per processor is about right; you can run more, but that requires a "trial and error" approach to determine what number of sessions starts to affect session performance and possibly adversely affect other executing tasks on the server.

The sessions should run at "off-peak" hours to have as many available resources as possible.

Even after available processors are determined, it is necessary to look at overall system resource usage. Determining memory usage is more difficult than the processors calculation; it tends to vary according to system load and number of Informatica sessions running.

The first step is to estimate memory usage, accounting for:

·         Operating system kernel and miscellaneous processes

·         Database engine

·         Informatica Load Manager

The DTM process creates threads to initialize the session, read, write and transform data, and handle pre- and post-session operations.

·         More memory is allocated for lookups, aggregates, ranks, sorters and heterogeneous joins in addition to the shared memory segment.

At this point, you should have a good idea of what is left for concurrent sessions. It is important to arrange the production run to maximize use of this memory. Remember to account for sessions with large memory requirements; you may be able to run only one large session, or several small sessions concurrently.

Load Order Dependencies are also an important consideration because they often create additional constraints. For example, load the dimensions first, then facts. Also, some sources may only be available at specific times, some network links may become saturated if overloaded, and some target tables may need to be available to end users earlier than others.

Q: Is it possible to perform two "levels" of event notification? At the application level and the Informatica Server level to notify the Server Administrator?

The application level of event notification can be accomplished through post-session e-mail. Post-session e-mail allows you to create two different messages, one to be sent upon successful completion of the session, the other to be sent if the session fails. Messages can be a simple notification of session completion or failure, or a more complex notification containing specifics about the session. You can use the following variables in the text of your post-session e-mail:

E-mail Variable	Description
%s	Session name
%l	Total records loaded
%r	Total records rejected
%e	Session status
%t	Table details, including read throughput in bytes/second and write throughput in rows/second
%b	Session start time
%c	Session completion time
%i	Session elapsed time (session completion time-session start time)
%g	Attaches the session log to the message
%m	Name and version of the mapping used in the session
%d	Name of the folder containing the session
%n	Name of the repository containing the session
%a<filename>	Attaches the named file. The file must be local to the Informatica Server. The following are valid filenames: %a<c:\data\sales.txt> or %a</users/john/data/sales.txt> On Windows NT, you can attach a file of any type. On UNIX, you can only attach text files. If you attach a non-text file, the send might fail. Note: The filename cannot include the Greater Than character (>) or a line break.

The PowerCenter Server on UNIX uses rmail to send post-session e-mail. The repository user who starts the PowerCenter server must have the rmail tool installed in the path in order to send e-mail.

To verify the rmail tool is accessible:

1. Login to the UNIX system as the PowerCenter user who starts the PowerCenter Server.

2. Type rmail <fully qualified email address> at the prompt and press Enter.

3. Type '.' to indicate the end of the message and press Enter.

4. You should receive a blank e-mail from the PowerCenter user's e-mail account. If not, locate the directory where rmail resides and add that directory to the path.

5. When you have verified that rmail is installed correctly, you are ready to send post-session e-mail.

The output should look like the following:

Session complete.
Session name: sInstrTest
Total Rows Loaded = 1
Total Rows Rejected = 0
Completed

Rows Loaded	Rows Rejected	ReadThroughput (bytes/sec)	WriteThroughput (rows/sec)	Table Name
Status
1	0	30	1	t_Q3_sales

No errors encountered.
Start Time: Tue Sep 14 12:26:31 1999
Completion Time: Tue Sep 14 12:26:41 1999
Elapsed time: 0:00:10 (h:m:s)

This information, or a subset, can also be sent to any text pager that accepts e-mail.

Backup Strategy Recommendation

Q: Can individual objects within a repository be restored from the back-up or from a prior version?

At the present time, individual objects cannot be restored from a back-up using the PowerCenter Repository Manager (i.e., you can only restore the entire repository). But, it is possible to restore the back up repository into a different database and then manually copy the individual objects back into the main repository.

Another option is to export individual objects to XML files.  This allows for the granular re-importation of individual objects, mappings, tasks, workflows, etc.

Server Administration

Q: What built-in functions, does PowerCenter provide to notify someone in the event that the server goes down, or some other significant event occurs?

The Repository Server can be used to send messages notifying users that the server will be shut down. Additionally, the Repository Server can be used to send notification messages about repository objects that are created, modified or deleted by another user.  Notification messages are received through the Informatica Client tools.

Q: What system resources should be monitored? What should be considered normal or acceptable server performance levels?

The pmprocs utility, which is available for UNIX systems only, shows the currently executing PowerCenter processes.

Pmprocs is a script that combines the ps and ipcs commands. It is available through Informatica Technical Support. The utility provides the following information:

- CPID - Creator PID (process ID)

- LPID - Last PID that accessed the resource

- Semaphores - used to sync the reader and writer

- 0 or 1 - shows slot in LM shared memory

(See Chapter 16 in the PowerCenter Repository Guide for additional details.)

A variety of UNIX and Windows NT commands and utilities are also available. Consult your UNIX and/or Windows NT documentation.

Q: What cleanup (if any) should be performed after a UNIX server crash? Or after an Oracle instance crash?

If the UNIX server crashes, you should first check to see if the repository database is able to come back up successfully. If this is the case, then you should try to start the PowerCenter server. Use the pmserver.err log to check if the server has started correctly. You can also use ps -ef | grep pmserver to see if the server process (the Load Manager) is running.

Metadata

Q: What recommendations or considerations exist as to naming standards or repository administration for metadata that might be extracted from the PowerCenter repository and used in others?

With PowerCenter, you can enter description information for all repository objects, sources, targets, transformations, etc, but the amount of metadata that you enter should be determined by the business requirements. You can also drill down to the column level and give descriptions of the columns in a table if necessary. All information about column size and scale, datatypes, and primary keys are stored in the repository.

The decision on how much metadata to create is often driven by project timelines. While it may be beneficial for a developer to enter detailed descriptions of each column, expression, variable, etc, it is also very time consuming to do so. Therefore, this decision should be made on the basis of how much metadata will be required by the systems that use the metadata.

There are some time saving tools that are available to better manage a metadata strategy and content, such as third party metadata software and, for sources and targets, data modeling tools.

Q: What procedures exist for extracting metadata from the repository?

Informatica offers an extremely rich suite of metadata-driven tools for data warehousing applications. All of these tools store, retrieve, and manage their metadata in Informatica's central repository. The motivation behind the original Metadata Exchange (MX) architecture was to provide an effective and easy-to-use interface to the repository.

Today, Informatica and several key Business Intelligence (BI) vendors, including Brio, Business Objects, Cognos, and MicroStrategy, are effectively using the MX views to report and query the Informatica metadata.

Informatica strongly discourages accessing the repository directly, even for SELECT access because some releases of PowerCenter change the look and feel of the repository tables, resulting in a maintenance task for you. Rather, views have been created to provide access to the metadata stored in the repository.

Additional products, such as Informaticas Metadata Reporter and PowerAnalyzer, allow for more robust reporting against the repository database and are able to present reports to the end-user and/or management.