G’day All,
So – I spent 7 hours working through a fancy REST API script, reading optimisation notes on Powershell queries, and re-working and re-writing the code to be simple and clean to finally post up a detailed ‘warts and all’ article.
And then I actually tried to use it! And it, er, didn’t quite go to plan.
So what is the issue?
Let’s look at a fairly basic hierarchy:
I’m using the Entity hierarchy in a fairly standard format. Reasonably ragged, some levels go to state and some into country, but functionally nothing that Essbase doesn’t handle perfectly.
Let me add the level zero flags.
Top section makes perfect sense – the issue comes with the Entity_NA member. Remembering the way our query works, for summary members we iterate to find the parent member of each level zero.
And now you can see the problem (and hopefully not just because I highlighted it in bright red). We have a ragged hierarchy, so the summary levels are ragged as well. While perfectly correct, this is very likely not what we want.
Fortunately- we’re in Powershell Country
Looking at the qualified names gives us a good idea on how to solve the issue though. By adding a “ParentMember” variable to the prompt, we can then filter on the qualifiedName to get only the members that have this upper level member.
{$member.items | Where qualifiedName -like "*``[$ParentMember*" | where childCount -eq "0"} #select only level zero UNDER the parent member
$Filename = "F:\PBCS_REST\Output.csv"
$AppName = "Demo"
$Dim = "Entity"
$Lev = 1
$ParentMember = "Total_Entity"
# 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"
$Finalmembers = foreach($member in $AllMembers) # iterate through all the members
{$member.items | Where qualifiedName -like "*``[$ParentMember*" | where childCount -eq "0"} #select only level zero UNDER the parent member
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 -Unique | ConvertTo-Csv > $Filename -NoTypeInformation # select the uniques and convert to csv
Much better! Now I can actually use this function to set up my Hybrid Dynamic Calc members correctly. The full updated Get-LevMbrs function is available below!
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 ParentMember
An optional filter to only get the dimensional members under the named member - defaults to the top level dimension name
.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, #note – this is the Appname from Data Management target application
[Parameter(Mandatory=$True)][string]$Dim,
[Parameter(Mandatory=$True)][int]$Lev,
[Parameter(Mandatory=$False)][string]$ParentMember = $Dim,
[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 qualifiedName -like “*“[$ParentMember*” | where childCount -eq “0”} #select only level zero UNDER the parent member
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}
}
