[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Star Schema or Snowflake?

Posted on 2005-04-21
7
Medium Priority
?
953 Views
Last Modified: 2008-03-10
Hello,

I am creating a small data warehouse for transactional type data that requires a lot of lookup tables for select criteria. The data will be used for trending these transactions. I've identified about 5 dimensions for the fact table, but my problem is each transaction will require 20 different criteria with lookup descriptions for each transaction.  Also, the transaction and the fact table are both at the transaction level and I wonder if I should put all transaction info in the fact table because of this. (Transaction number plus criteria for searches).

Should I create a dimension for the transaction itself along with the 20 description/codes used in the selection criteria, keep all this in the fact table, or snowflake the 20 transaction lookups off the transaction dimension?

Thanks for any help here...
0
Comment
Question by:surfsista9
  • 4
  • 3
7 Comments
 
LVL 4

Accepted Solution

by:
plamen73 earned 750 total points
ID: 13833908
You said: "small data warehouse"
If I had to deal with "small" data warehouse I would choose:
1. 3rd normalize form, i.e. the usual approach,
2. Snowflake, as it is closer to normalized approach
3. Star schema

the 1st has the highest priority, 3rd one - least priority. But be very careful about this "small" word. Also, consider oracle advise:

--------------------------------------------------------------------------------
Note:
Oracle Corporation recommends you choose a star schema over a snowflake schema unless you have a clear reason not to.
--------------------------------------------------------------------------------
so, you have to make your trade off, according to how small or big is your small.
0
 

Author Comment

by:surfsista9
ID: 13834774
Thanks,

My small is very small less than 100,000 transactions. I am using this app as a demonstration within my department (but it will also serve a purpose).

I want to place a BO universe over the schema so believe I may need a star schema for this. Do you know if BO can report effectively with 3rd normalize form data structures or Snowflake structures?

Thanks
0
 
LVL 4

Expert Comment

by:plamen73
ID: 13835386
What is "BO universe" ??
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:surfsista9
ID: 13835837
Business Objects -- the universe is a meta layer ontop of a data warehouse that creates the sql structures needed for reporting.
0
 
LVL 4

Expert Comment

by:plamen73
ID: 13840714
ok, my knoweldge stops here.
I always prefer "do it by yourself" approach. Maybe BO has its own requirments about the schemma design
0
 
LVL 4

Expert Comment

by:plamen73
ID: 13840715
ok, my knoweldge stops here.
I always prefer "do it by yourself" approach. Maybe BO has its own requirments about the schemma design
0
 

Author Comment

by:surfsista9
ID: 13844166
Apparently BO can handle all but Star is the recommended.

I have decided to go for the star schema because I do not need to change history (only open transactions).
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question