Kimball Dimensional modeling techniques

enterprise data warehouse bus matrix.

Most fact tables focus on the results of a single business process. Choosing the process
is important because it defines a specific design target and allows the grain, dimensions, and facts to be declared. Each business process corresponds to a row in the enterprise data warehouse bus matrix.

Dimensions

Dimensions provide the “who, what, where, when, why, and how” context surrounding a business process event. Dimension tables contain the descriptive attributes used by BI applications for filtering and grouping the facts.

Facts for Measurements

Facts are the measurements that result from a business process event and are almost always numeric. A single fact table row has a one-to-one relationship to a measurement event as described by the fact table’s grain.

Star Schemas and OLAP cubes

Star schemas are dimensional structures deployed in a relational database management system (RDBMS). They characteristically consist of fact tables linked to associated dimension tables via primary/foreign key relationships. An online analytical processing (OLAP) cube is a dimensional structure implemented in a multidimensional database;

Grace Extensions to Dimensional Modeling

Dimensional models are resilient when data relationships change. All the following changes can be implemented without altering any existing BI query or application, and without any change in query results.

• Facts consistent with the grain of an existing fact table can be added by creating new columns.
• Dimensions can be added to an existing fact table by creating new foreign key columns, presuming they don’t alter the fact table’s grain.
• Attributes can be added to an existing dimension table by creating new columns.
• The grain of a fact table can be made more atomic by adding attributes to an existing dimension table, and then restating the fact table at the lower grain, being careful to preserve the existing column names in the fact and dimension tables.   

 uitleg grain: stel je hebt postcode dimensie met 4 cijfers en je wilt over naar 6 cijfers. Dan kan de grain van de feiten gaan vreranderen

Fact Table Structure

A fact table contains the numeric measures produced by an operational measurement event in the real world. At the lowest grain, a fact table row corresponds to a measurement event and vice versa. Thus the fundamental design of a fact table is entirely based on a physical activity and is not influenced by the eventual reports that may be produced. In addition to numeric measures, a fact table always contains foreign keys for each of its associated dimensions, as well as optional degenerate dimension keys and date/time stamps. Fact tables are the primary target of computations and dynamic aggregations arising from queries.

Conformed Facts

If the same measurement appears in separate fact tables, care must be taken to make sure the technical definitions of the facts are identical if they are to be compared or computed together. If the separate fact definitions are consistent, the conformed facts should be identically named; but if they are incompatible, they should be differently named to alert the business users and BI applications

 Dat betekent dat je overal in je feittabellen dezelfde definitie gebruikt maar je deelt geen Feittabellen. Dit levert nl teveel problemen op

Degenerate Dimensions

Sometimes a dimension is defined that has no content except for its primary key. For example, when an invoice has multiple line items, the line item fact rows inherit all the descriptive dimension foreign keys of the invoice, and the invoice is left with no unique content. But the invoice number remains a valid dimension key for fact tables at the line item level. This degenerate dimension is placed
in the fact table with the explicit acknowledgment that there is no associated dimension table. Degenerate dimensions are most common with transaction and accumulating snapshot fact tables.

Degenerate dimension keys

Bijv Factuurnummer: geen dimensie met attributen maar enkel waarde
dit zijn dimensie waarden ondergebracht in een feiten tabel. Dit  kun je doen wanneer een dimensie attribuut dezelfde granulariteit/grain heeft als de het feit waaraan het gekoppeld is/wordt. Er is dan sprake van een 1-op-1 relatie. zie : https://en.wikipedia.org/wiki/Degenerate_dimension

Flags and Indicators as Textual Dimension Attributes

Cryptic abbreviations, true/false flags, and operational indicators should be supplemented in dimension tables with full text words that have meaning when independently viewed. Operational codes with embedded meaning within the code value should be broken down with each part of the code expanded into its own separate descriptive dimension attribute