multidimensional modeling in data warehouses n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Multidimensional Modeling in Data warehouses PowerPoint Presentation
Download Presentation
Multidimensional Modeling in Data warehouses

Loading in 2 Seconds...

play fullscreen
1 / 18

Multidimensional Modeling in Data warehouses - PowerPoint PPT Presentation


  • 125 Views
  • Uploaded on

Multidimensional Modeling in Data warehouses. Shilpa Seth. To Be Discussed. Multidimensional Data Model Concepts Data Cube Data warehouse Schemas - Star Schema - Snowflake Schema - Fact Constellation Schema. MULTIDIMENSIONAL DATA MODELS.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Multidimensional Modeling in Data warehouses' - tyra


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
to be discussed
To Be Discussed..
  • Multidimensional Data Model Concepts
  • Data Cube
  • Data warehouse Schemas

- Star Schema

- Snowflake Schema

- Fact Constellation Schema

multidimensional data models
MULTIDIMENSIONAL DATA MODELS

A data warehouse is based on a multidimensional data model which views data in the form of a DataCube.

A data cube, such as sales, allows data to be modeled and viewed in multiple dimensions.

Dimension tables, such as time (month, quarter, year)

Fact table contains measures (such as units, price) and keys to each of the related dimension tables.

multidimensional data

Dimensions: Product, Store, Time

Hierarchical summarization paths

Store

Brand Region Year

Product Country Quarter

Type

State Month Week

City Day

Product

Time

Multidimensional Data
  • Sales volume as a function of product, month, and region.
slide5

Dimensions and Facts

  • Dimensions are entities or perspective with respect to which an organization wants to keep records.
  • Facts are numerical measures.

Back

sample data cube

Time(months)

2

3

4

5

milk

Product

cheese

Toronto

eggs.

Vancouver

Store

Victoria

∑ ∑ ∑

Sample Data Cube

Multidimensional

viewofsales

data

slide7

Cube: A Lattice of Cuboids

In data warehousing literature, an n-D base cube is called a Base cuboid. The top most 0-D cuboid, which holds the highest-level of summarization, is called the Apex cuboid. The lattice of cuboids forms a Data Cube.

cuboids corresponding to the cube
Cuboids Corresponding to the Cube

all

0-D(apex) cuboid

product

store

1-D cuboids

time

product, store

2-D cuboids

store, time

product, time

3-D(base) cuboid

product, store, time

Back

data warehouse schemas
DATA WAREHOUSE SCHEMAS
  • Star Schema
  • Snowflake Schema
  • Fact Constellation Schema
sales data warehouse model
Sales Data Warehouse Model

Time

Sales fact

Store

City

Product

sales measures dimensions
Sales Measures & Dimensions
  • Measures– Units , Price.
  • Dimensions – Product, Time, Store.
star schema
Star Schema
  • A single , large and central fact table and one table for each dimension.
  • Every fact points to one tuple in each of the dimensions and has additional attributes.
  • Star Schema makes heavy use of denormalization to optimize for speed, at a potential cost of storage space.
star schema1
Star Schema

Sales Fact Table

Store Dimension

Time Dimension

Measures

Product Dimension

Back

snowflake schema
SnowFlake Schema
  • Variant of star schema model.
  • A single , large and central fact table and one or more tables for each dimension.
  • Dimension tables are normalized i.e. split dimension table data into additional tables.
snow flake schema
Snow Flake Schema

Sales Fact Table

Time Dimension

Store Dimension

Product Dimension

Back

City Dimension

fact constellation schema galaxy schema
Fact Constellation Schema (Galaxy Schema)
  • Multiple fact tables share dimension tables.
  • This schema is viewed as collection of stars hence called galaxy schema or fact constellation.
  • Sophisticated application requires such schema.
fact constellation
Fact Constellation

Sales Fact Table

Shipping Fact Table

Product Dimension

Shipper

Time Dimension

Store Dimension

Back