Deck 9: Data Warehouse Implementation and Use

Question 1

If applicable,the archived data from operational sources is loaded in the data warehouse during the:

A)Refresh load
B)First load
C)Final load
D)Archived data can never be loaded in the data warehouse

Answer 1

B

Question 2

Data cleansing is a part of:

A)OLAP
B)Extraction
C)Transformation
D)Load

Answer 2

C

Question 3

OLAP/BI tools can be used for:

A)Ad-hoc direct analysis only
B)Creation of front-end (BI)applications only
C)Ad-hoc direct analysis and creation of front-end (BI)applications
D)Neither ad-hoc direct analysis nor creation of front-end (BI)applications

Answer 3

C

Question 4

GENERICDATA
Observe the tables GENERICDIMENSION and GENERICFACT:

GENERICDIMENSION
$\begin{array} { l l l l l l } A A & B B & C C & D D & E E & F F \\ A A 1 & B B 1 & C C 1 & D D 1 & E E 1 & F F 1 \\ A A 2 & B B 2 & C C 1 & D D 2 & E E 2 & F F 1 \\ A A 3 & B B 3 & C C 2 & D D 3 & E E 1 & F F 1 \\ A A 4 & B B 4 & C C 2 & D D 4 & E E 2 & F F 1 \\ A A 5 & B B 5 & C C 2 & D D 5 & E E 3 & F F 1 \end{array}$
GENERICFACT
$\begin{array} { l c c } \text { AA } & \ldots & \text { Number } \\ \text { AA1 } & \ldots & 100 \\ \text { AA1 } & \ldots & 100 \\ A A 2 & \ldots & 200 \\ \text { AA2 } & \ldots & 200 \\ A A 3 & \ldots & 300 \\ A A 3 & \ldots & 300 \\ A A 4 & \ldots & 400 \\ A A 4 & \ldots & 400 \\ \text { AA5 } & \ldots & 100 \end{array}$ Assume the following two hierarchies exist in the table GENERICDIMENSION:
AA --> BB --> CC
AA --> DD --> EE --> FF
Assume the following result table was created using the OLAP/BI Tool:

GENERICOLAPRESULT1
$$\begin{array} { l r l } & C C 1 & C C 2 \\E E 1 & 200 & 600 \\E E 2 & 400 & 800 \\E E 3 & 0 & 100\end{array}$$
This scenario will be used for the following questions citing GENERICDATA.




-The result table below is the result of a ________ action on the GENERICOLAPRESULT1 result table.
$E E 1$
$\begin{array} { l l } \mathrm { CC } 1 & 200 \\ \mathrm { CC } 2 & 600 \end{array}$
EE2
$\begin{array} { l l } \mathrm { CC } 1 & 400 \\ \mathrm { CC } 2 & 800 \end{array}$
EE3
$\begin{array} { r r } C C 1 & 0 \\ C C 2 & 100 \end{array}$

A)Slice and dice
B)Pivot
C)Drill-up
D)Drill-down

Question 5

Querying and presenting data from data warehouses and/or data marts for analytical purposes is known as:

A)ETL
B)OLAP
C)OLTP
D)Micro batches

Question 6

GENERICDATA
Observe the tables GENERICDIMENSION and GENERICFACT:

GENERICDIMENSION
$\begin{array} { l l l l l l } A A & B B & C C & D D & E E & F F \\ A A 1 & B B 1 & C C 1 & D D 1 & E E 1 & F F 1 \\ A A 2 & B B 2 & C C 1 & D D 2 & E E 2 & F F 1 \\ A A 3 & B B 3 & C C 2 & D D 3 & E E 1 & F F 1 \\ A A 4 & B B 4 & C C 2 & D D 4 & E E 2 & F F 1 \\ A A 5 & B B 5 & C C 2 & D D 5 & E E 3 & F F 1 \end{array}$
GENERICFACT
$\begin{array} { l c c } \text { AA } & \ldots & \text { Number } \\ \text { AA1 } & \ldots & 100 \\ \text { AA1 } & \ldots & 100 \\ A A 2 & \ldots & 200 \\ \text { AA2 } & \ldots & 200 \\ A A 3 & \ldots & 300 \\ A A 3 & \ldots & 300 \\ A A 4 & \ldots & 400 \\ A A 4 & \ldots & 400 \\ \text { AA5 } & \ldots & 100 \end{array}$ Assume the following two hierarchies exist in the table GENERICDIMENSION:
AA --> BB --> CC
AA --> DD --> EE --> FF
Assume the following result table was created using the OLAP/BI Tool:

