Data Warehousing Questions and Answers

Answer: A

Correct answer: True

A surrogate key is a meaningless integer generated for the warehouse, and using one insulates the schema from the operational keys, which may be reused, may change format, or may collide between source systems. It also makes possible the slowly changing dimension, where several rows describe the same real entity at different times.

Answer: B

Correct answer: A process to load the data in the data warehouse and to create the necessary indexes

Load and index is the final stage of ETL, placing the transformed data into the warehouse and building the indexes that queries will use. Building the indexes after the load rather than before is deliberate: maintaining them during a bulk load would be far slower.

Answer: C

Correct answer: One-to-many

Each row of a dimension - one product, one date, one store - relates to many rows of the fact table, and each fact row points to exactly one row of each dimension. That is what gives the star its shape, with the fact table at the centre surrounded by its dimensions.

Answer: B

Correct answer: False

The star schema is designed for analytical querying, not for transaction processing. Its denormalised dimensions make queries fast and updates awkward, which is exactly the wrong balance for an operational system - those use normalised designs, and an enterprise warehouse uses a normalised reconciled layer as well.

Answer: B

Correct answer: A system that is used to run the business in real time and is based on current data.

An operational system records transactions as they happen, so it holds current data and is optimised for many small reads and writes. A warehouse is the opposite on both counts: historical data, optimised for large analytical queries, and used to support decisions rather than to run the business.

Answer: B

Correct answer: False

Periodic data are never physically altered once added: a change produces a new record and the old one remains, which is what preserves history. Data that are altered in place are transient, and the statement has given the definition of the one under the name of the other.

Answer: A

Correct answer: True

An operational system needs the current state and overwrites the old value with the new, which is transient behaviour. This is precisely why history has to be captured elsewhere: by the time an analyst wants last year’s address, the operational system no longer holds it.

Answer: A

Correct answer: True

A data mart serves one department or function, so its uses are known in advance and its structure can be tuned for them - the aggregations precomputed and the indexes chosen for the queries that will actually be asked. That is the trade against an enterprise warehouse, which must serve queries nobody has thought of yet.

Answer: D

Correct answer: All of the above.

The active warehouse architecture is distinguished by near real-time updating, which is what allows it to support operational decisions rather than only strategic ones. It retains the features of the earlier architectures - extraction from many internal and external sources, and dependent data marts for particular user groups.

Answer: A

Correct answer: True

Independent marts get built because a department needs an answer this quarter and the enterprise warehouse will take two years. The result is the fragmentation the warehouse was meant to cure - several marts with inconsistent definitions and no common reconciled layer beneath them.