So How Exactly Does Set Analysis Work?
Set Analysis works together with the tables that are already part of your Data Warehouse. For example, in your Data Warehouse today you have customers, products and orders, etc. Therefore all the tables that below to Set Analysis must reside in the same database as your datasource. (From this perspective, I like was SQL Server allows you to do. In SQL Server, as long as you specify the database.owner.table, you can have the database tables in physically different database instances. It works because they can share the same connection.)
Installing SetAnalyzer
The first thing you might have problems with is finding the Set Analyzer software. Although the engines for using Set Analyzer are built into the BusinessObjects Platform, you still need to install the SetArchitect legacy client-server software to do some minimal configuration.
Traditionally BusinessObjects provided SetArchitect as a seperate download; however starting in XI 3.0, it is located with your BusinessObjects XI 3.0 installation media under the Add-Ons directory. The actual file path is: D:BOEXI30_Install_Fileswin32_x86CollateralsAdd-OnsSetArchitect.
You may have trouble finding the documentation for the legacy Set Architect client application. The documentation will be installed when you install the Set Architect Add-on from the BusinessObjects XI 3.0 installation media. However if you want to see the documentation prior to the installation you can download it here:
NOTE: Traditionally Set Analyzer existed as a client-server application, but once it was integrated into the BusinessObjects Platform, it no longer became necessary to use Set Analyzer. Business Objects has moved all but some very basic configuration steps to the web-based Performance Manager interface.
Set Tables
The tables which belong to the Set Analysis application all belong with set_. There are just over 100 tables in all. These tables definitions get created one of two ways. The set_ tables are created when:
- The Business Objects administrator creates the repository from the Dashboard and Analytics Setup Screen. From within System SetupRepository, the administrator defines the repository connection and “Creates” the Performance Manager tables. When using this method, not only are the set tables created, but the tables for all the BusinessObjects Performance Manager Engines are created. (Standard Method)
- The Set Analysis Administrator uses Set Analyzer Architect to “Setup” the repository. This method will only create the set tables for the Set Analyzer application. (Legacy Method – Not Recommended)
Once the Set Analysis tables have been created, you will be able to begin configuring your Set Analysis Data Sources. In a typical BusinessObjects Performance Manager Repository, this is what you would see:
Defining Your Data Sources
When setting up Set Analysis for the first time, there are only two tasks that need to be performed from within the legacy Set Architect application.
- You will need to define a connection, so that Set Architect understands how to connect to the database which contains your data warehouse data and the set analyzer tables.
- Next, you will need to set the metadata for your data warehouse tables so that set analyzer understands how to query the underlying database and create the sets.
Optimizing Your Data Sources and Set Analyzer
I highly recommend that if possible, you create database views on top of all the data sources that you plan to use for creating sets. For example if you have a customer table with 5 or 10 associated look-up tables for things such as geography, demographics, customer details, etc; then join these tables together and create a master view. Be aware that it is very important that your view NOT contain duplicate customer keys.
in Set Analyzer you need to define subjects. These are non-lookup tables associated with the fact table. In traditional data warehousing venacular they would be called dimension tables. These dimension tables need to be defined as Subjects in Set Architect.
If there is a many-to-many relationship between two tables, then I would need to create two subjects for each of the two tables. For example, if products have many suppliers and suppliers supply multiple products, (e.g. a many to many relationship exists), then I will need to define suppliers and products as Subjects. This will allow me to create complex sets such as:
- All suppliers who supply less than 10 products.
- All products which are supplied by suppliers in Georgia, who supply at least 5 products.
In my next post, I’ll provide screenshots of a sample metadata configuration as well as an excel spreadsheet for you to download and use yourself. All you will need to do is import the data into your favorite database.