Data warehousing

Question 1

what is data warehousing

Answer 1

collection of data that helps analysts to make informed decisions in an organisation, allow us to analyse data in a multi-dimensional space, results in data mining and data generalisation

Question 2

what kind of data do data warehouses store

Answer 2

very large store of historical data

Question 3

features of data warehouses

Answer 3

subject oriented
integrated
time - variant
non - volatile

Question 4

what does subject oriented mean

Answer 4

provides information around a subject rather than ongoing operations. focuses on modelling and analysis of data. examples of this are product, customers, suppliers, sales, revenue etc.

Question 5

what does integrated mean

Answer 5

constructed by integrating data from many different sources such as relation databases or flat files

Question 6

time - variant

Answer 6

data collected is identified with a particular time period e.g. in the last 12 months

Question 7

non - volatile

Answer 7

data is not erased when new data is added

Question 8

what is different in a Data warehouse over a database (only data warehouse points)

Answer 8

Queries are often complex and present a general form of data.
it is a database but there is no frequent updating
OLAP query only needs read-only access to stored data
Doesn’t require transaction processing, recovery and concurrency controls as its stored separately from the operational database

Question 9

what is different in an operational database over a database (only operational database points)

Answer 9

Built for well-known tasks and work such as searching particular records and indexing.
Support concurrent processing of multiple transactions (OLTP)
Concurrency control and recovery mechanisms are needed.
Allows to read and modify operations
maintains current data - goes through frequent changes on a daily basis

Question 10

Types of data warehouse

Answer 10

Information processing
Analytical processing
Data mining

Question 11

What is information processing

Answer 11

allows processing of data stored in it

can be processed by querying, basic statistical analysis, reporting using cross tabs, tables, charts or graphs

Question 12

What is Analytical processing

Answer 12

supports analytical processing of the data within it by means of OLAP operations
these are: slice and dice, drill down, drill up and pivot

Question 13

What is data mining

Answer 13

support knowledge discovery by finding hidden patterns and associates.
Constructing analytical models, performing classification and prediction.
Results can be presented using visualisation tools

Question 14

What is Enterprise Data Warehouse(EDW)

Answer 14

Services entire enterprise

Question 15

What might an EDW environment have

Answer 15

An EDW(enterprise data warehouse)
an operational datastore
physical and virtual data marts

Question 16

what are data marts

Answer 16

they are used by individual departments or groups

use a dimensional data model to build

Question 17

what is an operational data store

Answer 17

Subject oriented system, optimised for looking up 1 or 2 records at a time for decision making.
allows access to more current data more quickly
a hybrid form of data warehouse containing integrated information

Question 18

Is a data warehouse OLAP or OLTP

Answer 18

OLAP

Question 19

Is a operational database OLAP or OLTP

Answer 19

OLTP

Question 20

True or false? Historical processing of information is done by a Data warehouse

Answer 20

True, operational databases do day to day processing

Question 21

does a data warehouse or operational database analyse the business

Answer 21

A data warehouse analyses the business and operational databases run the business

Question 22

which would be larger in size, a data warehouse or an operational database

Answer 22

a datawarehouse(100GB - 100TB)

Question 23

what does the top tier include

Answer 23

Data mining, reporting tools for analysis and business intelligence.
Query tools, reporting tools, analysis or data mining tools

Question 24

what does the middle tier include

Answer 24

OLAP server
used to provide information to business analysts and managers
interacts with the bottom tier and passes on insights to the top tier tools

Question 25

what does the bottom tier include

Answer 25

database server extracts data from different sources using a gateway
data sources fed into the bottom tier can include operational databases and other types of front end data

Question 26

functions of data warehouse tools

Answer 26

data extraction
data cleaning
data transformation
data losing
refreshing

Question 27

what is refreshing

Answer 27

updating data sources to the warehouse

Question 28

data losing

Answer 28

sorting, summarising, consolidating, checking the integrity and building indices and patterns

Question 29

data transformation

Answer 29

convert the data to warehouse format

Question 30

data cleaning

Answer 30

finding and correcting errors in data

Question 31

data extraction

Answer 31

gather data from many different sources

Question 32

metadata

Answer 32

data about data which defines the data warehouse

used for building, maintaining and managing the data warehouse

Question 33

Fact tables

Answer 33

contain measurements of business(sales, purchase orders, shipment)
can be quite large(50 billion records, 1-5 terabytes)

Question 34

dimension tables

Answer 34

store the descriptions of the dimensions of the business (product, customer, vendor, store)
smaller than fact tables

Question 35

what are data warehouses used for

Answer 35

analysis and separate to the day-to-day operational database