BI – Special DimensionsBI4JDE – Thoughts (4) – Cadran publishes a series of articles about the ideas surrounding Business Intelligence in combination with Oracle JD Edwards (BI4JDE). 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
Nonconforming DimensionsA 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 salesIf 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 ProducedSuppose 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.
- 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
Slowly Changing DimensionsA 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?
- 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
Ragged- & Skipped Level HierarchiesA 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:
- Postal Code
- House number
- Postal Code
Advice about our solutions?
Rick Brobbel, BI Consultant at Cadran, would be happy to talk about the possibilities for your organization.