ADBS - Data Warehousing

Question 1

What is data warehousing?

Answer 1

Data warehousing is a set of hardware and software components used to better analyse data to make better business decisions.

Question 2

What are the benefits of data warehousing?

Answer 2

• Understand business trends and make better forecasting decisions
• Bring better products to market in a more timely manner
• Analyse daily sales information and make quick decisions that can significantly affect your company’s performance

ROI - return on investment can range between 40% and 600%

Competitive Advantage - discover trends, customers and demands

Increase in productivity from decision makers - contains integrated database of consistent, subject oriented, historical data

Transforms data into meaningful information

Question 3

Name 3 applications of data warehousing

Answer 3

• Sales and marketing analysis across all industries
• Inventory turn and product tracking in manufacturing
• Category management, vendor analysis, and marketing program effectiveness analysis in retail
• Profitability analysis or risk assessment in banking
• Claims analysis or fraud detection in insurance

Question 4

What is a data warehouse?

Answer 4

A data warehouse is a subject-oriented, integrated, non-volatile, and time-variant collection of data in support of management’s decisions. (Inmon05)

Question 5

What is subject oriented?

Answer 5

data stored in a warehouse is about the major subjects of an enterprise (such as customers, products, and sales)

Question 6

What is integrated?

Answer 6

source data for a warehouse is from different enterprise-wide applications systems. The data is often inconsistent, for example, in different formats. The source data must be integrated into a consistent format in order to present a unified view of the data to users.

Question 7

What is non-volatile?

Answer 7

New data is always added as a supplement to the database, rather than a replacement.

The database continually absorbs this new data, incrementally integrating it with the previous data.

Question 8

What is time-variant?

Answer 8

data in a warehouse is only accurate and valid at some point in time or over some time interval.

Question 9

What is Operational Data?

Answer 9

data used to run business. This data is normally stored, retrieved, and updated by an Online Transactional Processing (OLTP) system. An OLTP system may be, for example, a reservations system, an accounting application, or an order entry application.

Question 10

What are the characteristics of operational data?

Answer 10

Updated often and through online transactions

Non-historical data (not more than three to six months old)

Optimised for transactional processing

Highly normalised in the relational database for easy update, maintenance, and integrity

Question 11

What is informational data?

Answer 11

Informational data is typically stored in a format that makes analysis much easier. Analysis can be in the form of decision support (queries), report generation, executive information systems, and more in-depth statistical analysis.

Question 12

What are the characteristics of informational data?

Answer 12

Summarised operational data
 De-normalised and replicated data
 Infrequently updated from the operational systems
 Optimised for decision support applications
 Possibly “read only” (no updates allowed)
 Stored on separate systems to lessen impact on operational systems

Question 13

What are differences between operational data and informational data

Answer 13

Operational Data
current, detailed, dynamic, repetitive processing, transaction driven, application oriented, day to day, serves clerical/operational users

Informational
historical data, summarised, static data, ad hoc unstructured heuristic, analysis driven, subject oriented, managerial, strategic

Question 14

Name 10 problems of data warehousing

Answer 14

Underestimate resources for data loading
Hidden problems with source systems
Required data not captured
Increased end user demands
data homogenisation
high demand for resources - space
data ownership - sensitivity is lost
high maintenance
time to implement
difficulty in integration

Question 15

Describe the architecture of a data warehouse

Answer 15

Operational data, ODS, Load Manager, Warehouse Manager, Query manager,

Question 16

In the architecture, describe the operational data

Answer 16

data from multiple sources sych as hierarchical or network databases, VSAM, RMS, Relational DBMS, private data, data from workstations or servers

Question 17

In the architecture, describe the operational data store

Answer 17

this is a repository of operational and integrated operational data used for analysis, already extracted and cleaned

Question 18

In the architecture, describe the Load Manager

Answer 18

responsible for extracting and loading data into the warehouse

Question 19

In the architecture, describe the Warehouse manager

Answer 19

Management of data in the warehouse
analyzes data for consistency
transforms and merges data
create index and views
aggregates data
back up and archiving

Question 20

In the architecture, describe the Query manager

Answer 20

management of user queries and directs to appropriate data source

consists of end user access tools, monitoring tools, OLAP, data mining

Question 21

What is meta data?

Answer 21

data about data
map data to a common view of data
automate the production of summary tables
direct to most appropriate data source
helps to define data source, consists of technical and business data.

Question 22

What is a data mart?

Answer 22

a subset of a data warehouse used to support the requirements of a particular department

Question 23

Name three differences between a Data Mart and a Data Warehouse

Answer 23

focuses on requirements of users within department
does not contain detailed operational data
contains substantially less data making it easier to navigate

Question 24

Name 5 reasons for implementing a data mart

Answer 24

reduced cost and time

users can access the data they need

less management of data - reduction in volume of data to be accessed

Less data - cleansing, loading, transformation and integration is easier

provide appropriately structured data in conjunction with end-user access tools

all users have the same collective view of the data in a particular department

easily targeted users for department as opposed to corporation

Question 25

Name 6 issues of Data Marts

Answer 25

``` functionality size load performance accessing data in multiple data marts administration user access data mart versioning, integrity and metadata installation ```

Question 26

What is dimensional modelling?

Answer 26

A logical design technique that aims to present the data in a standard, intuitive form that allows for high-performance access.

Question 27

what are the characteristics of dimensional modelling?

Answer 27

uses the concepts of er modelling composed of one table with composite primary key (fact table) contains smaller tables called dimension tables

Question 28

What is the star schema?

Answer 28

it is a database structure that has a fact table surrounded by dimension tables containing reference data

Question 29

what are facts?

Answer 29

these are generated by past events and are unlikely to change

Question 30

what are dimensions?

Answer 30

dimensions contain descriptive textual information. Used as constraints in data warehouse queries

Question 31

What is the snowflake schema?

Answer 31

simila to teh star schema however dimension tables do not contain denormalised data

Question 32

What is the starflake schema?

Answer 32

this is a mixture of star and snowflake schemas which contains denormalised star and normalised snowflake

Question 33

What are the advantages of dimensional modelling?

Answer 33

Efficiency - efficient access to data Change - Handles changing requirements, supports ad hoc queries Extensible - add new facts consistent with granularity, add new dimensions, add new attributes to dimensions Model business situations Query processing is predictable

Question 34

What are the differences between dimensional modelling and entity relational modelling?

Answer 34

ER modelling usually a database for OLTP, identifies relationships among entities, removes redundancy in data, normalised, focused on transactional processing Dimensional modelling usually a component of the data warehouse, supports ad-hoc queries, decomposed into multiple DM from ER

Question 35

What is the 4 step proces for dimensional modelling design for data warehouses?

Answer 35

# Choose the process - what is teh subject matter of the data mart Declare the grain - What is the fact table going to represent e.g. Identify Dimensions - Dimensions set the context for asking questions about the facts in the fact table. A well built set of dimensions makes the data mart understandable and easy to use. Identify facts - The grain of the fact table determines which facts can be used in the data mart. All the facts must be expressed at the level implied by the grain.

Question 36

What are the issues of choosing a DBMS?

Answer 36

potential size parallelism in the database performance scalability availability manageability

Question 37

What are the requirements of a DBMS?

Answer 37

Load performance - incremental loading frequently, must be done in minimum amount of time Load processing - steps to load new or updated data like conversions, filtering, reformatting, integrity checks, physical storage, indexing data quality management query performance terabyte scalability user scalability concurrency with users Networking data warehouses Administration Integrated dimensional analysis Advanced query functionality