Today I was reading about the new OBIEE 11g release, which is being released one of these days. I remember that some time ago I had the experience of discussing the Oracle Retail’s Active Retail Intelligence (ARI) in a client, being this a monitoring system product . In a nutshell, it can monitor databases and values, notifying or alerting business users for specific business events based in rules.

Well, since OBIEE iBots will do something similar and can even invoke business processes I wonder if OBIEE 11g will be, somehow, used to substitute this OR module in the near future.

Just guessing…

1. Essence of BI – Operational BI vs. Analytical BI

Operational & Analytical BI

Operational & Analytical BI

Retailers/Wholesalers, at least some of the ones I have visited so far, often tend to make confusions between the “operational and analytical worlds” and Business Intelligence (BI) applied to the operational side and BI applied to the analytical side. I know that some readers and DW/BI followers/practitioners consider BI as unique, not distinguishing both. But for me, and this is my opinion, there is a clear limit between one and another.

The purpose of a BI solution is to provide “value” to any business entity, being this entity any operational, daily intervenient, such as a buyer, or any other agent more related with higher strategic arenas, for instance anyone from a Finance department. So, if this “value” can be obtained from operational sources directly, providing immediate means and reasoning to trigger some action (let’s imagine I want to apply some emergency promotions, knowing that my downtown stores are not selling a specific product today), then yes this is an action that I take upon realizing the sales volume for that product in that store in my processing day => this is, for me, Operational BI. If value is obtained from long term kept data, including or not today’s transactional data, with for e.g. nightly batch based Data Warehousing solutions, then yes we have Analytical BI. But, if today’s ERP’s allow some historical data to be kept, for some specific areas, where are the limits between both? This question can be somehow answered with another question:

Is the analysis impacting the working performance of operational users?

Operational BI against the ERP should be used for non exhaustive analysis, such as proving minor historical answers or obtaining the results of daily operations/processes. Deeper and/or complex analysis should never be done in the Operational side, mainly because these systems follow a 3FN normalized model, where performance is decreased with multiple consecutive query joinings. This is already known in theory and, as a consultant, I always try to explain the impacts of having complex Operational BI applied to the ERP directly.

An ERP is meant to be transactional not analytical and that is why Data Warehousing solutions evolved in the last few years. This scenario happens in the Oracle Retail (OR) domain with, for instance, the Oracle Retail Merchandising System (ORMS). ORMS keeps historical data in some cases, but this system itself should never be used to do complex analysis. Can you imagine the impacts of having this core system slow/down when one of your buyers wants to approve a buying order to guarantee that stock and customer service levels are not affected? ORMS or any other OR transactional module is meant for operations, not for analysis.

2. From the ERP to the DW with Oracle – a few more considerations

ORDW, ORDM, OR Analytics

Usually we have a large set of distinct intervenients and areas involved in any BI pre implementation discussion such as Commerce, Finance, Supply chain and others. We expect to have questions, varying from the most simple ones to the most complex, operational or/and analytical, technical and business related.

So, having Oracle a large set of newer/older solutions, what shall we suggest? What has Oracle to offer? Shall we go for a be spoke solution or a packaged solution? What are the Pros and Cons? What are the challenges and risks of implementing one or another solution? These are some of my considerations:

a) A Be-spoke solution with OR

Any retail be-spoke solution, with OR as the back end, is a great solution when there aren’t considerable money and time constraints. Well, everyone has time and money constraints! But, without these two variables, an in-house well designed solution is always a good approach because the DW/BI solution is built from scratch according to business users requirements. Having a typical hub and spoke approach, following Inmon or even Kimball with pure star/snowflake modeling, a be spoke solution can provide Operational and Analytical BI if the BI component is designed to cover both layers. A packaged stable solution is more suitable when the retailer requires a faster deliverable, it takes less time to implement, but it is never perfect. Worst case scenario, the model may differ a lot from the current business analysis, which implies lots of customizations and understanding of the current or new needs (this can be the case of ORDM): So for a be-spoke solution clients should have in mind the following thoughts:

  1. Do I have enough knowledge, OR operational processes knowledge and current analysis, to design my DW from scratch accordingly?
  2. Do I need cross functional analysis, OR/EBS/JDA … ? Do I need near real time analysis, Operational and Analytical BI?
  3. Do I have in-house technical experience to accomplish this? To I need to off-shore?
  4. How long will I take to have this delivered?
  5. Looking to the current solutions that Oracle provides, what are the gaps between those and our current business requirements?

b) The Oracle Retail Data Warehouse (ORDW)

The ORDW is OR oriented. It is sourced with consecutive ORETL nightly batches, proving data from several Merchandise Operations Management (MOM) modules. It has granular and aggregated data until yesterday, but it doesn’t have today’s data. This DW uniquely covers the results (inside the facts) of applying several Merchandising processes, but unfortunately not all of them. It is a pure Analytical system (star/snowflake based), providing Analytical BI. Users must be aware that analysis with OBIEE doesn’t contemplate today’s processing data. For that purpose, ERP’s are directly consulted with BI Publisher (with static reports).

It follows type II Kimball’s approach only – as was approach. Sometimes users realize that this is a great benefit, but others don’t. It has a few reports/dashboards, forcing/allowing users to do/schedule their own ad-hoc analysis.

The ORETL framework is Java based and ORDW is packaged with a large set of interfaces from a few OR modules. The framework itself should be well tuned when data volumes are high, but some years ago I did a performance test with some other methods (ProC API, Spool, UTL_FILE) and, for the volumes I was testing, this framework was comparable to the ProC API. In a business perspective, ORDW can be limited if cross functionality comes into table.

Finance, Supply Chain, International Trading areas for instance, are not covered with ORDW.

c) The Oracle Retail Data Model (ORDM)

ORDM is a model, like described in the previous post. Being a model it can evolve and adapt to any Retail business. It doesn’t include yet interfaces for Oracle Retail or any other source. It is ARTS based, but OR itself is not, which makes the process of mapping more complicated. ORDM provides Operational and Analytical BI, plus the As Is and As Was perspective. I have ORDM here installed and I expect to see a good product soon.

It includes good reporting samples, sampled OBIEE metadata, the OLAP (workspaces “cubes”) and data mining models. This product is technically different from ORDW but, in general, it covers the same merchandising domains. At this moment, ORDM requires more stability and integrity, ODI interfaces and the BI Layer out of the box to beat the “ready to go” ORDW.

d) The BI APPS with an OR like template (aka Retail Analytics)

BI Apps are templates for cross functional areas having a dimensional DW model in the back end. Consider the following link:

For instance, talking about Finance, ORDW/ORDM are not Finance oriented – instead Merchandising oriented. For e.g. even knowing that Stock Ledger is kept in the ORMS, keeping financial values of daily transactions affecting stock, and interfaced to ORDW this is not enough for a Finance individual that requires deeper & consolidated accounting related analysis. Of course both ORDW and ORDM with OBIEE can be customized to cover these non Merchandising areas, but this is not the real purpose of these models!

Looking at BI Apps, the current version comes with Informatica/DAC interfaces to a central Business Analytics Warehouse (BAW). As I have seen, a specific older version even introduced Oracle Data Integrator (ODI), but current versions ODI was left out. BAW is star based only and dimensions are conformed to provide cross functionality. So, having already multiple functional areas inside BI Apps, with pre packaged interfaces and all the BI Layer, several questions come to my mind.

  • Will we have Operational and Analytical BI in BI Apps? Both.
  • Will BAW “Merchandising” layer be based in ORDW or ORDM? I guess BAW can only be based in ORDM model, proving b).
  • Will BAW be customized with a “Merchandising” layer created from scratch? I guess that BAW will contemplate a new model, based in the ORDM. Dimensions, for sure, will be conformed to guarantee cross functionality.
  • What ETL tool (Informatica/ODI/ORETL) be used to populate this layer? Well, Informatica is currently everywhere in BI APPS, but I guess ODI will be adopted.

e) The Oracle Retail Workspace (ORW)

Since I have been testing ORW lately, for some POC, I will try to put some considerations about it in the next post.

ORW seams to be a good portal based product allowing both Operational & Analytical analysis from a unique point.  With Single Sign On (SSO) available for some of the OR modules, it is a great (not yet largely implemented) solution to provide a cross functional prespective of the business and take the best decisions with greater transparency for end users.

3. Summary

ORDW, ORDM or BI APPS with Merchandising included in BAW (not released) are/will all be valid solutions. I believe none of them will die so soon and they all have place in the market.

A working effort, gap analysis and requirements should be evaluated for each client. If you are about to implement a Retail Analytical Enterprise Solution with or without OR consider the following opinions:

  • If Oracle Retail is implemented => ORDW is currently the best alternative for Analytical BI. It is a ready to implement solution in version 13.1, working directly with MOM systems using the ORETL framework. If data volumes are really high ORETL needs a well tuned configuration, but interfaces for the most common analysis are out of the box and operate relatively well.
  • If Oracle Retail is implemented but other ERP will still be used in parallel like EBS/PeopleSoft/JDA, cross functionality is required and both Analytical and Operational BI is required => consider a Be Spoke DW, ORDM or, in the near future, BI Apps with BAW + Merchandising layer.

ORDW is the Retail oriented Data Warehouse that Oracle provides, part of the Oracle Retail suite of products, to support retailers in their decisions. ORDW is in version 13 and I have worked with this product since version 9.3 (Retek old times). This product is alive and going pretty well so far with the OBIEE now as the BI platform to support ad-hoc reporting, instead of the previous Microstategy.

The picture below depicts the current approach of the RDW:


A few weeks ago I have started to install the ORDM for a small POC. Since it is mainly based in Inmon’s paradigm, and I haven’t implemented so far, in my career, a Retail DW using this approach, it was a good opportunity to go on with some experiments, during my dead times inside some hotel, with this promising product. My main objective was to evaluate the product.

Theoretically Inmon’s philosophy around the initial design of an Enterprise 3FN data warehouse and then data marts sourced by it, are perfectly represented with ORDM. To be honest, looking at the product, at first sight, it looks a lot more blurry than RDW looked to me some years ago. This is natural since Inmon’s paradigm is professionally new to me, I am not a real follower of his paradigm, and because Kimball was greatly emphasized during my university times, and inside the literature I am really used to read.

So, in a few words, ORDM is just another approach for building a data warehouse, not from scratch, offering a retail base product based on the ARTS standard.

Technologies involved with ORDM

There are several technologies around base installation of ORDM, based on the Oracle large set of new products. The Oracle Database should be 10g latest version or the 11g ( preferably), have the OLAP and Mining options included (if installed) with analytical workspaces and models pre created. Several patches are required to put things working properly, like described inside the installation guide. Any developer can use AWM/OX/OWB to manage the AW’s, and OWB to manage the Intra-ETL packages that are also part of ORDM.


Unfortunately/fortunately the RDM is not a specific product to Oracle Retail sources. ORDM is a real generic base data warehouse, based in the ARTS 5.0 standard, which can indeed be used to support Oracle Retail sources, or any other retail source like SAP Retail.

During the installation of the product it provides some samples, not supported by Oracle, which can be indeed a very good help during the developments of new user defined dashboards/reports. I had a few problems installing the OLAP part of the samples, mainly because AW’s have still some issues with distinct version of the option and the AWM itself. Both must be coherent with the indications that the guide describes.


External interfaces into the ORDM do no exist! Some years ago I have used ODI (old Sunopsys) to create a few interfaces into a few data marts. Using ODI was simple, accessible, and with just a few steps, a few knowledge modules, topology well defined, and voila the interface was done. Now I still don’t know which technology is the best, since Oracle has already a few of them, and each post I read refers something different. Informatica is another option or even OWB can also be used here. Or even RETL with OWB. Whatever technology is used for “Extra-ETL”, it doesn’t really matter, I am sure that things will work perfectly. For now, looking at some feedback provided by some Oracle colleagues, ODI is to way to go. Let’s see.

The use of the OLAP Option was not new to me. But I really still have some concerns about using it. Since 9i version I have researched this option with plenty of examples using JDeveloper with BI Beans and a few cubes. During that time I found out that the idea of having everything in the database was great, but, to be honest, the results I had (performance and space) disappointed me a lot. The Option had lots and lots of bugs, it didn’t have a minimal suitable performance compared to other tools like Microsoft Analysis Services. With those results, I really quit considering it for an internal project. With version 11g, and future versions, we expect much more! Personally I believe in this product! Therefore I will do the same exercise of analysing the behaviour of this 11g Option with my old tests in 9i.

Regarding the Mining Option, I have applied a few Data Mining techniques already using the product for my master’s academic project. In the previous version I have tested it for a Classification problem using decision trees (I believe it has a variation of the CART algorithm). It worked perfectly, but the output I was expecting was not sufficient compared to any other academic tool. Nevertheless, I believe that with 11g, again, things get better and the ODMiner GUI and the Mining Option have been improved. For me, at least, during my short readings, I know now that the tree design model output already exists, which makes me a little bit happier.

I am researching ORDM deeply now, looking at the current functional model which interests me most, in a business perspective, and learning in parallel the latest Oracle technologies involved. Great! For now The Oracle ORDM forum is almost empty, a few colleagues are helping me with a few suggestions; Metalink doesn’t have a lot of information regarding the product, which makes me wonder why? I will continue with this investigation, since I have already planned to provide the POC on this in a few days.

ORDM Provided Samples

The provided samples are somehow easy to install. Nevertheless some DB, OBIEE, option OLAP, option Mining knowledge is required to put things working properly. The OLAP option requires a few extra DB optimizations to have a minimal performance.

Analytical Workspace:

Opening the AW Manager I can already see the four provided  cubes: OOS_INV, OOS_INV_FCST, OOS_SALES, OOS_SALES_FCST. Nevertheless I am still getting really poor performance in maitaining the cubes using a virtual machine for testings (2MB RAM).


OBIEE Metadata

Opening the OBIEE Administrator with the provided repository returns already metadata to the ORDM model, including the Mining tables and the OLAP views. For now everything is pretty straight if you understand all the technologies involved. During the installation of this sample, make sure your connection pool is well configured inside the administrator GUI (windows side).


Business Areas sampled without OLAP and advanced analytics


Used Data Mining Models


Final Considerations

For me, both products are suitable! When shall we implement Kimball or Inmon paradigm? If I don’t use Oracle Retail sources should I use the ORDM model based in Inmon?


For me there are several great benefits of still having RDW implemented:

1. It uses a clean, easier, Kimball’s like approach for the data model design, which makes it easy to understand and extend it, in the future, to support other missing retail areas. Since RDW is not perfect, it doesn’t have, vanilla, data marts for some of the operational areas that exist inside the operational applications of the Oracle Retail suite. Using this design paradigm, it is somehow easy to design a new sub model and an ETL interface to accomplish it.

2. The ETL framework that RDW uses, for many users, it was a big “black box”. For me, on the other side, during my old professional years has a technical analyst, I found out that even not being the best solution to have RETL interfacing to this DW, it works perfectly when the data volume is not that high, and it’s portable because of JAVA. Besides, the RETL framework improved a little bit since 9.3: e.g the debugging option was improved, making it easier to maintain/analyze the interfaces. In a developer perspective, the base libraries, provided in the form of korn shells, if well used, can simplify very much their developments, becoming pretty straight and simple to develop new data flows with this framework.

3. RDW connects directly to the operational systems like RMS (Oracle Retail Merchandise System), using flat files attached to a fixed schema with pre-built interfaces, which makes it easy to implement it against other OR modules. Plug and play the “in-box” interfaces and you have already a vanilla implementation of this product up and running.

So, for now, I cannot say for sure if ORDM substitutes ORDW. Sincerely I believe it doesn’t because they are different products.

Nevertheless, it’s another option for a base Retail data warehouse implementation, with Inmon’s paradigm for data warehousing. The “winner” will be the simpler/cheaper (nice dilemma) to implement, since it will be hard to convince many users to pay for the effort of building interfaces for scratch for a simple similar RDW implementation in ORDM, and the use of multiple paid technologies. Besides, Inmon’s approach is always harder to understand, more complex, compared to Kimball’s, and theoretically with less performance.

During the next posts I will try to write my opinion about this approach.

Today I HAVE started my own blog. I believe that I have started my blog 1000 times, already, but the truth is that suddenly I get out of time, too much work to post something, too many flights or too many hours inside meeting rooms. Today I am with the right mood to start something that I hope to continue doing as much as possible.

The blog will be divided in two main parts. The first part will be more technical, related to my daily technical work and experiences, related to my working area, something that I really appreciate doing. On the other hand I will try to write about retail in general, news, products and the Oracle Retail suite. I may occasionally dig into some other complete different areas that interest me.

This is a place to learn mutually, technically and functionally. So regarding you, please feel free to contact me whenever you want to ask me something, point me to a better direction, provide me some suggestions, comment anything that I write here, or even offer me a better job with a better salary. I am always willing to analyze new opportunities abroad. 😉

Note: All the posts and ideas expressed here are my entire responsibility. They do not reflect any view of my company.