Why I created a blog

Its been many years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.


Thursday, April 30, 2020

Creating Encrypted Keys for Essbase using the OCI CLI


In the latest release of Marketplace 19c we are now required to encrypt three keys used in the install process: The Essbase password, the Confidential Application secret and the Database password.  I find it funny we are encrypting a secret, but I digress.  If you want a really good article on the whole install process, look at Sarah Zembrum's post creating-an-essbase-19c-stack-on-the-oci-marketplace It has a detailed step by step instruction on what you need to do. In the post at step 13, she talks about installing the OCI CLI on your machine and has a link another of her good posts on doing the setup. That said, There is an easier way.

Once you are on your Cloud service console, go to the hamburger next to Oracle and select Compute (I'm sure it shows up other places, but I found it here)
Once you are on your compute instance, on the right side of the screen next to the data center you will see an icon that looks like a box with a greater than sign
Clicking on that opens Cloud Shell instance. This is a LinuX instance that has OCI installed on it already so you don't have to go through the tedious install process. 

Once you are in, you can convert the items to Base 64 and use the CLI command to encrypt your  passwords. Follow Sarah's step14 and repeat for your Confidential Application and DB passwords. It is easiest to do them all now than to come back later to do the other two.
What is nice is I can use VI to create s a script to do everything and save it there.  Once I saved it, I did have to go back ad do a chmod to make it executable 
For example here is my keyencrypt.sh script. 
I know it says Phoenix and in my earlier step I connected to Ashburn.  This is just an example of the script where I did not change everything.

Note, I can't use this for doing things like scheduling the stop and start of Essbase, but it gets me past the install pretty easily.

Tuesday, March 10, 2020

Making Smart View Smarter

I have been working on cloud products for a while now, for multiple clients and in multiple instances. My list of private connections was getting out of hand and I really didn;t like having to search through them. Plus I wanted to connect to EPM Cloud, OAC, Essbase in OAC, in 19c , on-prem. Plus an added level for me, is this is for multiple customers. I dealt with it for a long time and finally asked my friends at Oracle if there was an easier way.  The answer I got back was "Of course, why don't you just do this".   Create a file and use it as a shred connection. The this is what I'm going to describe. I'm sure it is buried in the documentation somewhere, but this way you can find it a bit easier.

First you have to create a xml file.
I'm putting in a full file so you can cut and paste it to make your life easier. In may case I have 4 test servers and 3 production servers. Mine are all OAC and Essbase 19c, but you can include EPM cloud and on-prem connections as well. The important thing is the Server name. That is what you see in Smart View.

<?xml version="1.0" encoding="UTF-8"?>
<res_GetProvisionedDataSources>
<!-- Test OAC (including 19c) -->
    <Product id="OAC-test" name="Oracle® Analytic Cloud - Essbase" displayVersion="12.2.1.1.115">
    <Server name="Test OAC Server 1" context="https://XXXXXXX.analytics.ocp.oraclecloud.com/essbase/smartview"/>
    </Product>

    <Product id="OAC-test" name="Oracle® Analytic Cloud - Essbase" displayVersion="19.1.1.1.001">
    <Server name="Test 19c Essbase " context="https://YYYYYYY/essbase/smartview"/>
    </Product>

    <Product id="OAC-test" name="Oracle® Analytic Cloud - Essbase" displayVersion="12.2.1.1.115">
    <Server name="Test OAC Server 2" context="https://ZZZZZZ.analytics.ocp.oraclecloud.com/essbase/smartview"/>
    </Product>
   
    <Product id="OAC-test" name="Oracle® Analytic Cloud - Essbase" displayVersion="12.2.1.1.115">
    <Server name="Test OAC Server3" context="https://essbasesdstest-gapinc1.analytics.ocp.oraclecloud.com/essbase/smartview"/>
    </Product>
<!-- Production OAC  -->   
    <Product id="OAC-Prod" name="Oracle® Analytic Cloud - Essbase" displayVersion="12.2.1.1.115">
    <Server name="Prod OAC Server 1" context="https://AAAAA.analytics.ocp.oraclecloud.com/essbase/smartview"/>
    </Product>

    <Product id="OAC-Prod" name="Oracle® Analytic Cloud - Essbase" displayVersion="19.1.1.1.001">
    <Server name="Prod OAC Server2" context="https://BBBBB.analytics.ocp.oraclecloud.com/essbase/smartview"/>
    </Product>

    <Product id="OAC-Prod" name="Oracle® Analytic Cloud - Essbase" displayVersion="12.2.1.1.115">
    <Server name="Prod OAC Server 3" context="https://CCCCCC.analytics.ocp.oraclecloud.com/essbase/smartview"/>
    </Product>

</res_GetProvisionedDataSources>


 Once you have the file created save it somewhere. So I could remember where it was, I put it under c:/Oracle/SmartView as that is the directory ORacle created when it installed Smart View for me. I named the file  "SmartViewProviders.xml 

Now Open Excel and go into the Smart View Options. 
On the advanced tab in the box for Shared Connection URL enter the path and name of your file . In my case I put in 
c:/Oracle/SmartView/SmartViewProviders.xml

 

After entering it, I clicked OK. clicking on Shared connection I now get a drop down box showing my options 
 

Typically when you click on Shared connections, it requires an immediate login. Not any more. You will not be prompted until you select one of the connections. Life could not be any easier. 

Wednesday, February 19, 2020

Is the rise and fall of OLAP really the end of it?

I recently saw a blog post by Cedric Chin on the Holistics Blog titled The Rise and Fall of the OLAP Cube. In the post Mr. Chin gives a good description of what OLAP is and how it arose. It is a good read for those who want to learm more about OLAP.  Mr. Chin then argues that with new technology, cheap memory, columnar databases and  parallel processing OLAP cubes are dying. While for some, I might agree, but what I believe is missing from the article is the reason OLAP cubes appeared in the first place.

First, Analysts wanted to get away from depending on IT to provide data for them. It was a constant wait for IT resources to work on the backlog of data requests to provide reporting the business wanted. OLAP for the most part is business run and business centered.  We have seen IT departments shrinking and their load increase. Adding more for them to do brings us back to the 90s waiting on them for data.

Second, and what I feel is the more important point, is OLAP is easy. Mr. Chen talks about the technology. Parallel processing, Columnar databases, etc. I agree those things are great and will change the way data is stored, but, with that technology, currently there is a cost. That cost is having more technical skills. knowing SQL and how to use it properly. Do I really want to write a new SQL query if  I want a different slice of data?  Most users are non-technical. With OLAP, they ask a question and get a response without having to understand complex languages.  Too often in my history, have I seen bad SQL joins and poorly created queries that provide the wrong answers. Users of OLAP point and click  in Excel or BI and get the answers.

Third, OLAP empowers users to go beyond what the structured data gives. Creation of unique hierarchies and ragged hierarchies, layering in simplistic and complex formulas and procedural calculations add to the users ability to analyze the data. Add to that, the ability to bring multiple data sources together (be it multiple relational sources, or manual user input, or both) make most OLAP instances data agnostic. While all of this could be done in in a tied in data source, it would require the data movement that Mr. Chen argues against and in many cases would be more difficult to accomplish that combining them in the OLAP target.  

I am an Oracle centric consultant and know Oracle is working on integrating OLAP into their Autonomous Data Warehouse (ADW) so OLAP will sit on top of it. I'm sure other companies are doing the same. Does this mean OLAP is going away? I don't think so, it is just the net incarnation of it. OLAP will be around. Why? because for the user, is it easy. 


Tuesday, January 14, 2020

Essbase 19c MaxL issue

Recently, I was working on an Essbase 19c project and ran into an issue with MaxL that did not occur in any of my OAC instances.  The issue was when I would submit a long running MaxL process, at different intervals I would get the following errors.

[Thu Mar 03 04:27:37 2016]Local/{appname}///6280/Error(1040025)
Invalid platform [5]. An application protocol error might exist between server and client processes.

[Thu Mar 03 04:27:37 2016]Local/{appname}///6280/Error(1040065)
Protocol mismatch may occur if a client other than an Essbase Client tries to access Essbase or if the packet is corrupted.

[Thu Mar 03 04:27:37 2016]Local/{appname}///6280/Error(1013295)
Server Request Fails with error code [1040025]

These errors occurred around 2 , 6, or 8 minutes of my process.  After working with Oracle support and development, it appears to be a bug. (Bug 30714391).  What they tole me was 

"There is a default 2 minute timeout in httpd and a 2 minute check for status happening in essnet, every time the two happen there is a chance that you will get a failure.
The longer the calc and or other operations take the more likely it is to get this protocol error."



This will be fixed in new installs, but not in existing implementations (even through patches). However, the fix is relatively painless and I did it in less than 5 minutes.  Here are the steps I followed (given by Oracle development)


  1. ssh to theEssbase 19c server
  2. As opc user edit the file /etc/httpd/conf.d/essbase.conf
    1. sudo vi /etc/httpd/conf.d/essbase.conf
    2. Find the line "WLProxyPassThrough ON" and add the following new line below it.
    3. WLIOTimeoutSecs 300
    4. save the changes and exit from vi.
  3. Restart httpd service:
    1. sudo systemctl stop httpd
    2. sudo systemctl start httpd

I actually changed the timeout from 300 seconds(5 minutes) to 600 seconds(10 minutes)
Retesting, with over 20 iterations of the scripts that had given me problems, resulted in no errors.
Thanks To Oracle Development, I now am moving forward with my development.