GENERICOLAPRESULT1
$$\begin{array} { l r l } & C C 1 & C C 2 \\E E 1 & 200 & 600 \\E E 2 & 400 & 800 \\E E 3 & 0 & 100\end{array}$$
This scenario will be used for the following questions citing GENERICDATA.




-The result table below is the result of a ________ action on the GENERICOLAPRESULT1 result table.
$$\begin{array} { r r r } & \mathcal { C C 1 } & \mathcal { C C } 2 \\F F 1 & 600 & 1500\end{array}$$

A)Slice and dice
B)Pivot
C)Drill-up
D)Drill-down

Question 7

OLAP tools are:

A)Write-only tools
B)Read-only tools
C)Read and write tools
D)Read,write,and delete tools

Question 8

Which of the following processes is typically the most time- and resource-consuming?

A)Creating the data warehouse tables using the DBMS functionalities
B)Creating the OLAP query
C)Creating the ETL infrastructure
D)Creating the data warehouse model

Question 9

GENERICDATA
Observe the tables GENERICDIMENSION and GENERICFACT:

GENERICDIMENSION
$\begin{array} { l l l l l l } A A & B B & C C & D D & E E & F F \\ A A 1 & B B 1 & C C 1 & D D 1 & E E 1 & F F 1 \\ A A 2 & B B 2 & C C 1 & D D 2 & E E 2 & F F 1 \\ A A 3 & B B 3 & C C 2 & D D 3 & E E 1 & F F 1 \\ A A 4 & B B 4 & C C 2 & D D 4 & E E 2 & F F 1 \\ A A 5 & B B 5 & C C 2 & D D 5 & E E 3 & F F 1 \end{array}$
GENERICFACT
$\begin{array} { l c c } \text { AA } & \ldots & \text { Number } \\ \text { AA1 } & \ldots & 100 \\ \text { AA1 } & \ldots & 100 \\ A A 2 & \ldots & 200 \\ \text { AA2 } & \ldots & 200 \\ A A 3 & \ldots & 300 \\ A A 3 & \ldots & 300 \\ A A 4 & \ldots & 400 \\ A A 4 & \ldots & 400 \\ \text { AA5 } & \ldots & 100 \end{array}$ Assume the following two hierarchies exist in the table GENERICDIMENSION:
AA --> BB --> CC
AA --> DD --> EE --> FF
Assume the following result table was created using the OLAP/BI Tool:

GENERICOLAPRESULT1
$$\begin{array} { l r l } & C C 1 & C C 2 \\E E 1 & 200 & 600 \\E E 2 & 400 & 800 \\E E 3 & 0 & 100\end{array}$$
This scenario will be used for the following questions citing GENERICDATA.




-The result table below is the result of a ________ action on the GENERICOLAPRESULT1 result table.
$$\begin{array} { r r r r r r } && { C C 1 } && { C C 2 } \\& D D 1 & D D 2 & D D 3 & D D 4 & D D 5 \\E E 1 & 200 & 0 & 600 & 0 & 0 \\E E 2 & 0 & 400 & 0 & 800 & 0 \\E E 3 & 0 & 0 & 0 & 0 & 100\end{array}$$

A)Slice and dice
B)Pivot
C)Drill-up
D)Drill-down

Question 10

