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.
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.
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,
SELECT d.[StartTime] as Time
FROM [shops] d
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:
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.
This will be the Service Level Fact table where we will count the number of employees to that granularity.
SELECT [Date] ,[Hour], [ShopId], count(distinct EmployeeId) NumOfEmployees
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.