About Warehouse,,,basic Questions....!!

                                     

Data Warehouse Interview Questions And Answers

What is data warehouse?

A data warehouse is a electronic storage of an Organization’s historical data for the purpose of Data Analytics, such as reporting, analysis and other knowledge discovery activities.

What is meant by Data Analytics?

Data analytics (DA) is the science of examining raw data with the purpose of drawing conclusions about that information. A data warehouse is often built to enable Data Analytics.

What is dimension?

A dimension is something that qualifies a quantity (measure).

What is Virtual Data Warehousing?

A virtual data warehouse provides a collective view of the completed data. A virtual data warehouse has no historic data. It can be considered as a logical data model of the containing metadata.

What is an Enterprise Data Warehouse Bus Matrix?

An enterprise data warehouse bus matrix is a document of the data warehouse bus architecture.

What is a junk dimension?

A junk dimension, also referred to as an indicator or transaction profile dimension is a separate dimension table that contains flags and indicators which have been removed from a fact table.

How do you load the time dimension?

Time dimensions are usually loaded by a program that loops through all possible dates appearing in the data. It is not unusual for 100 years to be represented in a time dimension, with one row per day.

What are the different types of SCD’s used in data warehousing?

SCD (Slowly changing dimensions), are the dimensions in which the data changes slowly, rather than changing regularly on a time basis.

Three types of SCDs are used in data warehousing, which are defined as: 

– SCD1: It is a record that is used to replace the original record even there is only one record existing in the database. The current data will be replaced and the new data will take its place.

– SCD2: It is the new record file that is added to the dimension table. This record exists in the database with the current data and previous data that is stored in the history.

– SCD3: This uses the original data that is modified to the new data. This consists of two records: one record that exist in the database and another record that will replace the old database record with the new information.

What is the difference between view and materialized view?

View:

– Tail raid data representation is provided by a view to access data from its table.

– It has logical structure that does not occupy space.

– Changes get affected in corresponding tables.

Materialized view:

– Pre-calculated data persists in materialized view.

– It has physical data space occupation.

– Changes will not get affected in corresponding tables.

Define Transformation ?

Transformation is the core of the reconciliation phase. It converts data from its operational source format into a specific data warehouse format. If you implement a three-layer architecture, this phase outputs your reconciled data layer.

What are operational DBMS?

They consist of Tables having attributes and are populated by tuples.

• They generally use the E-R data model.

• It is used to store transactional data.

• The information content is generally recent.

• These are thus called as OLTP systems.

• Their goals are data accuracy & consistency , Concurrency , Recoverability, Reliability (ACID Properties).

What are the features of  Warehouse?

It is separate from Operational Database.

Integrates data from heterogeneous systems.

Stores HUGE amount of data, more historical than current data.

Does not require data to be highly accurate.

Queries are generally complex.
Goal is to execute statistical queries and provide results which can influence decision making in favor of the Enterprise.
These systems are thus called Online Analytical Processing Systems (OLAP).

What does the data warehouse provide for Business Analysts?

Presents relevant information useful for measuring performance and evaluation issues.

Enhances business productivity by quick and efficient gathering of information.

Facilitates customer relationship management by providing a consistent view of customers across all lines of business, all departments, and all markets.

Brings cost reduction by tracking trends, patterns and exceptions overlong periods of time in a consistent and reliable manner.