Set Analysis Part 3a: Setting Up Sets Metadata

I have found that setting up the Metadata around Sets was one of the most confusing things about learning sets. The problem was two-fold. The interface of Set Architect is old and not terribly intuitive. It is little more than a database table browser with a few rules built in.

I typically learn by example, so I put together what I felt was the best combination of screenshots to show you the core of what you will likely run into when you need to configure Set Architect.

To download the sample database that we used for this example, please click here.

You may find trouble reading through the Set Architect manual before install the Set Analyzer client because the document isn’t available on our website. The document is contained within the installer program but I thought it might be a good idea to make it available here in case you need it.

• Set Analyzer Administrator Guide – Administration and Setup Guide, which primarily uses Set Architect (required for using Sets)
• Set Analyzer User Guide – Legacy documentation on how to use the Set Analyzer client tool. This tool was made obsolete because the primary interface for sets has been integrated into the Business Objects Performance Manager dashboard interface.

Metadata Configuration within SetArchitect

There are four core components of the metadata that need to be configured from within Set Architect. They are the:

• Tables/Objects – the database tables and the field definitions we will use when generating SQL to create our sets.
• Subjects – traditionally the dimension tables. Subjects are types of data that we might want to create a set of. For example Customers, Products, Suppliers, Contacts
• Lookups – tables that are used to lookup decodes, e.g. customer status, type, category.
• Converters – advanced metadata that tells Set Analysis how to convert a query from one subject to another. Converters are used for the most powerful types of sets.

Let’s look first at the database tables and database fields metadata, which is called Tables and Objects respectively.

Configuring Tables/Objects Metadata

We need to define the metadata for four tables in our example: CONTACTS, SECTORS, PRODUCTS and FACT.

CONTACT TABLE AND FIELDS

The first table we needed to set up is customer. The unique key for this table is Contact Id. Because the customer table is not a lookup table, we indicate that this is indeed a source table.

Most of this metadata can be imported automatically when you use the Load Tables and Load Fields button at the top of the interface.

Here is a screenshot of the completed definition. (Click on the image to enlarge)

I made only a few modifications to the default metadata that was imported using the import tables button. I added:

• to contact_id, the linked table DM Contacts
• to sector_id, the linked table DM Contact Sector
• a new field called Annual Income Band with the following formula (SQL Server Syntax):
  case when <a>.annual_income = 0 then ‘Unknown’ when <a>.annual_income < 30000 then ‘<30K’ when <a>.annual_income < 50000 then ’30-50K’ when <a>.annual_income < 75000 then ’50K-75K’ when <a>.annual_income < 125000 then ’75K-125K’ else ‘125K+’ end
• a new field called Age Band with the following formula (SQL Server Syntax):
  case when <a>.age < 30 then ‘<30′ when <a>.age < 40 then ’30-40′ when <a>.age < 50 then ’40-50′ else ’50+’ end

The new fields could have been defined within a database view (which is what I would recommend), but I wanted to demonstrate how they can also be added directly from within the metadata of Set Analysis. The syntax will be database specific. (There is much more information about this in the Set Architect Manual)

SECTOR TABLE AND FIELDS METADATA

The second table we needed to set up is sector. The unique key for this table is Sector Id. Because the sector table is a lookup table for customer, we indicate that this is indeed a Lookup Object and NOT a source table.

Here is a screenshot of the completed definition. (Click on the image to enlarge)

I made only one modifications to the default metadata that was imported using the import tables button. I added:

• to sector_id, the linked table DM Contact Sectors

As I’ve mentioned before you can define lookup tables from within Set Architect, but why? Why not create a new view and join the Contacts and Sectors tables into a common view on the sector_id? By pushing this to the database, you can simplify your configuration and you don’t have to worry about a lookup tables with lookups (e.g. state -> country -> continent).

PRODUCT METADATA

Next is the product table. The unique key for this table is Product Id. Because the product table is not a lookup table, we indicate that this is indeed a source table.

Here is a screenshot of the completed definition. (Click on the image to enlarge)

I made only one modifications to the default metadata that was imported using the import tables button. I added:

• to product_id, the linked table DM Products

FACT METADATA

The fourth and final table we needed to set up is fact table. There is no unique key for this fact table, but that doesn’t matter because we won’t be creating sets on top of orders. (If we needed to, then we would need an order_key which could be inserted into the set_ tables for linking). Because this fact table is not a lookup table, we indicate that this is a source table.

Here is a screenshot of the completed definition. (Click on the image to enlarge)

I made only a few modifications to the default metadata that was imported using the import tables button. I added:

• to contact_id, I added the linked table DM Contacts
• to product_id, I added the linked table DM Products
• a new field called Quarter with the formula (SQL Server Syntax):
  case when <a>.week between 1 and 13 then ‘Q1’ when <a>.week between 14 and 26 then ‘Q2’ when
  <a>.week between 27and 39 then ‘Q3’ else ‘Q4’ end

Keep in simple. In real life, if you wanted to add additional calculations like quarter or profit (sales_actual – cost), then do it in the database or through a database view. This will make your life easier when it comes to long term maintenance.

Once all the tables have been defined, we can move to Subjects.

DEFINING SUBJECTS

Subjects of a set are the central theme of the set. it will be associated with the table that most uniquely describes the subject for which sets are created. In our example, we are using contacts and products. We want to be able to create sets of both contacts and sets of products, so we will define two subjects.

Contact Subject

Here is how we will define the Contact Subject. (to enlarge click on picture)

Product Subject

Here is how we will define the Product Subject. (to enlarge click on picture)

Next, we need to check our lookups. This information should already be defined based on the changes we made when defining the sector table and defining it as a lookup for contacts.

DEFINING LOOKUPS

Here is how we will define the metadata for the sector lookup. (to enlarge click on picture)

DEFINING CONVERTERS

Converters are just plain cool. When I went through training on sets, I never really grasp what it is they do or their role in defining sets. Converters are used to allow you to convert from one subject to another. Clear as mud? Well, actually they allow you to define sets based on the “related” subject. Here are a couple of great examples:

• The set of customers that buy these products
• The set of products that have been purchased by these customers
• The set of other products sold with these products

So now you can track the set of Gold Customers who have purchased Premium Products during the period.

Converting Contacts to Products

The SQL we used for our converter was:
select distinct PRODUCT_ID as ID
from DW_CRM_FACT_ORDERS
where CONTACT_ID in ( <sub_query> )

Converting Products to Customers

The SQL we used for our converter was:
select distinct CONTACT_ID as ID
from DW_CRM_FACT_ORDERS
where PRODUCT_ID in ( <sub_query> )

The help text is completely optional and is not exposed to Set Analysis within the Performance Manager dashboard.

CONCLUSION

We’ve had an opportunity to walk through an example that covers the most common scenarios that I’ve seen in the field. If you have additional questions or comments, please list them below. I will use the database and metadata associated with this article to demonstrate the power of sets.

Technical Footnote

The information is stored in the following tables:

• set_table – contains Table metadata
• set_field – contains Field/Object metadata
• set_transform – contains the Converter metadata

Additional metadata we didn’t cover above:

• set_calendar & set_period – contains the calendar information that is generated by the Performance Manager engine from within the dashboard interface.
• set_user – contains the user login information
• set_user_group – contains the user group information
• Other tables of interest around security include set_profile, set_grp_member, set_grp_source, set_grp_subj.