This requires Admin or Data Admin privileges for implementation.

Please Note: If you do not have the correct privileges to access Data Hub you will receive the following upon your login attempt. If you get this message and think it is an error please see your organization's iDashboards Administrator for assistance as Support is unable to give you access.

To help prevent Java Heap error messages using the following process will help to maintain a smooth running system.

Creating a large data load job

We will be working backward a bit to start with for this solution, so following these steps will be very important.

From the Data Set Tab select New Data Set
1. Choose Cloud API
2. Pick the API you want to work with
3. For this example select Objects
  1. Select an Object from the list to work with.
  2. Field: Add the fields you want to pull into your Data Set (you will need a created date, a last modified date and a unique ID field).
  3. Optional Related Fields: Select any fields you want to include in your data.
  4. Filters: Click Parameters at the bottom left of the page and then the + button to add a simple input parameter. This will act as a row limiter so we are not trying to pull and process excessively large amounts of data in one batch.
  5. 1. Name: CreatedDate
    2. Label: Created Date
    3. Data Type: Datetime
    4. Required: Checked
    5. Save
  6. Create a second Parameter

Add the filters by click the Plus Icon in the bottom right of the page.

We now need to configure the variables with selecting the 3 logic operator values:

Choose Created Date for the Column, >= for the Logic Operator, and enter ${param:createddate} in the empty criteria field at the bottom. Click Save.
Click the AND button to add an AND to the filter.
Click the + to add a second filter, choose the same Column as above, select <= for the logic operator and enter ${param:topcreateddate} in the criteria field. Click Save.
You should now see the following under the Specify Filters area.

Save your new filter
Save the Data Set
1. Having a standard folder structure helps recalling the jobs later and what connection types are used.
2. Create a Folder with your API Name then create a sub directory named Bulk Loads and save the data set in that.
3. Naming Jobs: Short API name, Type of connection - Table - Short Description
  1. E.g. SFobj - Account - Created Date Filtered for Batch

ETL - Setup

Create a new ETL Job
Pull the Extract icon ("E") to the stage
1. Use the configure gear to select the Data Set we just created.
2. Select the Data Set we just created
3. We have to fill in the parameter values for the Data Set. Use ${currdate +1} for both values. (We don't want to pull data, we're just creating the tables at this time.)
4. Save
Drag the Load icon ("L") to the stage.
1. Using the vertical pointing arrow on the Extract Data task connect it to the your new Load Data into Table task.
2. On the Load Data into Table task click the configuration gear.
3. select the option Create a Target Table from Input
4. Select your Data Store
5. Select your Schema
6. You will See the columns in your data now. Scan the page and look for anything you want to remove or alter. Enter The Table name and click Create.
7. The Load Data into Table window now appears, I recommend checking Truncate Long Strings to Fit
8. For Load Mode select Insert New Rows Only
9. Check the boxes that appear under Update Key. With any Load Mode that is creating or updating rows, you will need to set a Update Key to find your data key. The data key is a unique column that appears in every row. Most of the time they are an ID column. NOTE: the Update Key can never be NULL and must be unique
10. Save
Save your ETL Job, you will need to name the job.
1. E.g. Tablename - Batch Load

Max Data Source

This can be used for both batch loads and incremental loads. This job will be kept for the long term.

From the top menu select Data Sets > New Data Set
Select Custom Query from the left side
On the Right Side of the New Data Set window, select your schema and find the target table we created previously
Using the custom SQL builder pull the Created Date and Modified date columns onto the stage. They should go into the query where they belong.
Now to your date field we need to add MAX. (This will return the Max Date for these values)
1. If you have spaces in your column name you will need to add square brackets around the column name. [Created Date]
2. The format for max is MAX([Column Name]) as MAXColumnName this is a good time to remove the spaces. It will save you some steps later.
  1. E.g. SELECT MAX([Created Date]) as MaxCreatedDate, MAX([Last Modified Date]) as MaxLastModifiedDate FROM YourTableName
3. Press the Retrieve Columns button to test your SQL.
4. Once confirmed Save - Give the Data Set a name
  1. E.g. MaxDates - TableNameobj Table
  2. Its recommended to create a folder for these Data Sets

First Date Entry in API

This section is optional but recommended. We are going to use our data source to find the first entry of data in for the table.

