Problems with dual role of time dimension

I'm fairly new to SSAS, so forgive me for asking a question that’s probably quite easy to answer, if you know your way around cubes and dimensions.

The situation is this: I'm creating a cube for resource planning (dimensions include resource/team, project/customer and time, fact table include number of bookings and value of bookings/projects). The time dimension plays two roles:

1) The booking date. Meaning that ressource1 from Team A is booked for project X on given dates in the future.

2) Reporting date. The complete plan is loaded into a Data warehouse once a day. The reporting date should allow us to compare the current situation with a previous point in time (compare booking ratio, value of future projects etc). In effect the reporting date is the versioning of the plan.

I'm currently facing two problems with the dual role of the time dimension:

1) How can you make the Reporting date semi-additive without also affecting the booking date (booking count and value should be summed across booking dates for a given reporting date, but not across reporting dates)?

2) How can you set default values for reporting dates? - Default I would always want to see data for the latest reporting date (corresponding to the latest version of the plan). But this should not affect the booking date - for the latest version of the plan, I want to see all future bookings.

I guess these are common problems when building a planning cube with time versioning, so I hope somebody has some valuable input.

Thanks in advance.

Thomas N. S?rensen

Hi Thomas,

Here are links to past threads in this forum that could help you:

For problem 1) relating to semi-additive measures when there are multiple time dimensions:

lastnonempty & role playing date dimension

>>

Hello,

I'm just curious if in case of a role-playing date dimention it's possible to somehow tell SSAS to use only one role for LastNonEmpty aggregate?

Like we have a fact table with a few date related members - such as TransactionDate, DateOpened, DateClosed etc.

All measures in this fact table are set to aggregate as LastNonEmpty & everything works just fine as long as only TransactionDate is linked to dimDate.

If any other dates are linked then LastNonEmpty doesn't work properly anymore & we get unpredictable results.

So if it possible to set ONLY TransactionDate to be used as LastNonEmpty & for all other dates just aggregate as sum?

Thanks!

...

It is always only ONE role-playing time dimension. The problem is, you cannot control which one it is. Sorry, but there is no way to tell directly to SSAS which one. You can keep reordering the cube dimensions in AMO until SSAS picks the right one. After that, if the order doesn't change - it will always use the same one (it is stable algorithm w.r.t. order of dimensions).

---

Mosha - http://www.mosha.com/msolap
...

>>

For problem 2) relating to different default members for each role of a dimension:

Different Default Members for Role Playing Dimension

>>

...

You could try updating the default member for each role with cube MDX script statements instead, like:

Code Snippet

ALTER CUBE CurrentCube UPDATE DIMENSION [Base UOM].[UOM Abbrev],

DEFAULT_MEMBER = [Base UOM].[UOM Abbrev].&[Abbrev1];

ALTER CUBE CurrentCube UPDATE DIMENSION [Reporting UOM].[UOM Abbrev],

DEFAULT_MEMBER = [Reporting UOM].[UOM Abbrev].&[Abbrev2];

>>