YTD calculation for regular dimension

Question

Hi All,

I have a dimension named TIME but it is a regular dimension, not a time dimension.

What is the way to calculate the YTD for such dimension?

I tried using YTD(), PERIODSTODATE() functions for the calculation but of no use.

Can anybody help?

Thanks in Advance

Answer 1

Why you made it regular?! anyway YTD should passed date dimension http://technet.microsoft.com/en-us/library/ms146039.aspx

---

BI Developer | LINKdotNET B.Sc Computer Science, Helwan University Technical blog | http://ramymahrous.wordpress.com/

Answer 2

Is there any reason you haven't specified the Type of the dimension as Time and the defined the Type of each of the attributes correctly? The YTD() and PeriodsToDate() functions were built to take advantage of those definitions.

Otherwise, if your natural attribute hierarchies have been defined correctly, Day related to Month, Month related to Quarter, Quarter related to Year, you can navigate the hierarchy to get the ancestor at the Year level using the Ancestor function, using the Descendants function and the Item operator to extract the first member, you can get the first member at the level of the current time member, then you can aggregate the resulting set using the range operator. Below is an example from Adventure Works.

WITH MEMBER [Measures].[YTD Internet Sales Amount] AS
     Aggregate(
          {Descendants(
               Ancestor(
                    [Date].[Calendar].CurrentMember,
                    [Date].[Calendar].[Calendar Year]
               ),
               [Date].[Calendar].CurrentMember.Level
          ).Item(0) : [Date].[Calendar].CurrentMember},
          ( [Measures].[Internet Sales Amount] )
     )
SELECT {
          [Measures].[Internet Sales Amount],
          [Measures].[YTD Internet Sales Amount]
     } ON COLUMNS
FROM [Adventure Works]
WHERE (
     [Date].[Calendar].[Month].&[2004]&[5]
)

 

 

 

Answer 3

Hi Ramy, Martin,


Thanks for the reply.

I am very new to the analysis services, that's why I didn't know how I can create TIME dimension.

Also my table from which I am creating the time dimension do not specifically holds data for year,quarter or weeks.
It could be anything as user like.
Mostly it would be weeks, quarter or months.

So its difficult to define what type of TIME dimension it could be. I took it as undefined time.

Also my table tblTime consists TIME_ID, CODE, PARENT_ID.
The values for CODE can be week names, quarter names or month names.
PARENT_ID would be TIME_ID, that is self referencing key.

So can you please tell me how I can manage this?


I tried creating the TIME dimension from tblTime with CODE as undefined time.
I then used  YTD([Time].[CODE].CURRENTMEMBER) , this gave me syntax error.


I hope I am clear ni explaining the scenario.

Thanks

Answer 4

To design your time dimension you need set the relationship between Time Table columns and itself. this done in snowflake or star scheme modeling. then analysis service in building cube will ask you to set the time dimension.

---

BI Developer | LINKdotNET B.Sc Computer Science, Helwan University Technical blog | http://ramymahrous.wordpress.com/

Answer 5

A properly defined Time dimension is critical to the success of a SSAS, or for that matter, any data warehouse project. I wouldn't recommend defining the Time dimension as a parent-child relationship as you have done. There are times when a parent-child dimension is useful but the Time dimension is most definitely not one of them. I would very highly recommend you create a time dimension with a single record for each day, and columns that specify the date, the week, the month, the quarter, and the year that day is related to. Chapter 2 in the The Data Warehouse Toolkit has an excellent discussion of a properly designed date dimension and there are probably several web references as well. Without a properly defined time dimension, your project is going to be swimming upstream, a very swiftly flowing stream.

Answer 6

Martin,


Its not possible to change the structure at this time.

Can you please suggest any work around?

Also we can ignore the PARENT-CHILD relation.

Thanks

Answer 7

Without more information, I can't confidently suggest a good workaround. You're going to either have to model the Time dimension as a Parent/Child dimension (TIME_ID as the child attribute and the PARENT_ID has the parent) within SSAS (not highly recommended) OR restructure the dimension. If you don't flatten out the time dimension, it's going to be difficult to define what each level of the time hierarchy represents.

One possibility you can use as a go between is to use a view (either defined in your source or in the DSV) to flatten out your P/C hierarchy. Something simple like the expression below would work if you time dimension is not a ragged hierarchy. For the life of me, I can't think of why you would have a ragged time hierarchy.

