Data Warehouse

Question 1

What is a data warehouse?

Answer 1

It is a database designed for analytical needs.

Can be SQL, NoSQL.

It is consolidated data from multiple locations.

Question 2

What is the very basic difference between a data warehouse and an operational database?

Answer 2

Data warehouse
- contains historical information which helps in analyzing business metrics

• data warehouse is used to mainly read data
• end users are business analysts/data analysts

Operational Database
- Contains current information that is required to run the business

• Database is mainly used to write the data
• End users are ops team members

Question 3

What is data warehousing?

Answer 3

• Data warehousing is the act of organizing & storing data in a way so as to make its retrieval efficient and insightful.
• it is also called as the process of transforming data into information.

Question 4

What is OLAP?

Answer 4

• OLAP (Online Analytical Processing) is a flexible way to make complicated analysis of multidimensional data.
• Data present in a data warehouse is accessed by running OLAP queries. DBs however, are queried by running OLTP (Online transaction processing) operations.
• OLAP activities are performed by converting the multi-dimensional data in a warehouse into an OLAP cube.
• OLAP data are de normalized.

Question 5

What is an OLAP cube?

Answer 5

A multidimensional array of data with any more than 3 dimensions.

Question 6

What is OLTP?

Answer 6

• OLTP stands for online transaction processing.
• OLTP queries are used to perform DML operations within a database. e.g. INSERT, UPDATE, DELETE
• OLTP uses data stored in the form of two dimensional tables by rows and columns

Question 7

How is OLAP different from OLTP?

Answer 7

OLAP is used to perform real-time analysis on a database where the data is de-normalized. OLAP data is archived and historical. Queries for OLAP are slow and complex, involving joins and aggregations. Used for READ or SELECT operations.

OLTP is designed to be fast query processing, maintained in multi-access environments and effectiveness is designed at number of transactions per second. Used specifically for INSERT, DELETE, and UPDATE operations.

Question 8

What is a dimension table?

Answer 8

• a dimension table is a structure that categorizes facts and measures in order to answer a business question
• dividing a data warehouse project into dimensions provide structured information for analysis and reporting.

Question 9

What is a fact table?

Answer 9

• a table which contains a measure of the dimensions in a dimension table.
• the fact table contains the measures of the dimension table and the dimension table contains the facts derived from the fact table using aggregations, etc.

Question 10

What is the level of granularity within a fact table?

Answer 10

• the depth of the data is known as data granularity
• a fact table is designed to be a low level of granularity
• for example, date dimension can be day, month, year within a fact table.

Question 11

What is the difference between additive, semi-additive, and non-additive facts?

Answer 11

• an additive fact is a measure in a fact table that can be fully summed across any of the dimensions associated with it. An example of additive fact are sales purchased from a store. You can add date information to get sales / (hour or week or month). Likewise, you can get sales data per region.
• a semi-additive fact is a measure in a fact table that can be summed across some dimensions associated with it, but not all. For example, a checking account. It doesn’t make sense to add the balance amounts from October, November, and December.
• a non-additive fact is measure in a fact table that cannot be summed across any of the dimensions associated with it.

Question 12

What is a factless fact table

Answer 12

• factless fact tables are the fact tables which do not contain numeric fact column in the fact table.
• essentially a factless table does not have any measures. it is an intersection of dimensions which contains only keys. In other words, a many-to-many table.

Question 13

What are conformed dimensions?

Answer 13

• a conformed dimension is a single dimension composed of multiple dimensions which can be shared across multiple data marts. For example, the time, product, and staff dimensions are shared by the sales and inventory data marts.

-

Question 14

What are conformed facts?

Answer 14

• a conformed fact is a shared fact which is used in more than one fact table and can be shared among multiple data marts.

Question 15

What are aggregate tables?

Answer 15

• Aggregate tables are tables which contain the existing warehouse data, grouped to certain levels of dimensions. It is easy to retrieve data from the aggregated tables than the original table which has more number of records.
• this table reduces the load in the database server and increases the performance of the query.

Question 16

What is summary information?

Answer 16

• an area in the data warehouse where predefined aggregations are kept.

Question 17

What is ETL?

Answer 17

• ETL stands for extract, transform, and load.
• It is the process of using a software to extract the desired data from various sources, then transform that data by using rules and lookup tables to meet your requirements, then loading it into a target data warehouse.

Question 18

What are the tools available for ETL?

Answer 18

Informatica Powercenter, SQL server integration services (SSIS), SAP data services, data migrator (IBI), IBM infosphere information server, SAS data management, data junction, Oracle warehouse builder.

Question 19

What is a data mart?

Answer 19

• a smaller version of the data warehouse which deals with a single subject.
• data marts are focused on one area. Hence, they draw data from a limited number of sources.
• time taken to build data marts is very less compared to the time taken to build a data warehouse.
• for example, a data warehouse can be subdivided into sales, marketing, and operations data within the data mart.

Question 20

What is metadata?

Answer 20

