BI – Special Dimensions

Oracle BI – Thoughts (4) – Cadran publishes a series of articles about the ideas surrounding Business Intelligence in combination with Oracle JD Edwards. 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 looks at breaking the mandatory link.

The fact that there is a binding link is a logical consequence of one of the design principles of dimensional modeling. The relationship between facts and dimensions is set up in the star scheme and there is by default a so-called inner join. Or: a relationship between two entities must exist to show data. This approach has a number of important advantages:

•    Consistency and relevance between the data
•    Integration of the data
•    Limited development time due to simplicity

This is sufficient for regular analyzes in BI4JDE. If we want to know the Revenue by product group per year, we are not interested in product groups without Revenue in that given year. And then it is still important to distinguish between “no revenue” and “zero revenue“. The latter means that a product group has a revenue of € 0, – (for example because this is the group with the free products).

Nevertheless, in certain cases there will be a need to deviate from this rule and to combine information in an analysis that can not be related. BI4JDE has here a number of mechanisms that are (again) derived directly from the theories of Ralph Kimball.

In general, the phenomenon can drastically decrease in size when dimensions are not endlessly normalized. When a Sales Order line falls under customer Johnsen which is under the North region, the danger of Slowly Changing Dimensions is quickly present. If a Sales Order itself falls under the North region, it is much easier to allocate revenue to a region.

In Oracle JD Edwards this has been partially solved because all kinds of category codes are copied from the master data into the transaction data. Think of all kinds of Customer Master Attributes that are replicated to the Order Header and all kinds of Item Branch Category Codes that are replicated to the order lines. What initially seems redundant in Oracle JD Edwards may be very convenient for BI4JDE.

Nonconforming Dimensions

A dimension like Product will preferably be set up once and unambiguously and can serve multiple subject areas. What in Oracle JD Edwards is the item, occurs in Purchasing, Sales, Production and Stock. This requires the corresponding unambiguous definition of the Product dimension. But is this the same for all subject areas? After all, a raw material will be used in Purchasing, Production and Stock, but not in Sales. The scale starts to slide. We want to reuse a dimension as much as possible to use against different facts, but because of this it may be that a dimension does not always have the right desired relationship with facts.

If a dimension can not always be related in an equivalent way to combining facts, one speaks of “Nonconforming Dimensions”. In BI4JDE there are techniques for realizing this. With these techniques we do introduce the need for even more precise definitions. After all, an interpretation is added. We will try to explain this by means of an example.

Example 1: All sales

If we want a Revenue overview with all sales and where applicable the product group, we make it possible to include the Revenue of, for example, services. Assuming that these services are not set up as a Non Stock Item in the Item Master, they will disappear if the Product dimension is involved. If this is not desired, then we are faced with a dichotomy in facts, namely Product Revenue and Service Revenue. This may also find its origin on the basis of different definitions. If we want to combine these two facts in an analysis, this can be done by relating the Services revenue to the Product dimension, but to the highest level of the hierarchy. In fact, this dimension is bypassed. It is preferable to connect a safety net here, such as a Non Stock Item or a dummy product number. As a result, the dimension continues to be useable in full value.

he value of services is involved in non-conformity in the Product dimension, without a product underlying it.

Example 2: Quantities Shipped and Quantities Produced

Suppose that insight is desired in the Quantities Shipped and the Quantities Produced at the factory. Both can probably be combined perfectly in the Product dimension, since they both deal with Items. Nevertheless, nonconforming dimensions also apply here. First of all, this analysis is complicated by the Time dimension. When, for example, the year is involved, it will immediately go over two different dates, namely Date Shipped versus Date Produced. These are not the same. It becomes even more difficult when a specific dimension is used in the subject area Production, in which the Quantities are produced, such as Production Line. This can not be applied to the measure Quantities Shipped.

Since Numbers Shipped can not be split to the dimension Production Line, the above will be the result.

Recommendations:

•     Sharpen the definitions
•     Be careful with Nonconforming Dimensions
•     Try to avoid this
•     Even though BI4JDE offers a technical solution here, it is advisable not to apply it in the model

A better result is obtained by building separate reports or analyzes in BI4JDE, which are designed purely on a single subject area. These reports can easily be presented next to or in each other on a dashboard, so that the desired result is obtained without having to bend the logical information model in curves.

Slowly Changing Dimensions

A more serious phenomenon is that of Slowly Changing Dimensions. A good example of this is Revenue per Sales Representative. This is the dimension Account Manager or Sales Representative in the subject area Revenue. The issue arises when such a dimension of content changes. If a representative leaves employment and a new one is employed, what happens to the older Revenue of those customers. On the one hand, it is desirable that all Revenue of the customers of that sales representative is in the picture. On the other hand, it is not desirable for this representative to have a Revenue of millions directly on his account. Another example is Revenue per Customer Region. What happens to the Revenue of a customer and that region when this customer moves and settles in another region?

Both issues fall under the slogan of Slowly Changing Dimensions and are perfectly answered by BI4JDE, as long as the definitions are correct. After all, we are talking here about four different issues:

•     What is the Revenue of customers of the current sales representative?
•     What is the Revenue of customers of the sales representatives over time?
•     What is the Revenue of customer Johnsen?
•     What is the Revenue of the North region?

When these questions are made clear in this way, the answer also applies. For question 1, only the current representative is needed, and therefore all Revenue from the past is also taken into account, because it concerns the Revenue of those customers. In question 2, we want to specialize this Revenue to the sales representative, who actually realized this Revenue and the employment period will have to be involved.

Kimball writes four possible solutions:

•     No history: overwrite new values and thus the current current situation
•     Introduce a new rule with effectiveness dates or validity dates
•     Introduce a new column so that old and new situation can be queried side by side (eg flag Active / Not Active)
•     Introduce a history table and feed it with the old situation

It is therefore essential to first assess what is necessary for which information need. When historical time is not an issue, it can usually go well with method 1.

If the time aspect is an issue, then this will have to be taken into account here in BI4JDE. For example, Account Manager relationships will have to be set up per date. But not all aspects in BI4JDE have facilities for this. Consider, for example, Accounts Receivable information.

In a later article, the motives for a Datawarehouse will be discussed. This aspect is then discussed in more detail in this context.

Ragged- & Skipped Level Hierarchies

A good Dutch name is not directly for this. This phenomenon can occur in, for example, organizational structures and geography. An example is given on the basis of the geographical hierarchy. This can be structured with the following levels:

• •  Continent
    • Country
      • State
        • Province
          • City
            • Postal Code
              • Street
                • House number

The levels street and house number may not appear in some countries or at some addresses (because the location may be indicated with GPS coordinates, such as for a field). When the lower levels in a hierarchy are not always reached, these are Ragged Hierarchies.

The level State does not occur in The Netherlands. When an intermediate level in a hierarchy does not always apply, these are Skipped Level Hierarchies.

Because both situations can occur and possibly difficult to catch in rules, BI4JDE has ingenious techniques for this, which make it very well possible to operate these situations.

In the following articles we will discuss these phenomena in more detail, especially when discussing Datawarehousing.

De implementatie van een BI-oplossing is meer dan de implementatie van een softwareoplossing. De visie van Cadran is gericht op het bepalen van de juiste informatie, die op het juiste moment bij de juiste mensen in uw organisatie beschikbaar is. Daarbij is een gedegen projectaanpak zeer belangrijk om de valkuilen van een dergelijke implementatie te voorkomen. BI gaat namelijk niet over het ontwikkelen van rapporten of het creëren van mooie dashboards. BI gaat over het managen van uw organisatie en Cadran is uw partner als het gaat om Business Intelligence en JD Edwards.

Contact us

Author:  Rick Brobbel
BI Consultant at Cadran Consultancy