Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Data Warehousing » Analysis Services » Identifying Facts and Dimensions.. Urgent...
Add to briefcase

Identifying Facts and Dimensions.. Urgent Help Required.. Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
a4apple
Posted Tuesday, September 04, 2012 4:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 3:05 PM
Points: 39, Visits: 87
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
Post #1354246
Evil Kraig F
Posted Tuesday, September 04, 2012 5:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 5,681, Visits: 6,135
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
Post #1354253
MissTippsInOz
Posted Tuesday, September 04, 2012 7:10 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 7:43 PM
Points: 286, Visits: 520
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?
Post #1354290
Thomas LeBlanc
Posted Wednesday, September 05, 2012 9:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:33 PM
Points: 2,706, Visits: 717
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/
Post #1354640
Lynn Pettis
Posted Wednesday, September 05, 2012 9:37 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:40 PM
Points: 21,627, Visits: 27,477
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/


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)
Post #1354684
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.