Extracting Metadata from PBCS using the REST API

Posted on: April 7, 2019 May 10, 2020
Categories: Guides
Tags: PBCS, Powershell, REST

G’day All,

As is seemingly often the case – most of my REST API work is simply trailing on the coat-tails of greatness, aka: John Goodwin.

Specifically a question came up in the OTN Planning forums Identifying Level 0 in metadata export

Obviously there are a number of manual ways to do it – simple Smartview exports, exporting the dimension and parsing the file, even writing a custom database export out to flat file – but none of them are particularly elegant and\or streamlined and would be very difficult to integrate into a metadata update.

When John mentioned that he had found a way, I was obviously intrigued! Basically in his investigation of the FDMEE on-prem to Cloud integration processes he found an undocumented data management query to return all of the members from within the requested dimension:

GET URL/aif/rest/V1/applications/App/dimension/Dim/

This query is from the data management layer – specifically querying the underlying FDMEE \ Data management tables that are populated by the target application metadata queries.

Running this query manually then returns a detailed listing of every single member including a large volume of metadata.

Unfortunately? It’s not the best metadata. ‘MemberLevel’ is a misnomer – basically you have +1 for the dimension name and -1 for everything else. ‘QualifiedName’ is useful, but because it’s a parent child listing you still need to iterate in order to actually identify where you are. Still – we should be able to construct some queries to give us what we want!

Let’s start nice and simple – setting up our query:

$Username = "Username"$
Password = "password"
$URL = "https://planning-test-pivot2.pbcs.ap1.oraclecloud.com"
$Domain = "pivot2"
$AppName = "Demo" # This is the name of the Planning application
$Dim = "Entity" # This is the dimension you're querying
$Filename "F:\PBCS_REST\Example.csv"

# 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

$AllMembers = Invoke-RestMethod -Uri $PBCS_REST_URL -Method GET -Headers @{"Authorization"="Basic $($Enc_PW)"} -ContentType "application/json"
$AllMembers | ConvertTo-Csv > $Filename -NoTypeInformation

This returns everything and utilises powershell’s built in csv converter to render something reasonable.

Useful but not what we want – let’s build out a query to just return level 0 members.

$Finalmembers= @() # Create an array for the members
try{
    #Trigger the dim query
    $AllMembers = Invoke-RestMethod -Uri $PBCS_REST_URL -Method GET -Headers @{"Authorization"="Basic $($Enc_PW)"} -ContentType "application/json"
    foreach ($member in $AllMembers)
        {$Finalmembers += $AllMembers.items | Where-Object {$_.childCount -eq "0"} | Select-object memberName -Unique }# filter for the level 0 members (where no children)
    $Finalmembers | Select-object memberName -Unique | ConvertTo-Csv > $Filename -NoTypeInformation  # select the uniques and convert to csv 
}
Catch {throw $_.Exception.Message}

We can’t use a level indicator, but it’s simple enough to just check if the members have children. This now returns us a nice simple list of members.

Great! So Level 0 is nice and easy. Recently however, I’ve been doing some work with EPBCS Hybrid and found myself reminiscing about Celvin’s incredible Essbase Member Operation Utility – specifically it’s ability to bulk update higher level members to a new storage type. So I got wondering, what if we want greater than level 0?

$Lev = 2 #define the level to return
# Trigger the Dim Function
$Finalmembers= @() # Create an array for the members
    #Trigger the dim query
    $AllMembers = Invoke-RestMethod -Uri $PBCS_REST_URL -Method GET -Headers @{"Authorization"="Basic $($Enc_PW)"} -ContentType "application/json"
    foreach ($member in $AllMembers) {
        $Finalmembers += $AllMembers.items | Where-Object {$_.childCount -eq "0"} | Select-object memberName,parentName -Unique # filter for the level 0 members (where no children)
        }
    while ($Lev -gt 0) # for any level higher than 0, recursively query 
    {
    $upperMbrs=@() # build an upper level array 
    foreach ($member in $Finalmembers) # for every member in the previous list (or level 0 for the first pass) 
        {
        $upperMbrs += $AllMembers.items | Where-Object {$Finalmembers.parentName -eq $_.memberName} #give the details where the previous levels parent member equals the member name
        }
    $lev -- #iterate through the levels 
    $Finalmembers = $upperMbrs | Select-object memberName,parentName,alias -Unique # select only the unique members (to make the recursion quicker)
    }
$Finalmembers | Select-object memberName -Unique | ConvertTo-Csv > $Filename -NoTypeInformation  # select the uniques and convert to csv

So this is a little more convoluted – but basically we’re finding the Level x members – getting all their parents and then looping and recursively searching up the hierarchy.

