Tag Archives: Oracle

Timestamp Functions and Presentation Variables in Oracle Cloud Analytics

One of the most popular Rittman Mead blog posts over the last 10 years is Timestamps and Presentation Variables. As we are seeing more and more migrations to OAC, we decided to review and revise this post for the latest version of Oracle Cloud Analytics (OAC), 105.4.0-140 as of October 2019. Read more about the latest updates here.

--

One could say that creating a chart is not the most complex task in the world of Business Intelligence but we would argue that creating a meaningful report that perfectly illustrates the message hidden in data and therefore adds value to the management is nowhere close to being easy!    A good way to make a report as informative as possible is to use trends and comparison. And to do so, a perfect tool would be the time analysis functions. For example comparing sales in a period of time this year to the same period of time the year before. Or measure the similarity or dissimilarity of sales in different months of the year.

Demo Platform

I have used a free trial instance of OAC for this demo. If you haven’t done yet, sign up for a free 30-day trial Oracle Cloud account (different to an Oracle account). Use the account to access the Oracle Cloud Infrastructure (OCI) console which is the latest Oracle movement towards having one integrated cloud platform to manage all your Oracle cloud applications, platforms, and infrastructure in one place.
From the OCI console it is 5 to 10 minutes before your free trial instance of OAC is up and running. For the detailed step by step of creating a new instance read here.

Demo Goals

In this blog post I intend to show you how to combine the power of timestamp functions and presentation variables to create robust, repeatable reports. We will create a report that displays a year over year analysis for any rolling number of periods, by week or month, from any date in time, all determined by the user. This entire demo will only use values from a date and a revenue field.


TIMESTAMP Functions

TIMESTAMPADD() manipulates data of the data types DATE and DATETIME based on a calendar year.

Syntax: TIMESTAMPADD(interval, expr, timestamp)
Example: TIMESTAMPADD(SQL_TSI_MONTH, 12,Time."Order Date")
Description: Adds a specified number of intervals to a timestamp, and returns a single timestamp.
Timestamp Interval (TSI) Options: SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR

Read more about other calendar functions.

Building Filters

Starting to build our demo, the filter below returns all dates greater than or equal to 7 days ago including the current date.

In other words we have now a functional filter to select all the rows where Date >= a week ago.

As a good practice, always include a second filter giving an upper limit to the time filter. For example "Periods"."Day Date" < CURRENT_DATE would confirm that there won’t be any records that you don’t want in the mix and therefore no unnecessary strain on the system.

Let’s go one step further, instead of going 7 days back, we could try and include all the previous days in the current month or in other words dates >= the first day of the month. In this scenario, we can use the DAYOFMONTH() function to get the calendar day of any date. From here it will be easy to calculate the number of days in the month so far. Our new filter would look like this:

For example, if today is October 16th, DAYOFMONTH(CURRENT_DATE) would equal 16. Thus, we would subtract 16 days from CURRENT_DATE to go back to September 30th, and adding one will give us October 1st.

Presentation Variables

A presentation variable is a variable that can be created from the front end, the Analytics as part of one of the following types of dashboard prompts:

  • Column prompt, Associated with a column and the values that it can take come from the column values. For information on working with column prompts, see Creating a Column Prompt.
  • Variable prompt, Not associated with any column, and you define the values that it can take. For information on working with variable prompts, see Creating a Variable Prompt.

Each time a user selects a value in the column or variable prompt, the value of the presentation variable is set to the value that the user selects and will then be sent to any references of that filter throughout the dashboard page. This could be filters, formulas and even text boxes.

The first presentation variable we could introduce is to replace the CURRENT_DATE with a prompted value. Let’s call this presentation variable pv_Date,

  • Use the syntax @{pv_Date} to call this variable in the reports.
  • For variables of type string, surround the name in single quotes: ‘@{pv_String]’
  • It is good practice to assign a default value to the presentation variables so that you can work with your report before publishing it to a dashboard. For example the default value for the pv_Date is CURRENT_DATE so the new syntax would be @{pv_Date}{CURRENT_DATE}

Demo Time!

Our updated filter after replacing the CURRENT_DATE looks like below. Will will refer to this filter later as Filter 1 (F1).

The filter is starting to take shape. Now let's say we are going to always be looking at a date range of six months before the selected date. All we would need to do is create a nested TIMESTAMP function. To do this, we will “wrap” our current TIMESTAMP with another that will subtract six months:

Now we have a filter to select dates that are greater than or equal to the first day of the month of any given date and all the six months prior to that.


To take this one step further, we can create another presentation variable called  pv_n to allow the users to determine the amount of months to include in this analysis from a dashboard prompt.


Here is the updated version of our filter using the number of periods presentation variable and a default value of 6, @{pv_n}{6}. We will refer to the following filter as Filter 2 (F2).

Our TIMESTAMPADD function is now fairly robust and will give us any date greater than or equal to the first day of the month from n months ago from any given date. Now we will see what we just created in action by creating date ranges to allow for a Year over Year analysis for any number of months. Consider the following filter set:

This may appear to be pretty intimidating at first but if we break it into parts we can start to understand its purpose. Notice we are using the exact same filters from before; Filter 1 and Filter 2.   What we have done here is filtered on two time periods, separated by the OR statement.

  • The first date range defines the period as being the most recent completed n months from any given prompted date value, using a presentation variable with a default of today. Dates in the current month have been removed from the set by Filter 1.
  • The second time period, after the OR statement, is the exact same as the first only it has been wrapped in another TIMESTAMP function subtracting a year, giving you the exact same time frame for the year prior.

This allows us to create a report that can run a year over year analysis for a rolling n month time frame determined by the user.

A note on nested TIMESTAMPS: you will always want to create nested TIMESTAMPS with the smallest interval first. Then you will wrap intervals as necessary. In this case our smallest increment is day, wrapped by month, wrapped by year.


Let’s Go Crazy

A more advanced trick, If you use real time or near real time reporting: using CURRENT_DATE may be how you want to proceed. Otherwise, instead of using today as your default date value, use yesterday’s date since most data are only as current as yesterday.  Using yesterday will be valuable especially when pulling reports on the first day of the month or year - you generally want the entire previous time period rather than the empty beginning of a new one.  So, to implement, wherever you have @{pDate}{CURRENT_DATE} replace it with @{pDate}{TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE)}

One more change on our filter to make it extra-flexible here is to use a new presentation variable to determine if you want to display year over year values, or by month, or by week. This can be done by inserting a variable into your SQL_TSI_MONTH and DAYOFMONTH statements; changing MONTH to SQL_TSI_@{pv_INT}{MONTH} and DAYOF@{pv_INT}{MONTH}, where pv_INT is the name of our variable.

Start by creating a dummy variable in your prompt to allow users to select either MONTH or WEEK.  You can try something like this: CASE MOD(DAY("Time"."Date"),2) WHEN 0 'WEEK' WHEN 1 THEN 'MONTH' END

The updated filter now look like this:

In order for our interaction between Month and Week to run smoothly we have to factor in one last consideration: if we are to take the date December 1st, 2019 and subtract one year we get December 1st, 2018.  However, if we take the first day of this week, Sunday December 15, 2019 and subtract one year we get Saturday December 15, 2014.  In our analysis this will cause an extra partial week to show up for prior years.  To get around this we will add a case statement determining if '@{pv_INT}{MONTH}' = 'Week' THEN subtract 52 weeks from the first of the week ELSE subtract 1 year from the first of the month. With this, our final filter set will look like this:


With the use of these filters and some creative dashboarding, you can construct a report that easily allows you to view a year over year analysis from any date in time for any number of periods either by month or by week.

Filtered by Week intervals;

The formula below will give you the value of period rolling to use in the analysis;

In this post, we created a cloud version of the amazing demo previously described by Brian Hall.  As demonstrated, Timestamp functions and their power have been within the interesting topics of the visualisation and reporting for as long as we at Rittman Mead remember and can still be used in the realm of the Oracle Cloud Services in a very similar way as the past.

