Oracle has a very nice reporting tool, called OBIEE. It is positioned on top of their database. This allows to exploit the data. To do so, a separate (Weblogic OBIEE) server is created that processes the data for reporting purposes. So on the server side, at least two server processes are running: the DBMS and the weblogic OBIEE server.
When a report will be created, data must be stored in DBMS. Let us assume, we have this set of tables in the DBMS.
The first step is to add the metadata on these tables to the repository in the Weblogic server. This repository is maintained by the Oracle BI Administration tool. This tool is a client tool that can be installed on the Windows platform. I noticed that the version number of that tool must exactly match the version of the Weblogic OBIEE version. In my example I used version 12 of both the server and the Oracle BI Administration tool.
The Oracle BI Administration tool has 3 different layers: a physical layer, a business layer and a presentation layer.
The physical layer is a mirror from the actual database. We copy the tables, relevant fields and relations from the database. The only difference between the database and the definition in the repository is that irrelevant fields are excluded. Let us show how the definitions look like:
The model above shows that the table names and relations are a 1 to 1 copy from the database. the tables have less fields as irrelevant fields are omitted.
The second level is the business layer. In the business layer, we apply the business logic. We have three steps here.
- The tables are modified to get a proper star like model with a fact table that is surrounded by denormalised tables whereby each dimension has one table only. It is applied here where 4 tables are joined together to form one geographical table. More specifically, winkelfiliaal, countries, topgrenzen_prov_actueel, and topgrenzen_gem_actueel are joined together in one table that contains geographical information.
- An hierarchy is created. This hierarchy shows that the total can be decomposed into information on countries. On its turn countries can be decomposed into provinces. Further to gemeente. Finally the individual shops are provided.
- Invidual fields are modified. An an example, some fieldnames are adjusted to make them more descriptive.
Above, a screenshot is provided that shows the work in the business layer.
- To create a dimensional table from several physical tables, a so-called logical datasource is created. The definition of the logical data source contains the physical tables and the joins between them.
- The geographical hierarchy (total>country>provincie>gemeente>shop) is created in several steps. Starting from total each underlying level is explicitly mentioned. On the bottom level a shared detail level must be created. On each level two fields are added: one field is used to indicated the identifiers that are used to link the tables. Another field is used to display the levels. Here a descriptive name can be used.
The third level is the presentation layer. Here, we only include the fields that will be used to create the reports. This implies that technical fields can be omitted. So, primary keys are only included if they are understandable by the user; the same holds for the foreign key fields. We get:
Within table “omzet”, the foreign keys are removed. Such fields only have a technical meaning and they can be omitted as they have no relevance for the user.
Also identifiers for the levels countries, provinces, and gemeentes are omitted for the same reason: no relevance to the end user.
The tables are collected in a subject area “Voorbeeld2”.
We have two kinds of data in the presentation layer: fields that be used for the report and an hierarchy the allows to jump from level to the level underneath.
Now the repository is updated. Now we can turn to the analysis. The analysis can be started. We do this from the internet site that allows to connect to the OBIEE server. The address is something like http://192.168.2.36:7780/analytics. Upon logging in, one may start the analysis. We get:
On the left side, one sees the items that were created in the presentation layer. As we left out all elements that only have a technical function, we only have items that are understandable by the end user. The user may select from the fields that are indicated with a square. We also see the hierarchy that was created.
The existence of the hierarchy becomes clear if we look at the results. One sees “country” as a hyperlink:
.
Clicking on this hyperlink displays the next level. Clicking again quickly leads you down to the lowest level of detail:
