DATASTAGE QUESTIONS

DATASTAGE QUESTIONS

1. WHAT IS DIFFERENCE BETWEEN SEQUENTAIL FILE AND DATA SET?

Sequentail file--- reads the data sequentially. cannot handle nullability. memory linit is 2gb. file extension is .txt/.csv
Dataset-----------reads the data parallel can handle nulls. extension of file is .ds

2.what is APT configuration file?

It is the environment variable which is used to recognize the *.apt file in DataStage. It is also used to keep the node information, scratch information and disk storage information.

3. what is diffenrece between datasatge 11.x version and previous?

DataStage 11.5 is the first version that promises to run anywhere - it can run on a dedicated DataStage engine (Windows, Linux, AIX or Z). It can push processing down into databases via Balanced Optimizer.  It can run natively on Hadoop.

  Tell any one of these:
 Dynamic RDBMS Stage: replaced by DRS Connector
 Oracle OCI, Oracle OCI Load: replaced by Oracle Connector
 Teradata API: replaced by Teradata Connector
 DB2 UDB Load, UBD API, DB2 Z: replaced by DB2 Connector

4. What is descriptor file and data file in dataset?

As the name says, data files contains the data and the descriptor file contains the information about the data in the data files.

5. How to remove duplicates in datastage?
Remove Duplicate Stage:
Duplicates can be detached by using Sort stage. We can use the opportunity, as allow duplicate = false.

6. Differnce between Join,Merge,Looup?

Join: linknames--Left,Right,Intermidiate
        Sorting is mandatory
        supports-  left join,inner join,right outer join,full outer join

Lookup: lonk names--- master,refernce
  sorting is optional
supports-- drop(inner join)  continue- left ouer join

All the three are dissimilar from each other in the way they use the memory storage, compare input necessities and how they treat various data . Join and Merge needs minimum memory as compared to the Lookup stage.

7.What are differnt types of Lookups in datasatge?

There are two types of Lookups in DataStage i.e. Normal lookup and Sparse lookup.

Normal lookup--- volume of primary is high and refernce is less
Sparse lookup--- Volume of refernce is high and primary less.
Default is Normal lookup

8.what are differnt types of partioning techniques?

Key based-Hash,Range
Key less--same,auto

9.Components in Datastage?

Designer-  Design parllall and sequnce jobs,compile jobs,
Director-- see the logs of jobs, schdule jobs
Adminstrataor--- create project,set permission project
manager- import/export the prject

10 How to import/export  jobs from one env to another env?

using export--- job design without executables--.dsx componenets
import----- .dsx format


Q #11) What are the primary usages of Datastage tool?

Datastage is an ETL tool which is primarily used for extracting data from source systems, transforming that data and finally loading it to target systems.

Q #12) What is a DataStage job?

The Datastage job is simply a DataStage code that we create as a developer. It contains different stages linked together to define data and process flow.
Stages are nothing but the functionalities that get implemented.
For example: Let’s assume that I want to do a sum of the sales amount. This can be a ‘group by’ operation that will be performed by one stage.
Now, I want to write the result to a target file. So, this operation will be performed by another stage. Once, I have defined both the stages, I need to define the data flow from my ‘group by’ stage to the target file stage. This data flow is defined by DataStage links.
Once, I have defined both the stages, I need to define the data flow from my ‘group by’ stage to the target file stage. This data flow is defined by DataStage links.

Q #13) What are DataStage sequences?

Datastage sequence connects the DataStage jobs in a logical flow.

Q #14) Where do the Datastage jobs get stored?

The Datastage jobs get stored in the repository. We have various folders in which we can store the Datastage jobs.

Datastage Interview Questions

Datastage Interview Questions

1. What is configuration file?

APT configuration file contains details of node. In this it has sub parts node, pool, scratch disk, resource disk

2. What is difference between join, lookup ?

Join---(a) does not support reject link,

            (b)  in this link name are left, right, intermediate, 

            ©   supports inner join, left outerjoin, right outerjoin, full outerjoin

Lookup-(a) supports reject link

               (b) in this link names are   primary, reference

               © supports inner join (called as drop) left outer join(continue)

3. What is dataset?

Dataset is file stage with extension .ds.  in this data is stored in descriptor file and data fiile.

To retrieve data from dataset from unix “ orchadmin command is used.

4. What are different types of portioning techniques?

Key based portioning—hash,modulus,range

Key less portioning---- same,auto

5. How to remove duplicates in datastage?

REMOVE DUPLICATE STAGE.

IN COPY STAGE( IN THIS GO TO INPUT--- PARTIONINS-CHANGE TO HASH PARTIONS-CHECK SORT- UNIQUE(THIS UNIQUES REMOVE DUPLICATES)

SORT STAGE AND TRANSFORMER STAGE--- KEY CHANGE COLUMN SET TO TRUE(THIS IS DONE IN SORT STAGE)—TRANSFORMER STAGE(CONSTARINT)

6. What is difference between active stage and passive stage?

Active stages- all processing stages are active(transformation part)

Passive stage- file stage,database stage.

7. How to  handle unix commands in sequential file? What other stages supports unix command?

In sequential file stage there is options—in that slect filter(here we can write unix) and change the file method to file pattern. 

External filter stage supports unix command

8. Difference between filter and switch?

Switch is used to write case statement and we can do on single coulumn

Fileter is used to write where clause and we can do on multiple columns

9. How to find nth maximum salary from emp?

 In oracle select * from ( select sal,empid,rownum over(order by sal desc) rn from emp) where rn=-2

10. How to remove duplicates from table?

11. Difference between union and union all?

Union- remove duplicates

Union all- with duplictaes

12. How to delete nth line in unix?

Sed nd

13. Where/Group by/having/ based scenario?

SELECT Salesperson, SUM(TotalSale)

FROM SALES

GROUP BY Salesperson

HAVING Salesperson <>'Bennett';

14. how to replace one string with another in unix?

Sed ‘s\ old string\ new string’  filename

15. how to remove duplicates in a file through unix

   Uniq command

DELLOIT INTERVIEW QUESTIONS

DELLOIT  INTERVIEW QUESTIONS 

1.TELL ME ABOUT ONE COMPLEX JOB WHICH YOU DEVELOPED?HOW MANY JOBS YOU CREATED IN YOUR PROJECT?

2.WHAT IS THE DIFFERENCE B/W SURRO GATE KEY AND PRIMAY KEY?

3.HOW MANY COLUMNS WE CAN DO AGGREGATION OPERATION IN AGGREGATE STAGE?

4.HOW CAN WE SEE THE CONTENTS OF THE SEQUENTIAL FILE DATA?

5.TELL ME ABOUT SEQUENTIAL FILTER?

6.IS IT POSIBLE TO REJECT THE RECORD WHICH RECORD CONTAIN ‘HEAD’?

7.HOW CAN WE DO NULL TO VALUE IN SEQUENTIAL FILE?

8.TELL ME COMMAND LINE TO RUN THE JOB?

9.TELL ME COMMAND TO PASS PARAMETER?

10.HOW I CAN CALL SHELL PROGRAM IN JOB ?'

11.HAVE U USED ANY SCRIPT?WHERE?HOW?

12.I RUN 3 JOBS IN JOBS SEQUENCE 2ND JOB HAS BEEN ABORTED HOW CAN WE DO ?HOW CAN U RUN 2ND JOB?

13. HOW CAN WE DECLARE GLOBAL PARAMETER?

RELIANCE COMPANY INTERVIEW QUESTIONS

RELIANCE COMPANY INTERVIEW QUESTIONS

1. WRITE A QUERY TO DELETE THE RECORDS WHICH ARE RECENTLY LOADED IN THE TABLE?

2. WRITE A QUERY TO VIEW nth HIGHEST SALARY EMPLOYEE DETAILS?

3. HOW CAN WE PERFORM SECOND EXTRACTION OF CLIENT DATABASE, WITHOUGHT ACCEPTING DATA WHICH WE ARE ALLREADY EXTRACTED IN FIRST EXTRATION TIME?

4. WHAT IS METERIALSED VIEW IN OUR PROJECT EXACTLY WHERE WE CAN USE IT?

5. HOW CAN WE USE PERAMETERS IN ORACLE, IN DATASTAGE?

6. WRITE A ARCHITECTURE OF PROJECT?

7. WHAT ARE THE DAY TO DAY ACTIVITIES OF ETL DEVELOPER?

8. HOW TO IMPLEMENT CHANGING DIMENSION?

9. TYPES OF BLOCKS IN PLSQL?

10. WHAT ARE IN, OUT PERAMETER IN PLSQL?

11. WRITE A QUERY TO DISPLAY DIPARTMENT WISE HIGHEST SALARY EMPLOYEE IS SENIOUR NUMBER WISE WITH USER INTRACTION?

12. WHAT IS THE CAPACITY OF EXCELL FILE (HOW MAY RECORDS IT CAN HOLD)?

13. TELL ME KIND OF PERFORMANCE TUNNING WHICH YOU IMPLEMENTED?

14. DO YOU KNOW ANY OF REPORTING TOOL?

15. HAVE WORKED ON ANY OF MIGRATION PROJECT? WHAT IS MIGRATION?

16. WHY YOUR CLIENT DON’T GIVE THE PERMITION TO ACCESS DATA DIRECTLY? INSTEAD OF THAT WHY FILES ARE PROVIDING BY HIM?

17. WHAT IS THE LEFT OUTER JOIN, TELL ME HOW CAN WE IMPLEMENTED IN ORACLE?

18. WHAT IS THE FUNCTION IN ORACLE TO SEE THE NUMBER OF DAYS BY PASSING DATE?

19. WHAT IS THE EXTENSION OF YOUR SOURCE FILE?

20. CAN YOU DO THE MODELING PART?

21. IN STAGING AREA EVERY TIME WE NEED TO LOAD THE DATA AND EVERY TIME WE NEED TO DELETE EXACTLY TELL ME WHAT IS PURPOUSE OF STAGING AREA?

22. IN WHICH TOOLS DO YOU HAVE IDIA ABOUT A PART FROM DATASTAGE?

23. EVERY INPUT FILE CONTAIN ONE SINGLE TABLE RIGHT?

24. WHAT IS THE PURPOUSE OF AGGREGATOR STAGE? WE HAVE AGGREGATION PARTITION IN ORACLE WHICH IS BETTER?

25. HAVE YOU INVOLVED ANY ORACLE SCRIPTING PART?

26. WIRTE A SHELL PROGRAMM WHICH IS EXAMPLE IF PASSING EAVEN AND ODD NUMBERS?

27. WHAT ARE THE THIRD PARTY TOOL USED IN YOUR PROJECT? TELL ME PURPOUSE OF THOSE?

IBM PUNE INTERVIEW QUESTIONS

IBM PUNE INTERVIEW QUESTIONS

IBM PUNE

1. WHAT IS FACT TABLE? WHY WE CAN USE FACT TABLE?

2.SEMI ADDITIVE FACTS& ADDITIVE FACTS

3.WHAT IS STAR SCHEMA&SNOW FLAKE SCHEMA?

4.WHAT IS RAPIDIALLY CHANGING DIMENSION?

5 DIFF B/W OLTP& OLAP . WHY WE NEED TO GO FOR OLAP IN DW ?

6.HOW TO REMOVE DUPLICATE USING TRANSFORMS?

7.WHAT IS THE USE OF NORMALIZATION?

8.WHAT IS BRIDGE TABLE WHERE WE USE ?

9.WHAT IS CONFORMED DIMENSION AN CONFORMED FACTS?

10.WHAT IS THE STAGE VARIABLE IF I TAKE STAGE VARIABLE WITH DATA TYPE VARCHAR WHAT WOULD BE THE DEFAULT VALUE?

11. WHAT IS THE USES ODBC STAGES?

12. WHAT IS THE DIFFERENCE BETWEEN JOIN AND LOOK UP STAGES?

13. IS IT POSSIBLE TO PERFORM LEFT OUTER JOIN IN MERGE AND LOOK UP?

14. USING THE SORT STAGE HOW CAN YOU SEND THE UNIQUE RECORDS TO ONE TABLE AND DUPLICATE RECORDS IN TO ANOTHER TABLE?

15. FROM SOURCE DUPLICATES I NEED TO SEND FOURTH DUPPLICATE TO THE TARGET HOW CAN YOU IMPLEMENT?

16. HOW YOU IMPLEMENT THE SCD IF MY SOURCE TABLE CONSISTING CID, CNAME, ADDRESS, PHONE NUMBER BUT I NEED TO CAPTURE THE CHANGES, FOR FIRST THREE COLUMNS HOW TO IMPLEMENT?

17. IS IT POSSIBLE TO IMPLEMENT THE SCD USING LOOK UP STAGE?

18. DIFFERENCE BETWEEN SCD AND RCD?

19. WHAT IS THE COLUMN EXPORT STAGE?

20. WHAT IS THE CDC STAGE?

21. WHAT IS THE USE OF ORACLE ENTERPRISE STAGE, WHAT ARE THE PARAMETERS YOU HAVE TO SET?

22. WHAT IS THE VERSION CONTROLLING ?

23. HOW TO REMOVE THE DUPLICATE IN ORACLE TABLE?

24. HOW WE SEE ONLY DUPLICATE RECORDS IN ORACLE TABLE?

25. WHAT IS THE EXECUTION SEQUENCE OF SELECT, WHERE, ORDER BY  GROUP BY, HAVING, CLAUSE?

26. WHAT IS THE NORMALIZATION AND DE NORMALIZATION?

27. HOW TO RELEASE THE LOCK FOR A PARTICULAR JOB UNLOCKED?

28. I HAVE THE SOURCE FROM UK, NORTH AMERICA HOW CAN I PASS THE DATA TWO TABLES BASED ON THE LOCATIONS?

29. DIFFERENCE BETWEEN 7.5 AND 8.0?

30. EXAMPLE FOR THE FACT LESS FACT TABLE FROM CURRENT PROJECT?

31. WHAT IS THE COLUMN EXPERT STAGE?

32. PROJECT FLOW ENVIRONMENT VARIABLE AND JOB PARAMETER?

Data Warehousing & DataStage

Data Warehousing & DataStage 

Data warehouse:

Data warehouse is a,

· Subject- Oriented.

· Integrated.

· Time variant.

· Non-volatile.

Data warehouse is a Subject-Oriented and Integrated Data base, designed to supplythe Information for decision making.

(Or)

Data warehouse is a repository used to manage business.

(Or)

Data warehouse is a relational database which stores complete information aboutthe transaction.

(Or)

Data warehouse is a powerful database model which can answer any type of thebusiness query (requirement).

Data warehousing:

It is a process of implementing both ETL and Reporting functions.

Data warehousing Architecture:

The following figure shows the Data ware housing Architecture.

Down load this document, here

click here to download.

Staging Area:

This is a temporary storage place which contains complete information of

the source system, there no transactions involved, while extracting the data from

source system and loading into staging tables.

Source Systems:

Generally these are a transactional database system which contains the list

of Transactions.

Ex: Oracle, DB2, Teradata, Sybase, Informatica, COBOL files, Xml files, Flat

files.

ODS (Operation Data Store):

It is a,

· Subject- Oriented.

· Integrated.

· Time variant and volatile data base system.

Tip: ODS is designed, to improve the performance of Updates.

Data Mart:

Sum amount of related information which will be capture from enterprise data

warehouse and loading into small data warehouses is called data marts.

Tip: Data Marts designed, to improve the performance of the select statement.

EDW: Enterprise Data Warehouse.

ETL :  

ETL Means E for Extraction , T for Transformation and finally L for Loading.

ETL: Extraction, Transformation and Loading.

This is the main area; the ETL developer has to concentrate and below is the

sample transformation activities.

1. Dropping unwanted columns.

2.  Adding required columns.

3. Applying filter conditions on source data.

4. Calculate aggregate functions (min, max, avg, sum...Etc).

5. Apply sorting operations.

6. Remove/ capturing the duplicate records.

7. Changing the date formats.

8. Changing the Meta data.

9. Applying join operations.

10. Applying functions (String, Number).

11. Null- rejects handling.

12. Combine the data from multiple data sources.

13. Distributing the data to multiple targets.

14. Generating sequence number.

ETL Tools:

1. Data Stage.

2. Abinitio.

3. Informatica.

4. SAP BI.

5. MS BI.

6. SSIS.

7. Pentaho.

8. SAS BASE.

9. SAS ACCESS.

10. Oracle Ware housing building.

11. Teradata Ware housing building.

12. Business objects Data Integration.

13. ETL4ALL.

14. Clever ETL.

15. MS Excel.

Programmatic GUI Tools:

· PLSQL.

· OWB.

· MS EXCEL.

· TWB.

· SAS BASE.

· SAS ACCESS.

Reporting Tools:

· Cognos.

· Business objects.

· Micro Strategy.

· Hyperian.

· Pentaho (It is an ETL & Reporting toll too.)

· OBIEE.

Differences between OLTP and OLAP:

OLTP (Online Transaction Process) :

1. It contains the current values.

2. These systems are designed to perform transactions.

3. These systems are designed to perform Insert, Updates actively &

Effectively.

4. End users can have direct access to modify the transcriptional data.

5. More joins increase the performance.

6. No duplicates, no redundancy.

7. Normalized data.

OLAP(Online Analytical Process):

1. It contains the current history.

2. These systems are designed to perform analysis.

3. These systems are designed to perform Select statements actively &

Effectively.

4. End users can’t access to modify the transcriptional data.

5. Less joins increase the performance.

6. More duplicates, more redundancy (history).

7. De normalized data.

Meta data:

Meta data is a layout which represents the actual data.

Fact table:

Fact table contains set of FK’s with respect to all the dimension tables

associated to it and fact Information. Facts are also called as “measures (or)

numerical information”.

Types of Dimensions:

1. Conformed Dimension.

2. Degenerate Dimension.

3. Junk Dimension.

4. Large/ Monster Dimension.

5. Small/ Mini Dimension.

6. Slowly Changing Dimension.

7. Rapidly changing Dimension.

8. Static Dimension.

9. Custom Dimension.

10.Dynamic Dimension.

11.Aggregate Dimension.

12. Multi valued Dimension.

Relational Database:

Relational data base provides relations between Tables.

Degenerate Dimension:

It is an attribute in the fact table and mainly generated to satisfy the

customer requirements.

Example:

Schema:

Set of tables having relations with each other.

Data stage developer has to load the data into data warehousing.

Data base average size is 1500 GB.

Junk Dimension:

The dimension which contains textual codes and flag variables is called junk

dimension.

Junk dimensions are used to reduce the memory space occupied by the data in data

warehouse and while generating the reports.

Junk dimensions are never maintaining any relationship with fact table.

Large/ monster Dimension:

If the dimension contains more number of columns (>100) or more number

of rows (>2milloins) is called Large Dimension.

Small/Mini Dimension:

If the dimension contains less number of columns (around 5) and less

number of rows (<1000) is called Small Dimension.

Slowly Changing Dimension (SCD): 

The dimension which changes slowly with respect to number of rows or

number of columns is called SCD.

There 3 types in SCD. They are:

SCD Type1:

It maintains the latest data only.

See figure SCD Type1 for further details.

SCD Type2:

It maintains complete history about all the transactions.

See figure SCD Type2 for further details.

SCD Type3:

It maintains Current, Immediate previous transaction values.

See figure SCD Type3 for further details.

Rapidly Changing Dimension (RCD):

The dimension which changes rapidly with respect to number of rows or

number of columns is called RCD.

Ten (10) to Fifteen (15) changes in a year – SCD.

More changes in a year – RCD.

Surrogate Key (SK): It is a normal primary key.

Custom Dimension:

The dimension in which the attributes are varied based on the incoming record is

called Custom Dimension.

These types of dimensions are never recommended in Data ware housing schema.

Static Dimension:

If the dimension is loaded with data and will never change, then it is called

as Static Dimension.

Dynamic dimension:

By default every dimension is dynamic dimension expect static.

Aggregate Dimension:

The dimension which is loaded at base level and is possible to calculate all

aggregate functions (min, max, sum, count) is called as Aggregate Dimension.

Multi Valued Dimension:

If the dimension contains many to many relationship with in the data

(attribute (or) values) then it is called Multi Valued Dimension.

It is not recommended in DWH.

Fact Table:

It is a large control table in DWH schemas. Contains set of foreign keys

with respect to all the dimension tables associated to it fact information and

degenerate dimensions.

Snap Shots:

It contains the information at exact point of time.

Ex: Sales Fact Table.

Cumulative Fact Tables:

It contains the information about it period.

Ex: Sales Fact Table.

Fact less Fact Table:

The fact table which does not contain fact information is called fact less fact

table.

Examples:

1. To track the attendance of a student.

2. To check the flight availability in airport or not.

3. To check the product availability in store.

The Following figure shows about the fact less fact table.

Types of Facts:

1. Additive Facts (AF).

2. Semi Additive Facts (SAF).

3. Non Additive Facts (NAF).

Additive Facts:

The facts which are calculated from any of the dimension table

independently called Additive Facts (AF).

Ex: Sales Account, Sales quantity and Revenue.

Semi Additive Facts:

The facts which are calculated from one or few dimension tables (but not all)

are called as Semi Additive Facts.

Ex: Account balance, quantity in hand.

Non Additive Facts:

The facts which are calculated by applying arithmetic operation on the

combination of additive, semi additive, non additive facts and de generate

dimensions (DD) are called as Non Additive Facts.

Ex: Avg of goods per order.

Avg no of goods per order = Sales quantity (AF) / Order no (DD)

Snow Flake Schema:

The schema not all the dimension tables are directly connect with fact table

is called as Snow Flake Schema. In Snow Flake Schema design both the dimension & fact tables are

normalized. When we keep on hitting the same dimension table many times with query

de normalized table is not suggestible. So that De generate dimension (DD) can be split into two or more normalized dimension tables.

FAQ’s in DWH

1. Difference between OLTP & OLAP?

Ans: OLTP is a Transcriptional Systems (data), operational data.

OLAP is a Data ware house, decision support system (DSS).

2. What is ODS and what are its functionalists?

3. What is Star schema and how it is differ from Snow Flake Schema design,

which is better?

4. What is dimension table, types of dimension tables (CD, DD, Junk, SCD*)?

5. Explain the functionalists of fact less fact table with example?

6. What is fact table, what are its types?

7. What are the types of facts?

8. What is data mart, what are its benefits?

9. What are the general transformation activities implemented by ETL

developer?