multiple time dimensions in one cube

Question

Post reply

multiple time dimensions in one cube

dankwart menor

Ten Centuries

Points: 1130
More actions
January 7, 2009 at 9:12 am

#204720

Hi!
I have a database that has the information when a movie came to the cinemas and when it was taken from the cinemas.
Table Movie (ID, Name)
Table Cinema (ID, Name)
Table CinemaPlayingMovie (MovieID,CinemaID, StartedShowingAt, EndedShowingAt)
In my cube I have one server time dimension. In the tab dimension usage i used it two times with regular relationships, referencing StartedShowingAt and EndedShowingAt respectively.
Now I want to display it in the cube browser. I cannot figure out how to do this. If I drag both dimensions to the column area, the measure is dependent on both dimensions at the same time (displaying the amount of movies that started and also ended on that date). I want to diplay the amount of started movies and, as another measure, the amount of movies that ended.
Something like this:
2007 2008
startedShowing endedShowing startedShowing endedShowing
Cinema 1 454 435 566 555
Cinema 2 64 33 66 55
Any thoughts?
Cheers
Dankwart
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Frank Woodard Old Hand Points: 327 More actions · Answer 1

Sounds like your typical role playing dimension... here's the link to BOL.

Granted I'm not a big fan of BOL but you should be able to get it set up by using the steps listed.

http://msdn.microsoft.com/en-us/library/ms174487(SQL.90).aspx

dankwart menor Ten Centuries Points: 1130 More actions · Answer 2

Thank you!

But the quest is not quite over yet:

The BOL exactly describes the steps I did so far but not to the end. After I defined the dimension usages for my two time dimensions (Time Start & Time End), I want to use them in the cube browser.

How?

If I add the two hierarchies, the measure is affected by both of them. I get one measure displaying the amount of movies started AND ALSO ended in that year (month or day respectively).

I don't get two measures, one displaying the amount of movies started and one displaying the amount of movies ended.

How can I get that behavior

Best regards!

--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]

dankwart menor Ten Centuries Points: 1130 More actions · Answer 3

After all I guess it is not possible with server time dimensions, is it?

I cannot define al relationship between a fact table and a server time dimension, can I?

Is that the point?

--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]

Frank Woodard Old Hand Points: 327 More actions · Answer 4

Unfortunately I haven't tried to use a server time dimension before so I couln't say for sure. I'm not sure what the differences between a server time dimension and a user created time dimension are. Have you tried to create a calculated measure for the values that you're looking for? This may be the route that you need to take if slicing one of the dates causes the other's data to change.

dankwart menor Ten Centuries Points: 1130 More actions · Answer 5

I think the problem lies somewhere else:

First

let's take the standard example "Order Time" and "Ship Time". Each of these dimensions basing on the same dimension table. when I added the time dimension in the "Dimension Usage" tab, two cube dimensions were created automatically: "Time (Order Time)", "Time (Ship Time)".

So far it looks like they way I want it to be

Then

I rebuild, deploy, reconnect, refresh, everything

When I switch to the "Browser" tab I want to display my cube in a matrix like this:

2007 2008

Order Time ShipTime Order Time ShipTime

Order_1 454 435 566 555

Order_2 64 33 66 55

So I'm adding "Order" to the data area and The row area contains cities e.g.

However, if I add both cube dimensions to the column area, I don't get what I want. What I get is:

2007 2008

City_1 452 553

City_2 62 53

This aggregates "Order Time" and "Ship Time" in one cell, displaying orders that are ordered AND shipped in the same Year (or quarter, month, day if I drill down).

How must to operate things in the "Browser" tab to get the proper result?

Thanks for helping!

Cheers

--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]