# Data that needs to analysed although part of it is outside of the Time Dimension

• ktflash

Ten Centuries

Points: 1109

Ahoi,

i have the following Situation:

• We have an IT Cube where tickets are analysed
• Each Ticket has a Buget/Plan Time which is the supposed time required for the task

• This time is booked on the creation time of the ticket

• Each Ticket also has the actual time booked on top of this ticket

• These times accumulative booked on N different positions, should be in buget/plan

My users tried to analyse the following, for august they wanted to which tickets where active:

• Tickets Dimension has a "status" attribut --> closed(inactive)/open(active)

• Tickets that are still open
• Tickets that are closed, but were closed in august--> time booked in this period (august)

The Problem is the following:

In the current Version they tried simply using the Time Dimension and filter in august.

• Plan/Budget Time is booked on creation time of the ticket and therefore is 0 if the ticket was not created in august
• If the Ticket is also older than august and time has been booked outside of august, not all of the accumulative times are aggregated, only the ones from august

• Budget Time often 0
• Booked Times by users is not the total time booked on the ticket(only august in this case), although the total is needed

I have a solution that works, but is not "pretty", I made a dummy measure and a 2nd Time Dimension:

• Seperate Time Dimension
• the Dummy Measure is solely used to filter the Tickets in Dimension that are "relevant" for in this case august

• The measure is connected to ticket Dimension and the seperate Time Dimension
• It takes all tickets that are currently still active and multiplies them across the Time Dimension, so is secured that each open Ticket is defently in any month  --> since active tickets are always relevant (still flawed, needs to be finetuned)
• For the tickets that are closed, i use part of the measuregroup that is used to get the 2 measures to get ticket times (buget/accumulitive) --> connected to the original Time Dimension

• By having these times booked on only by this one measure, i know that if use the new dimension and the dummy measure, that only if a dummy value is booked that it is a relevant ticket for the selected time

My Question is there a better solution for this type of Problem?

EDIT: I guess i need a MDX Solution that does the following

[Measures].[Aufwand]                                                                          -- Measure required for relevance Filter

[Ticket].[Status].&[Offen],[Ticket].[Status].&[Warte auf]                 --  Tickets with these states that need to be in no matter what

[Ticket].[Ticket ID]                                                                                -- Attribute that needs to be filtered based on state + Measure

Need all [Ticket].[Ticket ID].Members

where Aufwand > 0 or is either [Ticket].[Status].&[Offen]  or   [Ticket].[Status].&[Warte auf]

• This topic was modified 4 months, 1 week ago by  ktflash.

I wanna be the very best
Like no one ever was

• Site Owners

SSC Guru

Points: 80378

This is an automated bump to increase visibility of your question.

• heb1014

Hall of Fame

Points: 3788

This is a tough post to follow, but I have one idea that may help.  Have you investigated role-playing dimensions?  This concept may be helpful and should eliminate the need to create multiple date/time dimensions.  That helps with the user experience because the user does not need to filter dates from multiple dimensions.

Viewing 3 posts - 1 through 3 (of 3 total)