1. REST API for Oracle Essbase
  2. Tasks
  3. Global Datasources

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
Body ()

Datasource details.

Root Schema : datasource
Type: object
Show Source
Nested Schema : ColumnsType
Type: object
Show Source
Nested Schema : headers
Type: array
Show Source
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
Nested Schema : widths
Type: array
Show Source
Nested Schema : Column
Type: array
Show Source
Nested Schema : ColumnType
Type: object
Show Source
Nested Schema : HeaderType
Type: object
Show Source
Nested Schema : QueryParamsInfo
Type: object
Show Source

  • 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.

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

  • 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.

  • true if the Datasource query parameter is required, or false otherwise.

  • If useSubVariable is true, the name of an Essbase substitution variable.

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

    Datatype of the Datasource query parameter.

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

Back to Top

Response

Supported Media Types

200 Response

OK

Datasource created successfully.

400 Response

Bad Request

Failed to create Datasource.

Back to Top

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"
}
Back to Top