1. REST API for Oracle Essbase
  2. Tasks
  3. Rules

Get Rules

get

/essbase/rest/v1/utils/rules

Gets rule file from file catalog path.

Request

Query Parameters
Back to Top

Response

Supported Media Types

200 Response

OK

Successfully returned rule file. Response type can be either JSON, XML, or JSON stream, depending on the Accept header. If Accept='application/json' or Accept='application/xml', the rules are returned in the response body. If Accept='application/octet-stream', the rules are returned as a JSON stream.

Body ()
Root Schema : Rules
Type: object
Show Source
Nested Schema : columnOperations
Type: array

Rule operations available to perform at the field level. For example, you can move a field to a new position in the record.

Show Source
Nested Schema : DataLoadOptions
Type: object

Rule operations available to perform on the data in a field; for example, changing how data affects existing values, clearing values, or flipping signs. Not applicable for dimension build rules.

Show Source
  • clearCombinations

    Array of text values to clear while loading the data.

  • Allowed Values: [ "NONE", "OVERWRITE", "ADD", "SUBTRACT" ]

    A data load option to specify how newly loaded data values affect existing data values. By default, Essbase overwrites the existing values of the cube with the values of the source data. For example, if you load weekly values, you can specify the ADD option to create monthly values in the cube.

  • Dimension name in which to flip signs. You can reverse, or flip, the value of a data field by flipping its sign. Sign flips are based on the UDA (user defined attribute) that you specify in signFlipUDA. When loading data into the accounts dimension, for example, you can specify that any record whose accounts member has a UDA of Expense change from a plus sign to a minus sign.

  • UDA (user defined attribute) for which to flip signs. You can reverse, or flip, the value of a data field by flipping its sign. Also specify the signFlipDimension.

Nested Schema : DataSource
Type: object

General Source Properties of a dimension build or data load rule. Use to broadly set how the rule behaves with respect to the source data.

Show Source
  • FileProperties

    File properties you can set for rules that are associated with flat files or Excel files. For example, the delimiter, the width (for fixed-width records), and record numbers indicating headers vs the first record to load.

  • Header string used in the source data, if known. This will be skipped during the dimension build or data load.

  • SQLProperties

    SQL Properties enable connectivity to an external system such as an RDBMS to extract data from a relational system using a query.

  • tokens

    Ignore source data records during the dimension build or data load if the record contains the specified token(s). A token is one delimited string in the source data. To specify multiple tokens, be sure to delimit them and specify a tokensCombineOption. Example of tokens delimited by space: "tokens" : [ "&& UNDEFINED" ]

  • Allowed Values: [ "AND", "OR" ]

    If you listed multiple tokens to ignore, specify AND if Essbase should ignore only records that contain all of the tokens. Specify OR if Essbase should ignore records that contain any of the tokens.

Nested Schema : DimBuildOptions
Type: object

Global properties affecting all dimensions included in a dimension build rule.

Show Source

  • Select which alias table to update with new aliases from the source data. If unspecified, dimension build updates the default alias table.

  • Set to true to arrange dimensions in hourglass order for calculation performance. Applicable to block storage cubes only. The order is: 1- densest dimensions (accounts and time), 2- remaining dense dimensions (largest to smallest), 3- sparse dimensions (smallest to largest), 4- attribute dimensions.

  • Set to true let Essbase automatically assign dimensions as dense or sparse. By default, density/sparsity settings are kept as either the existing setting or the setting specified in the dimension build rule. Applicable to block storage cubes only.

  • smartLists

    Array to add or update a text list object, also known as a Smart List. Text list objects are a way to store metrics as textual values, when your accounts dimension is designed to work with text measures and your outline is enabled for typed measures. To see a sample cube that uses a textual measure dimension, import the sample application Facility Rating, available in the gallery section of the Files catalog.

Nested Schema : dimensions
Type: array

Properties defined per dimension, to set how the rule behaves for a dimension.

Show Source
Nested Schema : EditorOptions
Type: object
Show Source
Nested Schema : EssbaseInfo
Type: object
Show Source
Nested Schema : fields
Type: array

Array of field information, including general properties for all fields, as well as information about specific fields.

Show Source
  • Field

    Field properties for performing load rule operations at the record level. For example, you can filter to select or reject certain records before they are loaded into the cube.

Nested Schema : ColumnOperation
Type: object

Rule operations available to perform at the field level. For example, you can move a field to a new position in the record.

Show Source
Nested Schema : clearCombinations
Type: array

Array of text values to clear while loading the data.

Show Source
Nested Schema : FileProperties
Type: object

File properties you can set for rules that are associated with flat files or Excel files. For example, the delimiter, the width (for fixed-width records), and record numbers indicating headers vs the first record to load.

Show Source
Nested Schema : SQLProperties
Type: object

SQL Properties enable connectivity to an external system such as an RDBMS to extract data from a relational system using a query.

Show Source

  • Essbase application name, if connectivity is to another Essbase cube.

  • Essbase database name, if connectivity is to another Essbase cube.

  • The FROM clause of the query, excluding FROM.

  • The SELECT clause of the query, excluding SELECT.

  • Connection string or Datasource info to establish SQL-based connectivity. Example for Datasource: REST;URL=LOCAL;DS=Orcl_DS. For Oracle Database with SID: oracle://somedb99:1521/orcl. For Oracle Database with service name: ORACLESERVICE:oracle://somedb99:1234/esscs.host1.oraclecloud.com. For Microsoft SQL Server: sqlserver://myMSSQLHost:1433:myDbName. For MySQL: mysql://HostName:3306:DBName. For Teradata: teradata://192.0.2.110:1025/myDBName. For IBM DB2: db2://myDB2Host:1234:myDbName.

  • The WHERE clause of the query, excluding WHERE.

Nested Schema : tokens
Type: array

Ignore source data records during the dimension build or data load if the record contains the specified token(s). A token is one delimited string in the source data. To specify multiple tokens, be sure to delimit them and specify a tokensCombineOption. Example of tokens delimited by space: "tokens" : [ "&& UNDEFINED" ]

Show Source
Nested Schema : smartLists
Type: array

Array to add or update a text list object, also known as a Smart List. Text list objects are a way to store metrics as textual values, when your accounts dimension is designed to work with text measures and your outline is enabled for typed measures. To see a sample cube that uses a textual measure dimension, import the sample application Facility Rating, available in the gallery section of the Files catalog.

Show Source
Nested Schema : SmartList
Type: object
Show Source
Nested Schema : Dimension
Type: object
Show Source
Nested Schema : AttributeOptions
Type: object
Show Source
Nested Schema : generations
Type: array
Show Source
Nested Schema : levels
Type: array
Show Source
Nested Schema : MeasureOptions
Type: object

Load rule definition properties relating primarily to the Accounts dimension.

Show Source

  • If the dimension is associated with a currency conversion application, the currency category. This is an Accounts member from the currency cube. Example: P&L.

  • Allowed Values: [ "EXISTING", "NONE", "CATEGORY", "NO_CONVERSION" ]

    Currency conversion action to take during the dimension build. Mark any members that should not be currency-converted as NO_CONVERSION. NONE does not indicate no conversion; rather, it indicates that the conversion category is unspecified (thus inherited).

  • If the dimension is associated with a currency conversion application, the currency name. This is a Country dimension type member from the currency cube. Examples: USD, CND, GPB, EUR

  • Allowed Values: [ "NONE", "NA", "MISSING", "ZERO", "MISSING_ZERO" ]

    If you set the timeBalanceOption as FIRST, LAST, or AVERAGE, then use the skip property to indicate how to calculate the parent value when missing- or zero-values are encountered. NONE: Does not skip data when calculating parent value. MISSING: Skips #MISSING data. ZERO: Skips data that equals zero. MISSING_ZERO: Skips #MISSING data and data that equals zero.

  • Allowed Values: [ "EXISTING", "NONE", "FIRST", "LAST", "AVERAGE" ]

    The time balance property, if used. By default, a parent in the time dimension is calculated based on the consolidation and formulas of its children. For example, in the Sample.Basic database, the Qtr1 member is the sum of its children (Jan, Feb, and Mar). However, setting a time balance property causes parents, for example Qtr1, to roll up differently.

  • True to mark the dimension as using two-pass calculation.

  • Allowed Values: [ "EXISTING", "NON_EXPENSE", "EXPENSE" ]

    Whether to treat accounts members as expense items. EXISTING to keep the current setting.

