Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I put together a sample <a href="https://dl.dropboxusercontent.com/u/27024221/stackoverflow.ktr" rel="noreferrer">transformation(right click and choose save link)</a> based on what you provided. The only step I feel a bit uncertain on is the last table inputs. I'm basically writing the join data to the table and letting it fail if a specific relationship already exists. </p> <h3>note:</h3> <p>This solution doesn't really meet the "All approaches should include some from of validation and a rollback strategy should an insert fail, or fail to maintain referential integrity." criteria, though it probably won't fail. If you really want to setup something complex we can but this should definitely get you going with these transformations.</p> <p><img src="https://i.stack.imgur.com/WRbLq.png" alt="alt text"></p> <h2>Dataflow by Step</h2> <p><strong>1.</strong> We start with reading in your file. In my case I converted it to CSV but tab is fine too. <img src="https://i.stack.imgur.com/TnMvM.png" alt="alt text"></p> <p><strong>2.</strong> Now we're going to insert the employee names into the Employee table using a <code>combination lookup/update</code>. After the insert we append the employee_id to our datastream as <code>id</code> and remove the <code>EmployeeName</code> from the data stream. </p> <p><img src="https://i.stack.imgur.com/YHNqT.png" alt="alt text"></p> <p><strong>3.</strong> Here we're just using a Select Values step to rename the <code>id</code> field to employee_id <img src="https://i.stack.imgur.com/VBwAe.png" alt="alt text"></p> <p><strong>4.</strong> Insert Job Titles just like we did employees and append the title id to our datastream also deleting the <code>JobLevelHistory</code> from the datastream.</p> <p><img src="https://i.stack.imgur.com/aXFsd.png" alt="alt text"></p> <p><strong>5.</strong> Simple rename of the title id to title_id(see step 3) <img src="https://i.stack.imgur.com/66Wjx.png" alt="alt text"></p> <p><strong>6.</strong> Insert offices, get id's, remove OfficeHistory from the stream.</p> <p><img src="https://i.stack.imgur.com/xwW0g.png" alt="alt text"></p> <p><strong>7.</strong> Simple rename of the office id to office_id(see step 3)</p> <p><img src="https://i.stack.imgur.com/oMPrx.png" alt="alt text"></p> <p><strong>8.</strong> Copy Data from the last step into two streams with the values <code>employee_id,office_id</code> and <code>employee_id,title_id</code> respectively. </p> <p><img src="https://i.stack.imgur.com/EvF7E.png" alt="alt text"> <img src="https://i.stack.imgur.com/I2LU8.png" alt="alt text"></p> <p><strong>9.</strong> Use a table insert to insert the join data. I've got it selected to ignore insert errors as there could be duplicates and the PK constraints will make some rows fail.</p> <h2>Output Tables</h2> <p><img src="https://i.stack.imgur.com/I5dXv.png" alt="alt text"></p> <p><img src="https://i.stack.imgur.com/jrQwJ.png" alt="alt text"></p> <p><img src="https://i.stack.imgur.com/k0Go0.png" alt="alt text"></p> <p><img src="https://i.stack.imgur.com/TBP7J.png" alt="alt text"></p> <p><img src="https://i.stack.imgur.com/Jur5w.png" alt="alt text"></p>
    singulars
    1. This table or related slice is empty.
    1. POUsing Pentaho Kettle, how do I load multiple tables from a single table while keeping referential integrity?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMichael Kohne
    1. USrwilliams
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POUsing Pentaho Kettle, how do I load multiple tables from a single table while keeping referential integrity?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO@rwilliams aka r-dub: +1 Great, thank you -- I'll look at your answer right now. FYI went to your profile and upvoted 10 of your existing answer, I'll up vote more late. Again, THANK YOU!!
      singulars
      1. PO
      1. USblunders
    2. CODon't worry about upvoting any of my posts. I'll be happy enough if you get this going. I felt pretty bad telling you to use pentaho and then letting you go at it blindly.
      singulars
      1. PO
      1. USrwilliams
    3. CO@ALL: Just a heads up that the link to the KTR is an HTML doc (not a KTR file), click the link, and follow the directions **to get the KTR file.**
      singulars
      1. PO
      1. USblunders
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload