Bending Time

[Acius]

I'm trying to represent time in a couple of different ways without building multiple cubes.

Usual way
Jan 01
Feb 01
... Dec 01
Jan 02
... Dec 02

To make it easier to report is there a way of 'breaking the time dimension into 2 dimensions
e.g
1 Dimension with
Jan
Feb
..Dec
and another with year
e.g.
2001
2002
This allows easier plotting/tabulation of figures year by year.

The other way we'd like to represent time is by merging with measures.
e.g.
in the original measure dimension
Actual
Budget
the time dimension as originally stated.
What we'd like to do is merge the years with the measures
and split the months into a seperate dimension.
e.g.
New Measure
Actual 2001
Actual 2002
Budget 2001
Budget 2001

Month Dimension
Jan
Feb
.. Dec

Is there a way in Analysis Services of defining virtual cubes to represent time in the additional 2 ways?

 

[MDXer]

In Analysis Services you can have Multiple Hierarchies on a dimesnion for this purpose. However I'm not sure that what you are trying to do requires you to define multiple hierarchies.


If your time dimension has Multiple levels thenyou could define your months at a level under your years which allows you to change your reporting period from years to months by just changing levels.

As for the merging of Time and measures This is also something you don't really have to do. If your front end tool allows you to crossjoin (which all should) then this handles the issue for you.

If you have a time Dimension with 2002 and 2003 members and your measures dimension has Budget, and Actual members then a cross join of Time at the year level and Budget and Actual of the measures dimension will give the breakout you are looking for. Which would visually look something like the following.

| 2002 | 2003 |
-----------------------------
|Budget|Actual|Budget|Actual|
-----------------------------
Product A |100 |200 |200 |250 |
---------------------------------------



"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Acius]

Thanks,

but what I was really trying to do was present as simple a view as possible (mainly for graphing purposes) to the user.
e.g. in the first case
the table would be
Jan Feb Mar .... Dec
2001 10 20 30 ....
2002 11 19 29 ...
This makes it dead easy for users to produce a graph that produces year on year comparisons.

The other example is pretty much the same. Just trying to put as simple a view as possible to the user.

We're migrating from another OLAP product and are trying to to present the same views that they are used to without duplicating physical cubes

Cheers