Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to Define a schema for the fact table, and the dimensional tables in SQL from a relational schema? Expand / Collapse
Author
Message
Posted Monday, July 29, 2013 3:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 2:29 PM
Points: 10, 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.
Post #1478756
Posted Tuesday, July 30, 2013 12:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 13,606, Visits: 10,489
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1478843
Posted Tuesday, July 30, 2013 1:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 2:29 PM
Points: 10, Visits: 27
that's right. I want to measure the number of students in class.
Post #1478847
Posted Tuesday, July 30, 2013 1:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 13,606, Visits: 10,489
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1478849
Posted Tuesday, July 30, 2013 1:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 2:29 PM
Points: 10, 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?
Post #1478856
Posted Tuesday, July 30, 2013 1:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 13,606, Visits: 10,489
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1478861
Posted Tuesday, July 30, 2013 1:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 2:29 PM
Points: 10, 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?
Post #1478863
Posted Tuesday, July 30, 2013 2:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 13,606, Visits: 10,489
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1478867
Posted Tuesday, July 30, 2013 2:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 2:29 PM
Points: 10, Visits: 27
Oh, thank you for helping me.
Post #1478869
Posted Tuesday, July 30, 2013 2:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 13,606, Visits: 10,489
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1478879
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse