logifleet-pulse-supply-chain-analytics

# LogiFleet Pulse Supply Chain Analytics Skill > Skill by [ara.so](https://ara.so) — Data Skills collection ## Overview LogiFleet Pulse is a comprehensive logistics intelligence platform combining MS SQL Server data warehousing with Power BI visualization. It provides: - **Multi-fact star schema** linking warehouse operations, fleet trips, and cross-dock activities - **Time-phased dimensions** with 15-minute granularity - **Warehouse Gravity Zones** for spatial optimization based on pick frequency and item value - **Fleet triage engine** prioritizing maintenance by revenue impact - **Cross-fact KPI harmonization** correlating inventory turnover with fuel consumption - **Predictive bottleneck detection** using historical pattern analysis Primary use cases: 3PL operators, retail chains, food distributors, any organization managing warehouse + fleet operations. ## Installation ### Prerequisites - MS SQL Server 2019+ (Standard or Enterprise edition recommended) - Power BI Desktop (latest version) - SQL Server Management Studio (SSMS) or Azure Data Studio - Access to data sources: WMS, TMS, telematics APIs ### Step 1: Deploy SQL Schema Clone the repository and navigate to the SQL scripts directory: ```bash git clone https://github.com/Empty5i/LogiCore-Analytics-Adaptive-Supply-Chain-Pulse.git cd LogiCore-Analytics-Adaptive-Supply-Chain-Pulse/sql ``` Execute the schema deployment script in SSMS: ```sql -- Run this in SSMS connected to your target database :r deploy_schema.sql -- Verify deployment SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA IN ('fact', 'dim', 'bridge') ORDER BY TABLE_SCHEMA, TABLE_NAME; ``` ### Step 2: Configure Data Sources Update the configuration file with your environment details: ```json { "sql_server": { "server": "${SQL_SERVER_HOST}", "database": "LogiFleetPulse", "authentication": "integrated" }, "data_sources": { "wms_connection": "${WMS_CONNECTION_STRING}", "telemetry_api": "${TELEMETRY_API_ENDPOINT}", "weather_api_key": "${WEATHER_API_KEY}" }, "refresh_intervals": { "warehouse_ops": "15min", "fleet_telemetry": "5min", "supplier_data": "1hour" } } ``` ### Step 3: Import Power BI Template Open `LogiFleet_Pulse_Master.pbit` in Power BI Desktop and configure the connection: ```powerquery // Connection parameters let Source = Sql.Database( "${SQL_SERVER_HOST}", "LogiFleetPulse", [Query="EXEC sp_GetWarehouseFleetSummary @StartDate='" & Text.From(Date.AddDays(DateTime.LocalNow(), -30)) & "'"] ) in Source ``` ## Core Database Schema ### Fact Tables **FactWarehouseOperations** - Tracks all warehouse activities: ```sql CREATE TABLE fact.FactWarehouseOperations ( OperationID BIGINT IDENTITY(1,1) PRIMARY KEY, TimeKey INT NOT NULL, WarehouseKey INT NOT NULL, ProductKey INT NOT NULL, OperationType VARCHAR(20) NOT NULL, -- 'PUTAWAY', 'PICK', 'PACK', 'SHIP' DwellTimeMinutes INT, PickRate DECIMAL(10,2), -- items per hour PackingTimeSeconds INT, GravityZoneID INT, CONSTRAINT FK_WO_Time FOREIGN KEY (TimeKey) REFERENCES dim.DimTime(TimeKey), CONSTRAINT FK_WO_Warehouse FOREIGN KEY (WarehouseKey) REFERENCES dim.DimWarehouse(WarehouseKey), CONSTRAINT FK_WO_Product FOREIGN KEY (ProductKey) REFERENCES dim.DimProduct(ProductKey) ); -- Columnstore index for analytics queries CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_FactWarehouseOperations ON fact.FactWarehouseOperations ( TimeKey, WarehouseKey, ProductKey, OperationType, DwellTimeMinutes ); ``` **FactFleetTrips** - Fleet telemetry and route data: ```sql CREATE TABLE fact.FactFleetTrips ( TripID BIGINT IDENTITY(1,1) PRIMARY KEY, TimeKey INT NOT NULL, VehicleKey INT NOT NULL, RouteKey INT NOT NULL, OriginGeographyKey INT NOT NULL, DestinationGeographyKey INT NOT NULL, FuelConsumedLiters DECIMAL(10,2), IdleTimeMinutes INT, LoadWeightKg DECIMAL(10,2), AverageSpeedKmh DECIMAL(5,2), MaintenanceScore DECIMAL(5,2), -- 0-100, higher = better DelayMinutes INT, DelayReason VARCHAR(100), CONSTRAINT FK_FT_Time FOREIGN KEY (TimeKey) REFERENCES dim.DimTime(TimeKey), CONSTRAINT FK_FT_Vehicle FOREIGN KEY (VehicleKey) REFERENCES dim.DimVehicle(VehicleKey) ); CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_FactFleetTrips ON fact.FactFleetTrips ( TimeKey, VehicleKey, RouteKey, FuelConsumedLiters, IdleTimeMinutes ); ``` ### Dimension Tables **DimTime** - Time intelligence with 15-minute buckets: ```sql CREATE TABLE dim.DimTime ( TimeKey INT PRIMARY KEY, FullDateTime DATETIME2 NOT NULL, Date DATE NOT NULL, Hour TINYINT NOT NULL, QuarterHour TINYINT NOT NULL, -- 0, 15, 30, 45 DayOfWeek TINYINT NOT NULL, DayName VARCHAR(10) NOT NULL, IsWeekend BIT NOT NULL, FiscalPeriod VARCHAR(10), IsHoliday BIT DEFAULT 0, ShiftName VARCHAR(20) -- 'MORNING', 'AFTERNOON', 'NIGHT' ); -- Populate time dimension EXEC sp_PopulateTimeDimension @StartDate = '2024-01-01', @EndDate = '2027-12-31'; ``` **DimProductGravity** - Product classification with gravity scoring: ```sql CREATE TABLE dim.DimProductGravity ( ProductKey INT PRIMARY KEY, SKU VARCHAR(50) NOT NULL UNIQUE, ProductName NVARCHAR(200), Category VARCHAR(50), Subcategory VARCHAR(50), UnitValue DECIMAL(10,2), FragilityScore DECIMAL(3,2), -- 0-1, higher = more fragile VelocityClass VARCHAR(20), -- 'FAST', 'MEDIUM', 'SLOW' GravityScore DECIMAL(5,2), -- Calculated: (velocity * value) / fragility OptimalZoneID INT, CONSTRAINT CHK_GravityScore CHECK (GravityScore >= 0) ); -- Function to calculate gravity score CREATE FUNCTION dbo.fn_CalculateGravityScore( @VelocityClass VARCHAR(20), @UnitValue DECIMAL(10,2), @FragilityScore DECIMAL(3,2) ) RETURNS DECIMAL(5,2) AS BEGIN DECLARE @VelocityMultiplier DECIMAL(3,2); SET @VelocityMultiplier = CASE @VelocityClass WHEN 'FAST' THEN 3.0 WHEN 'MEDIUM' THEN 1.5 WHEN 'SLOW' THEN 0.5 ELSE 1.0 END; RETURN (@VelocityMultiplier * @UnitValue) / NULLIF(@FragilityScore, 0); END; ``` ## Key Stored Procedures ### Cross-Fact KPI Query ```sql CREATE PROCEDURE sp_GetWarehouseFleetSummary @StartDate DATE, @EndDate DATE, @WarehouseKey INT = NULL AS BEGIN SET NOCOUNT ON; WITH WarehouseSummary AS ( SELECT w.WarehouseKey, w.WarehouseName, COUNT(DISTINCT wo.OperationID) AS TotalOperations, AVG(wo.DwellTimeMinutes) AS AvgDwellTime, SUM(CASE WHEN wo.OperationType = 'PICK' THEN 1 ELSE 0 END) AS TotalPicks, AVG(wo.PickRate) AS AvgPickRate FROM fact.FactWarehouseOperations wo INNER JOIN dim.DimTime t ON wo.TimeKey = t.TimeKey INNER JOIN dim.DimWarehouse w ON wo.WarehouseKey = w.WarehouseKey WHERE t.Date BETWEEN @StartDate AND @EndDate AND (@WarehouseKey IS NULL OR w.WarehouseKey = @WarehouseKey) GROUP BY w.WarehouseKey, w.WarehouseName ), FleetSummary AS ( SELECT g.WarehouseKey, -- Assuming origin is warehouse COUNT(DISTINCT ft.TripID) AS TotalTrips, SUM(ft.FuelConsumedLiters) AS TotalFuelConsumed, AVG(ft.IdleTimeMinutes) AS AvgIdleTime, SUM(ft.DelayMinutes) AS TotalDelayMinutes FROM fact.FactFleetTrips ft INNER JOIN dim.DimTime t ON ft.TimeKey = t.TimeKey INNER JOIN dim.DimGeography g ON ft.OriginGeographyKey = g.GeographyKey WHERE t.Date BETWEEN @StartDate AND @EndDate AND (@WarehouseKey IS NULL OR g.WarehouseKey = @WarehouseKey) GROUP BY g.WarehouseKey ) SELECT ws.WarehouseKey, ws.WarehouseName, ws.TotalOperations, ws.AvgDwellTime, ws.TotalPicks, ws.AvgPickRate, ISNULL(fs.TotalTrips, 0) AS TotalTrips, ISNULL(fs.TotalFuelConsumed, 0) AS TotalFuelConsumed, ISNULL(fs.AvgIdleTime, 0) AS AvgIdleTime, ISNULL(fs.TotalDelayMinutes, 0) AS TotalDelayMinutes, -- Cross-fact KPI: Fuel efficiency per pick CASE WHEN ws.TotalPicks > 0 THEN fs.TotalFuelConsumed / ws.TotalPicks ELSE 0 END AS FuelPerPick FROM WarehouseSummary ws LEFT JOIN FleetSummary fs ON ws.WarehouseKey = fs.WarehouseKey ORDER BY ws.WarehouseKey; END; GO ``` ### Predictive Bottleneck Detection ```sql CREATE PROCEDURE sp_PredictBottlenecks @ForecastDays INT = 7, @ThresholdPercentile DECIMAL(5,2) = 0.90 AS BEGIN SET NOCOUNT ON; -- Calculate historical patterns WITH HistoricalPatterns AS ( SELECT t.DayOfWeek, t.Hour, p.GravityScore, AVG(wo.DwellTimeMinutes) AS AvgDwellTime, STDEV(wo.DwellTimeMinutes) AS StdDevDwellTime, PERCENTILE_CONT(@ThresholdPercentile) WITHIN GROUP (ORDER BY wo.DwellTimeMinutes) OVER (PARTITION BY t.DayOfWeek, t.Hour) AS P90DwellTime FROM fact.FactWarehouseOperations wo INNER JOIN dim.DimTime t ON wo.TimeKey = t.TimeKey INNER JOIN dim.DimProductGravity p ON wo.ProductKey = p.ProductKey WHERE t.Date >= DATEADD(DAY, -90, GETDATE()) GROUP BY t.DayOfWeek, t.Hour, p.GravityScore ), ForecastPeriods AS ( SELECT DATEADD(DAY, n, CAST(GETDATE() AS DATE)) AS ForecastDate, DATEPART(WEEKDAY, DATEADD(DAY, n, GETDATE())) AS DayOfWeek, h.Hour FROM (SELECT TOP (@ForecastDays) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n FROM sys.objects) d CROSS JOIN (SELECT DISTINCT Hour FROM dim.DimTime) h ) SELECT fp.ForecastDate, fp.Hour, hp.AvgDwellTime, hp.P90DwellTime, CASE WHEN hp.P90DwellTime > hp.AvgDwellTime * 1.5 THEN 'HIGH' WHEN hp.P90DwellTime > hp.AvgDwellTime * 1.2 THEN 'MEDIUM' ELSE 'LOW' END AS BottleneckRisk, hp.GravityScore AS AffectedGravityZone FROM ForecastPeriods fp INNER JOIN HistoricalPatterns hp ON fp.DayOfWeek = hp.DayOfWeek AND fp.Hour = hp.Hour WHERE hp.P90DwellTime > hp.AvgDwellTime * 1.2 ORDER BY fp.ForecastDate, fp.Hour, hp.P90DwellTime DESC; END; GO ``` ## Power BI DAX Measures ### Fleet Idle Cost Calculation ```dax Fleet Idle Cost = VAR IdleCostPerHour = 45 -- USD per hour (fuel + labor) VAR TotalIdleMinutes = SUM(FactFleetTrips[IdleTimeMinutes]) RETURN (TotalIdleMinutes / 60) * IdleCostPerHour ``` ### Warehouse Efficiency Score ```dax Warehouse Efficiency Score = VAR TargetPickRate = 120 -- items per hour VAR ActualPickRate = AVERAGE(FactWarehouseOperations[PickRate]) VAR TargetDwellTime = 48 -- hours VAR ActualDwellTime = AVERAGE(FactWarehouseOperations[DwellTimeMinutes]) / 60 VAR PickEfficiency = DIVIDE(ActualPickRate, TargetPickRate, 0) VAR DwellEfficiency = DIVIDE(TargetDwellTime, ActualDwellTime, 0) RETURN (PickEfficiency * 0.6) + (DwellEfficiency * 0.4) ``` ### Cross-Fact Correlation Measure ```dax Dwell vs Idle Correlation = VAR SummaryTable = SUMMARIZE( FactWarehouseOperations, DimTime[Date], DimWarehouse[WarehouseKey], "AvgDwell", AVERAGE(FactWarehouseOperations[DwellTimeMinutes]) ) VAR FleetTable = SUMMARIZE( FactFleetTrips, DimTime[Date], DimGeography[WarehouseKey], "AvgIdle", AVERAGE(FactFleetTrips[IdleTimeMinutes]) ) VAR JoinedTable = NATURALLEFTOUTERJOIN(SummaryTable, FleetTable) RETURN CORRELATIONX(JoinedTable, [AvgDwell], [AvgIdle]) ``` ## Data Ingestion Patterns ### Incremental Load from WMS ```sql CREATE PROCEDURE sp_IncrementalLoadWarehouseOps @LastLoadTimestamp DATETIME2 AS BEGIN SET NOCOUNT ON; BEGIN TRANSACTION; -- Load new operations from external WMS table INSERT INTO fact.FactWarehouseOperations ( TimeKey, WarehouseKey, ProductKey, OperationType, DwellTimeMinutes, PickRate, PackingTimeSeconds, GravityZoneID ) SELECT t.TimeKey, w.WarehouseKey, p.ProductKey, ext.operation_type, DATEDIFF(MINUTE, ext.start_time, ext.end_time) AS DwellTimeMinutes, ext.pick_rate, ext.packing_seconds, p.OptimalZoneID FROM OPENQUERY(WMS_LINKED_SERVER, 'SELECT * FROM warehouse_operations WHERE last_updated > ?', @LastLoadTimestamp) ext INNER JOIN dim.DimTime t ON CAST(ext.operation_time AS DATETIME2) = t.FullDateTime INNER JOIN dim.DimWarehouse w ON ext.warehouse_code = w.WarehouseCode INNER JOIN dim.DimProductGravity p ON ext.sku = p.SKU WHERE NOT EXISTS ( SELECT 1 FROM fact.FactWarehouseOperations existing WHERE existing.OperationID = ext.external_id ); COMMIT TRANSACTION; -- Update last load timestamp UPDATE admin.ETLControl SET LastLoadTimestamp = GETDATE() WHERE TableName = 'FactWarehouseOperations'; END; GO ``` ### Real-Time Telemetry via REST API ```sql -- External table setup for streaming data CREATE EXTERNAL DATA SOURCE TelemetryAPI WITH ( TYPE = BLOB_STORAGE, LOCATION = '${TELEMETRY_API_ENDPOINT}', CREDENTIAL = TelemetryCredential ); -- Scheduled job to poll and insert CREATE PROCEDURE sp_PollFleetTelemetry AS BEGIN DECLARE @JsonResponse NVARCHAR(MAX); -- Call REST API (requires CLR or external script) EXEC sp_InvokeRESTAPI @Endpoint = '${TELEMETRY_API_ENDPOINT}/vehicles/active', @Method = 'GET', @Headers = 'Authorization: Bearer ${TELEMETRY_API_TOKEN}', @Response = @JsonResponse OUTPUT; -- Parse and insert JSON INSERT INTO fact.FactFleetTrips ( TimeKey, VehicleKey, RouteKey, OriginGeographyKey, DestinationGeographyKey, FuelConsumedLiters, IdleTimeMinutes, LoadWeightKg, AverageSpeedKmh, MaintenanceScore ) SELECT t.TimeKey, v.VehicleKey, r.RouteKey, og.GeographyKey, dg.GeographyKey, JSON_VALUE(trip, '$.fuel_consumed'), JSON_VALUE(trip, '$.idle_minutes'), JSON_VALUE(trip, '$.load_weight'), JSON_VALUE(trip, '$.avg_speed'), JSON_VALUE(trip, '$.maintenance_score') FROM OPENJSON(@JsonResponse, '$.trips') trip CROSS APPLY (SELECT GETDATE() AS CurrentTime) ct INNER JOIN dim.DimTime t ON DATEPART(MINUTE, ct.CurrentTime) / 15 * 15 = t.QuarterHour INNER JOIN dim.DimVehicle v ON JSON_VALUE(trip, '$.vehicle_id') = v.VehicleExternalID INNER JOIN dim.DimRoute r ON JSON_VALUE(trip, '$.route_id') = r.RouteExternalID INNER JOIN dim.DimGeography og ON JSON_VALUE(trip, '$.origin') = og.LocationCode INNER JOIN dim.DimGeography dg ON JSON_VALUE(trip, '$.destination') = dg.LocationCode; END; GO ``` ## Alert Configuration ### SQL Server Agent Job for Proactive Alerts ```sql CREATE PROCEDURE sp_CheckCriticalAlerts AS BEGIN SET NOCOUNT ON; -- Alert 1: High idle time threshold IF EXISTS ( SELECT 1 FROM fact.FactFleetTrips ft INNER JOIN dim.DimTime t ON ft.TimeKey = t.TimeKey WHERE t.Date = CAST(GETDATE() AS DATE) GROUP BY ft.VehicleKey HAVING AVG(ft.IdleTimeMinutes) > 30 ) BEGIN EXEC msdb.dbo.sp_send_dbmail @recipients = '${FLEET_MANAGER_EMAIL}', @subject = 'ALERT: Fleet Idle Time Exceeded', @body = 'One or more vehicles exceeded 30 minutes average idle time today.',