Nested Schema : indepDimensions
Type: array
Show Source
Nested Schema : IndepDimension
Type: object
Show Source
Nested Schema : Level
Type: object
Show Source
Nested Schema : Field
Type: object

Field properties for performing load rule operations at the record level. For example, you can filter to select or reject certain records before they are loaded into the cube.

Show Source
  • Allowed Values: [ "NOOP", "LOWER_CASE", "UPPER_CASE", "FIRST_CAPITAL_CASE" ]

    Select a character case-conversion operation to perform on incoming data while loading to Essbase. NOOP: perform no conversion. LOWER_CASE: convert upper case to lower case. UPPER_CASE: convert lower case to upper case. FIRST_CAPITAL_CASE: convert the first character to upper case and the remaining characters to lower case.

  • Convert spaces in source data fields to underscores while loading to Essbase.

  • FieldDataLoadOptions

    Field-level options you can set for data-load rules. For example, Essbase can ignore, rescale, and perform extractions on fields from the source data.

  • Date format for the field, if applicable.

  • FieldDimBuildOptions

    Field-level options you can set for dimension-build rules. For example, Essbase can ignore, split, join, and reposition fields.

  • Prefix for the field, if applicable.

  • Allowed Values: [ "AND", "OR" ]

    If you define more than one rejection filter, choose a logical join operator, AND or OR. AND means that all the defined rejection criteria must apply (if even one criterion is not met for any given record, then the filter does not apply to that record). OR means the opposite (if even one criterion is met for a given record, the filter applies).

  • rejectFilters

    Rejection filter criteria for omitting data load or dimension build records while loading to the Essbase cube. Filtration options include string or numeric matching of specific values, logical join options, and case sensitivity.

  • replaceInformation

    Replacement value specification, if the filter is designed for finding and replacing values in source records as you load them to Essbase.

  • Allowed Values: [ "AND", "OR" ]

    If you define more than one selection filter, choose a logical join operator, AND or OR. AND means that all the defined selection criteria must apply (if even one criterion is not met for any given record, then the filter does not apply to that record). OR means the opposite (if even one criterion is met for a given record, the filter applies).

  • selectFilters

    Selection filter criteria for approval of data load or dimension build records while loading to the Essbase cube. Filtration options include string or numeric matching of specific values, logical join options, and case sensitivity.

  • Text list object (smart list) associated with the field, if applicable.

  • Suffix for the field, if applicable.

  • Transform

    Transformation options for data processing that you can use in an index-based dimension build rule. Types can be COLUMN, SUBSTR for substring function, CONCAT for joins, STATICSTR for static strings, or IGNORE. The following index-based rule transformation spec is the same as the Essbase Web interface using a field expression of join(column0,column1). "transform" : {"type": "CONCAT", "nodes": [ {"type": "COLUMN", "index" : 0}, {"type": "COLUMN", "index": 1}]}}.

  • Trim leading or trailing spaces from around source data fields so that they map correctly to Essbase member names.

  • Width of the field, if applicable (if the source data fields are fixed width).

Nested Schema : FieldDataLoadOptions
Type: object

Field-level options you can set for data-load rules. For example, Essbase can ignore, rescale, and perform extractions on fields from the source data.