One note to make at this junction – this is not speedy code. Recursive loops through the pipeline with a where-object as a filter is….about as bad as you can get for performance! It’s not that awful – pulling big dimensions (10k+ members) takes around 5 mins. Buuuuut….yeah, performance is important. Let’s re-write it.

    $AllMembers = Invoke-RestMethod -Uri $PBCS_REST_URL -Method GET -Headers @{"Authorization"="Basic $($Enc_PW)"} -ContentType "application/json"
    foreach ($member in $AllMembers) {
    $FinalMembers = $member.items | where childCount -eq "0"
    }

Firstly changing to a where clause from a where-object clause removes our script block meaning we can only do a simple comparison, but this massively speeds up the iteration.

while ($Lev -gt 0) # for any level higher than 0, recursively query 
{
$lookup = foreach ($member in $Finalmembers) # for every member in the previous list (or level 0 for the first pass) 
    {$member.parentName}  #add the parent names to the lookup table
    $lookup = $lookup | sort -Unique # grab only the unique parents
$Uppermbrs = foreach ($member in $allmembers) #iterate through the entire list again
    {$member.items | where {$_.memberName -in $lookup}} #filter now on the members in the lookup
$lev -- #iterate through the levels 
$Finalmembers = $upperMbrs #assign the upper members to the new list
}
$Finalmembers | Select-Object memberName | ConvertTo-Csv > $Filename -NoTypeInformation  # select the uniques and convert to csv

For the recursion – building the parent member as a lookup list with a single iteration means we can run through it much quicker.

Much much quicker.

But wait! You want to query using generations instead of levels? Alright then. We can’t use recursion up the ParentName hierarchy, instead we’ll use a (glorious) hackjob and look at the qualifiedName. By counting the number of members in the qualifiedName reference we can work out which generation it is!

$Gen = 5
$v2 = (measure-command{
$AllMembers = Invoke-RestMethod -Uri $PBCS_REST_URL -Method GET -Headers @{"Authorization"="Basic $($Enc_PW)"} -ContentType "application/json"
foreach ($member in $AllMembers)
    {$Finalmembers = $AllMembers.items | Where-object {(($_.qualifiedName) -ne $null) -and (($_.qualifiedName).split(".").count -eq $Gen)}}
    $Finalmembers | Select-object memberName -Unique | ConvertTo-Csv > $Filename -NoTypeInformation  # select the uniques and convert to csv 
}).totalseconds

Write-Host ('15k members {0} secs' -f  $v2)

Same basic logic – except we’re filtering out the nulls, and then counting the number of “.”‘s to define how deep we are in the hierarchy.

A bit slower – but reasonable given the size of the dimension.

Hopefully that helps – I’ve built out the queries above as powershell functions available below – feel free to modify, use etc as needed, making sure to always test test and test again before running anything in Production.

Function Get-LevMbrs
{
<#
.SYNOPSIS
Queries PBCS Data Management Rest API to the get the dimensional members referenced by level 
.DESCRIPTION
Queries PBCS Data Management Rest API to the get the dimensional members referenced by level - will write to a file
.PARAMETER Username
The PBCS username for the instance
.PARAMETER password
The PBCS password for the instance
.PARAMETER URL
The PBCS URL including the HTTPS prefix
.PARAMETER AppName
The Data management application name (refer to the target application screen in setup for the names)
.PARAMETER Dim
The dimension you wish to return the members for
.PARAMETER Lev
The level in the dimension you wish to return the members for
.PARAMETER filname
Optional local path to where you would like the file saved - defaults to $Dim_Lev$Lev_Members.csv in the current directory. 
.OUTPUTS
Returns a csv file with the member names
.LINK
https://essbasedownunder.com/2019/04/extracting-metadata-from-pbcs-using-the-rest-api/
.EXAMPLE
/Get-LevMbrs -Username admin -Password password -URL https://planning-test-domain.pbcs.ap1.oraclecloud.com/ -App Demo -Dim Entity -Lev 0
.EXAMPLE
/Get-LevMbrs admin password https://planning-test-domain.pbcs.ap1.oraclecloud.com/ Demo Entity 0 "F:\PBCS\Members.csv"
.NOTES
Author: Peter Nitschke
#>
[cmdletbinding()]
Param(
[Parameter(Mandatory=$True)][string]$Username, 
[Parameter(Mandatory=$True)][string]$Password, 
[Parameter(Mandatory=$True)][string]$URL,
[Parameter(Mandatory=$True)][string]$App, 
[Parameter(Mandatory=$True)][string]$Dim, 
[Parameter(Mandatory=$True)][string]$Lev,
[Parameter(Mandatory=$False)][string]$Filename = $PSScriptRoot + ‘\’ + $Dim + ‘_Lev’ + $Lev + ‘_Members.csv’
)

    # Construct Domains   
    $URLSplit = ([uri]”$URL”).Host.split(‘-‘).split(‘.’)
    if ($URLSplit[1] -eq “test”)
    {$Domain = $URLSplit[2]}
    else
    {$Domain=$URLSplit[1]}
    
    #Trim the URLs in case they have trailing /
    $URL = $URL.TrimEnd(‘/’)

    # Construct URL
    $PBCS_REST_URL = “{0}/aif/rest/V1/applications/{1}/dimension/{2}/” -f $URL $App $Dim

    # 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)

    # Invoke PBCS REST API to get Latest API version
    try
    {
    $AllMembers = Invoke-RestMethod -Uri $PBCS_REST_URL -Method GET -Headers @{“Authorization”=”Basic $($Enc_PW)”} -ContentType “application/json”   
        $Finalmembers = foreach($member in $AllMembers)
            {$member.items | where childCount -eq “0”}
        while ($Lev -gt 0) # for any level higher than 0, recursively query 
        {
            $lookup = foreach ($member in $Finalmembers) # for every member in the previous list (or level 0 for the first pass) 
                {$member.parentName}  #add the parent names to the lookup table
            $lookup = $lookup | sort -Unique # grab only the unique parents
            $Uppermbrs = foreach ($member in $allmembers) #iterate through the entire list again
                {$member.items | where {$_.memberName -in $lookup}} #filter now on the members in the lookup
            $lev — #iterate through the levels 
            $Finalmembers = $upperMbrs #assign the upper members to the new list
        }
        $Finalmembers | Select-Object memberName | ConvertTo-Csv > $Filename -NoTypeInformation  # select the uniques and convert to csv 
    }
    # Return the error if the connection fails
    Catch
    {throw $_.Exception.Message}
}

Function Get-GenMbrs
{
<#
.SYNOPSIS
Queries PBCS Data Management Rest API to the get the dimensional members referenced by generation 
.DESCRIPTION
Queries PBCS Data Management Rest API to the get the dimensional members referenced by generation - will write to a file
.PARAMETER Username
The PBCS username for the instance
.PARAMETER password
The PBCS password for the instance
.PARAMETER URL
The PBCS URL including the HTTPS prefix
.PARAMETER AppName
The Data management application name (refer to the target application screen in setup for the names)
.PARAMETER Dim
The dimension you wish to return the members for
.PARAMETER Gen
The Generation in the dimension you wish to return the members for
.PARAMETER filname
Optional local path to where you would like the file saved - defaults to $Dim_Gen$Gen_Members.csv in the current directory. 
.OUTPUTS
Returns a csv file with the member names
.LINK
https://essbasedownunder.com/2019/04/extracting-metadata-from-pbcs-using-the-rest-api/
.EXAMPLE
/Get-GenMbrs -Username admin -Password password -URL https://planning-test-domain.pbcs.ap1.oraclecloud.com/ -App Demo -Dim Entity -Gen 3
.EXAMPLE
/Get-GenMbrs admin password https://planning-test-domain.pbcs.ap1.oraclecloud.com/ Demo Entity 4 "F:\PBCS\Members.csv"
.NOTES
Author: Peter Nitschke
#>
[cmdletbinding()]
Param(
[Parameter(Mandatory=$True)][string]$Username, 
[Parameter(Mandatory=$True)][string]$Password, 
[Parameter(Mandatory=$True)][string]$URL,
[Parameter(Mandatory=$True)][string]$App, 
[Parameter(Mandatory=$True)][string]$Dim, 
[Parameter(Mandatory=$True)][string]$Lev,
[Parameter(Mandatory=$False)][string]$Filename = $PSScriptRoot + ‘\’ + $Dim + ‘_Lev’ + $Lev + ‘_Members.csv’
)
    # Construct Domains   
    $URLSplit = ([uri]”$URL”).Host.split(‘-‘).split(‘.’)
    if ($URLSplit[1] -eq “test”)
    {$Domain = $URLSplit[2]}
    else
    {$Domain=$URLSplit[1]}
    
    #Trim the URLs in case they have trailing /
    $URL = $URL.TrimEnd(‘/’)

    # Construct URL
    $PBCS_REST_URL = “{0}/aif/rest/V1/applications/{1}/dimension/{2}/” -f $URL $App $Dim

    # 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)

    # Invoke PBCS REST API to get Latest API version
    try
    {
    $AllMembers = Invoke-RestMethod -Uri $PBCS_REST_URL -Method GET -Headers @{“Authorization”=”Basic $($Enc_PW)”} -ContentType “application/json”
    foreach ($member in $AllMembers)
        {$Finalmembers = $AllMembers.items | Where-object {(($_.qualifiedName) -ne $null) -and (($_.qualifiedName).split(“.”).count -eq $Gen)}}
    $Finalmembers | Select-object memberName -Unique | ConvertTo-Csv > $Filename -NoTypeInformation  # select the uniques and convert to csv 
    }
    # Return the error if the connection fails
    Catch
    {throw $_.Exception.Message}
}

Tekin