Snowflake or Star Schema?

View previous topic View next topic Go down

Snowflake or Star Schema?

Post  garrywh on Tue Aug 31, 2010 4:33 pm

Hi,

I am doing some dimensional modelling for a "not for profit" Fundraising Company

They solicit Funds from the WEB, Direct Mail and Phone Solitications, etc and all Transactions are stamped with the FundID.

The legacy system has all the "smarts" of the fund code in 1 column (WEB1009RA, DM1009PA)

Where:

WEB = CampaignType
1009 = AccountingPeriod (i.e., September 2010)
R = CampaignSubType (Retention, Prospect)
A = CampaignSegment

In the new dimensional Model, I want to create 4 new dimension tables

d_CampaignType
d_AccountingPeriod
d_CampaignSubType
d_CampaignSegment

Consider the following Operational Data Model
=============================================

Fact Table

RevenueTransaction (3 million records)

Transaction ID (PK)
FundID (FK to Fund)
CurrencyAmount

Dimension Table

Fund (1000 records)
FundID (PK)
FundCode (WEB1009RA)
CampaignID (FK to Campaign)

Dimension Table

Campaign (300 records)
CampaignID (PK)
CampaignName

My question is, in the Dimensional Model is it better to snowflake this model into 7 tables as follows

Option 1 (Snowflake)
====================
f_RevenueTransaction (links to d_Fund on FundID)
d_Fund links to d_Campaign
d_Fund links to d_CampaignType
d_Fund links to d_AccountingPeriod
d_Fund linkt to d_CampaignSubType
d_Fund links to d_CampaignSegment


Or is it better to move all the FKs of the fund dimension to the fact table...

Option 2 (Star Schema)
======================

f_RevenueTransaction
TransactionID (PK - Surrogate)
OperationalTransactionID
FundID (links to d_Fund)
CampaignTypeID (links to d_CampaignType)
AccountingPeriodID (links to d_AccountingPeriod)
CampaignSubTypeID (links to d_CampaignSubType)
CampaignSegmentID (links to d_CampaignSegment)

I am leaning towards Option 2. I think the queries would run faster.

Any opinions/suggestions?

Hopefully the explanation above makes sense.

Thanks,

Garry

garrywh

Posts : 2
Join date : 2010-08-30

View user profile

Back to top Go down

Re: Snowflake or Star Schema?

Post  BoxesAndLines on Wed Sep 01, 2010 9:11 am

I like option 2 better as well.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Snowflake or Star Schema?

Post  garrywh on Wed Sep 01, 2010 9:50 am

Thanks,

I'll benchmark both models. The space requirements are a little more for option 2 (4 new 4 byte integer fields added to the fact table (16 bytes * 3 million = extra 45 MB)), but it will be direct JOINS from the fact table dimension tables and easier for the user to navigate...

I put together a visual to show the Options more clearly...

www.garry.ca/SampleDimensionalModel.pdf

Garry

garrywh

Posts : 2
Join date : 2010-08-30

View user profile

Back to top Go down

Re: Snowflake or Star Schema?

Post  BoxesAndLines on Wed Sep 01, 2010 11:58 am

I'd also put the fund id as a degenerate dimension in the fact.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Snowflake or Star Schema?

Post  ngalemmo on Wed Sep 01, 2010 1:51 pm

garrywh wrote:Thanks,

I'll benchmark both models. The space requirements are a little more for option 2 (4 new 4 byte integer fields added to the fact table (16 bytes * 3 million = extra 45 MB)), but it will be direct JOINS from the fact table dimension tables and easier for the user to navigate...

I put together a visual to show the Options more clearly...

www.garry.ca/SampleDimensionalModel.pdf

Garry

I'll chip in $1.00 to cover the cost of the extra disk...

If it is a choice between hardware and improvement in clarity, useablilty, and utility, the latter wins hands down.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Snowflake or Star Schema?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum