Visually BI: OLAP Modelling Concepts

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


The map is here




types of table

fact table

types of columns


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


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


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





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


default member

convert nulls to value

allow duplicates


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


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


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


subject matter experts (SMEs)



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

Dimension Usage grid


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



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


intermediate tables


it improves dimension query performance

FK between tables of the same Dim

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


database modelling tool

to generate script


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.



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: Logo

You are commenting using your 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