We have talked a little bit about how we can have multiple logical table sources behind each logical table. This supports the federation and fragmentation of data, and is an extremely important part of OBIEE's capability. This is an advanced topic, but we will provide an introduction to advanced LTS functionality with an example of fragmentation. There are three main scenarios where we require multiple logical table sources.
This is used when we have aggregate tables in our schema. An aggregate table is a summary of our base data at a higher level of one or multiple dimensions. This is usually created to improve query performance. We can introduce an aggregate table as a new LTS. As we have done with our previous level-based measure, we can set the content for the whole LTS to the appropriate level in one of our dimensions. Logical columns will be mapped to both sources and OBIEE will then choose to utilize the appropriate source depending on the grain of the query run. This is an extremely common scenario, and as such we will go into more detail and a step-by-step example of this in Chapter 12, Improving Performance.
This is where a fact or dimension has multiple table sources at the same grain, but they contain different column information. Columns will be mapped to one of the sources and OBIEE will choose a single source table or combine them depending on which columns are chosen in the query.
An example of horizontal federation is where we want to combine two sources at the same granularity as one logical table. An example would be that we have financial actuals information and budget figures in different sources, but want to be able to report on them as a whole. These physical sources would not even need to be in the same location, that is, we could combine data from two different databases.
This is where we have multiple physical tables for a dimension or fact, but this time these tables have the same set of columns and they differ in the information or rowset contained in each column. For example, this is quite commonly used when we have a set of data that is rarely accessed and we want to save it on the database I/O.
So let's look at a scenario for content-based fragmentation. Our data warehouse development team have come back to us and said that for performance reasons they would prefer if they present the information for Accounts in two different tables--one for accounts in the East and another one for those in the West. We have created two new physical tables--DimAccountEast and DimAccountWest. These supersede the old dimension table of DimAccount. Firstly, we import them and add the appropriate joins in the physical layer, as shown in the following screenshot:
At this point, we could delete the current logical table source for Account in the business model and add the two new tables as two new sources. However, to save a bit of time we can rename and repoint the current source to one of the new tables. As you can see in the following screenshot, we have opened the Account logical table source. Note that in the Map to these tables section, we have deleted the reference to the
DimAccount
physical table and added a mapping to the new table for accounts in the East:
Now we move onto the Column Mapping tab and ensure that it is showing both mapped and unmapped columns (check boxes). The change in the LTS mapping to the underlying physical table means that some logical columns may not be mapped correctly to a physical column (or previous expressions). OBIEE will make a best guess by comparing column names, but if we have renamed the logical tables to use names with business meaning, it will struggle to map all of the columns. So we must ensure that all of these columns are mapped successfully:
In the preceding screenshot, we can see that we have now mapped all of the existing columns correctly. Note that we have also renamed the source DimAccount (East) so that it is clear what this LTS represents.
Now create a new LTS called DimAccount (West) and map it to the DimAccountWest physical table. We can do this by right-clicking on the Sources folder of the logical table and choosing New
Logical Table Source...:
Name and map the table and then the individual columns accordingly. As you can see in the following screenshot, we now have two logical table sources for one logical table. Also, if we open a logical column as we have done in the following screenshot, we will see that each one is now mapped to two physical columns. So how does the OBIEE server know which physical table and column to use?
Well, as you may have seen in the General tab, there is a priority option for the LTS called Priority Group. We could set one as a higher priority over another, and the OBIEE server would choose that one first. However, that does not make sense in this case as these tables are of equal priority, they just differ in their datasets. So we need to give an indication to the server of how they differ. We do this in the Content tab of each LTS. We can set a condition based on a column in the Fragmentation Content pane. Once this condition is satisfied, the associated LTS will be used in the query. We have added a physical varchar column to the tables called Region that has a value of E or W to differentiate between the two datasets. Let's bring that in to the logical layer and ensure it is mapped to both East and West tables. We can then use it in our condition, and also add it to the presentation layer.
You can see the condition set up in the following screenshot. Note that you can use the Expression Builder to create your condition. Also, we have to do the same for our other LTS, but this time the condition will be as follows:
AdventureWorksBM"."DimAccount"."Region" = 'E'
This is because we are denoting that this LTS has records for our East records:
Note that we have ticked the This source should be combined with other sources at this level option. This is important because if a query is made that does not filter on Region, we will need OBIEE to combine record sets from both of these sources in order to produce a result.
Repeat the same step for the DimAccount (West) LTS, but with the condition being "AdventureWorksBM"."DimAccount"."Region" = 'W'.
With that we are done with fragmentation for these tables. Note that at no point should you delete a logical column. This means that no presentation columns were affected and all of this work was hidden from the end users. It would not have impacted them in any way, and any previously created reports would have worked seamlessly.