Set Analysis Part 3b: Creating Sets In BusinessObjects

w that we’ve spent some time with sets, it’s time to begin to see the technology in action. Business Objects has made sets easy to build and maintain. There is virtually no limit to the ways you can now choose to look at your customers and look at how those customers are contributing to revenue, profit and margins.

Using Sets to Maximize Revenue

Click HERE is a quick online demo that we’ve built to explain how some of our telecommunications customers use sets to maximize revenue, cross sell and up sell opportunities.

Creating Sets

The beauty of sets is the fact that you don’t have to involve IT in making changes to the underlying data structures to segmentation. With the set tables that come with Set Analysis, end users can create their own custom collections of products, customers, etc. BusinessObjects users will then be able to use those sets together with the BusinessObjects Suite to provide in depth reporting of key metrics and kpis with respect to these custom collections of customers. Whether you want to track standardized silver, gold and platinum customers or time, or simply do some adhoc customer analysis, sets gives you what you need.

In XI 3.0 you can create sets using our Performance Manager dashboard interface. A set can contain data for only one subject. The subject is normally identified as a primary key in a dimension table. In the case of the Customer subject, the Customer Id key will be the key we are tracking.

Create Calendar

The single remaining configuration component that still remains is to create a calendar. Calendars are used by BusinessObjects to allow administrators to automate task such as refreshing sets and to allow us to track the changes of sets over time. We need to tell BusinessObjects how often track the changes in our sets.

Based on continuing our demo from the my previous post, lets create a calendar monthly. To create calendar, navigate to the Dashboard & Analytics Setup. Next, choose the Time Config > Calendar submenu. Here is where you can see a list of all available calendars. Let’s choose New Calendar

Based on the same data provided in my previous post, we will create a calendar:

1. Name: DM_Monthly
2. Interval: Monthly
3. Format: First Day of Period, 9/21/04
4. Range: Jul-00 to Mar-03

After you have created the calendar the screen should look like this:

NOTE: Once a calendar has been created, you cannot change values such as interval.

Now that the setup is complete, we can move to Dashboards & Analytics. Chose the Set Analysis > Sets submenu.

The first thing we need to do is create a folder so that we can categorize our sets. Choose New Folder and let’s name our folder: Customer Types.

Let’s also create a second folder called: Customer Segments.

Now let’s create our first set.

Static Sets

The first set we will create will be a static set. A static set is a collection of objects that either:

• Doesn’t change over time or
• if it changes, we are not concerned about tracking those changes.

Some examples of static customer sets are:

1. Customers Living in Montreal
2. Homeowners with more than one child
3. Key accounts younger than 30 years old
4. Customers in the UK with sales over 10,000 in 2001

We will be able to create a set of customers using any of the customer table reference that we setup previously. Let’s create a set of all High Tech GenX Customers, that is: All Customers with Internet and in the 30-40, 40 – 50 Age Bands.

First we need to name our static set and set some attributes for our set. Our set will be called:

1. High Tech GenX
2. Folder: Customer Types
3. Description: This set will contain a list of all customers who are considered Highly Technical, Generation Xers.
4. Scope: Public
5. Set Type: Static

Click Apply. Now we will specify the content of the set. In our case, we will use Visual Data.

NOTE: The interface can be a little tricky. You will see Add/Copy/Remove buttons next to Sets and also find Add/Edit/Remove/Empty button within the Set Building Sets section. The buttons on the left operate on the entire set itself. The buttons on the right only affect the selected building step.

There are 5 was to add information into a set. They are:

• Visual Data – Allows you to look at all the fields associated with your subject (e.g. customers) and visually see how many records will be included in the set. (Most Popular)
• Visual Sets – Allow you to reuse existing sets to create new sets. For example you can visually see the intersection between High Tech GenX Customers and Gold Customers for example.
• Free Hand SQL – Allows the users to specify their own SQL against the database. (Not Recommended)
• Event Analysis – Allows you to create new sets based on a records movement within a dynamic set for a specific time period. For example, all customers who were leavers between Oct-02 and Dec-02.
• Import – Allows you to manually import records (e.g. contact_id values) from a csv or txt file.

We will chose Add > Visual Data and be taken to the window, where we can select the attributes of our set members.

We will chose:

• Has Internet – Y
• Age Range – 30-40 and 40-50

We will select the attribute on the left and choose the arrow to move the attribute to the right. (If prompted, chose Exact Counts). The system will tell you that 1135 contacts have Internet. Choose the Y checkbox.

Next select the Age Band attribute on the left and expand the section. You will see that of those with Internet, 321 are aged 40-50 and 302 are aged 30-40. Select both of these checkboxes. This means that when we build the set, we will expect a total of 623 members.

Now choose Next and fill in the Description: Internet and Aged 30-50

Now we can choose OK and we can see the resulting set.

