Introduction

Dimensional schemas work well for modelling a particular part of a business where there are one-to-many relationships between the dimension table and fact tables. However, sometimes it is necessary to model many-to-many relationships between the fact and dimension tables.

This article covers two topics:

Modelling Time Series data

Modelling many-to-many relationships

1. Modelling Time Series Data

1.1- Time Comparisons

Time dimension is little different from other dimension because any other dimension is levelled clearly. Time dimension is little critical in the SQL development because if you want to see the right set of data at right time. Time comparison is made for the sales till date between different time periods. OBIEE made simple analysis of sales by month, quarter, and year.  The ability to compare business performance with previous time periods is essential for understanding the business. Time comparison enables businesses to analyse data that spans multiple time periods, providing a context for the data.

Example:

Time comparison made for different time periods is shown below:

Figure 1

1. Business Challenge

SQL was not designed to make direct comparisons over time. For example, to compare this year’s sales to last year’s sales, you must run three separate queries as given below:

What were this year’s sales?

What were the previous year’s sales?

How to perform the comparison between last year and current year sales?

Figure 2:

2. Business Solution

OBIEE make single representation for all the queries. The solution is to model time series data in Oracle BI Repository. This enables users to make one request for the desired result. Oracle BI Server runs multiple queries in parallel to get the desired results. The queries that run in the background to support the time measure are transparent to the user.

Figure 3

3. Time Dimensions

SQL does not provide a direct way to make time comparisons. So you must model time series data in the Oracle BI Repository.

The Time dimensions is set based on the period table in data warehouse. Then the measures that take advantage of time dimension to use the AGO, TODATE, and PERIODROLLING functions.

Compared to modelling an ordinary dimension, the time dimension requires two steps:

• Select the time option in the Logical Dimension dialog box.

• Designate a chronological key for every level of the dimension hierarchy.

At query time, the Oracle BI Server then generates the highly optimized SQL that pushes the time offset processing down to the database wherever possible, resulting in best performance and functionality.

Figure 4

4. Time Series Functions

Oracle BI Server provides AGO, TODATE, and PERIODROLLING time series functions for time series comparisons.

AGO function calculates aggregated value as of some time period shifted from the current time. For example, this function is user

TODATE function aggregates a measure attribute from the beginning of a specified time period to the currently displayed time.

PERIODROLLING function performs an aggregation across a specified set of query grains period, rather within a fixed time series grain. The most common use is to create rolling averages, such as 13-week rolling average for sales.

Query Grain

It is the lowest time grain of the request. For example the query grain is Month.

Time Series Grain

It is the grain at which the aggregation or offset is requested, for both AGO and TODATE functions. In the example shown below, the time series grain is Quarter. Time series functions are valid only if the time series grain is at the query grain or longer.

Note:

The PERIODROLLING function does not have a time series grain. Instead, you specify a start and end period in the function.

5. Storage Grains

The example report shown below can be computed from daily sales or monthly sales. The grain of the sources is called storage grain. In time dimension, the chronological key is set to order data in the desired chronology. A chronological key must be defined at this level for the query to work, but performance is generally better if a chronological key is defined at the query grain.

In the example report shown below,

Figure 5

Dollars Qago is an example of the AGO function. It compares the dollars to dollars a quarter ago.

Dollars QTD is an example of the TODATE function. It accumulates dollars from the beginning of each quarter to the end of each quarter.

Dollars 3-Period Rolling Sum and Dollars 3-Period Rolling Avg are examples of the PERIODROLLING function. For instance, for Dollars 3-Period Rolling Sum, the three-month rolling sum starts two periods in the past and includes the current period. That is, for the month 2008/07, the rolling sum includes 2008/07; the rolling sum includes 2008/05, 2008/06, and 2008/07.

Example:

Figure 6

6. Create Measure and add them to Presentation Layer

Use the Expression Builder to build a measure by using AGO function with the following form:

AGO (<<Measure>>,<<Level>>,<<Number of Periods>>)

Figure 7

Use the Expression Builder to build a measure by using the TODATE function with the following form:

TODATE (<<Measure>>,<<Level>>)

Figure 8

7. Test Results

Use the Expression Builder to build a measure by using the PERIODROLLING function with the following form:

PeriodRolling(<<Measure>>, <<integer>>, <<integer>>)

Figure 9

Create analysis and verify the results.

Figure 10

2. Modelling Many-to-Many Relationships

In dimension model, you have one-to-many relationship. In order to model many-to-many relationship, many-to-many relationship is break down to one-to-many relationship by using an additional table called bridge table.

Dimensional star schemas are ideal for modelling a business when one-to-many relationships exist between the dimension tables and fact tables.

Challenge: It is often necessary to model many-to-many relationships between dimension tables and fact tables.

Solution: Use a bridge table to model many-to-many relationships.

1. Bridge Table

• It resolves many-to-many relationships between dimension tables and fact tables.

• It stores multiple records corresponding to a dimension.

• It contains a weight factor column representing the ratio of the many-to-many relationship.

  • For example, if two sales representatives are associated with a given sales commission, the weight factor for each representative would be 0.50.

  • The weight factor is multiplied by the commission amount to yield each representative’s share of the commission.

  • More complex factors can be used (for example, 0.50, 0.25, 0.25) as long as the sum of all factors is 1.

When you need to model many-to-many relationships between dimension tables and fact tables, you can create a bridge table that resides between the fact and dimension tables. A bridge table stores multiple records corresponding to that dimension.

Example 1:

• Each sales representative may participate in many deals that pay commission.

• Each deal may include many sales representatives who split the commission.

• A bridge table is required to model many-to-many relationship between the commission fact table and sales representative dimension table.

Figure 11

In the example above, you model a bridge table to resolve many-to-many relationship between a commission fact table and a sales representative dimension table.

Example 2:

Use known techniques to import the commission fact tables and commission bridge tables to the physical layer.

Figure 12

Use the bridge table to model many-to-many relationship between the commission fact and the sales representative in the physical layer.

Figure 13

Use physical columns to create a measure that calculates “commission amount” * “weight factor”.

Figure 14

Use an analysis and query log to verify the results.

Figure 15