Show Source

  • Set to true to indicate that the field is a data (non-metadata) field.

  • Ignore (do not load) this data-source column.

  • For duplicate member outlines, specify the dimension and the referOption that indicates the build method (level reference or generation reference) that Essbase uses to map the field.

  • For duplicate member outlines, use with referOption to specify the generation or level number expected for the source data field.

  • For duplicate member outlines, specify referOption that indicates the build method (level reference or generation reference) that Essbase uses to map the field. Use level reference when fields are organized bottom-up in the source data. Use generation reference when fields are organized top down in the source data.

  • Available only for data fields. Set to true if you want to scale the values of the source data to match the scale of values stored in the cube. Must be used with scalingfactor.

  • Available only for data fields and when scale is set to true. A scaling factor, if the values of the source data are not in the same scale as the values of the cube. For example, assume the real value of sales is $5,460. If the Sales source data tracks the values in hundreds, the value is 54.6. If the Essbase cube tracks the real value, you must multiply the value coming in from the Sales source data (54.6) by 100 to have the value display correctly in the Essbase cube (as 5460).

  • Allowed Values: [ "MIN", "MAX", "AVG", "SUM", "COUNT" ]

    Column-level options to extract the source data in a specific way. Available only for data fields. MIN stores the minimim value of the incoming data, including a comparison with existing cube data. MAX stores the maximum value. SUM behaves the same as the ADD global option, adding the incoming data to existing cube data. COUNT stores the count of values present in the incoming data.

Nested Schema : FieldDimBuildOptions
Type: object

Field-level options you can set for dimension-build rules. For example, Essbase can ignore, split, join, and reposition fields.

Show Source
Nested Schema : rejectFilters
Type: array

Rejection filter criteria for omitting data load or dimension build records while loading to the Essbase cube. Filtration options include string or numeric matching of specific values, logical join options, and case sensitivity.

Show Source
Nested Schema : replaceInformation
Type: array

Replacement value specification, if the filter is designed for finding and replacing values in source records as you load them to Essbase.

Show Source
Nested Schema : selectFilters
Type: array

Selection filter criteria for approval of data load or dimension build records while loading to the Essbase cube. Filtration options include string or numeric matching of specific values, logical join options, and case sensitivity.

Show Source
Nested Schema : Transform
Type: object

Transformation options for data processing that you can use in an index-based dimension build rule. Types can be COLUMN, SUBSTR for substring function, CONCAT for joins, STATICSTR for static strings, or IGNORE. The following index-based rule transformation spec is the same as the Essbase Web interface using a field expression of join(column0,column1). "transform" : {"type": "CONCAT", "nodes": [ {"type": "COLUMN", "index" : 0}, {"type": "COLUMN", "index": 1}]}}.

Show Source
Nested Schema : AttributeBuildProperties
Type: object

Dimension build properties for attribute dimensions that use numeric ranges.

Show Source
Nested Schema : endIndepColumns
Type: array
Show Source
Nested Schema : startIndepColumns
Type: array
Show Source
Nested Schema : Filter
Type: object
Show Source
Nested Schema : ReplaceInfo
Type: object
Show Source
Nested Schema : nodes
Type: array
Show Source
  • Transform

    Transformation options for data processing that you can use in an index-based dimension build rule. Types can be COLUMN, SUBSTR for substring function, CONCAT for joins, STATICSTR for static strings, or IGNORE. The following index-based rule transformation spec is the same as the Essbase Web interface using a field expression of join(column0,column1). "transform" : {"type": "CONCAT", "nodes": [ {"type": "COLUMN", "index" : 0}, {"type": "COLUMN", "index": 1}]}}.

400 Response

Bad Request

Failed to import rule file. The catalog path information may be incorrect.

500 Response

Internal Server Error.

Back to Top

Examples

The following example shows how to get a data load or dimension build rule file definition.

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.

Script with cURL Commands - Data Load Rule

The following script requests details, in JSON format, about a data load rule for Sample Basic.

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

Sample JSON Response - Data Load Rule

The REST API returns the following details about Data.rul. 

