Rittman Mead - Delivered Intelligence

As mentioned in my previous post here, EPM 11.1.2.2 has introduced a bevy of new features that customers have been asking for a while. Also, 11.1.2.2 theoretically is the actual Fusion release as all components now use ADF UI natively. In today’s post we shall cover the new features introduced in Planning 11.1.2.2.

UI Enhancements:

ADF branding is a lot more apparent with the 11.1.2.2 release. For example, all forms now use the native ADF based prompts, POVs and selectors. Also, the UI has been enhanced to give “Concertina” style menus so that it is easier to access related & relevant objects

Adhoc Grids use more of ADF features like swapping columns from rows to columns, pages to columns etc. Adhoc grids are more like BI EE 11g in UI.

Cell Data History:

This is one feature that planners and people who are part of the approval chain will really like i.e. showcase who has modified what data. Generally when plans are submitted for approval, the approver has to go through all the cells and find out what has changed manually. But with this feature its a lot easier to find out who has modified what cells. This can result in faster approvals and reduced effort spent.

For this to work Auditing at Cell Data level needs to be turned on.

Charts in Planning Forms:

Another key feature is in the ability to display the forms as charts in a Composite form. This way planners can immediately visualize the change in data.

More important drills are available on graphs as well – Now we need this capability in BI EE i.e. here the drills are all native Essbase drills (unlike in BI EE where we have to go through the RPD).

Grid Diagnostics:

Another significant new feature is the ability to find out poorly performing/designed forms by using the Grid Diagnostics feature. This will let us know the page load times, number of row retrieved, suppressed etc. Pretty handy tool especially for Administrators

Substitution Variables Management from Planning:

Another excellent & long-pending feature is the ability to manage & update the substitution variables used per plan type directly from Planning App. There is no more dependency on Essbase/EAS (of course they still reside there but no need to create there first in EAS) to create the variables

Predictive Planning & Crystal Ball:

Another very interesting new feature is the ability to predict future plan values using Crystal Ball (or called as Predictive Planning now). It is natively integrated into Smart View. By just clicking on a cell within a form, one can start predicting what will be the plan values. Crystal Ball provides a comprehensive set of statistics as well while providing a recommended value (on what basis the recommendation was done).

In addition there are other small enhancements like Group Based Approvals, Rolling Forecasts (Setup at form level), Multiple cell document attachments, De-support of Business rules etc. Also, from an architecture standpoint we can now have multiple Planning Managed Servers in a cluster thereby making it truly Highly Available. In the next post, we shall see the new features in HFM 11.1.2.2.

Firstly hello, I’m James Knight.  Having recently joined Rittman Mead I was pleasantly surprised to find that we are encouraged to share our knowledge and experiences via this blog.  So hence, here I am.

You may have heard of Oracle Real-Time Decisions (RTD), but aren’t fully aware of what it’s about.  Why you are reading my waffle is that I’ve led a number of RTD implementations.  Additionally, I’ve been fortune enough to spend time with other RTD customers and with the RTD product team, so in theory I should have some knowledge to share.

I thought you might be interested in finding out more as the product is going down a storm with customers that have implemented it.  For example, a global financial services organisation with 10 million customers visiting its website in one month generated 100% conversion uplift, and one of the largest US retailers with over 70,000 products achieved a 25% lift in sales rates compared to control.

So, what is this RTD?  Oracle say… “The RTD platform combines both rules and predictive analytics to power solutions that provide real-time automated decisions”.  I think of RTD as a Q&A box, which can answer any question.  In order to do this, I need to provide the system with all the possible answers or choices and input data (for example, customer data).  Each time the system is asked for a decision it will provide the most relevant choice by scoring all of the available choices based on the input data.  I can automate the upload of the inputs and the choices (for example, by integrating with CMS solution for the available banners and associated metadata).  Sounds simple, but under the covers there is a ton of clever stuff going on and lots of clever solutions you can provide through its use.

Having the inside track on this product, I’ll be taking a few articles to share with you how RTD works and why I think it’s so interesting.  Here’s a glimpse of some of the topics we will be covering:

• Potential uses for RTD
• Why Oracle RTD
• When not to use
• Hardware requirements and technical integration
• Project timescale and resource example