Feel free to get in touch, let us know your reviews and comments.

ODC Appreciation Day: Oracle Cloud PSM Cli

ODC Appreciation Day: Oracle Cloud PSM Cli

Oracle Developer Community (ODC) Appreciation Day (previously know as OTN Appreciation Day) is a day, started from an initiative of Tim Hall, where everyone can share their Thanks to the Oracle community by writing about a favourite product, an experience, a story related to Oracle technology.

ODC Appreciation Day: Oracle Cloud PSM Cli

Last year I wrote about OBIEE Time Hierarchies and how they are very useful to perform time comparison, shifts, and aggregations.

This year I want to write about Oracle Paas Service Manager (PSM) Client!
I've already written a blog post about it in detail, basically Oracle PSM allows Oracle cloud administrators to manage their instances via command line instead of forcing them to use the Web-UI.

ODC Appreciation Day: Oracle Cloud PSM Cli

PSM Cli allows you to create an Oracle Analytics Cloud instance by just calling

psm analytics create-service -c <CONFIG_FILE> -of <OUTPUT_FORMAT>

and passing a JSON <CONFIG_FILE> which can easily be downloaded after following the creation process in the Web-UI, a bit like the response file in on-premises OBIEE can be saved and customised for future reuse after the first UI installation. Examples of the PSM JSON payloads can be found here.

OAC Instances can also easily be started/stopped/restarted with the command

psm analytics start/stop/restart -s <INSTANCE_NAME>

And the status of each command tracked with

psm analytics operation-status -j <JOB_ID>

As mentioned in my previous post, PSM Cli opens also the doors for instance management automation which is a requirement for providing cost-effective fully isolated feature-related OAC instances useful when thinking about DevOps practices. The fact that PSM Cli is command line, means that it can be integrated in any automation tool like Jenkins and thus integrated in any DevOps flow being designed in any company.

So Thank you, Oracle, for enabling such automation with PSM Cli!

Follow the #ThanksODC hashtag on Twitter to check which post have been published on the same theme!

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

This summer we unselfish Italians decided to not participate to the World Cup to give another country the opportunity to win (good luck with that England!). This decision, which I strongly support, gives me lot of time for blogging!
DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

As already written, two weeks ago while in Orlando for Kscope18, I presented a session about DevOps and OBIEE focusing on how to properly source control, promote and test for regression any component of the infrastructure.

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

Development Isolation

One key aspect of DevOps is providing the Development Isolation: a way of allowing multiple development streams to work independently and merging the outcome of the process into the main environment only after this has been tested and validated. This is needed to avoid the standard situation where code promotions are blocked due to different working streams not being in sync: forcing a team to postpone a code release just because another team doesn't have the UAT OK is just an example of non-isolated development platforms.

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

We have been discussing development isolation topic in the past focusing mainly on concurrent repository development and how to integrate it with versioning tools like Git and SVN. The concurrent online editing option is not viable since multiple developers are modifying the same artifact (RPD) without a way of testing for regression the changes or to verifying that what has been done is correct before merging the changes in the RPD.
Alternative solutions of using MUDE (default multi-user development method provided by the Admintool) or pure offline RPD work encounter the same problems defined above: no feature or regression testing available before merging the RPD in the main development environment.

Different RPD development techniques solve only partially the problem: almost any OAC/OBIEE development consist at least in both RPD and catalog work (creation of analysis/dashboards/VA projects) we need an approach which provides Development Isolation at both levels. The solution, in order to properly build a DevOps framework around OAC/OBIEE, it's to provide isolated feature-related full OBIEE instances where the RPD can be edited in online mode, the catalog work can be done independently, and the overall result can be tested and validated before being merged into the common development environment.

Feature-Related Instances

The feature instances, as described above, need to be full OAC/OBIEE development instances where only a feature (or a small set) is worked at the time in order to give the agility to developers to release the code as soon as it's ready and tested. In the on-premises world this can "easily" be achieved by providing a number of dedicated Virtual Machines or, more in line with the recent trends, an automated instance provisioning with Docker using a template image like the one built by our previous colleague Gianni Ceresa.

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

However, when we think about Oracle Analytics Cloud (OAC), we seem to have two problems:

  • There is a cost associated with every instance, thus minimizing the number of instances and the uptime is necessary
  • The OAC provisioning interface is point and click, thus automating the instance management seems impossible

The overall OAC instance cost can be mitigated by the Bring Your Own License (BYOL) licensing method which allows customers to migrate on-premises licenses to the cloud and have discounted prices on the hourly/monthly instance cost (more details here). However, since the target is to minimize the cost thus the # of instances and the uptime, we need to find a way to do so that doesn't rely on a human and a point and click interface. Luckily the PaaS Service Manager Command Line Interface (PSM Cli) allows us to solve this problem by providing a scriptable way of creating, starting and stopping instances.

PaaS Service Manager Command Line Interface

PSMCLI is a command line interface acting as a wrapper over the PaaS REST APIs. Its usage is not limited to OAC, the same interface can be used to create and manage instances of the Oracle's Database Cloud Service or Java Cloud Services amongst the others.
When talking about OAC please keep in mind that, as of now, PSM Cli works only with the non-autonomous version but I believe the Autonomous support will be added soon.

Installing and Configuring PSM Cli

PSMCLI has two prerequisites before it can be installed:

  • cURL - a command line utility to transfer data with URLs
  • Python 3.3 or later

Once both prerequisites are installed PSM can easily be downloaded with the following cURL call

curl -X GET -u <USER>:<PWD> -H X-ID-TENANT-NAME:<IDENTITY_DOMAIN> https://<REST_SERVER>/paas/core/api/v1.1/cli/<IDENTITY_DOMAIN>/client -o psmcli.zip

Where

  • <USER> and <PWD> are the credentials
  • <IDENTITY_DOMAIN> is the Identity Domain ID specified during the account creation
  • <REST_SERVER> is the REST API server name which is:
  • psm.us.oraclecloud.com if you are using a US datacenter
  • psm.aucom.oraclecloud.com if you are in the AuCom region
  • psm.europe.oraclecloud.com otherwise

Next step is to install PSM as a Python package with

pip3 install -U psmcli.zip

After the installation is time for configuration

psm setup

The configuration command will request the following information:

  • Oracle Cloud Username and Password
  • Identity Domain
  • Region, this need to be set to
  • emea if the REST_SERVER mentioned above contains emea
  • aucom if the REST_SERVER mentioned above contains aucom
  • us otherwise
  • Output format: the choice is between short, json and html
  • OAuth: the communication between the CLI and the REST API can use basic authentication (flag n) or OAuth (flag y). If OAuth is chosen then ClientID, Secret and Access Token need to be specified

A JSON profile file can also be used to provide the same information mentioned above. The structure of the file is the following

{ 
    "username":"<USER>",
    "password":"<PASSWORD>",
    "identityDomain":"<IDENTITY_DOMAIN>",
    "region":"<REGION>",
    "outputFormat":"<OUTPUT_FORMAT>",
    "oAuth":{ 
        "clientId":"",
        "clientSecret":"",
        "accessTokenServer":""
    }
}

If the profile is stored in a file profile.json the PSM configuration can be achieved by just executing

psm setup -c profile.json

One quick note: the identity domain Id, shown in the Oracle Cloud header, isn't working if it's not the original name (name at the time of the creation).

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

In order to get the correct identity domain Id to use, check in an Oracle Cloud instance already created (e.g. a database one) and check the Details, you'll see the original identity domain listed there (credits to Pieter Van Puymbroeck).

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

Working With PSM Cli

Once the PSM has been correctly configured it's time to start checking what options are available, for a detailed list of the options check PSM documentation.

The PSM commands are product related, so each command is in the form:

psm <product> <command> <parameters>

Where

  • product is the Oracle cloud product e.g. dbcs, analytics, BigDataAppliance, for a complete list use psm help
  • command is the action to be executed against the product e.g. services, stop, start, create-service
  • parameters is the list of parameters to pass depending on the command executed

The first step is to check what instances I already created, I can do so for the database by executing

psm dbcs services

which, as expected, will list all my active instances

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

I can then start and stop it using:

psm dbcs start/stop/restart -s <INSTANCE_NAME>

Which in my example provides the Id of the Job assigned to the stop operation.

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

When I check the status via the service command I get Maintenance like in the web UI.

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

The same applies to the start and restart operation. Please keep in mind that all the calls are asynchronous -> the command will call the related REST API and then return the associated Job ID without waiting for the command to be finished. The status of a job can be checked with:

psm dbcs operation-status -j <JOB_ID>

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

The same operations described above are available on OAC with the same commands by simply changing the product from dbcs to analytics like:

psm analytics start/stop/restart -s <INSTANCE_NAME>

On top of the basic operation, PSM Cli allows also the following:

  • Service Instance: start/stop/restart, instance creation-deletion
  • Access Control: lists, creates, deletes, enables and disables access rules for a service.
  • Scaling: changes the computer shape of an instance and allows scaling up/down.
  • Storage: extends the storage associated to OAC
  • Backup Configuration: updates/shows the backup configurations
  • Backups: lists, creates, deletes backups of the instance
  • Restore: restores a backup giving detailed information about it and the history of Restores
  • Patches: allows patching, rollbacking, doing pre-checks, and retrieving patching history

Creating an OAC Instance

So far we discussed the maintenance on already created instances with start/stop/restart commands, but PSM Cli allows also the creation of an instance via command line. The call is pretty simple:

psm analytics create-service -c <CONFIG_FILE> -of <OUTPUT_FORMAT>

Where

  • CONFIG_FILE: is the file defining all OAC instance configurations
  • OUTPUT_FORMAT: is the desired output format between short, json and html

The question now is:

How do I create a Config File for OAC?

The documentation doesn't provide any help on this, but we can use the same approach as for on-premises OBIEE and response file: create the first instance with the Web-UI, save the payload for future use and change parameters when necessary.

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

On the Confirm screen, there is the option to Download the REST payload in JSON format

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

With the resulting json Config File being

{
  "edition": "<EDITION>",
  "vmPublicKeyText": "<SSH_TOKEN>",
  "enableNotification": "true",
  "notificationEmail": "<EMAIL>",
  "serviceVersion": "<VERSION>",
  "isBYOL": "false",
  "components": {
    "BI": {
      "adminUserPassword": "<ADMINPWD>",
      "adminUserName": "<ADMINUSER>",
      "analyticsStoragePassword": "<PWD>",
      "shape": "oc3",
      "createAnalyticsStorageContainer": "true",
      "profile_essbase": "false",
      "dbcsPassword": "<DBCSPWD>",
      "totalAnalyticsStorage": "280.0",
      "profile_bi": "true",
      "profile_dv_forced": "true",
      "analyticsStorageUser": "<EMAIL>",
      "dbcsUserName": "<DBUSER>",
      "dbcsPDBName": "<PDBNAME>",
      "dbcsName": "<DBCSNAME>",
      "idcs_enabled": "false",
      "analyticsStorageContainerURL": "<STORAGEURL>",
      "publicStorageEnabled": "false",
      "usableAnalyticsStorage": "180"
    }
  },
  "serviceLevel": "PAAS",
  "meteringFrequency": "HOURLY",
  "subscriptionId": "<SUBSCRIPTIONID>",
  "serviceName": "<SERVICENAME>"
}

This file can be stored and the parameters changed as necessary to create new OAC instances with the command:

psm analytics create-service -c <JSON_PAYLOAD_FILE> -of short/json/html

As shown previously, the result of the call is a Job Id that can be monitored with

psm analytics operation-status -j <JOB_ID>

Once the Job is finished successfully, the OAC instance is ready to be used. If at a certain point, the OAC instance is not needed anymore, it can be deleted via:

psm analytics delete-service -s <SERVICE_NAME> -n <DBA_NAME> -p <DBA_PWD>

Where

  • SERVICE_NAME is the OAC instance name
  • DBA_NAME and DBA_PWD are the DBA credentials where OAC schemas are residing

Summary

Worried about providing development isolation in OAC while keeping the costs down? Not anymore! With PSM Cli you now have a way of creating instances on demand, start/stop, up/down scaling with a command line tool easily integrable with automation tools like Jenkins.

Create an OAC instances automatically only when features need to be developed or tested, stop&start the instances based on your workforce timetables, take the benefit of the cloud minimizing the cost associated to it by using PSM Cli!

One last note; for a full DevOps OAC implementation, PSM Cli is not sufficient: tasks like automated regression testing, code versioning, and promotion can't be managed directly with PSM Cli but require usage of external toolsets like Rittman Mead BI Developer Toolkit. If you are interested in a full DevOps implementation on OAC and understanding the details on how PSM Cli can be used in conjunction with Rittman Mead BI Developer Toolkit don't hesitate to contact us!

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

This summer we unselfish Italians decided to not participate to the World Cup to give another country the opportunity to win (good luck with that England!). This decision, which I strongly support, gives me lot of time for blogging!
DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

As already written, two weeks ago while in Orlando for Kscope18, I presented a session about DevOps and OBIEE focusing on how to properly source control, promote and test for regression any component of the infrastructure.

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

Development Isolation

One key aspect of DevOps is providing the Development Isolation: a way of allowing multiple development streams to work independently and merging the outcome of the process into the main environment only after this has been tested and validated. This is needed to avoid the standard situation where code promotions are blocked due to different working streams not being in sync: forcing a team to postpone a code release just because another team doesn't have the UAT OK is just an example of non-isolated development platforms.

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

We have been discussing development isolation topic in the past focusing mainly on concurrent repository development and how to integrate it with versioning tools like Git and SVN. The concurrent online editing option is not viable since multiple developers are modifying the same artifact (RPD) without a way of testing for regression the changes or to verifying that what has been done is correct before merging the changes in the RPD.
Alternative solutions of using MUDE (default multi-user development method provided by the Admintool) or pure offline RPD work encounter the same problems defined above: no feature or regression testing available before merging the RPD in the main development environment.

Different RPD development techniques solve only partially the problem: almost any OAC/OBIEE development consist at least in both RPD and catalog work (creation of analysis/dashboards/VA projects) we need an approach which provides Development Isolation at both levels. The solution, in order to properly build a DevOps framework around OAC/OBIEE, it's to provide isolated feature-related full OBIEE instances where the RPD can be edited in online mode, the catalog work can be done independently, and the overall result can be tested and validated before being merged into the common development environment.

Feature-Related Instances

The feature instances, as described above, need to be full OAC/OBIEE development instances where only a feature (or a small set) is worked at the time in order to give the agility to developers to release the code as soon as it's ready and tested. In the on-premises world this can "easily" be achieved by providing a number of dedicated Virtual Machines or, more in line with the recent trends, an automated instance provisioning with Docker using a template image like the one built by our previous colleague Gianni Ceresa.

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

However, when we think about Oracle Analytics Cloud (OAC), we seem to have two problems:

  • There is a cost associated with every instance, thus minimizing the number of instances and the uptime is necessary
  • The OAC provisioning interface is point and click, thus automating the instance management seems impossible

The overall OAC instance cost can be mitigated by the Bring Your Own License (BYOL) licensing method which allows customers to migrate on-premises licenses to the cloud and have discounted prices on the hourly/monthly instance cost (more details here). However, since the target is to minimize the cost thus the # of instances and the uptime, we need to find a way to do so that doesn't rely on a human and a point and click interface. Luckily the PaaS Service Manager Command Line Interface (PSM Cli) allows us to solve this problem by providing a scriptable way of creating, starting and stopping instances.

PaaS Service Manager Command Line Interface

PSMCLI is a command line interface acting as a wrapper over the PaaS REST APIs. Its usage is not limited to OAC, the same interface can be used to create and manage instances of the Oracle's Database Cloud Service or Java Cloud Services amongst the others.
When talking about OAC please keep in mind that, as of now, PSM Cli works only with the non-autonomous version but I believe the Autonomous support will be added soon.

Installing and Configuring PSM Cli

PSMCLI has two prerequisites before it can be installed:

  • cURL - a command line utility to transfer data with URLs
  • Python 3.3 or later

Once both prerequisites are installed PSM can easily be downloaded with the following cURL call

curl -X GET -u <USER>:<PWD> -H X-ID-TENANT-NAME:<IDENTITY_DOMAIN> https://<REST_SERVER>/paas/core/api/v1.1/cli/<IDENTITY_DOMAIN>/client -o psmcli.zip

Where

  • <USER> and <PWD> are the credentials
  • <IDENTITY_DOMAIN> is the Identity Domain ID specified during the account creation
  • <REST_SERVER> is the REST API server name which is:
    • psm.us.oraclecloud.com if you are using a US datacenter
    • psm.aucom.oraclecloud.com if you are in the AuCom region
    • psm.europe.oraclecloud.com otherwise

Next step is to install PSM as a Python package with

pip3 install -U psmcli.zip  

After the installation is time for configuration

psm setup  

The configuration command will request the following information:

  • Oracle Cloud Username and Password
  • Identity Domain
  • Region, this need to be set to
    • emea if the RESTSERVER mentioned above contains emea
    • aucom if the RESTSERVER mentioned above contains aucom
    • us otherwise
  • Output format: the choice is between short, json and html
  • OAuth: the communication between the CLI and the REST API can use basic authentication (flag n) or OAuth (flag y). If OAuth is chosen then ClientID, Secret and Access Token need to be specified

A JSON profile file can also be used to provide the same information mentioned above. The structure of the file is the following

{ 
    "username":"<USER>",
    "password":"<PASSWORD>",
    "identityDomain":"<IDENTITY_DOMAIN>",
    "region":"<REGION>",
    "outputFormat":"<OUTPUT_FORMAT>",
    "oAuth":{ 
        "clientId":"",
        "clientSecret":"",
        "accessTokenServer":""
    }
}

If the profile is stored in a file profile.json the PSM configuration can be achieved by just executing

psm setup -c profile.json  

One quick note: the identity domain Id, shown in the Oracle Cloud header, isn't working if it's not the original name (name at the time of the creation).

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

In order to get the correct identity domain Id to use, check in an Oracle Cloud instance already created (e.g. a database one) and check the Details, you'll see the original identity domain listed there (credits to Pieter Van Puymbroeck).

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

Working With PSM Cli

Once the PSM has been correctly configured it's time to start checking what options are available, for a detailed list of the options check PSM documentation.

The PSM commands are product related, so each command is in the form:

psm <product> <command> <parameters>  

Where

  • product is the Oracle cloud product e.g. dbcs, analytics, BigDataAppliance, for a complete list use psm help
  • command is the action to be executed against the product e.g. services, stop, start, create-service
  • parameters is the list of parameters to pass depending on the command executed

The first step is to check what instances I already created, I can do so for the database by executing

psm dbcs services  

which, as expected, will list all my active instances

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

I can then start and stop it using:

psm dbcs start/stop/restart -s <INSTANCE_NAME>  

Which in my example provides the Id of the Job assigned to the stop operation.

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

When I check the status via the service command I get Maintenance like in the web UI.

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

The same applies to the start and restart operation. Please keep in mind that all the calls are asynchronous -> the command will call the related REST API and then return the associated Job ID without waiting for the command to be finished. The status of a job can be checked with:

psm dbcs operation-status -j <JOB_ID>  

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

The same operations described above are available on OAC with the same commands by simply changing the product from dbcs to analytics like:

psm analytics start/stop/restart -s <INSTANCE_NAME>  

On top of the basic operation, PSM Cli allows also the following:

  • Service Instance: start/stop/restart, instance creation-deletion
  • Access Control: lists, creates, deletes, enables and disables access rules for a service.
  • Scaling: changes the computer shape of an instance and allows scaling up/down.
  • Storage: extends the storage associated to OAC
  • Backup Configuration: updates/shows the backup configurations
  • Backups: lists, creates, deletes backups of the instance
  • Restore: restores a backup giving detailed information about it and the history of Restores
  • Patches: allows patching, rollbacking, doing pre-checks, and retrieving patching history

Creating an OAC Instance

So far we discussed the maintenance on already created instances with start/stop/restart commands, but PSM Cli allows also the creation of an instance via command line. The call is pretty simple:

psm analytics create-service -c <CONFIG_FILE> -of <OUTPUT_FORMAT>  

Where

  • CONFIG_FILE: is the file defining all OAC instance configurations
  • OUTPUT_FORMAT: is the desired output format between short, json and html

The question now is:

How do I create a Config File for OAC?

The documentation doesn't provide any help on this, but we can use the same approach as for on-premises OBIEE and response file: create the first instance with the Web-UI, save the payload for future use and change parameters when necessary.

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

On the Confirm screen, there is the option to Download the REST payload in JSON format

DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli

With the resulting json Config File being

{
  "edition": "<EDITION>",
  "vmPublicKeyText": "<SSH_TOKEN>",
  "enableNotification": "true",
  "notificationEmail": "<EMAIL>",
  "serviceVersion": "<VERSION>",
  "isBYOL": "false",
  "components": {
    "BI": {
      "adminUserPassword": "<ADMINPWD>",
      "adminUserName": "<ADMINUSER>",
      "analyticsStoragePassword": "<PWD>",
      "shape": "oc3",
      "createAnalyticsStorageContainer": "true",
      "profile_essbase": "false",
      "dbcsPassword": "<DBCSPWD>",
      "totalAnalyticsStorage": "280.0",
      "profile_bi": "true",
      "profile_dv_forced": "true",
      "analyticsStorageUser": "<EMAIL>",
      "dbcsUserName": "<DBUSER>",
      "dbcsPDBName": "<PDBNAME>",
      "dbcsName": "<DBCSNAME>",
      "idcs_enabled": "false",
      "analyticsStorageContainerURL": "<STORAGEURL>",
      "publicStorageEnabled": "false",
      "usableAnalyticsStorage": "180"
    }
  },
  "serviceLevel": "PAAS",
  "meteringFrequency": "HOURLY",
  "subscriptionId": "<SUBSCRIPTIONID>",
  "serviceName": "<SERVICENAME>"
}

This file can be stored and the parameters changed as necessary to create new OAC instances with the command:

psm analytics create-service -c <JSON_PAYLOAD_FILE> -of short/json/html  

As shown previously, the result of the call is a Job Id that can be monitored with

psm analytics operation-status -j <JOB_ID>  

Once the Job is finished successfully, the OAC instance is ready to be used. If at a certain point, the OAC instance is not needed anymore, it can be deleted via:

psm analytics delete-service -s <SERVICE_NAME> -n <DBA_NAME> -p <DBA_PWD>  

Where

  • SERVICE_NAME is the OAC instance name
  • DBA_NAME and DBA_PWD are the DBA credentials where OAC schemas are residing

Summary

Worried about providing development isolation in OAC while keeping the costs down? Not anymore! With PSM Cli you now have a way of creating instances on demand, start/stop, up/down scaling with a command line tool easily integrable with automation tools like Jenkins.

Create an OAC instances automatically only when features need to be developed or tested, stop&start the instances based on your workforce timetables, take the benefit of the cloud minimizing the cost associated to it by using PSM Cli!

One last note; for a full DevOps OAC implementation, PSM Cli is not sufficient: tasks like automated regression testing, code versioning, and promotion can't be managed directly with PSM Cli but require usage of external toolsets like Rittman Mead BI Developer Toolkit. If you are interested in a full DevOps implementation on OAC and understanding the details on how PSM Cli can be used in conjunction with Rittman Mead BI Developer Toolkit don't hesitate to contact us!

The Forrester Wave™: Big Data Fabric, Q2 2018

The @Forrester Wave™: Big Data Fabric, Q2 2018. @Oracle continues to broaden its Big Data Fabric solution and leads the pack.