Service Level Analysis

[intapiuser]

 Analytical Need 

When we want to measure the service level we provide our customers we can do that using many different KPIs. These differentiate between industries and management decisions.

This modeling use case is focused on employees in stores - making sure there is a sufficient amount of employees working on a specific shift to attend the changing amount of store visitors.

We will like to make sure there is a correlation between the number of employees to the number of visitors.

This can help a store manager to plan his shifts throughout the day or help chain managers to find stores that are managed with higher efficiency while making sure the customers are receiving the desired level of service.

 Modeling Challenge

Usually, we will have a table of Shifts per employee, that will have the information on when an employee started and finished a shift. The information we'll need going ahead with the analysis is actually, how many employees worked on a specific hour. We would need to ask the question - for every hour of the day, is this hour BETWEEN the start and end time of the employee shift?

Due to the fact that in Sisense all joins are inner joins, we will need to create a table that has a record per store, per employee and per hour. This table will then be aggregated to answer the above question.
 

Solution

Step 1: Modeling the Elasticube

We will start by taking care of our Time dimension.

The analysis we're looking for is to the level of the round hour. We will add a flag column to our Time dimension that will mark the round hours only. This will be used to make sure we only account for every round hour once. If your Dim Time is an imported table you can use the following script to add a new column:

CASE WHEN addhours(createdate(2000,1,1),gethour(Time)) = Time 
THEN 1 ELSE 0 END AS RoundHourFlag

If the Dim Time is derived from the existing time fields in your Fact tables, here is an example of how to incorporate the script for the new field within the existing custom query:

SELECT * , 
 CASE WHEN addhours(createdate(2000,1,1),gethour(Time)) = Time THEN 1 ELSE 0 END RoundHourFlag, 
 gethour(time) Hour_Int
FROM (
   SELECT d.[StartTime] as Time
   FROM [shops] d
 UNION
   SELECT v.[Minutes in Time]
   FROM [Visitors] v ) a

Note: This flag takes into consideration a common date of '1/1/2000' for the Time fields.

Make sure the time dimension has a complete population of all the relevant hours of the day.

You can test that by creating a custom query: 

SELECT count(*)
FROM  DimTime
WHERE RoundHourFlag=1

If the answer is 24, you have all the hours of the day.

If there are missing hours, you can add records to the dimension via a Union function. You may use the attached DimTime.xlsx file that contains a record per hour of the day, for the default date of '1/1/2000'. 

For more information regarding the time dimension, please see this article.

We will now go ahead and create a custom table that will contain a row per store, per hour.

SELECT [Date] ,[Hour], [ShopId], count(distinct EmployeeId) NumOfEmployees
FROM (
    SELECT *
    FROM (SELECT t.[Time] hour
            FROM [Dim Time] t
            WHERE t.[RoundHourFlag]=1) h
    JOIN [FactEmpShifts] f
    ON  h.[Hour] BETWEEN f.[StartTime] AND f.[EndTime] ) m
GROUP BY [Date],[Hour], [ShopId]

Connect the new fact table to the rest of the scheme by linking to DimStore, DimDate, DimTime, and any other relevant dimension table.

 

Step 2 : Creating the Dashboard

Now we can create our Service Level widget. Make sure to use the common Dim Time field for the X-axis to allow adding other fields from different Fact tables that are also directly connected to the Dim Time table (Visitors, for example):
  

A suggested calculation for the service level metric would be the number of employees per visitor, on average:

AVG (No. Employees) / AVG (No. Visitors)

We can create this calculation in an Indicator and set a Pulse alert to be notified if the Service level exits the agreed boundaries.

That's it!