Create new Data Set
Choose our API Connection we are working with
Query Type select SQL or Custom query
On the right find your table expand the table now you can search for date and you should find the created date. Drag Created Date and the table name to the stage.
Now we need to order the data and limit the rows returned.
1. Salesforce: order by CreatedDate asc This will order the data in way we need.
  1. add Limit 1 to the end
2. SELECT CreatedDate FROM TableName order by CreatedDate asc limit 1
Save
Save Data Set and Name
1. E.g. soql - TableName- Find First Date

Back to our ETL Job

Edit ETL Job
1. Load our ETL we created
2. All we have on our stage should be our Extract Data and Load Data into Table.
Pull an Extract Icon to the stage.
1. Use the configure gear
2. select the Data Set we created to find the fist created date in our API table.
3. Save
Pull the Transform Icon to the stage
1. Select Create Job Variables
2. Attach the Extract Data task to the Create Job Variables Transformation
3. Use the configure gear
4. In the bottom right click Add
5. Now we are going to create and name a Variable
  1. Name: SF_CreatedDate
  2. Type: An Expression
  3. Data Type: DateTime
  4. $first["CreatedDate"] we should only ever have 1 row returned from our query
  5. Create
Pull the Extract Icon to the stage
1. Connect our Job Variables task to our new Extract Data task.
2. Select our Max Date Data Set.
  1. Save
Pull the Transform Icon to the stage
1. Select Create Job Variables
  1. Attach extracted Date Max Loads to this new Variables
  2. Use the configuration gear
    1. Add
      1. Name: myCreatedDate
      2. Type: An Expression
      3. Data Type: DateTime
      4. Our Expression will need an IF statement to fine the most current date to use for our job. if(new Date($first["MaxCreatedDate"]) < new Date(SF_CreatedDate)){
        SF_CreatedDate;
        } else {
        $first["MaxCreatedDate"]
        }
      5. Create
    2. Add
      1. Name: addDays
      2. Type: A Fixed Value
      3. Data Type: Number
      4. Value: 180 Note: Depending on your data size change this value. Less days if the job is taking a long time to finish.
      5. Create
Pull the Transform Icon to the stage
1. Select Create Job Variables
  1. Attach previous Transform task to this new Variables
  2. Use the configuration gear
    1. Add
      1. Name: MaxPullDate
      2. Type: An Expression
      3. Data Type: Datetime
      4. var sliceSizeInMillis = ( 1000 * 60 * 60 * 24 * addDays);
        new Date( myCreatedDate.getTime() + sliceSizeInMillis); This take your last created date returned and adds the value setup in the previous job as addDays so that will give you the Max Date to pull.
      5. Create
    2. Save
Connect our Last Created Job Variables to our fist Extract
1. Use the configuration gear on Extract Job 1
  1. Under Data Set Parameters we need to replace our ${currdate +1} values
    1. Max Created Date: ${jv:myCreatedDate}
    2. Top Date: ${jv:MaxPullDate}
    3. Save
Save your ETL Job again.

Incremental Data Loads

To do an incremental Load you need to have data already populated into your table.

Max Data Source

This can be used for both batch loads and incremental loads. This job will be kept for the long term.

From the top menu select Create a new Data Set
select your newly created database the choose Custom Query
On the Right Side of the stage select your schema and find your target table data store
Using the custom SQL builder pull the Created Date and Modified date columns onto the stage. They should go into the query where they belong.
Now to your date field we need to add MAX. (This will return the Max Date for these values)
1. If you have spaces in your column name you will need to add square brackets around the column name. [Created Date]
2. The format for max is MAX([Column Name]) as MAXColumnName this is a good time to remove the spaces. It will save you some steps later.
  1. E.g. SELECT MAX([Created Date]) as MaxCreatedDate, MAX([Last Modified Date]) as MaxLastModifiedDate FROM YourTableName
3. Press the Retrieve Columns button to test your SQL.
4. Once confirmed Save - Give the Data Set a neme
  1. E.g. MaxDates - TableNameobj Table
  2. Its recommended to create a folder for these Data Sets

Create Max Created Date Data Set

From the top menu select Create a new Data Set
1. Select the Cloud API and choose your object
2. Fields: Select the fields you want to work with (you will need a created date, a last modified date and a unique ID field).
3. Related Fields (optional): add any fields you want to work with
4. Add a Parameter
  1. Name: maxcreateddate
  2. Lable Max Created Date
  3. Data Type: Datetime
  4. Required: Checked
  5. Save
