Automate the Export of a Large Number of Cells from an Aggregate Storage Cube

Use the PowerShell script in this section to export a large number of cells from an Aggregate Storage (ASO) cube.

Because the limits imposed by 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:

  • This script executes an existing job of type export data. For detailed instructions on creating jobs in Planning, see "Managing Jobs" in Administering Planning.

  • If you are using the PDF version of this document: To avoid line breaks and footer information that will render this script unusable, copy the script from the HTML version of this topic.

  1. Copy the following script and save it to your file system, for example, as ASOCellExport.ps1.
    $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"
    
  2. Modify the PowerShell script, for example,ASOCellExport.ps1 to set the values for the parameters in the following table.

    Table 3-7 Variable Values to Include in the PowerShell Script

    Variable Description
    $user Domain and user name of a Service Administrator in DOMAIN.USER format.

    Example: $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.

    Example: $pass = 'Example'

    $serverURL The URL of the Oracle Enterprise Performance Management Cloud environment that hosts the Planning application.

    Example: $serverURL = 'https://example .oraclecloud.com'

    $applicationName Name of a Planning application.

    Example: $applicationName = 'Vision'

    $cubeName Name of a Cube in the application.

    Example: $cubeName = 'VisASO'

    $splitDimension Name of a dimension the members of which are used to split the export into groups.

    Example: $splitDimension = 'Account'

    $topLevelMemberForExport Name of a member of the dimension sub-hierarchy under which a list of Level 0 members is created.

    Example: $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.

    Example: $exportJobName = 'ASO Cell Export'

    $exportFilePrefix A file name prefix to uniquely identify the files generated by the export job.

    Example: $exportFilePrefix = 'cashflow'

    $columnMembers The member columns to include in the export.

    Example: $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)

    Example: $povMembers = 'YTD'

    $numberOfExportFiles Number of jobs to execute for this export operation. If export still fails due to query limit limitations, increase this number.

    Example: $numberOfExportFiles = 3

  3. Using Windows Scheduler, schedule the PowerShell script, for example,ASOCellExport.ps1. See Automating Script Execution for detailed steps.