How do I model this and create SSAS cube from it?

Asked tihs question on stackoverflow here, but no responses. I am very new to dimensional modelling and SSAS cube generation and hoping for some pointers...

I'm struggling to correctly model and create an SSAS cube (to be deployed to end-users via Excel) for a set of data to show how students milestones (like 'graduated within 4 years', 'enrolled in second semester') are affected by indicators (like 'completed college level english within first two semesters', 'completed 10 unites in first semester', etc.)

The end result I'm hoping for is something like:
SUCCESS INDICATORS Retention 2nd Term (94%) Retention 2nd year (83%)
College-Level Math
Completed in First Year (58%) 98.00% 91.90%
Did Not Complete in First Year (42%) 87.80% 71.30%
College-Level English
Completed in First Year (84%) 96.90% 88.10%
Did Not Complete in First Year (16%) 78.20% 59.60%
So the question is first, how do I model this?

I was thinking a student dimension (one row per studentid), a year dimension, a milestone dimension (not sure, use junk dimension or the binary style for every combination), an indicator dimension (not sure, junkk or binary), and a fact table with one row per student per year...?

And the second question is, how do I get from the model to a simple cube I can browse in Excel or Visual Studio that shows results like above.

I'm reading the Kimball's Data Warehouse Toolkit but the topic is so new to me I'm just not sure how to approach this.

Note that ultimately i will have 10+ indicators and 10+ milestones, so the table above is just a sample.

I'm a complete newbie, so may need some explicit help - thanks for any help

Asked tihs question on stackoverflow here, but no responses. I am very new to dimensional modelling and SSAS cube generation and hoping for some pointers...

I'm struggling to correctly model and create an SSAS cube (to be deployed to end-users via Excel) for a set of data to show how students milestones (like 'graduated within 4 years', 'enrolled in second semester') are affected by indicators (like 'completed college level english within first two semesters', 'completed 10 unites in first semester', etc.)

The end result I'm hoping for is something like:
SUCCESS INDICATORS Retention 2nd Term (94%) Retention 2nd year (83%)
College-Level Math
Completed in First Year (58%) 98.00% 91.90%
Did Not Complete in First Year (42%) 87.80% 71.30%
College-Level English
Completed in First Year (84%) 96.90% 88.10%
Did Not Complete in First Year (16%) 78.20% 59.60%
So the question is first, how do I model this?

I was thinking a student dimension (one row per studentid), a year dimension, a milestone dimension (not sure, use junk dimension or the binary style for every combination), an indicator dimension (not sure, junkk or binary), and a fact table with one row per student per year...?

And the second question is, how do I get from the model to a simple cube I can browse in Excel or Visual Studio that shows results like above.

I'm reading the Kimball's Data Warehouse Toolkit but the topic is so new to me I'm just not sure how to approach this.

Note that ultimately i will have 10+ indicators and 10+ milestones, so the table above is just a sample.

I'm a complete newbie, so may need some explicit help - thanks for any help



Edit: can't get the table above to format so you can see what Im hoping to achieve, so link to stackoverflow is below...

stackoverflow.com/questions/6929490/dimensional-modelling-and-ssas-cube-creation

Talk about deja vu, this question seems very familier! Was it you that started this thread as 'engil'? (Took me a little while to find it again becuase Search appears to just do titles and not content!)

Did you ever read my (long) response near the bottom? (Ignore all the discussion about junk dimensions in the middle of the thread!)

Let me try again and see if I can shed some light on this.

Basically you have your base fact table with junk dimension as your core physical model and load the tables in your ETL as suggested in Warren's article. Make sure you get this right in first place, and it's important for any down-stream queries as well as the cube I am about to focus on.

To cater for your special cube presentation, you then need to have another layer for your fact and dimensions. Create and data-entry a once-off dimension table for the cube, Let's consolidate Milestones_dim and Indicators_dim, as suggested in VHF's previous thread, into one Cube_dim for simplicity.

You then need to use union statements to construct a view or insert into a physical table by joining the base fact to your junk dimensions. You would have many union statements as follows:

create view vCube_fact
as
select Student_Key, 'Completed_college_Math_Y' as Cube_dim_value, 1 as Student_Count
from Base_fact f
join Junk_dim j
on f.Junk_key=j.Junk_key
and Completed_College_Math="Y'

union all

select Student_Key, 'Completed_college_Math_N' as Cube_dim_value, 1 as Student_Count
from Base_fact f
join Junk_dim j
on f.Junk_key=j.Junk_key
and Completed_College_Math='N'

......

Once you construct the view for every junk attributes that you want to use in the cube, you need another fact view to wrap it up with surrogate keys to Cube_dim, as follows:

Create view vCube_fact_final
as
select f.Student_Key, c.Cube_dim_key, Student_Count
from vCube_fact f
join Cube_dim c
on c.Cube_dim_value=f.Cube_dim_value

That's all you need to feed the cube, one fact view vCube_fact_final and one dimension table Cube_dim. You need to extend your date dimension to cover the term attributes so you can work out term based retention rate using LAG function in you MDX.

Since you use SSAS, I wouldn't bother creating a separate physical fact table if performance is acceptable, as it is purely for cube presentation and would create confusion to your schema if used for ad-hoc queries. Even with the cube navigation, you need to be mindful of the incorrect aggregate values without the context of Cube_dim, as suggested in VHF's post.

Yes, really sorry, couldn't find my original post and couldn't remember my username (and the forums requires both username and email address to retrieve password...)

I did read your post and it was very helpful but I couldn't figure out the last piece of the puzzle (incorporating into SSAS). Also on other forums some people were adamant about using the other type of dimension, which contains one row for every possible combination, ie.

Milestone_dim
milestone_key CompCollMath CompCollEngl CompSuccessCourse
1 N N N
2 N N Y
3 N Y N
4 N Y Y
.
.
.

Finally (and I should have mentioned this earlier it appears) - there are other dimensions that ultimately will need to be added, for student ethnicity, student grade level, student type (transfer vs non-transfer), etc. so the cube will need to be able to deliver correct percentages based on selected attributes (what are the percentages for only African-American transfer students)

Sorry to keep asking the same question, I'm completely new to SSAS and data modelling and in retrospect this was probably not a good candidate for one of my first cubes (I created two previously but they didn't have this level of complexity) - I'm basically only able to spend about 10% or less of my workweek on this and its been kind of a whole new world of technologies and concepts to learn (MDX, SSAS, modelling, star schemas, etc.)

Thanks again for all your help

I would focus first on getting a clean dimensional model to store the facts. A good model will allow end users to do ad hoc analysis of the data (using Excel against SSAS cubes.)

As I identified in the original thread, to get the output you want in a single pivot table is going to require some special handling, because your desired output format is really the output of multiple queries assembled together. In typical "slice-and-dice" drill-down analysis you are starting with a whole (such as total sales) and slicing it into pieces that always add up to the original total. But the percentages in your desired output would add up to much more than 100%.

In your desired cross-tab report you are looking at indicators and results as individual pairs. That's why I proposed the special fact table that would represent each intersection of indicator and result. But as I mentioned before, I would not allow end users to access it becasue it would too easily allow them to generate misleading/erroneous results.

There is nothing wrong with having multiple models of the same thing to serve different purposes.

It is always tough when you can only work on your DW/BI project as time allows.

ngilbert wrote:Also on other forums some people were adamant about using the other type of dimension, which contains one row for every possible combination, ie.

Milestone_dim
milestone_key CompCollMath CompCollEngl CompSuccessCourse
1 N N N
2 N N Y
3 N Y N
4 N Y Y
.
.
.

In theory, it is one way to build the junk dimension by cross joining on all the attributes, and there is nothing wrong with it if the total number of attributes is not terribly high (10 - 20). Otherwise build the dimension incrementally based on the attributes' possible combinations in the source. I have implemented similar student demographic junk dimension that covers most of the flags you mentioned. I suspect you still haven't got your head around on the concept of junk dimension.

If you really want to get it working, you need to try to digest Warren's article by playing with the data. In case you could not find it, here's the article again: http://www.kimballgroup.com/html/09dt/DT113CreatingUsingMaintainingJunkDimensions.pdf .