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

Identifying Facts and Dimensions.. Urgent Help Required.. Expand / Collapse
Author
Message
Posted Tuesday, September 4, 2012 4:44 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
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 :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1354246
Posted Tuesday, September 4, 2012 5:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:44 AM
Points: 6,236, Visits: 7,380
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
Posted Tuesday, September 4, 2012 7:10 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:02 PM
Points: 286, Visits: 575
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
Posted Wednesday, September 5, 2012 9:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, August 7, 2014 8:32 PM
Points: 3,077, Visits: 782
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
Posted Wednesday, September 5, 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 @ 6:19 PM
Points: 23,243, Visits: 31,938
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