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
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?
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.