We’ll also be getting under the covers of Oracle RTD and showing that you can get inside the magic black box to understand how and why it’s making particular decisions and how you can learn from the data and correlations that RTD makes and use this information within a wider business context.

In my previous post, I provided an introduction to the Extreme BI approach to change data capture from Source → Staging Layer and Source → Foundation Layer. I described the Oracle Next-Generation Reference DW Architecture and how we plan to implement an appropriate solution for agile data warehousing. Now it is time to get into the setup and implementation of Oracle GoldenGate via Oracle Data Integrator 11g.

GoldenGate Replication via ODI 11g: Getting Started

To recap where we left off, our solution begins with Oracle GoldenGate and its real-time replication capabilities, integrating with Oracle Data Integrator 11g and its journalizing change data capture implementation. The setup will be as follows, with the replication from GoldenGate loading both Staging and Foundation layers, and then the ODI CDC moving data through Staging into the Performance layer. We’ll focus on the GoldenGate piece of the solution in this post.

After the install and initial configuration of GoldenGate, installation of ODI, and setup of the databases representing each layer (source, staging, and foundation), I was ready to roll. For reference, I used one of the more detailed GoldenGate blogs, written by Gavin Soorma. The tutorial is very intuitive for the setup of GoldenGate in a Linux environment. Mark Rittman also touched on the subject, pre-ODI 11g, in a blog post here. To learn the integration between ODI and GoldenGate, I worked through a helpful Oracle By Example titled ODI11g: Combining Oracle Data Integrator and Oracle GoldenGate (NOTE: the OBE scripts were developed specifically for ODI 11.1.1.3). When all was installed and ready to go, I had created 3 models and 3 datastores, and was ready for replication.

The three datastores are identical, with the exception of a few additional columns in the Foundation version of the table. These columns will be used to capture information related to each transactional change on the source.

EDW_SCN (System Change Number)
EDW_COMMIT_TIMESTAMP (when the change was committed)
EDW_TRANS_TYPE (DML type for the transaction)

As you can imagine, it might become a bit cumbersome to add 3 new columns to each datastore in your Foundation model, especially if you have hundreds of tables. To speed up development time, I was able to create a simple Groovy script within ODI 11g that loops through each datastore in the model, adding the appropriate columns. I’ll save this one for a future post.

GoldenGate Replication via ODI 11g: Capturing Change

The key to the ODI / GoldenGate integration is a Journalizing Knowledge Module in ODI named “JKM Oracle to Oracle Consistent (OGG)”. The knowledge module has a long list of commands to support the CDC functionality in ODI (Add to CDC, Add Subscriber, Start Journal, etc), including several very specific to the GoldenGate parameter file configuration (hence the OGG in the name). As you will come to see, ODI does most of the heavy lifting for you regarding the GoldenGate extract, datapump and replicat parameter file creation and setup.

By default, the OGG parameter files are set to propagate a change from the source to the target, keeping both tables in sync with each other. The great thing about ODI’s Knowledge Modules is that they are very flexible and can be edited to perform almost any function. By adding a few lines of code to the replicat parameter file generation command, and mapping the transaction history capture columns, we can now use an enhanced version of the JKM to create the Source to Foundation layer replication. My modifications to the JKM were minimal: 1) additional code in one command to allow the tracking of history in the target database by inserting a new record for each change, and 2) an Option to determine whether we output this code during execution or not.

Below you’ll find the modified “Create apply pro (2)” command from my custom JKM. It is a little messy, but a quick comparison with the original and the description below should help you single out the code additions.

OdiOutFile "-FILE=<%= odiRef.getOption("LOCAL_TEMP_DIR") %>/<%= odiRef.getOption("SRC_OGG_OBJECT_GROUP") %>_to_<%= odiRef.getOption("STG_OGG_OBJECT_GROUP") %>/stg/dirprm/<%= odiRef.getOption("STG_OGG_OBJECT_GROUP") %>A#ODI_APPLY_NUMBER.prm" "-CHARSET_ENCODING=<%= odiRef.getOption("CHARSET_ENCODING") %>" -APPEND

