Automate the Export of a Large Number of Cells from an Aggregate Storage Cube
Use the PowerShell or Bash script in this section to export a large number of cells from an Aggregate Storage (ASO) cube.
Because the limits imposed by Oracle Essbase QUERYRESULTLIMIT
, it is impossible to export a large quantity of data from the user interface. The PowerShell script available in this section splits the export operation into a specified number of jobs, run each job, downloads the exported data, and concatenates the export files into one export file ensuring that only one header is present.
Note:
These scripts execute an existing job of type export data. For detailed instructions on creating jobs, see "Managing Jobs" in Administering Planning.PowerShell Script
$user = '<USERNAME>'
$pass = '<PASSWORD>'
$serverURL = '<URL>'
$applicationName = '<APPLICATIONNAME>'
$cubeName = '<CUBENAME>'
$splitDimension = '<DIMENSION_TO_SPLIT_THE_EXPORT>'
$topLevelMemberForExport = '<TOP_MEMBER_FOR_EXPORT>'
$exportJobName = '<EXPORT_JOB_NAME>'
$exportFilePrefix = '<PREFIX_FOR_EXPORT_FILE>'
$columnMembers = '<MEMBERS_ON_COLUMNS>'
$povMembers = '<POV_MEMBERS>'
$numberOfExportFiles = <NUMBER_OF_FILES_TO_SPLIT_THE_EXPORT>
$memberArray = @()
$exportFileArray = @()
function getLevel0 ($parent) {
$parent.children.ForEach({
if ( $_.children.count -eq 0 ) {
$script:memberArray += $_.name
}
getLevel0($_)
})
}
function findMember ($tree, $memberName) {
$subtree = ""
if ($tree.name -eq $memberName){
return $tree
} else {
$tree.children.ForEach({
#Write-Host $_.name
if ($subtree -eq ""){ $subtree = findMember $_ $memberName}
})
return $subtree
}
}
#putting together base64 encoded authentication header based un user and password
$encodedCredentials = [Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes($($user) + ":" + $($pass)))
$headers = @{ Authorization = "Basic $encodedCredentials" }
#test login
$testRequest = $serverURL + '/HyperionPlanning/rest/v3/applications'
try {
$response = Invoke-RestMethod -Uri $testRequest -Method Get -Headers $headers -UseBasicParsing
}
catch {
Write-Host $_
return
}
#retrieve dimension hierarchy from application
Write-Host "Retrieving member list for split dimension " $splitDimension
$request = $serverURL + '/HyperionPlanning/rest/v3/internal/applications/' + $applicationName + '/plantypes/' + $cubeName + '/dimensions/' + $splitDimension
try {
$response = Invoke-RestMethod -Uri $request -Method Get -Headers $headers -UseBasicParsing
}
catch {
Write-Host $_
return
}
Write-Host $splitDimension " member list retrieved"
#search for the top of the export hierarchy
Write-Host "Searching for member " $topLevelMemberForExport " in hierarchy"
$member = findMember $response $topLevelMemberForExport
if ( $member.name -ne $topLevelMemberForExport ) {
Write-Host $topLevelMemberForExport " not found in hierarchy, exiting ..."
return 128
}
Write-Host "Found member " $topLevelMemberForExport " in hierarchy"
#retrieve level 0 memebers in export hierarchy
Write-Host "Retrieving Level 0 members for hierarchy"
getLevel0($member)
if ( $memberArray.Length -eq 0 ) {
Write-Host "no level 0 members found in hierarchy, exiting ..."
return 128
}
Write-Host $memberArray.Length " Level 0 members for export hierarchy retrieved"
$request = $serverURL + '/HyperionPlanning/rest/v3/applications/' + $applicationName + '/jobs'
#splitting member list into the number of export files
$numberOfEntitiesPerFile = [math]::truncate($memberArray.Length / $numberOfExportFiles)
for ($i = 1; $i -le $numberOfExportFiles; $i++) {
$memberList = ""
$firstMember = ($i - 1) * $numberOfEntitiesPerFile
if ($i -lt $numberOfExportFiles) {
$lastMember = $i * $numberOfEntitiesPerFile
} else {
$lastMember = $i * $numberOfEntitiesPerFile + $memberArray.Length % $numberOfExportFiles
}
for ($j = $firstMember; $j -lt $lastMember; $j++) {
$memberList += $memberArray[$j]
if ($j -lt $lastMember - 1) {$memberList += ","} #avoid adding a comma (,) after the last member of each set
}
$jobDetails='
{
"jobType":"EXPORT_DATA","jobName":"' + $exportJobName + '",
"parameters":{
"exportFileName":"Export-' + $i + '.zip",
"rowMembers":"' + $memberList + '",
"columnMembers":"' + $columnMembers + '",
"povMembers":"' + $povMembers + '"
}
}'
#start export job
try{
$response = Invoke-RestMethod -Uri $request -Method Post -Headers $headers -Body $jobDetails -ContentType "application/json"}
catch {
Write-Host $_
return
}
Write-Host "Started export job " $i " out of " $numberOfExportFiles
#checking job status, continue once jos is completed
$statusRequest = $serverURL + '/HyperionPlanning/rest/v3/applications/' + $applicationName + '/jobs/' + $response.jobId
$statusResponse = Invoke-RestMethod -Uri $statusRequest -Method Get -Headers $headers -UseBasicParsing
while ( $statusResponse.descriptiveStatus -eq "Processing" ) {
Write-Host $statusResponse.descriptiveStatus
Start-Sleep -s 10
$statusResponse = Invoke-RestMethod -Uri $statusRequest -Method Get -Headers $headers -UseBasicParsing
}
Write-Host $statusResponse.descriptiveStatus
Write-Host "Downloading export file ..."
$downloadRequest = $serverURL + '/interop/rest/11.1.2.3.600/applicationsnapshots/Export-' + $i + '.zip/contents'
$statusResponse = Invoke-RestMethod -Uri $downloadRequest -Method Get -Headers $headers -OutFile "$exportFilePrefix-$i.zip"
Write-Host "Expanding archive ..."
Expand-Archive -Force -LiteralPath "$exportFilePrefix-$i.zip" -DestinationPath "$exportFilePrefix-$i"
Remove-Item "$exportFilePrefix-$i.zip"
Get-ChildItem -Path "$exportFilePrefix-$i" -File -Name | ForEach-Object { $exportFileArray += "$exportFilePrefix-$i\" + $_ }
}
Write-Host "creating outputfile ..."
#write header to outputfile
Get-Content $exportFileArray[0] | Select-Object -First 1 | Out-File "$exportFilePrefix.csv"
#write content to outputfile skipping header
ForEach ($exportFile in $exportFileArray) {
Get-Content $exportFile | Select-Object -Skip 1 | Out-File -Append "$exportFilePrefix.csv"
}
Compress-Archive -LiteralPath "$exportFilePrefix.csv" -DestinationPath "$exportFilePrefix.zip"
Write-Host "cleaning up ..."
Remove-Item "$exportFilePrefix-*" -Recurse
Remove-Item "$exportFilePrefix.csv"
Bash Script
#!/bin/bash
user='<USERNAME>'
pass='<PASSWORD>'
serverURL='<URL>'
applicationName='<APPLICATIONNAME>'
cubeName='<CUBENAME>'
splitDimension='<DIMENSION_TO_SPLIT_THE_EXPORT>'
topLevelMemberForExport='<TOP_MEMBER_FOR_EXPORT>'
exportJobName='<EXPORT_JOB_NAME>'
exportFilePrefix='<PREFIX_FOR_EXPORT_FILE>'
columnMembers='<MEMBERS_ON_COLUMNS>'
povMembers='<POV_MEMBERS>'
numberOfExportFiles=<NUMBER_OF_FILES_TO_SPLIT_THE_EXPORT>
getRowMembers() {
local memberList="$1"
local firstMember=$2
local lastMember=$3
local nameCount=0
local rowMember=""
local rowMembers=""
while IFS= read -r line
do
if [[ "${line}" == *"name"* ]]
then
if [[ ${nameCount} -ge ${firstMember} ]] && [[ ${nameCount} -lt ${lastMember} ]]
then
rowMember=$(echo "${line}" | cut -d':' -f2- | sed s'/[",]//g')
rowMembers="${rowMembers}${rowMember},"
fi
((nameCount+=1))
fi
done <<< "${memberList}"
rowMembers=$(echo "${rowMembers}" | rev | cut -d',' -f2- | rev)
echo "${rowMembers}"
}
getLevel0()
{
local memberList="$1"
local names=$(echo "${memberList}" | jq 'recurse (try .children[]) | .name' | sed -e 's/"//g')
local elements=""
formerIFS=$IFS
IFS=$'\n'
namesarr=($names)
IFS=$formerIFS
for i in ${!namesarr[@]}
do
testelement=$(echo "${memberList}" | jq --arg currentName "${namesarr[i]}" 'recurse (try .children[]) | select(.name==$currentName)')
if [[ "${testelement}" != *"children"* ]]
then
elements="${elements}${testelement}"
fi
done
echo "${elements}"
}
#test login
header="Content-Type: application/x-www-form-urlencoded"
applicationsRequest="${serverURL}/HyperionPlanning/rest/v3/applications"
response=$(curl -X "GET" -s -w "%{http_code}" -u "${user}:${pass}" -H "${header}" "${applicationsRequest}")
http_response_code=$(echo "${response}" | rev | cut -d'}' -f1 | rev)
if [ ${http_response_code} -ne 200 ]
then
echo "${response}"
exit
fi
#retrieve dimension hierarchy from application
echo "Retrieving member list for split dimension ${splitDimension}"
splitDimensionRequest="${serverURL}/HyperionPlanning/rest/v3/internal/applications/${applicationName}/plantypes/${cubeName}/dimensions/${splitDimension}"
response=$(curl -X GET -s -w "%{http_code}" -u "${user}:${pass}" -o "response-memberlist.txt" -D "respHeader-memberlist.txt" -H "${header}" "${splitDimensionRequest}")
http_response_code=$(echo "${response}" | rev | cut -d'}' -f1 | rev)
if [ ${http_response_code} -ne 200 ]
then
echo "${response}"
exit
fi
echo "${splitDimension} member list retrieved"
#search for the top of the export hierarchy
echo "Searching for member ${topLevelMemberForExport} in hierarchy"
memberList=$(cat response-memberlist.txt | jq --arg topLevelMember "${topLevelMemberForExport}" 'recurse(try .children[]) | select (.name == $topLevelMember)')
if [[ "${memberList}" == "" ]]
then
echo "${topLevelMemberForExport} not found in hierarchy, exiting ..."
exit 128
fi
echo "Found member ${topLevelMemberForExport} in hierarchy"
#retrieve level 0 members in export hierarchy
echo "Retrieving Level 0 members for hierarchy"
totalCount=$(echo "${memberList}" | grep "name" | wc -l)
grepChildrenCount=$(echo "${memberList}" | grep "children" | wc -l)
levelZeroCount=$((totalCount-grepChildrenCount))
if [[ "${levelZeroCount}" -eq 0 ]]
then
echo "no level 0 members found in hierarchy, exiting ..."
exit 128
fi
echo "${levelZeroCount} Level 0 members for export hierarchy retrieved"
#splitting member list into the number of export files
numberOfEntitiesPerFile=$((levelZeroCount/numberOfExportFiles))
jobsRequest="${serverURL}/HyperionPlanning/rest/v3/applications/${applicationName}/jobs"
header="Content-Type: application/json"
for ((i = 1 ; i <= ${numberOfExportFiles}; i++))
do
firstMember=$((($i-1)*numberOfEntitiesPerFile))
if [[ ${i} -lt ${numberOfExportFiles} ]]
then
lastMember=$((i*numberOfEntitiesPerFile))
else
lastMember=$((i*numberOfEntitiesPerFile+levelZeroCount%numberOfExportFiles))
fi
elements=$(getLevel0 "${memberList}")
rowMembers=$(getRowMembers "${elements}" ${firstMember} ${lastMember})
response=$(curl -X POST -s -w "%{http_code}" -u "${user}:${pass}" -o "response-job.txt" -D "respHeader-job.txt" -H "${header}" "${jobsRequest}" -d '{"jobType":"EXPORT_DATA","jobName":"'"${exportJobName}"'","parameters":{"exportFileName":"Export-'"${i}"'.zip","rowMembers":"'"${rowMembers}"'","columnMembers":"'"${columnMembers}"'","povMembers":"'"${povMembers}"'"}}')
echo "Started export job " $i " out of " $numberOfExportFiles
jobId=$(cat response-job.txt | grep -o '"jobId":[^, }]*' | cut -d':' -f2)
descriptiveStatus=$(cat response-job.txt | grep -o '"descriptiveStatus":[^, }]*' | cut -d':' -f2 | sed -e 's/"//g')
jobIdRequest="${serverURL}/HyperionPlanning/rest/v3/applications/${applicationName}/jobs/${jobId}"
response=$(curl -X GET -s -w "%{http_code}" -u "${user}:${pass}" -o "response-jobstatus.txt" -D "respHeader-jobstatus.txt" -H "${header}" "${jobIdRequest}")
jobId=$(cat response-jobstatus.txt | grep -o '"jobId":[^, }]*' | cut -d':' -f2)
descriptiveStatus=$(cat response-jobstatus.txt | grep -o '"descriptiveStatus":[^, }]*' | cut -d':' -f2 | sed -e 's/"//g')
while [[ "${descriptiveStatus}" == "Processing" ]]
do
echo "${descriptiveStatus}"
sleep 10
response=$(curl -X GET -s -w "%{http_code}" -u "${user}:${pass}" -o "response-jobstatus.txt" -D "respHeader-jobstatus.txt" -H "${header}" "${jobIdRequest}")
descriptiveStatus=$(cat response-jobstatus.txt | grep -o '"descriptiveStatus":[^, }]*' | cut -d':' -f2 | sed -e 's/"//g')
done
echo "${descriptiveStatus}"
echo "Downloading export file ..."
contentsRequest="${serverURL}/interop/rest/11.1.2.3.600/applicationsnapshots/Export-${i}.zip/contents"
curl -X GET -s -w "%{http_code}" -u "${user}:${pass}" -D "respHeader-download.txt" "${contentsRequest}" > "${exportFilePrefix}-${i}.zip"
echo "Expanding archive ..."
unzip "${exportFilePrefix}-${i}.zip" -d "${exportFilePrefix}-${i}"
rm "${exportFilePrefix}-${i}.zip"
echo "Writing to outputfile ..."
if [[ -d "${exportFilePrefix}-${i}" ]]
then
find "${exportFilePrefix}-${i}" -name \*.csv | xargs cat | tail -n +2 >> "${exportFilePrefix}.csv"
fi
done
zip "${exportFilePrefix}.zip" "${exportFilePrefix}.csv"
echo "cleaning up ..."
find . -name "${exportFilePrefix}-*" | xargs rm -r
rm "${exportFilePrefix}.csv"
To export a large number of cells from an Aggregate Storage (ASO) cube:
- Copy the PowerShell or Bash script and save it to your file system, for example, as
ASOCellExport.ps1
orASOCellExport.sh
. - Modify the script file and set parameter values. See the following table for details.
Table 3-16 Variable Values to Include in the PowerShell and Bash Scripts
Variable Description user
Domain and user name of a Service Administrator in DOMAIN.USER
format.Examples:
Windows:
$user = 'exampleDomain.jDoe'
Linux/UNIX:
user = 'exampleDomain.jDoe'
pass
Password of the Service Administrator or the location of the encrypted password file. See the encrypt
command for information on creating an encrypted password file.Examples:
Windows:
$pass = 'Example'
Linux/UNIX:
pass = 'Example'
serverURL
The URL of the Oracle Enterprise Performance Management Cloud environment. Examples:
Windows:
$serverURL = 'https://example .oraclecloud.com'
Linux/UNIX:
serverURL = 'https://example .oraclecloud.com'
applicationName
Name of a Planning, Financial Consolidation and Close, Tax Reporting or Enterprise Profitability and Cost Management application. Examples:
Windows:
$applicationName = 'Vision'
Linux/UNIX:
applicationName = 'Vision'
cubeName
Name of a Cube in the application. Examples:
Windows:
$cubeName = 'VisASO'
Linux/UNIX:
cubeName = 'VisASO'
splitDimension
Name of a dimension the members of which are used to split the export into groups. Examples:
Windows:
$splitDimension = 'Account'
Linux/UNIX:
splitDimension = 'Account'
topLevelMemberForExport
Name of a member of the dimension sub-hierarchy under which a list of Level 0 members is created. Examples:
Windows:
$topLevelMemberForExport = 'Total Cash Flow'
Linux/UNIX:
topLevelMemberForExport = 'Total Cash Flow'
exportJobName
Name of an existing job of type Export Data. The settings specified in this job will be overwritten by the parameters that you set in the script. Examples:
Windows:
$exportJobName = 'ASO Cell Export'
Linux/UNIX:
exportJobName = 'ASO Cell Export'
exportFilePrefix
A file name prefix to uniquely identify the files generated by the export job. Examples:
Windows:
$exportFilePrefix = 'cashflow'
Linux/UNIX:
exportFilePrefix = 'cashflow'
columnMembers
The member columns to include in the export. Examples:
Windows:
$columnMembers = 'Period'
Linux/UNIX:
columnMembers = 'Period'
povMembers
Point of Views to include in the export. POV Members must include all other dimensions and can include functions as shown below:
ILvl0Descendants(YearTotal), ILvl0Descendants(Year), ILvl0Descendants(Scenario), ILvl0Descendants(Version), ILvl0Descendants(P_TP), ILvl0Descendants(AltYear)
Examples:
Windows:
$povMembers = 'YTD'
Linux/UNIX:
povMembers = 'YTD'
numberOfExportFiles
Number of jobs to execute for this export operation. If export still fails due to query limit limitations, increase this number. Examples:
Windows:
$numberOfExportFiles = 3
Linux/UNIX:
numberOfExportFiles = 3
- Using Windows Scheduler or a cron job, schedule the script to execute at a convenient time. See Automating Script Execution for detailed steps.