Data Warehousing - Dimensional Modeling at Duale Hochschule Baden-Württemberg | Flashcards & Summaries

Select your language

Suggested languages for you:
Log In Start studying!

Lernmaterialien für Data Warehousing - Dimensional Modeling an der Duale Hochschule Baden-Württemberg

Greife auf kostenlose Karteikarten, Zusammenfassungen, Übungsaufgaben und Altklausuren für deinen Data Warehousing - Dimensional Modeling Kurs an der Duale Hochschule Baden-Württemberg zu.

TESTE DEIN WISSEN

Was ist die dimensionale Modellierun?

Lösung anzeigen
TESTE DEIN WISSEN

Dimensional Modelling is a Data Modelling technique for analytical Systems. Its aim is to analyze key figures (facts) from different perspectives (dimensions). (e. g. Regions, seasons, customer cluster, product)

The Multidimensional Model is often illustrated as a Data-Cube, to explain, how one Business Figure can be analyzed from the different perspectives.

Lösung ausblenden
TESTE DEIN WISSEN

Aus welchen vier Komponenten besteht ein multidimensionales Modell?

Lösung anzeigen
TESTE DEIN WISSEN
  • Facts 
  • Dimensions

  • Hierarchies

  • Aggregation ruleset

Lösung ausblenden
TESTE DEIN WISSEN

Beschreibe die Komponente der facts eines multidimensionalen Modells.

Lösung anzeigen
TESTE DEIN WISSEN

Facts are the operating Figures that are in the interest of the management and should by monitored / understood with help of the BI Systems.

Facts can be divided by several types:

  1. Data-Type : e.g numeric fact, enummeric fact
  2. Origin: Physical/stored facts or computed/derived fact
  3. Perspective from the perspective of kimballs consolidation rules (additive facts, semi & non-additive facts) -> Can data be aggregated?
Lösung ausblenden
TESTE DEIN WISSEN

Beschreibe die Komponente der hierarchies eines multidimensionalen Modells.

Lösung anzeigen
TESTE DEIN WISSEN

A Dimension can be spearated in several Hierachie-Levels. For example the Dimension „Time“ can be separated in the Levels „Day“  „Week“  „Month“  „Quarter“  „Year“. The Level, on which the Fact is availible (e.g. „Day“) is the most detailed (atomic) Level of the „Time“-Dimension.

The process of modeling the Dimension Hierarchies is important to understand their characteristic and their effect on the consolidation of the Facts. The Hierarchie is always on top of the Level of Granularity, the number of levels and the structure between each level depends on the Business Context.

It can have the following structures:

  1. Flat structure (one stage All -> Level 0)
  2. Balanced tree structure (Data separated into several trees: three levels: All -> lvl 1 -> lvl2)
  3. Unbalanced tree structure ( comparable to balanced but with different amount of levels between strings)
  4. Parallel hierarchie structure (One hierarchie entity can mix the relation between different hierarchie strings)
  5. Hererarchie (M:N Structure) (two aggregation hierarchie point to the same. Should be aware that the M:N data relation when looking on the all-perspective is doubled)
Lösung ausblenden
TESTE DEIN WISSEN

Beschreibe die Komponente der aggregation rulesets eines multidimensionalen Modells.

Lösung anzeigen
TESTE DEIN WISSEN

The aggregation Ruleset defines the mathmatical Rules for the consolidation of the Facts along the Hierachie Levels.

In the Aggregation Ruleset, the Business Side has to describe, how to treat the BusinessFigures (Facts) from mathmatical perspective, especially when the System has toconsolidate them along the hierarchy levels. Here the first aspect is the type of consolidation rule applying for the particular Fact

- Additive Facts  Just SUM it up

- Semi Additive Facts  Depends on the chosen Dimension(s)

- Non Additive Facts  Can other mathmatical rules be applied – e.g. AVG, MIN, MAX?

Lösung ausblenden
TESTE DEIN WISSEN

Wie passt das multidimensionale Modell mit dem relationalen schema zusammen?

Lösung anzeigen
TESTE DEIN WISSEN

Each dimension is one column or attribute in the relational model. The business figure bevodes the fact attribute. The dimensions become the key of the fact table.

Lösung ausblenden
TESTE DEIN WISSEN

Welche Komponenten beinhaltet das Star Schema? Was bedeuten diese?

Lösung anzeigen
TESTE DEIN WISSEN

Components:

  1. Fact table: (Contains all business transaction in atomic level; has a compound key means all FK pointing to the Dim.-tables representing the PK; have a high number of records and a very high number of space consumption on hard disk)
  2. Dimension Table: (Contains the context to the business transactions; Has a surrogate key as a PK instead of business key; possibly has a lot of descriptive attributed for all hierarchie levels; only a few records and low space consumption on hard disk)
  3. Relationship: Always 1:N relationships between dimensions and facts
Lösung ausblenden
TESTE DEIN WISSEN

Beschreibe das Galaxy Schema.

Lösung anzeigen
TESTE DEIN WISSEN

The Galaxy-Schema is an extension of the Star Schema with the Kimball-Touch: 2 or more Fact Tables that use one or more common (conformed) Dimensions build a „Galaxy“

Lösung ausblenden
TESTE DEIN WISSEN

WElche zusätzlichen modellierungsmuster für fact tables gibt es und was bedeuten diese?

Lösung anzeigen
TESTE DEIN WISSEN
  1. Factless fact

Under some circumstances it can be reasonable / necessary from business perspective, to have a Fact-Table without any figures – in this case, the fact-table is just for recording the existance of the relationship between Dimension-Values and the frequency of occurrence of this combination.

  1. Attributed in the fact table

While in literature the Facttable mostly just contains Key-Fields and Fact-Fields, in real life it can be reasonable / nessecary to have additional Attributes in the Fact-Table, that can be used for filtering certain sets of Fact-Records. This modelling alternative can be applied, when the attribute does not suite for creating a separate Dimension (eg. No hierachie availible)

Lösung ausblenden
TESTE DEIN WISSEN

Welche Regeln für hierarchie objekte gilt es bei der dimensionsmodellierung zu beachten?

Lösung anzeigen
TESTE DEIN WISSEN

1. The Relationship between the Hierarchie-Objects can be 1:N or M:N 

2. If the Releationship between the two Hierarchie-Objects is 1:1 they must be consolidated to one.

3. A Hierarchie-Object can have a random set of descriptive Attributes.

4. The descriptive Attributes always have a 1:1 relation to the Hierarchie-Object

Lösung ausblenden
TESTE DEIN WISSEN

Welches Problem wird durch das Konzept der langsam verändernden dimensionen addressiert?

Lösung anzeigen
TESTE DEIN WISSEN

The concept of slowly changing Dimensions addresses the fact, that the content of a Dimension can change over the time. It provides answers, how to handle theses changes in the Data Warehouse.


Bspw. Filialen warden von Zeit zu Zeit anderen Vertriebsregionen zugeordnet. Unterschiedliche Zustände zu unterschiedlichen Zeiten.

Lösung ausblenden
TESTE DEIN WISSEN

Welche vier Businessanforderungen können in Bezug zu den langsam verändernden Dimensionen existieren?

Lösung anzeigen
TESTE DEIN WISSEN

perspective. There are four possible requirements, that the business side can have:

1) AS IS -> Reports have to reflect the current state of the Dim.-Hierarchie

2) AS OF -> Reports have to reflect the original state of the Dim.-Hierarchie

3) AS POSTED->Reports have to reflect the historic truth (state of the Dim.-Hierarchie at the time a fact was inserted) -> Analyzing facts with the dimensions when the facts were inserted

4) Comparable Results (Facts + Dim. Data (only without change); Daten mit historischen Dimensionen)

Lösung ausblenden
  • 341307 Karteikarten
  • 5604 Studierende
  • 222 Lernmaterialien

