0

Design Help needed

Charles Kelsoe

August 22, 2012 14:39

None

Follow

Hi,

Could you provide some guidance on how to handle the following scenario.

I have a target database with 4 phone fields and 4 phone type fields (not normalized). The fields are intended for the following use:

Phone 1: primary phone or home phone

Phone 2: work phone

Phone 3: mobile phone

Phone 4: fax

 

I have a normalized source phone table. It has columns for phone number and phone type. How would I design a data flow that would put the phone numbers in the correct place for those that have a matching phone type then fill in the other phone numbers with the remaining data? The target fields are as follows:

Phone
PhoneTitle
WorkPhone
WorkPhoneTitle
MobilePhone
MobilePhoneTitle
Fax
FaxTitle

So even though the phone  number may be in the Fax field the title stored in the FaxTitle can be something else other than Fax for the phone type. 

For Example:

Source                                      Target

PhoneNumber  Type             PhoneField              PhoneFieldType      Goes in Target Field               (these match phone type in the target)
123-456-7860   Phone -->   123-456-7860         Phone                         Phone1
231-654-9870   Mobile -->    231-654-9870        Mobile                         MobilePhone

Then have a second loop that would put the next numbers in any target phone field that does not already have a value

456-789-1234  Wife               456-789-1234         Wife                           WorkPhone (with a phone title of Wife)
456-789-6547  Secretary      456-789-6547         Secretary                  Fax (with a fax title of Secretary)

I am not sure if this is clear - hopefully the attached excel file will help.

PhoneNumberExamples.xlsx

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Support August 29, 2012 19:15

  There is a transformation available for this scenario. You should be using denormalize transformation with creation of groups based on sequence. You can find details of this transformation here - http://www.youtube.com/watch?v=AUE5d5T4EJQ

• 0
  
  Charles Kelsoe August 29, 2012 19:20

  Right - I have the denormalization part working as expected. The issue is inserting the data into the target data that has the following columns:

   

  Phone
  PhoneTitle
  WorkPhone
  WorkPhoneTitle
  MobilePhone
  MobilePhoneTitle
  Fax
  FaxTitle

  The problem I am trying to solve is this - in the first pass put in the data that matches the default purpose of the field. For example, Fax goes into the fax field and work goes into the work field. Then I need a second pass that puts the additional source values into the first empty phone field it finds. That is what this example is trying to explain:

  Source                                      Target

  PhoneNumber  Type             PhoneField              PhoneFieldType      Goes in Target Field               (these match phone type in the target)
  123-456-7860   Phone -->   123-456-7860         Phone                         Phone1
  231-654-9870   Mobile -->    231-654-9870        Mobile                         MobilePhone

  Then have a second loop that would put the next numbers in any target phone field that does not already have a value

  456-789-1234  Wife               456-789-1234         Wife                           WorkPhone (with a phone title of Wife)
  456-789-6547  Secretary      456-789-6547         Secretary                  Fax (with a fax title of Secretary)

  The Excel file also tried to explain this.

  
  

  
  

• 0
  
  Support August 29, 2012 20:52

  This is a tricky problem. Here's one solution that can work for your scenario -

  1)Create a passthru transformation as destination for denormalize. For the destination layout, make sure that it has all the fields for your standard fields AND fields for non-standard ones too. So that your layout should look like -

  Phone
  PhoneTitle
  WorkPhone
  WorkPhoneTitle
  MobilePhone
  MobilePhoneTitle
  Fax
  FaxTitle

  WifePhone

  WifePhone Title

  Insurance phone

  Insurance phone title

  .

  .

  .

  2) Now, use the driver field based normalize to populate all the columns in this destination.At this point, you'll have a structure where all additional phone numbers are stored in their own additional columns, but the standard ones are already in their respective places.
  

  3) Now, you'll have to create four passes - one each for four standard destination fields. At each pass, you'll be working on one standard phone/title combination in destination and populating with the right logic (using an expression) and mapping rest of the fields as is.

  For example, first pass would have an expression to check if the phonetitle field is empty, then place the first additional field (you'll have to check iteratively to figure out which is the first non null additional field), otherwise copy the incoming value of phone field. As a result, after this pass you are assured that first field contains either the phone or the first additional field. Work on the subsequent standard fields in subsequent passes. At the end of all four passes, you should have all your four field combinations populated properly.

  4) Now, map the first four field combinations to your database destination.