Data warehouse Questions

 

1. Data Warehouse /Concept?
It is the act of organizing and storing the data in a way so that we can make its retrieval efficient and insightful. It is also called as the process of transforming data into information. DWH is like a relational database, designed for analytical needs. It is a central location where consolidated data from multiple locations are stored and It functions on the basis of OLAP(Online analytical processing). 

2. OLTP VS OLAP

The basic difference between OLTP (Online Transactional Processing) and OLAP is that OLTP is an online database modifying system, whereas OLAP is an online database query answering system.

1. Online transactional data becomes the source of data for OLTP. However, the different OLTPs database becomes the source of data for OLAP.

2.OLTP’s main operations are insert, update and delete whereas, OLAP’s main operation is to extract multidimensional data for analysis.

3.OLTP has short but frequent transactions whereas, OLAP has long and less frequent transaction.

4.Processing time for the OLAP’s transaction is more as compared to OLTP.

5.OLAPs queries are more complex with respect OLTPs.

3.Type of OLAP

1. ROLAP:- It is a form of OLAP that performs dynamic multidimentional analysis of data stored in relational database rather than multidimentional DB. 

Advantage - Greater amount of data can be processed. Disadvantage- Requires more processing time and disk space.

2. MOLAP:- It is a form of OLAP which process and stores data directly into a multidimentional database. 

Advantage - Excellent Performance Disadvantage- Only Limited data can be handled

3. HOLAP:- It is a Combination of ROLAP and MOLAP.

4. What is OLAP Operations ?

Roll-up - Roll-up performs aggregation on a data cube

Drill-down - Drill-down is the reverse operation of roll-up

Slice and dice - The slice operation selects one particular dimension from a given cube and provides a new sub-cube. Dice selects two or more dimensions from a given cube and provides a new sub-cube. 

Pivot (rotate) - The pivot operation is also known as rotation. It rotates the data axes in view in order to provide an alternative presentation of data.

5. What is Schemas ?

Schemas give you the logical description of the whole database. It gives the details about the constraints placed on a table, key value present and how the key values are linked with the tables.

6. What is Dimension Table ?

Dimension tables contains the descriptive information. And every dimension table is linked with the fact table.

7. What is Fact Tables ?

A fact is measure that can be summed, averaged, or manipulated. Fact table contains 2 types of data dimension key and measure.

8. Type of Schemas ?

1.STAR Schema- Each dimesion in Star schema is representd with a one-dimensional table which contains set of attributes. Fact table is at the center which contains key to every dimension table and attribute like total sold qty, revenue.

2. SNOWFLAKE schema- dimension table in the snowflake schema are normalized(split into additional tables.)

3. GALAXY schema- also known as Fact constellation schema and contains more than one fact tables.

9. What is Data Mining ?

Data mining is set of processes of analyzing the data in different dimensions or perspective and summarizing into a useful information.

10. What is ETL Process ?

The process of extracting data from source systems and bringing it into the data warehouse is commonly called ETL, which stands for extraction, transformation, and loading.

11. What is Informatica ?

Informatica is a data integration tool based on ETL architecture. It is the most commonly used Data integration tool used for connecting & fetching data from different data source.

12. Data-Analyst-position ?

A data analyst is someone who collects, processes and performs statistical analyses of data. Who can translate numbers and data into plain English in order to help organizations and companies to understand how to make better business decisions.

13. Real Time DWH

Real time data warehousing captures the business data whenever it occurs. When there is a business activity gets completed that data will be available  for the use instantly.

14. What is Cube ?

Cubes are the logical representation of multidimensional data. The edge of the cube has the dimension members and the body contains data values.

15. What is DB Architecture ?

A DBMS architecture is depending on its design and can be Centralized and Decentralized. There are 3 types of database architecture.

1 Tier Architecture in DBMS is the simplest architecture of Database in which the client, server, and Database all reside on the same machine.

2 Tier Architecture in DBMS is a Database architecture where the presentation layer runs on a client (PC, Mobile, Tablet, etc.), and data is stored on a server called the second tier.

3 Tier Architecture in DBMS is the most popular client server architecture in DBMS in Three Tier architecture contains a presentation layer, an application layer, and a database server.

16. What is RDMS ?

Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model. The data in an RDBMS is stored in database objects which are called as tables.

17. What is Unit Testing ?

UNIT TESTING is a type of software testing where individual units or components of a software are tested. The purpose is to validate that each unit of the software code performs as expected. Unit Testing is done during the development (coding phase) of an application by the developers.