What is a Datawarehouse?

Oracle BI – Thoughts (6) – Cadran publishes a series of articles about the ideas surrounding Business Intelligence in combination with Oracle JD Edwards ERP. In these articles various considerations and reflections are discussed, which can be helpful in making the right decisions in the implementation and application of both systems. The previous articles dealt with the star scheme, facts, dimensions and the interplay between these two. This article discusses the meaning and role of so-called data warehouses.

What is a data warehouse?

A data warehouse is ultimately ‘just another‘ database with tables and fields and records with data. Oracle BI can access every data source and thus also the data warehouse database.

What a data warehouse is directly intended for, is to prepare large amounts of data from (possibly several) source systems in a smart way in order to be able to answer the obvious questions in an efficient and quick way. This is done in so-called cubes, which keep the required aggregations at all levels. In addition, a data warehouse is able to record the progress of data over time, so that historical progress is made possible. In the theory of Ralph Kimball this is known as Slowly Changing Dimensions.

An example of this latter concept is a new sales representative, who comes into employment and takes over the work of someone who retires. When this representative is linked to his customers, this man would have a good revenue target immediately upon entry. In order to keep this image clear, insight into the aspect of time that plays a role in this is necessary, as well as employee status (such as Out of Employment).

Another example is the standard Accounts Receivable information. If Oracle BI makes the information of Open Invoices and Open Days transparent, we always talk to Oracle JD Edwards about the current situation. How many invoices are open? How high are the outstanding amounts? Which customers have too much ovedue amounts? But if this position has to be compared with last year to analyze which customers will behave better or worse, the data in Oracle JD Edwards will not be able to deliver this information (not just like that).

A data warehouse is able to serve the above aspects. In addition, a data warehouse offers the possibility to centrally combine information that is spread over all sorts of sources and systems in the organization. This makes it even possible to integrate all kinds of outdated data structures, Excel sheets and text files into a centrally accessible database. In addition, it enables an Oracle BI solution to easily report on this central storage. Could sales order history from Oracle JD Edwards easily be combined and compared with a CRM system to determine, for example, order success and quotation duration.

Of course, when setting up a data warehouse, definition immediately comes into play in order to prevent comparison between apples and oranges. In addition, the data warehouse will have to cover the source of information needs. Following the above example, it will be necessary to know what can be counted as sales order history (which selection criteria, such as order types, rule types, order statuses and by what date). Consensus will then have to be formed about the concept of customer if measurement values such as order success and quotation duration per customer or customer group must be able to be displayed. After all, a customer in Oracle JD Edwards may be a different concept than in a CRM system.

Transactional database vs. Datawarehouse database

When an Oracle Database is installed on a machine, this is just about the first question asked in the installation process. Is the database intended for transactions or as a data warehouse? Although it does matter at first, a number of essential parameters are set differently in the database. Eventually, data from a database is queried using SQL. Oracle BI is no different here. However, it is all about the optimal execution plans of these SQL statements, which means that high performance can be achieved. How do you get a lot of data on the screen in a short time? All systems and techniques have been set up to achieve the highest possible optimization of these SQL statements. This optimization obviously looks fundamentally different for a transactional database (OLTP) than for a data warehouse (OLAP). After all, storing a sales order with ten order lines is different from questioning the total revenue per month versus last year’s.

JDE – DWH – OBI

If we try reflect all of this onto Oracle JD Edwards and Oracle BI, this combination does not necessarily require a data warehouse. Especially when the amount of data is limited, it is technically very well possible to query the Oracle JD Edwards database directly with Oracle BI. The BI Server in which the logical information model operates then actually becomes as a data warehouse in memory.

Many users of Oracle JD Edwards do not have the size, for which a data warehouse is required. Nevertheless, it is advisable to think seriously about this matter before starting an Oracle BI implementation. The main considerations are:

•     How can (complex and / or heavy) SQLs on the operational Oracle JD Edwards system be prevented or is not that bad?
•     What requirements apply to historical data and data over time (Slowly Changing Dimensions)?
•     What requirements apply to performance? (After all, this is also a feeling issue)
•     What kind of combination of data from other systems than just Oracle JD Edwards are needed in the Oracle BI solution?
•     What kind of requirements do I impose on the topicality of data? (possible combination of data warehouse and so-called Real Time information)

But with a data warehouse there will be an extra element in the ICT landscape with the associated care, management, costs and maintenance. Not every organization is waiting for this directly or has the IT organization fitted for this. There are, however, alternatives that vary in weight. This can take the form of Staging Areas, data replication or even simpler Materialized Views in the database of Oracle JD Edwards. In this way, a kind of DataWareHouse Light can be created.

With the correct Oracle BI Information Model, however, it is easy to set up a data warehouse at a later stage and to make it available as a data source with Oracle BI. As long as the definitions do not change, there is ‘only’ a different data source, but Customer and Revenue remains ‘simply the same’. This is obviously greatly exaggerated, but the message is in essence correct.

Oracle BI Applications

With Oracle BI Applications ready-to-use dashboards, analytics and data access are offered. These have come about from countless implementations and years of experience. Oracle BI Applications can be seen as a best-of-bread standard solution. For Oracle JD Edwards this is available for the subject areas Finance and for Sales Order Management. Next is Procurement and Manufacturing. That includes a ready-made Datawarehouse. It is then only the ETL (Extract Transform & Load), which feeds the data warehouse, to match Oracle JD Edwards (the JD Edwards Adapter).

All treatises in this article are thus answered in one swoop. The issue of definition does, however, again arise. What do we actually get to see? It is highly recommended to consider this only with an Oracle JD Edwards implementation already in use. Only when the ‘plug’ is inserted into a well-filled JD Edwards database, the dashboards come to life and this standard solution can be tested against the information needs of the organization with a fit / gap analysis. Additional modules can be added into Oracle BI Apps through customizations of ETL, DWH and BI.

In previous articles we discussed the aspects that come with dimensional modeling. The use or not of a data warehouse is a choice, but not a necessity. Everything has to do with the size and maturity of the solution and the organization. In the coming articles, these aspects will be examined.

More information?

Contact us

Author:  Rick Brobbel
BI Consultant at Cadran Consultancy