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 (11.1.0.7 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
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?
Why ORDW? Why ORDM?
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.
June 27, 2009 at 8:49 am
Hi Alvaro, great article, must have worked hard to set it all up. Regards, Erik.
October 8, 2009 at 11:15 am
Hi Alvaro,
Great article – it’s heartening to see someone putting so much effort. We were involved in doing a few POCs using ORDM for some Oracle shops in the Retail business.
November 11, 2009 at 2:53 pm
Hey Alvaro.
I don’t suppose you know which tables are the fact tables at the center of the ORDM star schemas when you take it out of the box do you ?
November 12, 2009 at 2:30 pm
Hi Ed.
Do you have access to the metadata? You can check from there pretty easily!
Please write to my email directly if you have any issues: alvarosilva2008@hotmail.com
Kind regards.
Alvaro Silva
November 12, 2009 at 3:16 pm
Thanks Alvaro.
I don’t have access to the metadata but I am sure someone does. I was hoping that there might be a prefix on the fact tables I could use to identify them.
-Ed.
November 13, 2009 at 1:51 am
Hi Ed. If you send me your email maybe I can provide you more details.
Nevertheless you have several options to check that, but the best and easy is to open the metadata file and check.
As you probably read in the documentation you have several types of tables: reference, lookups, base, derived, aggregate. So each logical star can use tables such as DWR_, DWB_, DWA_ for instance. So whenever you try to check the star for e.g. inventory position, the facts will use several aggregated DWA’s (dept_day, dept_wk, item_wk, sbc_wk) and one DWD(item_day). In the presentation layer you have already the inventory position splitted into the distinct levels, so that the final user can design his reports at the level he desires. Hope it helps.
December 9, 2009 at 12:04 pm
Lots of folks blog about this subject but you wrote down really true words!
March 5, 2010 at 11:17 pm
Hi Alvaro,
I came across this article via the oracle forums on ORDM. My main comment is actually about the initial differentiation you mention (plus #1 above).
I do agree that a set of star schemas is simpler. There are simply fewer tables and relationships to worry about and it cleanly reflects a set of business areas.
However I think having the 3NF model in front of the stars is essential if you take this data warehouse as a basis for a lot of reporting elements. I imagine you want some horizontal data in here (typically from ERP systems to manage personnel etc.). While it is more complex, it is also much more flexible and gives you a historic base to run reports on that cannot be run on a star schema.
I think ORDM tries to deliver a 3NF model to store the detailed data and have it available for detail inquiries. The star schemas on top of this 3NF model are the performance or summary layer looking roughly the way the RDW model looks (not that I compared them 1:1) and these layers serve the same purpose.
Just thought I’d add that to the discussion. Not trying to say one product is better than the other, just trying to reflect some thoughts on the 3NF component in the architecture.
Also, we see a lot of DW systems (especially the large ones) be 3NF with derived layers (stars) and so ORDM fits well in what we see in the market.
Thanks,
JP
March 7, 2010 at 12:25 am
Hi JP, thanks for your good points.
The implementation of a typical denormalized data mart based DW vs EDW 3FN highlights the classical discussion between Kimball/Inmon. There are lot’s of
opinions and there is no golden rule to choose one or another. It depends…
I agree with you that the EDW can provide the overall picture of the business indeed, cross multiple horizontal areas (horizontal data), and that the lower granularity
area (HUB) will allow doing analysis that are difficult to get from a dimensional model where cross functionality is difficult to achieve. The “spokes” area
will appear to be “similar” to the RDW, but not the same!
Regarding the Oracle Retail operational world (3FN based), or any other ERP, my opinion is that, when building be-spoke EDW’s with the classic hub and spoke approach,
plenty of times we will be replicating our operational side. The OR ETL’s -> EDW will be somehow easier to implement (sometimes with direct mappings) but lot’s of redundancy
will exist inside this EDW.
The ORDM fits with this HUB/SPOKES approach and what we see in the market. This is why we both agree that RDW (dimensional) vs ORDM (3FN) are two distinct valid products.
But, looking at the OR world, mappings from one side to another are not that easy to do and I had recently the experience to do that exercice for a bespoke Teradata EDW,
based in old legacies, where the mappings were not direct when implementing OR.
So, looking at the packaged solution ORDM:
1) The model has plenty of functional fields that don’t exist in the OR operation side. Functionally, OR/ORMS, for instance, behaves differently.
2) The model still misses some areas that exist in the ERP. Customizations/readjustments are then required.
Still have plenty of questions here:
-> Should we go for ORDM or should we build a in-house be-spoke EDW??
-> Is Exadata already stable enough? Will ORDM take benefit of the Exadata arquitecture? Now or in a few years?
-> Again, how much will I pay for the ORDM complete solution, taking advantage of all the technological pieces that come with the product?
-> What’s the cost of customizations and ETL’s and the learning curve for this product?
Thanks,
Alvaro
July 14, 2010 at 2:21 pm
Very interesting and complete analysis!
Regards,
Rui
July 25, 2010 at 12:04 pm
Hello Alvaro,
Really nice article. This product of oracle being in generalize structure can be use with any ERP modules.
But populating its base layer from outside world requires source programs to be built.
I am wondering for which can be the best solution for integrating this module with other oracle retail modules (ORMS,ORPM etc.).
In oracle retail suite RETL are specific to RDW only.
Please kindly suggest me the same.
July 25, 2010 at 4:34 pm
Hi Aniket. Thanks for the post. Well, RETL is indeed the current technology for batch processing into RDW and between some Operation modules. Even knowing that you can do your own RETL interfaces to populate this 3FN base layer it’s somehow important to have in mind two things. 1) RETL probably will be substituted soon with ODI. So, the efforf to build RETL interfaces from scratch should be taken into consideration. 2) Mappings are not easy to do. So, OR operational knowledge is required as well as ORDM model knowledge. We don’t have direct mappings. There are currently companies that have done “somehow” this work. Please refer to Soft Praxis if you wish to obtain further understanding. I believe that one or two more companies have “something” similar going on. If you read my latest post, you will notice that OR Analytics that will have a “merchandising” oriented template that will have interfaces ready to go as well with ODI/Informatica (?!?!!?). So, ODI is probably the best way to go for ORDM! Hope it helps. Regards, Alvaro.
May 6, 2011 at 8:43 am
According to the RMS13.2 release notes (http://download.oracle.com/docs/cd/E12448_01/rms/pdf/rms_rfm_br/132/rms-132-rn.pdf), RETL and all RETL based interfaces to ORDW are desupported from 13.2! “Discontinued Support for Oracle Retail Data Warehouse (RDW)
Interfaces
A new analytical application is in development. Because of this strategic decision,
support for interface between Oracle Retail applications and Oracle Retail Data
Warehouse has been discontinued for Oracle Retail 13.2 releases. Oracle Retail Extract,
Transform, and Load (RETL) extract scripts for RDW are not supported for use with
Oracle Retail 13.2 applications and databases.”
That makes things a lot clearer!
Keep up the good work, best regards from Erik