ALSETMAX COMPANY
Observe the three different tables that exist in the ALSETMAX COMPANY and contains the information about customers.Assume all the values in all tables are correct.All three tables will be used as the source tables for the dimension CUSTOMER in the ALSETMAX COMPANY dimensionally modeled data warehouse.
ALSE TMAX CUSTOMERS
$\begin{array} { c l l c } \text { SSN } & \text { Name } & \text { Gemder } & \text { CustBirthYear } \\ 111 - 11 - 1111 & \text { Ned } & \text { Male } & 1980 \\ 222 - 22 - 2222 & \text { Nelly } & \text { Female } & 1990 \\ 333 - 33 - 3333 & \text { Anna } & \text { Female } & 1989 \\ 444-44 - 4444 & \text { Ned } & \text { Male } & 1970 \end{array}$ \\
A-MAX CUSTOMER TABLE
$\begin{array} { c c c c } \text { GSN } & \text { Nane } & \text { Sex } & \text { YearOfBirth } \\ 111 - 11 - 1111 & \text { Ned } & \text { M } & 1980 \\ 222 - 22 - 2212 & \text { Nelly } & F & 1990 \\ 333 - 33 - 3333 & \text { Anna } & F & 1989 \\ 555 - 55 - 5555 & \text { Anna } & F & 1970 \end{array}$ \\
AM CUSTOMER ZIPCODE INFO
$\begin{array} { c c } \text { SSN } & \text { ZipCode } \\ 111 - 11 - 1111 & 60611 \\ 222 - 22 - 2122 & 60655 \\ 333 - 33 - 3333 & 60601 \\ 444 - 44 - 4444 & 60611 \\ 555 - 55 - 5555 & 60655 \end{array}$

This scenario will be used for the following questions citing ALSETMAX COMPANY.

-Based on the three source tables,how many columns will the dimension CUSTOMER in the populated star schema for ALSETMAX COMPANY have? (Assume that the dimension uses a surrogate key,but no time stamps or row indicators. )

A)6
B)7
C)8
D)10

Question 11

Updating,querying,and presenting data from databases for operational purposes is known as:

A)ETL
B)OLAP
C)OLTP
D)Micro batches

Question 12

GENERICDATA
Observe the tables GENERICDIMENSION and GENERICFACT:

GENERICDIMENSION
$\begin{array} { l l l l l l } A A & B B & C C & D D & E E & F F \\ A A 1 & B B 1 & C C 1 & D D 1 & E E 1 & F F 1 \\ A A 2 & B B 2 & C C 1 & D D 2 & E E 2 & F F 1 \\ A A 3 & B B 3 & C C 2 & D D 3 & E E 1 & F F 1 \\ A A 4 & B B 4 & C C 2 & D D 4 & E E 2 & F F 1 \\ A A 5 & B B 5 & C C 2 & D D 5 & E E 3 & F F 1 \end{array}$
GENERICFACT
$\begin{array} { l c c } \text { AA } & \ldots & \text { Number } \\ \text { AA1 } & \ldots & 100 \\ \text { AA1 } & \ldots & 100 \\ A A 2 & \ldots & 200 \\ \text { AA2 } & \ldots & 200 \\ A A 3 & \ldots & 300 \\ A A 3 & \ldots & 300 \\ A A 4 & \ldots & 400 \\ A A 4 & \ldots & 400 \\ \text { AA5 } & \ldots & 100 \end{array}$ Assume the following two hierarchies exist in the table GENERICDIMENSION:
AA --> BB --> CC
AA --> DD --> EE --> FF
Assume the following result table was created using the OLAP/BI Tool:

GENERICOLAPRESULT1
$$\begin{array} { l r l } & C C 1 & C C 2 \\E E 1 & 200 & 600 \\E E 2 & 400 & 800 \\E E 3 & 0 & 100\end{array}$$
This scenario will be used for the following questions citing GENERICDATA.




-The result table below is the result of a ________ action on the GENERICOLAPRESULT1 result table.
$$\begin{array} { l l l } & \text { CC1 } & \text { CC2 } \\E E 1 & 200 & 600 \\E E 2 & 400 & 800\end{array}$$

A)Slice and dice
B)Pivot
C)Drill-up
D)Drill-down

Question 13

The set of processes that is used to populate the data warehouse tables with the appropriate relevant data retrieved from the operational databases is known as:

A)BI
B)ETL
C)OLAP
D)OLTP

Question 14

The ETL infrastructure is essentially predetermined by the results of the requirements collection and data warehouse modeling processes that specify:

A)The OLAP/BI tools
B)The alpha release
C)The sources and the target
D)The refresh cycle

Question 15

