data modeling IT service management / case tracking system

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • data modeling IT service management / case tracking system

    Dear forum members,
    Is anybody out there who has got experience with data modeling in the area of IT Service Management and case tracking systems?
    In a first step I would like to set up a cube and define some measures like:
    1. Amount of new tickets per period e.g. month, quarter , year, YTD and comparison with the values from the previous period
    drill down:
    - Amount of new tickets from each customer
    - “ region
    - “ product
    2. Amount of tickets which were closed within a period e.g. month, quarter, year,
    drill down:
    - closed at which service level

    3. No. of tickets which are at the reporting date in status open
    drill down:
    - per customer
    - Region
    - Product

    4. Percentage of tickets which were not solved within the contractual agreed time
    drill down:
    - Customer
    - Region
    - Service level

    From the source system each incident ticket is unambiguously identified by its incident no. and the create date. The status of tickets can change at any time (e.g. open, closed). Specific timers are defined in the operative system to monitor whether contractual agreed response times are kept in dependency of the priority of each ticket.
    I defined already the cube but I´m faced with the question where to calculate the data which are not provided by the source system e.g. how (and where) to sum up the amount of tickets – is it sensible to calculate it in the cube or is it better to do aggregation and calculation in a pre-system or in Excel?
    As this is only the first step and other measures will be more complex it would be helpful to get in contact with someone who has already gained experience in the data modeling for this purpose.

    thanks & best regards

  • RE: data modeling IT service management / case tracking system

    Hi Rainer,
    the idea behind an OLAP cube is rather straight forward: All calculations are done in-memory. So you don't have to care about running the calculation out of the cube to get the calculations without any performance constraint. So the model should include your aggregations as you listed them already in your thread.
    It makes also from the point of view of architecture more sense to aggregate within the cube, even based on the embedded rules, because based on this, everybody has the same view, you don't have to care about the frontend or media used, like Excel, web or like any specific client application. The definition or formula for your rule is kept and calculated centrally. If this becomes too slow on th elong run, you can use a batch rule engine. This BRE writes the calculated figures to the cube - whenever you like, independent of the activity on the cube.

    The real issue about your model and behind your question is, what to do about the moving data ...
    I'm assuming, you are not going to archieve these data, you are more or less looking for an actual status and to compare this with the previous days or periods.
    So from my understanding, you should store daily data in the cube only, so just the figure about the tickets opened, the tickets touched and the tickets closed and so on ... From your issue I read, you are aggregating only per customer, per region and so for, so doesn't seem you are storing by incident/ticket.

    To run updates is never a good idea, as well as storing in the cube every single ticket. Yes it depends on the volume of tickets, but nevertheless, MOLAP is not designed for this kind of usage scenario. DWH in general are not designed to mirror your operative data, but to store them for analysis and reporting!

    I hope this helps, Cheers Alexander
  • re data modeling IT service management / case tracking system

    Thank you Alexander and sorry for my late reply!
    Yes, indeed your comment helped a lot. I understood that it makes no sense to just mirror the operative system.

    The proper planning of the cube, the dimensions and measures is an important issue. I was looking for a structured, method how to plan and design a cube. The ADAPT method seems to be very helpful and supports the set up.

    Thx & best regards