Oms API Read Run Microdata aggregated

Read a "page" of aggregated microdata values from model run.

• Aggregate one or more microdata value attribute (float of integer type attribute). For example, two aggregations: OM_AVG(Income) , OM_MAX(Salary + Pension).

• Group by one or more dimension attributes (enum-based or bool type attribute). For example, group by two dimension attributes: AgeGroup , Sex.

• Page is part of output table values defined by zero-based "start" row number and row count. If row count <= 0 then all rows below start row number returned.

• Dimension attribute(s) returned as enum codes. For boolean dimensions string value used, e.g.: "true".

• Method verb must be POST and Content-Type header "application/json".

Following aggregation functions avaliable:

• OM_AVG mean of accumulators sub-values
• OM_SUM sum of accumulators sub-values
• OM_COUNT count of accumulators sub-values (excluding NULL's)
• OM_COUNT_IF count values matching condition
• OM_MAX maximum of accumulators sub-values
• OM_MIN minimum of accumulators sub-values
• OM_VAR variance of accumulators sub-values
• OM_SD standard deviation of accumulators sub-values
• OM_SE standard error of accumulators sub-values
• OM_CV coefficient of variation of accumulators sub-values

It is also possible to use parameter(s) in calculation, parameter must be a scalar of float or integer type. For example: OM_COUNT_IF(Income > param.High), where param.High is a value of scalar parameter High in that model run.

For more details please see: Model Output Expressions

JSON body POSTed to specify entity name, page size, row count, filters and row order. It is expected to be JSON representation of db.ReadCalculteMicroLayout structure from Go library. See also: db.ReadLayout structure from Go library.

// ReadCalculteMicroLayout describe microdata generation read layout, aggregation measures and group by attributes.
type ReadCalculteMicroLayout struct {
	ReadLayout           // entity name, run id, page size, where filters and order by
	CalculateMicroLayout // microdata aggregations
}

// CalculateMicroLayout describes aggregations of microdata.
//
// It can be comparison aggregations and/or calculation aggregations.
// Comparison aggregation must contain [base] and [variant] attribute(s), ex.: OM_AVG(Income[base] - Income[variant]).
// Calculation aggregation is attribute(s) aggregation expression, ex.: OM_MAX(Income) / OM_MIN(Salary).
type CalculateMicroLayout struct {
	Calculation []CalculateLayout // aggregation measures, ex.: OM_MIN(Salary), OM_AVG(Income[base] - Income[variant])
	GroupBy     []string          // attributes to group by
}

// CalculateLayout describes calculation expression for parameters, output table values or microdata entity.
// It can be comparison calculation for multiple model runs, ex.: Expr0[base] - Expr0[variant].
type CalculateLayout struct {
	Calculate string // expression to calculate, ex.: Expr0[base] - Expr0[variant]
	CalcId    int    // calculated expression id, calc_id column in csv,     ex.: 0, 12000, 24000
	Name      string // calculated expression name, calc_name column in csv, ex.: Expr0, AVG_Expr0, RATIO_Expro0
}

// ReadLayout describes source and size of data page to read input parameter, output table values or microdata.
//
// Row filters combined by AND and allow to select dimension or attribute items,
// it can be enum codes or enum id's, ex.: dim0 = 'CA' AND dim1 IN (2010, 2011, 2012)
type ReadLayout struct {
	Name           string           // parameter name, output table name or entity microdata name
	FromId         int              // run id or set id to select input parameter, output table values or microdata from
	ReadPageLayout                  // read page first row offset, size and last page flag
	Filter         []FilterColumn   // dimension or attribute filters, final WHERE does join all filters by AND
	FilterById     []FilterIdColumn // dimension or attribute filters by enum ids, final WHERE does join filters by AND
	OrderBy        []OrderByColumn  // order by columnns, if empty then dimension id ascending order is used
}

Methods:

POST /api/model/:model/run/:run/microdata/calc

For example:

curl -v -X POST -H "Content-Type: application/json" http://localhost:4040/api/model/modelOne/run/Microdata%20in%20database/microdata/calc -d @read_m1_person_calc_1.json

Arguments:

:model - (required) model digest or model name

Model can be identified by digest or by model name. It is recommended to use digest because it is uniquely identifies model. It is possible to use model name, which is more human readable than digest, but if there are multiple models with same name in database than result is undefined.

:run - (required) model run digest, run stamp or run name

Model run can be identified by run digest, run stamp or run name. It is recommended to use digest because it is uniquely identifies model run. Run stamp, if not explicitly specified as model run option, automatically generated as timestamp string, ex.: 2016_08_17_21_07_55_123. It is also possible to use name, which is more human readable than digest, but if there are multiple runs with same name in database than result is undefined.

JSON body arguments:

Example 1: Aggregate Person entity to get OM_AVG() average Income value and group it by AgeGroup , Sex dimension attributes.

{
    "Name": "Person",
    "Calculation": [{
            "Calculate": "OM_AVG(Income)",
            "CalcId": 2401,
            "Name": "Avg_Income"
        }
    ],
    "GroupBy": [
        "AgeGroup",
        "Sex"
    ]
}

Example 2.

• aggregate Person entity
• calculate two values:
  • OM_AVG() average of Income value, adjusted by using parameter StartingSeed values
  • OM_AVG() average of Salary + Pension value, adjusted by using parameter StartingSeed values
• and group it by AgeGroup , Sex dimension attributes
• filter only rows where:
  • dimension AgeGroup IN ["20-30", "40+"]
  • and dimension Sex = "F"

{
    "Name": "Person",
    "Calculation": [{
            "Calculate": "OM_AVG(Income) * (param.StartingSeed / 100)",
            "CalcId": 2401,
            "Name": "Avg_Income_adjusted"
        }, {
            "Calculate": "OM_AVG(Salary + Pension + param.StartingSeed)",
            "CalcId": 2404,
            "Name": "Avg_Salary_Pension_adjusted"
        }
    ],
    "GroupBy": [
        "AgeGroup",
        "Sex"
    ],
    "Offset": 0,
    "Size": 100,
    "IsFullPage": true,
    "Filter": [{
            "Name": "AgeGroup",
            "Op": "IN",
            "Values": ["20-30", "40+"]
        }, {
            "Name": "Sex",
            "Op": "=",
            "Values": ["F"]
        }
    ],
    "OrderBy": [{
            "IndexOne": 2,
            "IsDesc": true
        }, {
            "IndexOne": 3,
            "IsDesc": true
        }
    ]
}

Name       - (required) entity name
Offset     - (optional) zero-based start row to select aggreagted microdata values
Size       - (optional) max row count to select rows, if size <= 0 then all rows selected
IsFullPage - (optional) if true then always return non-empty last page of data
Filter     - (optional) conditions to filter dimension attributes
OrderBy    - (optional) list of columns indexes (one based) to order by

Filter conditions joined by AND and can have following operations:

=       - enum equal to:          AgeGroup = "20-30"
!=      - enum not equal to:      AgeGroup <> "20-30"
>       - enum greater than:      AgeGroup > "20-30"
>=      - enum greater or equal:  AgeGroup >= "20-30"
<       - enum less than:         AgeGroup < "20-30"
<=      - enum less or equal:     AgeGroup <= "20-30"
IN      - enum is in the list of: AgeGroup IN ("20-30", "30-40", "40+")
BETWEEN - between min and max:    AgeGroup BETWEEN "30-40" AND "all"
IN_AUTO - automatically choose most suitable: = or != or IN or BETWEEN

Keep in mind: dimension enums are always ordered by id's, not by code and result of filter Sex < "M" may not be Sex = "F".

Order by specified by one-based column(s) index(es) in result. Columns always contain enum id's, not enum codes and therefore result ordered by id's. First two columns are run_id, calc_id:

  SELECT run_id, CalcId AS calc_id, AgeGroup, Sex, ..., calc_value FROM .... ORDER BY 1, 2,...

JSON response:

{
  Layout: {
    Offset:     actual first row number of the page data (zero-base),
    Size:       actual data page row count,
    IsLastPage: true if this is last page of data
  },
  Page: [....page of data...]
}

Result:

*   Trying [::1]:4040...
* Connected to localhost (::1) port 4040
> POST /api/model/modelOne/run/Microdata%20in%20database/microdata/calc HTTP/1.1
> Host: localhost:4040
> User-Agent: curl/8.4.0
> Accept: */*
> Content-Type: application/json
> Content-Length: 702
>
< HTTP/1.1 200 OK
< Content-Type: application/json
< Date: Fri, 29 Dec 2023 03:36:38 GMT
< Content-Length: 830
<
{
    "Page": [{
            "Attr": [{
                    "IsNull": false,
                    "Value": "40+"
                }, {
                    "IsNull": false,
                    "Value": "F"
                }, {
                    "IsNull": false,
                    "Value": 57665830.54215979
                }
            ],
            "CalcName": "Avg_Salary_Pension",
            "RunDigest": "a59c91359c4cd98f6275529c798d2485"
        }, {
            "Attr": [{
                    "IsNull": false,
                    "Value": "20-30"
                }, {
                    "IsNull": false,
                    "Value": "F"
                }, {
                    "IsNull": false,
                    "Value": 100657151.25
                }
            ],
            "CalcName": "Avg_Salary_Pension",
            "RunDigest": "a59c91359c4cd98f6275529c798d2485"
        }, {
            "Attr": [{
                    "IsNull": false,
                    "Value": "40+"
                }, {
                    "IsNull": false,
                    "Value": "F"
                }, {
                    "IsNull": false,
                    "Value": 71069306.57187325
                }
            ],
            "CalcName": "Avg_Income",
            "RunDigest": "a59c91359c4cd98f6275529c798d2485"
        }, {
            "Attr": [{
                    "IsNull": false,
                    "Value": "20-30"
                }, {
                    "IsNull": false,
                    "Value": "F"
                }, {
                    "IsNull": false,
                    "Value": 134209535
                }
            ],
            "CalcName": "Avg_Income",
            "RunDigest": "a59c91359c4cd98f6275529c798d2485"
        }
    ],
    "Layout": {
        "Offset": 0,
        "Size": 4,
        "IsLastPage": true,
        "IsFullPage": false
    }
}