Data Warehouse Manager Interview Questions And Answers

A Datawarehouse is the repository of a data and it is used for Management decision support system. Datawarehouse consists of wide variety of data that has high level of business conditions at a single point in time.

In single sentence, it is repository of integrated information which can be available for queries and analysis.

The business process of the dimensional modeling includes:
Choose The Business Process:In this, 4-step design method is followed that helps to provide the usability of the dimensional model. This allows the business process to be more systematic in representation and more helpful in explaining it as well. It includes the use of Business Process Modelling Notation (BPMN) or Unified Modelling Language (UML).

Declaring The Grain:After choosing the business process, the declaration of the model comes that consists of grains. The grain of the model provides the accurate description of the dimensional model and allows the focus should be shifted there.

Identify The Dimensions:In this phase, the identification of the dimension takes place in the dimensional model. The dimensions are defined in the grain that is defined in the declaration part above. Dimensions acts as a foundation of the fact table where the data gets collected that comes under the fact.

Identify The Facts:Defining the dimensions provides a way to create a table in which the fact data can be stored. These facts are populated on the basis of the numerical figures and facts.

The warehouse manager performs consistency and referential integrity checks, creates the indexes, business views, partition views against the base data, transforms and merge the source data into the temporary store into the published data warehouse, backs up the data in the data warehouse, and archives the data that has reached the end of its captured life.

The functions performed by Data warehouse tool and utilities are
☛ Data Extraction,
☛ Data Cleaning,
☛ Data Transformation,
☛ Data Loading and Refreshing.

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

Non-additive measures are those which can not be used inside any numeric aggregation function (e.g. SUM(), AVG() etc.). One example of non-additive fact is any kind of ratio or percentage. Example, 5% profit margin, revenue to asset ratio etc. A non-numerical data can also be a non-additive measure when that data is stored in fact tables, e.g. some kind of varchar flags in the fact table.

OLTP is the transaction system that collects business data. Whereas OLAP is the reporting and analysis system on that data.

OLTP systems are optimized for INSERT, UPDATE operations and therefore highly normalized. On the other hand, OLAP systems are deliberately denormalized for fast data retrieval through SELECT operations.

In a departmental shop, when we pay the prices at the check-out counter, the sales person at the counter keys-in all the data into a "Point-Of-Sales" machine. That data is transaction data and the related system is a OLTP system.

On the other hand, the manager of the store might want to view a report on out-of-stock materials, so that he can place purchase order for them. Such report will come out from OLAP system.

Execution Plan is a plan which is used to the optimizer to select the combination of the steps.

ER modeling will have logical and physical model but Dimensional modeling will have only Physical model.

ER Modeling is used for normalizing the OLTP database design whereas Dimensional Modeling is used for de-normalizing the ROLAP and MOLAP design.

Metadata is defined as data about the data. The metadata contains information like number of columns used, fix width and limited width, ordering of fields and data types of the fields.

There are three types of SCD and they are as follows:

SCD 1 – The new record replaces the original record

SCD 2 – A new record is added to the existing customer dimension table

SCD 3 – A original data is modified to include new data

Non-Addictive facts are said to be facts that cannot be summed up for any of the dimensions present in the fact table. If there are changes in the dimensions, same facts can be useful.

OLAP is abbreviated as Online Analytical Processing, and it is set to be a system which collects, manages, processes multi-dimensional data for analysis and management purposes.

Business Intelligence is also known as DSS – Decision support system which refers to the technologies, application and practices for the collection, integration and analysis of the business related information or data. Even, it helps to see the data on the information itself.

Operational systems are optimized to preserve the data integrity of the system, whereas data warehouse are optimized to speed up the process of data analysis.

Operational system increases the speed of the business transactions through the use of normalization of the database and using the entity relationship models, whereas data warehouse uses de-normalization and dimension based model to speed the data retrieval.

Operational system uses relational databases to maintain the relationship between the tables. It also consists of insert and update process that takes very less time hence increment in the performance of the system to create the transaction. Whereas, data warehouse store the same data multiple times to keep the aggregation of the data and gather the data from the operational systems.

The key features that are in the chameleon are:

The chameleon method determines the pair of similar sub-clusters that can be connected with other clusters. It also finds the closeness of the clusters from one another.

The chameleon with the above property overcomes the limitation that is present in agglomerative hierarchical model.

It uses different methods to take the internal characteristics of the clusters and matches with those which are already present.

It doesn't depend on static model that is supplied by the user and uses automated functions to perform the merging of the clusters that are already associated in the cluster.

Normalization helps in reducing data redundancy.

Query Manager is responsible for directing the queries to the suitable tables.

Metadata respiratory contains definition of data warehouse, business metadata, operational metadata, data for mapping from operational environment to data warehouse, and the algorithms for summarization.

Semi-additive measures are those where only a subset of aggregation function can be applied. Let’s say account balance. A sum() function on balance does not give a useful result but max() or min() balance might be useful. Consider price rate or currency rate. Sum is meaningless on rate; however, average function might be useful.

Data marts are generally designed for a single subject area. An organization may have data pertaining to different departments like Finance, HR, Marketing etc. stored in data warehouse and each department may have separate data marts. These data marts can be built on top of the data warehouse.

There are two approaches:

☛ Rule Based
☛ Cost Based

Following are the steps to be followed to build the datawaerhouse:

☛ Gathering business requirements
☛ Identifying the necessary sources
☛ Identifying the facts
☛ Defining the dimensions
☛ Defining the attributes
☛ Redefine the dimensions and attributes if required
☛ Organize the Attribute hierarchy
☛ Define Relationships
☛ Assign unique Identifiers

Yes, dimension table can have numeric value as they are the descriptive elements of our business.

BUS schema consists of suite of confirmed dimension and standardized definition if there is a fact tables.