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.

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)?
  
• Q2: Who are the “New” customer starting to buy our product in the last half year?
  
• Q3: Who are the “lost” “initial” customers (starting to buy in the first half year but not buy in the last half year)?
• Q4: Who are the loyalest customers (buying in each quarter without interruption)?
  

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.

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

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.

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).

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:

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)?

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

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)?

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

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