Beispielhafte Karteikarten für deinen Data Warehousing - Dimensional Modeling Kurs an der Duale Hochschule Baden-Württemberg - von Kommilitonen auf StudySmarter erstellt!

Q:

Was ist die dimensionale Modellierun?

A:

Dimensional Modelling is a Data Modelling technique for analytical Systems. Its aim is to analyze key figures (facts) from different perspectives (dimensions). (e. g. Regions, seasons, customer cluster, product)

The Multidimensional Model is often illustrated as a Data-Cube, to explain, how one Business Figure can be analyzed from the different perspectives.

Q:

Aus welchen vier Komponenten besteht ein multidimensionales Modell?

A:
  • Facts 
  • Dimensions

  • Hierarchies

  • Aggregation ruleset

Q:

Beschreibe die Komponente der facts eines multidimensionalen Modells.

A:

Facts are the operating Figures that are in the interest of the management and should by monitored / understood with help of the BI Systems.

Facts can be divided by several types:

  1. Data-Type : e.g numeric fact, enummeric fact
  2. Origin: Physical/stored facts or computed/derived fact
  3. Perspective from the perspective of kimballs consolidation rules (additive facts, semi & non-additive facts) -> Can data be aggregated?
Q:

Beschreibe die Komponente der hierarchies eines multidimensionalen Modells.

A:

A Dimension can be spearated in several Hierachie-Levels. For example the Dimension „Time“ can be separated in the Levels „Day“  „Week“  „Month“  „Quarter“  „Year“. The Level, on which the Fact is availible (e.g. „Day“) is the most detailed (atomic) Level of the „Time“-Dimension.

The process of modeling the Dimension Hierarchies is important to understand their characteristic and their effect on the consolidation of the Facts. The Hierarchie is always on top of the Level of Granularity, the number of levels and the structure between each level depends on the Business Context.

It can have the following structures:

  1. Flat structure (one stage All -> Level 0)
  2. Balanced tree structure (Data separated into several trees: three levels: All -> lvl 1 -> lvl2)
  3. Unbalanced tree structure ( comparable to balanced but with different amount of levels between strings)
  4. Parallel hierarchie structure (One hierarchie entity can mix the relation between different hierarchie strings)
  5. Hererarchie (M:N Structure) (two aggregation hierarchie point to the same. Should be aware that the M:N data relation when looking on the all-perspective is doubled)
Q:

Beschreibe die Komponente der aggregation rulesets eines multidimensionalen Modells.

A:

The aggregation Ruleset defines the mathmatical Rules for the consolidation of the Facts along the Hierachie Levels.

In the Aggregation Ruleset, the Business Side has to describe, how to treat the BusinessFigures (Facts) from mathmatical perspective, especially when the System has toconsolidate them along the hierarchy levels. Here the first aspect is the type of consolidation rule applying for the particular Fact

- Additive Facts  Just SUM it up

- Semi Additive Facts  Depends on the chosen Dimension(s)

- Non Additive Facts  Can other mathmatical rules be applied – e.g. AVG, MIN, MAX?

Mehr Karteikarten anzeigen
Q:

Wie passt das multidimensionale Modell mit dem relationalen schema zusammen?

A:

Each dimension is one column or attribute in the relational model. The business figure bevodes the fact attribute. The dimensions become the key of the fact table.

Q:

Welche Komponenten beinhaltet das Star Schema? Was bedeuten diese?

A:

Components:

  1. Fact table: (Contains all business transaction in atomic level; has a compound key means all FK pointing to the Dim.-tables representing the PK; have a high number of records and a very high number of space consumption on hard disk)
  2. Dimension Table: (Contains the context to the business transactions; Has a surrogate key as a PK instead of business key; possibly has a lot of descriptive attributed for all hierarchie levels; only a few records and low space consumption on hard disk)
  3. Relationship: Always 1:N relationships between dimensions and facts
Q:

Beschreibe das Galaxy Schema.

A:

