Archive for Hyperion

« Older Entries

DEVEPM will be at Kscope18!

Posted in ACE, DEVEPM, Essbase, Kscope 18, ODI, ODTUG with tags , on March 14, 2018 by Rodrigo Radtke de Souza

Hi all, how are you doing? With some delay (same as last year, I was on vacation 🙂 ) we are very happy to announce that DEVEPM will be once again at KScope! We are very honored to be selected to present on the best EPM conference in the word! We got two presentations in, so here is what we are going present at Kscope18:

Dynamic Metadata Integrations for Multiple ASO Applications

Metadata management is a challenge on complex environments, since it is always changing over time. If the metadata process is not build in a robust and dynamic way, we will end up having a lot of rework every time that the business changes.

One of the best ways to maintain metadata on ASO cubes is using ODI, which gives us great flexibility to create real complex enterprise ETL processes. But if we use ODI in its standard way, we will end up having multiple similar ODI objects, since each ASO application/dimension is tied to a particular ODI data store. In other words, the higher the number of ASO applications we have, the higher is the number of ODI objects, increasing the possible failure points and code rework if something changes, which will make us lose a lot of time, money and trust on those systems.

This study case describes how to implement a smart EPM environment that uses Oracle Data Integrator with Oracle Essbase and take full advantage of its potential. This session will show how to create dynamic processes that changes automatically for any number of Essbase applications, allowing metadata maintenance that meets the business needs with low development costs.

Incredible ODI tips to work with Hyperion tools that you ever wanted to know

ODI is an incredible and flexible development tool that goes beyond simple data integration. But most of its development power comes from outside the box ideas.

  • Did you ever wanted to dynamically run any number of “OS” commands using a single ODI component?
  • Did you ever wanted to have only one datastore and loop different sources without the need of different ODI contexts?
  • Did you ever wanted to have only one interface and loop any number of ODI Objects with a lot of control?
  • Did you ever need to have a “Third Command Tab” in your procedures or KMs to improve ODI powers?
  • Did you still use an old version of ODI and miss a way to know the values of the Variables in a scenario execution?
  • Did you know that ODI has 4 “Substitution Tags”? And do you know how useful they are?
  • Do you use “Dynamic Variables” and know how powerful they can be?
  • Do you know how to have control over you ODI priority jobs automatically? (Stop, Start and Restart scenarios)

If you want to know the answer of all this questions please join us in this session to learn the special secrets of ODI that will take your development skills to the next level.

ys2header

Kscope is the largest EPM conference in the world and it will be held in Orlando on June 2018. It will feature more than 300 technical sessions, five symposiums, deep dive sessions, and hands on labs over the course of five days.

Got interested? If you register by March 29th you’ll take advantage of the Kscope early bird rates. Don’t waste more time and let’s be part of the greatest EPM event in the world. If you are still unsure about it, read our post about how Kscope/ODTUG changed our lives! Kscope is indeed a life changer event!

 

Leave a comment »

Automating Essbase Copy Outline Operation using Java API

Posted in ACE, BSO, Cubes, Essbase, Hacking, Hyperion Essbase, Java, Migration, Oracle with tags , , , , , , on August 9, 2017 by RZGiampaoli

Hi guys how are you? Did you guys ever tried to automate the process of coping a cube outline from one application to another?

Well, there’s an easy way to do that. Basically you copy the .otl from the server file system over the other cube. The problem is that if the cube is not empty, the database becomes corrupted since we just replaced an .otl file for another strange .otl file (no restructure happened).

Then if you want to copy the outline to an existing cube (that has data) this is not a solution.

The thing is, the only two possible ways to do what we want is the EAS “Save as” operation and the migration wizard. These both operations work because they copy the .otl file as .otn and then run a restructure in the database. The restructure “synchronize” the cube with the new outline, making the process safe for a cube that has data on it.

The problem is, none of these can be automated and there’re no way to do this operation using Maxl or EssCmd.

In fact, even using the Java API, it’s hard to figure out how to do that because all the copy methods seem to copy all kind of objects but the outline.

The good news is, we figured out a way to replicate the “Save as” operation using the Java API after hours of frustration and tears…

Here we go:

Save As Java code

The code is really simple. We need to connect in the essbase server, lock the target outline (the one we’ll overwrite) and then copy the outline from one application to another. To do that we are going to use the functions “lockOlapFileObject” and “copyOlapFileObjectToServer”.

