Oracle – Hyperion Labs

New Journey!!

Celvin Kattookaran — Wed, 21 Feb 2018 17:03:57 +0000

The last post before I say goodbye.

There are a few people who knew this was happening and for those of you who didn’t.

I’m leaving the Oracle EPM world and starting a new technical journey. I used to joke about Oracle being my second wife, even though my real wife has a different opinion. (She thinks she is second, and sometimes I think she is right about it.)

I was talking to Cameron Lackpour yesterday, and I was telling him that I got divorced and Oracle is my ex-wife now :).

You might be now thinking about, what I’m going to do?

I’m joining OneStream Software as an employee. I’m excited and anxious to start this new journey. I’m excited about the opportunity, the future, and exploring new frontiers. I’m anxious because I’m new to Smart CPM and I know nothing!!!!

Here’s to new beginnings, and a new CPM Family. You can follow my new blog CPM Insights or EPM Insights (Yup I copied my big brother )

I admit that past couple of months, I was in a rush to finish my draft posts. I’m leaving, and I wanted to do a little more before I leave

A couple of days I was sad that I’m leaving the EPM world. I admit I’ll miss the people, KScope,…

In no particular order (I’m overwhelmed, so I could miss names, apologies if you don’t see your name)

Peter Nitschke, Tim German, John Booth, Sree Menon, Natalie Delemar, Jessica Cordova, Ricardo Giampaoli, Rodrigo Radtke de Souza, Prasad Kulkarni, Shankar Viswanathan, Ujwala Maheswari, Dan Pressman, Gabby Rubin, Glenn Schwartzberg, Shaji Puthenveetil, Shaun Bakey, Suren Seshadri, ODTUG, and many more.

It’s been a long journey; I started this blog because I didn’t want to forget what I learned (I was on the longest beach when I got recruited at Deloitte).

I think it was in 2009 or maybe even before I was fighting on an OTN thread with someone called “CL” about block creation!!!! I was like who is this CL guy doesn’t know anything!!! Later I did tell him this story and we both we laughing hysterically :):):) o

I think after 2 or 3 years into writing a lot of posts (I hate when I go back and look at them), I think it was Edward Roske, who first added a link to my blog. I went ahead looked at it again, and Orahyplabs is the first one ;). I was thrilled that I emailed a screenshot to a few of my friends.

When I got added to Cameron’s best and brightest email list, I was so excited about it, and my wife thought I won a lottery.

I still remember talking to Tim German (mistaking him for someone else) and him asking me to come and start looking at OTN forums, I didn’t know anything, however, him thinking so highly of me was like conquering a new world.

When I first went to Kscope and met all the EPM giants and them recognizing me, let’s say my cheeks were hurting (I was blushing and smiling). I’m blushing now too.

The exciting thing about memories is they all come rushing like a hurricane and crush you

I believe I’ve tried my best to keep this place alive, Wish me luck and this is me signing off for the last time.

I give you love and CPM

The post New Journey!! appeared first on Oracle - Hyperion Labs.

Execute a Calculation script based on Planning unit status – Groovy on-premises Snippets Part III

Celvin Kattookaran — Wed, 21 Feb 2018 15:12:07 +0000

I was on a mission to finish all the posts in my drafts, and I’m somewhat satisfied that I could cover halfway through.

I remember someone asking this on OTN forums, “How can I run a business rule when a Planning unit is approved?”

I used Vision application and will not be covering Planning Unit details 🙂 (I’m tired 🙁 )

I’m going to use Groovy script to achieve this request.

I’m logged in as a user who can approve Planning units.

If you look at Application schema, you’ll be able to see the tables associated with Planning Units. Yep, they all start with HSP_PM_.

HSP_PM_ACTIONS holds all the actions a user can perform, from here we now know that approval action id is 2

HSP_PM_STATES table will tell us all we need to know about different states.

Table of our interest is HSP_PLANNING_UNIT, and this table holds all the details we need for creating a Groovy script. I’m using Account as my secondary dimension.

We can run a quick SQL to get the member names involved from the four dimensions.


select B.Object_Name Scenario,C.Object_Name Version,d.object_name Entity,e.object_name Secondary_Member from Hsp_Planning_Unit a,
hsp_object b,
hsp_object c,
hsp_object d,
hsp_object e
where a.Process_State=3
and A.Scenario_Id=B.Object_Id
and a.version_id=c.object_id
and a.entity_id=d.object_id
and A.Secondary_Member_Id=e.object_id

Now that we have all the required information let’s create a groovy script calc to run a calculation. I’m going to copy working data to final for all the approved Account, Entity, Scenario, and Version combination.


import groovy.sql.Sql

Thread.currentThread().setContextClassLoader( getClass().getClassLoader() )

// Read java and maxl configuration script for the prod environment.
def keyConfig = new ConfigSlurper().parse(new File('C:/groovy_scripts/Properties/JavaMaxl.txt').toURL())

def appname=keyConfig.essbaseEnv.prod.esbApp
def dbname=keyConfig.essbaseEnv.prod.esbDB
def sqluser=new String(keyConfig.essbaseEnv.prod.sqlUser.decodeBase64())
def sqlpass=new String(keyConfig.essbaseEnv.prod.sqlPass.decodeBase64())

Sql sql=Sql.newInstance("jdbc:oracle:thin:@localhost:1521:XE", sqluser, sqlpass)

def now = new Date()

def tmpMaxlfile = new File("C:/groovy_scripts/tmpmxl_${now.format("yyyyMMdd-HH_mm_ss_SSS", TimeZone.getTimeZone('UTC'))}.msh")
tmpMaxlfile.withWriter{ out ->
    out.println ($/
    login $$key ${keyConfig.essbaseEnv.prod.maxlUser} $$key ${keyConfig.essbaseEnv.prod.maxlPass} on localhost;
    execute calculation '
        SET AGGMISSG ON;
        SET UPDATECALC OFF;
        SET EMPTYMEMBERSETS ON;
         
        FIX(&CurYr,
        @LEVMBRS("Period",0),
        @IDESC("P_TP"))
    /$)
        sql.eachRow("""select B.Object_Name Scenario,C.Object_Name Version,d.object_name Entity,e.object_name Secondary_Member from Hsp_Planning_Unit a,
                        hsp_object b,
                        hsp_object c,
                        hsp_object d,
                        hsp_object e
                        where a.Process_State=3
                        and A.Scenario_Id=B.Object_Id
                        and a.version_id=c.object_id
                        and a.entity_id=d.object_id
                        and A.Secondary_Member_Id=e.object_id""")
                     { row ->
        out.println """
                FIX("$row.Scenario",
                "$row.Entity",
                "$row.Secondary_Member")
                   
                    DATACOPY "$row.Version" TO "Final";
                   
                ENDFIX
                    """    
                     }
            

        sql.close() 
        out.println """
        ENDFIX'
        
    on $appname.$dbname;
    logout;
    spool off;
    exit;
    """    
}

    essmsh.runFile(["-D","C:/groovy_scripts/tmpmxl_${now.format("yyyyMMdd-HH_mm_ss_SSS", TimeZone.getTimeZone('UTC'))}.msh","1036072223,1105601687"] as String[])

First, we import the excellent Groovy SQL, read the parameters.
Line 13, I’m connecting to Planning schema and going to write a temporary MaxL script. I’m using time stamp so that it is unique for different users. (I don’t think two users are going to perform a similar action with millisecond precision).
Lines 24 – 30 I’m creating a calc script on the fly.
Line 30 I’m running the SQL and finishing my calc script 🙂

Line 64 we are going to execute the temporary MaxL script, the order of arguments are important, if you are using an encrypted Maxl, follow the order below.

-D for decrypting
MaxL file name
Private key for decryption
Any variables