• defined as data about data.
• Metadata in a data warehouse defines the source data i.e. flat file, relational database, and other objects.
• metadata is used to define which table is source and target, and which concept is used to build business logic called transformation to the actual output.

Question 21

What is data mining?

Answer 21

Data mining is the process of analyzing data in different dimensions & summarizing it into useful info. Data is searched, retrieved and analyzed from a data warehouse to answer business questions.

Question 22

What’s the difference between a data warehouse and data mining?

Answer 22

Data warehouse is about storing analytical data in a structure suitable for data mining. This analytical data is extracted from operational systems usually on a daily basis.

Question 23

List the types of OLAP servers

Answer 23

Multi-dimensional OLAP: MOLAP is a form of OLAP that processes and stores the data directly into a multidimensional database. Benefit is, it can perform complex calculations b ut, only limited data can be handled.

• Relational OLAP: ROLAP is a form of OLAP that performs analysis of multidimensional data stored in a relational database rather than a multidimensional database. Greater amount of data can be processed but it requires more processing time/disk space.
• Hybrid OLAP: HOLAP is a combination of advantages of MOLAP and ROLAP. HOLAP can drill through from the cube into underlying relational data.

Question 24

Which one is faster, multidimensional or relational OLAP?

Answer 24

Multidimensional OLAP is faster than relational OLAP.

Question 25

What are the operations that can be performed by an OLAP cube?

Answer 25

- Roll-up: performs aggregation on a data cube by either 1. climbing up a concept hierarchy for a dimension 2. dimension reduction roll up is an aggregation on top of an aggregation. For example, you can aggregate products sales by product which is an aggregate function. A roll up aggregate function can summarize total sales of the products. - Pivot: a rotation operation which transposes the axes in order to provide an alternative presentation of data.

Question 26

How many dimensions are selected in a slice operation?

Answer 26

Only one dimension is selected for the slice operation.

Question 27

How many dimensions are selected in a dice operation?

Answer 27

two or more dimensions are selected for a given cube.

Question 28

What is normalization?

Answer 28

- Normalization is the process of splitting up the data into multiple tables. - different normalized forms of the data are 1NF, 2NF, and 3NF. The process converting data into their normal forms is called normalizing.

Question 29

What's the benefit of normalization?

Answer 29

it helps in reducing data redundancy.

Question 30

What is an ER diagram?

Answer 30

Entity-relationship diagram. Illustrates the interrelationships between the various entities in a database.

Question 31

is an ER diagram implemented in a data warehouse?

Answer 31

No, ER diagram is not implemented in a data warehouse. We make use of dimensional modeling in a data warehouse.

Question 32

What is called as dimensional modeling?

Answer 32

- dimensional modelling is a concept which can be used by data warehouse deigners to build thier own data warehouse. This model can be stored in two types of tables - Facts and dimension table. Fact tables has the facts and measurements of the business and dimension tables contain the context of measurements. - Types of dimensional modelling are: 1) conceptual modeling 2) logical modeling 3) physical modeling

Question 33

What is SCD?

Answer 33

- slowly changing dimensions is a dimension that stores and manages both current and historical data over time in a data warehouse. It is considered and implemented as one of the most critical ETL tasks in tracking the history of dimension records.

Question 34

What are the three types of SCDs?

Answer 34

- 1) Type 1, Overwriting; Type 1 SCD the new data overwrites the existing data. The existing data is lost as it is not stored anywhere else. - 2) Type 2 SCD - Creating another dimension record; retains the full history of values. When the value of a chosen attribute changes, the current record is closed. A new record is created with the changed data values and this new record becomes the current record. Each record contains the effective time and expiration time to identify the time period between which the record was active. - 3) Type 3 SCD - Creating a current value field; Type 3 SCD stores two versions of values for certain selected level attributes. Each record stores the previous value and the current value of the selected attribute. When the value of any of the selected attribute changes, the current value is stored as the old value and the new value becomes the current value.

Question 35

What's type 1 SCD?

Answer 35

- 1) Type 1, Overwriting; Type 1 SCD the new data overwrites the existing data. The existing data is lost as it is not stored anywhere else.

Question 36

What's type 2 SCD?

Answer 36

Type 2 SCD - Creating another dimension record; retains the full history of values. When the value of a chosen attribute changes, the current record is closed. A new record is created with the changed data values and this new record becomes the current record. Each record contains the effective time and expiration time to identify the time period between which the record was active.

Question 37

What's type 3 SCD?

Answer 37

Type 3 SCD - Creating a current value field; Type 3 SCD stores two versions of values for certain selected level attributes. Each record stores the previous value and the current value of the selected attribute. When the value of any of the selected attribute changes, the current value is stored as the old value and the new value becomes the current value.

Question 38

What are the key columns in a dimension tables?

Answer 38

- The key column in dimension tables are the primary keys of entity tables.

Question 39

What are the key columns in a fact table?

Answer 39

- the key column of a fact table are the foreign keys of entity tables.

Question 40

Which schemas do data warehouses implement?

Answer 40

- each dimension in a star schema is represented with a one-dimensional table which contains a set of attributes