REST API for Oracle Essbase

Create Global Datasource

post

/essbase/rest/v1/datasources

Creates a global-level Datasource based on specified inputs. name, connection, and type are required inputs for all types of Datasources. Other required inputs differ based on the type of Datasource.

Request

Supported Media Types

application/json
application/xml

Datasource details.

Root Schema : datasource

Type: object

Show Source

application(required): string

Applicable if the type of Datasource is ESSBASE. The Essbase application name.
columns(required): object ColumnsType
connection(required): string

Connection used for this Datasource.
cube(required): string

Applicable if the type of Datasource is ESSBASE. The Essbase database name.
customDelimiter: string

Custom delimiter of the data records, if the value provided for delimiter is Custom.
delimeter: string

Delimiter of the data records, if Datasource type is DELIMITEDFILE. Delimiter can be Comma for CSV format or Tab for tab-separated format. To use a custom delimiter, use value Custom, and provide the delimiter as a value to customDelimiter.
description(required): string

Optional description of this Datasource.
endRow: integer(int64)

For a Datasource that is an Excel or text file, the ending row number.
headerRow: integer(int64)

For a Datasource that is an Excel or text file, the header row number. 0 if there is no header.
headers: array headers
ignoreErrorRecords: boolean
links: array links
name: string

Datasource name. Must begin with a letter. Can contain only letters, underscore character, and digits.
query(required): string

Query associated with the Datasource. For example, a SQL query for an external database, or an MDX query for another Essbase cube. The query selects which data you want to make available in this Datasource.
queryParameters: array queryParameters

Parameter implementation details, if the Datasource query is parameterized. For example, if the query includes a ? placeholder for passing a parameter, as in the following query: select * from profit_data where year=?, then you need define the implementation details.
sheet(required): string

For a Datasource that is an Excel file, the worksheet name.
skipHiddenRows: boolean
startRow(required): integer(int64)

Optional (default is 1 if not given). For a Datasource that is an Excel or text file, the starting data row number, excluding headerRow if one exists. For example, if headerRow is specified as 1 and startRow is specified as 10, the actual starting data row will be 11.
type(required): string

Allowed Values: [ "TEMPLATE", "EXCELFILE", "DB", "DELIMITEDFILE", "FIXEDWIDTHFILE", "BI", "ESSBASE", "JDBC", "SPARK", "MS_SQL", "MYSQL", "DB2", "ORACLE", "FILE" ]
widths(required): array widths