This process that we just described will create an .otn file in the target cube. Now comes the great catch of this code (that is not documented anywhere):

If we open the target outline in EAS we will still see the old metadata. To commit the changes, we need to perform a restructure to merge the new outline (.otn) with the old one (.otl) updating the metadata.

To do that we are going to use the functions in the class “IEssCubeOutline” to “open”, “restructureCube” and “close” the target outline.

That is it. This process will do exactly what the “Save As” in EAS does, which means that you can copy outlines from one application to another even when the target database contains data.

I hope you guys enjoy and see you soon.

1 Comment »

ODI KMs for HFM 11.1.2.4

Posted in 11.1.1.9.0, ACE, Configuration, DEVEPM, ETL, Hacking, HFM, Knowledge Models, ODI, ODI 11g, ODI Architecture, Uncategorized with tags , , , , , on March 3, 2017 by RZGiampaoli

Hi guys how are you? Today we are proud to announce that we are making available the ODI KMs for HFM 11.1.2.4.

—- EDITED on June/17 —-

We developed these KMs around 6 months ago, but we were waiting to release them together with an article that we wrote for Oracle.

Since OTN had some “Priority changes”, our article was postponed to later this year. As we had some people asking for these KMs we decide to release the KMs now and when the article is published we will let you guys know as well.

The article is live here! And if you guys are having errors with our KMs, please check our troubleshooting post here.

—- EDITED on June/17 —-

Prior to version 11.1.2.4, ODI could be easily used for HFM integration processes. ODI used its KMs with specific HFM drivers (HFMDriver.dll) provided by Oracle that were used to access and manipulate HFM applications. However, on HFM’s latest version, Oracle decided to remove its support for ODI, meaning that all HFM integrations would have to move from ODI to either manual iteration with HFM, usage of another integration tool (Like FDMEE) or create custom code using the new Java HFM API.

Since we didn’t want to re-write all our ODI environment and also none of the above options are robust enough, we decided to recreate the ODI KMs using Java HFM API. For these KMs to work we need to do two things: import them from ODI Java Net and do some setup in the ODI agent.

In the article we explain all options and how do we came up with this solution, but here we will not talk about it since we want you guys to read our article as well and we can’t use the content of the article here since we already signed an exclusivity agreement with Oracle.

The first part is easy and you just need to download the files from the link below

ODI KMS for HFM 11.1.2.4

The second one is more difficult. We need to make the new HFM Jars available to the ODI Agent and in order to do so we have two options:

Install the agent in the HFM machine OR copy the necessary jar files to the agent drivers folder (oracledi\agent\drivers).

If your architecture allows to have both HFM and ODI agent in the same server, then you may use this approach, which is very simple. The only thing to do is to change odiparams file (oracledi\agent\bin\odiparams.bat file in a standalone agent) and add the location of those three HFM jar files. Open odiparams.bat file and search for “ODI_ADDITIONAL_CLASSPATH”. On that setting, just set the location of the HFM jar files, as below (this is just an example. Please adjust the path accordingly to your environment):

set ODI_ADDITIONAL_CLASSPATH=%ODI_ADDITIONAL_CLASSPATH%;

“D:\Oracle\Middleware\EPMSystem11R1\common\jlib\11.1.2.0\epm_j2se.jar”;

“D:\Oracle\Middleware\EPMSystem11R1\common\jlib\11.1.2.0\epm_thrift.jar”;

“D:\Oracle\Middleware\EPMSystem11R1\common\jlib\11.1.2.0\epm_hfm_server.jar”

Save the file, restart the ODI agent and it is done

If you decide to go with the second option, we’ll provide a list of all the necessary jars (be prepared… it’s huge). In the article we explain how to identify all the necessary jar files in a systematic way but here this is not an option as explained before.

Search for all the Jars in the below list and copy all of them under oracledi\agent\drivers folder.

