Many times, we'd like to analyze workload over time.
The following article describes a modeling solution approach to an often asked question: "How many active items are there per day?"
Counting the number of open tickets per date.
- Many to Many relationships - There can be multiple tickets per date, and there are multiple dates in which the ticket was open (all dates between Start Date & Resolved Date)
- Handling tickets that are still open - Tickets with resolved date set as Null.
New field for Resolved Date:
Creating a new field for temporary Resolved date, that will assign today's date in case the Resolved Date is Null.
The data will be updated daily, so this field will be populated daily.
Creating a relationship table connecting the Date Dimension and the Tickets tables.
Dates will be flattened. The relationship table will hold a record per each date in between the Start & Resolved dates of each ticket.
We'll use the below example to demonstrate the solution approach:
Create a new field for temporary Resolved date. For the purpose of the example, let's say today's date is 7-31 -2017. The updated Tickets table will look like:
WHEN [Resolved Date] IS NULL
ELSE [Resolved Date]
- Name the field 'New Resolved Date'
- Set the field to be of type Date-Time
- Set the original Resolved Date field as invisible.
Step 2 -
Create a relationship table connecting the Date Dimension and the Tickets tables.
SELECT distinct d.Date,
t.[New Resolved Date]
ON t.[Start Date] <= d.Date
AND d.Date <= t.[New Resolved Date]
- Connect the relationship table to the Date Dimension on the Date field.
- Connect the relationship table to the Tickets table both on Start Date and New Resolved Date fields.
Choose the date from the date dimension, and count the number of IDs per date.
- Note: If the dates in your Tickets table contain exact timestamps (rather than being converted to 12:00:00 AM), you should convert them to 12:00:00 AM of the relevant date to enable the join with the Dates dimension. This can be done using the following common syntax:
Relevant ecdata link: https://drive.google.com/open?id=0BxC_ZlZziye2MDRxdjBWVTNwVjg
Feel free to reach out if you have any questions.
Please sign in to leave a comment.