Data Warehouse Load Methods

A data warehouse is a warehouse that contains data. Sounds funny doesn't it? Imagine a warehouse as a storage structure that may be physical or logical. In the DW it's both...logical as in the data model representation and physical as to the hard disks and other computer equipment that support the data warehouse.

The first question should be "How does the data get there?" Here are three key areas to keep in mind....

Availability - will someone create a report with transactions completed 20 minutes ago or will it be with yesterday's data?

Data sources in DataStage

IIS Datastage connectivity options give us a wide scope to connect with different source or targets. It's support RDBMS, ERP, z/OS DB, OLAP system and many more.

Below listed Data Sources are available in IIS v11.3

Execution Steps in Transformer Stage - Explanation

 You can access Part1 Here -  Execution Steps in Transformer Stage

Certain constructs are inefficient if they are included in output column derivations, because they are evaluated once for every output column that uses them. The following examples describe these constructs:

The same part of an expression is used in multiple column derivations.

For example, if you want to use the same substring of an input column in multiple columns in output links, you might use the following test in a number of output columns derivations:

IF (DSLINK1.col1[1,3] = "001") THEN ...

In this case, the evaluation of the substring of DSLINK1.col1[1,3] is repeated for each column that uses it. The evaluation can be made more efficient by moving the substring calculation into a stage variable. The substring is then evaluated once for every input row. This example has thus stage variable definition for StageVar1:

Execution Steps in Transformer Stage

I've been asked this questions so many times in interviews and by different practitioner also that What are the data processing steps when datastage is processing transformer, So here I tried to compiled. Have a look -


To write efficient Transformer stage derivations, it helps to understand what items get evaluated and when.

Disable auto insertion of Partition and Sort

Partitioner insertion and sort insertion each make writing a flow easier by alleviating the need for a user to think about either partitioning or sorting data. By examining the requirements of operators in the flow, the parallel engine can insert partitioners, collectors and sorts as necessary within a data flow.

However, there are some situations where these features can be a avoided or not needed.
If data is pre-partitioned and pre-sorted, and the InfoSphere DataStage job is unaware of this, you could disable automatic partitioning and sorting for the whole job by setting the following environment variables while the job runs:

SQL Assignment08 - Delete Statement

1. Delete from customers.
2. Delete depositor of branches having number of customers between 1 and 3.
3. Delete branches havinng average deposit less than 5000.
4. Delete branches having maximum loan more than 5000.
5. Delete branches having deposit from Nagpur.

SQL Assignment07 - Update Statement

1. Give 10% interest to all depositors.
2. give 10% interest to all depositors having branch VRCE
3. Give 10% interest to all depositors living in city Nagpur.
4. Give 10% interest to all depositors having branch in city BOMBAY and living in Nagpur.
5. Add 100 Rs to deposit of Anil and assign it to Sunil.
6. Change the deposit of VRCE branch to 1000 and change the branch as VRCE_UPT
7. Assign to the deposit of Anil the maximum deposit from VRCE branch

SQL Assignment06 - Group By or Having Clause

1. List the branches having sum of deposit more than 5000.
2. List the branches having a sum of deposit more than 5000 and located in city BOMBAY.
3. List the name of Customers having deposit in the branches where the average deposit is more than 5000.
4. List the names of customers having maximum deposit.
5. List the names of customers having maximum deposit in the table CUSTOMERS living in Nagpur.
6. List the name of branch having highest number of depositors.
7. Count the number of depositors living in Nagpur.

SQL Assignment05 - Aggregate Functions

1. List total loan.
2. List total deposit.
3. List total loan taken from KAROLBAGH branch.
4. List total deposit of customers having account date later than 1-Jan-96.
5. List total deposit of customers living in city Nagpur.
6. List maximum deposit of customers living in BOMBAY.
7. List total deposit of customers having branch city DELHI

SQL Assignment04 - Operations

1. List all the customers who are depositors but not borrowers.
2. List all the customers who are both depositors and borrowers.
3. List all the customers, their amount, who are either depositors or borrowers and living in city Nagpur.
4. List all the depositors having deposit in all the branches where Sunil is having account.
5. List all the customers living in city Nagpur and having branch city BOMBAY or DELHI.

SQL Assignment03 - Join or Cartesian Product

Some more queries on SQL to work on --

1. Give names of customers having living city BOMBAY and branch city Nagpur.
2. Give Names of Customers having the same living city as their branch city.
3. Give names of customers who are borrowers as well as depositors and having living city as Nagpur.
4. Give names of customers who are depositors and having the same branch city as that of Sunil.
5. Give names of depositors having the same living city as that if Anil and having deposit amount greater than 2000.