adm.jar
admaps.jar
admodbo.jar
ap.jar
ArtifactListing.jar
audit-client.jar
axiom-api-1.2.10.jar
axiom-impl-1.2.10.jar
axis-ant.jar
axis-jaxrpc-1.2.1.jar
axis.jar
axis2-adb-1.5.4.jar
axis2-kernel-1.5.4.jar
axis2-transport-http-1.5.4.jar
axis2-transport-local-1.5.4.jar
backport-util-concurrent.jar
broker-provider.jar
bsf.jar
castor-1.3.1-core.jar
castor-1.3.1.jar
com.bea.core.apache.commons.collections_3.2.0.jar
com.bea.core.apache.commons.net_1.0.0.0_1-4-1.jar
com.bea.core.apache.commons.pool_1.3.0.jar
com.bea.core.apache.log4j_1.2.13.jar
com.bea.core.apache.regexp_1.0.0.0_1-4.jar
com.bea.core.apache.xalan_2.7.0.jar
com.bea.core.apache.xml.serializer_2.7.0.jar
com.oracle.ws.orawsdl_1.4.0.0.jar
commons-cli-1.1.jar
commons-codec-1.4.jar
commons-compress-1.5.jar
commons-configuration-1.5.jar
commons-dbcp-1.4.0.jar
commons-discovery-0.4.jar
commons-el.jar
commons-fileupload-1.2.jar
commons-httpclient-3.1.jar
commons-io-1.4.jar
commons-lang-2.3.jar
commons-validator-1.3.1.jar
cpld.jar
css.jar
cssimportexport.jar
ctg.jar
ctg_custom.jar
dms.jar
epml.jar
epm_axis.jar
epm_hfm_web.jar
epm_j2se.jar
epm_jrf.jar
epm_lcm.jar
epm_misc.jar
epm_stellant.jar
epm_thrift.jar
essbaseplugin.jar
essbasestudioplugin.jar
ess_es_server.jar
ess_japi.jar
fm-actions.jar
fm-adm-driver.jar
fm-web-objectmodel.jar
fmcommon.jar
fmw_audit.jar
glassfish.jstl_1.2.0.1.jar
hssutil.jar
httpcore-4.0.jar
identitystore.jar
identityutils.jar
interop-sdk.jar
jacc-spi.jar
jakarta-commons.jar
javax.activation_1.1.jar
javax.mail_1.4.jar
javax.security.jacc_1.0.0.0_1-1.jar
jdom.jar
jmxspi.jar
jps-api.jar
jps-common.jar
jps-ee.jar
jps-internal.jar
jps-mbeans.jar
jps-unsupported-api.jar
jps-wls.jar
js.jar
json.jar
jsr173_1.0_api.jar
lcm-clu.jar
lcmclient.jar
LCMXMLBeans.jar
ldapbp.jar
ldapjclnt11.jar
libthrift-0.9.0.jar
log4j-1.2.14.jar
lucene-analyzers-1.9.1.jar
lucene-core-1.9.1.jar
lucene-spellchecker-1.9.1.jar
neethi-2.0.4.jar
ojdbc6dms.jar
ojdl.jar
opencsv-1.8.jar
oraclepki.jar
org.apache.commons.beanutils_1.8.3.jar
org.apache.commons.digester_1.8.jar
org.apache.commons.logging_1.1.1.jar
osdt_cert.jar
osdt_core.jar
osdt_xmlsec.jar
quartz.jar
registration_xmlBeans.jar
registry-api.jar
resolver.jar
saaj.jar
scheduler_ces.jar
servlet-api.jar
slf4j-api-1.5.8.jar
slf4j-log4j12-1.5.8.jar
sourceInfo.jar
stax-api-1.0.1.jar
wf_ces_utils.jar
wf_eng_agent.jar
wf_eng_api.jar
wf_eng_server.jar
wldb2.jar
wlpool.jar
wlsqlserver.jar
wsplugin.jar
xbean.jar
xmlparserv2.jar
xmlpublic.jar
xmlrpc-2.0.1.jar
XmlSchema-1.3.1.jar

Restart the ODI agent and it should be ready to execute any HFM Java code inside of ODI.

I know that this is a lot of jars and will take some time to find all of them but at least you’ll be able to upgrade you HFM and still use the same interfaces you have today in ODI to manage HFM (just remember to use the new data store objects reversed from the new RKM).

The KM usage is very similar to the old ones and we had the instructions in all its options so we’ll not explain then here (just in the article). The only important difference is on how to setup the “Cluster (Data Server)” information on Data Server (Physical Architecture). For the new HFM API, we need to inform two new settings: Oracle Home and Oracle Instance Paths. Those paths are related to the server where your HFM application is installed. These settings will be used internally in HFM API to figure out all HFM information related to that specific HFM instance.

Due to these two new settings and in order to continue to accommodate all connection information within a single place (ODI Topology), “Cluster (Data Server)” was overloaded to receive three settings instead of just one, separating them by colon. So now “Cluster (Data Server)” receives “dataServerName:oracleHomePath:oracleInstancePath” instead of just dataServerName.

