Partitioning Data Objects

This topic explains how to partition data objects using the Unity API.

In this topic:

Introduction

Partitioning is a data management feature that subdivides the data in data objects into smaller pieces.

Use Cases

  • Improves query performance. When viewing data object records, you can filter your search by partitions. This reduces the time Oracle Unity needs to complete your search. Since you define specific partitions in the data object to search, Oracle Unity can skip irrelevant records and only search in the defined partitions.

  • Partition data objects by date. Partitions are defined to date-based data objects such as Event, Order, and Order Item. Partitioning a data object by date enables you to access specific partitions of the data object, querying by date. Using an Order data object as an example, you may want to view orders from a specific year, or specific month.

  • Efficiently manage data. Partitioning allows you to more efficiently manage your data by dividing the data into smaller, more manageable units.

Workflow

The workflow to partition a data object resembles:

  1. Identify the data object to partition (i.e. Event, Order, Purchase, etc.)

  2. Select a partition strategy (monthly, yearly, by order date, by event date, and so on)

  3. Partition the data object

Before You Begin

Note the following before you partition a data object:

  • Data object partitioning is supported by data objects that need to be processed by the Data warehouse job.

  • The "Date" partition strategy is the only available strategy.

  • The format of the strategy controls the size of the partitions. The two available formats in Oracle Unity are monthly and yearly partitions.

Important: Partitioning a Data Object is irreversible. The partition strategy for a data object can't be changed after it has been published.

Partitioning Data Objects

