Skip to Content

In my previous blog *New BI4.2 SP3 feature* :  Working with BI Sets,  I showed how to understand, create set in IDT and then use it in Web Intelligence.

Now, I will show you a sample about how to create and use Temporal Set to look into your data based on calendar.

Scenario

Before showing steps to create temporal set, please check the following result of Web Intelligence document.

Temporal Set - original crosstab result.gif

It shows revenue of each customer per each quarter in three years by using Cross Table in Web Intelligence.

Is it enough for your business? Do you want more “Intelligence” information as below?

  • Q1: Who are the “Initial” customers (starting to buy our product in the first half Year)?
    Temporal Set - Q1.gif
  • Q2: Who are the “New” customer starting to buy our product in the last half year?
    Temporal Set - Q2.gif
  • Q3: Who are the “lost” “initial” customers (starting to buy in the first half year but not buy in the last half year)?Temporal Set - Q3.gif
  • Q4: Who are the loyalest customers (buying in each quarter without interruption)?
    Temporal Set - Q4.gif

Now, think about how complex it will be if you are using simple query. Is it crazy?

But you have a better solution –  Temporal Set – which is introduced in BI4.2 SP3.

Introduction

Temporal sets are based on calendar periods. You often need to create sets that contain information about set members who join the set, leave it, stay in it, or simply visit the set, during specified calendar periods.

Creating a Temporal Set

Before you can create a Temporal Set, ensure that the following pre-requisites are met:

  • A set container has been created on a universe (.UNX) in the repository
  • The universe has a date-type attribute in the business layer.
  • Customer dimension in the business layer has Primary Key specified in the Keys tab.

Add a new Calendar

Calendars available to all sets are listed in Set Container. Click Add a new Calendar to create a new calendar or click the ellipsis button to modify an existing one in the list.

Temporal Set - Calendar.gif

In this sample, as we want to analyze our data in all quarters between 2004 and 2006, calendar will be defined as below:

Temporal Set - Calendar detail.gif

Define a Temporal Set

After calendar is added into the set container, you could retrieve it to local project in IDT and then click the arrow to the right of the Insert Temporal Set icon in the menu bar of the Sets pane.

Temporal Set - Insert.gif

A calendar and date selection box appears. It lists the calendars defined in the set container and the date attributes available in the business layer that can be used to associate the calendar with a date reference in your data mart. Now, you need to click a calendar in the list, select a date attribute that links your data to the calendar and then select the Auto-fill checkbox because we need to build periods in the past (Year between 2004 and 2006).

Temporal Set - calendar and date selection.gif

Then you could build a set to create its list members in the database. Depending on the type of set, you can build using one of the following:

  • For a temporal set, if you want data for the current period, click the Build button available on the Steps and History tabs in the Sets Editor.
  • For an auto-fill temporal set, if you want data for one or more specific periods in the past, click the Auto-Fill button activated next to Build.

Since there are only periods in the past (Year between 2004 and 2006) defined in the calendar, please use Auto-Fill to build your set ensures that the active calendar period for a set is incremented each time it is built. If you do not build with Auto-Fill, then only data from the current calendar period is taken into account.

You have the following options:

Auto-Fill Type Description
Complete Build in a row all possible periods, from the first non-built one in the past, to the last available. You cannot build in the future.
Partial
  • You can select an end period date from the Auto-fill to calendar. The number of affected periods are automatically incremented in the Periods to auto-fill list.
  • You can directly select the number of periods to automatically fill data by clicking the increase or decrease arrows in the Periods to auto-fill list.

Temporal Set - Auto-Fill.gif

After that, save and publish to update the set container in the repository.

Use Temporal Set in Web Intelligence

OK, now let us to check how intelligence it will be by using Temporal Set as filter in Web Intelligence with the following operators together:

  • Current member
  • members for period
  • members who joined
  • members who left

Query for Q1: Who are the “Initial” customers (starting to buy our product in the first half Year)?

Temporal Set - Q1 Solution.gif

Query for Q2: Who are the “New” customer starting to buy our product in the last half year?

Temporal Set - Q2 Solution.gif

Query for Q3: Who are the “lost” “initial” customers (starting to buy in the first half year but not buy in the last half year)?

Temporal Set - Q3 Solution.gif

Query for Q4: Who are the loyalest customers (buying in each quarter without interruption)?

Temporal Set - Q4 Solution.gif

Amazing 😎 ? Do you think that the WebI report becomes more intelligent (like data mining 😉 )?


Known issues

2370270 – BI4.2 SP3: Error “Today’s date does not fall into any period of this calendar” occurred when building a Temporal Set in Information Design Tool



Thank you for reading and I hope you enjoyed this blog.

Look forward to your feedback about this. You could provide more scenario and then I could try to find a solution and then add into this blog.

Best regards,

Xiwen

To report this post you need to login first.