data-server

Having those considerations in mind, it is just a matter to create a new Data Server and set the overloaded “Cluster (Data Server)” information and the user/password that ODI will use to access the HFM application. After that, we just need to create a Physical Schema with the name of the HFM application, a new Logical Schema and associate that to a context.

And that is it, you guys are ready to upgrade your HFM environment and still use your old ODI interface to maintain HFM. If you guys have any doubts/suggestions about the KMs please few free to contact us.

If you guys are having errors with our KMs, please check our troubleshooting post here.

I hope you guys enjoy these KMs. See you soon!

23 Comments »

DEVEPM on OTN 2MTT (Kscope16 Preview)

Posted in 11.1.1.9.0, 11.1.2.4, 2MTT, ACE, ArchBeat, ASO, BSO, Configuration, EPM, Essbase, Essmsh, ETL, Hacking, Hyperion Essbase, Hyperion Planning, Kscope 16, MaxL, ODI, ODI 10g, ODI 11g, ODI 12c, ODI Architecture, Oracle, Oracle Database, OS Command, OTN, Uncategorized with tags , , , , , , , , , , , , , , , , , on May 5, 2016 by RZGiampaoli

Hi all! Quick post today. DEVEPM recorded another OTN 2MTT. Now we are talking about “Incredible ODI Tips to Work with Hyperion Tools (Kscope16 Preview)”. In this 2MTT we give an idea of what will be one of our session at Kscope 16 that will happen on June 27, 2016, 4:30 PM – 5:30 PM. Take a look and join us at Kscope 16!

Thank you guys and see you soon 🙂

2MTT

Leave a comment »

Let’s Join DEVEPM @ KSCOPE 16

Posted in ACE, EPM, Essbase, ETL, Hyperion Essbase, Hyperion Planning, InfraStructure, Kscope 16, ODI, ODI 10g, ODI 11g, ODI 12c, ODI Architecture, ODTUG, Oracle Database, OS Command, Performance, Tips and Tricks with tags , , , , , , , , , , , , on April 5, 2016 by RZGiampaoli

Hi Guys how are you?

Just a quickly post about this year KSCOPE. This year we’ll have 2 excellent sessions:

Take a Peek at Dell’s Smart EPM Global Environment:

Ricardo Giampaoli , TeraCorp

Co-presenter(s): Rodrigo Radtke de Souza, Dell

When: Jun 27, 2016, Session 2, 10:15 am – 11:15 am

Topic: EPM Applications – Subtopic: Planning

In a fast-moving business environment, finance leaders are successfully leveraging technology advancements to transform their finance organizations and generate value for the business.
Oracle’s Enterprise Performance Management (EPM) applications are an integrated, modular suite that supports a broad range of strategic and financial performance management tools that help business to unlock their potential.

Dell’s global financial environment contains over 10,000 users around the world and relies on a range of EPM tools such as Hyperion Planning, Essbase, Smart View, DRM, and ODI to meet its needs.

This session shows the complexity of this environment, describing all relationships between those tools, the techniques used to maintain such a large environment in sync, and meeting the most varied needs from the different business and laws around the world to create a complete and powerful business decision engine that takes Dell to the next level. 

Incredible ODI Tips to Work with Hyperion Tools

Ricardo Giampaoli , TeraCorp

Co-presenter(s): Rodrigo Radtke de Souza, Dell

When: Jun 27, 2016, Session 6, 4:30 pm – 5:30 pm

Topic: EPM Platform – Subtopic: EPM Data Integration

ODI is an incredible and flexible development tool that goes beyond simple data integration. But most of its development power comes from outside-the-box ideas.

  • Did you ever want to dynamically run any number of “OS” commands using a single ODI component?
  • Did you ever want to have only one data store and loop different sources without the need of different ODI contexts?
  • Did you ever want to have only one interface and loop any number of ODI objects with a lot of control?
  • Did you ever need to have a “third command tab” in your procedures or KMs to improve ODI powers?
  • Do you still use an old version of ODI and miss a way to know the values of the variables in a scenario execution?
  • Did you know ODI has four “substitution tags”? And do you know how useful they are?
  • Do you use “dynamic variables” and know how powerful they can be?
  • Do you know how to have control over you ODI priority jobs automatically (stop, start, and restart scenarios)?

If you want to know the answer to all this questions, please join us in this session to learn the special secrets of ODI that will take your development skills to the next level.

Join us in KSCOPE 16 and book our 2 sessions in schedule. They will be very good sessions and I’m sure that you’ll learn some new stuff that will help you in your EPM Environment!

SpeakerSquare (1)

Leave a comment »

Remotely Ziping files with ODI

Posted in 11.1.1.9.0, ACE, Configuration, EPM, Essbase, ETL, Hacking, Hyperion Essbase, InfraStructure, ODI, ODI 10g, ODI 11g, ODI 12c, ODI Architecture, ODI Architecture, OS Command, Performance, Remotely, Tips and Tricks, Zip Files with tags , , , , , , , , , , , on April 5, 2016 by RZGiampaoli

Hi guys how are you? It has been a long time since last time I wrote something but it was for a good reason! We were working in our two Kscope sessions! Yes, this year we will have 2 sessions and I think they will be great!

Anyway, let us get to the point!

Today I want to talk about something that should be very simple to do it but in the end, it is a nightmare…. Zip a file in a remote server…

A little bit of context! I was working in a backup interface for one client and, because their cubes are very big, I was trying to improve the performance as much as I can.

Part of the backup was to copy the .ind and .pag files and the data extract files as well. For an app we are talking in 30 gb of .pag and 40 gb of data extract files.

Their ODI infrastructure is like this:

Infrastructure

Basically I need to extract/copy data from Essbase server to the disaster recovery server (DR Server). Nothing special here. The problem is, because the size of the files I wanted to Zip the files first and then send it to the DR server.

If you use the ODI tools to Zip the file, what it does is bring all the files to the ODI Agent server, zip everything and the send it back. I really do not want all this traffic in the network and all the time lost in this process (also, the agent server is a LOT less powerful then the Essbase server).

Regular odi tools zip process

Then I start to research how I could do that (and thank you my colleague and friend Luis Fernando Cairo that help me a lot doing a lot of tests on this)

First of all we have three main options here:

  1. Create a .bat file and run it remotely: I did not like it because I do not want a lot of .bats all over the places
  2. Use windows invoke command: I need a program in the server like 7 zip or so and I don’t have access to install freely and I do not want to install zip’s program all over the places too
  3. Use Psexec to execute a program in the server: Same as the previous one.

Ok, I figure out that in the end I’ll need to create/install something in the server… and I rate it. Well, let’s at least optimize the problem right!

Then I was thinking, what I have in common in all Hyperion servers? The answer is JAVA.

Then I thought, I can use the JAR command to zip a file:

jar cfM file.zip *.pag *.ind

Where:

c: Creates a new archive file named jarfile (if f is specified) or to standard output (if f and jarfile are omitted). Add to it the files and directories specified by inputfiles.

f: Specifies the file jarfile to be created (c), updated (u), extracted (x), indexed (i), or viewed (t). The -f option and filename jarfile are a pair — if present, they must both appear. Omitting f and jarfile accepts a “jar file” from standard input (for x and t) or sends the “jar file” to standard output (for c and u).

M: Do not create a manifest file entry (for c and u), or delete a manifest file entry if one exists (for u).

Humm, things start to looks better. Now I had to decide if I would use the Invoke command or Psexec.

I started trying the Invoke command, but after sometime I figure out that I can’t execute the jar command using invoke.

Then my last alternative was Psexec.

The good thing about it is that is a zip file that you need just to unzip in the agent server, set it in the Environment Variables (PATH) and you are good to go.

It works amazingly.

You can run anything remotely with this and it’s a centralized solution and non-invasive as well (what I liked).

You just need to:

psexec \\Server  -accepteula  -w “work dir” javapath\jar cfM file.zip *.pag *.ind

Where:

-w: Set the working directory of the process (relative to remote computer).

-accepteula: This flag suppresses the display of the license dialog.

There’s one catch, for some unknown reason, the ODI agent does not get the PATH correctly then you need to use the complete path where it was “Installed”. The ODI is like this:

OdiOSCommand “-OUT_FILE=Log_Path/Zip_App_Files-RUM-PNL.Log” “-ERR_FILE =Log_Path /Zip_App_Files-RUM-PNL.err”

D:\Oracle\PSTools\psexec \\server -accepteula -w \\arborpath\APP\RUM\PNL\ JAVA_PATH\jdk160_35\bin\jar cfM App_Files-RUM-PNL.zip *.pag *.ind

With this, we will have a process like this:

Remotly Zip Process

This should not be something that complicate but it is and believe me, I create a very fast process and the client is very happy.

I hope you guys enjoy it and see you soon.