To partition a data object:

  1. Identify the data object to partition.

  2. Retrieve the data object metadata using the endpoint "GET /metadata/tables/<tableName>"

    Example

    Retrieve the order data object:

    GET http://{accountURL}/api-metadata/v1/<tenantAccessKey>/metadata/tables/order
    [
  {
    "tenantId": 100173,
    "name": "Order",
    "versionTS": 1579112501790,
    "active": true,
    "lastModifiedBy": "system",
    "createdTS": 1562190916762,
    "bucketingStrategy": {
      "fieldName": "ID",
      "numberOfBuckets": 16
    },
    "partitions": [
      {
        "partitionStrategy": "DATE",
        "partitionFields": [
          {
            "ptype": ".PartitionDateField",
            "targetFieldName": "OrderEntryDate",
            "referenceField": "OrderEntryTS",
            "format": "yyyyMM"
          }
        ]
      }
    ],
    "foreignKeys": [
      {
        "tenantId": 100173,
        "name": "FK_Order_Customer_CustomerID_ID",
        "versionTS": 1583969414876,
        "active": true,
        "createdTS": 1562190916674,
        "type": "CHILD",
        "sourceAttribute": "SourceCustomerID",
        "sourceColumn": "CustomerID",
        "destinationColumn": "ID",
        "destinationTable": "Customer",
        "sourceTableId": "Order",
        "foreignKeyId": "Customer.ID.CustomerID"
      },
      {
        "tenantId": 100173,
        "name": "FK_Order_Organization_OrganizationID_ID",
        "versionTS": 1583969414949,
        "active": true,
        "createdTS": 1562190916691,
        "type": "CHILD",
        "sourceAttribute": "SourceOrganizationID",
        "sourceColumn": "OrganizationID",
        "destinationColumn": "ID",
        "destinationTable": "Organization",
        "sourceTableId": "Order",
        "foreignKeyId": "Organization.ID.OrganizationID"
      },
      {
        "tenantId": 100173,
        "name": "FK_Order_Event_ID_OrderID",
        "versionTS": 1583969415054,
        "active": true,
        "createdTS": 1562190916708,
        "type": "PARENT",
        "sourceAttribute": "SourceOrderID",
        "sourceColumn": "ID",
        "destinationColumn": "OrderID",
        "destinationTable": "Event",
        "sourceTableId": "Order",
        "foreignKeyId": "Event.OrderID.ID"
      },
      {
        "tenantId": 100173,
        "name": "FK_Order_Events_ID_OrderID",
        "versionTS": 1562190916725,
        "active": true,
        "createdTS": 1562190916725,
        "type": "CHILD",
        "sourceAttribute": "SourceOrderID",
        "sourceColumn": "ID",
        "destinationColumn": "OrderID",
        "destinationTable": "Events",
        "sourceTableId": "Order",
        "foreignKeyId": "Events.OrderID.ID"
      },
      {
        "tenantId": 100173,
        "name": "FK_Order_OrderItem_ID_OrderID",
        "versionTS": 1583969415117,
        "active": true,
        "createdTS": 1562190916742,
        "type": "PARENT",
        "sourceAttribute": "SourceOrderID",
        "sourceColumn": "ID",
        "destinationColumn": "OrderID",
        "destinationTable": "OrderItem",
        "sourceTableId": "Order",
        "foreignKeyId": "OrderItem.OrderID.ID"
      }
    ],
    "tablePrimaryKey": "ID",
    "sourceAttribute": "SourceOrderID",
    "scope": "DW",
    "insertOnly": false,
    "tableId": "Order",
    "categoryId": "Transactional",
    "resourceName": "orders",
    "columns": [
      {
        "tenantId": 100173,
        "name": "ID",
        "versionTS": 1583969413199,
        "description": "Unique identifier for the object",
        "active": true,
        "createdTS": 1562190916250,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "STRING"
        },
        "tableId": "Order",
        "fieldId": "ID",
        "systemAttribute": true,
        "required": true
      },
      {
        "tenantId": 100173,
        "name": "Tenant ID",
        "versionTS": 1583969413266,
        "description": "Unique Identifier for the tenant",
        "active": true,
        "createdTS": 1562190916266,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "INT"
        },
        "tableId": "Order",
        "fieldId": "TenantID",
        "systemAttribute": true,
        "required": true
      },
      {
        "tenantId": 100173,
        "name": "Source ID",
        "versionTS": 1583969413333,
        "description": "Unique Identifier for the source ",
        "active": true,
        "createdTS": 1562190916283,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "STRING"
        },
        "tableId": "Order",
        "fieldId": "SourceID",
        "systemAttribute": true,
        "required": true
      },
      {
        "tenantId": 100173,
        "name": "Source Order ID",
        "versionTS": 1583969413402,
        "description": "(Comes from the source) unique ID ",
        "active": true,
        "createdTS": 1562190916299,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "STRING"
        },
        "tableId": "Order",
        "fieldId": "SourceOrderID",
        "systemAttribute": true,
        "required": true
      },
      {
        "tenantId": 100173,
        "name": "Source Customer ID",
        "versionTS": 1583969413472,
        "description": "(Comes from the source) unique Customer ID ",
        "active": true,
        "createdTS": 1562190916316,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "STRING"
        },
        "tableId": "Order",
        "fieldId": "SourceCustomerID",
        "systemAttribute": true,
        "required": true
      },
      {
        "tenantId": 100173,
        "name": "Customer ID",
        "versionTS": 1583969413548,
        "description": "FK to the Customer Table",
        "active": true,
        "createdTS": 1562190916332,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "STRING"
        },
        "tableId": "Order",
        "fieldId": "CustomerID",
        "systemAttribute": true,
        "required": true
      },
      {
        "tenantId": 100173,
        "name": "Source Organization ID",
        "versionTS": 1583969413619,
        "description": "(Comes from the source) unique Organization ID ",
        "active": true,
        "createdTS": 1562190916348,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "STRING"
        },
        "tableId": "Order",
        "fieldId": "SourceOrganizationID",
        "systemAttribute": true,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Organization ID",
        "versionTS": 1583969413689,
        "description": "FK to the Organization Table",
        "active": true,
        "createdTS": 1562190916365,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "STRING"
        },
        "tableId": "Order",
        "fieldId": "OrganizationID",
        "systemAttribute": true,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Type",
        "versionTS": 1583969413774,
        "description": "Type of the order, such as 'Purchase', 'Service' etc.",
        "active": true,
        "createdTS": 1562190916383,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "STRING"
        },
        "tableId": "Order",
        "fieldId": "Type",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Status",
        "versionTS": 1583969413859,
        "description": "Status of order, such as 'Completed', 'Cancelled', 'Shipping', 'Partially shipped', 'Waiting for warehouse' etc.",
        "active": true,
        "createdTS": 1562190916400,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "STRING"
        },
        "tableId": "Order",
        "fieldId": "Status",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Order Date",
        "versionTS": 1583969413938,
        "description": "Date & time when the order was placed",
        "active": true,
        "createdTS": 1562190916416,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "TIMESTAMP"
        },
        "tableId": "Order",
        "fieldId": "OrderEntryTS",
        "systemAttribute": false,
        "required": true
      },
      {
        "tenantId": 100173,
        "name": "Order Completed Date",
        "versionTS": 1583969414026,
        "description": "Time Order Is Completed",
        "active": true,
        "createdTS": 1562190916433,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "TIMESTAMP"
        },
        "tableId": "Order",
        "fieldId": "CompletedTS",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Total",
        "versionTS": 1562190916449,
        "description": "Total+Tax-Discount Price",
        "active": true,
        "createdTS": 1562190916449,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "DECIMAL",
          "typeDescription": {
            "typedesc": ".ColumnTypeDecimalDescription",
            "scale": 4,
            "precision": 18
          }
        },
        "tableId": "Order",
        "fieldId": "Total",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Sub Total",
        "versionTS": 1562190916465,
        "description": "Total Price (Including all order Items)",
        "active": true,
        "createdTS": 1562190916465,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "DECIMAL",
          "typeDescription": {
            "typedesc": ".ColumnTypeDecimalDescription",
            "scale": 4,
            "precision": 18
          }
        },
        "tableId": "Order",
        "fieldId": "SubTotal",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Tax Total",
        "versionTS": 1562190916482,
        "description": "Tax Price (Including all order Items)",
        "active": true,
        "createdTS": 1562190916482,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "DECIMAL",
          "typeDescription": {
            "typedesc": ".ColumnTypeDecimalDescription",
            "scale": 4,
            "precision": 18
          }
        },
        "tableId": "Order",
        "fieldId": "TaxTotal",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Discount Total",
        "versionTS": 1562190916497,
        "description": "Discount Price (Including all order Items)",
        "active": true,
        "createdTS": 1562190916497,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "DECIMAL",
          "typeDescription": {
            "typedesc": ".ColumnTypeDecimalDescription",
            "scale": 4,
            "precision": 18
          }
        },
        "tableId": "Order",
        "fieldId": "DiscountTotal",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Cost Total",
        "versionTS": 1562190916512,
        "description": "Cost Price",
        "active": true,
        "createdTS": 1562190916512,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "DECIMAL",
          "typeDescription": {
            "typedesc": ".ColumnTypeDecimalDescription",
            "scale": 4,
            "precision": 18
          }
        },
        "tableId": "Order",
        "fieldId": "CostTotal",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Quantity",
        "versionTS": 1583969414236,
        "description": "Number of Items purchased",
        "active": true,
        "createdTS": 1562190916528,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "INT"
        },
        "tableId": "Order",
        "fieldId": "Qty",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Award Total",
        "versionTS": 1562190916543,
        "description": "Balance of an award at the time of the activity",
        "active": true,
        "createdTS": 1562190916543,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "DECIMAL",
          "typeDescription": {
            "typedesc": ".ColumnTypeDecimalDescription",
            "scale": 4,
            "precision": 18
          }
        },
        "tableId": "Order",
        "fieldId": "AwardTotal",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Currency",
        "versionTS": 1583969414353,
        "description": "Currency used in the order",
        "active": true,
        "createdTS": 1562190916559,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "STRING"
        },
        "tableId": "Order",
        "fieldId": "Currency",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Created By",
        "versionTS": 1583969414434,
        "description": "User responsible for creating the table row",
        "active": true,
        "createdTS": 1562190916574,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "STRING"
        },
        "tableId": "Order",
        "fieldId": "CreatedBy",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Modified By",
        "versionTS": 1583969414510,
        "description": "User responsible for modifying the table row",
        "active": true,
        "createdTS": 1562190916590,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "STRING"
        },
        "tableId": "Order",
        "fieldId": "ModifiedBy",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Created Timestamp",
        "versionTS": 1583969414579,
        "description": "Date the table row was created at source",
        "active": true,
        "createdTS": 1562190916606,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "TIMESTAMP"
        },
        "tableId": "Order",
        "fieldId": "CreatedTS",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Modified Timestamp",
        "versionTS": 1583969414651,
        "description": "Date the table row was modified at source",
        "active": true,
        "createdTS": 1562190916622,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "TIMESTAMP"
        },
        "tableId": "Order",
        "fieldId": "ModifiedTS",
        "systemAttribute": false,
        "required": false
      },
      {
        "tenantId": 100173,
        "name": "Row Created Timestamp",
        "versionTS": 1583969414724,
        "description": "Date the table row was created",
        "active": true,
        "createdTS": 1562190916639,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "TIMESTAMP"
        },
        "tableId": "Order",
        "fieldId": "RowCreatedTS",
        "systemAttribute": true,
        "required": true
      },
      {
        "tenantId": 100173,
        "name": "Row Modified Timestamp",
        "versionTS": 1583969414797,
        "description": "Date the table row was modified",
        "active": true,
        "createdTS": 1562190916656,
        "type": {
          "columntype": ".ColumnPrimitiveType",
          "dataType": "TIMESTAMP"
        },
        "tableId": "Order",
        "fieldId": "RowModifiedTS",
        "systemAttribute": true,
        "required": true
      }
    ]
  }
]

  3. Send a request to update the data object's partition metadata using the endpoint "PUT /metadata/tables/<tableId>".

    Partition parameters

    Parameter Required? Data Type Description
    partitionStrategy Y String Determines the partition behavior. Must be "DATE".
    partitionFields Y Array Array of partition fields below.
    ptype Y String Partition type. Must be ".PartitionDateField"
    format Y String

    Determines the size of the partition. Must be one of:

    • "yyyyMM": Monthly partition

    • "yyyy": Yearly partition

     
    referenceField Y String

    Specify an attribute from the data object, which is used to determine the data object's partitions. This attribute must be a supported data type for the chosen partition strategy. For example, if the partition strategy is a DATE strategy, then the referenceField must be a date or timestamp data type.
    targetFieldName Y String

    Specify a name for a new attribute for the data object, which Unity generates and populates with the partition value for each data record in the data object. For example, if the partition strategy is DATE and its format is yyyyMM, Unity automatically extracts the year and month values from the referenceField attribute and uses them in the targetFieldName attribute.

    Example

    Update the "order" data object's partition strategy to monthly:

    PUT http://{accountURL}/api-metadata/v1/<tenantAccessKey>/metadata/tables/order

    Request body

    {
  "name": "Order",
  "tenantId": 0,
  "tableId": "Order",
  "tablePrimaryKey": "ID",
  "sourceAttribute": "SourceOrderD",
  "insertOnly": true,
  "resourceName": "orders",
  "scope": "DW",
  "categoryId": "Transactional",
  "partitions": [
    {
      "partitionStrategy": "DATE",
      "partitionFields": [
        {
          "ptype": ".PartitionDateField",
          "targetFieldName": "OrderDate",
          "format": "yyyyMM",
          "referenceField": "OrderTS"
        }
      ]
    }
  ]
}

  4. Publish metadata changes.

Learn more

Data Objects API

Retrieve a data object

Update a data object