Analytics queries

Sales above 10000: 3 rows, 2 columns
Desktop sales in regions containing "e": 2 rows
    East  Desktop  18000 1
    West  Desktop  22500 1
    East  Service   6400 1
   North  Desktop  12000 1
    West  Service   9600 1
   North  Service   4800 1
Average revenue 12216.6666666667 over 7 rows
Rejected: Unknown field: Margin
Rejected: Invalid finite number: lots
Dataset JSON: {"version":1,"fields":[{"name":"Region","kind":"text"},{"name":"Product","kind":"text"},{"name":"Revenue","kind":"number"},{"name":"Units","kind":"number"}],"rows":[["East","Desktop",18000.0,12.0],["West","Desktop",22500.0,15.0],["East","Service",6400.0,8.0],["North","Desktop",12000.0,8.0],["West","Service",9600.0,12.0],["North","Service",4800.0,6.0],["East","Service",null,1.0]]}
PASS

program AnalyticsQueries;

// Atlas Analytics queries without a report or a dashboard: TAtlasAnalytics
// selects, filters and aggregates a TAtlasTable and exports the dataset JSON
// that the web example consumes. The program checks its own results and exits
// with code 1 on any mismatch.

{$APPTYPE CONSOLE}

uses
  System.SysUtils,
  Atlas.Data, Atlas.Analytics;

procedure Check(Condition: Boolean; const Message: string);
begin
  if not Condition then
    raise Exception.Create('Check failed: ' + Message);
end;

procedure Expect(const Expected, Actual, What: string);
begin
  Check(Expected = Actual, What + ': expected "' + Expected + '", got "' + Actual + '"');
end;

var
  Source, Rows, Totals: TAtlasTable;
  JSON: string;
  Rejected: Boolean;
  I: Integer;
begin
  try
    Source := TAtlasTable.Create(nil);
    try
      Source.AddColumn('Region');
      Source.AddColumn('Product');
      Source.AddColumn('Revenue', ackNumber);
      Source.AddColumn('Units', ackNumber);
      Source.AddRow(['East', 'Desktop', '18000', '12']);
      Source.AddRow(['West', 'Desktop', '22500', '15']);
      Source.AddRow(['East', 'Service', '6400', '8']);
      Source.AddRow(['North', 'Desktop', '12000', '8']);
      Source.AddRow(['West', 'Service', '9600', '12']);
      Source.AddRow(['North', 'Service', '4800', '6']);

      // Select: a projection plus conjunctive filters. Numeric fields compare
      // as numbers, text compares without case, Contains needs a text field.
      Rows := TAtlasAnalytics.Select(Source, ['Region', 'Revenue'],
        [TAtlasFilter.Create('Revenue', '10000', afGreater)]);
      try
        Writeln('Sales above 10000: ', Rows.RowCount, ' rows, ', Rows.ColumnCount, ' columns');
        Check(Rows.RowCount = 3, 'typed greater-than filter');
        Check(Rows.ColumnCount = 2, 'field projection');
      finally
        Rows.Free;
      end;
      Rows := TAtlasAnalytics.Select(Source, [],
        [TAtlasFilter.Create('Region', 'e', afContains), TAtlasFilter.Create('Product', 'desktop')]);
      try
        Writeln('Desktop sales in regions containing "e": ', Rows.RowCount, ' rows');
        Check(Rows.RowCount = 2, 'conjunctive, case-insensitive filters');
      finally
        Rows.Free;
      end;

      // Aggregate: one row per distinct dimension combination, first seen first.
      Totals := TAtlasAnalytics.Aggregate(Source, ['Region', 'Product'],
        [TAtlasMeasure.Create('Revenue', 'Revenue', amSum), TAtlasMeasure.Create('', 'Orders', amCount)]);
      try
        for I := 0 to Totals.RowCount - 1 do
          Writeln('  ', Totals.Cell(0, I):6, ' ', Totals.Cell(1, I):8, ' ', Totals.Cell(2, I):6, ' ', Totals.Cell(3, I));
        Check(Totals.RowCount = 6, 'six region/product groups');
        Expect('East', Totals.Cell(0, 0), 'first group');
        Expect('18000', Totals.Cell(2, 0), 'group sum');
      finally
        Totals.Free;
      end;

      // A missing value is skipped by numeric measures but still counted.
      Source.AddRow(['East', 'Service', '', '1']);
      Totals := TAtlasAnalytics.Aggregate(Source, [],
        [TAtlasMeasure.Create('Revenue', 'Average', amAverage), TAtlasMeasure.Create('', 'Rows', amCount)]);
      try
        Writeln('Average revenue ', Totals.Cell(0, 0), ' over ', Totals.Cell(1, 0), ' rows');
        Expect('12216.6666666667', Totals.Cell(0, 0), 'average ignores the missing value');
        Expect('7', Totals.Cell(1, 0), 'count includes every row');
      finally
        Totals.Free;
      end;

      // Queries read the source's current view: its own filter and sort apply.
      Source.SetFilter('West', TAtlasAnalytics.FieldIndex(Source, 'region'));
      Totals := TAtlasAnalytics.Aggregate(Source, [], [TAtlasMeasure.Create('Revenue', 'Revenue', amSum)]);
      try
        Expect('32100', Totals.Cell(0, 0), 'aggregate over the filtered view');
      finally
        Totals.Free;
      end;
      Source.SetFilter('');

      // Errors: unknown fields raise EAtlasAnalyticsError; a filter value that
      // does not fit the column kind raises EAtlasDataError from Atlas VCL.
      Rejected := False;
      try
        Rows := TAtlasAnalytics.Select(Source, ['Margin'], []);
        Rows.Free;
      except
        on E: EAtlasAnalyticsError do
        begin
          Writeln('Rejected: ', E.Message);
          Rejected := True;
        end;
      end;
      Check(Rejected, 'unknown field rejected');
      Rejected := False;
      try
        Rows := TAtlasAnalytics.Select(Source, [], [TAtlasFilter.Create('Revenue', 'lots')]);
        Rows.Free;
      except
        on E: EAtlasDataError do
        begin
          Writeln('Rejected: ', E.Message);
          Rejected := True;
        end;
      end;
      Check(Rejected, 'non-numeric filter value rejected');

      // DatasetJSON: the version 1 contract read by the web dashboard.
      JSON := TAtlasAnalytics.DatasetJSON(Source);
      Writeln('Dataset JSON: ', JSON);
      Check(Pos('{"name":"Revenue","kind":"number"}', JSON) > 0, 'field descriptors');
      Check(Pos('["East","Desktop",18000.0,12.0]', JSON) > 0, 'numbers are JSON numbers');
      Check(Pos('["East","Service",null,1.0]', JSON) > 0, 'missing values are null');
      Check(Source.RowCount = 7, 'queries never change the source');
    finally
      Source.Free;
    end;
    Writeln('PASS');
  except
    on E: Exception do
    begin
      Writeln(E.ClassName, ': ', E.Message);
      ExitCode := 1;
    end;
  end;
end.