In the case of so-called active data warehouses,the loads occur in ________ that happen continuously,ensuring that the data in the data warehouse is updated close to real time (enabling analysis of the latest data).

A)Dimensional modeling actions
B)OLAP actions
C)OLTP actions
D)Micro batches

Question 16

GENERICDATA
Observe the tables GENERICDIMENSION and GENERICFACT:

GENERICDIMENSION
$\begin{array} { l l l l l l } A A & B B & C C & D D & E E & F F \\ A A 1 & B B 1 & C C 1 & D D 1 & E E 1 & F F 1 \\ A A 2 & B B 2 & C C 1 & D D 2 & E E 2 & F F 1 \\ A A 3 & B B 3 & C C 2 & D D 3 & E E 1 & F F 1 \\ A A 4 & B B 4 & C C 2 & D D 4 & E E 2 & F F 1 \\ A A 5 & B B 5 & C C 2 & D D 5 & E E 3 & F F 1 \end{array}$
GENERICFACT
$\begin{array} { l c c } \text { AA } & \ldots & \text { Number } \\ \text { AA1 } & \ldots & 100 \\ \text { AA1 } & \ldots & 100 \\ A A 2 & \ldots & 200 \\ \text { AA2 } & \ldots & 200 \\ A A 3 & \ldots & 300 \\ A A 3 & \ldots & 300 \\ A A 4 & \ldots & 400 \\ A A 4 & \ldots & 400 \\ \text { AA5 } & \ldots & 100 \end{array}$ Assume the following two hierarchies exist in the table GENERICDIMENSION:
AA --> BB --> CC
AA --> DD --> EE --> FF
Assume the following result table was created using the OLAP/BI Tool:

GENERICOLAPRESULT1
$$\begin{array} { l r l } & C C 1 & C C 2 \\E E 1 & 200 & 600 \\E E 2 & 400 & 800 \\E E 3 & 0 & 100\end{array}$$
This scenario will be used for the following questions citing GENERICDATA.




-The result table below is the result of a ________ action on the GENERICOLAPRESULT1 result table.
$$\begin{array} { l l l c } & E E 1 & E E 2 & E E 3 \\C C 1 & 200 & 400 & 0 \\C C 2 & 600 & 800 & 100\end{array}$$

A)Slice and dice
B)Pivot
C)Drill-up
D)Drill-down

Question 17

ALSETMAX COMPANY
Observe the three different tables that exist in the ALSETMAX COMPANY and contains the information about customers.Assume all the values in all tables are correct.All three tables will be used as the source tables for the dimension CUSTOMER in the ALSETMAX COMPANY dimensionally modeled data warehouse.
ALSE TMAX CUSTOMERS
$\begin{array} { c l l c } \text { SSN } & \text { Name } & \text { Gemder } & \text { CustBirthYear } \\ 111 - 11 - 1111 & \text { Ned } & \text { Male } & 1980 \\ 222 - 22 - 2222 & \text { Nelly } & \text { Female } & 1990 \\ 333 - 33 - 3333 & \text { Anna } & \text { Female } & 1989 \\ 444-44 - 4444 & \text { Ned } & \text { Male } & 1970 \end{array}$ \\
A-MAX CUSTOMER TABLE
$\begin{array} { c c c c } \text { GSN } & \text { Nane } & \text { Sex } & \text { YearOfBirth } \\ 111 - 11 - 1111 & \text { Ned } & \text { M } & 1980 \\ 222 - 22 - 2212 & \text { Nelly } & F & 1990 \\ 333 - 33 - 3333 & \text { Anna } & F & 1989 \\ 555 - 55 - 5555 & \text { Anna } & F & 1970 \end{array}$ \\
AM CUSTOMER ZIPCODE INFO
$\begin{array} { c c } \text { SSN } & \text { ZipCode } \\ 111 - 11 - 1111 & 60611 \\ 222 - 22 - 2122 & 60655 \\ 333 - 33 - 3333 & 60601 \\ 444 - 44 - 4444 & 60611 \\ 555 - 55 - 5555 & 60655 \end{array}$

This scenario will be used for the following questions citing ALSETMAX COMPANY.