Here is a sample output of this script.

    login $key 980700984040509848903004677700 $key 2954990220132113241093195557202967062540 on localhost;
    execute calculation '
        SET AGGMISSG ON;
        SET UPDATECALC OFF;
        SET EMPTYMEMBERSETS ON;
         
        FIX(&CurYr,
        @LEVMBRS("Period",0),
        @IDESC("P_TP"))
    

                FIX("Forecast",
                "410",
                "1110")
                   
                    DATACOPY "Working" TO "Final";
                   
                ENDFIX
                    

                FIX("Forecast",
                "410",
                "1150")
                   
                    DATACOPY "Working" TO "Final";
                   
                ENDFIX
                    

        ENDFIX'
        
    on Vision.Plan1;
    logout;
    spool off;
    exit;

I’m going to call this script from a calc script as given below.

RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "clear"; RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "level" "FINE"; RUNJAVA com.hyperion.calcmgr.common.groovy.cdf.GroovyCDF "run" /* compile, run */ "-file" "C:\groovy_scripts\PMApprove.groovy" "" /* variables in Groovy Script */ ;

There you go, you can run this script, and it’ll look at all approved PUs and copy data to the final version.

Here is the output from CDFLogger.


[2018-02-20 17:52:18] [INFO]  GroovyCDF main: [run, -file, C:\groovy_scripts\PMApprove.groovy, ]
[2018-02-20 17:52:18] [INFO]  Binding essmsh=com.hyperion.calcmgr.common.groovy.cdf.MaxLGroovyShell@235dc721
[2018-02-20 17:52:18] [INFO]  Binding =[null]
[2018-02-20 17:52:18] [INFO]  Binding MaxL=com.hyperion.calcmgr.common.groovy.cdf.MaxLGroovyShell@61bc59aa
[2018-02-20 17:52:18] [INFO]  Binding logger=java.util.logging.Logger@75be16f5
[2018-02-20 17:52:18] [FINE]  [essmsh, -D, C:/groovy_scripts/tmpmxl_20180221-01_52_18_512.msh, 1036072223,1105601687]
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]   Essbase MaxL Shell 64-bit - Release 11.1.2 (ESB11.1.2.4.017B009)
[2018-02-20 17:52:19] [FINE]   Copyright (c) 2000, 2017, Oracle and/or its affiliates.
[2018-02-20 17:52:19] [FINE]   All rights reserved.
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]  MAXL> login $key 980700984040509848903004677700 $key 2954990220132113241093195557202967062540 on localhost;
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1051034 - Logging in user [admin@Native Directory].
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1241001 - Logged in to Essbase.
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]  MAXL> execute calculation '
[2018-02-20 17:52:19] [FINE]     2>         SET AGGMISSG ON;
[2018-02-20 17:52:19] [FINE]     3>         SET UPDATECALC OFF;
[2018-02-20 17:52:19] [FINE]     4>         SET EMPTYMEMBERSETS ON;
[2018-02-20 17:52:19] [FINE]     5>          
[2018-02-20 17:52:19] [FINE]     6>         FIX(&CurYr,
[2018-02-20 17:52:19] [FINE]     7>         @LEVMBRS("Period",0),
[2018-02-20 17:52:19] [FINE]     8>         @IDESC("P_TP"))
[2018-02-20 17:52:19] [FINE]     9>     
[2018-02-20 17:52:19] [FINE]    10> 
[2018-02-20 17:52:19] [FINE]    11>                 FIX("Forecast",
[2018-02-20 17:52:19] [FINE]    12>                 "410",
[2018-02-20 17:52:19] [FINE]    13>                 "1110")
[2018-02-20 17:52:19] [FINE]    14>                    
[2018-02-20 17:52:19] [FINE]    15>                     DATACOPY "Working" TO "Final";
[2018-02-20 17:52:19] [FINE]    16>                    
[2018-02-20 17:52:19] [FINE]    17>                 ENDFIX
[2018-02-20 17:52:19] [FINE]    18>                     
[2018-02-20 17:52:19] [FINE]    19> 
[2018-02-20 17:52:19] [FINE]    20>                 FIX("Forecast",
[2018-02-20 17:52:19] [FINE]    21>                 "410",
[2018-02-20 17:52:19] [FINE]    22>                 "1150")
[2018-02-20 17:52:19] [FINE]    23>                    
[2018-02-20 17:52:19] [FINE]    24>                     DATACOPY "Working" TO "Final";
[2018-02-20 17:52:19] [FINE]    25>                    
[2018-02-20 17:52:19] [FINE]    26>                 ENDFIX
[2018-02-20 17:52:19] [FINE]    27>                     
[2018-02-20 17:52:19] [FINE]    28> 
[2018-02-20 17:52:19] [FINE]    29>         ENDFIX'
[2018-02-20 17:52:19] [FINE]    30>         
[2018-02-20 17:52:19] [FINE]    31>     on Vision.Plan1;
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012553 - Copying data from [Working] to [Final] with fixed members [Account(1110); Period(BegBalance, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, YTD, QTD, ITD, No Period, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Rolling); Year(].
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012675 - Commit Blocks Interval for the calculation is [3000].
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012675 - Commit Blocks Interval for the calculation is [3000].
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012574 - Datacopy command copied [24] source data blocks to [24] target data blocks.
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012553 - Copying data from [Working] to [Final] with fixed members [Account(1150); Period(BegBalance, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, YTD, QTD, ITD, No Period, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Rolling); Year(].
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012675 - Commit Blocks Interval for the calculation is [3000].
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012675 - Commit Blocks Interval for the calculation is [3000].
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012574 - Datacopy command copied [24] source data blocks to [24] target data blocks.
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012550 - Total Calc Elapsed Time : [0.098] seconds.
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1013274 - Calculation executed.
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]  MAXL> logout;
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]        User admin is logged out
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]    MaxL Shell completed
[2018-02-20 17:52:19] [FINE]

The post Execute a Calculation script based on Planning unit status – Groovy on-premises Snippets Part III appeared first on Oracle - Hyperion Labs.

Notepad++ Plugins

Celvin Kattookaran — Tue, 20 Feb 2018 15:30:32 +0000

I’m a big advocate of Notepad ++. It’s free, and it got a great community of developers who offer great plugins that can enhance your productivity. I did inform Tim German (2 years ago, I did start and what you now see is a resurfaced draft ;)) that I’ll write a post on the plugins that I use and offer some tips. I feel now is the time o_O. Before I jump into the plugins, let’s look at some shortcuts.

Here are two links that you can explore if you are all about shortcuts.

http://techtalk.virendrachandak.com/notepad-keyboard-shortcuts/

http://www.keyxl.com/aaacd5a/43/Notepad-Plus-text-editor-software-keyboard-shortcuts.htm

Moving lines in Notepad++

Let’s say for example you want to move lines, cut and paste works all the time. However, this is an excellent shortcut to know.

Keep the cursor on the line you want to move, Use Ctrl+Shift keys and Up/Down keys to move the line.

This works for multiple lines too, and the difference is you got to select those lines.

Duplicating and Cut lines

Of course, Ctrl+X works. However, you’ve to select the line and then cut it, here is a shortcut for you – Ctrl + L, cuts the line that you are on.

To duplicate a line, Ctrl + D

Preferences

You can set preferences, and I would recommend setting at least one and that would be the backup.

Settings->Preferences->Backup.

I’ve gone back and looked up one-month-old documents that I forgot to save!!!!

You can run a search from Notepad ++ and even set up your search engine.

If you want to use Essbase techref, you can do that too.

TechRef
For any words
https://docs.oracle.com/cd/E40248_01/epm.1112/essbase_tech_ref/search/srchrslt.htm?searchField=$(CURRENT_WORD)&srcriteria=any

Exact Phrase
https://docs.oracle.com/cd/E40248_01/epm.1112/essbase_tech_ref/search/srchrslt.htm?searchField=$(CURRENT_WORD)&srcriteria=phrase

All Words
https://docs.oracle.com/cd/E40248_01/epm.1112/essbase_tech_ref/search/srchrslt.htm?searchField=$(CURRENT_WORD)&srcriteria=all

If you would like to use the latest documentation, then it is as follows

https://docs.oracle.com/apps/search/search.jsp?q=”$(CURRENT_WORD)”&category=applications&product=e57185-01

Open Menu

