OAC - PowerShell RestAPI to Upload files from On-Prem to OAC

Today, we are going to see a function which uploads files / data from your On-Prem Server to OAC.

Read my prior blog so that you get the basics on authenticating to OAC and getting the Object handle to invoke the rest commands.

Pre-requisites:

- Create a folder on your local server - D:\Up
- Create a file "MyUploadFile.txt"  within the folder created above. You can create any number of files. 
- Ensure you are running the PowerShell version 4 or above.

Without delay, I'll straight away introduce the code (which has explicit comments on each step to understand it better).

PowerShell Code:

Function UploadingFiles {  

#Parameters passed to the above function
param( [string] $dirToUpload, [string] $upURL )

# This Steps constructs your Rest API Base URL
$baseURL = "http://<your OAC URL>/essbase/rest/v1/" 

#Logging in to OAC - Replace the $username and $password with your specifics.
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 
$EncodedAuthorization = [System.Text.Encoding]::UTF8.GetBytes("<username>" + ":" + "<password>") 
$EncodedPassword = [System.Convert]::ToBase64String($EncodedAuthorization) 
$headers = @{"Authorization"="Basic $($EncodedPassword)"} 

# Construct your upload URL
$uploadURL = $baseURL + $upURL

# Files in the folder which needs to be uploaded
$filesTobeUploaded = Get-ChildItem $dirToUpload 

# Loop through the folder and upload the files to OAC
ForEach($file in $filesTobeUploaded) { 
     $restURL = $uploadURL + $file 
     $restResponse = Invoke-RestMethod -Uri $restURL -Method Put -Header $headers -ContentType "application/octet-stream" -OutFile "$file" 
} 
} 

Use the below command to call the function written above

#Function Call
UploadingFiles -dirToUpload "D:\Up\" -upURL "files/shared/" 

Please try this out and provide your feedback on the comments section.

OAC Utility for RestAPI and PowerShell

I've been playing around with RestAPI's through PowerShell, creating various functions for Uploading, Downloading, Listing, Copying, Moving and Deleting files from an OAC folder.

I'm giving you a sneak peek into one of the function, which can help enhance your automation.

Here below is a snippet of code which helps you copy files from one location to another within OAC using PowerShell.

The below code has been Tested and its working fine.

The Invoke-RestMethod Powershell command does the work of connecting to OAC and performing the requested operation.

Step 1: Logging in to OAC - Replace the $username and $password with your specifics.

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$EncodedAuthorization = [System.Text.Encoding]::UTF8.GetBytes($username + ":" + $password)
$EncodedPassword = [System.Convert]::ToBase64String($EncodedAuthorization)
$headers = @{"Authorization"="Basic $($EncodedPassword)"}

Step 2: Data Copy happens here!

# Data Copy on OAC using Powershell - Begin
$restURL = "https://<OAC DOMAIN ID>/essbase/rest/v1/files/actions/copy?overwrite=true"

# Creating an Empty object
$body = @{}

# Adding the destination where the file should land
# "$ToPath" - Replace this with your specific path

$body.add("to","$ToPath")

#Source Location from which the file has to be copied
# "$FromPath" - Replace this with your specific path
$body.add("from","$FromPath")

# Converting the object to JSON format
$body = $body | ConvertTo-Json

#Copy it now!
$restResponse = Invoke-RestMethod -Uri $restURL -Method Post -Header $headers -ContentType "application/json" -Body $body

# Data Copy on OAC using Powershell - End

Code:

Throw the below Code Snippet into a file and Save it as a PowerShell (.ps1) application

CopyUtility.ps1

# Login to OAC
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$EncodedAuthorization = [System.Text.Encoding]::UTF8.GetBytes($username + ":" + $password)
$EncodedPassword = [System.Convert]::ToBase64String($EncodedAuthorization)
$headers = @{"Authorization"="Basic $($EncodedPassword)"}

# Data Copy on OAC using Powershell - Begin
$restURL = "https://<OAC DOMAIN ID>/essbase/rest/v1/files/actions/copy?overwrite=true"
$body = @{}
$body.add("to","$ToPath")
$body.add("from","$FromPath")
$body = $body | ConvertTo-Json
$restResponse = Invoke-RestMethod -Uri $restURL -Method Post -Header $headers -ContentType "application/json" -Body $body
# Data Copy on OAC using Powershell - End

Step 3: That's it!!!

See you soon with my next post! 

Hyperion Planning - DB issue

PLANNING APPLICATION CREATION - VERSION 9.3.1 - REVISITED 

Today I happened to revisit on the procedure for creating a Planning application using version 9.3.1.
As you are aware of, I followed the regular text book procedure given by oracle for creating the application. Here are the steps which I followed while creating the new PLAN application called as "TESTAPP".

·         I created a blank database on the SQL Server environment called "TESTDB"

·         Then, I went ahead with the data source creation using the Hyperion Configuration utility. Refer screenshot below.

·         The data source will be pointing TESTDB database (as mentioned in the configuration utility screenshot above).

·         I reused an existing ID on the DB called as "TESTUSR" after provisioning the ID with the db_owner privilege

               
Once I happened to complete the initial setup, I went ahead to create the application using the regular planning AppWizard URL. I went ahead with  the default options / selections as I was refreshing my memory on the application creation procedure (in order to help one of my friend). The moment I hit the "FINISH" button, I encountered an Error saying that “THE SQL OPERATION FAILED. CHECK LOG FOR DETAILS".


I started troubleshooting this issue and checked the SystemErr.log and SystemOut.Log. All that I could find is the snippet below at the end.


---- Begin backtrace for Nested Throwables
java.sql.SQLException: [Hyperion][SQLServer JDBC Driver][SQLServer]The statement has been terminated.

at hyperion.jdbc.base.BaseExceptions.createException(Unknown Source)
at hyperion.jdbc.base.BaseExceptions.getException(Unknown Source)
at hyperion.jdbc.sqlserver.tds.TDSRequest.processInfoToken(Unknown Source)
at hyperion.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)


The interesting thing (or the so called "WEIRD" thing) that happened as an additional impact of this is application creation is that it deleted all the records on another database called "SAMPLE" on the same SQL Server environment, which I created earlier for whole different purpose (an OLD planning application).
Interesting, huh................
My brain lightened up on finding a solution for this interesting issue and forced me to quit all the other work which I was supposed to do on the later part of the day.

 I started analyzing from the DB point of view, since the planning was throwing an error that it could not perform the SQL operation. I double checked  the privileges  and everything looked appropriate while I noticed an interesting thing which I assumed could be the root cause for this issue.

The SQL Server ID which I used for creating the planning application (data source) is defaulting to the database called "SAMPLE" which got erased in the process of application creation, which I assumed to be the root cause of the error. 

 

Since the application creation failed on the TESTDB database due to various unidentified reason (still under research), the DROP and CREATE statements (which is internally issued by the Planning application creation wizard) got executed on the default database (SAMPLE) assigned to the user id TESTUSR.

I'm not convinced till now on whether my assumption is right or wrong. I opened a discussion thread on the ORACLE FORUM in parallel to my on-going analysis. Eagerly looking forward on whether my brain is going to identify and prove my assumption right it before a Hyperion Expert says the other way around... Keeping my fingers crossed...:)

By the by, Lesson Learnt / Resolution to avoid this in future:
Creating an independent DB User ID and assigning it as an OWNER to the DB created (for planning application). In other words, the application needs to be created with a separate USER ID that references only one database at a time.
I've tried creating an application using the recommended approach and everything went fine..... Still in search of a solution for the issue I faced.

Please provide your valuable feedback or suggestion... Thanks....