Archive for the ‘Set Analysis’ Category

Sets Integrated into the Semantic Layer

August 18th, 2009 6 comments

On August 5, 2009, the SAP BusinessObjects Innovation Center announced a prototype for moving Sets Technology directly into the Semantic Layer.

Potential Future of Sets

Since the EOL announcement of BusinessObjects Performance Manager and two of the three analytic engines, Process Analysis and Predictive Analysis, there has been an ongoing question on the future of Set Analysis.  I have long been an advocate of the incredible power and importants of sets to help customers get a better understand of how data is changing under the covers.  If you have 100 more customers, that’s great… but how many did you lose and how many did you gain?  You may have lost 50 and actually added 150.  What about my “Gold” Customers?  How many new Gold Customers do I have?  Where did they go?  Did they become Silver members?

We have many customers who have successfully implemented Set Analysis; however the solution had not had significant R&D investment for some time and was tied to the legacy metrics engine of Performance Manager.  How could this technology be integrated into the new XI 3.X platform?

Sets and the Semantic Layer – I’m in Heaven

The Innovation Center has delivered a prototype which show Sets integrated directly into the Semantic Layer.  This prototype is a plug-in to the universe that allows you to create these “sets” , static or dynamic – all in the query panel.  Semantic layer sets lets customers leverage the complexity of  sets natively within that environment.  This is a huge step forward.

Check out the 15 minute video on the SDN website

You can see that this implementation can support all the classic calendar sets which will support of joiners, leavers, stayers, which are at the core of Set Analysis.  In this labs implementation there is support for both Visual Data and Visual Sets; however I hope this is expanded in the future to support Freehand SQL and an imported list.

I think this is a fantastic new innovation and needs to be added to the core product ASAP.  If you think so too, let your voice be heard:

I’ll keep you up-to-date with additional innovations around sets and may provide some additional posts on this topic if there’s interest.  Let me know.

«Good BI»

Set Analysis Part 5b: Custom Groups in WebIntelligence

June 26th, 2008 7 comments

So how we do do this exactly? How can I use BusinessObjects to create custom groups on-the-fly? How can I get beyond my requirements for multi-pass SQL and speed up my reports? The answer is Sets.

Set DataOn Target with Set Analysis

All the information regarding sets is located is the tables called sets_*. The most important tables are:

  • set_set – contains the set name, description, etc.
  • set_set_detail – contains all the set member information including member key, join date, leave date.

Here is a universe I created that you can download and use with the sample database provided in my earlier blog post. We need to add the set_set and set_set_detail tables to our WebIntelligence Universe so that the set member information they contain can be used within our query.

Custom Groups in WebIntelligence

Here you can see that we created a join to the DW_CRM_CONTACT table. We also created a self-join on the set_set_detail table. The self join make sure that we reference the edate value of 12/31/2999, which is the default edate for current members of any set. In the case of a dynamic set, If a member had left the set then the edate would not be equal to 12/31/2999.

In addition to this, we also joined the set_set table to the set_set_detail table using the setversion_id key. (It may appear that the setversion_id is always the same as the set_id, but it is not so only join the tables via the setversion_id)

Here is a look at the Custom Product Groups

Custom Groups in WebIntelligence

BEST PRACTICE: Whenever you may need to alias a table. If you alias a table, then all references to that table should be aliased. You can see that because I needed to reference the set_set_detail and set_set tables twice, once for products and once for contacts, I aliased both.

User Prompts

After joining the tables, we needed to create the filters for our custom groupings to make it easy for users to select which groups they would like to use on the report.

Creating a Group Filter in the Universe

I specifically defined the filters a multi, which will allow the user to select more than one filter. This is important because it provides even more power for your end-users. Now they can do direct comparisons of one group to another, even if a member (e.g. contact) exists in more than one group. The only downside is that it means that a member can be counted twice, this is why I recommended that when you define your sets you should use naming conventions that identify the sets as being mutually exclusive.

Here is what the prompt will look like in action:

BusinessObjects Universe Prompt

In my universe I did not use cascading prompts to first limit the sets by folder, but that is certainly an option since folder information is located in the set_project table.

Getting Results

After configurating the universe and setting up the prompts this is an example of the results you will see. I have run a report and selected multiple custom groups which are mutually exclusive. These groups on based on the current periods sales amounts:

Here you can see the report is broken down by my custom groups which were defined and processed by the Sets Engine.


I’ve really enjoyed taking you on a quick tour of Set Analysis and showing you some of the ways that you might get more value out of your existing data assets. Sets offers so many solutions around data analysis:

  • Custom Groupings – which allows the organization to more nimble as changes such as mgmt structures, mergers and acquisitions take place.
  • Classic Segmentation – Joiners, Leavers, Stayers (which in turn are perfect for analysis by a predictive tool so that you can see why are people joining or leaving? What are the influencers?)
  • Advanced Analysis – “Give me all quad-pack customers who have never purchased phone accessories from a company owned retail store.”

The ability to divide customers up into categories of: highly profitable down through :unprofitable, and using that to drive the business because the bottom line of any business is the customer and your organization’s ability to make a profit.

Although dashboards are currently in vogue and everyone wants one, I often wonder how organizations are analyzing the information beyond the dashboards. I have nothing against dashboards, but I would encourage you to ask yourself this. Once we see that sales are falling, what am we going to do? How am we going to do root cause analysis? Were all customers affected? What about products? Segmentation might well hold the answer to these yet unsolved mysteries.

Set Analysis Part 5a: Custom Groups in WebIntelligence

June 25th, 2008 4 comments

Just What the Doctor OrderedI recently had a customer who was in the process of migrating from a legacy version of Microstrategy to BusinessObjects. They had been able to migrate over most of their core reports, but there were several reports that required a number of custom groups.

