Visually BI: OLAP Modelling Concepts

https://mamatucci.wordpress.com/2012/03/19/visually-bi-ssas-part-1/

In this 4th part the topic covered is the star schema  and related concepts

 

The map is here

#DW.O.1

http://dl.dropbox.com/u/63221860/BI.Blog/Visually%20OLAP%20Modeling%20Concepts/%23DW.O.1.png

 

 

types of table

fact table

types of columns

keys

foreign key (FK) values that relate rows in the fact table to rows in the dimension tables

measures

measures are the values as stored and displayed in an OLAP cube

facts

Facts are individual values stored in rows

numeric values

business metric

degenerate dimension

Fact tables can also contain columns that are neither keys nor facts.

Aggregation Type

clip_image001

additive

semiadditive

Nonadditive

significant storage space

justify every column added to any fact table

Fact prefix

deep and narrow

dimension table

they give context or meaning to” the facts

business entity

denormalized source data

attributes

default member

convert nulls to value

allow duplicates

inclusive

when choosing which attributes add to the dim being “inclusive” in dimensional modelling is preferred….

types of columns

newly generated primary key (PK)

surrogate key

loading from disparate sources this guarantee uniqueness

original PK from the source system

business key

additional columns

to describe the business entity

Dim Prefix

wide and not very deep

SCD

when modeling dimensional data is to review business requirements for the desired outcome when dimension member data is updated

or deleted.

Last change wins

Type 1

overwriting previous value

Changing Attribute

Retain some history

Type 2

Historical Attribute

adding a new record (or row value) when the dimension member value changes

Retain all history

Type 3

adding additional attributes (or column values) when the dimension member value changes

SSIS Slowly Changing Dimension wizard.

star schema

advantages

work best in the BIDS

cleansing and validation

during the ETL process

clip_image002 grain statements

to determine how to create the model

key metrics for the business

the numeric values related to the business

by what factors evaluate the key metrics

by what

multiple facts table

in case of different granularity for the same key metric

by what level of granularity

evaluation of the key metrics are evaluated by factors how often? … by day, by hour

sign-off procedure

during the requirements phase, to define them and get formal approval

SME

subject matter experts (SMEs)

snowflake

 

the difference is a dimension on more are based than one source relational table

Dimension Usage grid

Regular

or star type: there is a zero or one-to-many relationship between the rows in the fact table and the dimension table

clip_image003

Referenced

or snowflake dimension: you must select the intermediate dimension table and define the relationship between the two dimension tables by selecting the appropriate key columns

clip_image004

intermediate tables

Materialize

it improves dimension query performance

FK between tables of the same Dim

to define nature of relationships between dims and facts and granularity level

tools

database modelling tool

to generate script

views

for etl process

source control

to save docs and diagrams too

iterative process

1. you’ll simply create the skeleton tables

2. will refine the model by adding details

customer’s business terminology

 

Please like this article if you found it useful.

Mario

4 thoughts on “Visually BI: OLAP Modelling Concepts

  1. Pingback: Link Resource # 43 : Mar 01 – Mar 24 « Dactylonomy of Web Resource

  2. Pingback: Link Resource # 44 : Mar 24 – Mar 31 « Dactylonomy of Web Resource

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s