Dimensional Modeling Fact Tables Essay

Submitted By deepika1604tiwari
Words: 1396
Pages: 6
Open Document
Dimensional
Modeling
ISYS637

Vocabulary
Fact Table
Dimension Table
Star Schema
Snowflake Schema

3/4/15

ISYS 637: DATA WAREHOUSE

2

Figure: Generating Reports from facts and dimensional attributes
3/4/15

ISYS 637: DATA WAREHOUSE

3

Dimensional Design Process
(Source: “The Data Warehouse Toolkit” by Kimball and Ross, 2013)

1. Select the business process to model
◦ E.g. a dimensional model to capture order process
2. Declare the grain of the business process
◦ Specify exactly what an individual row in a fact table represents.
3. Choose the dimensions that apply to each fact table row
◦ “How do businesspeople describe the data that results from the business process?” 4. Identify the measures (or numeric facts) that will populate each fact table row
◦ “What are we measuring?”
3/4/15

ISYS 637: DATA WAREHOUSE

4

Where to Start?
1. Business Process
1. EVENT

2. DATA (or Dimensions) relevant to the EVENT
1. People
2. Product/Service
3. Location
4. Date/Time
5. Organization(s)
6. Document(s)
7. Measures

3/4/15

ISYS 637: DATA WAREHOUSE

5

Data Warehouse Bus Matrix

Sharing dimensions across value chain
3/4/15

ISYS 637: DATA WAREHOUSE

6

Data Warehouse Bus Matrix

Sample data warehouse bus matrix 3/4/15

ISYS 637: DATA WAREHOUSE

7

Data Warehouse Bus Matrix
• The rows of the bus matrix correspond to data marts.
• You should create separate matrix rows if the sources are different, the processes are different, or if the matrix row represents more than what can reasonably be tackled in a single implementation iteration.
• Creating the data warehouse bus matrix is one of the most important up-front deliverables of a data warehouse implementation.
• It is a hybrid resource that is part technical design tool, part project management tool, and part communication tool.

3/4/15

ISYS 637: DATA WAREHOUSE

8

Data Warehouse Bus Matrix

Data
Marts
Sample data warehouse bus matrix 3/4/15

ISYS 637: DATA WAREHOUSE

9

Measures (or Facts)

3/4/15

ISYS 637: DATA WAREHOUSE

10

FACTS- Measures
Measure- Represents the property of the fact that we want to analyze
Measures can be◦ Atomic- Generated directly from the source data and stored in the data warehouse, e.g. product price.
◦ Derived- Generated on the fly through pre-calculations on one or more measures in the fact, e.g., “total price”, that is generated according to the formula {unit_price * quantity}
◦ Additive- A measure is additive along a dimension if we can use the SUM operator to aggregate attribute values along all hierarchies defined on that dimension
◦ Semi-additive- Additive across some dimensions but not all, e.g. Inventory is not additive on time dimension but additive on product and location dimensions
◦ All measures that record a static level (inventory levels, financial account balances, and measures of intensity such as room temperatures) are inherently non-additive across the date dimension and possibly other dimensions.
◦ In these cases, the measure may be aggregated usefully across time, for example, by averaging over the number of time periods.
◦ Non-additive- non-additive across any dimension, e.g. ratios such as profit margins
◦ If possible, store the fully additive components of the non-additive measure and sum these components into the final answer set before calculating the final non-additive fact. This final calculation is often done in the BI layer or OLAP cube

3/4/15

ISYS 637: DATA WAREHOUSE

11

Example: Additive, Semi-Additive, and Non-Additive Facts

Current_Balance is semi-additive because it is additive for Account, but nonadditive for Date

Sales_Amount is additive for Date,
Store and Product

3/4/15

Profit_Margin is nonadditive for Account, and Date levels

ISYS 637: DATA WAREHOUSE

12

Factless Facts
Facts that do not contain any measures, i.e. have no measures associated to them. This type of facts is usually used to represent the state of something, where there are no measures required to be stored on this fact
These are facts
Show More

Related Documents: Dimensional Modeling Fact Tables Essay

Essay Bill Gates

Jeffrey F. Zhang EXPERTISE Business Intelligence, ETL, Data Modeling, Database MSQL Development, Data Warehouse, VBScrip & .NET Development, Business Analysis, Project Management EDUCATION Master of Computer Science & MBA, University of Virginia B.S., Computer Science, Beijing University EXPERIENCE Ullico Inc., Silver Spring, MD 2007 - Present Senior Information System Analyst Business Intelligence * Implemented ETL packages to process large data files from the Third Party…

Words 877 - Pages 4

Computer crime involves Essay example

indecipherable garble of characters  After it is received, it will be reconstructed with a matching key  Audit-control software  Monitor and record computer transactions as they happen so auditor can trace and identify suspicious computer activity after the fact  To avoid the case that electromagnetic signals emanating from the computer hardware is “heard” so that some sensitive information is read.  Backups and other precautions  A power surge or failure can wipe out well protected data  Uninterruptible…

