REST API for Oracle Essbase

Create Global Datasource

post

/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

There are no request parameters for this operation.

Response

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"
}