SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Define a schema for the fact table, and the dimensional tables in SQL from a relational...


How to Define a schema for the fact table, and the dimensional tables in SQL from a relational schema?

Author
Message
phamkhanhtung1989
phamkhanhtung1989
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 27
I am very new to Data warehouse and OLAP, and I dont know how to create OLAP database from a relational schema.

For example, I have a schema like this:

Student (Id, Name, Addr, Status)
Professor (Id, Name, DeptId, Rank)
Course (DeptId, CrsCode, CrsName, Descr)
Transcript (StudId, CrsCode, Semester, Grade)
Teaching (ProfId, CrsCode, Semester)
Department (DeptId, Name, FacultyName)

And I want to create a fact table and its dimensional table from that. I think my fact table will include:

Fact_Table(DeptId, CrsCode, ProfessorId, Semester, enrollment)

with 4 dimensional tables:

Department, Course, Professor and Semester

the attribute enrollment is the number of students in a class. But that is the design, I dont know how to implement it in SQL. is that like:

Create table Fact_table(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

or I have to do in another way. please help me! Thank you.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27813 Visits: 13268
First of all you have to think about what it is that you want to measure.
Is it the number of students enrolled in a course? The number of courses a professor teaches?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
phamkhanhtung1989
phamkhanhtung1989
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 27
that's right. I want to measure the number of students in class.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27813 Visits: 13268
OK, but now your grain of the fact table (meaning the lowest level at which you can analyze) is at the course level. Typically you would choose the lowest level possible.

For example, for a store selling bikes, you would want to store the sales per day, not per month. If you would store them by month, you would never be able to analyze data at the day or week level.

In your case, you would not be able to analyze your data at the student level. Your fact table would not be able to answer your questions like these:

* How many male students are enrolled for that course?
* How many students of the age group between 20-25 are enrolled for that course?
* Show me the different cities where the students come from.
* ...

So I would add a student dimension and a studentID in your fact table. Now you would have to aggregate data to get the number of students for a course, but that's OK. That's what we have cubes for :-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
phamkhanhtung1989
phamkhanhtung1989
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 27
you are great. honestly, this is one of my assignment, I just have to define a schema for the fact table, and the dimensional tables in SQL with the information above and make a bulk insertion into the fact table. So, just ignore the correctness of the design. However, your suggestion is awesome.

After designing the database, what do I have to do next?
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27813 Visits: 13268
Probably implement the database? Create the tables, create indexes to speed up performance, if necessary create foreign keys.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
phamkhanhtung1989
phamkhanhtung1989
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 27
that is what I am asking for. to create the database, can I use the normal way as relational database? and which table do I have to create first, dimensional tables or fact table?
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27813 Visits: 13268
phamkhanhtung1989 (7/30/2013)
that is what I am asking for. to create the database, can I use the normal way as relational database? and which table do I have to create first, dimensional tables or fact table?


A data warehouse in SQL Server is just the same as a regulare database, it is just modelled using the dimensional methodology.

An OLAP database however, is created using SSAS.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
phamkhanhtung1989
phamkhanhtung1989
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 27
Oh, thank you for helping me.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27813 Visits: 13268
No problem, glad to help.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search