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?