The Galaxy-Schema is an extension of the Star Schema with the Kimball-Touch: 2 or more Fact Tables that use one or more common (conformed) Dimensions build a „Galaxy“

Q:

WElche zusätzlichen modellierungsmuster für fact tables gibt es und was bedeuten diese?

A:
  1. Factless fact

Under some circumstances it can be reasonable / necessary from business perspective, to have a Fact-Table without any figures – in this case, the fact-table is just for recording the existance of the relationship between Dimension-Values and the frequency of occurrence of this combination.

  1. Attributed in the fact table

While in literature the Facttable mostly just contains Key-Fields and Fact-Fields, in real life it can be reasonable / nessecary to have additional Attributes in the Fact-Table, that can be used for filtering certain sets of Fact-Records. This modelling alternative can be applied, when the attribute does not suite for creating a separate Dimension (eg. No hierachie availible)

Q:

Welche Regeln für hierarchie objekte gilt es bei der dimensionsmodellierung zu beachten?

A:

1. The Relationship between the Hierarchie-Objects can be 1:N or M:N 

2. If the Releationship between the two Hierarchie-Objects is 1:1 they must be consolidated to one.

3. A Hierarchie-Object can have a random set of descriptive Attributes.

4. The descriptive Attributes always have a 1:1 relation to the Hierarchie-Object

Q:

Welches Problem wird durch das Konzept der langsam verändernden dimensionen addressiert?

A:

The concept of slowly changing Dimensions addresses the fact, that the content of a Dimension can change over the time. It provides answers, how to handle theses changes in the Data Warehouse.


Bspw. Filialen warden von Zeit zu Zeit anderen Vertriebsregionen zugeordnet. Unterschiedliche Zustände zu unterschiedlichen Zeiten.

Q:

Welche vier Businessanforderungen können in Bezug zu den langsam verändernden Dimensionen existieren?

A:

perspective. There are four possible requirements, that the business side can have:

1) AS IS -> Reports have to reflect the current state of the Dim.-Hierarchie

2) AS OF -> Reports have to reflect the original state of the Dim.-Hierarchie

3) AS POSTED->Reports have to reflect the historic truth (state of the Dim.-Hierarchie at the time a fact was inserted) -> Analyzing facts with the dimensions when the facts were inserted

4) Comparable Results (Facts + Dim. Data (only without change); Daten mit historischen Dimensionen)

Data Warehousing - Dimensional Modeling

Erstelle und finde Lernmaterialien auf StudySmarter.

Greife kostenlos auf tausende geteilte Karteikarten, Zusammenfassungen, Altklausuren und mehr zu.

Jetzt loslegen

Das sind die beliebtesten StudySmarter Kurse für deinen Studiengang Data Warehousing - Dimensional Modeling an der Duale Hochschule Baden-Württemberg

Für deinen Studiengang Data Warehousing - Dimensional Modeling an der Duale Hochschule Baden-Württemberg gibt es bereits viele Kurse, die von deinen Kommilitonen auf StudySmarter erstellt wurden. Karteikarten, Zusammenfassungen, Altklausuren, Übungsaufgaben und mehr warten auf dich!

Das sind die beliebtesten Data Warehousing - Dimensional Modeling Kurse im gesamten StudySmarter Universum

MM3: Decision Modeling

Karlsruher Institut für Technologie

Zum Kurs
Dimensionsanalyse

Karlsruher Institut für Technologie

Zum Kurs
Diagnostik: Modell und Praxisdimensionen

Universität Regensburg

Zum Kurs
Data Warehouse

Duale Hochschule Baden-Württemberg

Zum Kurs

Die all-in-one Lernapp für Studierende

Greife auf Millionen geteilter Lernmaterialien der StudySmarter Community zu
Kostenlos anmelden Data Warehousing - Dimensional Modeling
Erstelle Karteikarten und Zusammenfassungen mit den StudySmarter Tools
Kostenlos loslegen Data Warehousing - Dimensional Modeling