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

  • ktflash

    Ten Centuries

    Points: 1109


    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

    ohne monat

    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

    mit monat

    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

    Thanks for posting your issue and hopefully someone will answer soon.

    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)

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