{
    "type":"object",
    "required":[
        "application",
        "columns",
        "connection",
        "cube",
        "description",
        "query",
        "sheet",
        "startRow",
        "type",
        "widths"
    ],
    "properties":{
        "type":{
            "type":"string",
            "enum":[
                "TEMPLATE",
                "EXCELFILE",
                "DB",
                "DELIMITEDFILE",
                "FIXEDWIDTHFILE",
                "BI",
                "ESSBASE",
                "JDBC",
                "SPARK",
                "MS_SQL",
                "MYSQL",
                "DB2",
                "ORACLE",
                "FILE"
            ]
        },
        "connection":{
            "type":"string",
            "description":"<p>Connection used for this Datasource.</p>"
        },
        "description":{
            "type":"string",
            "description":"<p>Optional description of this Datasource.</p>"
        },
        "columns":{
            "$ref":"#/definitions/ColumnsType"
        },
        "name":{
            "type":"string",
            "description":"<p>Datasource name. Must begin with a letter. Can contain only letters, underscore character, and digits.</p>"
        },
        "ignoreErrorRecords":{
            "type":"boolean"
        },
        "delimeter":{
            "type":"string",
            "description":"<p>Delimiter of the data records, if Datasource <i>type</i> is <code>DELIMITEDFILE</code>. Delimiter can be <code>Comma</code> for CSV format or <code>Tab</code> for tab-separated format. To use a custom delimiter, use value <code>Custom</code>, and provide the delimiter as a value to <i>customDelimiter</i>.</p>",
            "xml":{
                "name":"delimiter"
            }
        },
        "customDelimiter":{
            "type":"string",
            "description":"<p>Custom delimiter of the data records, if the value provided for <i>delimiter</i> is <code>Custom</code>.</p>"
        },
        "query":{
            "type":"string",
            "description":"<p>Query associated with the Datasource. For example, a SQL query for an external database, or an MDX query for another Essbase cube. The query selects which data you want to make available in this Datasource.</p>"
        },
        "application":{
            "type":"string",
            "description":"<p>Applicable if the <i>type</i> of Datasource is ESSBASE. The Essbase application name.</p>"
        },
        "cube":{
            "type":"string",
            "description":"<p>Applicable if the <i>type</i> of Datasource is ESSBASE. The Essbase database name.</p>"
        },
        "startRow":{
            "type":"integer",
            "format":"int64",
            "description":"<p>Optional (default is 1 if not given). For a Datasource that is an Excel or text file, the starting data row number, excluding headerRow if one exists. For example, if headerRow is specified as 1 and startRow is specified as 10, the actual starting data row will be 11.</p>"
        },
        "endRow":{
            "type":"integer",
            "format":"int64",
            "description":"<p>For a Datasource that is an Excel or text file, the ending row number.</p>"
        },
        "headerRow":{
            "type":"integer",
            "format":"int64",
            "description":"<p>For a Datasource that is an Excel or text file, the header row number. 0 if there is no header.</p>"
        },
        "sheet":{
            "type":"string",
            "description":"<p>For a Datasource that is an Excel file, the worksheet name.</p>"
        },
        "skipHiddenRows":{
            "type":"boolean"
        },
        "widths":{
            "type":"array",
            "items":{
                "type":"integer",
                "format":"int64",
                "xml":{
                    "name":"widths"
                }
            }
        },
        "queryParameters":{
            "type":"array",
            "description":"<p>Parameter implementation details, if the Datasource query is parameterized. For example, if the query includes a <code>?</code> placeholder for passing a parameter, as in the following query: <code>select * from profit_data where year=?</code>, then you need define the implementation details.</p>",
            "items":{
                "xml":{
                    "name":"queryParameters"
                },
                "$ref":"#/definitions/QueryParamsInfo"
            }
        },
        "headers":{
            "type":"array",
            "items":{
                "xml":{
                    "name":"headers"
                },
                "$ref":"#/definitions/HeaderType"
            }
        },
        "links":{
            "type":"array",
            "items":{
                "$ref":"#/definitions/Link"
            }
        }
    },
    "xml":{
        "name":"datasource"
    }
}

Nested Schema : ColumnsType

Type: object

Show Source

column(required): array Column

{
    "type":"object",
    "required":[
        "column"
    ],
    "properties":{
        "column":{
            "type":"array",
            "xml":{
                "name":"Column"
            },
            "items":{
                "xml":{
                    "name":"Column"
                },
                "$ref":"#/definitions/ColumnType"
            }
        }
    }
}

Nested Schema : headers

Type: array

Show Source

Array of: object HeaderType

{
    "type":"array",
    "items":{
        "xml":{
            "name":"headers"
        },
        "$ref":"#/definitions/HeaderType"
    }
}

Nested Schema : links

Type: array

Show Source

Array of: object Link

{
    "type":"array",
    "items":{
        "$ref":"#/definitions/Link"
    }
}

Nested Schema : queryParameters

Type: array

Parameter implementation details, if the Datasource query is parameterized. For example, if the query includes a ? placeholder for passing a parameter, as in the following query: select * from profit_data where year=?, then you need define the implementation details.

Show Source

Array of: object QueryParamsInfo

{
    "type":"array",
    "description":"<p>Parameter implementation details, if the Datasource query is parameterized. For example, if the query includes a <code>?</code> placeholder for passing a parameter, as in the following query: <code>select * from profit_data where year=?</code>, then you need define the implementation details.</p>",
    "items":{
        "xml":{
            "name":"queryParameters"
        },
        "$ref":"#/definitions/QueryParamsInfo"
    }
}

Nested Schema : widths

Type: array

Show Source

Array of: integer(int64)

{
    "type":"array",
    "items":{
        "type":"integer",
        "format":"int64",
        "xml":{
            "name":"widths"
        }
    }
}

Nested Schema : Column

Type: array

Show Source

Array of: object ColumnType