-Based on the data in the three source tables,how many records in the dimension CUSTOMER in the populated star schema for ALSETMAX COMPANY represent a female customer?

A)2
B)3
C)4
D)5

Question 18

Which of the following is NOT true? Front-end (BI)applications are used to retrieve the data from:

A)Dependent data marts
B)ETL infrastructure
C)Independent data marts
D)Data warehouse

Question 19

ALSETMAX COMPANY
Observe the three different tables that exist in the ALSETMAX COMPANY and contains the information about customers.Assume all the values in all tables are correct.All three tables will be used as the source tables for the dimension CUSTOMER in the ALSETMAX COMPANY dimensionally modeled data warehouse.
ALSE TMAX CUSTOMERS
$\begin{array} { c l l c } \text { SSN } & \text { Name } & \text { Gemder } & \text { CustBirthYear } \\ 111 - 11 - 1111 & \text { Ned } & \text { Male } & 1980 \\ 222 - 22 - 2222 & \text { Nelly } & \text { Female } & 1990 \\ 333 - 33 - 3333 & \text { Anna } & \text { Female } & 1989 \\ 444-44 - 4444 & \text { Ned } & \text { Male } & 1970 \end{array}$ \\
A-MAX CUSTOMER TABLE
$\begin{array} { c c c c } \text { GSN } & \text { Nane } & \text { Sex } & \text { YearOfBirth } \\ 111 - 11 - 1111 & \text { Ned } & \text { M } & 1980 \\ 222 - 22 - 2212 & \text { Nelly } & F & 1990 \\ 333 - 33 - 3333 & \text { Anna } & F & 1989 \\ 555 - 55 - 5555 & \text { Anna } & F & 1970 \end{array}$ \\
AM CUSTOMER ZIPCODE INFO
$\begin{array} { c c } \text { SSN } & \text { ZipCode } \\ 111 - 11 - 1111 & 60611 \\ 222 - 22 - 2122 & 60655 \\ 333 - 33 - 3333 & 60601 \\ 444 - 44 - 4444 & 60611 \\ 555 - 55 - 5555 & 60655 \end{array}$

This scenario will be used for the following questions citing ALSETMAX COMPANY.

-Based on the data in the three source tables,how many records in the dimension CUSTOMER in the populated star schema for ALSETMAX COMPANY represent a customer named Ned?

A)1
B)2
C)3
D)4

Question 20

ALSETMAX COMPANY
Observe the three different tables that exist in the ALSETMAX COMPANY and contains the information about customers.Assume all the values in all tables are correct.All three tables will be used as the source tables for the dimension CUSTOMER in the ALSETMAX COMPANY dimensionally modeled data warehouse.
ALSE TMAX CUSTOMERS
$\begin{array} { c l l c } \text { SSN } & \text { Name } & \text { Gemder } & \text { CustBirthYear } \\ 111 - 11 - 1111 & \text { Ned } & \text { Male } & 1980 \\ 222 - 22 - 2222 & \text { Nelly } & \text { Female } & 1990 \\ 333 - 33 - 3333 & \text { Anna } & \text { Female } & 1989 \\ 444-44 - 4444 & \text { Ned } & \text { Male } & 1970 \end{array}$ \\
A-MAX CUSTOMER TABLE
$\begin{array} { c c c c } \text { GSN } & \text { Nane } & \text { Sex } & \text { YearOfBirth } \\ 111 - 11 - 1111 & \text { Ned } & \text { M } & 1980 \\ 222 - 22 - 2212 & \text { Nelly } & F & 1990 \\ 333 - 33 - 3333 & \text { Anna } & F & 1989 \\ 555 - 55 - 5555 & \text { Anna } & F & 1970 \end{array}$ \\
AM CUSTOMER ZIPCODE INFO
$\begin{array} { c c } \text { SSN } & \text { ZipCode } \\ 111 - 11 - 1111 & 60611 \\ 222 - 22 - 2122 & 60655 \\ 333 - 33 - 3333 & 60601 \\ 444 - 44 - 4444 & 60611 \\ 555 - 55 - 5555 & 60655 \end{array}$

This scenario will be used for the following questions citing ALSETMAX COMPANY.

