Tracking Tool Availability

[HeWe]

Dear EazyBI Team,

this i think is a really tricky one question.
So i have a ticket system where for every tool i have in the field every task needs to be a ticket with a start and end date (including time) and a categorization.

What i need is following: in generall my tools are running unless i get a ticket with a time reported.
On a daily basis i want to see how much time my tool was running based on the tickets.

A day has 24*60 minutes. From this i have to substract the time needed reported in my tickets.

When i have this on daily basis i want to have the same then for weekly/monthly/annual. But this should be done automatically by the time hirarchy.

Please help me here out…i am currently wasting a lot of time with manually tracking this but i think it is possible in eazyBI.

BR, Herbert

[daina.tupule]

Do I understand this correctly? You have a custom data source with details about tool downtimes, either scheduled or unscheduled.

In this case, you might need to build a new custom cube.

eazyBI might not import well data from the current file structure:
Tool, Service Start, Service End, Category

You would like to import the duration (Service end - service start) mapped by service start or end date. The simplest way would be importing duration time instead of calculating it in eazyBI.

I would suggest preparing the source in this structure:
Tool, Service date, Duration, Category

In the mapping, I defined a new cube Service. Type in the name of the cube to create a new one. Then I mapped columns:
Tool with data type String, dimension with name Tool, and set the name column
Service date with data type Date to dimension Time
Duration min with data type integer as Measures with a name Duration min
Category with data type String as a dimension with the name Category.

Then I run the import and could create this report from import data directly:

In this setup, we are importing the service down times only. Therefore, we need to calculate the up time in minutes based on 24x60 per day.

(
  -- make it general - calculate days in periods:
  DateDiffDays(
    [Time].CurrentHierarchyMember.StartDate,
    [Time].CurrentHierarchyMember.NextStartDate
  )
  *
  -- total time per day
  24 * 60
)
- 
-- subtract all downtime
([Measures].[Duration min], [Category].DefaultMember)

Add the Up time measure to the report. Then click on the Downtime min measure and split it by category with the option Drill into Category.

Then switch to the Bar chart. Make it Vertical, Stacked. By default, Up time will be in a separate stack. Please set the option Stacked the same, for example, 1 for each metric in the report.

Daina / support@eazybi.com