Planning

Get Involved. Join the Conversation.

Topic

    Dishant Dalal
    Exporting Level0 Data Using REST (EPBCS)
    Topic posted May 15, 2019 by Dishant DalalGreen Ribbon: 100+ Points, tagged ARCS, EPBCS, FCCS 
    80 Views, 5 Comments
    Title:
    Exporting Level0 Data Using REST (EPBCS)
    Summary:
    Troubleshoot REST API to export Level0 Data Using REST API
    Content:

    Hello-

    Does anyone know what the best method to export Level0 data through EPM Automate or via REST APIs is? We are using the below method, however it is not working for our instance. We have tried it with other domains and it seems to work, but it does not work for our EPBCS domain. 

    Unfortunately, our domain simply gets a blank file produced. 

    Any help on this will be appreciated. 

    PowerShell Script Name: 

    Powershell_ExportLevel0.ps1

    Content: 

    $Username = "" #replace with username

    $Password = "" #replace with password

    $URL = "https://urldefense.proofpoint.com/v2/url?u=https-3A__planning3-2Dtest-2Degdr.pbcs.us6.oraclecloud.com&d=DwIGAg&c=AgWC6Nl7Slwpc9jE7UoQH1_Cvyci3SsTNfdLP4V1RCg&r=4rlVrt4UVmNw1ClH0NSIp5Ko5eHS4_xMtIjhV3mKTUc&m=sHjt7oiGxCrhV6EhufbPRW_sIWt0-r9F8n3Dl8u3SRM&s=5ca06CcAEKNkxh7SRqAxB-ZZpMKNubsUzELLF_B_f-0&e="

    $Domain = "egdr" 

    $AppName = "SCBNF" 

    $Dim = "Account" 

    $Filename = "E:\Southern\Temp\Account.csv" #replace with an output file

     

    # Convert creds to auth headers

    $Enc_Auth = [System.Text.Encoding]::UTF8.GetBytes(("{0}.{1}:{2}" -f $Domain, $Username, $Password))

    $Enc_PW = [System.Convert]::ToBase64String($Enc_Auth)

    $PBCS_REST_URL = "{0}/aif/rest/V1/applications/{1}/dimension/{2}/" -f $URL, $Appname, $Dim

     

    # Trigger the Dim Function

        try{

            $DimDetails = Invoke-RestMethod -Uri $PBCS_REST_URL -Method GET -Headers @{"Authorization"="Basic $($Enc_PW)"} -ContentType "application/json"

            $DimDetails.items | Where-Object {$_.childCount -eq "0"} | select memberName -Unique | ConvertTo-Csv > $Filename -NoTypeInformation

            write-host "Created dimensional list: $Filename"

        }

    Catch {throw $_.Exception.Message}

    Version:
    19.03.59

    Comment

     

    • Peter Nitschke

      G'day Dishant, 

      Erm, a few things:

      1) your URL is incorrect, it's expecting it in this format:

      https://planning-test-<domain>.pbcs.<DataCentre>.oraclecloud.com/

      2) that Rest query won't return data, it's instead going to return the dimensional members for the account dimension, filtered for level 0 (and uniques):

      Cheers

      Pete

       

       

       

      • Dishant Dalal

        Hi Pete-

        Thank you for the suggestion. I changed my URL to https://planning-test-egdr.pbcs.us6.oraclecloud.com/. However, the behavior is exactly the same, I still get a blank file. 

        The reason I expect data within the output file is because we do have level0 Account data within the application. Do you have any other suggestions? 

        Regards,

        Dishant Dalal

        • Peter Nitschke

          Hey Dishant,

          Yes - but unfortuantly that query DOESN'T return data. It returns members. 

          The REST query to get data out can be found here:

          https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/prest/export_dataslices.html

          Cheers

          Pete

          • Dishant Dalal

            Hi Pete,

            Sorry for the confusion, by data, I did not mean actual application data, I mean a member output. In other words, information within the file. My output file comes out as blank with nothing within in. 

            Regards,

            Dishant Dalal

            • Peter Nitschke

              Ahh - okay.

              First things then, I wrote an updated version of that code here - includes the ability to pull from any level (and it's much faster):

              http://essbasedownunder.com/2019/04/extracting-metadata-from-pbcs-using-the-rest-api-updated/

              Secondly, I'm going to guess it's because you haven't refreshed the Data Management tables. You need to make sure that Data Management has setup the target applications for the databases you're attempting to query from (either Planning or Essbase - the code works for either) and then refresh the metadata (from the Target application page in the setup tab). Weirdly that code pulls from the metadata tables NOT from the planning metadata directly. It will need to be refreshed the first time to build the tables. There are then back end processes (overnight?) that resync the metadata occasionally, but hitting refresh members will bring all the new ones in. 

              Let us know how that goes. 

              Cheers

              Pete