Star Schema or Snowflake?

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...
surfsista9Asked:
Who is Participating?
 
plamen73Commented:
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
 
surfsista9Author Commented:
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
 
plamen73Commented:
What is "BO universe" ??
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
surfsista9Author Commented:
Business Objects -- the universe is a meta layer ontop of a data warehouse that creates the sql structures needed for reporting.
0
 
plamen73Commented:
ok, my knoweldge stops here.
I always prefer "do it by yourself" approach. Maybe BO has its own requirments about the schemma design
0
 
plamen73Commented:
ok, my knoweldge stops here.
I always prefer "do it by yourself" approach. Maybe BO has its own requirments about the schemma design
0
 
surfsista9Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.