September 4, 2012 at 4:44 pm
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.
September 4, 2012 at 5:02 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 4, 2012 at 7:10 pm
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/[/url]
September 5, 2012 at 9:07 am
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.
Thomas LeBlanc, MVP Data Platform Consultant
September 5, 2012 at 9:37 am
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.
Making it easier for those that will follow:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply