DatawareHouse / Business Intelligence Flashcards
Data Warehouse?
A giant storehouse for your data
ALL your data
Central repository, aggregate data from multiple systems
Business Intelligence?
Leveraging data you already have to convert knowledge into informed actions
Providing ways to measure the health of your business
Examining the data in your warehouse to look for the three main areas of interest
OLAP
Online Analytical Processing
Used mostly in data warehouses
Data denormalized
Number of tables reduced
star schema or snowflake schema
OLTP
OnLine Transaction Processing
Data normalized
Many joins
Harder for ‘ad-hoc’ reporting
slower
Star Schema
Data mart design pattern consisting of Fact and Dimension tables resembling a star like shape
TDWI
The Data Warehouse Insitute
Kimball Method
An iterative approach to data warehousing focused on dimensional modeling data for max business value
developed mid 1980’s
Dimension Modelling
The process of modelling a business process into a series of fact and dimension tables designed for analysis
Business Process
Is an activity which an organization performs. Typically these are the actions in the object >action model
ex: Sales processing, payment processing, service calls
Fact Table
The center of our star schema containing measures and links to dimension tables
3 types:
Transaction
Periodic Snapshot
Accumulating Snapshot
Dimension Table
Provide the who, what, where, when, why, and how context surrounding a business processing event
3 types Denormalized Role playing Outrigger Junk
Surrogate Key
A dimensions tables primary key that is not depending upon any source system, but is created by the data warehouse itself
Natural Key
The primary key of the dimension row from the source system which it came. This is often persisted over time while the surrogate key may change
