REST API for Oracle Essbase

Create Rules

post

/utils/rules

Creates a rule file.

Request

Query Parameters

overwrite:

Optional overwrite specification. Default value is false, meaning if the rules file already exists, an error is returned. When set to true, if the rule file already exists, it will be overwritten.
path:

Optional file catalog path.

Response

200 Response

Rules file created successfully.

400 Response

Bad Request

Failed to create rules file. The catalog path information may be incorrect.

500 Response

Internal Server Error.

Examples

The following examples show how to create data load and dimension build rule file definitions.

SQL Based Data Load Rule (DSN-less)
SQL Based Data Load Rule (using Datasource)
SQL Based Data Load Rule (using DSN)
SQL Based Data Load Rule (using Oracle Call Interface)
Dimension Build Rule (Regular)
Dimension Build Rule (Index-based)

Script with cURL Commands – SQL Based Data Load Rule (DSN-less)

The following script creates a SQL-based data load rule for Sample Basic.

This example uses cURL to access the REST API from a Windows shell script. The calling user's ID and password are variables whose values are set in properties.bat.

call properties.bat
curl -X POST "https://myserver.example.com:9001/essbase/rest/v1/utils/rules?path=/applications/Sample/Basic/sqld_dsnless.rul" -H "Accept:application/json"  -H "Content-Type:application/json" --data "@./sqld_dsnless.json" -u %User%:%Password%

Sample JSON Payload

The cURL example above delivers the following JSON payload in sqld_dsnless.json to create the rule. The server parameter in the SQL properties uses a DSN-less connection string to establish connectivity to Oracle Database.

{
  "dimensions" : [ ],
  "fields" : [ {
    "name" : "Product",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    }
  }, {
    "name" : "Market",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    }
  }, {
    "name" : "Year",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    }
  }, {
    "name" : "Scenario",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    }
  }, {
    "name" : "SALES",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    },
    "dataloadOptions" : {
      "data" : true
    }
  }, {
    "name" : "STATENAME",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    },
    "dataloadOptions" : {
      "ignore" : true,
      "data" : true
    }
  }, {
    "name" : "COGS",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    },
    "dataloadOptions" : {
      "data" : true
    }
  }, {
    "name" : "MARKETING",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    },
    "dataloadOptions" : {
      "data" : true
    }
  }, {
    "name" : "PAYROLL",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    },
    "dataloadOptions" : {
      "data" : true
    }
  }, {
    "name" : "MISC",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    },
    "dataloadOptions" : {
      "data" : true
    }
  }, {
    "name" : "Opening Inventory",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    },
    "dataloadOptions" : {
      "data" : true
    }
  }, {
    "name" : "ADDITIONS",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    },
    "dataloadOptions" : {
      "data" : true
    }
  } ],
  "dataSource" : {
    "tokensCombineOption" : "OR",
    "sqlProperties" : {
      "server" : "oracle://somedb99:1521/orcl",
      "select" : " aa.SKU, ac.STATE, SUM(ae.AMOUNT) from TBC.PRODUCT aa, TBC.FAMILY ab, TBC.MARKET ac, TBC.REGION ad, TBC.SALES ae where aa.FAMILYID = ab.FAMILYID   AND ac.REGIONID = ad.REGIONID   AND ae.PRODUCTID = aa.PRODUCTID   AND ae.STATEID = ac.STATEID   AND aa.SKU is NOT NULL AND ac.STATE is NOT NULL  GROUP BY ab.FAMILY, aa.SKU, ad.REGION, ac.STATE ORDER BY   1  ASC ,  2  ASC"
    }
  }
}

Script with cURL Commands – SQL Based Data Load Rule (using Datasource)

The following script creates a SQL-based data load rule for Sample Basic.

call properties.bat
curl -X POST "https://myserver.example.com:9001/essbase/rest/v1/utils/rules?path=/applications/Sample/Basic/sqlrulDS.rul" -H "Accept:application/json" -H "Content-Type:application/json" --data "@./sqlrulDS.json" -u %User%:%Password%

Sample JSON Payload

The cURL example above delivers the following JSON payload in sqlrulDS.json to create the rule. The server parameter in the SQL properties uses a global Datasource named Orcl_DS to establish connectivity to Oracle Database.

{
  "dimensions" : [ ],
  "fields" : [ {
    "name" : "Product",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    }
  }, {
    "name" : "Market",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    }
  }, {
    "name" : "Year",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    }
  }, {
    "name" : "Scenario",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    }
  }, {
    "name" : "SALES",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    },
    "dataloadOptions" : {
      "data" : true
    }
  } ],
  "dataSource" : {
    "sqlProperties" : {
      "server" : "REST;URL=LOCAL;DS=Orcl_DS",
      "select" : " * FROM Orcl_DS"
    }
  }
}

Script with cURL Commands – SQL Based Data Load Rule (using DSN)

The following script creates a SQL-based data load rule for Sample Basic.

call properties.bat
curl -X POST "https://myserver.example.com:9001/essbase/rest/v1/utils/rules?path=/applications/Sample/Basic/sqld_dsn.rul" -H "Accept:application/json" -H "Content-Type:application/json" --data "@./sqld_dsn.json" -u %User%:%Password%

Sample JSON Payload

The cURL example above delivers the following JSON payload in sqld_dsn.json to create the rule. The SQL properties specify that Essbase uses an ODBC driver and DSN name to establish connectivity to Oracle Database. TBC_ORA is an ODBC data source configured in odbc.ini on the Essbase machine.

