Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identifying Facts and Dimensions.. Urgent Help Required..


Identifying Facts and Dimensions.. Urgent Help Required..

Author
Message
a4apple
a4apple
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 406
Hi all,

I have an OLTP Database and we are currently planning to build a datawarehouse and a cube and then reports over the cube. I am totally new to OLAP. Please help me figure out the solution by explaining the below question.

I have 5 tables in my OLTP.

User(id, login, fname, lname)
Proj(id, name, desc, website)
members(id, user_id, project_id)
roles(id, name)
member_roles(id, member_id, role_id)

Now, I have to figure out Dimensions and Facts from these tables (STAR SCHEMA). I am a newbie to this total terminology.

From my readings I figured out, that I will need,

DimProj(ProjectKey, Pid, name, desc, website)
DimRole(RoleKey, Rid, name)
DimUser(UserKey, UID, login, fname, lname)
Fact_MemberRoles(MemberRoleKey, UserID, RoleID, ProjID)

Is this correct?? Please correct and show me a way to move ahead.

Thanks

Good Luck Smile .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6277 Visits: 7660
For warehouses, particulary for cubes, you need to start with 'what questions do I want to ask of the data?' This will help inform you of what are facts and what are dimensions. Incredibly short form: Facts can answer questions, Dimensions will describe facts.

In this case, it looks like you want to ask questions about the projects, so think of that as your fact table, and decide what questions you want to be able to ask of it, and then hang your dimensions off the resulting fact table.

This of course is not the only approach to warehouse building, but it's an excellent starting point if you're new to the process. Then take what you've learned and re-read all that material that's got you twisted around and it should make a TON more sense.

I know it's not what you're asking for, you're looking for a solution. I'm loathe to give you *my* solution because I doubt the questions I'd expect to ask of data are the ones that are actually on your mind.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
MissTippsInOz
MissTippsInOz
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 597
Evil Kraig F (9/4/2012)
For warehouses, particulary for cubes, you need to start with 'what questions do I want to ask of the data?' This will help inform you of what are facts and what are dimensions. Incredibly short form: Facts can answer questions, Dimensions will describe facts.

In this case, it looks like you want to ask questions about the projects, so think of that as your fact table, and decide what questions you want to be able to ask of it, and then hang your dimensions off the resulting fact table.



In all honesty none of the OLTP tables/data you are describing really translates into a Fact table as it stands. Agree with EKF - what are your questions? What do you want your reports to tell you?

Your fact table(s) generally represent the 'transactional' element of your system. So, for example, if I rent cars, my dimensions might be my cars, customers, rental stations (and never forgetting date) but the transactional element is the process; the actual renting of the car. So my fact table would bring together a customer, a car, a rental station and a date together with the relevant bits of data (the 'measures') like the cost and the duration of the rental.

The Dimensions are the objects and the Facts are the processes.

There are some great free resources on the Kimball website that may help you get moving http://www.kimballgroup.com/data-warehouse-and-business-intelligence-resources/kimball-core-concepts/

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Thomas LeBlanc
Thomas LeBlanc
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3824 Visits: 902
I would first suggest reading some articles from the Kimbal group and concentrate on Fact tables - Tranaction, Snapshot and Accumalitve.

You need this understanding to get the measures the reports are requiring.

http://www.kimballgroup.com/

Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26466 Visits: 38125
Thomas LeBlanc (9/5/2012)
I would first suggest reading some articles from the Kimbal group and concentrate on Fact tables - Tranaction, Snapshot and Accumalitve.

You need this understanding to get the measures the reports are requiring.

http://www.kimballgroup.com/


Making it easier for those that will follow:

http://www.kimballgroup.com/

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
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