December 14, 2010 at 1:24 pm
Hi I have a simple question ... (I think its simple)
Suppose that i have the following staging table with the following columns:
---------------------------------------------------------------------------
Student_Name | RollNo | Test_Date | Subject-Taken
--------------------------------------------------------------------------
with data such as
Kevin |123|12-4-2010|Physics
now suppose that I want to design a cube on the basis of the above table so that I can succesfully get the result of a query such as
List the names of all those students who took the test b/w 12-4-2010 to 12-5-2010 of the Subject Physics
Here what i need to know what dimensions/Levels would u set and what would be our fact?
I think that one dimension would be time ( but i am not sure how i would accommodate and handle duration... any idea ) would it be wise to make each column a dimension ??? for example the student_name a dimension and details of the student its attributes??
Anyways the main thing is what bothers me is looking at the query we see that we are required 3 things the name of the student , the TestDate and subject taken so if i make the 3 columns the dimension i am still not sure that i would be able to acomodate the query properly...any ideas on how to approach and handle these situations
December 16, 2010 at 11:46 am
My approach would be to have 3 dimensions and a fact table.
Dimensions: DimStudent, DimDate, and DimSubject.
This would allow you to provide data/reports by student, by date, and by subject. Your fact table might end up being very similar to your staging table, but would contain surrogate keys for student, date, and subject and improve performance significantly.
I'm not sure what the context is for the RollNo attribute. Is this a metric or an attribute of a student? If it's a metric then my fact table would be FactRollNo - otherwise I would just include it in your student demension as an attribute and then create a the fact table. For the report you need, it appears that you might end up with a factless fact depending on your report - since you can get the report you need without a metric in your fact.
December 16, 2010 at 11:58 am
Hi , i am just a starter here...
So if we mention the above dimensions in our cube and no fact(measure) will our cube explorer be able to accommodate such a query.. from what i heard it is always necessary to include a measure in your expression as it is the measure which interlinks two dimensions... but i am more interested in creating a factless(measureless) cube..
December 16, 2010 at 1:31 pm
You can build a cube with your metric being the count of students. Might be able to even get away with 1 as StudentCount as a column in your fact table. Then your cube aggregates the sum of that column for each way you slice the cube.
December 17, 2010 at 6:43 am
You can build a cube with your metric being the count of students. Might be able to even get away with 1 as StudentCount as a column in your fact table. Then your cube aggregates the sum of that column for each way you slice the cube.
There's a count already built in, so this would be unnecessary. From what is described, I agree with those who suggest it is probably a factless fact table. This seems close to a Kimball sample if I remember correctly.
December 18, 2010 at 9:03 am
johnacandy (12/14/2010)
Suppose that i have the following staging table with the following columns...
As a side note I'll say you should never start with the Staging table but with the actual design of your Datamart e.g. FACT and related DIM tables then decide what kind of data you need which will lead you to the design of any staging tables you may need - doing it starting with the Staging table is like putting the carriage before the horses.
Taking a look at "dimensional modeling" wouldn't hurt - it's not rocket science but it is definitevely a different approach to data modeling, the approach needed to design efficient DSS systems.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply