The LayerNext AI system has demonstrated exceptional accuracy in answering analytical queries from BI-ready databases. When tested on Microsoft's AdventureWorks Warehouse database, it achieved an impressive 95% accuracy.

Starting with the initial understanding of the data, LayerNext can deliver fairly accurate analytical insights. This accuracy can be further enhanced through continuous human feedback.

Evaluation Process

The capability of the LayerNext AI system to answer analytical questions from a standard dataset is measured through a systematic evaluation process.

High Level Overview

The LayerNext AI system comprises two key components: MetaLake and the Insight Engine. MetaLake manages metadata from connected data sources, while the Insight Engine generates queries and answers using this metadata. For evaluation, predefined questions were submitted to the Insight Engine, and accuracy was measured against available ground truth answers.

Dataset and Schema

Used the schema of Microsoft's AdventureWorks Warehouse SQL DB which contains 28 tables and 344 total columns.

Table Architecture

The tables are organised as 4 major stars such as internet sales, reseller sales, inventory and finance.

Internet sales

Reseller Sales

Inventory

Finance

Note that all dates of the original database are shifted in order to have up to date data. Originally the AdventureWorks had data from 2010 to 2013 and we have added a shift to make the range 2021 to 2024. Other than this, no alterations were made to the schema or data records in the original database.

AI Model

Used the GPT-4o version 2024-08-06

Evaluation Matrix

We created 20 analytical questions covering key areas of the dataset, ranging from low to high data retrieval complexity. The complexity of data retrieval for each question is determined based on these parameters.

1. How many tables to join - measures the understanding of relationships of data
2. How many where conditions - measures the understanding of matching criteria required
3. No. of steps during data retrieval (i.e. with separate SQLs or sub queries in main SQL) - measures the understanding of distribution of data

Based on this, 3 complexity levels are defined as shown below.

‍

The questions can be categorized into 3 analytical objectives as this:

1. Revenue and sales performance: Which products, customers, sales territories, and promotional campaigns drive the highest revenue and sales volume.
2. Marketing and Promotions: Analyse the different reasons or factors affecting the sales growth and measure the impact of marketing and promotions.
3. Inventory Management: Understand inventory turnover and optimize stock levels, to minimize stockouts and overstocks

Here is the full list of questions:

Automatic Tuneup Process

The accuracy gained with initial metadata can be further improved by the automated tuneup facility provided by MetaLake. There are two methods of tuneup.

1. Tuneup by comparing the ground truth data query with the LayerNext Insight agent generated query
2. Tuneup from the user’s comments (@feedback) given to the analysis questions

Evaluation Results

Initial Evaluation - with initially generated metadata

‍

Auto Tuneup Round1: Using the comparison with ground truth

‍

Auto Tuneup Round2: Using direct user feedback for analysis questions

Example analytical question answered by LayerNext system

Question: Can you get the monthly percentage of total sales (online and resell) from volume discount promotions in 2024 compared to total sales. Give a table with month, total volume discount sales amount, total sales amount, percentage.

‍SQL generated by LayerNext Insight Agent

WITH VolumeDiscountPromotions AS (
    SELECT PromotionKey
    FROM ERP_Data.DimPromotion
    WHERE EnglishPromotionType = 'Volume Discount'
),
ResellerSales AS (
    SELECT d.MonthNumberOfYear, SUM(f.SalesAmount) AS VolumeDiscountSalesAmount
    FROM ERP_Data.FactResellerSales f
    JOIN ERP_Data.DimDate d ON f.OrderDateKey = d.DateKey
    WHERE f.PromotionKey IN (SELECT PromotionKey FROM VolumeDiscountPromotions)
    AND d.CalendarYear = 2024
    GROUP BY d.MonthNumberOfYear
),
InternetSales AS (
    SELECT d.MonthNumberOfYear, SUM(f.SalesAmount) AS VolumeDiscountSalesAmount
    FROM ERP_Data.FactInternetSales f
    JOIN ERP_Data.DimDate d ON f.OrderDateKey = d.DateKey
    WHERE f.PromotionKey IN (SELECT PromotionKey FROM VolumeDiscountPromotions)
    AND d.CalendarYear = 2024
    GROUP BY d.MonthNumberOfYear
),
TotalResellerSales AS (
    SELECT d.MonthNumberOfYear, SUM(f.SalesAmount) AS TotalSalesAmount
    FROM ERP_Data.FactResellerSales f
    JOIN ERP_Data.DimDate d ON f.OrderDateKey = d.DateKey
    WHERE d.CalendarYear = 2024
    GROUP BY d.MonthNumberOfYear
),
TotalInternetSales AS (
    SELECT d.MonthNumberOfYear, SUM(f.SalesAmount) AS TotalSalesAmount
    FROM ERP_Data.FactInternetSales f
    JOIN ERP_Data.DimDate d ON f.OrderDateKey = d.DateKey
    WHERE d.CalendarYear = 2024
    GROUP BY d.MonthNumberOfYear
)
SELECT 
    COALESCE(r.MonthNumberOfYear, i.MonthNumberOfYear) AS MonthNumber,
    COALESCE(r.VolumeDiscountSalesAmount, 0) + COALESCE(i.VolumeDiscountSalesAmount, 0) AS TotalVolumeDiscountSales,
    COALESCE(rt.TotalSalesAmount, 0) + COALESCE(it.TotalSalesAmount, 0) AS TotalSales,
    CASE 
        WHEN COALESCE(rt.TotalSalesAmount, 0) + COALESCE(it.TotalSalesAmount, 0) > 0 THEN 
            (COALESCE(r.VolumeDiscountSalesAmount, 0) + COALESCE(i.VolumeDiscountSalesAmount, 0)) /
            (COALESCE(rt.TotalSalesAmount, 0) + COALESCE(it.TotalSalesAmount, 0)) * 100
        ELSE 0
    END AS Percentage
FROM ResellerSales r
FULL OUTER JOIN InternetSales i ON r.MonthNumberOfYear = i.MonthNumberOfYear
FULL OUTER JOIN TotalResellerSales rt ON COALESCE(r.MonthNumberOfYear, i.MonthNumberOfYear) = rt.MonthNumberOfYear
FULL OUTER JOIN TotalInternetSales it ON COALESCE(r.MonthNumberOfYear, i.MonthNumberOfYear) = it.MonthNumberOfYear
ORDER BY MonthNumber;

‍

Final Answer