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

Question

Post reply

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

ktflash

Default port

Points: 1400
More actions
October 10, 2019 at 5:49 am

#3687652
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 5 years, 2 months ago by ktflash.
I want to be the very best
Like no one ever was
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

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

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

Site Owners SSC Guru Points: 80325 More actions · Answer 1

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: 3790 More actions · Answer 2

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.