SSAS Time Dimension (academic years, quarters) change over time

apologies if don't state teh question or overlooking obvious answers - started working ssas , barely beginner level.

i'm working on cube show student enrollments in different subject areas on time - purpose i'm concerned academic years , semesters.  in underlying dimension table have data like

key acad year     term          descr
1 2006          summer  summer 2006
2 2006          fall  fall 2006
3 2006          spring  spring 2007 (note spring 2007 part of 2006 academic year)
4 2007          summer  summer 2007
5 2007          fall  fall 2007
6 2007          spring  spring 2008
, on

i've set simple hierarchy 2 levels - acad year->descr

for cube have 1 measure: 'enrollments' shows sum of students have enrolled

what i'd able show following change on time:
 - year year % (2005 2006)
 - term term % (fall 2006 spring 2007)
 - term same term % (fall 2006 fall 2007)

preferably if possible i'd have set such percent change shows based on level of drilldown, if have acad years rolled (so columsn show 2005, 2006, etc.) show year on year change, if drilldown columns fall 2006, spring 2007, summer 2007, etc show term on term change % (and better yet, additionally show term same term %)

added business intelligence time dimension , seemed kind of work year on year, struggling rest...

pointers appreciated!

 

with   member [measures].[orderkey]     ancestor([date].[calendar].currentmember, [date].[calendar].[calendar year]).member_key   + "#" +   ancestor([date].[calendar].currentmember, [date].[calendar].[calendar semester]).member_name  set datehier 	order({[date].[calendar].[calendar year],[date].[calendar].[calendar semester]},[measures].[orderkey], desc)  member currentperiodvalue 	([measures].[internet sales amount], [date].[calendar].currentmember)  member parallelperiodvalue 	(parallelperiod([date].[calendar].[calendar year],1,[date].[calendar].currentmember), [measures].[internet sales amount])  member termtotermvalue 	iif([date].[calendar].currentmember.level.name="calendar year", null, 	(parallelperiod([date].[calendar].currentmember.level,1,[date].[calendar].currentmember), [measures].[internet sales amount]) 	) select {[measures].[internet sales amount],[measures].[orderkey],currentperiodvalue,parallelperiodvalue,termtotermvalue} on 0,  datehier on 1 [adventure works]

check if want. yeartoyear , termtosameterm addressed by parallelperiodvalue. termtoterm addressed termtotermvalue

i have created order key better representation of data.

 

 

---

vinu

SQL Server  >  SQL Server Analysis Services