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