{
  "dimensions" : [ ],
  "fields" : [ {
    "name" : "Product",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    }
  }, {
    "name" : "Market",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    }
  }, {
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    },
    "dataloadOptions" : {
      "data" : true
    }
  } ],
  "dataSource" : {
    "name" : "TBC_ORA",
    "tokensCombineOption" : "OR",
    "sqlProperties" : {
      "select" : " aa.SKU, ac.STATE, SUM(ae.AMOUNT) from TBC.PRODUCT aa, TBC.FAMILY ab, TBC.MARKET ac, TBC.REGION ad, TBC.SALES ae where aa.FAMILYID = ab.FAMILYID   AND ac.REGIONID = ad.REGIONID   AND ae.PRODUCTID = aa.PRODUCTID   AND ae.STATEID = ac.STATEID   AND aa.SKU is NOT NULL AND ac.STATE is NOT NULL  GROUP BY ab.FAMILY, aa.SKU, ad.REGION, ac.STATE ORDER BY   1  ASC ,  2  ASC"
    }
  }
}

Script with cURL Commands – SQL Based Data Load Rule (using Oracle Call Interface)

If you use applications developed with Oracle Call Interface (OCI), you can design rules that connect to Oracle Database using the OCI connection string.

The following script creates a SQL-based data load rule for Sample Basic.

call properties.bat
curl -X POST "https://myserver.example.com:9001/essbase/rest/v1/utils/rules?path=/applications/Sample/Basic/sqld_oci.rul" -H "Accept:application/json" -H "Content-Type:application/json" --data "@./sqld_oci.json" -u %User%:%Password%

Sample JSON Payload

The cURL example above delivers the following JSON payload in sqld_oci.json to create the rule.

{
  "dimensions" : [ ],
  "fields" : [ {
    "name" : "Product",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    }
  }, {
    "name" : "Market",
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    }
  }, {
    "trim" : true,
    "dimensionBuildOptions" : {
      "refer" : 0,
      "dimension" : "",
      "attributeDimension" : ""
    },
    "dataloadOptions" : {
      "data" : true
    }
  } ],
  "dataSource" : {
    "name" : "$OCI$Ora123.example.com:1521/orcl.example.com",
    "tokensCombineOption" : "OR",
    "sqlProperties" : {
      "select" : " aa.SKU, ac.STATE, SUM(ae.AMOUNT) from TBC.PRODUCT aa, TBC.FAMILY ab, TBC.MARKET ac, TBC.REGION ad, TBC.SALES ae where aa.FAMILYID = ab.FAMILYID   AND ac.REGIONID = ad.REGIONID   AND ae.PRODUCTID = aa.PRODUCTID   AND ae.STATEID = ac.STATEID   AND aa.SKU is NOT NULL AND ac.STATE is NOT NULL  GROUP BY ab.FAMILY, aa.SKU, ad.REGION, ac.STATE ORDER BY   1  ASC ,  2  ASC"
    }
  }
}

Script with cURL Commands – Dimension Build Rule (Regular)

The following script creates a regular dimension build rule for Sample Basic.

call properties.bat
curl -X POST "https://myserver.example.com:9001/essbase/rest/v1/utils/rules?path=/applications/Sample/Basic/addproduct.rul" -H "Accept:application/json"  -H "Content-Type:application/json" --data "@./addproduct.json" -u %User%:%Password%

Sample JSON Payload

The cURL example above delivers the following JSON payload in addproduct.json to create the dimension build rule. The studio parameter absent or set to false means it is a Regular rule.

{
  "dimensions" : [ {
    "allowassociationChanges" : true,
    "allowFormulaChanges" : true,
    "addMemberOption" : "PARENT_CHILD",
    "name" : "Product",
    "added" : true
  } ],
  "fields" : [ {
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "PARENT",
      "refer" : 0,
      "dimension" : "Product",
      "attributeDimension" : "",
      "generation" : -1
    }
  }, {
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "CHILD",
      "refer" : 0,
      "dimension" : "Product",
      "attributeDimension" : "",
      "generation" : -1
    }
  } ],
  "dataSource" : {
    "fileProperties" : {
      "delimiter" : "\t"
    }
  },
  "editorOptions" : {
    "viewMode" : "DIMBUILD"
  }
}

The above rule will work to add a product category (500) and new products to Sample Basic using the following dimension source file.

Script with cURL Commands – Dimension Build Rule (Index-based)

The following script creates an index-based dimension build rule for Sample Basic.

call properties.bat
curl -X POST "https://myserver.example.com:9001/essbase/rest/v1/utils/rules?path=/applications/Sample/Basic/addcomment.rul" -H "Accept:application/json"  -H "Content-Type:application/json" --data "@./addcomment.json" -u %User%:%Password%

Sample JSON Payload

The cURL example above delivers the following JSON payload in addcomment.json to create the dimension build rule. The studio parameter set to true means it is an Index-based rule.

{
  "dimensions" : [ {
    "allowassociationChanges" : true,
    "allowFormulaChanges" : true,
    "allowPropertyChanges" : true,
    "name" : "Product",
    "added" : true
  } ],
  "fields" : [ {
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "REFMEMBER",
      "dimension" : "Product",
      "attributeDimension" : "",
      "generation" : 2
    }
  }, {
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "COMMENT",
      "refer" : 0,
      "dimension" : "Product",
      "attributeDimension" : "",
      "generation" : 2
    }
  } ],
  "dataSource" : {
    "fileProperties" : {
      "delimiter" : ","
    }
  },
  "editorOptions" : {
    "viewMode" : "DIMBUILD"
  },
  "studio" : true
}

The above rule will work to add a comment to Sample Basic using the following dimension source file.

500,"New product added 2024"