SELECT d.TIME_ID AS DateID,
       d.CODE AS DateDesc,
       m.TIME_ID AS MonthID,
       m.CODE AS MonthDesc,
       q.TIME_ID AS QuarterID,
       q.CODE AS QuarterDesc,
       ....
FROM TIME_DIMENSION d
INNER JOIN TIME_DIMENSION m
       ON d.PARENT_ID = m.DATE_ID
INNER JOIN TIME_DIMENSION q
       ON m.PARENT_ID = q.DATE_ID
WHERE NOT EXISTS (
                   SELECT *
                   FROM TIME_DIMENSION p
                   WHERE d.TIME_ID = p.PARENT_ID
       ) 

 

 HTH,

Answer 8

Hi Martin,



My view would be like this.

SELECT A.TIME_ID, A.TIME, B.TIME PARENT FROM TBLTIME A INNER JOIN TBLTIME B ON A.PARENT_ID=B.TIME_ID

My output for this view would be

TIME_ID                         TIME                          PARENT
-------------------------------------------------------------------
1                                   Quarter1                    
2                                   Jan                             Quarter1
3                                   Feb                             Quarter1
4                                   Mar                             Quarter1
5                                   Quarter2                   
6                                   Apr                             Quarter2
7                                   May                            Quarter2
8                                   Jun                             Quarter2
-------------------------------------------------------------------


Now from this I would create the dimension.

And I expect the YTD calculation to be like this

YTD for Feb=values for Jan+Feb
YTD for Mar=values for Jan+Feb+Mar
YTD for APR=values for Jan+Feb+Mar


Can you please tell me how I can achieve this?

I hope I am clear on explaining this.

Thanks

Answer 9

The problem with your approach is it's still a parent/child dimension (quarter and month in same column) whereas the view I gave you before, each column in the view represented a date, a month, a quarter, or a year. There was no mixing of time granularities within the same field. Once a column means something, the YTD column is simple to implement but you got to get to that point first.

Answer 10

Ok...

What if I have the following data?

TIME_ID                         TIME                        
-------------------------------------------------------
1                                   Jan                          
2                                   Feb                          
3                                   Mar                          
4                                   Apr                          
5                                   May                         
6                                   Jun                          
-------------------------------------------------------


Can YTD() be done on this?

Answer 11

It's a start. The Time_ID and Time columns now represent Months. You now need a column to represent years. Or are you only expecting a single year worth of data? The easiest approach to get YTD if you only have the hierarchy above represented as Aggregate ( { [Time].[Time].CurrentMember.FirstSibling : [Time].[Time].CurrentMember} ). This expression would give you what you want.

YTD for Feb = Jan + Feb
YTD for Mar = Jan + Feb + Mar
YTD for Apr = Jan + Feb + Mar + Apr

Answer 12

Martin,


I have other dimension which stored the year data and its structure is same like the time dimension.

Well this query is working fine.

Just that I want to add the filter to this so that the values return belongs to particular version ( I also have a dimension named version) say 'Actual'.

I tried this but its giving result as zero.

FILTER(AGGREGATE({[Time].[Time].CurrentMember.FirstSibling : [Time].[Time].CurrentMember},[Measures].[Local Value]),[Version].[Version]='Actual')

Can you help?

Thanks for the above solution though.

Answer 13

If you're trying to retrieve only actuals, the following expression should work.

AGGREGATE({[Time].[Time].CurrentMember.FirstSibling : [Time].[Time].CurrentMember},([Measures].[Local Value], [Version].[Version].[Actual]))

Rather than using a Filter function, just specify the member to aggregate over in the tuple used as the second argument of the Aggregate function.

Answer 14

Martin,

Thanks for the reply.

I tried implementing this but it is giving me error.

Aggregate functions can not be used on calculated members in Measures dimension.


Can you please suggest anything on this?

Thanks

Answer 15

Sorry, my bad. The Aggregate function cannot be used on calculated measures. Try

SUM ( {[Time].[Time].CurrentMember.FirstSibling : [Time].[Time].CurrentMember},([Measures].[Local Value], [Version].[Version].[Actual]))

instead.

Answer 16

Martin,


Thats great!!!!!!1

That worked perfectly fine.

Thanks for the help....

Thank you very much....

Answer 17

Hi Martin,

Some time back you gave me the above solution, But now I am facing some issue in it.

Can you please help?

Here is the thread where I posted the issue

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/526e6349-6c1c-45a8-b695-45d1fc11e52c

Thanks in advance.