dbMango/Rms.Risk.Mango/wwwroot/docs/meta.md

Meta collections

dbMango uses meta collections to store pivot definitions and user-specific settings.

Meta collections include:

• CalculatedFields - formulas for calculated fields
• FieldsMap - various field information
• FieldDescriptors - various UI related information bout pivot columns
• Lookups - lookup definitions
• PredefinedPivots - the main storage for pivot definitions
• PredefinedPivots-[USERNAME] - pivot definitions only visible for USERNAME

CalculatedFields

The CalculatedFields object in meta collection responsible for holding formulas for generated (calculated) fields that can be added to queries. In a way these are macros to be reused. Below is the JSON format for a calculated field:

Formula field have MongoDB aggregation pipeline expression format. It must use @ instead of $ to avoid conflicts with MongoDB operators, field names are serialized with # instead of ..

JSON Format

{
  "_id" : "CalculatedFields",
  "FieldName1" : {                     // field name corresponding to calculated field name
  {
    "Formula": { JSON },               // The formula defining the calculated field using expression JSON syntax.
    "DrillDown": "string",             // The drill-down logic for the field (e.g., a MongoDB query or filter).
    "LookupDef": ["string", ...],      // An optional array of lookup definitions associated with the field.
    "AggregationOperator": "string"    // The aggregation operator (e.g., "$sum", "$avg") to be used in aggregation pipelines.
  },
  ...
}

Field Descriptions

1. Formula:

   • A string representing the formula for the calculated field.
   • Example: "$sum: $Amount".

2. DrillDown:

   • A string defining the drill-down behavior for the field.
   • Example: A MongoDB query or filter to retrieve detailed data.

3. LookupDef:

   • An optional array of strings representing lookup definitions.
   • These lookups may be used to enrich or map data for the calculated field.

4. AggregationOperator:

   • A string specifying the aggregation operator to be applied within aggregation pipeline.
   • Common values: "$sum", "$avg", "$max", etc.

Example JSON

{
  "_id" : "CalculatedFields",
  "AcctCcy" : {
    "Formula" : {
      "@cond" : {
        "else" : "@Currency",
        "if" : {
          "@gte" : [{
              "@strLenCP" : "@Currency"
            }, 6]
        },
        "then" : {
          "@substrCP" : ["@Currency", 3, 3]
        }
      }
    }
  },
  "Count" : {
    "Formula" : {
      "@literal" : 1
    }
  }
}

FieldMap

FieldMap object is designed to represent metadata about a field in a pivot table. Below is the JSON format:

{
  "_id" : "FieldsMap",
  "FieldName1" : {
  {
    "Purpose": "string",          // (string) The purpose of the field, represented by the `PivotFieldPurpose` enum.
    "Type": "string"              // (string) The fully qualified name of the field's data type (e.g., "System.String").
  },
  ...
}

Field Descriptions

2. Purpose:

   • Represents the purpose or role of the field in the pivot table.

   • Defined by the PivotFieldPurpose enum, which likely includes values such as Key, Data, or Lookup.

   • Possible Purpose values:

   1. Data:

      • Represents a field that contains the primary data to be aggregated or analyzed in the pivot table.
      • Example: Sales amount, quantity, or revenue.

   2. primary key 1:

      • Represents the first primary key field used to uniquely identify a record or group in the data.
      • Example: A unique identifier such as OrderID or CustomerID.

   3. primary key 2:

      • Represents the second primary key field, often used in conjunction with PrimaryKey1 to form a composite key.
      • Example: A secondary identifier such as ProductID in a dataset where OrderID and ProductID together form a unique key.

   4. Key:

      • Represents a general key field used for grouping or categorizing data in the pivot table.
      • Example: Fields like Region, Department, or Category.

   5. Info:

      • Represents an informational field that provides additional context or metadata but is not directly used for aggregation or grouping.
      • Example: Descriptive fields like Description or Comments.

   6. Hidden:

      • Represents a field that is not displayed in the pivot table but may still be used internally for calculations or filtering.
      • Example: Fields used for intermediate calculations or backend logic.

3. Type:

   • A string representing the fully qualified name of the field's data type (e.g., System.String, System.Int32).
   • This is used to dynamically determine the type of the field at runtime.

Example JSON

Here is an example of a SingleFieldMapping object serialized to JSON:

{
  "_id" : "FieldsMap",
  "AcctCcy" : {
    "Purpose" : "key",
    "Type" : "string"
  },
  "AtRisk" : {
    "Purpose" : "data",
    "Type" : "double"
  },
  "BlendImpact" : {
    "Purpose" : "data",
    "Type" : "double"
  },
  "Book" : {
    "Purpose" : "primary key 2",
    "Type" : "string"
  }
}

FieldDescriptors

FieldDescriptors object used to define metadata for pivot table columns. Its purpose:

• Matching column names using regular expressions.
• Customizing the appearance of columns (background and alternate background colors).
• Specifying value formatting for display.

Below is the JSON format based on its properties:

JSON Format

{
  "_id" : "FieldDescriptors",
  "Fields" : [
  {
      "NameRegex": "string",             // (string) A regular expression pattern for matching column names.
      "Background": "string",            // (string, nullable) The background color of the column (e.g., "White", "Red").
      "AlternateBackground": "string",   // (string, nullable) The alternate background color for the column.
      "Format": "string"                 // (string, nullable) The format string for displaying column values.
  },
  ...
  ]
}

Field Descriptions

1. NameRegex:

   • A string representing a regular expression pattern used to match column names.
   • Example: "^Column.*" matches all column names starting with "Column".

2. Background:

   • A string representing the background color of the column.
   • If null, the default color is "White".
   • Example: "Blue", "Red".

3. AlternateBackground:

   • A string representing the alternate background color for the column.
   • If null, the default color is "White".
   • Example: "LightGray".

4. Format:

   • A string representing the format for displaying column values.
   • Example: "C2" for currency with two decimal places, "P0" for percentages with no decimals.

---

Example JSON

{
  "_id" : "FieldDescriptors",
  "Fields" : [{
      "Background" : "#54440f57",
      "Format" : "",
      "NameRegex" : "Book$|^Ccy$|.*\\WCcy$|^Currency|.*\\WCurrency|Location|Layer|Desk|Portfolio"
    }, {
      "Background" : "#113f4857",
      "Format" : "N0",
      "NameRegex" : "Unexplained|RiskBasedUnexplained"
    }
  ]
}

PredefinedPivots

PredefinedPivots used to store pivot information. There is no need to edit it manually - there is a UI for it. If you want more details, you can refer to PivotDefinition.

PredefinedPivots-[USERNAME]

These objects uses the same format as PredefinedPivots. Pivots defined here will only be visible to USERNAME.