ExoInsight provides REST calls for the following functionality:
- Pushing Essbase Data and Hierarchies to a Relational Database
- Pushing Essbase Data and Hierarchies to a Delimited File
- Creating a Tableau Hyper File from Oracle Essbase
Pushing Essbase Data and Hierarchies to a Relational Database
ExoInsight provides the ability to integrate your Essbase cubes with a relational database. A simple script can provide a complete ETL process from your source cubes to your destination database, opening up your Essbase data and hierarchies to any imaginable use case.
When using the REST call to push Essbase data and hierarchies to a relational database, one of three methods can be used to pull data:
POV String: use pov as the parameter name
MDX: use mdx as the parameter name.
Report Script: use reportScript as the parameter name. Note: You can reference either the name of an existing Essbase report script or simply enter Essbase Report script syntax as the parameter value. When using an existing Essbase report script or Essbase report script syntax, ALL dimensions must be referenced in the script. If all dimensions are not referenced in the script, the output will not be complete. Also, there must be at least one <ROW dimension and one <COLUMN dimension.
POST: http(s)://ServerName:PortNumber/ExoInsight/database
BODY:
{
"license":"Enterprise",
"env":"ExoInsightEnvironmentName",
"user":"username",
"password":"password",
"application":"applicationName",
"cube":"cubeName",
"pov":"valid POV string",
"loops":"valid loops statement, or blank",
"table":"tableName",
"SqlEnv":"ExoInsightRelationalEnvironmentName",
"UDA_MF": true or false,
"preLoadSQL": "valid SQL statement, or blank",
"postLoadSQL": "valid SQL statement, or blank",
"SqlPassword":"password",
"TruncateDataTable":true or false,
"TruncateDimTables":true or false,
"dimensions":"all, none, or pipe-delimited list",
"GEN_Aliases":true or false,
"dimensionNameColumnPrefix":true,
"dimensionNamePrepend":"prefix for dimension tables"
}
|
Parameter |
Description |
|
license |
Always keep as “Enterprise” |
|
env |
The Essbase environment variable set up in ExoInsight. Case-sensitive. |
|
user |
The Essbase user name |
|
password |
The Essbase password |
|
application |
The Essbase application |
|
cube |
The Essbase database |
|
pov/mdx/reportScript |
Use “pov” for POV String syntax, “mdx” for MDX syntax, or “reportScript” for report script syntax. See above for an explanation of each type. |
|
loops |
A valid POV String referencing the members that the base query should be looped on. |
|
preLoadSQL |
(ExoInsight version 8.3.1 and higher) Any valid SQL Statement. This statement will be run prior to processing the query or dimension data into the destination database. |
|
postLoadSQL |
(ExoInsight version 8.3.1 and higher) Any valid SQL Statement. This statement will be run after the processing of the query and dimension data into the destination database. |
|
table |
The table name that the data set will land in. Use “none” to skip the data pull and only pull dimensions. |
|
TruncateDataTable |
This flag determines whether or not the table identified in the “table” parameter is truncated prior to loading. Valid values are “true” and “false”. |
|
TruncateDimTables |
This flag determines whether or not the dimension tables identified in the “dimensions” parameter is truncated prior to loading. Valid values are “true” and “false”. |
|
UDA_MF |
(ExoInsight version 8.3.1 and higher) Determines whether expanded metadata is included in the dimension exports. Expanded metadata includes member formulas, UDAs, attribute values, etc. Valid values are “true” and “false”. |
|
SqlEnv |
The name of the destination SQL environment that you set up in ExoInsight. Case-sensitive. |
|
SqlPassword |
The password for the username and environment referenced in the SqlEnv parameter. Required. |
|
dimensions |
The dimensions to create tables for. “all” will pull all dimensions, “none” will pull none. A pipe delimited list can also be used to pull specific dimensions: Measures|Product|Year |
|
GEN_Aliases |
(ExoInsight version 8.4.3 and higher) Adding this parameter will add alias columns for each generation column in the dimension tables. There will be one alias column per dimension created for each alias table defined in the Essbase cube. Valid values are "true" and "false". Default if not included is "false". |
|
dimensionNamePrepend |
(ExoInsight version 8.4.3 and higher) This parameter allows the user to specify the prefix that will be added to the dimension names instead of the default "ApplicationName_CubeName_" prefix. Make sure the text specified is a legal value for table names in the destination database. Default if not included is "ApplicationName_CubeName_". |
|
dimensionNameColumnPrefix |
(ExoInsight version 8.5.0 and higher) This parameter allows the user to specify whether or not to include the dimension name as part of the column name in the dimension table. Valid values are "true" and "false". Default if not included is "true". |
Example in PowerShell syntax using pov method:
Param(
[string]$ExoInsightUrl = "http://localhost:9090/ExoInsight/database"
)
$JSON = @'
{
"license":"Enterprise",
"env":"prod",
"user":"admin",
"password":"password",
"application":"Sample",
"cube":"Basic",
"pov":"ExoPowerPOV-;NONEMPTY-;level0([Year])-;descendants([Profit])-;[East]|[West]-;[Actual]-;",
"loops":"ExoPowerPov-;level0([Product])",
"table":"EB_Actuals",
"SqlEnv":"SQLServer_CBSW",
"SqlPassword":"casabase",
"preLoadSQL": "",
"postLoadSQL": "",
"UDA_MF": true,
"GEN_Aliases": true,
"TruncateDataTable":true,
"TruncateDimTables":true,
"dimensions":"all",
"dimensionNameColumnPrefix": true,
"dimensionNamePrepend": ""
}
'@
try {
Invoke-RestMethod -Uri $ExoInsightUrl -Method Post -Body $JSON -ContentType "application/json"
}
catch
{
$exception = $_.Exception
Write-Host "StatusCode:" $exception.Response.StatusCode.value__
Write-Host "StatusDescription:" $exception.Response.StatusDescription
}
The script above will pull data as defined in the pov parameter while looping over the members defined in the loops parameter, create the table as defined by the table parameter in the destination database identified by the SqlEnv parameter, populate the table, then create one table per dimension for each dimension defined in the dimensions parameter. Dimension tables are named in the following format:
ApplicationName_CubeName_DimensionName
(Note: Starting in ExoInsight version 8.4.3 the user may specify their own dimension table prefix)
Since the TruncateDataTable parameter is set to true, the table defined by the table parameter will be cleared and reloaded. If it had been set to false, the data would have be appended to the table defined by the table parameter.
Pushing Essbase Data and Hierarchies to a Delimited File
ExoInsight enables you to create delimited files that contains Essbase data and hierarchies in a relational format.
When using the REST call to push Essbase data and hierarchies to a flat file, one of three methods can be used to pull data:
POV String: use pov as the parameter name
MDX: use mdx as the parameter name.
Report Script: use reportScript as the parameter name. Note: You can reference either the name of an existing Essbase report script or simply enter Essbase Report script syntax as the parameter value. When using an existing Essbase report script or Essbase report script syntax, ALL dimensions must be referenced in the script. If all dimensions are not referenced in the script, the output will not be complete. Also, there must be at least one <ROW dimension and one <COLUMN dimension.
POST: http(s)://ServerName:PortNumber/ExoInsight/file
Body:
{
"license": "Enterprise",
"env": "ExoInsightEnvironmentName",
"application": "applicationName",
"cube": "cubeName",
"user": "username",
"password": "password",
"pov": "name of a saved POV or a valid POV string",
"loops": "valid loops statement, or blank",
"dimensions": "Product or Product|Measures|Market or \"all\"",
"GEN_Aliases": true or false,
"UDA_MF": true or false,
"dimensionNameColumnPrefix": true or false,
"leaveFlatFileOnServer": "no or yes",
"flatFileName": "fileName",
"FieldDelimiter": ",",
"plainText": true or false,
"quotes": true or false
}
|
Parameter |
Description |
|
license |
Always keep as “Enterprise” |
|
env |
The Essbase environment variable set up in ExoInsight. Case-sensitive. |
|
user |
The Essbase user name |
|
password |
The Essbase password |
|
application |
The Essbase application |
|
cube |
The Essbase database |
|
pov/mdx/reportScript |
Use “pov” for POV String syntax, “mdx” for MDX syntax, or “reportScript” for report script syntax. See above for an explanation of each type. |
|
loops |
A valid POV String referencing the members that the base query should be looped on. |
|
dimensions |
The dimensions to create tables for. “all” will pull all dimensions, “none” will pull none. A pipe delimited list can also be used to pull specific dimensions: Measures|Product|Year |
|
UDA_MF |
(ExoInsight version 8.3.1 and higher) Determines whether expanded metadata is included in the dimension exports. Expanded metadata includes member formulas, UDAs, attribute values, etc. Valid values are “true” and “false”. |
|
GEN_Aliases |
(ExoInsight version 8.4.3 and higher) Adding this parameter will add alias columns for each generation column in the dimension tables. There will be one alias column per dimension created for each alias table defined in the Essbase cube. Valid values are "true" and "false". Default if not included is "false". |
|
dimensionNameColumnPrefix |
(ExoInsight version 8.5.0 and higher) This parameter allows the user to specify whether or not to include the dimension name as part of the column name in the dimension table. Valid values are "true" and "false". Default if not included is "true". |
| leaveFlatFileOnServer |
The files will initially be created and consolidated in the <installation directory>/casabase/data folder on the ExoInsight server. Once the process is complete, the files will be zipped and delivered to the destination. If you would like to leave the files in the data directory, set this value to true. Otherwise the files will be deleted upon the process completing. |
| flatFileName |
The name of the file that will contain the delimited data. |
|
FieldDelimiter |
The delimiter of the data in both the data file and dimension files. Common values are comma (,), pipe (|), and tab (\t). |
|
plainText |
If writing to a file, keep as "false". If piping to another source, such as a command prompt or streaming to another application, change to "true". If "true", do not specify a flatFileName or $filename in the REST call. |
|
quotes |
Determines whether to put quotes around exported data. Valid values are true and false. |
Example in PowerShell syntax using pov method:
Param(
[string]$ExoInsightUrl = "http://localhost:9090/ExoInsight/file"
,[string]$filename = "ExoInsight_Data_and_Hierarchy_Export.zip"
)
$JSON = @'
{
"license": "Enterprise",
"env": "ProdEB",
"application": "Sample",
"cube": "Basic",
"user": "admin",
"password": "password",
"dimensions": "Product|Measures|Market",
"GEN_Aliases": false,
"UDA_MF": false,
"dimensionNameColumnPrefix": true,
"pov": "ExoPowerPOV-;NONEMPTY-;descendants([Year])-;descendants([Measures])-;",
"loops": "",
"leaveFlatFileOnServer": "no",
"flatFileName": "fileName",
"FieldDelimiter": ",",
"plainText": false,
"quotes": true
}
'@
$response = ''
try {
$response = Invoke-RestMethod -Uri $ExoInsightUrl -Method Post -Body $JSON -ContentType "application/json" -UseDefaultCredentials -OutFile $filename
Write-Host $response
} catch {
$exception = $_.Exception
Write-Host "StatusCode:" $exception.Response.StatusCode.value__
Write-Host "StatusDescription:" $exception.Response.StatusDescription
Write-Host "ErrorMessage:" $_.ErrorDetails.Message
}
Creating a Tableau Hyper File from Oracle Essbase
ExoInsight enables you to create a Tableau Hyper file directly off of an Essbase cube. The hyper file will contain data as specified by the pov string, mdx statement, or Essbase report script specified, along with any dimensions specified.
POV String: use pov as the parameter (highlighted in yellow below).
MDX: use mdx as the parameter.
Report Script: use reportScript as the parameter. Note: You can reference either the name of an existing Essbase report script or simply enter Essbase Report script syntax as the parameter value. When using an existing Essbase report script or Essbase report script syntax, ALL dimensions must be referenced in the script. If not all dimensions are referenced in the script, the output will not be complete. Also, there must be at least one <ROW dimension and one <COLUMN dimension.
POST: http(s)://ServerName:PortNumber/ExoInsight/tableau
Example in PowerShell syntax:
Param(
[string]$ExoInsightUrl = "http://localhost:9090/ExoInsight/tableau",
[string]$filename = "Tableau.zip",
[string]$destination = ""
)
$JSON = @'
{
"license":"Enterprise",
"env":"ProdEB",
"user":"admin",
"password":"password",
"application":"Sample",
"cube":"Basic",
"pov":"NONEMPTY;Children(Year);Children(Profit);Children(Product);Children(Market);Actual",
"dimensions":"all"
}
'@
try {
Invoke-RestMethod -Uri $ExoInsightUrl -Method Post -Body $JSON -ContentType "application/zip" -OutFile $filename
#Uncomment the following line to extract hyper file to $destination directory
#Expand-Archive -Path $filename -DestinationPath $destination
} catch {
$exception = $_.Exception
Write-Host "StatusCode:" $exception.Response.StatusCode.value__
Write-Host "StatusDescription:" $exception.Response.StatusDescription
}