I’ve seen a handful of people navigating back to the same file directory and opening another file in same location using the right-click menu. You don’t have to do it. The default directory always follows the current document. What that means is if you have got a document open in NPP, which was on your Desktop, you can directly use Ctrl + O, or File->Open, it will take to “Desktop” (current document directory).

Right Click menus

You can get full file path, file name only, and a lot of other handy shortcuts from right click menu. Go ahead and right click on the file tab.

My favorites

Close all to left (and right)
Open containing folder in explorer
Full File path to clipboard
Move to new Instance (I like to take screenshots, without clutter )

Now if I perform a right click on a line or a selection, I get this

Depending on the language selection, you can comment (Ctrl + K) and uncomment (Ctrl + Alt + K) the selection, search on the internet, change case, last but not the least use base encoding.

Oh yeah, there is spell check (and I’m always Calvin or Kelvin )

Some cool stuff that I regularly use

Encoding, this one is hard to miss – there is a menu for it where you can change/encode to ANSI, UTF-8, UTF with markup, and lot more.

Indent

Tab away; you can select multiple lines and indent them, to reduce the indent – Shift + Tab

Editing

There is a ton of stuff hidden in that edit menu, the ones that I use most

Case Conversion

EOL Conversion

When I’m working on a windows machine writing a shell script (Don’t ask why ), this one is my savior

Remove Trailing space

What would I do without this, especially when you set variables, and you got spaces, oh boy you are in trouble.

I used to use Tab to Space, when I code in NPP and move that to Calc Manager (in earlier versions, now I code directly in Calc Mgr ). I don’t usually trim leading spaces because of the indent – hey there is an easy way to do that too, just saying

Sorting and removing empty lines

I’ve used remove empty lines a lot, sorting not that much.

Column selection and keep unique

Yup, you can make column selection by pressing “Alt” while selecting, TextFX is a plugin with tons of options for editing text and keep unique is my favorite.

Navigate to TextFX->TextFX Tools and check “Sort output unique”

Once done perform a sort, let’s look at a video.

Plugins

These are the plugins that I use, some of them are the default ones and the other you need to install.

JSTool

I’ve written briefly about JSTool when I published the blog post about exportdataslices.

You can use this to format a JSON string, view the string in a tree view.

Compare

Well, it is used for comparing two files.

You can even perform a compare against the same file before last save. That is an excellent option if you are not clear what you were editing before saving. It has saved me a lot of hair and lot of undo operations. You can use the navigation bar for quick navigation, just saying.

DSpellCheck

Even in NPP, I like to spell check 😉 and this is what the above plugin does, you can turn it on (that is the default)

MIMETools

You saw base encoding and decoding earlier, and yes that comes from MIMETools. You can use this to perform URL encoding (yes you don’t have to look them up on internet again)

XML Tools and Treeview

I talked about XML tools when I wrote about LCM and pretty printing LCM. Once enabled it will automatically validate your XML file. In the earlier post I used the XSL Transformation feature to pretty print LCM.

Take a look at the listing.xml file from an LCM backup.

Two lines!!! Let’s do the magic.

XML Tools -> Pretty print with line breaks

Now that’s a readable XML.

If you want to see a tree view of the XML, you can use the XML Treeview plugin.

Execute from Notepad++

I’ve talked about this feature/plugin when I wrote about launching MaxL’s from Notepad++. You can pretty much launch any program from here.

Here is me launching Groovy code from NPP.

SQL on CSV files

Here is a neat one where you can run SQL on delimited files.

Here is the result!!!

SnippetPlus

I’ve talked about this one when I wrote the post on EAS Console in Notepad ++.

I hope I can update this one with the latest commands. You can dock Snippet Plus and use the available commands.

Sometimes these come handy. Hope it helps.

The post Notepad++ Plugins appeared first on Oracle - Hyperion Labs.

Essbase outline from XML!! – Part II

Celvin Kattookaran — Wed, 14 Feb 2018 16:12:05 +0000

If you didn’t read Part I of editing Essbase outline using XML, you are missing out on how this XML can be read.

Now that we know how to read the XML, let’s start creating the XML file and update our outlines. I’m a lazy person, and I guess you all know that by now. I’m going to use Groovy to generate the XML file from a flat file. If you are just interested in the code, then you can get the code from the end of the blog post and be done. I know you want to follow along

Since we are dealing with a file with delimiters why don’t we get an excellent file reader for that?


@Grab('com.opencsv:opencsv:4.1')

The above command will resolve all dependencies and get opencsvreader. Next part is easy we need to import opencsv as it is not natively available in Groovy. Let’s also import groovy XML while we are at it.


import com.opencsv.CSVReader
import groovy.xml.*

otlfile = new FileReader("D:/OneDrive-Business/OneDrive - Intekgrate/GroovyScripts/testuda.txt")
outlineXML = new File("C:/Users/ckatt/Documents/testessbaseXML.xml")

Use a file reader to read the member file and a location for the XML file.


char delimiter ='|'
CSVReader reader = new CSVReader(otlfile,delimiter)
// read header to check columns
header=reader.readNext()
def now = new Date()
println "${now.format("yyyy MM dd-HH:mm:ss.SSS", TimeZone.getTimeZone('UTC'))} Start XML creation"

You can specify a delimiter of your choice, by default as the name implies CSVReader take comma as the delimiter. I’m going to read the first line to check for the columns (line 4). Now we need to read the header information. I’m going to make it flexible enough that the columns don’t need to follow an order. Here is a sample of CSV file that I’m using in this example.

Here is another format.

All headers except Alias must remain the same, or you’ll have to update that in the following code.

To add multiple UDAs, you can use comma separated list. To add multiple attribute association, you must specify the values as comma seperated list in the following format

Attribute Member:Attribute Dimension.


// find the column positions
storagecol=header.findIndexOf{it.equalsIgnoreCase("storage")}
consolcol=header.findIndexOf{it.equalsIgnoreCase("aggregation")}
opcol=header.findIndexOf{it.equalsIgnoreCase("operation")}
mbrcol=header.findIndexOf{it.equalsIgnoreCase("member")}
prntcol=header.findIndexOf{it.equalsIgnoreCase("parent")}
siblcol=header.findIndexOf{it.equalsIgnoreCase("prevsibling")}
newnamecol=header.findIndexOf{it.equalsIgnoreCase("newname")}
udacol=header.findIndexOf{it.equalsIgnoreCase("uda")}
attrcol=header.findIndexOf{it.equalsIgnoreCase("attribute")}
udareplacecol=header.findIndexOf{it.equalsIgnoreCase("replaceuda")}

aliascols=header.findIndexValues{it =~ /^Alias.*/}

What we are doing here is trying to find the column indexes that can be used to generate XML. For the alias, we are going to find all the columns that start with “Alias”.
Now that we’ve column indexes, let’s create the XML file. I’m using StreamingMarkupBuilder to create XML.

Below given is just a portion of StreamingMarkupBuilder


