5.2 Quiz: Data Warehousing & Business Intelligence

Question 1

A good datawarehouse schema to track events is

a star schema.
a snowflake schema.
a factless fact table.
a fact constellation.

Answer 1

a factless fact table.

Star Schema:
Definition: A data warehouse schema design where a central fact table is connected to dimension tables in a star-like structure.
Characteristics: Simple, with one main fact table at the center and dimension tables surrounding it. Facilitates efficient querying and reporting.

Snowflake Schema:
Definition: A variation of the star schema where dimension tables are normalized into multiple related tables, creating a snowflake-like shape.
Characteristics: More normalized than a star schema, which can save storage space. However, it may introduce additional complexity in querying.

Factless Fact Table:
Definition: A fact table that lacks measures (numeric values) but captures the relationships between dimensions.
Use Cases: Useful for scenarios where you want to record events or associations without numerical data, such as tracking attendance or relationships between entities.

Fact Constellation:
Definition: A data warehouse design with multiple fact tables connected to shared dimension tables.
Characteristics: Involves multiple star or snowflake schemas sharing common dimensions. Suitable for complex analytical scenarios where different types of facts need to be analyzed together.

Question 2

A data warehouse is integrated because information from different functional domains within an organization are brought together with external data in one centralized system.

True
False

Answer 2

True

Question 3

Which OLAP operation is shown below?
2x8x4 -> 2x3x2

Dice
Roll-up
Slice
Drill-down

Answer 3

Dice

Question 4

OLTP databases are usually normalized in order to optimize query performance. This is necessary to support users with quick results for complex queries. A normalized structure will improve the speed with which information can be retrieved from the database.

True
False

Answer 4

False

normalized to reduce redundancy and improve data integrity

denormalization= For systems where quick query performance is a priority,

Question 4

Analytics is an example of

verification-based Business Intelligence.
discovery-oriented Business Intelligence.

Answer 4

discovery-oriented Business Intelligence.

Question 5

An independent datawarehouse pulls data directly from the datawarehouse.

True
False

Answer 5

False ->indepent directly
dependent -> directly

Question 6

Hybrid OLAP (HOLAP) tries to combine the best of both MOLAP and ROLAP. An RDBMS can then be used to store the detailed data in a relational data warehouse whereas the pre-computed aggregated data can be kept as a multidimensional array managed by a MDBMS.

True
False

Answer 6

True

Question 7

In a star schema, the foreign key in a dimension table, refers to a single record in the fact table.

True
False

Answer 7

False;corresponds to multiple records

As the name suggests, a star schema has one large central fact table which is connected to various smaller dimension tables. As illustrated in this figure, the fact table has multiple foreign keys referring to each of the dimension tables, implementing a 1:N relationship type. The primary key of the fact table consists of the composition of all these foreign keys.

Question 8

Which statement about Hybrid OLAP (HOLAP) is CORRECT?

Hybrid OLAP (HOLAP) tries to combine the best of both MOLAP and ROLAP.

In HOLAP, an RDBMS can be used to store the detailed data in a relational data warehouse whereas the pre-computed aggregated data can be kept as a multidimensional array managed by a MDBMS.

In HOLAP , the OLAP analysis can first start from the multidimensional database. If more detail is needed (e.g., during drill-down), the analysis can shift to the relational database.

All statements are correct.

Answer 8

All statements are correct.

Question 9

Which statement about data marts is NOT CORRECT?

A data mart is a scaled down version of a data warehouse aimed at meeting the information needs of a homogeneous small group of end-users such as a department or business unit (e.g., marketing, finance, logistics, HR, etc.).

A data mart typically contains some form of aggregated data and is used as the primary source for report generation and analysis by this end-user group.

Data marts provide focused content such as finance, sales or accounting information, in a format tailored to the user group at hand.

Data marts can also improve query performance by offloading complex queries, and therefore workload, from other data sources (e.g., a data warehouse).

All statements are correct.

Answer 9

All statements are correct.

Question 10

A snowflake schema

normalizes the dimension tables.
denormalizes the dimension tables.

Answer 10

normalizes the dimension tables.

Question 11

Consider the following statement : a datawarehouse always contains up to date information. This statement is

True
False

Answer 11

false

A data warehouse is a large, centralized repository of data that is used for reporting and analysis. Unlike operational databases (OLTP databases), which are designed for transactional processing and often contain real-time or near-real-time data, data warehouses are typically updated periodically, such as daily, weekly, or monthly.

Question 12

Consider the following statement: datawarehouses are usually designed at the logical level since there is no conceptual data model (e.g., ER, UML) available. This statement is

True
False

Answer 12

True

Question 13

In case front-end tools such as reporting applications and data mining software get direct access to the operational databases, i.e. without the use of a true datawarehouse and/or OLAP server, we denote this as a “virtual datawarehouse”.

True
False

Answer 13

True

Question 14

The non-volatile characteristic of datawarehouse implies that the data is

read-only.
write-only.
both read-only and write-only.

Answer 14

read-only

Question 15

Independent data marts are standalone systems, drawing data directly from the operational systems, external sources or a combination of both.

True
False

Answer 15

True

Question 16

OLAP is an example of verification based Business Intelligence (BI).

True
False

Answer 16

True

Question 17

What statement about Multidimensional OLAP (MOLAP) is NOT CORRECT?

MOLAP can be fast in terms of data retrieval but it needs more storage to accomplish this.

MOLAP scales easily when the number of dimensions increases.

MDBMSs make use of proprietary data structures and data manipulation languages (DML), so no universal SQL-like standard is provided for data handling, which impedes their adoption.

MDBMSs are not optimized for transaction processing. Updating, inserting or deleting data is usually quite inefficient.

Answer 17

MOLAP scales easily when the number of dimensions increases.

Question 18

Operational systems focus on

INSERT, UPDATE, DELETE and/or SELECT statements.
only INSERT statements.
only UPDATE statements.
only SELECT statements.
only DELETE statements.

Answer 18

INSERT, UPDATE, DELETE and/or SELECT statements.

Question 19

In a star schema, every foreign key out of the set of foreign keys forming the composed key of a fact table, refers to one dimension table.

True
False

Answer 19

True

Question 20

In OLAP, accessing information from 2 or more connected fact tables is an example of

drill-across
drill-up
drill-down
roll-across

Answer 20

drill-across

Drill-Across: This operation involves navigating through different fact tables to combine data from multiple sources. It allows users to analyze data from different dimensions and levels across separate but related fact tables.

Question 21

An Operational Data Store (ODS) provides no query facilities.

True
False

Answer 21

False

An Operational Data Store (ODS) typically does provide query facilities. An ODS is a database that is used for short-term storage and integration of operational data from various sources. It acts as an intermediate storage and processing layer between the operational systems and the data warehouse.

Question 22

A datawarehouse is non-volatile because the records are never overwritten.

True
False

Answer 22

True

Question 23

One part of the ETL process consists in cleaning, integrating and transforming the input data. For most organizations, the design and implementation of these ETL functionalities requires a lot of effort, given that most data sources in organizations not integrated and contain many errors.

True
False

Answer 23

True

Question 24

What statement about Multidimensional OLAP (MOLAP) is NOT CORRECT? MOLAP stores the multidimensional data using a Multidimensional DBMS (MDBMS) whereby the data is stored in a multi-dimensional array-based data structure optimized for efficient storage and quick access. In MOLAP, the array cells represent the index keys of the array, whereas the dimensions contain the actual fact data (e.g., sales). In MOLAP, also totals can be precomputed and stored in the array. A potential problem with MOLAP is that the array may get sparse with many 0 s if only a limited number of combinations of dimension values occur.

Answer 24

In MOLAP, the array cells represent the index keys of the array, whereas the dimensions contain the actual fact data (e.g., sales). ## Footnote the array cells do not represent the index keys of the array; rather, the array cells contain the actual fact data (e.g., sales). The dimensions in MOLAP represent the index keys.

Question 25

Which OLAP operation is shown below? 4x4x4 -> 4x4x2 Dice Roll-up Slice Drill-down

Answer 25

Roll-up ## Footnote Roll-up= reduce dimension by aggregating it (aka reduce data, but keep dimension) Drill down= opposite of roll-up, open up a dimension aka show observations. Slice= remove a dimension (eg. from 3 dimension to 2 dimension) Dice= selects multiple fixed vamues form a dimension pivot= changes x and y-axis from place, keeps all data

Question 26

Which statement is NOT CORRECT? A virtual data warehouse can be built as a set of SQL queries directly on the underlying operational data sources as you see here. A virtual datawarehouse can be built as an extra layer on top of a collection of physical independent data marts as illustrated here. A virtual datawarehouse should provide a uniform and consistent metadata model and data manipulation language (e.g., SQL). A key advantage of virtualization is that it is easy to keep track of historical data.

Answer 26

A key advantage of virtualization is that it is easy to keep track of historical data. ## Footnote Virtualization itself is a technology or approach that abstracts and separates the physical implementation details from the logical representation of data. It is not inherently focused on keeping track of historical data. Virtualization can be applied to various aspects of computing, including storage, network, and data. tracking historical data is more closely associated with concepts like data warehousing and temporal databases.

Question 27

Normalisation is typically not applied to datawarehouses because of query performance: users want to have quick results for complex queries and a denormalized structure will improve the speed at which the results are retrieved from the database. True False

Answer 27

True

Question 28

What type of schema is shown below? middle fact table linked to 3 table which in turn are linked to other tabales Snowflake schema Star schema Multidimensional schema HOLAP schema