3 Comments »

Reviewing “Developing Essbase Applications – Hybrid Techniques and Practices” from an ETL Architect perspective

Posted in Book, EPM, Review with tags , , , , , on January 8, 2016 by Rodrigo Radtke de Souza

Hi guys! Today we are very happy to be reviewing “Developing Essbase Applications – Hybrid Techniques and Practices” for you. We will review the book not once, but twice and the reason behind that is that Ricardo and I (Rodrigo) have different backgrounds and this creates very different opinions around the book topics.

My professional career comes much more strongly from an ETL Architect position, so although I know the basics and principals around Essbase, for me it was always one more target/source system to pull/retrieve data from. Ricardo in the other hand has his major years working with EPM space and he knows/uses Essbase at its fullest. So based on that, let’s see how our reviews will differ from one another!

51QGgxNE-vL._SX340_BO1,204,203,200_

ETL Architect perspective review

“Developing Essbase Applications – Hybrid Techniques and Practices” may be considered the “second” book of the “Developing Essbase Application” series (“Developing Essbase Applications: Advanced Techniques for Finance and IT Professionals” is the first one and it is a must to have!) but it is completely different from the first book, since it cover different topics with some different authors. Speaking on topics, this is what you will find in “Hybrid Techniques and Practices”:

  • Exalytics
  • Hybrid Essbase
  • Young Person’s Guide to Essbase Cube Design
  • Essbase Performance and Load Testing
  • Utilizing SQL to Enhance Essbase
  • Integrating OBIEE and Essbase
  • Managing Spreadsheets with Dodeca
  • Smart View

The books is edited by Cameron Lackpour along with John Booth, Tim German, William Hodges, Mike Nader, Martin Neuliep, and Glenn Schwartzberg. Those names are more than a reference in the EPM community and it is great to see great minds putting their effort to create this book. We know that anything in life (it can be a book/work/presentation/project) will be great when the people that are involved in that process are passionate about what they do and this is well represented here. All these people just love EPM/Essbase and this passion alongside with their great knowledge about the topics created a fantastic book.

The chapters in the book are well separated, so you may read any topic in any order that you prefer. First topic will talk about Exalytics and its “Secret Sauce”. Hardware aficionados will love this chapter, but “not-hardware guys” like me may fell kind of lost. I got that Exalytics is great and do great things for Essbase, but all metrics and hardware comparison tables I’ll just leave for the hardware/environment guys 🙂

The second chapter for me is the shiny gem of the book and it alone is already worth the cost of the book (it couldn’t be different, since this chapter is also the book’s title). Hybrid is a very new concept in the Essbase world so you will read what it is, its architecture, what you can/can’t do with Hybrid right now and so on. For someone to understand what Hybrid means, he must also understand the concepts of ASO/BSO and these are well covered in this chapter as well. In resume, this is fantastic overview what Hybrid currently is and how to get along with it.

I read the third chapter before I got the book because it is available for free on OTN!  You may download it and read it entirely! This chapter I would recommend to everyone that will have any kind of iteration with Essbase, especially those people that ask requirements to be implemented :). Although the information may be considered “basic” Essbase design, it is the core foundation to have a good Essbase implementation. Fantastic chapter and as I said, it is free, so go there and read it (after you finish to read the rest of the post, of course)!

I was extremely glad to read chapter 4 because it talks about Essbase performance and load test. This kind of subject is very rare to be found and this chapter contains a very good overview on some techniques that could be applied to have a performance test on Essbase. This is a complicated topic since testing Essbase is not a trivial thing. There are too many factors to consider but this chapter does a great job explaining those details and how you could accomplish a good test scenario.

On the very beginning of chapter five, Glenn states that his chapter contains very basic information about SQL and if you know SQL already you may just skip the first part of it. Since SQL is in my blood for some years now, I skipped almost the entire chapter, just reading the end of it when it talks about how to use SQL within Essbase. This chapter is for people that uses Essbase but are now aware of what SQL could do for them. My opinion is that everyone that “works with numbers” should know at the least the basic concepts of SQL and it heavily applies for Essbase users.

Chapter 6 will give you a very good perspective of what you can do when reporting Essbase using OBIEE, its limitations, its good practices and the workarounds that may be used to implement some of the most common requirements that are “not natively” supported by the tool. A great chapter that I’ll just keep coming back to it whenever I have some doubt about the do’s and don’ts around Essbase and OBIEE integration.

I’ll talk about chapter 7 and 8 together since they talk about tools that can be used to interact with Essbase data. I must say that I just use Smart view to check if something got loaded or to do some basic tie out checks, so my iteration with those tools are pretty basic. Chapter 7 talks all about Dodeca and I got some pretty interesting information that I was not aware of, like its architecture, how it differs from other tools and so on. Chapter 8 goes deep into Smart View and tell us how it can be customized to allow the users to retrieve more value from the tool. From my personal perspective I don’t know how much I’ll be able to use it in my daily work but at least now I know where to find some answer if some more advanced Essbase user comes to talk to me about those tools.

Verdict

In resume, this is a great book to have and it will for sure bring you some great information about Essbase and the things that goes around it. My “personal award” goes to chapter two since I was really interested to learn what Hybrid was all about and it got accomplished reading it. One thing that I (of couse) would love to see would be a chapter around data integration but I get why there was none. The first book already talked about ODI integration with Essbase and since then there wasn’t anything new around this topic. At the last Oracle Open World, Oracle gave some insight around new ODI KMs for Essbase and Planning, as well as some new ODI components like Dimensions and Cubes, so maybe we may have something new to write about in a third volume of the book??? DEVEPM will be very willing to even contribute for this writing if that would be the case 🙂

And that’s it folks, I hope you have enjoyed this review. By the next days we will be posting about a more “Essbase experienced” kind of review, so let’s see how it goes!

See ya!

Leave a comment »

Using templates to create dynamic rules in Calcmanager 11.1.2.4

Posted in 11.1.2.4, ACE, BSO, Business Rules, CalcScript, Calculation Manager, Calculation Script, EPM, Essbase, Hacking, Hyperion Essbase, Hyperion Planning, New Features, Oracle, Performance, Templates, Tips and Tricks with tags , , , , , , , , , , , on January 1, 2016 by RZGiampaoli

Hi guys and happy new year!!!

And to start well the new year what’s best then a post?

Today I want to talk about the new version of Calculation manager (11.1.2.4). I know that it is out for a while now but still I think it has some cool features that are not explored.

In all Planning project, sooner or later, we come to a time that we need to create a currency conversion Rule (at least I like to create a custom Rule for performance reasons). Also some companies uses a lot of currencies.

Before continue I need to say that in our case I find out that less code is equal a less performance. What I mean by that is that for the forecast horizon range period for example, instead of use “IF” and test my 15/18 months horizon I triplicate the code using “FIX” and using “SET EMPTYMEMBERSETS ON ;”.

This set command ignores the “FIX” if it returns an empty set. This approach increases the performance a lot, some times more than 8 times (In this currency example, if I ran it at channel level with “IF”, toke 8 hours, with “FIX” takes 1 hours).

Ok that means I rarely use “IF” in my Rules.

Well, you can already imagine the size and row boring and prone error is the Rules if I use only “FIX” right? However, with the “Template” feature in calcmanager and the ability to call any template or rule using a script this nightmare turns in to a dream!

Let us see how it works!

A Currency conversion for forecast applications normally has two parts:

First parts is a period range part.

Second part is the currency conversion itself.

With calcmanager, we can create two template, one for the period and the other for call the currency conversion part.

Then for the Currency conversion calculation, I create a simple core template with just a formula and a script on it:

UDA Loop Template

The “dtp_Quote_UDA”  is a DTP (design time prompt) variable with a function that will insert double quotes in every value that comes from the “dtp_UDA” DTP variable (this will be used to get values from the outside template), this way we can have use just one variable to do two papers, currency name and UDA value. The code is:

@QUOTE([dtp_UDA])

The inside the Currency calculation script we will have:

Currency Script

As we can see inside the script, I used the “dtp_Quote_UDA” as well the “dtp_UDA”. This simplify the amount of parameters I need to pass and the maintenance as well. Let’s think, we need the same information, one with double quotes, for the UDA values and other without quotes, for the Rate name.

With this technic we need to pass just once the value, let’s say BRL, and in the code Calcmanager will replace before the execution in all places, and we’ll have @UDA(Entity,”BRL”) as well HSP_Rate_BRL.

This is awesome because now I have just 8 line of code that will be transformed in any amount of times I want. The best thing is, or everything is right or everything is wrong J

Because calcmanger now we have a layer between the code written and the code generated, and this is pretty cool because opens a huge windows for creativity. You can even generate the entire code dynamically.

