Home > Semantic Layer, Set Analysis, Web Intelligence > Set Analysis Part 5b: Custom Groups in WebIntelligence

Set Analysis Part 5b: Custom Groups in WebIntelligence

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.

Summary

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.

  1. Roberta
    February 23rd, 2009 at 10:22 | #1

    Hi David,
    I’m newbie with Set Analysis and Set Architect. I’m reading these posts and I think they’re so helpful, but I tried to follow the links to set analysis and set architect users guide and I found they’re not reachable anylonger. How can I do to read the users guides?
    Thank you so much
    Roberta

    • February 23rd, 2009 at 17:46 | #2

      Roberta,

      Thanks for your comments. I had a misspelling in the URL. It is fixed now. You should able to download everything now.

  2. Reddeppa
    March 25th, 2010 at 03:55 | #3

    I have downloaded sample database but i didn’t get Set_* Tables

  3. Cris E
    April 6th, 2010 at 09:48 | #4

    The second to last graphic has a bad url and isn’t displaying. It should be http://trustedbi.com/images/blog/sets/ContactGroup_prompt.jpg (Yyou have a capital P in the word Prompt on the page.)

  4. Vamsi Ch
    April 28th, 2010 at 11:48 | #6

    Hello Dave,

    Thanks for your in-valuable blog posts.We have a similar requirements to do some groups in prompts but our source is SAP BW and BEx queries.How to implement Set Analysis with SAP BW ?

    – Vamsi Ch

  5. May 3rd, 2010 at 15:30 | #7

    You would most likely need to replicate these data structures in SAP. Unfortunately the “sets” functionality only works with relational databases. I am a huge fan of sets. If you want SAP to continue to support this functionality make sure and let ASUG and your SAP Account Manager know!

  1. No trackbacks yet.