GRC
HR
SCM
CRM
BI
Expand +


Article

 

RESTful Services Eases the Documentation of Web Intelligence Reports in SAP BusinessObjects 4.X

by Boris Knizhnik, Vice President, BIK Information Services, Inc.

April 4, 2017

Documenting Web Intelligence reports can require a great deal of effort, but if this task is combined with exploring internals of Web Intelligence, and learning the basics of Powershell and SAP Raylight Representational State Transfer (RESTful) Web Services API, it can be a fun and very useful experience.


Companies often require very detailed documentation of developed Web Intelligence reports and Universes. The object-oriented nature of the BusinessObjects platform makes it difficult to create simple Excel-style documentation for several reasons. First, a Web Intelligence report’s internal structures (e.g., the code used to create the Web Intelligence report) are not published as proprietary information. Second, the idea of exposing the internal structure of reports or Universes flies in the face of one of the cornerstones of object-oriented design: encapsulation.

However, since companies often insist on having report documentation, it falls to developers to produce this kind of document, in many cases requiring a painstaking process of copying and pasting. Not only is this not a very productive use of a developer’s time, this documentation also quickly becomes obsolete since reports and Universes are constantly evolving. Any automation that allows for the quick generation of lists of reports, variables, columns, Universes, SQLs, and data sources used in each Web Intelligence document produced at the last moment would be very useful.

SAP Raylight Representational State Transfer (RESTful) Web Services can sometimes be a bit of an intimidating concept for BusinessObjects developers. In this article, I explain why using PowerShell rather than Java can make concurring RESTful Web Services a lot easier and really open them up as a tool to explore and document Web Intelligence reports.

SAP provides a component object model (COM), application program interfaces (APIs), and Java Software Development Kits (SDKs) to get this information, but no actual documentation utility, so all the details of putting together a script, which uses these methods and creates readable documentation, was left to companies to find on their own.

In BusinessObjects Enterprise (BOE) 4.0, SAP introduced RESTful Web Services APIs to access the internals of their software, but finding good step-by-step instructions for developers is still difficult.

(Note: I found these nice snippets by Ted Ueda for scripting Web Intelligence reports: https://blogs.sap.com/2013/09/05/scripting-web-intelligence-the-restful-raylight-web-services/. Here are several good tips from Martin Gough: https://developmentish.wordpress.com/2014/05/30/logging-on-and-off-of-sap-businessobjects-using-powershell-and-the-restful-api/.)

These sources provide a lot of details about how to use RESTful services in BOE. (I want to point out this article is limited to documentation only; RESTful Web Services can create documents and run them, but this is outside of the scope of this article). What is missing is an attempt to create a library of functions, such as Get lists of columns for this Web Intelligence document or Get lists of variables for this Web Intelligence document to cover the entire documentation process. Gathering little bits and snippets of information from the Web is not the same as having five or six functions to document Web Intelligence design.

In the following, I show how to create a library to document Web Intelligence reports. (The documenting of Universes is beyond the scope of this article.)

Software Recommendations and Requirements

In the examples in this article, I use Powershell version 4.0 (on my PC) and SAP BusinessObjects enterprise version 4.0 Support Package (SP3) server. (Powershell comes free with the Windows operating system).  I chose Powershell because it offers the simplest way to build a library of functions. Java is another option, and it gives you full control. However, you can become bogged down by too many details when using Java and, for BOE developers who only occasionally need to code something outside of Web Intelligence or a Universe, Java is too complicated. The same is true for the other options, C#, .net, and even Python. Powershell is also a good choice because even with only a rudimentary knowledge of how to use it, you can find working examples of whatever code is needed on the Internet. And, finally, unlike Java, where to run a batch program you must make sure to include all the pertinent .jar files in your PATH variable, when using Powershell you can have your library and running code in one or two files.

What Makes This Code a Library?

Here are the three main principles on which to build your library:

  1. Each Powershell function should use the absolute minimum of parameters and require no knowledge of what’s inside to use it (even though the code is there in the open).
  2. Each Powershell function should output results as a table of simple attributes that can then be saved into a .csv file or piped into another function. (For more information about Powershell, follow this link: https://msdn.microsoft.com/en-us/library/ms714469(v=vs.85).aspx.)
  3. Each Powershell function should be self-documented with complete example(s) of how to use it.

Following these principles allows you to create other functions in the same manner. It also allows you to combine functions with a little bit of Powershell code. For instance, you can get a list of documents into an external .csv file, then take this file, filter it to include only documents from a certain folder, and pipe the document IDs into the get_report_variables function.

What Are RESTful Web Services?

To become better acquainted with the concept of RESTful Web Services, an excellent source is Alex Rodriguez’ article, “RESTful Web Services: The Basics.” A quick description is that RESTful Web Services is a Web development framework for reading information from the Web that only requires passing a URL with all the parameters embedded in it (you must pass some authentication information in a more elaborate way, but I show a painless way to do this).

Basically, to get a list of documents you must call http://something/documents. To get a specific document 123, you call http://something/documents/123. To get all the reports within document 123, you call http://something/documents/123/reports, and so forth.

The biggest problem is knowing what this next level is called. The structure or the URL you are using is determined by the internal structure of your underlying object. This is not a coincidence, but a guiding principle of the RESTful Web Services framework. Another RESTful principle is that when the Web service returns the result to you it should include some other useful information. For instance, if a list of documents is longer than 50 documents, you get a link (in addition to the first 50 documents) to call the next page. This is not a nicety from a developer with a lot of free time, but a RESTful framework requirement. The SAP implementation of the RESTful framework is called Raylight.

The Administrative Items

I assume the installation has several similar environments and you may need to point to any of them. First, create a file (c:\Users\{yourWindowsId}\Bo4.properties) with the code shown in Figure 1.

myBOE:URL=http://servername:6405/biprws

myBOE:userName=myid

myBOE:password=mypassword

myBOE:auth=secEnterprise

anotherBOE:URL=http://anotherservername:6405/biprws

anotherBOE:userName=myid

anotherBOE:password=mypassword

anotherBOE:auth=secEnterprise

Figure 1
Save the properties file of BusinessObjects 4.0

Replace server names with the appropriate server names. Port 6405 and biprws are the default values out of the box. Check with your administrator to see if any changes have been made to the server names before proceeding. Obviously, the user name you are using should have the correct permissions to access what you want to access.

How to Run Powershell Scripts

If you are familiar with Powershell and how it works, skip this section.

In Windows, click Start and then enter Powershell into your search box. Once the search options appear, select Windows Powershell ISE or Windows Powershell ISE (X86). In Powershell you may run entire files or just highlighted parts of the script. In this example, we show how to do the latter option.

To run script in Powershell ISE, select the code you want to run and then click the run selection icon (Figure 2).


Figure 2
Click the run selection icon

How to Use RESTful Web Services

The best way to experiment with RESTful services is to save the RESTful_Web Intelligence.ps1 file in c:\users\{your id} directory (the same directory where you saved your BusinessObjects 4.0 properties file in Figure 1). Then type in the first three lines of code, starting with . c:\Users (shown in Figure 3), highlight them, and click the run selection icon (shown in Figure 2). Then enter the rest of the code where indicated in Figure 3. When done experimenting, highlight the last line of code (starting with BOE4_logoff) to log off.

. c:\Users\$env:username\RESTful_webi.ps1

$logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

$boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

{place your code here}

BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

Figure 3
Code template for working with RESTful Web Services

The RESTful services are now set up and ready to use. If you created the bo4.properties file correctly (with your user ID and password), you are automatically logged on and the object $boeRestfulHeaders is filled correctly and ready for you to start experimenting.

The first line of code reads all the function definitions from the RESTful_Web Intelligence.ps1 file into your current session. The second line reads your bo4.properties file and passes the server/userid and password information from the appropriate section. The third line of code establishes the connection the BOE server and creates the correct headers for future experiments. (If you want to know the details of all this, you can see them in the file RESTful_Web Intelligence.ps1.) Once you run these three lines you may continue to experiment without having to know how you connected and what’s inside the $boeRestfulHeaders.

After you’re done experimenting, highlight and run the last line of code, BOE4_logoff, to disconnect from the BOE server. In this example, I used the JavaScript Object Notation (JSON) method for passing and receiving parameters/information and choose US English as the language setting.

Exploring Web Intelligence Documents 

Using the first three lines of code (Figure 3), you can hide all the details of establishing the connection with BOE and saving the security token, and the JSON settings to send information back and forth. Now you are free to explore the internal structure of Web Intelligence documents with ease. For instance, you want to try to get a list of folders under the root folder using RESTful services.

Remember, you already established the connection and your variable $BOERestfulHeaders is set up with the token and the rest of the necessary information. So, just enter the code in Figure 4 as the fourth line in your Powershell ISE window, highlight it, and click the "run selection" icon.


Figure 4
Get information about the InfoView’s root folder

To display the result in variable $res, highlight $res in your Powershell ISE screen (Figure 2) and click the "run the selection" icon. This results in something like what is shown in Figure 5


Figure 5
The root folder information returned by your first RESTful API call

What do you learn from the details in Figure 5? You know what cuid, name, description, id, and type are, but what are Children and "up"? To find out, run $res.Children (e.g., enter $res.Children in your Powershell screen as the next line, highlight it, and click the "run selection" icon) and you get the result shown in Figure 6.


Figure 6
The information results for the children of the root folder

This means that the __deferred result consists of Powershell’s so-called hash table, which is a list of the key value pairs (in this case it is only one pair).

(Note: BOE 4.0 models create a Java version of these hash tables, but Powershell converts Java into a Powershell version of each data type. (Strictly speaking, when you made the call [in Figure 4] because $BOERESTfulHeaders was passed, the results are returned in the form of JSON data types. Powershell version 4 fixed some problems with RESTful Web Services, and now it seamlessly converts JSON results into Powershell variables.)

Follow up by running $res.Children.__deferred and you see the result show in Figure 7.


Figure 7
The uri to get the subfolders of the root folder

The code in Figure 7 is the URI to get the subfolders of the root folder. You can either plug it into your URI for your next RESTtful API call or use the expression $loginfo.URL+"/Infostore/Root%20Folder/Children " (shown in Figure 4).

Before going any further, note the pattern. The immediate result may not be a simple value; it may be a more complex variable.  However, Powershell’s dot notation allows you to get the bottom value by stringing the dots.  For instance, to see what the up variable is in Figure 5, simply type $res.up.__deferred  to get the result in one step, rather than in two. The result is shown in Figure 8.


Figure 8
The uri to get the list of objects available at your Infostore screen

This is a good example of what the RESTful Web Services framework does. In addition to retrieving the information you requested, the software returns additional information that helps you build the next request without remembering the values supplied in a previous request.

Tips for Using RESTful Web Services

Using the sources cited previously in this article, I discovered some helpful RESTful Web Services URLs. In the absence of SAP-issued official documentation about how to build the initial URI for a specific task (for instance, how to build the initial URI to get a list of reports within one document, or how to get a list of all available data providers) I have come with some documentation that I think you will find useful. To help you get started, look at Figure 9, which contains examples of code for using RESTful API calls using my methodology.

. c:\Users\$env:username\RESTful_webi.ps1

$logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

$boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

#------------------------------------------------------------------

# to get metadata about folders under folder Id 123

Invoke-RestMethod -Method GET -Uri  ($logonInfo.URL+"/infostore/123/children?type=folder&page=1&pageSize=50")  -Headers $boeRestfulHeaders

# to get metadata about the first 50 documents from CMS

$result = Invoke-RestMethod -Method GET -Uri ($logonInfo.URL+"/raylight/v1/documents?offset=0&limit=50") -Headers $boeRestfulHeaders

# to get detailed metadata about document with id 567

$result = Invoke-RestMethod -Method GET -Uri ($logonInfo.URL+"/raylight/v1/documents/567") -Headers $boeRestfulHeaders

# to get a list of reports (tabs) in document id 567

$result = Invoke-RestMethod -Method GET -Uri ($logonInfo.URL+"/raylight/v1/documents/567/reports") -Headers $boeRestfulHeaders

# to get a dataprovider (query) list for document id 567

$result = Invoke-RestMethod -Method GET -Uri ($logonInfo.URL+"/raylight/v1/documents/567/dataproviders") -Headers $boeRestfulHeaders

# to get information (including list of elements) from dataprovider (query) DP0 in document 567:

$result = Invoke-RestMethod -Method GET -Uri ($logonInfo.URL+"/raylight/v1/documents/567/dataproviders/DP0") -Headers $boeRestfulHeaders

# to get generic meta data about universe (datasource) with id 112233:

$result = Invoke-RestMethod -Method GET -Uri ($logonInfo.URL+"/infostore/112233") -Headers $boeRestfulHeaders

# to get SQL generated by dataprovider (query) DP0 of document id 567:

#   the information returned slightly varies depending on whether this dataprovider generates one or several SELECTs

$result = Invoke-RestMethod -Method GET -Uri ($logonInfo.URL+"/raylight/v1/documents/567/dataproviders/DP0/queryplan") -Headers $boeRestfulHeaders

# to get information about variables used in document id 567:

$result = Invoke-RestMethod -Method GET -Uri ($logonInfo.URL+"/raylight/v1/documents/567/variables") -Headers $boeRestfulHeaders

# to get information about variable with id L10 (not the same as variable name) in document 567:

$result = Invoke-RestMethod -Method GET -Uri ($logonInfo.URL+"/raylight/v1/documents/567/variables/L10") -Headers $boeRestfulHeaders

# to get information about prompts used in dataprovider (query) DP0 of document id 567

$result = Invoke-RestMethod -Method GET -Uri ($logonInfo.URL+"/raylight/v1/documents/567/dataproviders/DP0/parameters") -Headers $boeRestfulHeaders

# logoff

BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

Figure 9
Examples of RESTful API calls using Powershell methodology

Using the code in Figure 9, however, is only the beginning of your exploration of Web Intelligence internal structures. It is a long journey from just exploring the internal structures to using real, robust code. For instance, when using folders, you not only need to page through the list of folders, you also need to use recursion to get subfolder information.

When using documents, you only need to page through the list (note that the parameter names used for page numbers for documents are not the same as the parameter names for folders).

Because of this, I am providing a RESTful_webi.ps1 library which does most of the tedious work for you. Some code examples of how these efforts come together in the form of libraries are shown in Figure 10.

#----------------------------------------------------

. c:\Users\$env:username\RESTful_webi.ps1

$logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

$boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

 

# all folders

$folderList = BOE4_get_folder_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

#   list of documents

$documentList = BOE4_get_document_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

#   list of reports for all documents

$reportList =  BOE4_get_report_list -hostUrl  $logonInfo.URL

 -BOEHeaders $boeRestfulHeaders

# Get a list of dataproviders for all documents

$dataproviderlist = BOE4_get_dataprovider_list -hostUrl  $logonInfo.URL

 -BOEHeaders $boeRestfulHeaders

# Get list of columns for dataproviders for all documents

$dataproviderColumnList = BOE4_get_dataprovider_column_list -hostUrl  $logonInfo.URL

 -BOEHeaders $boeRestfulHeaders

# Get list of variables for all documents

$documentVariableList = BOE4_get_document_variable_list -hostUrl  $logonInfo.URL

 -BOEHeaders $boeRestfulHeaders

# Get list of prompts for all documents

$documentPromptList = $docfilter |BOE4_get_prompt_list -hostUrl  $logonInfo.URL

 -BOEHeaders $boeRestfulHeaders

# logoff

BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders 

Figure 10
Examples of RESTful_webi.ps1 library usage

As I showed previously, after running the log-on part you can run each individual line, then highlight the result, and then click the run the selection icon and see what is returned. What you will find is that each of these functions used the low-level calls to RESTful services (shown in Figure 9), but they also took care of the loops (for example, to get all the documents it had to loop through several pages returned by RESTful calls) and formatting (for instance, returning the results in a form ready to be saved as a Comma-Separated-Values (CSV) file.

Now, as I stated in the beginning of this article, the goal is to create one function that can be run quickly at the last minute to create up-to-the-minute documentation of the Web Intelligence reports being delivered.

Figure 11 shows this one function, BOE4_document_webi, that calls all the appropriate functions (shown in Figure 10) and combines their results in an Excel file. This example also includes a method that developers can use to filter reports.

#----------------------------------------------------------------------

. c:\Users\$env:username\RESTful_webi.ps1

$logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

$boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

# all documents

$documentList = BOE4_get_document_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

# filter only documents for smith

$documentList | Where-Object {($_.createdBy -eq "smith")} | Select ID |  foreach-object{$_.id} |BOE4_document_webi -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders -xlsxFile "c:\temp\Webi_documentation_smith.xlsx"

# logoff

BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

Figure 11
Example of a high-level Powershell call to create a complete Excel file with documentation of all the reports developed by the user

The code in Figure 12 is the RESTful_webi.ps1 file that contains the library of Powershell functions mentioned in this article

Function BOE4_getconfig {

<#

.SYNOPSIS

Get specific configuration from a file with several configurations

 

.NOTES

AUTHOR: Boris Knizhnik

 

.PARAMETER fileName

full path to your properties file

    the file structure is expected as follows:

 

    myBOE:URL=http://servername:6405/biprws

    myBOE:userName=myid

    myBOE:password=mypassword

    myBOE:auth=secEnterprise

 

    anotherBOE:URL=http://anotherservername:6405/biprws

    anotherBOE:userName=myid

    anotherBOE:password=mypassword

    anotherBOE:auth=secEnterprise

 

.PARAMETER configItem

configuration nickname

 

.OUTPUTS

Arraylist of objects with properties URL,userName,password,auth

 

.EXAMPLE

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

 

#>

    [CmdletBinding()]

    param(

        [Parameter(Position=0,Mandatory=$true)] 

        [string] $fileName,  # c:\Users\yourname\BO4.properties

        [Parameter(Position=1,Mandatory=$true)]

        [String] $configItem  #configNickName

    )

 

    $config = (ConvertFrom-StringData ((Get-Content $fileName -Raw) -replace "\\","\\") )

    $config1 = $config.GetEnumerator() | ? {$_.key -like "${configItem}:*"}

 

    $config2 = @{}

    for ($i = 0; $i -lt $config1.Count; $i++) {$config2.Add(($config1[$i].name -replace "${configItem}:",""),$config1[$i].value)}

 

    $config2

}

#get-help BOE4_getconfig -full

 

 

Function BOE4_logon {

<#

.SYNOPSIS

Log on to BOE enviornment

 

.NOTES

AUTHOR: Boris Knizhnik

 

.PARAMETER hostUrl

link to your host - see BOE4_getConfig

 

.PARAMETER BOEHeaders

request header information to be passed - see BOE4_getConfig

 

.OUTPUTS

 

.EXAMPLE

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

#>

    [CmdletBinding()]

    param(

        [Parameter(Position=0,Mandatory=$true)] 

        [string] $hostUrl, 

        [Parameter(Position=1,Mandatory=$true)]

        [hashtable] $logonInfo 

    )

    $locale = "en-US"

    $contentLocale = "en-US"

 

    $headers = @{"Accept"="application/json"; "Content-Type"="application/json"}

    $result = Invoke-RestMethod -Method Post -Uri ($hostUrl+"/logon/long") -Headers $headers -Body (ConvertTo-Json($logonInfo))

    $logonToken = "`"" + $result.logonToken+"`""  # The logon token must be delimited by double-quotes

 

    $headers = @{"X-SAP-LogonToken" = $logonToken;"Accept"="application/json"; "Content-Type"="application/json"; "Accept-Language"="en-US"; "X-SAP-PVL" = "en-US" }

    $headers

 

}

#get-help BOE4_logon -full

 

 

Function BOE4_logoff {

<#

.SYNOPSIS

Log off of BOE enviornment

 

.NOTES

AUTHOR: Boris Knizhnik

 

.PARAMETER hostUrl

link to your host - see BOE4_getConfig

 

.PARAMETER BOEHeaders

request header information to be passed - see BOE4_getConfig

 

.OUTPUTS

 

.EXAMPLE

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

 

#>

    [CmdletBinding()]

    param(

        [Parameter(Position=0,Mandatory=$true)] 

        [string] $hostUrl, 

        [Parameter(Position=1,Mandatory=$true)]

        [hashtable] $BOEHeaders

    )

    Invoke-RestMethod -Method Post -Uri ($hostUrl+"/logoff") -Headers $BOEHeaders

}

#get-help BOE4_logoff -full

 

Function BOE4_get_folder_list {

<#

.SYNOPSIS

Get List of Folders from BOE 4 environment

 

.NOTES

AUTHOR: Boris Knizhnik

 

.PARAMETER hostUrl

link to your host - see BOE4_getConfig

 

.PARAMETER BOEHeaders

request header information to be passed - see BOE4_getConfig

 

.PARAMETER folderID

optional folder IsD if you want to get a list of subfolders for a specific folder

 

.PARAMETER fullPath

optional string to be placed in front of each folder path

 

.OUTPUTS

Arraylist of objects with properties id,name,type,cuid,description,FullPath,__metadata

 

.Example

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    $folderList = BOE4_get_folder_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

    BOE4_logoff -hostUrl $hostUrl -BOEHeaders $boeRestfulHeaders

 

.Example

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    $folderList = BOE4_get_folder_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders -folderID 123 -fullPath '\Public\CompanyFolder'

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

 

#>

 

    [CmdletBinding()]

    param(

        [Parameter(Position=0,Mandatory=$true)] 

        [string] $hostUrl,  # http://server:6405/biprws

        [Parameter(Position=1,Mandatory=$true)]

        [hashtable] $BOEHeaders, 

        [Parameter(Position=2,Mandatory=$false)]

        [String] $folderID,

        [Parameter(Position=3,Mandatory=$false)]

        [string] $fullPath

    )

 

    ####  start inner function

                Function BOE4_get_folder_list_recur {

                [CmdletBinding()]

                param(

                    [Parameter(Position=0,Mandatory=$true)] 

                    [string] $hostUrl, 

                    [Parameter(Position=1,Mandatory=$true)]

                    [hashtable] $BOEHeaders, 

                    [Parameter(Position=2,Mandatory=$false)]

                    [string] $folderID,

                    [Parameter(Position=3,Mandatory=$false)]

                    [string] $fullPath

                )

 

                if (($PSBoundParameters.ContainsKey('folderID') -eq $false) -or ($folderID -eq $null)  -or ($folderID -eq "")) {

                        $requestUri = "${hostUrl}/infostore/Root%20Folder/"

                        $result = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $BOEHeaders

#                        $nm = "Root Folder"

                        $nm = "Public Folders"

                        $id = $result.id

                } else {

                    $id = $folderID

                }

 

                $requestUri = "${hostUrl}/infostore/${id}"

                $result = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $BOEHeaders

                $nm = $result.name

                if ($nm -eq "Root Folder") {$nm="Public Folders"}

 #               Write-Verbose ("nm="+$nm)

                $nm = $fullPath+"\"+$nm

 

                $res = @()

 

 

                # get list of folders in increments of 50

                $folderRequesttUri = "${hostUrl}/infostore/${id}/children"

                $startPage = 1

                $pgSize = 50

                $cnt = $pgSize

                While ($cnt -ne 0 ) {

                    $requestUri = "${folderRequesttUri}?type=folder&page=${startPage}&pageSize=${pgSize}"

                    try {

                        $result = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $BOEHeaders

                        $folders = $result.entries

                        $cnt = $folders.count

                        $startPage = $startPage + 1

                        $res = $res + $folders

                        if ($cnt -lt $pgSize) {$cnt = 0}

                       } catch {$cnt = 0}  # this may only happen if the number of users is divisible by 50  (this seems to be only correct for documents and not users

                }

 

                # output results of the first level

                $res | ForEach-Object {

                            $wrknm = $_.name

                            $uri = $_.__metadata.uri

                            $fpath = "${nm}\${wrknm}"

 

                            # output

                            @{id=$_.id;cuid=$_.cuid;name=$_.name;description=$_.description;type= $_.type; __metadata = $uri; FullPath="${nm}\${wrknm}" }

 

                            # recursive call to the next level

                            BOE4_get_folder_list -hostUrl $hostUrl -BOEHeaders $BOEHeaders -FolderID $_.id -fullPath "${nm}"

                        }

            }

 

 

    #### end of inner function

 

 

 

    $res = BOE4_get_folder_list_recur -hostUrl $hostUrl -BOEHeaders $BOEHeaders -folderID $folderID -fullPath $fullPath

 

    [System.Collections.ArrayList]$collection = New-Object System.Collections.ArrayList($null)

    $res | ForEach-Object {

    $folderInfo = @{id = $_.id;name = $_.name;type = $_.type;cuid = $_.cuid;description = $_.description;FullPath = $_.FullPath;__metadata = $_.__metadata;}

    $collection.Add((New-Object PSObject -Property $folderInfo)) | Out-Null

    }

    $collection

}

#get-help BOE4_get_folder_list -full

 

 

Function BOE4_get_document_list {

<#

.SYNOPSIS

Get List of documents from BOE 4 environment

 

.NOTES

AUTHOR: Boris Knizhnik

 

.PARAMETER hostUrl

link to your host - see BOE4_getConfig

 

.PARAMETER BOEHeaders

request header information to be passed - see BOE4_getConfig

 

.PARAMETER DocIDs

one or several comma delimited docIDs.  Instead of this parameter one may pipe the list of DocIDs

 

.OUTPUTS

Arraylist of objects with properties id,name,folderPath,updated,refreshOnOpen,scheduled,createdBy,lastAuthor,size,folderId,cuid,state

 

.Example

 

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    $documentList = BOE4_get_document_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

 

.Example

 

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    $documentList = BOE4_get_document_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders -DocID 1234

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

 

.Example

 

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $hostUrl=$logonInfo.URL

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    $documentList = (Get-Content "c:\temp\list_of_ids.txt") | BOE4_get_document_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

 

 

#>

    [CmdletBinding()]

    param(

        [Parameter(Position=0,Mandatory=$true)] 

        [string] $hostUrl,

        [Parameter(Position=1,Mandatory=$true)]

        [hashtable] $BOEHeaders,

        [Parameter(Position=2,Mandatory=$false,ValueFromPipeline=$true)]

        [String[]] $DocIDs 

    )

 

    if (($Input.length -ne 0) -and ($input[1] -ne $null)) {$docfilter = [System.Collections.ArrayList]  $Input}

    else {$docfilter = [System.Collections.ArrayList]  $docIDs }

 

 

#    write-host ("docfilter="+$docFilter)

    # get list of Documents in increments of 50 (lightray's limitation)

 

    $DocRequestUri = $hostUrl+"/raylight/v1/documents"

    $startOffset = 0

    $res = @()

 

    $cnt = 50

    While ($cnt -ne 0 ) {

        $DocRequestFullUri = $DocRequestUri+"?offset="+$startOffset+"&limit=50"

        try {

            $result = Invoke-RestMethod -Method GET -Uri $DocRequestFullUri -Headers $BOEHeaders

            $Docs = $result.documents.document

            $cnt = $Docs.count

            $startOffset = $startOffset + $cnt

            $res = $res + $Docs

            if ($cnt -lt 50) {$cnt = 0}

           } catch {$cnt = 0}  # this may only happen if the number of reports is divisible by 50

    }

 

    # if necessary leave only requested docIDs

 

    if (($docFilter -ne $null ) -and ($docFilter -ne "")) {

        $docList = $res | where-object {$_.id -in $docFilter}

    } else {

        $docList = $res

    }

 

    # document list is in variable $res, but it only has folderIds. Let's enrich it with the folder name

    $folderList = BOE4_get_folder_list -hostUrl $hostUrl -BOEHeaders $boeHeaders

 

 

    [System.Collections.ArrayList]$collection = New-Object System.Collections.ArrayList($null)

    $docList | select id,cuid, name, state, folderId | forEach-object{

                $detDocInfoURL=$DocRequestUri+"/"+$_.id

                $detResult = Invoke-RestMethod -Method GET -Uri $detDocInfoURL -Headers $BOEHeaders

 

                $newObjInfo= @{id = $_.id; cuid = $_.cuid; name = $_.name; state = $_.state; folderId = $_.folderId;

#                    folderPath=($folderHash[$_.folderId]);

                    folderPath=$detResult.document.path;

                    size=$detResult.document.size; updated=$detResult.document.updated; refreshOnOpen = $detResult.document.refreshOnOpen;

                    createdBy=$detResult.document.createdBy;lastAuthor =$detResult.document.lastAuthor; scheduled=$detResult.document.scheduled

                }

                $collection.Add((New-Object PSObject -Property $newObjInfo)) | Out-Null

                                                                  }

    $collection | select id,name,folderPath,updated,refreshOnOpen,scheduled,createdBy,lastAuthor,size,folderId,cuid,state

 

 

}

#get-help BOE4_get_document_list -full

 

Function BOE4_get_report_list {

<#

.SYNOPSIS

Get List of reports within one or many documents from BOE 4 environment

 

.NOTES

AUTHOR: Boris Knizhnik

 

.PARAMETER hostUrl

link to your host - see BOE4_getConfig

 

.PARAMETER BOEHeaders

request header information to be passed - see BOE4_getConfig

 

.PARAMETER docIDs

docIDs  of the document to display reports of.

 

.OUTPUTS

Arraylist of objects with properties docId, docName, name, id, reference, showDataChanges, folderId, folderPath

 

.Example

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    $reportList = BOE4_get_report_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders -docID 12345

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

 

.Example

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    $reportList = 123,234 | BOE4_get_report_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

#>

    [CmdletBinding()]

    param(

        [Parameter(Position=0,Mandatory=$true)] 

        [string] $hostUrl, 

        [Parameter(Position=1,Mandatory=$true)]

        [hashtable] $BOEHeaders, 

        [Parameter(Position=2,Mandatory=$false,ValueFromPipeline=$true)]

        [String[]] $DocIDs 

    )

 

 

    if (($Input.length -ne 0) -and ($input[1] -ne $null)) {$docfilter = [System.Collections.ArrayList]  $Input}

    else {$docfilter = [System.Collections.ArrayList]  $docIDs }

 

    # get document information to enrich report information

    $documentList = BOE4_get_document_list -hostUrl $hostUrl -BOEHeaders $boeHeaders -DocIDs $DocFilter

 

     # Create document dictionary

    $documentHash = @{}

    $documentList | SELECT id, name, folderPath, folderId | forEach-object {$newObjectInfo = @{name=$_.name; folderPath=$_.folderPath; folderId = $_.folderId}; $documentHash.add($_.id,$newObjectInfo)}

 

    [System.Collections.ArrayList]$reportCollection = New-Object System.Collections.ArrayList($null)

    $documentList | forEach-object {

                    $docId = $_.id

                    $docName = $documentHash[$docId].name

                    $folderPath = $documentHash[$docId].folderPath

                    $folderId = $documentHash[$docId].folderId

                    $requestUri = "${hostUrl}/raylight/v1/documents/${docId}/reports"

                    $report = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $boeHeaders

                    $rprts = @(,$($report.reports).report)  # in case the document has only one report we still want this function to return an array and not scalar

 

                    $rprts = $report.reports.report

                    $rprts |  forEach-object{

                                $newObjInfo= @{docId = $docId; docName = $docName; id = $_.id; name = $_.name; reference = $_.reference;

                                                folderId=$folderId;folderPath=$folderPath;

                                                showDataChanges = $_.showDataChanges}

                                $reportCollection.Add((New-Object PSObject -Property $newObjInfo)) | Out-Null

                                                                                  }

                                    }

     $reportCollection | select docId, docName, name, id, reference, showDataChanges, folderId, folderPath

}

#get-help BOE4_get_report_list -full

 

 

Function BOE4_get_dataprovider_list { 

<#

.SYNOPSIS

Get List of providers for all or any number of documents from BOE 4 environment

 

.NOTES

AUTHOR: Boris Knizhnik

 

.PARAMETER hostUrl

link to your host - see BOE4_getConfig

 

.PARAMETER BOEHeaders

request header information to be passed - see BOE4_getConfig

 

.PARAMETER docIDs

docIDs  of the document to display providers of.

 

.OUTPUTS

Arraylist of objects with properties docName,docId,isPartial,universeName,universeDesc,dataSourceType,universeType,dataproviderId,dataSourceId,name,updated,duration,rowCount,flowCount, op, qryNum, query, folderId, folderPath

 

.Example

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    $dataproviderList = BOE4_get_dataprovider_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders -docID 12345

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

 

.Example

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    $dataproviderList = 123,234 | BOE4_get_dataprovider_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

#>

    [CmdletBinding()]

    param(

        [Parameter(Position=0,Mandatory=$true)] 

        [string] $hostUrl,

        [Parameter(Position=1,Mandatory=$true)]

        [hashtable] $BOEHeaders, 

        [Parameter(Position=2,Mandatory=$false,ValueFromPipeline=$true)]

        [String[]] $DocIDs 

    )

 

    if (($Input.length -ne 0) -and ($input[1] -ne $null)) {$docfilter = [System.Collections.ArrayList]  $Input}

    else {$docfilter = [System.Collections.ArrayList]  $docIDs }

 

    # get document information to enrich document information

    $documentList = BOE4_get_document_list -hostUrl $hostUrl -BOEHeaders $BOEHeaders -DocIDs $DocFilter

 

     # Create document dictionary

    $documentHash = @{}

    $documentList | SELECT id, name, folderPath, folderId | forEach-object {$newObjectInfo = @{name=$_.name; folderPath=$_.folderPath; folderId = $_.folderId}; $documentHash.add($_.id,$newObjectInfo)}

 

    [System.Collections.ArrayList]$dataproviderCollection = New-Object System.Collections.ArrayList($null)

    $documentList | forEach-Object {

                $docId = $_.id

                $nm = $_.name

                $docName = $documentHash[$docId].name

                $folderPath = $documentHash[$docId].folderPath

                $folderId = $documentHash[$docId].folderId

                $requestUri = "${hostUrl}/raylight/v1/documents/${docId}/dataproviders"

 

                $work = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $BOEHeaders

                $dataproviders = ($work.dataproviders).dataprovider

                $dataproviders | forEach-object {

                          $dataSourceId = $_.dataSourceId

                          $dataProviderId = $_.id

                          $dataSourceType=$_.dataSourceType

                          $updated=$_.updated;

                          $requestUri = "${hostUrl}/raylight/v1/documents/${docId}/dataproviders/${dataproviderID}"

                          $dp = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $boeHeaders

                          $dps = $dp.dataprovider

                          $requestUri ="${hostUrl}/infostore/${dataSourceId}"

                          $univ = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $boeHeaders

 

                          $requestUri = "${hostUrl}/raylight/v1/documents/${docId}/dataproviders/${dataproviderID}/queryplan"

                          $qryPlan = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $boeHeaders

                          if ($qryplan.queryplan.fullOuterJoin.statement.count -gt 0) {

                                    $op="FullOuterJoin"

                                    $stmt=$qryplan.queryplan.fullOuterJoin.statement

                          } else {

                                    $op="Statement"

                                    $stmt=@($qryplan.queryplan.statement)

                          }

                          $stmt | forEach-Object {

                                      $newObjInfo= @{docId = $docId; docName = $docName; dataproviderId = $dataProviderId; name = $nm; dataSourceId = $dataSourceId;

                                                    dataSourceType=$dataSourceType; updated=$updated;

                                                    duration=$dps.duration; isPartial=$dps.isPartial;

                                                    rowCount=$dps.rowCount;

                                                    flowCount=$dps.flowCount;

                                                    op=$op; qryNum=$_.'@index'; query=$_.'$'

                                                    folderPath=$folderPath;folderId=$folderId;

                                                    universeName=$univ.name; universeDesc=$univ.description; universeType=$univ.type

                                                    }

                                      $dataproviderCollection.Add((New-Object PSObject -Property $newObjInfo)) | Out-Null

                          }

 

                                                }    

                             }                                          

      $dataproviderCollection | select docName,docId,isPartial,universeName,universeDesc,dataSourceType,universeType,dataproviderId,dataSourceId,name,updated,duration,rowCount,flowCount, op, qryNum, query, folderId, folderPath

}

 

 

Function BOE4_get_dataprovider_column_list {

<#

.SYNOPSIS

Get List of columns for all or any number of documents from BOE 4 environment

 

.NOTES

AUTHOR: Boris Knizhnik

 

.PARAMETER hostUrl

link to your host - see BOE4_getConfig

 

.PARAMETER BOEHeaders

request header information to be passed - see BOE4_getConfig

 

.PARAMETER docIDs

docIDs  of the document to display column list of.

 

.OUTPUTS

Arraylist of objects with properties docName,docId,isPartial,universeName,universeDesc,dataSourceType,universeType,dataSourceId,name,updated,duration,rowCount,flowCount,query,folderId,folderPath

 

.Example

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    $dataproviderList = BOE4_get_dataprovider_column_list -hostUrl $hostUrl -BOEHeaders $boeRestfulHeaders -docID 12345

    BOE4_logoff -hostUrl $hostUrl -BOEHeaders $boeRestfulHeaders

 

.Example

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    $dataproviderList = 123,234 | BOE4_get_dataprovider_column_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

#>    [CmdletBinding()]

    param(

        [Parameter(Position=0,Mandatory=$true)] 

        [string] $hostUrl, 

        [Parameter(Position=1,Mandatory=$true)]

        [hashtable] $BOEHeaders, 

        [Parameter(Position=2,Mandatory=$false,ValueFromPipeline=$true)]

        [String[]] $DocIDs 

    )

 

    if (($Input.length -ne 0) -and ($input[1] -ne $null)) {$docfilter = [System.Collections.ArrayList]  $Input

    } else {$docfilter = [System.Collections.ArrayList]  $docIDs }

 

    # get document information to enrich document information

    $documentList = BOE4_get_document_list -hostUrl $hostUrl -BOEHeaders $BOEHeaders -DocIDs $DocFilter

 

     # Create document dictionary

    $documentHash = @{}

#    $documentList | SELECT id, name | forEach-object {$documentHash.add($_.id,$_.name)}

    $documentList | SELECT id, name, folderPath, folderId | forEach-object {$newObjectInfo = @{name=$_.name; folderPath=$_.folderPath; folderId = $_.folderId}; $documentHash.add($_.id,$newObjectInfo)}

 

    [System.Collections.ArrayList]$dataproviderColumnCollection = New-Object System.Collections.ArrayList($null)

  

 

    $documentList | forEach-object {

                    $docId = $_.id

                    #$docId = 29819

                    $docName = $documentHash[$docId].name

                    $folderPath = $documentHash[$docId].folderPath

                    $folderId = $documentHash[$docId].folderId

                    $requestUri = "${hostUrl}/raylight/v1/documents/${docId}/dataproviders"

                    $dp = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $BOEHeaders

                    $dp.dataproviders.dataprovider | foreach-object {

                                $dataproviderID = $_.id

                                $dataproviderName=$_.name

                                $dataSourceId = $_.dataSourceId

                                $requestUri = "${hostUrl}/raylight/v1/documents/${docId}/dataproviders/${dataproviderID}"

                                $dp1 = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $BOEHeaders

                                $dct = $dp1.dataprovider.dictionary.expression

                                $dct | foreach-object {

                                $newObjInfo= @{docId = $docId; docName = $docName; dataSourceId = $dataSourceId;

                                                dataProviderId=$dataProviderID;dataProviderName=$dataproviderName;

                                                formulaLanguageId = $_.formulaLanguageId;name = $_.name; description = $_.description;

                                                folderPath=$folderPath;folderId=$folderId;

                                              '@datatype'=$_.'@datatype';'@qualification' = $_.'@qualification';dataSourceObjectId = $_.dataSourceObjectId;

                                                id=$_.id;associatedDimensionId=$_.associatedDimensionId

                                               }

                                               $dataproviderColumnCollection.Add((New-Object PSObject -Property $newObjInfo)) | Out-Null

                                                      }

                                                                    }

                                    }

   

    $dataproviderColumnCollection | select docId,docName,dataProviderName,formulaLanguageId,name,description,'@dataType','@qualification',dataSourceObjectId,id,associatedDimensionId,dataSourceId, dataProviderId, folderId, folderPath

 

}

#get-help BOE4_get_dataprovider_column_list -full

 

Function BOE4_get_document_variable_list {   

<#

.SYNOPSIS

Get List of document Variables for all or any number of documents from BOE 4 environment

 

.NOTES

AUTHOR: Boris Knizhnik

 

.PARAMETER hostUrl

link to your host - see BOE4_getConfig

 

.PARAMETER BOEHeaders

request header information to be passed - see BOE4_getConfig

 

.PARAMETER docIDs

docIDs  of the document to display variables of.

 

.OUTPUTS

Arraylist of objects with properties docName,docId,isPartial,universeName,universeDesc,dataSourceType,universeType,dataSourceId,name,updated,duration,rowCount,flowCount,query,folderId,folderPath

 

.Example

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo.URL $logonInfo

    $dataproviderList = BOE4_get_document_variable_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders -docID 12345

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

 

.Example

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    $dataproviderList = 123,234 | BOE4_get_document_variable_list -hostUrl $hostUrl -BOEHeaders $boeRestfulHeaders

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

#>  

    [CmdletBinding()]

    param(

        [Parameter(Position=0,Mandatory=$true)] 

        [string] $hostUrl,

        [Parameter(Position=1,Mandatory=$true)]

        [hashtable] $BOEHeaders,

        [Parameter(Position=2,Mandatory=$false,ValueFromPipeline=$true)]

        [String[]] $DocIDs 

     )

 

    if (($Input.length -ne 0) -and ($input[1] -ne $null)) {$docfilter = [System.Collections.ArrayList]  $Input}

    else {$docfilter = [System.Collections.ArrayList]  $docIDs }

 

    # get document information to enrich document information

    $documentList = BOE4_get_document_list -hostUrl $hostUrl -BOEHeaders $boeHeaders -DocIDs $DocFilter

 

     # Create document dictionary

    $documentHash = @{}

    $documentList | SELECT id, name, folderPath, folderId | forEach-object {$newObjectInfo = @{name=$_.name; folderPath=$_.folderPath; folderId = $_.folderId}; $documentHash.add($_.id,$newObjectInfo)}

 

    [System.Collections.ArrayList]$documentVariableCollection = New-Object System.Collections.ArrayList($null)

    $documentList | forEach-object {

                    $docId = $_.id 

                    $docName = $documentHash[$docId].name

                    $folderPath = $documentHash[$docId].folderPath

                    $folderId = $documentHash[$docId].folderId

                    $requestUri = "${hostUrl}/raylight/v1/documents/${docId}/variables"

                    $vars = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $boeHeaders

                    $vars.variables.variable | FOREACH-OBJECT {

                        $workId = $_.ID

                        $requestUri = "${hostUrl}/raylight/v1/documents/${docId}/variables/${workId}"

                        $var = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $boeHeaders

                        $newObjInfo= @{docId = $docId; docName = $docName; dataSourceId = $dataSourceId;

                        folderPath=$folderPath;folderId=$folderId;

                        "@datatype"=$_."@dataType"; "@qualification" = $_."@qualification"; id=$_.id; name=$_.name;formulaLanguageId=$var.variable.formulaLanguageId;definition=$var.variable.definition}

                        $documentVariableCollection.Add((New-Object PSObject -Property $newObjInfo)) | Out-Null

 

                    }

                                }

 

    $documentVariableCollection | select docId,docName,name,formulaLanguageId,'@qualification',definition,'@datatype',id,dataSourceId,folderId,folderPath

}

#get-help BOE4_get_document_variable_list -full

 

Function BOE4_get_prompt_list { 

<#

.SYNOPSIS

Get List of parameters for all or any number of documents from BOE 4 environment

 

.NOTES

AUTHOR: Boris Knizhnik

 

.PARAMETER hostUrl

link to your host - see BOE4_getConfig

 

.PARAMETER BOEHeaders

request header information to be passed - see BOE4_getConfig

 

.PARAMETER docIDs

docIDs  of the document to display providers of.

 

.OUTPUTS

Arraylist of objects with properties select docId,docName,dataproviderId,id,type,promptName,optional,answerType,answerConstrained,answerInfo,dataSourceId,dataSourceType,universeName,universeType,universeDesc,folderId,folderPath,promptTechnicalName

 

.Example

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    $dataproviderList = BOE4_get_prompt_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders -docID 12345

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

 

.Example

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $hostUrl -logonInfo $logonInfo

    $dataproviderList = 123,234 | BOE4_get_prompt_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

#>

    [CmdletBinding()]

    param(

        [Parameter(Position=0,Mandatory=$true)] 

        [string] $hostUrl,

        [Parameter(Position=1,Mandatory=$true)]

        [hashtable] $BOEHeaders, 

        [Parameter(Position=2,Mandatory=$false,ValueFromPipeline=$true)]

        [String[]] $DocIDs 

    )

 

    if (($Input.length -ne 0) -and ($input[1] -ne $null)) {$docfilter = [System.Collections.ArrayList]  $Input}

    else {$docfilter = [System.Collections.ArrayList]  $docIDs }

   

    # get document information to enrich document information

    $documentList = BOE4_get_document_list -hostUrl $hostUrl -BOEHeaders $BOEHeaders -DocIDs $DocFilter

 

     # Create document dictionary

    $documentHash = @{}

    $documentList | SELECT id, name, folderPath, folderId | forEach-object {$newObjectInfo = @{name=$_.name; folderPath=$_.folderPath; folderId = $_.folderId}; $documentHash.add($_.id,$newObjectInfo)}

 

    [System.Collections.ArrayList]$promptCollection = New-Object System.Collections.ArrayList($null)

    $documentList | forEach-Object {

                $docId = $_.id

                $nm = $_.name

                $docName = $documentHash[$docId].name

                $folderPath = $documentHash[$docId].folderPath

                $folderId = $documentHash[$docId].folderId

                $requestUri = "${hostUrl}/raylight/v1/documents/${docId}/dataproviders"

 

                $work = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $BOEHeaders

                $dataproviders = ($work.dataproviders).dataprovider

                $dataproviders | forEach-object {

                          $dataSourceId = $_.dataSourceId

                          $dataProviderId = $_.id

                          $dataSourceType=$_.dataSourceType

                          $updated=$_.updated;

                          $requestUri = "${hostUrl}/raylight/v1/documents/${docId}/dataproviders/${dataproviderID}"

                          $dp = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $boeHeaders

                          $dps = $dp.dataprovider

                          $requestUri ="${hostUrl}/infostore/${dataSourceId}"

                          $univ = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $boeHeaders

 

                          $requestUri = "${hostUrl}/raylight/v1/documents/${docId}/dataproviders/${dataproviderID}/parameters"

                          $res = Invoke-RestMethod -Method GET -Uri $requestUri -Headers $boeHeaders

                          $parms = $res.parameters.parameter

                          $parms | forEach-Object {

                                      $newObjInfo= @{docId = $docId; docName = $docName; dataproviderId = $dataProviderId; dataSourceId = $dataSourceId;

                                                    dataSourceType=$dataSourceType;

                                                    optional = $_.'@optional';type=$_.'@type'; id=$_.id; promptTechnicalName=$_.technicalName;

                                                    promptName=$_.name; answerConstrained=$_.answer.'@constrained'; answerType = $_.answer.'@type'; answerInfo=$_.answer.info

                                                    folderPath=$folderPath;folderId=$folderId;

                                                    universeName=$univ.name; universeDesc=$univ.description; universeType=$univ.type

                                                    }

                                      $promptCollection.Add((New-Object PSObject -Property $newObjInfo)) | Out-Null

                          }

 

                                                }    

                             }                                          

      $promptCollection | select docId,docName,dataproviderId,id,type,promptName,optional,answerType,answerConstrained,answerInfo,dataSourceId,dataSourceType,universeName,universeType,universeDesc,folderId,folderPath,promptTechnicalName

     

}

 

 

Function BOE4_document_webi {

<#

.SYNOPSIS

Build an Excel file documenting Webi reports from BOE 4 environment

 

.NOTES

AUTHOR: Boris Knizhnik

 

.PARAMETER hostUrl

link to your host - see BOE4_getConfig

 

.PARAMETER BOEHeaders

request header information to be passed - see BOE4_getConfig

 

.PARAMETER xlsxFile

full file name of xlsx file to be created.  All directories in its path must exist

 

.PARAMETER DocIDs

one or several comma delimited docIDs.  Instead of this parameter one may pipe the list of DocIDs

 

.Example

 

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    BOE4_document_webi -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders -xlsxFile "c:\temp\mydocum.xlsx"

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

 

.Example

 

    $logonInfo = BOE4_getconfig -fileName "c:\Users\$env:username\Bo4.properties" -configItem "myBOE"

    $boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo

    BOE4_document_webi -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders -DocID 1234

    BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders

 

 

 

#>

    [CmdletBinding()]

    param(

        [Parameter(Position=0,Mandatory=$true)] 

        [string] $hostUrl,

        [Parameter(Position=1,Mandatory=$true)]

        [hashtable] $BOEHeaders,

        [Parameter(Position=2,Mandatory=$true)]

        [string] $xlsxFile,

        [Parameter(Position=3,Mandatory=$false,ValueFromPipeline=$true)]

        [String[]] $DocIDs 

    )

 

    if (($Input.length -ne 0) -and ($input[1] -ne $null)) {$docfilter = [System.Collections.ArrayList]  $Input

    } else {$docfilter = [System.Collections.ArrayList]  $docIDs }

 

    #   list of documents

    $documentList = $docfilter | BOE4_get_document_list -hostUrl $hostUrl -BOEHeaders $boeRestfulHeaders

    $documentList | Export-csv -Path c:\temp\tmpDocuments.csv -NoTypeInformation

 

    #   list of reports for these documents

    $reportList =  $docfilter | BOE4_get_report_list -hostUrl $hostUrl -BOEHeaders $boeRestfulHeaders

    $reportList | Export-csv -Path c:\temp\tmpReports.csv -NoTypeInformation

 

    # Get a list of dataproviders for these documents

    $dataproviderlist = $docfilter | BOE4_get_dataprovider_list -hostUrl $hostUrl -BOEHeaders $boeRestfulHeaders

    $dataproviderlist | Export-csv -Path c:\temp\tmpDataproviders.csv -NoTypeInformation

 

 

    # Get list of columns for dataproviders for these documents

    $dataproviderColumnList = $docfilter | BOE4_get_dataprovider_column_list -hostUrl $hostUrl -BOEHeaders $boeRestfulHeaders

    $dataproviderColumnList | Export-csv -Path c:\temp\tmpDataproviderColumns.csv -NoTypeInformation

 

    # Get list of variables for these documents

    $documentVariableList = $docfilter |BOE4_get_document_variable_list -hostUrl $hostUrl -BOEHeaders $boeRestfulHeaders

    $documentVariableList | Export-csv -Path c:\temp\tmpDocumentVariables.csv -NoTypeInformation

 

    # Get list of prompts for these documents

    $documentPromptList = $docfilter |BOE4_get_prompt_list -hostUrl $hostUrl -BOEHeaders $boeRestfulHeaders

    $documentPromptList | Export-csv -Path c:\temp\tmpDocumentPrompts.csv -NoTypeInformation

 

 

    $excel = New-Object -ComObject Excel.Application

    $excel.Visible = $false

    $excel.DisplayAlerts = $False

    $workbook = $excel.Workbooks.Add()

    $workbook.Worksheets.Item(1).activate()

    $workbook.Worksheets.Add() | out-null

    $workbook.Worksheets.Add() | out-null

    $workbook.Worksheets.Add() | out-null

 

 

    $workbook.Worksheets.Item(1).name = "documents"

    $workbook.Worksheets.Item(2).name = "reports"

    $workbook.Worksheets.Item(3).name = "dataproviders"

    $workbook.Worksheets.Item(4).name = "columns"

    $workbook.Worksheets.Item(5).name = "variables"

    $workbook.Worksheets.Item(6).name = "prompts"

 

 

    #import CSV for documents

    $sheet = $workbook.Worksheets.Item("documents")

    $sheet.activate() | out-null

    $myf = Import-Csv "c:\temp\tmpDocuments.csv"

    $myf | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | clip

    $sheet.cells.item(1,1).select() | out-null

    $sheet.paste()

    $sheet.range("A2").select() | out-null

    $sheet.range("A1:L1").Interior.ColorIndex = 15

    $excel.ActiveWindow.FreezePanes = $True

    $sheet.UsedRange.entireColumn.Autofit()| out-null

 

 

    #import CSV for reports

    $sheet = $workbook.Worksheets.Item("reports")

    $sheet.activate() | out-null

    $myf = Import-Csv "c:\temp\tmpreports.csv"

    $myf | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | clip

    $sheet.cells.item(1,1).select() | out-null

    $sheet.paste()

    $sheet.range("A2").select() | out-null

    $sheet.range("A1:H1").Interior.ColorIndex = 15

    $excel.ActiveWindow.FreezePanes = $True

    $sheet.UsedRange.entireColumn.Autofit() | out-null

 

    #import CSV for dataproviders

    $sheet = $workbook.Worksheets.Item("dataproviders")

    $sheet.activate() | out-null

    $myf = Import-Csv "c:\temp\tmpdataproviders.csv"

    $myf | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | clip

    $sheet.cells.item(1,1).select() | out-null

    $sheet.paste()

    $sheet.range("A2").select() | out-null

    $sheet.range("A1:S1").Interior.ColorIndex = 15

    $excel.ActiveWindow.FreezePanes = $True

    $sheet.UsedRange.entireColumn.Autofit() | out-null

 

    $sheet = $workbook.Worksheets.Item("columns")

    $sheet.activate() | out-null

    $myf = Import-Csv "c:\temp\tmpDataProviderColumns.csv"

    $myf | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | clip

    $sheet.cells.item(1,1).select() | out-null

    $sheet.paste()

    $sheet.range("A2").select() | out-null

    $sheet.range("A1:O1").Interior.ColorIndex = 15

    $excel.ActiveWindow.FreezePanes = $True

    $sheet.UsedRange.entireColumn.Autofit() | out-null

 

    $sheet = $workbook.Worksheets.Item("variables")

    $sheet.activate()

    $myf = Import-Csv "c:\temp\tmpDocumentVariables.csv"

    $myf | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | clip

    $sheet.cells.item(1,1).select() | out-null

    $sheet.paste()

    $sheet.range("A2").select() | out-null

    $sheet.range("A1:K1").Interior.ColorIndex = 15

    $excel.ActiveWindow.FreezePanes = $True

    $sheet.UsedRange.entireColumn.Autofit() | out-null

 

    $sheet = $workbook.Worksheets.Item("prompts")

    $sheet.activate()

    $myf = Import-Csv "c:\temp\tmpDocumentPrompts.csv"

    $myf | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | clip

    $sheet.cells.item(1,1).select() | out-null

    $sheet.paste()

    $sheet.range("A2").select() | out-null

    $sheet.range("A1:R1").Interior.ColorIndex = 15

    $excel.ActiveWindow.FreezePanes = $True

    $sheet.UsedRange.entireColumn.Autofit() | out-null

 

    # save the document

    $sheet = $workbook.Worksheets.Item("documents")

    $sheet.activate()

    $excel.ActiveWorkbook.SaveAs($xlsxFile,51)  # 51-xlsx; 52-xlsm; 50-xlsb; 56-xls

    # quit Excel nicely

    $excel.Workbooks.Close()

    $excel.Quit()

 

    $res = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet)

    $res = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook)

    $res = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)

    $res = [System.GC]::Collect()

    $res = [System.GC]::WaitForPendingFinalizers()

 

    Remove-Variable -Name excel

 

    # remove csv files

    # Remove-Item (c:\temp\tmpDocuments.csv")

    # Remove-Item (c:\temp\tmpDataproviderColumns.csv")

    # Remove-Item (c:\temp\tmpDataproviders.csv")

    # Remove-Item (c:\temp\tmpDocumentVariables.csv")

    # Remove-Item (c:\temp\tmpReports.csv")

 

 

}

#get-help BOE4_document_webi -full

Figure 12
The complete code the of RESTFUL_webi.ps1 file

Using Powershell for RESTful Web Services to explore and document Web Intelligence reports makes a lot more sense than using either .net or Java. I didn’t spend much time optimizing the performance of my code, but, for documentation purposes, performance is not really a factor. However, if you compare the examples in this article with the corresponding examples done in Java or .net, and the coding it requires to parse the results, it is obvious that Powershell code makes it a lot easier to get started and get the job done.

Each Powershell function inside RESTful_Web Intelligence.ps1 file looks a lot bigger than it is. I tried to make these functions self-documented with several examples of their usage. The actual meat inside is usually a dozen or so lines, and even these lines are mostly to add additional information to the output to make it self-sufficient.

An email has been sent to:





 

Boris Knizhnik

Boris Knizhnik was born in the former Soviet Union and has lived in U.S. since 1989. He has over 35 years of IT experience, using different technologies, from Cobol and CA-IDMS to Java and Oracle. He is a Vice President of BIK Information Services, Inc., which provides Business Intelligence solutions.



COMMENTS

Please log in to post a comment.

No comments have been submitted on this article. Be the first to comment!


SAPinsider
FAQ