A very productive day, started with navigating through the hierarchies. I realised that the way i always have gone through the levels are vertical navigation and now i learned about horizontal navigation. This is a way to navigate in time. Say that you want to compare sales for some product for july 2011 with the same product another year, say 2012. Then you would like to use some of the functions that are made for this in the MDX-language.
For once you could use member.PrevMember or member.NextMember which returns previously/next member within the current memberlevel.
Also there are the member.Lag and member.Lead functions which takes an int parameter which determines direction to return data. Negative int equals forward and positive int means back in time. It´s very useful to create analys comparing how much the sales differed between month-to-month or year-to-year.
A thing i didn´t fully grasped was the Ancestor & Ascendants functions which also is used for navigating through the hierarchies. Well they really just return either the ancestor members or the ascendants members of the current level(member). The descendants function also return a member from a given level but it can be modified through the use of eight different optional flags.
I will have to work with these some more to really know when and how to use them.
Time based analysis – this is what i is all about after you have learned some of thoose basic functions. What time based analysis is about i touched on above when talking about analysing sales from different year or months. Here there are four major functions that are used to compare:
- YTD – Year To Date
- QTD – Quarter To Date
- MTD – Month To Date
- WTD – Week To Date
There are also the function PeriodsToDate but i think i like the previously ones because you will get a few less lines, at least i did in my examples. Could be that this will change later on.
One thing a read about that´s pretty important is the possibility to make average values during a specific time window. This could be achived through the use of LastPeriod and with that you will make rolling averages. This function returns a set of members that´s either before or after a specified member of a hierarchy. This rolling average value is very useful when you have problem spotting trends.
Last function to notice is the parallelPeriod which alsa compares two members in time at the same level.
Next up is create these calculated members inside BIDS and SSAS in my cubes but still there are issues when processing so i guess i will just do everything except processing, or else i will use the demo-cube Step-by-Step which hopefully will work.