otlxml = new groovy.xml.StreamingMarkupBuilder().bind {
mkp.xmlDeclaration()
    
        mkp.comment "You need to keep track of otlversion or you can always reset it to 0 using -1 as a value for otlVersion"
        
        otlEditMain ("xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance", xmlns: "mbredit","xmlns:xs": "http://www.w3.org/2001/XMLSchema", otlVersion: "0") {
         
            mkp.comment "You can ignore otlUpdate section if you are not adding AliasTable or changing DTS or other properties"  
         
            otlUpdate(caseSensitive:"true",enableMemberType:"false"){
                 
                impliedShareSetting("default")
                 
                mkp.comment "Add new alias table name, if you are adding one"
                 
                aliasTable("LongName")
                 
                mkp.comment "Use for changing dynamic time series"
                 
                dtsMbr(mbrName:"Q-T-D",number:"2",enable:"true")
            }
             
}

mkp.xmlDeclaration() will add this nice line to the generated XML file.

&lt;?xml version="1.0" encoding="UTF-8"?&gt;

To add comments, you just write the comment inside mkp.comment(“your comment”).

To add child elements inside a parent, you enclose the child elements inside a closure. Elements attributes are defined like maps.

The above code will produce a file like the one given below.

Now that we have the information for otlUpdate let’s start reading rest of the file contents.

reader.each{list ->
	if(storagecol !=-1){
		storageval=list[storagecol]
	}
	if(siblcol !=-1){
		prvsibling=list[siblcol]
	} else {
		prvsibling=""
	}
	if(attrcol != -1){
		attrvals=list[attrcol]
	}
	if(udacol != -1){
		udavals=list[udacol]
	}
	if(consolcol != -1){
		consolval=list[consolcol]
	}
	
	if(list[opcol].equalsIgnoreCase("update")){
		mbrUpdate(thisMbr:list[mbrcol]){
			mbrInfo(''){
			   if(aliascols){
				 aliascols.each{ tblindx ->
				   aliasindx=tblindx.intValue() ?: null
				   aliasval=list[aliasindx]
				   if(aliasval){
					alias(aliasTable:header[aliasindx].split(':')[1].trim(), alias:aliasval)
				   }
				 }
			   }
			   if(storageval!='shared'){
				   if(udavals){
					udareplace=list[udareplacecol]
					udavals.split(',').each{udaname->
							if(udareplace){
								udas(replace:udareplace, name:udaname)
							} else {
								udas(name:udaname)
							}    
						}
					}                               
			   }
			  if(storageval){
				dataStorage(storageval)
			  }
			  if(consolval){
				consolidation(consolval)
			  }
			}
		}
		if(storageval!='shared'){                    
			if(attrvals){
				attrvals.split(',').each{attr->
					dimattr=attr.split(':')
					mbrAssoc(thisMbr:list[mbrcol],attrDim:dimattr[1],attrMbr:dimattr[0]){
					}
				}    
			} 
		}    
	}
}

reader.each will read the file line by line (see it is that easy)
We are checking whether the operation column is Update if it is then create the tags for mbrUpdate.
Aliases we need to provide the table name and alias.
Lines 2-16 makes sure that you got those columns, findIndexOf returns -1 if nothing is found.
Line 21 checks whether the file got any Alias columns if present it loops through all alias indexes (line 22). Line 23 is called as an Elvis operator in Groovy. Groovy lists store the numbers as Long and to get the output using the index from a list you need an integer.
Line 26 splits the header (e.x. Alias:LongNames) at ‘:’ and then we take the last value.

If you are not adding a shared member, you can assign UDAs (line 30), values from UDA column are split at ‘,’ so if you have got a UDA which contains comma you’ll have to change that here (line 33).

Line 43 is where the dataStorage element is created and if you are on a duplicate name allowed outline you’ll have to change this to the following.

prototypeMbr(unique name of the member)

I would add another column in the CSV file called PrototypeMember and get the index of that column and use it as shown below


prototypecol=header.findIndexOf{it.equalsIgnoreCase("prototypemember")}

prototypeMbr(list[prototypecol])

If not a shared member, attribute association tag creation starts at line 50, here attributes needs the attribute dimension too. Attribute name and Attribute dimension are separated using “:”, multiple associations are separated using “,”s.

You’ll see two splits, one to separate the attributes (line 52) and second to separate the attribute dimensions and attribute members (line 53).

Rest of the code follows the similar logic of looking at the operation and add appropriate tags.

Keep in mind that rename using this method will not retain data!!

Here is the entire code.

@Grab('com.opencsv:opencsv:4.1')

import com.opencsv.CSVReader
import groovy.xml.*

otlfile = new FileReader("C:/Users/ckattookaran/Documents/testdense.txt")
outlineXML = new File("C:/Users/ckattookaran/Documents/testessbaseXML.xml")

char delimiter ='|'
CSVReader reader = new CSVReader(otlfile,delimiter)
// read header to check columns
header=reader.readNext()
def now = new Date()
println "${now.format("yyyy MM dd-HH:mm:ss.SSS", TimeZone.getTimeZone('UTC'))} Start XML creation"

// find the column positions
storagecol=header.findIndexOf{it.equalsIgnoreCase("storage")}
consolcol=header.findIndexOf{it.equalsIgnoreCase("aggregation")}
opcol=header.findIndexOf{it.equalsIgnoreCase("operation")}
mbrcol=header.findIndexOf{it.equalsIgnoreCase("member")}
prntcol=header.findIndexOf{it.equalsIgnoreCase("parent")}
siblcol=header.findIndexOf{it.equalsIgnoreCase("prevsibling")}
newnamecol=header.findIndexOf{it.equalsIgnoreCase("newname")}
udacol=header.findIndexOf{it.equalsIgnoreCase("uda")}
attrcol=header.findIndexOf{it.equalsIgnoreCase("attribute")}
udareplacecol=header.findIndexOf{it.equalsIgnoreCase("replaceuda")}

aliascols=header.findIndexValues{it =~ /^Alias.*/}

storageval=""
prvsibling=""
attrvals=""
udavals=""
consolval=""

otlxml = new groovy.xml.StreamingMarkupBuilder().bind {
mkp.xmlDeclaration()
    
        mkp.comment "You need to keep track of otlversion or you can always reset it to 0 using -1 as a value for otlVersion"
        
        otlEditMain ("xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance", xmlns: "mbredit","xmlns:xs": "http://www.w3.org/2001/XMLSchema", otlVersion: "0") {
         
            mkp.comment "You can ignore otlUpdate section if you are not adding AliasTable or changing DTS or other properties"  
         
            otlUpdate(caseSensitive:"true",enableMemberType:"false"){
                 
                impliedShareSetting("default")
                 
                mkp.comment "Add new alias table name, if you are adding one"
                 
                aliasTable("LongName")
                 
                mkp.comment "Use for changing dynamic time series"
                 
                dtsMbr(mbrName:"Q-T-D",number:"2",enable:"true")
            }
             
            reader.each{list ->
                if(storagecol !=-1){
                    storageval=list[storagecol]
                }
                if(siblcol !=-1){
                    prvsibling=list[siblcol]
                }
                if(attrcol != -1){
                    attrvals=list[attrcol]
                }
                if(udacol != -1){
                    udavals=list[udacol]
                }
                if(consolcol != -1){
                    consolval=list[consolcol]
                }
                
                if(list[opcol].equalsIgnoreCase("update")){
                    mbrUpdate(thisMbr:list[mbrcol]){
                        mbrInfo(''){
                           if(aliascols){
                             aliascols.each{ tblindx ->
                               aliasindx=tblindx.intValue() ?: null
                               aliasval=list[aliasindx]
                               if(aliasval){
                                alias(aliasTable:header[aliasindx].split(':')[1].trim(), alias:aliasval)
                               }
                             }
                           }
                           if(storageval!='shared'){
                               if(udavals){
                                udareplace=list[udareplacecol]
                                udavals.split(',').each{udaname->
                                        if(udareplace){
                                            udas(replace:udareplace, name:udaname)
                                        } else {
                                            udas(name:udaname)
                                        }    
                                    }
                                }                               
                           }
                          if(storageval){
                            dataStorage(storageval)
                          }
                          if(consolval){
                            consolidation(consolval)
                          }
                        }
                    }
                    if(storageval!='shared'){                    
                        if(attrvals){
                            attrvals.split(',').each{attr->
                                dimattr=attr.split(':')
                                mbrAssoc(thisMbr:list[mbrcol],attrDim:dimattr[1],attrMbr:dimattr[0]){
                                }
                            }    
                        } 
                    }    
                } else if(list[opcol].equalsIgnoreCase("delete")){ 
                    mbrDelete(thisMbr:list[mbrcol])
                } else if(list[opcol].equalsIgnoreCase("add")){ 
                    mbrAdd (mbrName:list[mbrcol],parent:list[prntcol],preSibling:prvsibling){
                        mbrInfo(''){
                           if(aliascols){
                             aliascols.each{ tblindx ->
                               aliasindx=tblindx.intValue() ?: null
                               aliasval=list[aliasindx]
                               if(aliasval){
                                alias(aliasTable:header[aliasindx].split(':')[1].trim(), alias:aliasval)
                               }
                             }
                           }
                           
                           if(storageval!='shared'){
                               if(udavals){
                                udareplace=list[udareplacecol]
                                udavals.split(',').each{udaname->
                                        if(udareplace){
                                            udas(replace:udareplace, name:udaname)
                                        } else {
                                            udas(name:udaname)
                                        }    
                                    }
                                }                               
                           }
                          if(storageval){
                            dataStorage(storageval)
                          }
                          if(consolval){
                            consolidation(consolval)
                          }
                        }                        
                    }

                    if(storageval!='shared'){                    
                        if(attrvals){
                            attrvals.split(',').each{attr->
                                dimattr=attr.split(':')
                                mbrAssoc(thisMbr:list[mbrcol],attrDim:dimattr[1],attrMbr:dimattr[0]){
                                }
                            }    
                        } 
                    }    
                } else if(list[opcol].equalsIgnoreCase("move")){ 
                    mbrMove (thisMbr:list[mbrcol],parent:list[prntcol],preSibling:prvsibling)                    
                } else if(list[opcol].equalsIgnoreCase("rename")){ 
                    mbrRename (thisMbr:list[mbrcol],mbrName:list[newnamecol])                      
                }
            }   
        }
    }

outlineXML.withWriter("UTF-8") { writer ->
   writer.write( XmlUtil.serialize(otlxml))
}
now = new Date()
println "${now.format("yyyy MM dd-HH:mm:ss.SSS", TimeZone.getTimeZone('UTC'))} Essbase outline edit XML created"

Line 170 is where this is written to a file. Line 171, XmlUtil.serialize, is doing the pretty print of XML.

Yes, I agree it is long. However I fell in love with the builder, it is effortless to add tag and elements.

After running the above code, the following XML file is generated.

Now to import this XML file, you’ll have to use either C API or JAVA API.

I chose to use Groovy 😉

Before running the code in groovy, you need to add the following jar files to your Groovy lib location.

 
import com.essbase.api.session.IEssbase
import oracle.core.ojdl.logging.ODLLogger
import java.util.logging.Level

def keyConfig = new ConfigSlurper().parse(new File('C:/Users/ckattookaran/Documents/usercred.txt').toURL())

appname=keyConfig.essbaseEnv.prod.esbApp
dbname=keyConfig.essbaseEnv.prod.esbDB
svrname=keyConfig.essbaseEnv.prod.esbServer
user=keyConfig.essbaseEnv.prod.esbUser
password=keyConfig.essbaseEnv.prod.esbPass
provider="Embedded"

logger = ODLLogger.getODLLogger("oracle.EPMOHPS")
logger.setLevel(Level.SEVERE)

ess = IEssbase.Home.create(IEssbase.JAPI_VERSION)
olapSvr = ess.signOn(user,password, false, null, provider, svrname)
now = new Date()
println "${now.format("yyyy MM dd-HH:mm:ss.SSS", TimeZone.getTimeZone('UTC'))} $user Connected to $svrname."
cube = olapSvr.getApplication(appname).getCube(dbname)
println "${now.format("yyyy MM dd-HH:mm:ss.SSS", TimeZone.getTimeZone('UTC'))} Start editing $appname.$dbname outline."
cube.buildDimensionXml("C:/Users/ckattookaran/Documents/testessbaseXML.xml", "C:/Users/ckattookaran/Documents/XMLEdit.log", true)
println "${now.format("yyyy MM dd-HH:mm:ss.SSS", TimeZone.getTimeZone('UTC'))} Finished editing $appname.$dbname outline."

olapSvr.clearActive()
olapSvr.disconnect()
ess.signOff()
now = new Date()
println "${now.format("yyyy MM dd-HH:mm:ss.SSS", TimeZone.getTimeZone('UTC'))} Closed connection and disconnected from $svrname."

Oh if you have got a space in XML location or log location this will not work. You’ll get an error.

If you are going to track the outlineVersion, then you either need to remember the version or reset it to zero using -1 as value or let the build fail!!! Oh yeah, there is no option as of now to retrieve the outline version using API and use that in the XML file.

Here is what a failed Dimension build log will look like.

The log file will give you the version number!!!

I think there should be a method to query the version and use that in the XML file, rather than a trial and error method.

If successful you should see something similar in the log file.

Let’s look at the outline.

New alias table is added, case sensitivity changed.

Q1 is deleted, Q2 moved and renamed, and IT expenses is added.

If I run the same Groovy using the file given below, it creates an XML as shown below.

There you go, now go ahead and kill those dimension build load rules 😉

The post Essbase outline from XML!! – Part II appeared first on Oracle - Hyperion Labs.

Essbase outline from XML!! – Part I

Celvin Kattookaran — Tue, 13 Feb 2018 15:00:22 +0000

When I published the latest version of Essbase Member Operations utility, I received a comment from Kumar Ramaiyer. If you don’t know Kumar Ramaiyer is a Vice President at Oracle, who looks after Essbase development. Kumar’s comment was about using an XML to perform the same operations in EsbMemberOperation utility.

I happen to remember a blog post from Harry Gates (I got short term memory ) about XML editing is only available in API and replied to Kumar saying “It can be accessed through API only”. In mind, I was thinking what difference it is going to make if I do it using “A” or “B” if the way is the same. However, I still thought of exploring the option, and the result is this blog.

Now to get the details my google-fu was tested. Finally, I got hold of new release feature link where this was the first time (maybe the last time too) where it gets a mention.

XML Outline Editing

If you think that there is an option called import outline using XML hiding in MaxL, you are so wrong. As I said earlier updating outline using an XML file is only available as an API method.

If you think that you can use the XML from export outline MaxL, you are wrong again. They are a lot different. Now I get that the XML for updating and exporting cannot be the same because update XML needs extra attributes and nodes. However, can’t they be somewhat similar?

Here is what you get using export outline MaxL.

Here is what an update outline XML looks like.

Why can’t they follow a similar method, You could then easily use that to create an edit XML file by adding the edit actions!!!

If you think that there is documentation available on creating the XML file, you are somewhat wrong again. There is an example available here, and there is an XSD file available in the bin folder for you to explore!!!

Now that we are done with my whining let’s get to work. I’m not going to cover all the attributes (read properties) or creating a new dimension in this blog post. However, I plan to cover enough so that you can easily use that information to continue on your own.

“Teach to fish”.

Before we start creating the XML, we need to understand mbredit.xsd file. Here is what the XSD file looks like.

Reading XSD (or should I say reading the XML using XSD)

The way you should look at the XSD is by looking at the very bottom; it starts with otlEditMain and references edit_actions group.
Now let’s look at edit actions. We got the following actions.

Outline Update (this is nice. It allows you to add Alias tables, change DTS options, and many more)
Member Update
Member Add
Member Delete
Member Rename
Member Move
Member Association (Attribute)
Dimension Add (nice one)
Dimension Update
Dimension Association (Attribute)
Mark for deletion
Delete marked items

Outline Update

For outline update, it looks at the outline_t element, and you can change the following properties of an outline.

Implied Share setting
Date format
Add alias tables
Change DTS
Update case sensitivity
Enable Member type (This is used for Text Lists and String formatting)

If you look at implied share, it accepts the following values.

Default
Default On
Default Off
Force On
Force Off

Member Add

Member add looks at the mbradd_t element and uses three attributes (two mandatory and one optional) and a child element.

You need to tell which member you are adding using mbrName attribute, use parent attribute for adding this member under a parent and finally you can decide the position of this member in the outline using preSibling attribute. preSibling is an optional attribute.

Once done you must add the child element attributes using mbrInfo. Now, mbrInfo, in turn, looks at the mbrinfo_t element, and this is a massive element in its own. If you look at the dataStorage element, it looks at datastorage_t for values.

One thing to keep in mind “shared” tag is for tagging shared members in a regular outline, for adding shared members in a non-unique (read duplicate members allowed outline) use prototypeMbr instead.

~~I’ve no idea how a shared member can be created using the XML file.~~

~~No matter what I do, it all results in an error, here is the thing prototypeMbr comes under mbrInfo and mbrInfo can be used with~~ mbrAdd ~~and mbrUpdate tags, now I’m adding this as a new member I’ve to use mbrAdd then I get this error.~~

~~I did try using the~~ UNIQUE ~~name to add the member; there is something that I’m missing, I’ve no idea what it is.~~

After a lot of tries, I reached out to Kumar and team, and they were gracious enough to help me with the syntax. Apparently, there is an issue with the documentation, the doc uses mbrAdd in a wrong way (it works for regular members, however breaks for shared ones), and that was throwing me off. I updated my syntax, and it started working.

I’ve no idea where commentEx is used as it is not visible from EAS. Same applies for mbrId, you can add a number for that, and it’ll take it. I have got a feeling that PBCS might use this later (remember those member GUIDs from metadata extract.

Member Update

Member update looks at the mbrupdate_t element, and it follows an attribute and a child element.

You need first to tell which member you are updating using the thisMbr tag and then use mbrInfo element we saw earlier to update the properties.

Where you see mbrlocator_t, replace that with the member names/udas/attribute members.

To update a shared member, you need to use fully qualified name of the shared member. Even though the outline is not enabled for duplicate members, you might have a member shared twice and therefore fully qualified name.

Just keep following the same pattern and without knowing you learned how to read the XSD!!!

I bet you might be thinking, this is all great. However, can you create a utility to generate this XML file? That’s what I did, and I’ll publish the Groovy code as another post :), this one is a bit longer than I thought.

I have a hunch that this is what OAC’s Cube Designer is using (I bet it is using this approach, and maybe an incremental build using XML file).

The post Essbase outline from XML!! – Part I appeared first on Oracle - Hyperion Labs.

Hybrid + MDXDataCopy, It’s black magic

Celvin Kattookaran — Wed, 07 Feb 2018 15:30:47 +0000

I’m going to consider this to be pure coincidence that I started working on this draft (which is a year old), and I got a call from the same client where I implemented this solution to work on a new project 🙂

I talked about this during my Kscope17 groovy presentation, and I did tell that I’ll write about it. Apologies for not doing it early. I was at a client converting a Planning application to a Hybrid cube and as you all now know that cross dimming to parent in Hybrid breaks Hybrid. I’ve also seen a different scenario which we’ll talk in this post.

There are different ways to handle this.

Make the cross dim parent and it’s hierarchy as stored and aggregate the dimension, this is fast Hybrid does it’s magic here.
Create a dummy member and add the parent as a child of this member, aggregate the dimension and use this member in formulas.
This blog post method 😉

Cameron (Big brother from an entirely different set of parents) and I had a long conversation on this topic about all the approaches, and even though Approach 1 works, I didn’t like that, it removes the Hybridness ;). If you didn’t see Pete’s and Cameron’s award-winning presentation, go ahead and watch the recording or download the presentation, they did cover a lot of Hybrid Essbase and different techniques.