Ok, the next step is to loop this template once for each currency we have. For this, I created another template. This one will be used for the Forecast horizon period range as well for loop the currencies.

Period loop template

Again, the code is pretty simple, just  two fixes and one script.

For the “Period FIX” we use two DTP variable to get the value of Year and period from the outside rule ([dtp_Period] and [dtp_Year]).

The product fix is just something related with our architecture and we do not need to bother about it.

Now the “Loop Currency” is a script that will call N number of times our first template. How can we do that with a script?

Basically every time you drag and drop a template inside a rule or to another template behind the graphic design calcmanager generate a command line. This code exists thanks to its API, and you can use it to manipulate and generate almost any kind of code inside calcmanger.

Currency loop template

As we can see, inside the script we have a “Fix” for the USD currency, (that is the only different conversion) and one row for each currency.

Each row is calling a template “%Template(name:=Currency Conversion – 2 – UDA Loop” from an application “application:=”WWOPS””, a plan type, “plantype:=”Pnl””, and is passing two DTS values, one for the UDA and other for the Entity, “dtps:=(“dtp_UDA”:=[[AED]],”dtp_Entity:=[[dtp_Entity]])”.

As you can see, you can pass a DTP variable using the variable itself (dtp_Entity:=[[dtp_Entity]]).

If you want to create this API code and don’t know how to write the right syntax you can just drag your template to a rule/template, set everything and change your view to “Edit Script” or “View Script”.

Edit script

Now we just need to create the rule that will call this template for the three range of periods we have:

Currency rule

Again a simple design with a small amount of components. Here we have our SET commands, a main fix and the three templates, each one calling the previous template for a different period of range.

Period Range

The final result is a Rule with 1213 rows generated from a 8 rows template. This is the magic of calcmanager and templates. You can simplify everything, you can create dynamic aggregations, that will change depending of the application and cube, you can create codes that changes depending of the member that is coming from the forms, everything with small set of code that is reusable anytime we want!

Rule code 1Currency code 2

…….

A dynamic way to build a currency rule in calcmanager. A lot faster to build and a lot easier to maintain, since if a new currency start to be used you just need to copy and paste one line in “Currency Loop” script, change the currency and it’s done.

Build Rules using templates looks more work and some time a little bit complicate but I remember well how much time I expend changing BRs and I can guarantee that this way is much faster and easier to develop and mainly to maintain.

In the end we just create a Rule and two templates that contains just one core calculation, in my case a script calling 47 times this core, some fixes, and that’s all. It was less than 60 rows of written code to generate 1213 rows. Pretty good for me 🙂

Rules ante templates

Hope you guys enjoy and I wish a happy new year for all and you dears ones.

Happy new year!!!! A new year full of surprises!

 

9 Comments »

DEVEPM 2015 in review

Posted in 2015, ACE, EPM, KScope 15 with tags , , , , , , on December 31, 2015 by Rodrigo Radtke de Souza

Hi all readers! Another year has gone and it was a great (if not the greatest) year for DEVEPM! We have done a lot of cool stuff this year like get nominated for the Oracle ACE Program, being at Kscope15, 2MTT for Oracle OTN, created articles for ODTUG’s Techinical Journal and Oracle OTN, got selected (twice) for Kscope16 and even Ricardo got married this year! An of course a lot of other EPM related topics 🙂

I would like to thank each one of you for accessing/reading/sharing our blog posts! I hope that we could positively impact your lives somehow with any of our post. That’s the reason why we blog: we want to somehow share our knowledge back to the community that always helped us in difficult times!

And 2016 will be no different! We will work even harder to bring new content about EPM world for you! Thanks again and see you soon!!!!

Follow us on Twitter: @RodrigoRadtke @RZGiampaoli @DEVEPM

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 25,000 times in 2015. If it were a concert at Sydney Opera House, it would take about 9 sold-out performances for that many people to see it.

Click here to see the complete report.

Leave a comment »

Article “Unleashing Hyperion Planning Security Using ODI” was published in OTN

Posted in Hyperion Planning, ODI, Configuration, Security with tags , , , , , , , , , , , , , on July 16, 2015 by RZGiampaoli

Hi guys!

OTN just published a new article from devepm entitled “Unleashing Hyperion Planning Security Using ODI”.

This article is all about Planning security and how you can use ODI to create cell level security based in anything you like (in our case is Attribute Dimension).

This is a study case and it’s in production for 3 years right now.

Please take a look and let us know your thoughts.

Thank you and see you soon.

1 Comment »

« Older Entries