{
  "dimensions" : [ {
    "createAttributeMembers" : true,
    "name" : "Year"
  }, {
    "createAttributeMembers" : true,
    "name" : "Product"
  }, {
    "createAttributeMembers" : true,
    "name" : "Market"
  }, {
    "createAttributeMembers" : true,
    "name" : "Scenario"
  } ],
  "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" : "\"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" : {
    "fileProperties" : {
      "delimiter" : ","
    }
  },
  "dataLoadOptions" : {
    "signFlipDimension" : "None",
    "signFlipUDA" : "None"
  }
}

Script with cURL Commands - Dimension Build Rule

The following script requests details, in JSON format, about a dimension build rule for Sample Basic.

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

Sample JSON Response - Dimension Build Rule

The REST API returns the following details about Dim_Product.rul. 

{
  "dimensions" : [ {
    "allowassociationChanges" : true,
    "allowFormulaChanges" : true,
    "allowPropertyChanges" : true,
    "allowUDAChanges" : true,
    "addMemberOption" : "PARENT_CHILD",
    "name" : "Product",
    "added" : true
  } ],
  "fields" : [ {
    "name" : "PARENT",
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "PARENT",
      "dimension" : "Product",
      "attributeDimension" : ""
    }
  }, {
    "name" : "CHILD",
    "trim" : true,
    "dimensionBuildOptions" : {
      "parent" : 0,
      "generationType" : "CHILD",
      "refer" : 0,
      "dimension" : "Product",
      "attributeDimension" : ""
    }
  }, {
    "name" : "STORAGE",
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "PROPERTY",
      "refer" : 1,
      "dimension" : "Product",
      "attributeDimension" : ""
    }
  }, {
    "name" : "CONSOLIDATION",
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "PROPERTY",
      "refer" : 1,
      "dimension" : "Product",
      "attributeDimension" : ""
    }
  }, {
    "name" : "Default",
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "ALIAS",
      "refer" : 1,
      "dimension" : "Default",
      "attributeDimension" : "",
      "alias" : "Default",
      "generation" : -1
    }
  }, {
    "name" : "ChineseNames",
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "ALIAS",
      "refer" : 1,
      "dimension" : "ChineseNames",
      "attributeDimension" : "",
      "alias" : "ChineseNames",
      "generation" : -1
    }
  }, {
    "name" : "JapaneseNames",
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "ALIAS",
      "refer" : 1,
      "dimension" : "JapaneseNames",
      "attributeDimension" : "",
      "alias" : "JapaneseNames",
      "generation" : -1
    }
  }, {
    "name" : "RussianNames",
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "ALIAS",
      "refer" : 1,
      "dimension" : "RussianNames",
      "attributeDimension" : "",
      "alias" : "RussianNames",
      "generation" : -1
    }
  }, {
    "name" : "GermanNames",
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "ALIAS",
      "refer" : 1,
      "dimension" : "GermanNames",
      "attributeDimension" : "",
      "alias" : "GermanNames",
      "generation" : -1
    }
  }, {
    "name" : "Caffeinated",
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "ATTRIBUTE_MEMBER",
      "refer" : 1,
      "dimension" : "Product",
      "attributeDimension" : "Caffeinated"
    }
  }, {
    "name" : "Ounces",
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "ATTRIBUTE_MEMBER",
      "refer" : 1,
      "dimension" : "Product",
      "attributeDimension" : "Ounces"
    }
  }, {
    "name" : "Pkg Type",
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "ATTRIBUTE_MEMBER",
      "refer" : 1,
      "dimension" : "Product",
      "attributeDimension" : "Pkg Type"
    }
  }, {
    "name" : "Intro Date",
    "trim" : true,
    "dimensionBuildOptions" : {
      "generationType" : "ATTRIBUTE_MEMBER",
      "refer" : 1,
      "dimension" : "Product",
      "attributeDimension" : "Intro Date"
    }
  } ],
  "dataSource" : {
    "fileProperties" : {
      "delimiter" : ","
    }
  },
  "editorOptions" : {
    "viewMode" : "DIMBUILD"
  },
  "studio" : true
}
Back to Top