How do I model this and create SSAS cube from it?
3 posters
Page 1 of 1
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 endusers 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%)
CollegeLevel Math
Completed in First Year (58%) 98.00% 91.90%
Did Not Complete in First Year (42%) 87.80% 71.30%
CollegeLevel 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 endusers 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%)
CollegeLevel Math
Completed in First Year (58%) 98.00% 91.90%
Did Not Complete in First Year (42%) 87.80% 71.30%
CollegeLevel 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/dimensionalmodellingandssascubecreation
I'm struggling to correctly model and create an SSAS cube (to be deployed to endusers 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%)
CollegeLevel Math
Completed in First Year (58%) 98.00% 91.90%
Did Not Complete in First Year (42%) 87.80% 71.30%
CollegeLevel 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 endusers 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%)
CollegeLevel Math
Completed in First Year (58%) 98.00% 91.90%
Did Not Complete in First Year (42%) 87.80% 71.30%
CollegeLevel 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/dimensionalmodellingandssascubecreation
ngilbert Posts : 2
Join date : 20110812
Re: How do I model this and create SSAS cube from it?
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!)
Did you ever read my (long) response near the bottom? (Ignore all the discussion about junk dimensions in the middle of the thread!)
VHF Posts : 236
Join date : 20090428
Location : Wisconsin, US
Re: How do I model this and create SSAS cube from it?
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 downstream 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 dataentry a onceoff 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 adhoc 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.
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 downstream 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 dataentry a onceoff 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 adhoc 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.
hang Posts : 528
Join date : 20100507
Location : Brisbane, Australia
Re: How do I model this and create SSAS cube from it?
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 nontransfer), etc. so the cube will need to be able to deliver correct percentages based on selected attributes (what are the percentages for only AfricanAmerican 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 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 nontransfer), etc. so the cube will need to be able to deliver correct percentages based on selected attributes (what are the percentages for only AfricanAmerican 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
ngilbert Posts : 2
Join date : 20110812
Re: How do I model this and create SSAS cube from it?
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 "sliceanddice" drilldown 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 crosstab 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.
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 "sliceanddice" drilldown 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 crosstab 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.
VHF Posts : 236
Join date : 20090428
Location : Wisconsin, US
Re: How do I model this and create SSAS cube from it?
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.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
.
.
.
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 .
hang Posts : 528
Join date : 20100507
Location : Brisbane, Australia
Similar topics
» SSAS Cube  zero downtime even during cube processing
» SSAS 2008  Hourly Sales Cube
» Sales Target Implementation in SSAS Cube
» Building a Summary table off an SSAS Cube?
» Adding a fact table with start date and end date to a (SSAS) multidimensional cube
» SSAS 2008  Hourly Sales Cube
» Sales Target Implementation in SSAS Cube
» Building a Summary table off an SSAS Cube?
» Adding a fact table with start date and end date to a (SSAS) multidimensional cube
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum

