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.
Key Concept
Powershell is a free tool that comes as part of the standard Microsoft Windows package. Its latest version has very good integration with the Representational State Transfer (RESTful) Web Services application programming interface (API), which makes it much easier to use than Java for Raylight RESTful Services in SAP. Its rich variety of data types and dot notation allow for easy exploration of complex internal structures of Web Intelligence documents.
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.
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:
- 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).
- 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.)
- 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 https://something/documents. To get a specific document 123, you call https://something/documents/123. To get all the reports within document 123, you call https://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=https://servername:6405/biprws
myBOE:userName=myid
myBOE:password=mypassword
myBOE:auth=secEnterprise
anotherBOE:URL=https://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:usernameRESTful_webi.ps1
$logonInfo = BOE4_getconfig -fileName "c:Users$env:usernameBo4.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:usernameRESTful_webi.ps1
$logonInfo = BOE4_getconfig -fileName "c:Users$env:usernameBo4.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:usernameRESTful_webi.ps1
$logonInfo = BOE4_getconfig -fileName "c:Users$env:usernameBo4.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:usernameRESTful_webi.ps1
$logonInfo = BOE4_getconfig -fileName "c:Users$env:usernameBo4.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:tempWebi_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=https://servername:6405/biprws
myBOE:userName=myid
myBOE:password=mypassword
myBOE:auth=secEnterprise
anotherBOE:URL=https://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:usernameBo4.properties" -configItem "myBOE"
#>
[CmdletBinding()]
param(
[Parameter(Position=0,Mandatory=$true)]
[string] $fileName, # c:UsersyournameBO4.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:usernameBo4.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:usernameBo4.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:usernameBo4.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:usernameBo4.properties" -configItem "myBOE"
$boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo
$folderList = BOE4_get_folder_list -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders -folderID 123 -fullPath 'PublicCompanyFolder'
BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders
#>
[CmdletBinding()]
param(
[Parameter(Position=0,Mandatory=$true)]
[string] $hostUrl, # https://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:usernameBo4.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:usernameBo4.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:usernameBo4.properties" -configItem "myBOE"
$hostUrl=$logonInfo.URL
$boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo
$documentList = (Get-Content "c:templist_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:usernameBo4.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:usernameBo4.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:usernameBo4.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:usernameBo4.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:usernameBo4.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:usernameBo4.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:usernameBo4.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:usernameBo4.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:usernameBo4.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:usernameBo4.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:usernameBo4.properties" -configItem "myBOE"
$boeRestfulHeaders = BOE4_logon -hostUrl $logonInfo.URL -logonInfo $logonInfo
BOE4_document_webi -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders -xlsxFile "c:tempmydocum.xlsx"
BOE4_logoff -hostUrl $logonInfo.URL -BOEHeaders $boeRestfulHeaders
.Example
$logonInfo = BOE4_getconfig -fileName "c:Users$env:usernameBo4.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:temptmpDocuments.csv -NoTypeInformation
# list of reports for these documents
$reportList = $docfilter | BOE4_get_report_list -hostUrl $hostUrl -BOEHeaders $boeRestfulHeaders
$reportList | Export-csv -Path c:temptmpReports.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:temptmpDataproviders.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:temptmpDataproviderColumns.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:temptmpDocumentVariables.csv -NoTypeInformation
# Get list of prompts for these documents
$documentPromptList = $docfilter |BOE4_get_prompt_list -hostUrl $hostUrl -BOEHeaders $boeRestfulHeaders
$documentPromptList | Export-csv -Path c:temptmpDocumentPrompts.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:temptmpDocuments.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:temptmpreports.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:temptmpdataproviders.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:temptmpDataProviderColumns.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:temptmpDocumentVariables.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:temptmpDocumentPrompts.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:temptmpDocuments.csv")
# Remove-Item (c:temptmpDataproviderColumns.csv")
# Remove-Item (c:temptmpDataproviders.csv")
# Remove-Item (c:temptmpDocumentVariables.csv")
# Remove-Item (c:temptmpReports.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.
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.
You may contact the author at
borisk@bikinfo.com.
If you have comments about this article or publication, or would like to submit an article idea, please contact the
editor.
