Data Warehouse Interview Questions And Answers

Data Warehouse Interview Questions list for experienced

  1. Define data warehouse?
  2. What does subject-oriented data warehouse signify?
  3. List any five applications of data warehouse.
  4. What do OLAP and OLTP stand for?
  5. What is the very basic difference between data warehouse and operational databases?
  6. ist the Schema that a data warehouse system can implements.
  7. What is the difference between metadata and data dictionary?
  8. What are the approaches used by Optimizer during execution plan?
  9. What are the tools available for ETL?
  10. Store (and count) non-fact records?
  11. In a star schema, are foreign key constraints between facts and dimensions neccessary?
  12. Why NULL values are mapped as 0 in Fact tables?
  13. How to pivot data using Informatica when you have variable amount of pivot rows?
  14. What should I have in mind when building OLAP solution from scratch?
  15. Datamart vs. reporting Cube, what are the differences?
  16. What is dimensional modeling?
  17. What is Star-schema?
  18. What is data cleaning? How can we do that?
  19. What is an error log table in Informatica occurs and how to maintain it in mapping?
  20. What is loop in Data warehousing?
  21. What is VLDB?
  22. What are the various ETL tools in the Market?
  23. What is surrogate key? Where we use it? Explain with examples.
  24. What is conformed fact?
  25. What is the main difference between Inmon and Kimball philosophies of data warehousing?
  26. What is a Hash Cluster?
  27. What is Many-To-Many dimensional model in datawarehousing ?
  28. How to pivot row data using Informatica?
  29. Fact table is in normalized or de-normalized form?
  30. What is Execution Plan?
  31. What is the difference between OLAP and OLTP?
  32. What is Virtual Data Warehousing?
  33. Where the Data cube technology is used?
  34. After the generation of a report to whom we have to deploy or what we do after the completion of a report?
  35. What are data modeling and data mining? Where it will be used?
  36. What is data analysis? Where it will be used?
  37. How can we run the graph? What is the procedure for that? How can we schedule the graph in UNIX?
  38. What is the data type of the surrogate key?
  39. What is drill-through?
  40. What is snow-flake schema?
  41. Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the table space?
  42. What is Mirrored on-line Redo Log?
  43. Can a View based on another View?
  44. Can objects of the same Schema reside in different table spaces?
  45. What are the different types of Segments?
  46. Describe Referential Integrity?
  47. What is data warehousing?
  48. What is Dimension table?
  49. What is Fact table?
  50. What are the stages of data warehousing?
  51. What is Data mining?
  52. What is OLTP?
  53. What is OLAP?
  54. What is the difference between View and Materialized View?

Data Warehouse interview questions and answers on advance and basic Data Warehouse with example so this page for both freshers and experienced condidate. Fill the form below we will send the all interview questions on Data Warehouse also add your Questions if any you have to ask and for apply in Data Warehouse Tutorials and Training course just send a mail on info@pcds.co.in in detail about your self.

Top Data Warehouse interview questions and answers for freshers and experienced

What is Data Warehouse ?

Answer :

Questions : 1 :: Define data warehouse?

Data warehouse is a subject oriented, integrated, time-variant, and nonvolatile collection of data that supports management's decision-making process.

Questions : 2 :: What does subject-oriented data warehouse signify?

Subject oriented signifies that the data warehouse stores the information around a particular subject such as product, customer, sales, etc.

Questions : 3 :: List any five applications of data warehouse.


Some applications include financial services, banking services, customer goods, retail sectors, controlled manufacturing.

Questions : 4 :: What do OLAP and OLTP stand for?

OLAP is an acronym for Online Analytical Processing and OLTP is an acronym of Online Transactional Processing.

Questions : 5 :: What is the very basic difference between data warehouse and operational databases?

A data warehouse contains historical information that is made available for analysis of the business whereas an operational database contains current information that is required to run the...View answers

Questions : 6 :: ist the Schema that a data warehouse system can implements.


A data Warehouse can implement star schema, snowflake schema, and fact constellation schema.

Questions : 7 :: What is the difference between metadata and data dictionary?

Metadata is defined as data about the data. But, Data dictionary contain the information aboutthe project information, graphs, abinito commands and server information

Questions : 8 :: What are the approaches used by Optimizer during execution plan?

There are two approaches:1. Rule Based2. Cost Based

Questions : 9 :: What are the tools available for ETL?


Following are the ETL tools available:InformaticaData StageOracleWarehouse Builder

Questions : 10 :: Store (and count) non-fact records?

As you described it, that would be just a fact table. Actually, there is name for this -- factless fact table; fact table without any measures. It is quite common for recoding events. Essentially...View answers

Questions : 11 :: In a star schema, are foreign key constraints between facts and dimensions neccessary?

Most data-warehouses (DW) do not have foreign keys implemented as constraints, because: In general, foreign key constraint would trigger on: an insert into a fact table, any key-updates, and a...View answers

Questions : 12 :: Why NULL values are mapped as 0 in Fact tables?

It depends upon what you're modeling, but in general it's to avoid complications with performing aggregates. And in many scenarios it makes sense to treat NULL as 0 for those purposes. For...View answers

Questions : 13 :: How to pivot data using Informatica when you have variable amount of pivot rows?

 You should use something along the lines of this:Source->Expression->Aggregator->TargetIn the expression, add a variable port:v_count expr: IIF(ISNULL(v_COUNT) OR v_COUNT=3, 1, v_COUNT...View answers

Questions : 14 :: What should I have in mind when building OLAP solution from scratch?

If you are implementing cubes, then separate the production SQL Server from the source for the cubes. Cubes require a lot of SELECT DISTINCT column_name FROM source.table. You don't want cube...View answers

Questions : 15 :: Datamart vs. reporting Cube, what are the differences?

Cube can (and arguably should) mean something quite specific - OLAP artifacts presented through an OLAP server such as MS Analysis Services or Oracle (nee Hyperion) Essbase. However, it also gets...View answers

Questions : 16 :: What is dimensional modeling?

Dimensional model consists of dimension and fact tables. Fact tables store different transactional measurements and the foreign keys from dimension tables that qualifies the data. The goal of...View answers

Questions : 17 :: What is Star-schema?

This schema is used in data warehouse models where one centralized fact table references number of dimension tables so as the keys (primary key) from all the dimension tables flow into the fact table...View answers

Questions : 18 :: What is data cleaning? How can we do that?

Data cleaning is a self-explanatory term. Most of the data warehouses in the world source data from multiple systems - systems that were created long before data warehousing was well understood, and...View answers

Questions : 19 :: What is an error log table in Informatica occurs and how to maintain it in mapping?

Error Log in Informatica is a one of output file created by Informatica Server while running the session for error messages. It is created in Informatica home directory.

Questions : 20 :: What is loop in Data warehousing?

In DWH loops may exist between the tables. If loops exist, then query generation will take more time, because more than one path is available. It creates ambiguity also. Loops can be avoided by...View answers

Questions : 21 :: What is VLDB?

The perception of what constitutes a VLDB continues to grow. A one-terabyte database would normally be considered VLDB.Degenerate dimension: it does not have any link with dimensions and it will not...View answers

Questions : 22 :: What are the various ETL tools in the Market?

Various ETL tools used in market are Informatica Data Stage Oracle Warehouse Builder Ab Initio Data Junction

Questions : 23 :: What is surrogate key? Where we use it? Explain with examples.

Surrogate key is a substitution for the natural primary key.It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a...View answers

Questions : 24 :: What is conformed fact?

Conformed dimensions are the dimensions, which can be used across multiple Data Marts in combination with multiple facts tables accordingly

Questions : 25 :: What is the main difference between Inmon and Kimball philosophies of data warehousing?

Both differed in the concept of building the data warehouse.According to Kimball, Kimball views data warehousing as a constituency of data marts. Data marts are focused on delivering business...View answers

Questions : 26 :: What is a Hash Cluster?

A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.

Questions : 27 :: What is Many-To-Many dimensional model in datawarehousing ?

There are a few things you can do to marry this with a dimensional model / star schema: Build two stars (possibly, they'd end up in different datamarts). One has FACT as the fact table, the other...View answers

Questions : 28 :: How to pivot row data using Informatica?

If every person has two addresses, you can use the FIRST and LAST functions in an Aggregator transformation:Aggregator transformation If every person has two addresses, you can use the FIRST and...View answers

Questions : 29 :: Fact table is in normalized or de-normalized form?

From the point of relational database design theory, dimension tables are usually in 2NF and fact tables anywhere between 2NF and 6NF.However, dimensional modelling is a methodology unto itself,...View answers

Questions : 30 :: What is Execution Plan?

The combination of the steps the optimizer chooses to execute a statement is called an execution plan.

Questions : 31 :: What is the difference between OLAP and OLTP?

OLAP stands for online analytical processing. In this, we have access to live data. This process contains historical information to analyze. Data needs to be integrated. We can create reports that...View answers

Questions : 32 :: What is Virtual Data Warehousing?

A virtual or point-to-point data warehousing strategy means that end-users are allowed to get at operational databases directly using whatever tools are enabled to the "data access network"

Questions : 33 :: Where the Data cube technology is used?

A multi-dimensional structure called the data cube. A data abstraction allows one to view aggregated data from a number of perspectives. Conceptually, the cube consists of a core or base cuboids,...View answers

Questions : 34 :: After the generation of a report to whom we have to deploy or what we do after the completion of a report?

The generated report will be sent to the concerned business users through web or LAN.

Questions : 35 :: What are data modeling and data mining? Where it will be used?

Data modeling is the process of designing a data base model. In this data model data will be stored in two types of table fact table and dimension table Fact table contains the transaction data and...View answers

Questions : 36 :: What is data analysis? Where it will be used?

Data analysis: consider that you are running a business and u store the data of that; in some form say in register or in a comp and at the year end you want know the profit or loss then it called...View answers

Questions : 37 :: How can we run the graph? What is the procedure for that? How can we schedule the graph in UNIX?

If you want to run the graph through GDE then after save the graph just press F5 button of your keyboard, it will run automatically. If you want to run through the shell script then you have to fire...View answers

Questions : 38 :: What is the data type of the surrogate key?

There is no data type for a Surrogate Key. Requirement of a surrogate Key: UNIQUE Recommended data type of a Surrogate key is NUMERIC.

Questions : 39 :: What is drill-through?

Drill through is the process of going to the detail level data from summary data. Consider the above example on retail shops. If the CEO finds out that sales in East Europe has declined this year...View answers

Questions : 40 :: What is snow-flake schema?

This is another logical arrangement of tables in dimensional modeling where a centralized fact table references number of other dimension tables; however, those dimension tables are further...View answers

Questions : 41 :: Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the table space?

All the default storage parameters defined for the table space can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS values cannot be changed.

Questions : 42 :: What is Mirrored on-line Redo Log?

A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks; changes made to one member of the group are made to all members.

Questions : 43 :: Can a View based on another View?

yes.

Questions : 44 :: Can objects of the same Schema reside in different table spaces?

yes.

Questions : 45 :: What are the different types of Segments?

Data Segment, Index Segment, Rollback Segment and Temporary Segment

Questions : 46 :: Describe Referential Integrity?

A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a...View answers

Questions : 47 :: What is data warehousing?

Answer- A data warehouse is the repository of a data and it is used for management decision support system.Datawarehouse consists of wide variety of data that has a high level of business conditions...View answers

Questions : 48 :: What is Dimension table?

Answer- Dimension table is a table which contains attributes of measurements stored in a fact tables.This table consists of hierarchies ,categories and logic that can be used to traverse in...View answers

Questions : 49 :: What is Fact table?

Answer- Fact table contains the measurement of business processes and it contains foreign keys for the dimension tables. Example-nIf the business process is manufacturing of bricks. Average number...View answers

Questions : 50 :: What are the stages of data warehousing?

Answer- there are 4 stages of data warehousing- 1.Offline Operational database 2.Offline Data warehouse 3.Real time datawarehouse 4.Integrated...View answers

Questions : 51 :: What is Data mining?

Answer- Data mining is set to be a process of analyzing the data in different dimensions or perspectives and summarizing into a useful information.Can be queried and retrieved the data from database...View answers

Questions : 52 :: What is OLTP?

Answer- OLTP is on line transaction processing and is an application that modifies the data whenever it received and has large number of simultaneous...View answers

Questions : 53 :: What is OLAP?

Answer- OLAP IS online analytical processing and is set to be a system which collects,manages,processes multi dimensional data for analysis and management...View answers

Questions : 54 :: What is the difference between View and Materialized View?

Answer- A view is a virtual table which takes the output of the query and it can be used in place of tables. A materialized view is an indirect access to the table data by storing the results of a...View answers
More Question

Ask your interview questions on Data Warehouse

Write Your comment or Questions if you want the answers on Data Warehouse from Data Warehouse Experts
Name* :
Email Id* :
Mob no* :
Question
Or
Comment* :
 





Disclimer: PCDS.CO.IN not responsible for any content, information, data or any feature of website. If you are using this website then its your own responsibility to understand the content of the website

--------- Tutorials ---