Align time series by setting the start date of each serie on an anchor date (icCube)

2
votes

(I hope my title is clear)

I have a MDX statement that results in:

  • time (months) in the rows
  • several projects in the columns

The data displayed is an amount (e.g. costs).

Now I would like to compare the project expenditure for each project and see if there are temporal differences. As such I want to get a fictive time axis (start month, month + 1, month + 2, .... month + 60) and for each relative month the expenditure for each project.

I will give a data example to illustrate (attached picture). (consider the green part as the lifetime of the project - you can assume 0 costs during the lifetime and null costs outside the lifetime): enter image description here

I think I can write a rather complex MDX for this (e.g. use the current period as the start month and add 12 months to it), then for each project lookup the amount in the "current projected period" - "start date of the project". It will be a bit messy though.

Is there an easier way to do this. For example using one or more of icCube's custom Matrix/Vector functions? I would like to use the end result in a widget to graphically display the data.

1
mdxiccube
the short answer is yes, the long answer tomorrow :-) – ic3

1 Answers

0
votes

We're looking for a table like this one :

+----------+-----------------+----------------+
|          |   Proj1         | Proj2          |
+----------+-----------------+----------------+
| Month 1  | (Month1,Proj1)  | (Month1,Proj2) |
| Month 2  | (Month2,Proj1)  | (Month2,Proj2) |
| Month 3  | (Month2,Proj1)  | (Month3,Proj2) |
+----------+-----------------+----------------+

Where 

 MonthN is the Nth month from the start of the project

This MonthN changes dynamically on each cell and will depend on the start date of the project. In MDX this is a calculated member that should look like :

MyCalcM AS IIF( [Project].current.isAll, null, ProjectStartDate([Project].current).lag(n) )

where ProjectStartDate is a function that returns the start date of a project. We won't enter how this function is but we can create a cached schema level declared function with a static context so it will be calculated just once.

Now we need to link this calculated measure with a [project date] dimension. In MDX this can be done with what we call an Utility dimension ( link ). Utility dimensions are dimension that define a transformation, aka calc. member, for each each of his members instead of the clasical slice&dice behavior.

There are different solutions for building this dimension.

One is doing something similar as the historgram defined here, this might be a bit cumbersome as you need to create manually each member.

Another one is creating directly from a fact table the new dimension, [Project Time], that is not linked in the schema to any facts like this one :

 Name ,  addMonths
 First Month, 0
 Month 2, 1
 Month 3, 2
 ..
 Month N, n-1

We have to create a calculated member that binds this dimension to the calc. members using the member key to calculate the lag ( lag(n) -> lag( [Project Time].current.key ) ) . The drawback of this solution is that we need to activate this calculated measure.

hope it helps