-Based on the data in the three source tables,how many records will the dimension CUSTOMER in the populated star schema for ALSETMAX COMPANY have?

A)4
B)5
C)8
D)13

Question 21

GENERIC RETAILER
Observe the ER diagram for the Generic Retailer:
Assume that the dimension PRODUCT containing the following columns:
ProductKey (surrogate key)
ProductID
ProductDescription
ProductBrandID
ProductBrandName
ProductCategoryID
ProductCategoryName
is created for the Generic Retailer.This dimension will be used for the following questions citing Generic Retailer PRODUCT dimension.
A user using an OLAP query (based on the dimensionally modeled data set that contains the ABC Retailer dimension PRODUCT)can drill up from ProductID to ProductCategoryID.

Question 22

OLAP/BI tools are designed for the analysis of dimensionally modeled data.

Question 23

The process of deciding what to extract during the extraction process begins after the requirements and modeling stages are completed.

Question 24

What is a first load?

Question 25

GENERIC RETAILER
Observe the ER diagram for the Generic Retailer:
Assume that the dimension PRODUCT containing the following columns:
ProductKey (surrogate key)
ProductID
ProductDescription
ProductBrandID
ProductBrandName
ProductCategoryID
ProductCategoryName
is created for the Generic Retailer.This dimension will be used for the following questions citing Generic Retailer PRODUCT dimension.
A user using an OLAP query (based on the dimensionally modeled data set that contains the Generic Retailer dimension PRODUCT)can drill up from ProductBrandID to ProductCategoryID.

Question 26

Developing front-end applications includes creating a set of predeveloped queries and interfaces to those queries.

Question 27

The word online in the term OLAP refers to the use of the Internet or other networks.

Question 28

The data in fact tables is always located faster in relational implementations than in the multidimensional implementations.

Question 29

The purpose of the drill up operation is to make the granularity of the data in the query result coarser.

Question 30

What takes place during the transformation part of ETL?

Question 31

Creating a data warehouse involves using the functionalities of database management software to implement the data warehouse model as a collection of physically created and mutually connected database tables.

Question 32

The multidimensional database cube always has three dimensions.

Question 33

GENERIC RETAILER
Observe the ER diagram for the Generic Retailer:
Assume that the dimension PRODUCT containing the following columns:
ProductKey (surrogate key)
ProductID
ProductDescription
ProductBrandID
ProductBrandName
ProductCategoryID
ProductCategoryName
is created for the Generic Retailer.This dimension will be used for the following questions citing Generic Retailer PRODUCT dimension.
A user using an OLAP query (based on the dimensionally modeled data set that contains the Generic Retailer dimension PRODUCT)can drill down from ProductBrandID to ProductID.

Question 34

Archived data from source systems is never present in the data warehouse.

Question 35

The process of transformation cannot involve standardizing different versions of the same data present in different data sources.

Question 36

The purpose of the drill down operation is to make the granularity of the data in the query result finer.

Question 37

What takes place during the load part of ETL?

Question 38

What takes place during the extraction part of ETL?

Question 39

GENERIC RETAILER
Observe the ER diagram for the Generic Retailer:
Assume that the dimension PRODUCT containing the following columns:
ProductKey (surrogate key)
ProductID
ProductDescription
ProductBrandID
ProductBrandName
ProductCategoryID
ProductCategoryName
is created for the Generic Retailer.This dimension will be used for the following questions citing Generic Retailer PRODUCT dimension.
A user using an OLAP query (based on the dimensionally modeled data set that contains the Generic Retailer dimension PRODUCT)can drill down from ProductBrandID to ProductCategoryID.

Question 40

What is a refresh load?

Question 41

Illustrate a typical HOLAP architecture.

Question 42

What is a data warehouse beta release?

Question 43

What is a data warehouse production release?

Question 44

What is a data warehouse alpha release?

Question 45

What is an executive dashboard?

Question 46

What enables direct lookup of values in the fact table in a multidimensional cube?

Question 47

What is the multidimensional database model?

Question 48

Illustrate a typical ROLAP architecture.

Question 49

Illustrate a typical MOLAP architecture.

Question 50

What is a refresh cycle?