Data Warehouse Developer Interview Questions And Answers

Chameleon is a hierarchical clustering algorithm that overcomes the limitations of the existing models and the methods present in the data warehousing. This method operates on the sparse graph having nodes: that represent the data items, and edges: representing the weights of the data items.
This representation allows large dataset to be created and operated successfully. The method finds the clusters that are used in the dataset using two phase algorithm.

☛ The first phase consists of the graph partitioning that allows the clustering of the data items into large number of sub-clusters.
☛ Second phase uses an agglomerative hierarchical clustering algorithm to search for the clusters that are genuine and can be combined together with the sub-clusters that are produced.

Hybrid SCDs are a combination of both SCD 1 and SCD 2.
It may happen that in a table, some columns are important and we need to track changes for them i.e., capture the historical data for them whereas in some columns even if the data changes, we do not have to bother.
For such tables, we implement Hybrid SCDs, where in some columns are Type 1 and some are Type 2.

Both differ in the concept of building the data warehouse.

☛ Kimball views data warehousing as a constituency of Data marts. Data marts are focused on delivering business objectives for departments in the organization. And the data warehouse is a conformed dimension of the data marts. Hence, a unified view of the enterprise can be obtained from the dimension modeling on a local departmental level.
☛ Inmon explains in creating a data warehouse on a subject-by-subject area basis. Hence, the development of the data warehouse can start with data from the online store. Other subject areas can be added to the data warehouse as their needs arise. Point-of-sale (POS) data can be added later if management decides it is necessary.
☛ Hence, Kimball–First Data Marts–Combined way —Data warehouse
☛ Inmon—First Data warehouse–Later—-Data marts

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

The view over an operational data warehouse is known as virtual warehouse.

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

Snowflake schema uses the concept of normalization.

Data Marting involves hardware & software cost, network access cost, and time cost.

Cluster analysis is used to define the object without giving the class label. It analyzes all the data that is present in the data warehouse and compare the cluster with the cluster that is already running. It performs the task of assigning some set of objects into the groups also known as clusters. It is used to perform the data mining job using the technique like statistical data analysis. It includes all the information and knowledge around many fields like machine learning, pattern recognition, image analysis and bio-informatics. Cluster analysis performs the iterative process of knowledge discovery and includes trials and failures. It is used with the pre-processing and other parameters as a result to achieve the properties that are desired to be used.

Purpose of cluster analysis :-

☛ Scalability
☛ Ability to deal with different kinds of attributes
☛ Discovery of clusters with attribute shape
☛ High dimensionality
☛ Ability to deal with noisy
☛ Interpretability

☛ Snapshot refers to a complete visualization of data at the time of extraction. It occupies less space and can be used to back up and restore data quickly.
☛ A snapshot is a process of knowing about the activities performed. It is stored in a report format from a specific catalog. The report is generated soon after the catalog is disconnected.

☛ In scenarios where certain data may not be appropriate to store in the schema, this data (or attributes) can be stored in a junk dimension. The nature of data of junk dimension is usually Boolean or flag values.
☛ A single dimension is formed by lumping a number of small dimensions. This dimension is called a junk dimension. Junk dimension has unrelated attributes. The process of grouping random flags and text attributes in dimension by transmitting them to a distinguished sub dimension is related to junk dimension.

A very large database, or VLDB, is a database that contains an extremely large number of tuples (database rows), or occupies an extremely large physical file system storage space. A one terabyte database would normally be considered to be a VLDB.

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

The dimensions are the entities with respect to which an enterprise keeps the records.

Summary Information is the area in data warehouse where the predefined aggregations are kept.

Normalization splits up the data into additional tables.

Partitioning is done for various reasons such as easy management, to assist backup recovery, to enhance performance.

There are four types of OLAP servers, namely Relational OLAP, Multidimensional OLAP, Hybrid OLAP, and Specialized SQL Servers.

Data mart contains the subset of organization-wide data. This subset of data is valuable to specific groups of an organization. In other words, we can say that a data mart contains data specific to a particular group.

Data Warehousing involves data cleaning, data integration and data consolidations.

☛ Conformed dimensions are the dimensions which can be used across multiple Data Marts in combination with multiple facts tables accordingly.
☛ A conformed dimension is a dimension that has exactly the same meaning and content when being referred from different fact tables. A conformed dimension can refer to multiple tables in multiple data marts within the same organization.

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.

A fact table is usually designed at a low level of Granularity. This means that we need to find the lowest level of information that can store in a fact table.
e.g.Employee performance is a very high level of granularity. Employee_performance_daily, employee_perfomance_weekly can be considered lower levels of granularity.
The granularity is the lowest level of information stored in the fact table. The depth of data level is known as granularity. In date dimension, the level could be year, month, quarter, period, week, day of granularity.
The process consists of the following two steps:
☛ Determining the dimensions that are to be included
☛ Determining the location to locate the hierarchy of each dimension of information. The above factors of determination will be resent to the requirements.

☛ 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.
☛ Virtual data warehousing is a ‘de facto’ information system strategy for supporting analytical decision making. It is one of the best ways for translating raw data and presenting it in the form that can be used by decision makers. It provides semantic map – which allows the end user for viewing as virtualized.

DMQL is based on Structured Query Language (SQL).