{
    "type":"array",
    "xml":{
        "name":"Column"
    },
    "items":{
        "xml":{
            "name":"Column"
        },
        "$ref":"#/definitions/ColumnType"
    }
}

Nested Schema : ColumnType

Type: object

Show Source

format: string
index: integer(int32)
name: string
nullable: boolean
system: boolean
type: string

Allowed Values: [ "STRING", "DOUBLE", "DATE", "TIMESTAMP", "LONG" ]

{
    "type":"object",
    "properties":{
        "name":{
            "type":"string",
            "xml":{
                "attribute":true
            }
        },
        "type":{
            "type":"string",
            "xml":{
                "attribute":true
            },
            "enum":[
                "STRING",
                "DOUBLE",
                "DATE",
                "TIMESTAMP",
                "LONG"
            ]
        },
        "nullable":{
            "type":"boolean",
            "xml":{
                "attribute":true
            }
        },
        "format":{
            "type":"string",
            "xml":{
                "attribute":true
            }
        },
        "index":{
            "type":"integer",
            "format":"int32",
            "xml":{
                "attribute":true
            }
        },
        "system":{
            "type":"boolean",
            "xml":{
                "attribute":true
            }
        }
    }
}

Nested Schema : HeaderType

Type: object

Show Source

cell: string
name: string

{
    "type":"object",
    "properties":{
        "name":{
            "type":"string",
            "xml":{
                "attribute":true
            }
        },
        "cell":{
            "type":"string",
            "xml":{
                "attribute":true
            }
        }
    }
}

Nested Schema : Link

Type: object

Show Source

href: string
method: string
rel: string
type: string

{
    "type":"object",
    "properties":{
        "rel":{
            "type":"string"
        },
        "href":{
            "type":"string"
        },
        "method":{
            "type":"string"
        },
        "type":{
            "type":"string"
        }
    }
}

Nested Schema : QueryParamsInfo

Type: object

Show Source

defaultValue: string

A fixed, default parameter value that the Datasource should use as a fallback in case the parameter has an invalid context at runtime. Example: Jan. Required only if the Datasource query is parameterized (it includes a ? placeholder for passing a parameter) AND the placeholder is not intended to reference a substitution variable nor a user-defined function developed in the external source.
index: integer(int32)

Ordinal index of the Datasource query parameter. For example, 1 for the first parameter, 2 for the second parameter, etc.
name: string

Optional name for the Datasource query parameter, meaningful for your use case. For example, instead of Param1 you can use param_G_month to indicate that the parameter uses a global variable for the current month, or you can rename it to param_appName_month to indicate that the parameter uses an application-level variable for the current month.
required: boolean

true if the Datasource query parameter is required, or false otherwise.
subVariableName: string

If useSubVariable is true, the name of an Essbase substitution variable.
type: string

Allowed Values: [ "STRING", "DOUBLE", "DATE", "TIMESTAMP", "LONG" ]

Datatype of the Datasource query parameter.
useSubVariable: boolean

true if the Datasource query parameter references an Essbase substitution variable, or false otherwise.

{
    "type":"object",
    "properties":{
        "index":{
            "type":"integer",
            "format":"int32",
            "description":"<p>Ordinal index of the Datasource query parameter. For example, 1 for the first parameter, 2 for the second parameter, etc.</p>"
        },
        "name":{
            "type":"string",
            "description":"<p>Optional name for the Datasource query parameter, meaningful for your use case. For example, instead of Param1 you can use param_G_month to indicate that the parameter uses a global variable for the current month, or you can rename it to param_appName_month to indicate that the parameter uses an application-level variable for the current month.</p>"
        },
        "defaultValue":{
            "type":"string",
            "description":"<p>A fixed, default parameter value that the Datasource should use as a fallback in case the parameter has an invalid context at runtime. Example: <b>Jan</b>. Required only if the Datasource query is parameterized (it includes a <code>?</code> placeholder for passing a parameter) AND the placeholder is not intended to reference a substitution variable nor a user-defined function developed in the external source.</p>"
        },
        "required":{
            "type":"boolean",
            "description":"<p><b>true</b> if the Datasource query parameter is required, or <b>false</b> otherwise.</p>"
        },
        "useSubVariable":{
            "type":"boolean",
            "description":"<p><b>true</b> if the Datasource query parameter references an Essbase substitution variable, or <b>false</b> otherwise.</p>"
        },
        "subVariableName":{
            "type":"string",
            "description":"<p>If <i>useSubVariable</i> is <b>true</b>, the name of an Essbase substitution variable.</p>"
        },
        "type":{
            "type":"string",
            "description":"<p>Datatype of the Datasource query parameter.</p>",
            "enum":[
                "STRING",
                "DOUBLE",
                "DATE",
                "TIMESTAMP",
                "LONG"
            ]
        }
    }
}