Let’s say there is a formula which uses top level of all dimensions in a formula. It pretty much becomes a regular BSO cube.

Approach 2

This method works (I knew you might be thinking if this works why an Approach 3) and here is how you can do this. I changed Sample Basic slightly to add zip codes to Market dimension and here is my updated Sample Basic.

Parent members are added to a calc only member. Now to use Calc_Only_East member I need to aggregate Market dimension. No, you cannot just ping Calc_Only_East member, there is no block for a dynamic member, and because of that reason, you’ll not see the values of East in Calc_Only_East member only an agg can help you here.

On my updated Sample Basic database this script took 6.353 seconds. In my real-world application, a similar script was taking 900+ secs.

Approach 3

In this approach, we are going to add dummy members, however no child members!!!! How are we going to get the results, are you crazy?

What I’m going to do is use @CalcMgrMDXDataCopy/RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy to copy the data from Parent member to the dummy members 🙂

I’m going to use Groovy to run the calc and will explain why I’m taking this approach.

I created a property file as given below

// Environments section
essbaseEnv {
 dev {
    maxlKey="1036072223,1105601687"
    maxlUser="980700984040509848903004677700"
    maxlPass="2954990220132113241093195557202967062540"
 }
 test { 
    maxlKey="1036072223,1105601687"
    maxlUser="980700984040509848903004677700"
    maxlPass="2954990220132113241093195557202967062540"
 } 
 prod { 
    maxlKey="1036072223,1105601687"
    maxlUser="980700984040509848903004677700"
    maxlPass="2954990220132113241093195557202967062540"
    calcMgrKey="fhe34i5hsdudoi3fhkjhds"
    calcMgrUser="QgEKVtKPyiOjk46kuKw4XuKiXMUxSBlLYFYiJVVeUJ0KInIKMTGycUYuvmVDWYDT"
    calcMgrPass="qZE1ykPXroMHCcfWIFv2g0IlYv4ZjX3ov/EKxLMDfw+S8YJ4BsbtQxwRHhECbyvU"
    esbApp="SampleH1"
    esbDB="Basic"
 } 
}