Words 7422 - Pages 30

King: Fluid Dynamics and Impeller Essay

velocity (w1 /w2 ). Kano et al. [2] presented results showing that in addition to the overall diffusion ratio, the rate of diffusion and maximum loading can significantly impact impeller peak efficiency and range. Moore et al. [3] used a three-dimensional viscous CFD method to examine the flow in a medium pressure ratio impeller. The CFD results showed several aspects of loss production in the impeller. Loss production was high over most of the shroud particularly within the clearance flow region…

Words 1865 - Pages 8

Essay about Christopher Sims Monetary Policy And It

STATISTICAL MODELING OF MONETARY POLICY AND ITS EFFECTS CHRISTOPHER A. SIMS A BSTRACT. The science of economics has some constraints and tensions that set it apart from other sciences. One reflection of these constraints and tensions is that, more than in most other scientific disciplines, it is easy to find economists of high reputation who disagree strongly with one another on issues of wide public interest. This may suggest that economics, unlike most other scientific disciplines, does not really…

Words 9146 - Pages 37

Essay On Edge Based Rich Representation

requirement to distinguish similar classes such as sedans vs. taxies makes the problem even harder. To tackle these challenges, this paper introduces an edge-based rich representation. The rich representation is able to give finer categorizations by modeling more details and improve robustness using over-complete information. The proposed approach augments edge points to repeatable and discriminative features, combines several existing techniques with modifications to fit them better to the considered…

Words 6518 - Pages 27

3114 14850 1 PB Essay

Mecánica Computacional Vol XXIX, págs. 1761-1781 (artículo completo) Eduardo Dvorkin, Marcela Goldschmit, Mario Storti (Eds.) Buenos Aires, Argentina, 15-18 Noviembre 2010 OPTIMIZATION OF LAMINATED TUBES USING FINITE ELEMENT ANALYSIS Rafael F. Silvaa; Iuri B. C. M. Rochaa, Evandro Parente Jr.a, Antônio M. C. Meloa and Áurea S. Holandab a Departamento de Engenharia Estrutural e Construção Civil, Universidade Federal do Ceará Campus do Pici, Bloco 710, 60455-760, Fortaleza, Ceará, Brasil b Departamento…

Words 8374 - Pages 34

Magnetism and Temperature Essay

i n t e r n a t i o n a l j o u r n a l o f r e f r i g e r a t i o n 3 5 ( 2 0 1 2 ) 1 0 4 3 e1 0 5 4 Available online at www.sciencedirect.com w w w . i i fi i r . o r g journal homepage: www.elsevier.com/locate/ijrefrig Assessment of demagnetization phenomena in the performance of an active magnetic regenerator Paulo V. Trevizoli, Jader R. Barbosa Jr.*, Pablo A. Oliveira, Fa´bio C. Canesin, Roge´rio T.S. Ferreira POLO Research Laboratories for Emerging Technologies in Cooling and Thermophysics…

Words 8450 - Pages 34

La Vague Du Japonisme: the Effects of Japanese Art on French Art in the Late 19th Century Essay example

Japanese objects within Tissot’s home (Art Fact). This work really captures the general curiosity that the French public had in Japan. Edward Degas (1834-1917) focused on the form of Japanese compositions perhaps more so than any other artist of his time (Lambourne 2005, 39). Instead of using Japanese props and paraphernalia, Degas sympathized with Japanese aesthetics (Ives 2004). Woman with Chrysanthemums (1865) portrays a woman sitting on the right side of the table with her…

Words 3077 - Pages 13

Marketing: Marketing and Customer Loyalty Essay

Expert Systems with Applications 31 (2006) 101–107 www.elsevier.com/locate/eswa Customer segmentation and strategy development based on customer lifetime value: A case study Su-Yeon Kim a, Tae-Soo Jung b, Eui-Ho Suh c, Hyun-Seok Hwang d,* b a School of Computer and Information Technology, Daegu University, 15 Naeri, Jinryang, Gyeongsan, Gyeongbuk 712-714, South Korea Department of Management Strategy, Samsung Economic Research Institute, 191 Hangangro 2-ga, Youngsan-gu, Seoul 140-722…

Words 4895 - Pages 20

Miller Shettleworth Essay

Journal of Experimental Psychology: Animal Behavior Processes 2007, Vol. 33, No. 3, 191–212 Copyright 2007 by the American Psychological Association 0097-7403/07/$12.00 DOI: 10.1037/0097-7403.33.3.191 Learning About Environmental Geometry: An Associative Model Noam Y. Miller and Sara J. Shettleworth University of Toronto K. Cheng (1986) suggested that learning the geometry of enclosing surfaces takes place in a geometric module blind to other spatial information. Failures to find blocking…

Words 21268 - Pages 86