Breaking Dimension Limitations: Innovations in Oracle BI

Oracle BI – Thoughts (4) – Cadran publishes a series of articles sharing insights on Oracle Business Intelligence combined with Oracle JD Edwards. This series covers various considerations and reflections that can help guide you in making the right decisions when implementing and applying both systems. Previous articles have covered topics such as the star schema, facts, dimensions, and how they interrelate. In this article, we explore how to break fixed relationships.

At the core, there is a logical, fixed structure between facts and dimensions—a fundamental principle in dimensional modeling. The relationship between facts and dimensions is set in the star schema and usually involves an inner join, which means data is only visible if there is a direct connection between two elements. This approach offers several key benefits:

•    Consistency and relevance between the data
•    Data integration
•    Faster development due to simplicity

For standard analyses in Oracle BI, this is sufficient. For instance, if you want to see revenue per product group per year, you don’t need product groups without revenue in that year. There’s also an important distinction between no revenue and zero revenue; zero revenue means a product group exists but only contains free products.

However, there are cases where it can be useful to deviate from this rule and combine data that doesn’t have a formal direct relationship. Oracle BI offers several methods for this based on Ralph Kimball’s theories.

Complex, Changing Dimensions
Complex, changing dimensions can be simplified by not detailing everything. For instance, if a sales order from customer Jansen falls under the North region, it can be recorded directly with the region assigned to the order, making it easier to attribute revenue to a specific region.

In Oracle JD Edwards, this is partly managed by transferring category codes from master data to transactions. This includes customer data that is passed along to order lines. What might seem like redundant data in Oracle JD Edwards can actually be valuable in Oracle BI.

Nonconforming Dimensions

A dimension such as Product is ideally recorded once and can serve multiple areas, like purchasing, sales, production, and inventory. This requires a clear, consistent definition: is the dimension the same in each area? For example, raw materials are used in Purchasing, Production, and Inventory but not in Sales. The more we want to reuse a dimension for different data, the greater the risk that the dimension may not fit all data sources.

When a dimension cannot always be combined with certain data, it’s called a Nonconforming Dimension. Oracle BI offers techniques for managing this, though it requires even sharper definitions. Here are some examples to clarify.

Example 1: total revenue 

Suppose we want a total overview of revenue, including product groups. Service revenue may be excluded because services may not be set as inventory items. This could result in a split between Product Revenue and Service Revenue. To combine both, we could link Service Revenue to the Product dimension but only at the highest hierarchy level. Another option is to add a catch-all, like a dummy item, to keep all revenue under a single dimension.

Example 2: Quantities Shipped and Quantities Produced

Imagine we want insight into Shipped Quantities and Produced Quantities. Although both relate to products, the time dimension difference introduces complexity. The shipping date and production date are not the same. Additionally, production-related dimensions like the Production Line don’t apply to shipped units, leading to a mismatch.

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

Recommendations:  

• Ensure clear definitions
• Be cautious with Nonconforming Dimensions

While Oracle BI offers technical solutions, it is often more effective to create specific reports or analyses per topic. Displaying them side by side on a dashboard maintains clarity without complicating the model.

Slowly Changing Dimensions

Another common phenomenon is Slowly Changing Dimensions. Take, for instance, Sales Revenue by Representative, which involves the Account Manager dimension in the revenue area. What happens to a representative’s revenue if they leave and are replaced by a new representative? On one hand, you want to see all revenue from those clients, but on the other, you don’t want the new representative to immediately inherit millions in revenue. The same applies to revenue by client region—what if a client moves to a different region?

Slowly Changing Dimensions raise questions that Oracle BI can address effectively if definitions are clear. Here are four common questions:

1. What is the revenue from the clients of the current representative?
2. What is the revenue from representatives’ clients over time?
3. What is the revenue from client Jansen?
4. What is the revenue for the North region?

With such clear questions, finding answers becomes easier. For instance, Question 1 is about the current representative and the total revenue from those clients, including past revenue. In Question 2, we want to track revenue by representative over time, which requires employment periods.

Ralph Kimball offers four solutions for Slowly Changing Dimensions:

1. No history: replace the value with the new situation
2. New record with effective dates
3. New column to show old and new states (e.g., Active/Inactive)
4. A history table with the old state

Determine what is needed first. If time is not a factor, Method 1 is often sufficient. If time is relevant, Oracle JD Edwards must consider date-based relationships, such as Account Manager relationships by date. Not all aspects have these capabilities, such as Accounts Receivable information. In a later article, we will discuss setting up a Data Warehouse and the benefits in this context.

Ragged- & Skipped Level Hierarchies

A good Dutch term for this is lacking. This phenomenon can occur in structures like organizational or geographic hierarchies. Take, for example, a geographic hierarchy structured at the following levels:

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

In some countries or areas, certain levels, like Street or House Number, may be missing, such as when a location is specified by GPS coordinates (like a field). When not every level is always filled, it’s called a Ragged Hierarchy. Levels can also sometimes be skipped. For instance, the level “State” exists in the US but not in the Netherlands. This is called a Skipped Level Hierarchy.

Since these situations frequently arise in practice and are difficult to capture in fixed rules, Oracle BI offers advanced techniques to display them effectively.

In future articles, we will delve deeper into these topics, especially when discussing data warehousing.

A BI solution is more than just installing software. Cadran’s vision is about delivering the right information, precisely at the right time, to the right people within your organization. A careful project approach is crucial to avoid pitfalls. Business Intelligence is not just about creating reports or beautiful dashboards; it’s about effectively managing your organization, and Cadran is here as your partner in Business Intelligence and JD Edwards.