Now using Groovy’s ConfigSluper, I can read the keys as opposed to writing them in the calc itself.

// Read java and maxl configuration script for the prod environment.
Thread.currentThread().setContextClassLoader( getClass().getClassLoader() )

def keyConfig = new ConfigSlurper().parse(new File('C:/groovy_scripts/Properties/JavaMaxl.txt').toURL())

// read only prod values, to read test use keyConfig.essbaseEnv.test.esbApp
appname=keyConfig.essbaseEnv.prod.esbApp
dbname=keyConfig.essbaseEnv.prod.esbDB

    essmsh.runScript(keyConfig.essbaseEnv.prod.maxlKey,$/
    login $$key ${keyConfig.essbaseEnv.prod.maxlUser} $$key ${keyConfig.essbaseEnv.prod.maxlPass} on localhost;
    execute calculation '
        RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy
        ${keyConfig.essbaseEnv.prod.calcMgrKey} /* key */
        ${keyConfig.essbaseEnv.prod.calcMgrUser} /*user*/
        ${keyConfig.essbaseEnv.prod.calcMgrPass} /* password */
        "$appname" /* from application */
        "$dbname" /* from database */
        "$appname" /* to application */
        "$dbname" /* to database */
        "[PERIOD].Levels(0).Members"  /* MDX that defines the column members */
        "NON EMPTY Crossjoin(Crossjoin({([FY16],[Sales],[Actual])}, [Product].Levels(0).Members),{[East],[West],[South],[Central]})" /* MDX that defines the row members */
        "East,West,South,Central" /* source member mappings, can be empty */
        "Calc_Only_East,Calc_Only_West,Calc_Only_South,Calc_Only_Central" /* target member mappings, can be empty */
        "" /* Target POV columns, members from dimensions that do not exist on the source*/
         "5000" /* rows per page */
        "C:/groovy_scripts/Logs/AggSal.log"; /* log file , can be empty */
    '
    on $appname.$dbname;
    logout;
    exit;
    /$)

Think about changing a lot of scripts for a user name or password vs change one prop file 🙂

Essbase calc script

RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "clear"; RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "level" "FINE"; RUNJAVA com.hyperion.calcmgr.common.groovy.cdf.GroovyCDF "run" /* compile, run */ "-file" "C:\groovy_scripts\AggSal.groovy" "" /* variables in Groovy Script */ "" ;

This script took 3.474 secs to complete. My real world calc takes 65 secs, now that is a big difference 900 secs to 65 secs. Again as usual with Essbase you need to test, test, test, and test to see whether this approach is faster or not.

Hybrid is magic and combine with Groovy+MDXDataCopy it is black magic.

The post Hybrid + MDXDataCopy, It’s black magic appeared first on Oracle - Hyperion Labs.

Email from calculation script – Groovy on-premises Snippets Part II

Celvin Kattookaran — Tue, 23 Jan 2018 15:01:00 +0000

I’m on a mission to go through my drafts and finish writing all of them before I move to my next project (Which I’m not sure when 😉 ), who knows what will happen 😉

This one is the second of the Groovy Snippet series and a more detailed version of what I presented during Kscope17.