Microstrategy’s approach was to allow the customer to define any number of groups and filters from with the Microstrategy Desktop tool. These global filters and group definitions can then be leveraged by the report editor for building the report. Microstrategy’s SQL generation engine uses multi-pass SQL when creating the reports. Multi-pass SQL means that a single SQL statement cannot deliver back the correct results, so the report data is written into temporary tables. These temporary tables are then combined with additional queries to obtain the final results.

In the case of BusinessObjects, it wasn’t going to be possible to take the same approach. BusinessObjects does not generate multi-pass SQL. Instead, BusinessObjects focuses on creating extremely complex SQL in a single SQL statement. In most cases, this works just as well, but this was an exception. This sales report used a combination of multiple filters at multiple levels in the heirarchy. The Heirarchy was also defined within the custom groupings and not within the database.

The right answer is: Modify the Data Warehouse and ETL processes to make the underlying data better fit your reporting requirements. You see, the problem was that these custom groups should be defined within the data warehouse natively. This would allow for simplified SQL that can be run in a single pass. Translation? Reports the use custom groups will run a heck of a lot faster.

In my situation, the customer wasn’t going to be able to make these changes, but still wanted to migrate the report to BusinessObjects.

The best answer is: Use Sets. Sets gives you the ability to generate those custom grouping within the data warehouse. Unlike Microstrategy which will run the same multi-pass SQL over and over again, sets will allow us to “cache” the previous passes using sets and run single-pass SQL. We can then tie the set tables into the data warehouse and add the custom groupings to our universe.

The advantage of sets over ETL is that it can be much more dynamic. Sets can allow an analyst to create a new set in a matter of minutes and then see a report broken down by the new custom group.

In my case it was just what the doctor ordered. By introducing sets and adding the set tables to the BusinessObjects semantic layer, we now have access to the sets, which in this case, the customer called custom groups. It was perfect.

Now, not only will the customer be able to migrated this complex report from Microstrategy to BusinessObjects, but they will also be able to run the report much faster than ever before because the report will be generated through a single-pass process.

Tomorrow, we’ll take a look at how to set this up using our simplified data.

Set Analysis Part 4b: Creating FreeHand SQL or Importing

June 24th, 2008 No comments

A less common way to create sets is to create sets is through free-hand SQL. Normally you will want to leverage to power of the set engine to design the SQL for you to return the correct results, but in rare cases you might find it helpful to create your own SQL Statements.

Free Hand SQL

The only item that needs to exist in the SELECT clause is ID. If an ID is specified and the SQL is valid, then the records will be imported into the set tables, if not, you will not be able to chose “Parse” to validate your SQL.

For our example, we can create a set called “All Customers” and use the following FreeHand SQL.

select CONTACT_ID as ID from dbo.DW_CRM_CONTACT

Set Analysis with FreeHandSQL

The SQL may be as complex as you require. As long as the SQL is valid, you should be able to insert it here and use it with Set Analysis.

I am not aware of a limit for the Free Hand SQL.


Set Analysis also provides the ability to import individual records into the set tables. This is valuable if users have exported information into Excel and manually developed a list of records they wish to add to a set.

You can either store the text file in the BusinessObjects Repository or on the physical filesystem. Unfortunately the schema definition is pretty limited, so I recommend you use one of the sample files below as a guide. As long as the file contains at least 1 column and the key is the FIRST column, the import will work fine.

Click on the Import button and pick your file. Do not type in the name manually. If necessary, you may need to define a file format. I recommend that you use comma delimited files with a double-quote text qualifier (“).

Here is a sample import file of DM Contacts.

NOTE: In order to use the Upload feature, a c:tmp directory must be defined on your system.

NOTE: If you store the file on a filesystem, you may need to make sure that the Sets Query Service (AAQueryMgr.exe) has rights to access the file across the network. By default all BusinessObjects services run as LocalSystem which means by default they will not have access to UNC file paths

Set Analysis Part 4a: Creating Visual Sets

June 23rd, 2008 No comments

I’ve decided it’s probably a good idea to break down these tutorials a bit. Although I love to share all this information, it’s probably getting a little long for a single post.

Visual Sets

When creating sets in Set Analysis, Visual Sets are extemely helpful. Visual Sets allow you to quick look at the union, intersection, exclusion of multiple sets simultaneously. Let’s say for example I want to see all Gold Customers who are also High Tech Customers but have NOT purchased products from our computer department. Let’s assume I already have the following three sets:

  • Gold Customers
  • High Tech Customers
  • All Products from the Computer Department

Let’s create a new static Set using DM Contacts, called Gold Customer – No Computer. Next we will add a Visual Set. We will chose 3 sets. One set for each of the three Collections.

Visual Set

  • Collection 1(blue) – add Gold Customers (All Gold Customers)
  • Collection 2 (red) – add High Tech (All High Tech Customers)
  • Collection 3 (yellow) – add Computer (All Products in the Computer Department)

In our case we want all Gold Customers who have NOT purchase Products in the Computer department, therefore we do not want to select anything in the yellow circle.

I can click on Counts at any time and the system will show many how many records are in the selected set.

From the selected venn diagram regions, I can see from the displayed count that there are 78 customers who fall into this category. Click Next>>, specify the description and OK to save the set.

Remember, just like before we must build the set before it will contain any objects. Once you build the set you will see there are 78 records retrieved.

Isn’t that easy? How cool is that?

NOTE: Remember how we specified Converters in Set Analyzer Architect? This converter allows us to chose information from DM Products, but have it converted products to contacts. In our example, it converted “products in the computer department”, to contacts who have purchased products from the computer department.