<%if(odiRef.getOption("STG_OGG_TRACK_HISTORY_RM").equals("1"))
{%>
map <%= odiRef.getObjectName("L", odiRef.getJrnInfo("TABLE_NAME"),  odiRef.getOption("SRC_LSCHEMA"), "D") %>, TARGET <%= odiRef.getJrnInfo("FULL_TABLE_NAME") %>, KEYCOLS (<%= odiRef.getColList("", "[COL_NAME]", ", ", "", "PK") %>, EDW_SCN)<%if (!odiRef.getOption("NB_APPLY_PROCESS").equals("1")) {%>, FILTER (@RANGE(#ODI_APPLY_NUMBER,<%= nbApplyProcesses %>,<%= odiRef.getColList("", "[COL_NAME]", ", ", "", "PK") %>))<% } %> INSERTALLRECORDS,
COLMAP (
USEDEFAULTS,
EDW_COMMIT_TIMESTAMP = @GETENV("GGHEADER" , "COMMITTIMESTAMP"),
EDW_SCN = @GETENV("TRANSACTION" , "CSN"),
EDW_TRANS_TYPE = @GETENV("GGHEADER" , "OPTYPE")
)
<%} else {%>

map <%= odiRef.getObjectName("L", odiRef.getJrnInfo("TABLE_NAME"),  odiRef.getOption("SRC_LSCHEMA"), "D") %>, TARGET <%= odiRef.getJrnInfo("FULL_TABLE_NAME") %>, KEYCOLS (<%= odiRef.getColList("", "[COL_NAME]", ", ", "", "PK") %>)<%if (!odiRef.getOption("NB_APPLY_PROCESS").equals("1")) {%>, FILTER (@RANGE(#ODI_APPLY_NUMBER,<%= nbApplyProcesses %>,<%= odiRef.getColList("", "[COL_NAME]", ", ", "", "PK") %>))<% } %>;
<%} %>;

The key to tracking history for each change is the INSERTALLRECORDS option, which tells GoldenGate to insert a new record into the target for each transaction. To make this work properly, be sure your database supplemental logging is turned on and set to capture all columns for each changed row.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS

To ensure the columns will be mapped properly, I set the USEDEFAULTS option in the COLMAP component, forcing like-named columns to map automatically. For the EDW_* column mappings, I used the @GETENV function, which returns information about the GoldenGate environment. The GGHEADER information type, from which I grab the COMMITTIMESTAMP (commit timestamp) and OPTYPE (DML operation/transaction type), provides info about the transaction environment of each record. The TRANSACTION information type, from which I get the CSN (the Commit Sequence Number, which for Oracle is the System Change Number), returns information about each source transaction.

I wrapped the creation of the map calls in an IF ELSE statement, using my newly created “STG_OGG_TRACK_HISTORY” option as the condition. This allows us to set the option to “true” when we want to track history and “false” when we do not. My updated JKM is saved as a copy of the original, with the name “JKM Oracle to Oracle Consistent (OGG) RM”. The replicat parameter file created when tracking history is turned on will look like this:

replicat ODIF1A1
userid system,  password oracle
discardfile /epm/ogg_fnd/dirrpt/ODIF1.dsc, purge
sourcedefs /epm/ogg_fnd/dirdef/ODISC.def

map GGS_OWNER.SRC_CUSTOMER, TARGET FOUNDATION.SRC_CUSTOMER, KEYCOLS (CUSTID, EDW_SCN) INSERTALLRECORDS,
COLMAP (
USEDEFAULTS,
EDW_COMMIT_TIMESTAMP = @GETENV("GGHEADER" , "COMMITTIMESTAMP"),
EDW_SCN = @GETENV("TRANSACTION" , "CSN"),
EDW_TRANS_TYPE = @GETENV("GGHEADER" , "OPTYPE")
)
;

GoldenGate Replication via ODI 11g: Setup and Configuration

Out-of-the-box, the “JKM Oracle to Oracle Consistent (OGG)” Knowledge Module allows multiple targets to be loaded from the same source. We’ll reuse this capability, with the new JKM Option “STG_OGG_TRACK_HISTORY” set appropriately for each model.

First, we set all of our options for the setup of the replication from Source to Staging. In the Model STAGING, select the Journalizing tab, set the Journalizing Mode to “Consistent Set”, and choose the new “JKM Oracle to Oracle Consistent (OGG) RM” Knowledge Module, allowing us to set the options. Below I have only listed those options which need to be modified or are of importance to this setup, leaving any not listed as the default value. Note: depending on the OS you are using, the location of your OGG install, and your database names, my values may differ slightly from yours.

LOCAL_TEMP_DIR : /epm/TEMP
SRC_LSCHEMA : SOURCE
SRC_DB_USER : ggs_owner
SRC_DB_PASSWORD : ggs_owner
SRC_OGG_PATH : /epm/ogg
SRC_HOSTNAME : LOCALHOST
SRC_SETUP_OGG_PROCESSES : true
STG_OGG_OBJECT_GROUP : ODIT1
STG_MANAGER_PORT : 7910
STG_OGG_TRACK_HISTORY : false
STG_OGG_PATH : /epm/ogg_stg
COMPATIBLE : 10
ENABLE_ODI_CDC : true

We’ll then add our table SRC_CUSTOMER to the CDC in ODI and add a subscriber, named “ODI”, at the model level.

Finally, we start the journal at the STAGING model level. This will not only create all of the standard ODI journalizing objects, such as the J$ tables and views, but it will also generate the configuration (obey) and parameter files for use in GoldenGate replication. It also, conveniently I might add, creates a ReadMe.txt file that guides you through the GoldenGate replication setup. All files are generated in the temporary file location specified in your JKM options.

After following the ReadMe.txt file instructions, we should be ready for replication. Now, when a record changes in the Source table SRC_CUSTOMER, the change will be propagated through to the Staging table SRC_CUSTOMER, with the change made in-place. Below, I change the Source version of my ADDRESS from “1221 Word St” to “1221 Word Ave”. When I query the Staging table, the update has already been made.

To begin capturing these changes in the Foundation layer database, we need to follow the same steps as with the Staging, but with a few variations.

First, we setup the journalizing in the FOUNDATION model, just as we did for the STAGING model. Then, we set our JKM options.

SRC_SETUP_OGG_PROCESSES : false
STG_OGG_OBJECT_GROUP : ODIF1
STG_MANAGER_PORT : 7911
STG_OGG_TRACK_HISTORY : true
STG_OGG_PATH : /epm/ogg_fnd
ENABLE_ODI_CDC : false

Beyond the more obvious differences, such as the location of the Foundation GoldenGate install and the different Manager port, we have several important options to ensure our change capture replication works. We set the “SRC_SETUP_OGG_PROCESSES” to false, as the source extract is already in place. The new option, “STG_OGG_TRACK_HISTORY”, set to true, will enable the additional code we added to the JKM to be implemented in the creation of the replicat parameter file. Finally, the “ENABLE_ODI_CDC” option is set to false. This will eliminate the creation of the J$ tables and views, and other objects that are used primarily by ODI for journalizing, since we have no plans to build ELT (Extract, Load, Transform) code using the Foundation layer data.

We then add the table to the CDC and start the journal at the FOUNDATION model level. No subscriber is necessary as we are not performing any CDC with the journalizing downstream. Follow the ReadMe.txt file instructions and the replication from Source to Foundation should be up and running.

Now, if I make another couple of changes to the SRC_CUSTOMER table, you can see that the update is again made to the Staging database table in-place. On the other hand, the old record with the previous value still exists in the Foundation database, and there is now a new record inserted into the SRC_CUSTOMER table for each change, with the EDW_* columns loaded with data about each transaction.

With the transactions being replicated via Oracle GoldenGate, we now have a new and improved process for loading the Foundation layer and capturing change. The process setup was straightforward and much less time consuming than creating a set of Source to Foundation Interfaces. The ability for ODI to integrate with GoldenGate provides a simple, yet effective way to implement your change data capture in Extreme BI data warehousing.

2 Weeks back, EPM 11.1.2.2 released with a lot of new features. The number of new features are so much so that it will take at least 8 to 10 posts to go over all them in detail. I thought i will start off with writing about a new feature that is relevant to a project we are executing currently i.e. HPCM Detailed profitability. If you had looked at my previous post on HPCM here(which i will refer to as Standard Profitability), i would have walked through how HPCM works and how Essbase plays a vital part in the functioning of HPCM. Though it is & was a great product, there were certain drawbacks in using Standard Profitability. I will list them below

1. Use of Essbase added un-necessary complication in data loads & reporting

2. Essbase though was very good, the calculation scripts generated by HPCM warranted minimal exception based driver assignments (single-cell driver assignments) as that can slow down the calculations

3. Though the concept of stages was very good, to me for simple models exposing a lot of stages to end users was not only confusing but also results in a bloated Essbase cube.

4. In a 8 stage model with intra stage allocation enabled (and each stage having 3 dimensions), the Essbase database will have a total of 16*3=48 dimensions. A block storage cube with so many such dimensions generally does not scale well at all.

5. Using BI EE to report out of a HPCM cube requires carefully applied filters. It requires a very good understanding of not only Essbase & BI EE but also how HPCM stores data within Essbase.

6. There was no easy way to do a direct integration from relational tables – So all data before getting loaded into HPCM had to be processed externally to fit into Essbase formats.

7. Standard Profitability had a restriction of 3 dimensions per source stage. In many cases we might need more than 3 source dimensions per stage.

With the introduction of Detailed Profitability, Oracle has tried to address all the issues above by moving away & using relational databases for storing input & calculated data, instead of Essbase. According to the docs, this results in extremely high scalability as it supports unto 5 source stage dimensions & upto 25 target stage dimensions. Also, Detailed Profitability supports only 2 stages instead of the standard 8 stages. More importantly, Detailed Profitability is not a replacement for Standard Profitability. Rather it is another way of doing Profitability & Cost Management as Standard Profitability has its own advantages as well. In this post, we shall look at how Detailed Profitability works and how it is different from Standard Profitability.

Concepts of allocation, driver etc remain the same in both types of applications. But the key difference is in the architecture. At a high the architecture for Detailed Profitability is given below

As you see, by default when we create an application, the product install/configuration schema will be used as the Product Metadata schema. This schema will contain all the necessary tables for holding the Metadata like Models, Stages, Dimensions etc. The Model Data Schema replaces the Essbase ASO-BSO cubes in the older Standard Profitability application. So, to create a Detailed Profitability application we start off with building an application from EPMA as shown below

As you notice, we have a small new check box called Detail Application. This is what enables the Detailed Profitability Module. Then as before we start building the dimensions for the new application.

There is one thing we can notice. There is only one Local System dimension called MeasuresDetailed. There is no AllocationType dimension.

Other dimensions and the order of members etc all remain the same.

Lets now open the application and understand the workings of Detailed Profitability. By just opening the application we can notice 3 main things

1. There is no separate Trace Allocations screen – Interesting as this was one of the most important points that Oracle/Partners like us showcase while demoing the product. But again from a practice standpoint i think it is one of the least used features by power users.

2. There are a couple of new features like Model Data Registration, Stage Object Calculation etc which will take a look later in the article

3. Manage Database option is still there though Essbase is not there – Manage Database is used to deploy the reporting views (that supersede Essbase in Detailed Profitability)

The first step in defining the Model is to associate the Model Data Schema to the model. I was sort of expecting a profile to be created to point to a specific database schema, That is not the case. Instead, we will have to create a separate database schema and then create all our necessary source/target tables within that schema. Grant select/update access on all these tables to the main product schema. Once the grant is done, the schemas will start appearing in the Model Summary as shown below

Once we have chosen the Model Data Schema, we will have to register all the tables that we are going to use as a source & target as part of the Model Data Registration. This is where Detailed Profitability scores higher than the Standard Profitability. In most practical customer scenarios, all cost/revenue related data will be coming in directly from ERP sources (like GL). In such cases, it is easier to model them directly on the source tables rather than using a separate Essbase data source. Another most important aspect of the registration process is in ensuring that we have a source and destination measure dimension. A measure dimension is what qualifies the incoming and outgoing costs. Other dimensions are considered as attributes of the measures. We can have the same dimension used for source as well as destination.

As you see there are 3 types of tables

1. Source Tables – Vertical Or Horizontal

2. Destination Tables – Horizontal

3. Lookup Table – Horizontal

A vertical table means the measures are stored in a separate dimension instead of separate table columns (similar to Essbase measure dimension). Horizontal table means all measures are represented as columns. This designation is needed as Essbase is now superseded by Relational Tables.

In the same map the target the HRCostsAllocation table to Target Horizontally oriented table type.

Few things to remember while mapping a Destination Stage Table – It has to have a Working column with a numeric data type. It also should have a primary key constraint.

We now have the source and the destination defined. Lets now define the stages. As mentioned Detailed Profitability has only 2 stages (Source & Destination) as against a possible 8 stages in the Standard Profitability.

Lets now define the driver HeadCount as shown below

If you notice, there are a few things that have changed in the Driver definition screen. In the older HPCM, we will just be choosing the Driver Measure and then will be assigning the priority along with the location (source, destination, global etc). But here, since it is relational, we have a destination measure that has to be the driver. In addition, the driver is associated with an actual measure that will be used as a target for all allocations. If a measure is assigned at a driver level how do we then assign target intersections? That is all done through Assignment rules in Detailed Profitability.

After the assignments, lets deploy the views required for reporting

Lets now run the calculation

If we now look at the Destination table, we will see the post allocated results as shown below

This to me is so much easier to work with when compared with Standard Profitability. The allocations are a lot more clearer and of course we still have the same flexibility as the Standard Profitability. The most important aspect from my standpoint is the ability to do out of the box reporting using BI EE. This does not require separate custom Essbase models unlike the Standard Profitability.

Before I jump into the first of two posts discussing change data capture with Oracle GoldenGate and Oracle Data Integrator 11g, I would like to first introduce myself. My name is Michael Rainey and I am the newest member of the Rittman Mead America team, joining just last month as an ODI consultant. I have been working with Oracle Data Integrator 11g since it was first released, and was part of a team at my former employer, Pacific Northwest National Laboratory, who developed an innovative solution for the rapid migration of SQL-based metadata to ODI Interfaces through use of the ODI SDK. I was able to present a brief overview of our solution at Oracle OpenWorld 2011 in a Customer Perspectives session. I plan on sharing some of my knowledge with you regarding data integration through this blog, and hopefully some future conference sessions. But, enough about me, on to the real topic of this post, Extreme BI: Capturing Change with Oracle GoldenGate and ODI.

A few months back, Stewart Bryson described the need for a real-time data warehousing solution for loading the Foundation Layer with raw source data changes in a blog post titled Agile Data Warehousing with Exadata and OBIEE: Puzzle Pieces. As he described the importance of the Foundation Layer in delivering Extreme BI, he noted that Oracle GoldenGate, though a powerful and flexible replication technology, is lacking a simple setup for tracking source data changes in the data warehouse. He also noted that there may be a workaround available with some Oracle GoldenGate configuration and a little coding, and alluded to a potential future blog post (which you are now reading). What follows is one approach to solving this problem, using Oracle Data Integrator (ODI) 11g and Oracle GoldenGate (OGG).

First, a little background on the Oracle Next-Generation Reference DW Architecture and what we are trying to accomplish. An excellent description to help further your understanding is laid out by Mark Rittman here. As Mark describes it, the Staging layer is a “landing pad” for a current version of the source data gathered into the data warehouse. It is the source for Extract, Load, and Transform (ELT) processes that move data to the Performance and Access layer, often using a tool such as ODI or Oracle Warehouse Builder (OWB). The Foundation layer, on the other hand, is a 3NF representation of data from all source databases. It represents the detailed history of all source transactions, enabling drill-through from the Performance layer for transactional data lookup. This layer also allows for changing star schema requirements to be readily implemented, as the source transaction history has been stored since the data warehouse inception.

The goal is to implement a real-time data replication process that will support Extreme BI, not only loading the current rows to Staging through to the Performance Layer, but also pushing the raw source data to the Foundation Layer, and capturing all transaction history. A typical misconception is that we must load the Staging from the Source first, then build the Foundation layer history from Staging. While this may look good in theory, the reality is that you must duplicate your effort when implementing the physical mappings from Source → Staging, and then again from Staging → Foundation. In order to increase the speed of development and remain an Agile data warehouse project, we must capture the changes to the raw source transaction data as they occur.

Our solution begins with Oracle GoldenGate and its real-time replication capabilities, integrating with Oracle Data Integrator 11g and its journalizing change data capture implementation. The setup will be as follows, with the replication from GoldenGate loading both Staging and Foundation layers, and then the ODI CDC moving data through Staging into the Performance layer. In this series of posts, we’ll focus on the replication piece, saving the Performance layer load for another time.

The next post will dig into the details of my solution, focusing on the integration between GoldenGate and ODI, and the modifications necessary for Extreme BI data warehousing.