We all have faced this question about sending notification after Essbase calculation completion. I’ve used a variety of options to fulfill this request.

Batch/Shell scripts
Automation tools

Then came Groovy 🙂

When I was looking for some uses cases for my presentation, I went back and looked at the article I wrote for OTN (now Oracle Developer Community). I was interested in that particular use case.

Imagine if you can send an email from Essbase calc script after it is done!!! Groovy opens up endless possibilities.

I’m going to walk you through 2 different approaches which can be used to send an email with or without attachments.

The primary challenge to make this work for TLS enabled providers like Office 365, AWS, and Gmail was to update mailapi and smtp jar files shipped with Essbase. Before we start coding, let’s update those jar files first.

Download 1.5.6 version of both jar files.

You cannot use the 1.6 version because of the JRE version difference. (when can we see the latest JRE in EPM)

Stop Essbase and replace the jar files under \products\Essbase\EssbaseServer\java\lib, once done restart Essbase.

I’m using a property file for username, password, SMTP server, and port number.

Let’s look at Commons Email example. You’ll need commons-email jar file to run this sample code. I liked this format better than javax one. If you don’t want to download an extra jar file, you can look at the javax examples.

Groovy Email with Commons Email

import org.apache.commons.mail.*
// needs for multiple email address
// import javax.mail.internet.*

email = new SimpleEmail()

def emailprop=new FileReader('C:/groovy_scripts/Properties/email.properties').readLines()
//username is 1st line
usrname=new String(emailprop[0].decodeBase64())

//password
psswd=new String(emailprop[1].decodeBase64())

//smtp host
smtphost=emailprop[2]

//smtp port
smtpport=emailprop[3].toInteger()

// mulitple email format
// recipientAddress=[new InternetAddress ('ckattookaran@intekgrate.com'),new InternetAddress ('celvinvincent@gmail.com')]

email.setHostName(smtphost)
email.setSmtpPort(smtpport)
email.setStartTLSRequired(true)
//email.setSSLOnConnect(true)

email.setAuthenticator(new DefaultAuthenticator(usrname, psswd))

email.setFrom(usrname)
// email.setTo(recipientAddress)
email.addTo('hyperionadmin@intekgrate.com') //use a DL for multiple emails
email.setSubject('Groovy CDF email from Essbase Calc script')
email.setMsg("""Email from Calc script:
$job for $mkt finished at ${new Date()}.""")
email.send()

Line 9 and 12 username and password is getting decoded. SMTP properties are set from line 14 to 26.
You can use this to send to multiple recipients; I’m sending this to a DL.
Essbase script
//ESS_LOCALE English_UnitedStates.Latin1@Binary
RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "clear";
RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "level" "FINE";

RUNJAVA com.hyperion.calcmgr.common.groovy.cdf.GroovyCDF
"compile" /* compile, run */
"-file"
"C:\groovy_scripts\apacheemail.groovy"
"job,mkt" /* variables in Groovy Script */
;

FIX("Jan",
"FY17",
"Actual",
"100-10",
@LEVMBRS("Market",0))
"Sales"(
@CalcMgrGroovyNumber("C:\groovy_scripts\apacheemail.groovy", "", @LIST("job","mkt"),@LIST("BudCalc",@NAME(@Currmbr("Market"))));
)
ENDFIX

What happens if you run this script, any guesses?
Before running the script

Essbase data after the script execution.

I did get the emails. However, I lost the actual data!!!!

Keep in mind if you are going to use the @CalcMgrGroovyNumber function, use that in a FIX statement with default members.
New script
//ESS_LOCALE English_UnitedStates.Latin1@Binary
RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "clear";
RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "level" "FINE";

RUNJAVA com.hyperion.calcmgr.common.groovy.cdf.GroovyCDF
"compile" /* compile, run */
"-file"
"C:\groovy_scripts\apacheemail.groovy"
"job,mkt" /* variables in Groovy Script */
;

FIX("Jan",
"NoYear",
"Actual",
"100-10",
@LEVMBRS("Market",0))
"Sales"(
@CalcMgrGroovyNumber("C:\groovy_scripts\apacheemail.groovy", "", @LIST("job","mkt"),@LIST("BudCalc",@NAME(@Currmbr("Market"))));
)
ENDFIX


To send attachments with a groovy script you’ll need the activation.jar file, download the file and place it under \products\Essbase\EssbaseServer\java\udf folder, once done restart Essbase.

Groovy script for sending attachments

import org.apache.commons.mail.*

email = new MultiPartEmail()

def emailprop=new FileReader('C:/groovy_scripts/Properties/email.properties').readLines()
//username is 1st line
usrname=new String(emailprop[0].decodeBase64())

//password
psswd=new String(emailprop[1].decodeBase64())

//smtp host
smtphost=emailprop[2]

//smtp port
smtpport=emailprop[3].toInteger()

logdir='E:/Oracle/Middleware/user_projects/AzgardEpmSystem/diagnostics/logs/essbase/essbase'

email.setHostName(smtphost)
email.setSmtpPort(smtpport)
email.setStartTLSRequired(true)
//email.setSSLOnConnect(true)

email.setAuthenticator(new DefaultAuthenticator(usrname, psswd))

email.setFrom(usrname)
email.addTo('hyperionadmin@intekgrate.com')
email.setSubject('Groovy CDF email from Essbase Calc script')
email.setMsg("""Email from Calc script:
$job for $mkt finished at ${new Date()}.""")

// Create the attachment
EmailAttachment attachment = new EmailAttachment()
attachment.setPath("$logdir/CDFLogger0.log")
attachment.setDisposition(EmailAttachment.ATTACHMENT)
attachment.setDescription("CDFLogger")
attachment.setName("CDFLogger.log")

Thread.currentThread().setContextClassLoader( getClass().getClassLoader() )

// add the attachment
email.attach(attachment);

email.send()


I’m sending the CDFLogger log file in this script, and you can see that Commons Email provides some nice features like set the name and description of the attachment.

Above file was send when Massachusetts calc was done (You can see that from the log file itself 😉 )
Javax version

import java.util.Properties
import javax.mail.*
import javax.mail.internet.*

def emailprop=new FileReader('C:/groovy_scripts/email.properties').readLines()
//username is 1st line
usrname=new String(emailprop[0].decodeBase64())

//password
psswd=new String(emailprop[1].decodeBase64())

//smtp host
smtphost=emailprop[2]

//smtp port
smtpport=emailprop[3]

logdir='C:/Oracle/Middleware/user_projects/epmsystem1/diagnostics/logs/essbase/essbase'

props = new Properties()
props.put('mail.smtp.auth', 'true')
props.put('mail.smtp.starttls.enable', 'true')
props.put('mail.smtp.host', smtphost)
props.put('mail.smtp.port', smtpport)
session = Session.getInstance(props,new Authenticator() {
PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(usrname, psswd)
}
})
message = new MimeMessage(session)
message.setFrom(new InternetAddress(usrname))
message.setRecipients(Message.RecipientType.TO,new InternetAddress(usrname))
message.setSubject('Groovy CDF email from Essbase Calc script')
message.setText("""Email from Calc script:
$job for $mkt finished at ${new Date()}.""")

Transport.send(message)


Javax version for sending attachments

import java.util.Properties
import javax.mail.*
import javax.mail.internet.*
import javax.activation.*

def emailprop=new FileReader('C:/groovy_scripts/email.properties').readLines()
//username is 1st line
usrname=new String(emailprop[0].decodeBase64())

//password
psswd=new String(emailprop[1].decodeBase64())

//smtp host
smtphost=emailprop[2]

//smtp port
smtpport=emailprop[3]

logdir='C:/Oracle/Middleware/user_projects/epmsystem1/diagnostics/logs/essbase/essbase'

props = new Properties()
props.put('mail.smtp.auth', 'true')
props.put('mail.smtp.starttls.enable', 'true')
props.put('mail.smtp.host', smtphost)
props.put('mail.smtp.port', smtpport)
session = Session.getInstance(props,new Authenticator() {
PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(usrname, psswd)
}
})
message = new MimeMessage(session)
message.setFrom(new InternetAddress(usrname))
message.setRecipients(Message.RecipientType.TO,new InternetAddress(usrname))
message.setSubject('Groovy CDF email from Essbase Calc script')

