8 Replies Latest reply on Apr 3, 2019 5:00 AM by Avinash Bhuma

    Identifying Level 0 in metadata export

    user608296

      Good morning All.

      we are on PBCS environment.

      When we export metadata we get all columns expect specifying which one is level 0

      is there anyway to identify the level 0 members in the metadata export.

      Below are the columns from my export.

      Entity

      Parent

      Alias: Default

      Valid For Consolidations

      Data Storage

      Two Pass Calculation

      Description

      Formula

      Formula Description

      UDA

      Data Type

      Hierarchy Type

      Enable for Dynamic Children

      Number of Possible Dynamic Children

      Access Granted to Member Creator

      Allow Upper Level Entity Input

      Process Management Enabled

      UUID

      Data Id

      Old Name

      Old Unique Name

      Base Currency

      Plan Type (CTXSWFP)

      Aggregation (CTXSWFP)

      Data Storage (CTXSWFP)

      Formula (CTXSWFP)

      Formula Description (CTXSWFP)

      Plan Type (WFP_Rpt)

      Aggregation (WFP_Rpt)

      Data Storage (WFP_Rpt)

      Formula (WFP_Rpt)

      Solve Order (WFP_Rpt)

      Formula Description (WFP_Rpt)

      ALLOC

      INDIR

      Operation

        • 1. Re: Identifying Level 0 in metadata export
          JohnGoodwin

          A level 0 member will not appear in the parent column.

           

          Cheers

           

          John

          • 2. Re: Identifying Level 0 in metadata export
            user608296

            Thank you very much John for your response.

            I think I didn't ask my question properly.

            previously when we had Razza outline extractor, we used to get a column which specifies the level number.

            is there any we can do same for PBCS environment.

            • 3. Re: Identifying Level 0 in metadata export
              JohnGoodwin

              The outline extractor used to connect to Essbase which is not possible in PBCS. There are possibilities to return member information using the REST API.

              1 person found this helpful
              • 4. Re: Identifying Level 0 in metadata export
                user608296

                Thank you very much John.

                can you guide me to the resources, how I can develop REST API which can give level information.

                • 5. Re: Identifying Level 0 in metadata export
                  Pete N

                  John - did you find a way to get the level information from planning?

                   

                  I had a look, but it didn't seem to be returned in the getmember functions.

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

                   

                  For the OP - it is possible to query 'get member' to work out which ones are level zero (they have a Child flag - so you're just looking for ones without children!). It does appear difficult to get 'level 1' members (which was what I was trying) without running some recursion (if the member you're on only has children, themselves with no children).

                   

                  Alternatively, if this is a once off thing - just drag it into excel and check for parents.

                   

                  =IF(COUNTIF(B:B,A2)>0,"Parent","Lev0")

                   

                  Where B:B is the Parent column and A2 is the Member name will give you a new column of parents and Lev0 flags.

                   

                  p

                  1 person found this helpful
                  • 6. Re: Identifying Level 0 in metadata export
                    JohnGoodwin

                    Hi Pete, yes it can be done through DM/FDMEE REST API, it is not documented but I found a way, it also returns child count, some examples in More to life...: FDMEE Hybrid and the REST Part 2

                    1 person found this helpful
                    • 7. Re: Identifying Level 0 in metadata export
                      Pete N

                      Hahahah - nice find John! (I'd read that post as well, just hadn't put 2 and 2 together!)

                       

                      To the OP - here's some hacked together Powershell that will generate a list of level 0 members.

                       

                      $Username = {User} #replace with username

                      $Password = {Password} #replace with password

                      $URL = {URL} <# in format "https://planning-test-<domain>.pbcs.<env>.oraclecloud.com" #>

                      $Domain = $Domain #Replace with domain

                      $AppName = "Demo" #replace with the app name

                      $Dim = "Entity" #replace with the dimension you want

                      $Filename = "F:\PBCS_REST\Entity_Dim_Lev0.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}

                       

                      Gives you a nice little list.

                       

                      entity_List.PNG

                      • 8. Re: Identifying Level 0 in metadata export
                        Avinash Bhuma

                        Hi,

                         

                        Download Planning admin extension from PBCS  downloads and install it. through this extension you can access your dimensions through smartview. once you connect your application through smartview you can see dimensions as below.

                        select the dimension and right click on dimension name and select edit

                        In Ad hoc ribbon page you have an option called Zoom in .

                        select Zoom in to bottom level which will give you all level 0 members with parents.

                         

                        Thanks.