Answer 28

Snowflake schema

Question 29

What type of schema is shown below? 3 tables linked to a middle table Snowflake schema Star schema Multidimensional schema HOLAP schema

Answer 29

star schema

Question 30

Which statement about query and reporting is NOT CORRECT? Query and reporting tools provide a user-friendly graphical user interface (GUI) where the business user can graphically and interactively design a report. It is not a business user doing the query and reporting, but the IT expert. Some tools provide an intermediate Query by Example (QBE) facility that sits in between the database and the business concepts. The idea is that a query is composed in a user-friendly way by visualizing database tables whereby the business user can enter conditions for each field that needs to be included in the query. This can then be translated to a formal data manipulation language such as SQL. Query and reporting tools implement innovative visualization techniques aimed at making interesting data patterns stand out more prominently.

Answer 30

It is not a business user doing the query and reporting, but the IT expert.

Question 31

Which statement is CORRECT? Traditional databases were primarily developed for these operational decisions and have a strong transactional focus in which many transactions need to be processed in small units of time. At the tactical level, decisions are made by middle management with a medium-term focus (e.g., a month, a quarter, a year). At the strategic level, decisions are made by senior management with long term implications (e.g., 1, 2, 5 years or more). All statements are correct.

Answer 31

All statements are correct.

Question 32

An datawarehouse is called volatile or time-variant because new data is continuously added to the database. True False

Answer 32

False

Question 33

A virtual datawarehouse or data mart always contains Outdated data Up-to-date data

Answer 33

Up-to-date data

Question 34

A fact constellation schema can have more than one fact table. True False

Answer 34

True

Question 35

A data mart which pulls its data directly from a central data warehouse is a dependent data mart independent data mart

Answer 35

dependent

Question 36

Which statement about an Operational Data Store (ODS) is NOT CORRECT? An Operational Data Store (ODS) deals with the disadvantage of data warehouses not containing up to date data. An ODS can be considered as a staging area that provides query facilities. Analysis tools that need data that is closer to real time, can query the ODS data as it is received from the respective source systems, before time consuming transformation and loading operations. An ODS and datawarehouse can never be combined.

Answer 36

An ODS and datawarehouse can never be combined.

Question 37

Adding data to a datawarehouse usually happens daily or weekly by means of the so-called ETL-process. During this Extract, Transform and Load process, data from internal (operational) databases and data from external sources is processed in a so-called data staging area. This is a database used to manipulate the data before it is once and for all loaded into the (enterprise) datawarehouse. True False

Answer 37

True

Question 38

Which statement about pivot or cross-tables is NOT CORRECT? A pivot or cross-table essentially cross-tabulates a set of dimensions in such a way that multidimensional data can be represented in a two-dimensional tabular format. A pivot table also contains row and column totals. BI tools also provide various user-friendly graphical facilities to customize the pivot table by dragging and dropping dimensions of interest. Pivoting means that the rows and columns always stay fixed and cannot be rotated. Pivot tables typically also provide drill-down facilities whereby either a dimension is further de-aggregated into more detail or new dimensions are added.

Answer 38

Pivoting means that the rows and columns always stay fixed and cannot be rotated.

Question 39

Which statement about the ETL (Extraction, Transformation and Loading) is NOT CORRECT? According to a full extraction strategy, only the changes since the previous extraction are considered, which is also called Changed Data Capture or CDC. During extraction, it is important to properly accommodate the different types of data sources, operating systems, and hardware environments from where the data is sourced. The transformation usually entails the following activities: formatting, cleansing, aggregation and merging, and enrichment. During the loading step, the data warehouse is populated by filling the fact and dimension tables hereby also generating the necessary surrogate keys to link it all up. Dimension rows should be inserted/updated before the fact rows can refer to them.

Answer 39

According to a full extraction strategy, only the changes since the previous extraction are considered, which is also called Changed Data Capture or CDC. ## Footnote False. According to a full extraction strategy, the entire dataset is extracted from the source system, regardless of whether the data has changed since the previous extraction. This approach contrasts with incremental extraction strategies, where only the changes since the last extraction (Changed Data Capture or CDC) are considered.

Question 40

In a datawarehouse the data is structured, whereas in a data lake the data is unstructured. True False

Answer 40

True

Question 41

A snowflake schema is beneficial if the dimension tables grow too large and a more efficient usage of storage capacity is required. if it turns out that most queries don t make use of the outer level dimension tables and only need access to the dimension tables directly connected to the fact table. both of the above.

Answer 41

both of the above.

Question 42

Multidimensional OLAP (MOLAP) stores the multidimensional data using a Multidimensional DBMS (MDBMS) whereby the data is stored in a multi-dimensional array-based data structure optimized for efficient storage and quick access. True False

Answer 42

True