// Create the message part
BodyPart messageBodyPart = new MimeBodyPart()

// Now set the actual message
messageBodyPart.setText("""Email from Calc script:
$job for $mkt finished at ${new Date()}.""")

// Create a multipar message
Multipart multipart = new MimeMultipart()

// Set text message part
multipart.addBodyPart(messageBodyPart)

// Part two is attachment
messageBodyPart = new MimeBodyPart()
DataSource source = new FileDataSource("$logdir/CDFLogger0.log")
messageBodyPart.setDataHandler(new DataHandler(source))
messageBodyPart.setFileName('CDFLogger0.log')
multipart.addBodyPart(messageBodyPart)

Thread.currentThread().setContextClassLoader( getClass().getClassLoader() )

// Send the complete message parts
message.setContent(multipart)

Transport.send(message)


Happy sending emails 🙂
The post Email from calculation script – Groovy on-premises Snippets Part II appeared first on Oracle - Hyperion Labs.

Run shell script in Workspace

Celvin Kattookaran — Tue, 16 Jan 2018 16:15:39 +0000

Writing a follow-up post for a blog post four years and eight months old, never thought.

Someone reached out to me asking “How can I run shell script in the workspace?”. I asked her to try changing the executable to use sh instead of cmd and she had some issues making it work.

She eventually got it working, and I thought maybe a follow-up post was due.

If you know about “Generic job applications” or if you have read the previous posts, then you know what I’m talking.

If not, then please read about generic job applications.

What we are going to do is create a generic job application in Workspace and run a shell script in Workspace!!

Command template is different from all the other scripts. We need an executable to run shell scripts ($CMD and /bin/sh), we need a shell script ($PROGRAM – the script which we are importing), and parameters if any.

Now if you are using a different shebang in your script, you’ll have to use that executable path. If needed you can create Variables here and refer them in the script by using “$.” E.g., If I create a variable called Application, then I can use that in my script as $Application.

Now that we have the job let’s create a shell script.

I’m going to call the OutlineLoad utility to export dimensions.

Application name, Dimension name, and log file location are parameters.

Let’s import this file into Workspace.

Select the generic job as “Job Factory Application.”

Now let’s navigate to 4. Parameters, You can define parameters in this section.

You can use Text or Pre-Determined values.

Once done, complete the import.

Order of the prompts is significant as we are using positional variables in the script. If we move Dimension down, then the script will try to extract the log name

Time to run the job.

Users can select/enter the prompts and execute the job.

Once done you’ll get a Table of Contents windows where you can see a stdout, stderr (if present), and any log files.

I did get a stderr file when I ran OutlineLoad without /-O switch. Since this is an extract maintaining the order is not needed, and the message is routed as an error.

After adding /-O switch in the script and reimporting it, here is the TOC.

Yes, it allows you to download the exported CSV file!!!

You can download the file by opening the CSV file and saving it. (Yes it was 2 in the morning )

The post Run shell script in Workspace appeared first on Oracle - Hyperion Labs.

2017 – A Groovy year

Celvin Kattookaran — Tue, 09 Jan 2018 16:30:20 +0000

21st century got matured, and I think it is a bit late for a 2017 review

When I look back, 2017 was an eventful year for me. I wrote 26 blog posts, spoke at KScope, was the flag bearer for EPBCS Groovy and On-Prem Groovy scripting. There are some events which I don’t want to remember (you know what I’m talking about) and wish you never go through that in your career.

Let’s look at how this blog performed.

Almost 38k users visited this site, and that generated 66k sessions. Thanks to you gentle reader that % has gone up.

Let’s look at some details.

That is an excellent heartbeat 45.1% of you guys are new visitors. English (US & UK English) is the top language followed by French, Spanish, and Brazilian Portuguese.

Thank you (or should I say Je vous remercie or Gracias or even Obrigado)

Now let’s look at how you all landed here. I’m sure that someone fooled you

Looking for the referrals and other acquisition data, I found that my big brother who went to the other side doesn’t love me like he used to

Brian, the weekly offender list has helped a lot.

Pete, Tim, and others thank you for all the referrals.

Countries

This I can say “I told you so”

The United States without any doubt got the largest EPM customers and obviously, India is going to come second. (Is that going to change next year, we’ll see)

The UK comes third. Canada I love you guys. France – Never thought someone from France is reading this blog.

Austraila, It’s Pete trying to find whether I made a mistake.

Brazil, I guess that’s Rodrigo and Ricardo following Pete’s footsteps

Desktop is showing a downward trend, that was expected. ~~I’m using a Surface myself~~ (I think Surface is considered as a Laptop, my bad)

I was interested to see what Mobile devices are you guys using.

iPhone rules

Someone using Xbox, that’s some dedication, I would love to know who you are. I swear that’s not me (I’m a PS4 guy)

Top 10 posts

Essbase language for Notepad ++ comes first, that reminds me that I need to update the new functions and the Snippet plugin.

People are looking for auditing features, can we please get better auditing Oracle. Its better on cloud!!!

Block creation is still a headache and surprisingly Groovy comes next, I did mention that there will be a tomorrow where block creation is a thing of the past. Embrace Groovy

People are still looking at how to format data exports, can we please get formatting with DATAEXPORT, man can only dream!!

Executing MaxL from a rule, yes why not go Groovy and you can do anything (Call a SQL procedure, send an email, update a table, run a batch/shell script oh yeah all these are possible.) I’m working on a Groovy Snippet series and you’ll see all of these there.

Run a batch file from workspace – this is a common question I get all the time after publishing the series of execute from workspace series. I’m working on a shell version of this one. (Need to install Linux on Virtual box)

Exporting users and provisioning – that’s a dead one, Jim unless you are still on 11.1.1.x version. You can either export using LCM or use NUMSys. Well, that reminds me that I must finish the NUMSys web version with Glassfish.

Encrypting MaxL – well I don’t really like the way encryption is handled in MaxL

I still hope to write 2 blog posts a month and hopefully there will be more “groovy” posts. I wish you all a Happy New Year (Well, it’s just second week of Jan. I still can, can’t I )

The post 2017 – A Groovy year appeared first on Oracle - Hyperion Labs.

Essbase Member Operation Utility – Rename Aliases and Exception handling

Celvin Kattookaran — Thu, 14 Dec 2017 21:58:20 +0000

I never thought I’d add an update to member operation utility

Now you might be thinking why this update?

Akram, who was using Essbase Member operation utility was asking for an ability to bulk update aliases. My response was “Why don’t you use a load rule and perform the update?”

His use case is more evolved, and he is looking to allow some users to update aliases of existing members. Yes, it can be still done using load rules.

However, what if the user adds a level 1 member instead of level 0, what if they use a non-existing member.

Adding level 1 or higher level member and updating aliases work fine.

The second case the non-existing member will be added as a new member.

In short, he was able to convince me to come up with this update

From this version of EsbMbrOperation utility, you get a new option renamealias

You can supply bulk alias rename operation as a commas separated file. The format is as follows

Alias table, Member name, New Alias

If no alias table is given, the default alias table will be used for the update operation.

As usual, you can encrypt the whole script using the following command.

[plain]

java -jar lib\EsbMbrOperation.jar -encrypt=”-u=admin -p=password -S=localhost -App=Sample -Db=Basic -file=C:\AliasChange.txt -renamealias”

[\plain]

Once encrypted use the encrypted string in the bat/shell script.

While I was at it, I found that sometimes you might want to continue the process even if there is an exception. As you can see from the above screenshot since the member “test” is not found, it’ll stop the process and won’t rename the aliases of Jan and Feb.

From this version, you get an option, not to abort the process. The utility will just report the error message and continue with the process.

You can download the latest version here.

The documentation can be found here.

The post Essbase Member Operation Utility – Rename Aliases and Exception handling appeared first on Oracle - Hyperion Labs.