Database II Final

How is transaction time typically represented in a dimensional model?

TTime Options: Part of Calendar Dimension New dimension (time dimension) Adding to fact table is applicable to any scenario which transactions provide basis for fact table

describe the components of a normalized data warehouse

ETL, OLAP, ER modeling, data mining tools

What is the role of fact tables in the dimensional model?

Fact tables contain measures related to the subject of analysis. In addition, fact tables contain foreign keys associating them with dimension tables

Why is snowflaking usually not used in dimensional modeling?

One reason is that it is not normalized dimensions provide simpler analysis. Another reason is that normalization is usually not necessary for analytical databases. Snowflake design sql is more complex

How is a transaction identifier typically represented in a dimensional model?

Transaction identifier is typically represented as TID enables analysis that considers which products were sold withing same transaction

What is a constellation of stars?

constellation (galaxy) of stars is a dimensional model with multiple fact tables allows for cross-fact analysis

describe the components of a dimensional modeled data warehouse

Collection of dimensionally modeled intertwined data marts-constellation of dimensional models design begins with a set of commonly used dimensions known as conformed dimensions Fact tables corresponding to the subject of analysis is added

Dimensionally modeled data warehouse

A set of dimensional models is created in which each fact table is connected to multiple dimensions, and some of the dimensions are shared by more than one fact table

How does the use of a dimensional model simplify analytical questions?

Analytical queries on the dimensionally modeled database can be significantly simpler to create than on the equivalent non dimensional database. The convenience can be seen when data necessary for analysis resides in multiple sources. (very common)

What is the difference between a detailed fact table and an aggregated fact table?

Detailed fact table-each record refers to a single fact Aggregated fact table- each record summarizes multiple facts

What is the role of dimension tables in the dimensional model?

Dimension tables contain descriptions of the business, organization, or enterprise to which the subject of analysis belongs Columns contain textual and numeric information (product color, 100)

What does granularity of the fact table refer to?

The granularity of the fact table describes what is depicted by one row in the fact table. Detailed fact tables= fine level of granularity each record represents a single fact

What are the most common approaches for handling slowly changing dimensions?

Type 1 approach-simplest approach and used most often, changes the value in the dimensions record, where the new value replaces the old value Type 2 approach-used in cases where history should be preserved creates a new additional dimension record using a new value for the surrogate key every time a value in a dimension record changes Type 3- used when there is a fixed number of changes possible per column of a dimension OR cases when only a limited history is recorded involves creating a "previous" and "current" column in the dimension table for each column where changes are anticipated