5. Click the Plus icon in the bottom right of the screen to add a filter.
  1. Search for and select created date.
  2. select your logic operator >
  3. use ${param:maxcreateddate}
  4. Save
6. Save
From the top menu select Create a new Data Set
1. Select your object we have been working with
2. Fields: Select the fields you want to work with (should be the same as last)
3. Related Fields (optional): add any fields you want to work with
4. Add a Parameter
  1. Name: maxcreateddate
  2. Lable Max Created Date
  3. Data Type: Datetime
  4. Required: Checked
  5. Save
5. Click the Plus icon in the bottom right of the screen to add a filter.
  1. Search for your created date and select created date once found
  2. select your logic operator >
  3. use ${param:maxcreateddate}
  4. Save
6. Save
7. Name your Data Set
  1. Name this with created at the end E.g. SFobj_Accounts_Created
From the current data set click Save AS

This will ask you to Name this Data Set
Add Modified to the end of the job name

Click the blue Configure button in the middle right of the screen.
1. Go all the way to the bottom and click Parameter
2. Click the Pencil Icon
  1. Change the name: lastmoddate
  2. Lable: Last Modified Date
  3. Save
  4. Close the Parameter window
3. Editing the Filter: Click the pencil in the bottom right
  1. Change The filter from created date to the Last Modified Date
  2. logic operator >
  3. Delete the macro in the macro field
  4. Use ${param:lastmoddate}
  5. Save
4. Save

Creating the ETL Job

From the ETL tab click New ETL Job
Pull the Extract icon ("E") to the stage
1. Use the configure gear to select the Data Set Max Dates.
2. Save
Pull the Transfrom ("T") to the stage
1. Select Create Job Variables
2. Connect the Extract to the Create Job Variable
3. Click the gear on Create Job variable
  1. Click Add
    1. Name: MaxDateMod
    2. Type: Expression
    3. Data Type: Datetime
    4. From Variables select $first["CreatedDate"] (we should only return one row so first or last doesn't matter)
    5. Create
  2. Save
Pull the Extract icon ("E") to the stage
1. Use the configure gear to select the Data Set for the Modifications..
2. Open
3. On the right side you should see ${} click that icon and go to job Variables and select ${jv:MaxDateMod}
4. Save
Drag the Load icon ("L") to the stage
1. Using the vertical pointing arrow on the Extract Data task connect it to the your new Load Data into Table task. (Note: If you previously created a new table in the steps above, you will link to that table instead).
2. Click the Gear icon on Lad Date into table
  1. Select Create a Target Table from Input
  2. select your data store
  3. select your schema
  4. enter table name
  5. Load Mode: Update Existing Rows Only
  6. Select your ID field - NOTE: the Update key can never be NULL and must be unique
  7. Check Truncate Long Strings to Fit
  8. Save
Pull the Extract icon ("E") to the stage
1. Use the configure gear to select the Data Set for the Created Date.
2. Open
3. On the right side you should see ${} click that icon and go to job Variables and select ${jv:MaxDateMod}
4. Save
Drag the Load icon ("L") to the stage
1. Using the vertical pointing arrow on the Extract Data task connect it to the your new Load Data into Table task.
2. Click the Gear icon on Load Data into table
  1. Select Use an Existing Table
  2. select your data store
  3. select your schema
  4. select your Table Name
  5. Load Mode: Insert New Rows Only
  6. Click Auto Map under the input column
  7. Select your ID field - NOTE: the Update key can never be NULL and must be unique
  8. Check Truncate Long Strings to Fit
  9. Save
Save
Name your Job
You can now schedule your job.

For More Information:

ETL: Scheduled Jobs Failing
Java: Out of Memory Error - System Unavailable
iDashboards Data Hub Manual 8. Extract, Transform and Load
Java Documentation: 3.2 Understand the OutOfMemoryError Exception

If the above is unable to resolve the issue, then please contact iDashboards Support for further assistance.

ETL: Job Optimization

Creating a large data load job

ETL - Setup

Max Data Source

First Date Entry in API

Back to our ETL Job

Incremental Data Loads

Max Data Source

Create Max Created Date Data Set

Creating the ETL Job

For More Information:

Comments