Response

Supported Media Types

application/json
application/xml

200 Response

Datasource created successfully.

400 Response

Bad Request

Failed to create Datasource.

Examples

The following examples show how to create a global Datasource.

A Datasource is an object in Essbase that you use to manage data flow into and out of Essbase cubes. You can define a Datasource to represent any external source of data, whether a relational system, a table, a file, or another cube. You can define one connection and use it to access multiple Datasources. Consider an external Oracle Database server that has separate tables for products, resellers, and sales territories. You need only one connection to access Oracle Database, but it might be useful to have unique Datasources for access to each of the tables.

The examples assume you have already created the required connections. Before you can create a Datasource for a file or other source of data, you must create connections to the sources, so that the Datasource can reference the connection. See Create Connection endpoint.

These examples use 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 Command

call properties.bat
curl -X POST "https://myserver.example.com:9001/essbase/rest/v1/datasources/?links=none" -H Accept:application/json -H Content-Type:application/json --data "@./DS_details.json" -u %User%:%Password%

The cURL example above delivers a JSON payload to Essbase in DS_details.json. The details you include in the payload determine what kind of Datasource is created.

See below for example JSON payloads that let you create Datasources for different sources supported by Essbase.

Sample JSON Payload - Delimited Text File

Consider a text file of data like the following, in which the first record is a header row, and the fields are delimited by #.

Product#Scenario#Measures#Mar#Apr
Colas#Actual#Opening Inventory#2041#2108
Colas#Actual#Ending Inventory#2108#2250
Colas#Budget#Opening Inventory#1980#2040
Colas#Budget#Ending Inventory#2040#2170
Root Beer#Actual#Opening Inventory#2378#2644
Root Beer#Actual#Ending Inventory#2644#2944
Root Beer#Budget#Opening Inventory#2220#2450
Root Beer#Budget#Ending Inventory#2450#2710

The following sample JSON payload, passed by cURL to REST API in DS_details.json, is an example for creating a Datasource for a text file like the one above.

The required parameters are name, connection, type, and columns. For each Column, provide its name, type, and ordinal index starting with 0 for the first column.

{
  "name" : "delimitedfile_DS"
  "connection" : "delimitedfile_conn",
  "type" : "DELIMITEDFILE",
  "delimiter" : "Custom",
  "customDelimiter" : "#",
  "headerRow" : 1,
  "startRow" : 1,
  "columns" : {
    "Column" : [ {
      "name" : "Product",
      "type" : "STRING",
      "index" : 0
    }, {
      "name" : "Scenario",
      "type" : "STRING",
      "index" : 1
    }, {
      "name" : "Measures",
      "type" : "STRING",
      "index" : 2
    }, {
      "name" : "Mar",
      "type" : "STRING",
      "index" : 3
    }, {
      "name" : "Apr",
      "type" : "STRING",
      "index" : 4
    } ]
  }
}

Sample JSON Payload - Excel Workbook

Consider an Excel workbook in which the first record is a header row, and the data is on a worksheet named SpendHistory. The header row includes columns Year, Quarter, Month, Purchase Organization, Category, Product Name, Org Name, Suppliers, Spend, Addressable Spend, and more.

Note:

This workbook is available in the File Catalog: All Files/ gallery/ Technical/Table Format/Unstr_NoHints.xlsx.

The following sample JSON payload, passed by cURL to REST API in DS_details.json, is an example for creating a Datasource for an Excel worksheet like the one above.

The required parameters are name, connection, sheet, type, and columns. For each Column, provide each column's name, alias, type, and ordinal index starting with 0 for the first column.

{
	"connection": "excel_conn",
	"description": "Datasource to Excel file",
	"headerRow": 1,
	"name": "excelDS",
	"sheet": "SpendHistory",
	"startRow": 1,
	"type": "EXCELFILE",
	"columns": {
		"Column": [
			{
				"index": 1,
				"name": "Year",
				"type": "STRING"
			},
			{
				"index": 2,
				"name": "Quarter",
				"type": "STRING"
			},
			{
				"index": 3,
				"name": "Month",
				"type": "STRING"
			},
			{
				"index": 4,
				"name": "Purchase Organization",
				"type": "STRING"
			},
			{
				"index": 5,
				"name": "Category",
				"type": "STRING"
			},
			{
				"index": 6,
				"name": "Product Name",
				"type": "STRING"
			},
			{
				"index": 7,
				"name": "Org Name",
				"type": "STRING"
			},
			{
				"index": 8,
				"name": "Suppliers",
				"type": "STRING"
			},
			{
				"index": 9,
				"name": "Spend",
				"type": "STRING"
			},
			{
				"index": 10,
				"name": "Addressable Spend",
				"type": "STRING"
			},
			{
				"index": 11,
				"name": "Non-Addressable Spend",
				"type": "STRING"
			},
			{
				"index": 12,
				"name": "Invoiced Quantity",
				"type": "STRING"
			},
			{
				"index": 13,
				"name": "Invoiced Amount",
				"type": "STRING"
			}
		]
	}
}

Sample JSON Payload - Oracle Database

The following sample JSON payload, passed by cURL to REST API in DS_details.json, is an example for creating a Datasource for Oracle Database. The required parameters are name, connection, type, columns, and query. If the query is parameterized, provide the implementation details as queryParameters. See Implement Parameters for Datasources for more information.

{
  "name" : "Orcl_DS",
  "connection" : "oraConn",
  "type" : "DB",
  "columns" : {
    "Column" : [ {
      "name" : "DIMENSION_PRODUCT",
      "type" : "STRING",
      "index" : 1
    }, {
      "name" : "DIMENSION_MARKET",
      "type" : "STRING",
      "index" : 2
    }, {
      "name" : "DIMENSION_YEAR",
      "type" : "STRING",
      "index" : 3
    }, {
      "name" : "DIMENSION_SCENARIO",
      "type" : "STRING",
      "index" : 4
    }, {
      "name" : "SALES",
      "type" : "DOUBLE",
      "index" : 5
    }, {
      "name" : "COGS",
      "type" : "DOUBLE",
      "index" : 6
    }, {
      "name" : "MARKETING",
      "type" : "DOUBLE",
      "index" : 7
    }, {
      "name" : "PAYROLL",
      "type" : "DOUBLE",
      "index" : 8
    }, {
      "name" : "MISC",
      "type" : "DOUBLE",
      "index" : 9
    }, {
      "name" : "INITIAL_INVENTORY",
      "type" : "DOUBLE",
      "index" : 10
    }, {
      "name" : "ADDITIONS",
      "type" : "DOUBLE",
      "index" : 11
    } ]
  },
  "query" : "select * from SB_DATA where dimension_year=?",
  "queryParameters" : [ {
    "index" : 1,
    "name" : "Param1",
    "required" : false,
    "useSubVariable" : true,
    "subVariableName" : "CurrMonth",
    "type" : "STRING"
  } ]
}

Sample JSON Payload - Another Essbase Cube

The following sample JSON payload, passed by cURL to REST API in DS_details.json, is an example for creating a Datasource for another Essbase cube. The required parameters are name, connection, type, columns, application, cube, and query.

{
  "name":"EssbaseDS",
  "type":"ESSBASE",
  "connection":"essconn",
  "columns":{
    "Column":[
      {
        "index":1,
        "name":"Measures",
        "type":"STRING"
      },
      {
        "index":2,
        "name":"Oregon",
        "type":"STRING"
      },
      {
        "index":3,
        "name":"Florida",
        "type":"STRING"
      },
      {
        "index":4,
        "name":"Utah",
        "type":"STRING"
      }]
  },
  "query":"select {Sales} on rows ,{Oregon, Florida, Utah} on columns",
  "application":"Sample",
  "cube":"Basic"
}