We could continue to add additional steps, which would allow us to modify this set further (allowing us to add or subtract members), but our example will only have one step. Now we need to build the set.

Building Sets

Building Sets is the process of the Set Analysis engine using the definition of the set to generate the appropriate SQL statement(s) that brings back the correct records. It will then insert those records into our set tables for future reference.

In the screenshot above, you can see the cursor next to the Build… icon. Click build now and watch as the set engine identifies 623 members for the set. Until you build the set, your set will have zero members. You an always see how many members a step and the last date/time the set was rebuilt.

If at any point you edit the building sets of a static set, the set will be automatically emptied (all set members will be deleted) and you will need to rebuild the set.

Dynamic Sets

The process of creating dynamic sets is very similar to that of static sets, except the set will be processed for specific time periods. Let’s create a dynamic set of Gold Customers. Gold Customers are customers who have made volume purchases and purchased more than $3000 of products from us in each period. We will again use the Visual Data to build our new set.

First we need to name our static set and set some attributes for our set. Our set will be called:

1. 1 – Gold Customers
2. Folder: Customer Segments
3. Description: Our Worldwide Gold Customers
4. Scope: Public
5. Set Type:Dynamic
6. Calendar: DM_Monthly
7. Membership History: Temporal (allows entire history of set membership to be kept)
8. Allow Auto-Fill: Yes

Click Apply. Now we need to build our first step. Let’s choose Add > Visual Data.

First, when specifying dynamic, temporal sets, it is important to specify the time element as the first component of the set detail. We want to make sure we are only including customers who have made a purchase is the current period. Because the sales date is part of the Orders Subject, we need to choose DM Orders.

Use the arrow to move the Start Date to the right hand side. You will be prompted to specify the Added Field Condition. We will need to specify, Start Date, between, with the values of Relative, Fixed, start of processing period and Relative, Fixed, end of processing period. The Yes/No condition should be selected.

In our case we will see that 18107 records are No. By default No may be selected. Make sure and change it to Yes.

TIP: Usually you will want Yes/No conditions to be Yes, so under Options (above the first Yes/No Condition line), you can chose the setting: Default Yes/No conditions to Yes.

Next, let’s look for Contacts who have purchase at least 12 items from us in a single monthly period. So chose Sum of Volume. You need to be careful here. Notice how we chose Sum of Volume. This is critically important, otherwise it would only choose customers with sales of one transaction with a Volume of 12. That’s not what we want. We want to look at total volume for the entire period.

Finally we want Contacts who have purchased more than $3000 of items in the same period. Let’s chose Sum of Actual Sales greater than 3000.

Now chose Next>> and enter a description: High Volume, High $$ and click OK.

After creating the set, BusinessObjects gives you the option to Autofill the set. The Autofill process uses the set engine to populate the historical values for the given set to a predetermined number of periods. In our case, we will autofill all 17 monthly periods from Jun-00 to Sep-03.

Click on AutoFill/Rollback…, Complete (radio button) and OK.

Now the request to process the set historically will be sent to the Set Analysis engine and after it processes all 32 periods, you can click on the Membership view and see the an historical view of the set.

Here you can see each period and the number of joiners, members and leavers.

Create two more sets for Silver and Bronze customers with volume purchases. I recommend Silver as Volume: 12 or more, Sales: 1000 – 3000. I recommend Bronze as Volume: 12 or more, Sales: <1000.

NOTE: If you want to get more members for each period, you can drop the volume requirements and only look at Amount of Sales.

Tips For Organizing Sets

It is important to note that you cannot create subfolders, so I recommend you create development standards when it comes to sets. Because all folders and sets are organized in alphabetical order, I recommend that you use the colon “:” to differentiate folder depth.

For example:

Level1
Level1:Level1a
Level2
Level2:Level2a
Level2:Level2b

In the same way, use colons, numbers and names to organize your sets (1, 2, 3 or A, B, C will help create order.) For example, if I am going to create three sets which are mutually exclusive, such as Gold, Silver, Bronze, I would name them:1-Gold, 2-Silver, 3-Bronze. Now they will appear in the correct order. If however I am going to put more than 1 set in the folder, I might called it ProjA:1 – Gold, ProjA:2 – Silver and ProjA:3 – Bronze. That way I can call the next ones ProjB, etc (or whatever project name made sense in my situation).

Try to keep set names to less than 25 characters.

Consider using other symbols such as asterisk(*), plus(+), etc at the end of the set name to indicate addition important information (such as the fact that this set depends on another set).

If you don’t organize your sets, they will become cluttered. Another advantage of these types of naming conventions is that all this set information is stored in a relational database, so we can create BusinessObjects reports which can help us keep our sets organized. We can parse out colons an other special characters which may have special meaning to the organization.

TECHNICAL DETAILS

set_set – contains the information about the specific sets

set_set_details- contains the information about the set members. We use set_set_details to determine the joiners, stayers, leavers for a specific time period (typically monthly)