Forum Stats

  • 3,825,706 Users
  • 2,260,552 Discussions
  • 7,896,646 Comments

Discussions

Identifying Level 0 in metadata export

user608296
user608296 Member Posts: 139 Blue Ribbon
edited Apr 3, 2019 1:00AM in Planning and Budgeting

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

Avinash Bhuma

Answers

  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Mar 24, 2019 12:55PM

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

    Cheers

    John

  • user608296
    user608296 Member Posts: 139 Blue Ribbon
    edited Mar 24, 2019 5:31PM

    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.

  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Mar 25, 2019 4:13AM

    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.

    user608296
  • user608296
    user608296 Member Posts: 139 Blue Ribbon
    edited Mar 25, 2019 9:53AM

    Thank you very much John.

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

  • Pete N
    Pete N Member Posts: 77 Blue Ribbon
    edited Mar 25, 2019 9:02PM

    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

    user608296Avinash Bhuma
  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Mar 26, 2019 3:16AM

    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

    user608296
  • Pete N
    Pete N Member Posts: 77 Blue Ribbon
    edited Apr 1, 2019 5:17PM

    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

  • Avinash Bhuma
    Avinash Bhuma Member Posts: 150
    edited Apr 3, 2019 1:00AM

    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.

    pastedImage_0.png

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

    pastedImage_1.png

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

    pastedImage_2.png

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

    Thanks.