ch8: data warehouse and data mart modeling

line-item detailed fact table

each row represents a line item of a particular transaction

transaction-level detailed fact table

each row represents a particular transaction

dimensional schema

fact tables connected to dimensions via foreign keys is often refereed to as a STAR SCHEMA. It often contains foreign keys to connect the dimensions and the subject of analysis

dimensional modeling

is a data design methodology used for designing subject oriented analytical databases

conformed dimensions

standarized dimesnions created before development of star schemas, typically used with multiple fact tables

Surrogate key

the primary key that takes the control of the dimension when tying it to a fact table in a star schema

snowflake model

this is a star schema with dimensions that are normalized with the construct of other dimensions. The result is a dimension with foreign keys tying it to its normalized counter parts

type 2 approach

this method is used for conserving history of a record change;. It creates a new record value with the altered record while preserving the record that would have been changed as is

type 1 approach

we simply replace the old values with the new values by changing them

row indicator

within type two approach, shows if the record is current or not current via text

time stamps

within type two approach, two additional columns with a effective start/end dates to help reader understand the effect of a slowly changing dimension

granularity

describes what is depicted by one row of the fact table. detailed fact tables have a fine level of granularity vs a more aggregated fact table that has a rougher granularity

type 3 approach

causes two columns to arise to account for the change. One column marked by the word previous, accounts for the record's value before the change. Another column marked current shows the change for the value of the record while also showing the same values for the unchanged columns

fact tables

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

transaction identifier

a column within a fact table that gives extra explanation of the topic at hand. If you are tracking goods sold you might want to include the transaction ID as a transaction identifier to help with anaylsis

normalized data warehouse

a data warehouse modeled by using the traditional database modeling techniques of Er modeling, and/or normalization, resulting in a normalized set of tables

slowly changing dimensions

a dimension that contains attributes whose values can change. dealing with these dimensions has three approaches

constellation (galaxy of stars)

a star schema with multiple fact tables

dimensional modeling data warehouse

a warehouse using dimensional modeling.

transaction time

also another attribute including in fact table to aid with anaylsis, helps subject be analyzed by time factor

dimensional tables (dimensions)

contains descriptions of the business to which the subject of analysis belongs. Columns often contain descriptive information, often textual,

detailed fact table vs aggregated fact table

each record shows the fact of one occurrence vs each record summarizing multiple facts