Solved

Source To Target Mapping Testing

Posted on 2016-11-02
14
40 Views
Last Modified: 2016-11-07
Can someone point me in the direction on how to test source target mapping of data?   I need a template or direction on how to create a test plan that tests source to target mapping of a web application.  The Web application source is from a Data Warehouse with SQL tables on the back end.  We are mapping to the application with Oracle tables.

I am asking testers of the source system to check that the data source is mapped correctly to tables and validation of the data.  I need suggestions or instructions on how to develop a test plan to provide to them.  

Thanks.
0
Comment
Question by:alpha100
  • 8
  • 6
14 Comments
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 41870809
In most cases, the mapping itself is just documentation.

What you need to do is compare source data to target data.
What most people do is write the SQL statement that shows source data in the target data's format, then compare that output to target rows completed by your ETL/SQL.  To assist, make a fake dataset of a small dataset.
0
 

Author Comment

by:alpha100
ID: 41872130
Can you provide me with an example of a statement that shows source date in the target data's format to make a comparison?  Thanks.
0
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 41872299
can you please be more specific?  Are you converting date formats?
Is this from a file to a db? db to db? or db to file?  if file, what kind?

Thanks
0
 

Author Comment

by:alpha100
ID: 41872414
Sorry,

What I am trying to do is send the data owners use case scenarios.  We are receiving an extract file  from our customer; the source being a data warehouse.  We are doing a 1 to 1 mapping of their data to our oracle warehouse tables used to display information on our GUI.  We do take the data through and ETL process but there is there is no transformation of the data.  No file format changes.   We just need  to verify if the information captured is true.  Since I am not familiar with the data, I just need to know how to format scenarios.  What should I provide to the data owner.  That is where the confusion is for me.  Thanks.
0
 
LVL 9

Accepted Solution

by:
Evan Cutler earned 500 total points
ID: 41872445
have you built a Source-to-Target Mapping document? SourcetoTarget-Mapping-Template.xlsx
It is the responsibility of your DB Modeler to define the document, while your ETL architect to design the movement (ETL Jobs).  If they are the same person, it's easier, but most of the time it isn't.  

In your case, it sounds like you are creating a ODS database which is a copy of the raw data coming in.  A user story for ODS would be: 'As a data consumer, I want a copy of all incoming data to work with so I may process them into my data warehouse from inside my database'.

You can use a Source to Target Mapping document like the one attached to visualize your transformations.  To answer your initial question, a date field is a date field.  As long as it's inserted, you can confirm that source.datefield = target.datefield in a Unit test.  Unless your ETL is changing the format of the Date, then your transformation is "COPY".  Otherwise, if the date is in a string format in your source, and you use ETL to capture the string into a date you can set transformation in your document to something like below:

Business Rule: transform String to Date when given 'YYYY/MM/DD' format <--You can chance that
Technical Rule: TO_DATE('2003/07/09', 'yyyy/mm/dd')

Hope this helps.
0
 

Author Comment

by:alpha100
ID: 41876533
Are these the instructions that I provide to the testers.  The testers are not use to source to target mapping.  I have my test plan developed.  I just need what wording to provide to them for test plan instructions?  What do they need to do?
0
 

Author Comment

by:alpha100
ID: 41876550
What exactly do the testers need to be checking for.  I just need wording on instructing them on what to check?  Thanks.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 9

Expert Comment

by:Evan Cutler
ID: 41876597
use this as your reference: https://www.tutorialspoint.com/etl_testing/etl_testing_techniques.htm

however, the Source To Target Matrix describes the expectation of data from Source to Target.  The Testers need to perform a series of tests (below is examples):

1. RowCount  (Expected Target Table rowcount = actual Target Table rowcount)
2. Proper Lookups (Expected Target field value = Actual Target field value)
3. Null Value handling (NO Null errors; Expected NULL to NON-NULL Hanlding)
4. Negative value test (Reject handling)
5. No Operational Failures (ALL ETL RUN)

You're testers should be trained enough to read your STM and develop a data sets and sql queries to create true/false reporting on expected results.  

So, to answer your question, give your testers the STM, and they should develop a series of data sets for your tables, and be able to run the ETL code without failures.  From there, their SQL statements will validate the values you are supposed to have.

Do you need a consultation?  I do provide full time consultation and assistance in ETL development and testing.
0
 

Author Comment

by:alpha100
ID: 41877061
Yes I need consultation.  How much do you charge.  I know in the future, I will be working on this again.  And we don't have individuals in our group who have ever had to do this type of document or testing.  So we have no templates, no instructions no nothing to assist with this type of task.
0
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 41877309
No problem.  I will message you directly with contact and other information.
0
 

Author Comment

by:alpha100
ID: 41877470
Do I need to send the tester the ETL Code?
0
 

Author Comment

by:alpha100
ID: 41877472
What if I don't have access to the code?
0
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 41877487
no. The tester shouldn't need to access the code.  just the ability to run it when developed.  please understand, I'm typing this in the blind with no knowledge of your business, so I'm just using normal standards.

The Source-to-Target Mapping document has all the information needed for the developers to create ETL code.  That same guidance is given to a tester to create a SQL-style test.

Example:

if I had a table of 5 columns, and they were going to just be copied from source to target, with no transformation, then the ETL will be designed with high-speed movement.  however a tester would do the following:

1. create a series of test rows (could be 10 or 100, depending on your preference).  
2. Insert the series of rows into an empty Source table.
3. Run the ETL or ask the Developers to run it.
4. Compare the following:
- Number of rows that made it into Target
- Values of a randomly selected row are identical to source
- ETL ran successfully with no errors.

To do this, i would write a sql statement like this in the target:
1. select count(1) from target table <- gets number of rows
2. select * from target table where table_primary_key = x <-- where x is a randomly selected row to compare with target

if you start running into more complicated issues, such as lookups and null handling, then I would pick a random record, and create this:
Example 1: select nonnullablefield from target_table where table_primary_key = x <--where x is a randomly selected row in source that has a null entry
Example 2: select lookup_field_value from target_table where table_primary_key = x <--where x is a randomly selected row in source that has a value that matches a lookup table's key value.

And further tests could be done, such as reject handling.  The number of tests are usually equaled by the patience of the product owner.  The Source-to-Target mapping informs the testers how the transformations are being done, and they should be creating these statements.  WHen they are run against the target table, the results can assist in proving positive, and sometimes negative, tests.

Hope this gives you an idea.
0
 

Author Comment

by:alpha100
ID: 41877494
Thank you.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

You can provide a virtual interface for remote stakeholders in a SWOT analysis through a Google Drawing template. By making real time viewing and collaboration possible, your team can build a stronger product.
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now