Help creating a star schema - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Help creating a star schema

Author

Topic

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-16 : 22:34:54

Hi, I am trying to convert my original database into a Star schema for data warehousing. My original database looks like this:

Relationships:
customer has a one to many with charter
charter has a many to one with pilot
charter has a many to one with aircraft
employee has a one to one with pilot
model has a one to one with aircraft

Customer (
Cus_code
cus_lname
cus_fname
cus_areacode
cus_phone
cus_balance
)

Charter(
Char_trip
char_date
char_pilot
ac_number
char_estination
char_distance
char_hours_flown
char_hours_wait
char_fuel_gallons
CHar_oil_qts
Cus_code
)

pilot{
Emp_numr
pil_license
pil_ratings
pil_med_type
pil_med_date
pil_pt135_date
)

aircraft(
Ac_number
mod_code
ac_ttaf
ac_ttel
ac_tier
)

Employee(
Emp_num
emp_title
Emp_lname
emp_fname
Emp_initial
emp_dob
emp_hire_date)

Model(
mod_code
mod_manufacturer
mod_name
mod_seats
Mod_chg_mile
mod_cruise
mod_fuel)

I am thinking about using one fact table called charters but I think maybe two fact table would be better. A charter and and a employee fact table.

Any help you can give me in turning this into a star schema would be very helpful. thanks

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-17 : 01:20:08

I came up with a trip fact table and my four dimension tables are Aircraft, Employee, Customer, Charter. The Trip Fact Table will a composite primary key of Emp_num, Ac_number, Char_trip, Customer_code.

Do you think this would look right?

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-17 : 01:42:29

I want to be able to analyze charter data such as cost, hours flown, fuel used, and revenue. I would also like to be able to drill down by pilot, type of airplane, and time periods. That is how I hope to setup this star schema

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-12-17 : 10:45:12

Star Schemas are not data warehouses.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-17 : 11:48:44

I thought this was the simplest form of data warehousing.

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-17 : 11:49:08

Creating dim tables and a fact table

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-12-17 : 13:22:43

You've been drinking the Kimball-Koolaid.
Star schemas are for datamarts, at best. There is absolutely no way a true enterprise data warehouse could be represented in a star schema, or snowflake either.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-17 : 22:26:55

Hahaha, That is what I been reading in my database book. Can you tell me the differences between datamart and a data warehouse.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-12-18 : 11:13:09

I can tell you MY definition. Opinions differ depending upon who you talk to.

Reporting Database: Copy of a transaction processing database used to offload expensive latency-tolerant query processing.

Data Mart: Subset of data aggregated from one or more data sources, limited to a single business area.

Data Warehouse: Aggregated data relating information from separate disciplines across the business enterprise.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources