Jump to

• Request
• Response
• Examples

Get Application Datasource

get

/essbase/rest/v1/applications/{applicationName}/datasources/{datasourceName}

Returns details about the specified application-level Datasource.

Request

Path Parameters

• applicationName: string

  Application name.

• datasourceName: string

  Datasource name

Back to Top

Response

Supported Media Types

• application/json
• application/xml

200 Response

OK

Datasource details returned successfully.

Body ()

Root Schema : datasource

Type: object

Show Source

• application: string

  Applicable if the type of Datasource is ESSBASE. The Essbase application name.

• columns: object ColumnsType
• connection: string

  Connection used for this Datasource.

• cube: string

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

• customDelimiter(optional): string

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

• delimeter(optional): 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: string

  Optional description of this Datasource.

• endRow(optional): integer(int64)

  For a Datasource that is an Excel or text file, the ending row number.

• headerRow(optional): integer(int64)

  For a Datasource that is an Excel or text file, the header row number. 0 if there is no header.

• headers(optional): array headers
• ignoreErrorRecords(optional): boolean
• links(optional): array links
• name(optional): string

  Datasource name. Must begin with a letter. Can contain only letters, underscore character, and digits.

• query: 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(optional): 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: string

  For a Datasource that is an Excel file, the worksheet name.

• skipHiddenRows(optional): boolean
• startRow: 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: string
  Allowed Values: [ "TEMPLATE", "EXCELFILE", "DB", "DELIMITEDFILE", "FIXEDWIDTHFILE", "BI", "ESSBASE", "JDBC", "SPARK", "MS_SQL", "MYSQL", "DB2", "ORACLE", "FILE" ]
• widths: 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: 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(optional): string
• index(optional): integer(int32)
• name(optional): string
• nullable(optional): boolean
• system(optional): boolean
• type(optional): 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(optional): string
• name(optional): string

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

Nested Schema : Link

Type: object

Show Source

• href(optional): string
• method(optional): string
• rel(optional): string
• type(optional): string

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

Nested Schema : QueryParamsInfo

Type: object

Show Source

• defaultValue(optional): 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(optional): integer(int32)

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

• name(optional): 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(optional): boolean

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

• subVariableName(optional): string

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

• type(optional): string
  Allowed Values: [ "STRING", "DOUBLE", "DATE", "TIMESTAMP", "LONG" ]

  Datatype of the Datasource query parameter.

• useSubVariable(optional): 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"
            ]
        }
    }
}

400 Response

Bad Request

Failed to get Datasource details.

Back to Top

Examples

The following examples show how to get details about specific application-level Datasources. A Datasource can be associated with an external database, a file in the catalog, or another Essbase cube.

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 - Oracle Database

The following example gets details about a Datasource for Oracle Database, defined on application Sample.

call properties.bat
curl -X GET "https://myserver.example.com:9001/essbase/rest/v1/applications/Sample/datasources/OracleDB_DS?links=none" -H Accept:application/json -u %User%:%Password%

Example of Response Body

The following example shows the contents of the response body in JSON format.

{
  "name" : "OracleDB_DS",
  "description" : "Datasource for ORCL current month only",
  "connection" : "SAMPLE.OracleDB",
  "type" : "DB",
  "columns" : {
    "Column" : [ {
      "name" : "DIMENSION_PRODUCT",
      "type" : "STRING",
      "index" : 1,
      "system" : false
    }, {
      "name" : "DIMENSION_MARKET",
      "type" : "STRING",
      "index" : 2,
      "system" : false
    }, {
      "name" : "DIMENSION_YEAR",
      "type" : "STRING",
      "index" : 3,
      "system" : false
    }, {
      "name" : "DIMENSION_SCENARIO",
      "type" : "STRING",
      "index" : 4,
      "system" : false
    }, {
      "name" : "SALES",
      "type" : "DOUBLE",
      "index" : 5,
      "system" : false
    }, {
      "name" : "COGS",
      "type" : "DOUBLE",
      "index" : 6,
      "system" : false
    }, {
      "name" : "MARKETING",
      "type" : "DOUBLE",
      "index" : 7,
      "system" : false
    }, {
      "name" : "PAYROLL",
      "type" : "DOUBLE",
      "index" : 8,
      "system" : false
    }, {
      "name" : "MISC",
      "type" : "DOUBLE",
      "index" : 9,
      "system" : false
    }, {
      "name" : "INITIAL_INVENTORY",
      "type" : "DOUBLE",
      "index" : 10,
      "system" : false
    }, {
      "name" : "ADDITIONS",
      "type" : "DOUBLE",
      "index" : 11,
      "system" : false
    } ]
  },
  "query" : "select * from SB_DATA where dimension_year=?",
  "queryParameters" : [ {
    "index" : 1,
    "name" : "Param1",
    "required" : false,
    "useSubVariable" : true,
    "subVariableName" : "CurrMonth",
    "type" : "STRING"
  } ],
  "links" : [ ]
}

Script with cURL Command - Essbase

The following example gets details about a Datasource that is pointing to another Essbase cube.

call properties.bat
curl -X GET "https://myserver.example.com:9001/essbase/rest/v1/applications/Sample/datasources/Essbase2_DS?links=none" -H Accept:application/json -u %User%:%Password%

Example of Response Body - Essbase

The following example shows the contents of the response body in JSON format.

{
  "name" : "Essbase2_DS",
  "description" : "Essbase instance 2",
  "connection" : "SAMPLE.Essbase2",
  "type" : "ESSBASE",
  "columns" : {
    "Column" : [ {
      "name" : "Product",
      "type" : "STRING",
      "index" : 1,
      "system" : false
    }, {
      "name" : "Market",
      "type" : "STRING",
      "index" : 2,
      "system" : false
    } ]
  },
  "query" : "SELECT\n  {Market}\nON COLUMNS,\n  {Product}\nON ROWS\nFROM Sample.Basic",
  "application" : "Sample",
  "cube" : "Basic",
  "queryParameters" : [ ],
  "links" : [ ]
}

Script with cURL Command - File

The following example gets details about a Datasource that is associated with a file in the catalog.

call properties.bat
curl -X GET "https://myserver.example.com:9001/essbase/rest/v1/applications/Sample/datasources/UserDetails_DS?links=none" -H Accept:application/json -u %User%:%Password%

Example of Response Body - File

The following example shows the contents of the response body in JSON format.

{
  "name" : "UserDetails_DS",
  "description" : "User details repository",
  "connection" : "SAMPLE.UserDetails",
  "type" : "DELIMITEDFILE",
  "delimiter" : "Comma",
  "headerRow" : 1,
  "startRow" : 1,
  "columns" : {
    "Column" : [ {
      "name" : "USERNAME",
      "type" : "STRING",
      "index" : 0,
      "system" : false
    }, {
      "name" : "COUNTRY",
      "type" : "STRING",
      "index" : 1,
      "system" : false
    }, {
      "name" : "COSTCENTER",
      "type" : "STRING",
      "index" : 2,
      "system" : false
    }, {
      "name" : "CURRENCY",
      "type" : "STRING",
      "index" : 3,
      "system" : false
    }, {
      "name" : "MANAGERNAME",
      "type" : "STRING",
      "index" : 4,
      "system" : false
    }, {
      "name" : "COMPANYNAME",
      "type" : "STRING",
      "index" : 5,
      "system" : false
    }, {
      "name" : "BUSINESSUNIT",
      "type" : "STRING",
      "index" : 6,
      "system" : false
    }, {
      "name" : "OFFICE",
      "type" : "STRING",
      "index" : 7,
      "system" : false
    } ]
  },
  "queryParameters" : [ ],
  "links" : [ ]
}

Back to Top