Posted on Leave a comment

Increased Performance Through Optimised Semantic Layer Design

SAP Business Objects Universe

SAP Business Objects UniverseThe semantic layer sits between the reporting tool e.g. Web Intelligence and the data warehouse. It is a type of graphical abstraction of how the data warehouse tables and relational joins would look if they could be viewed with the naked eye.

The critical function of the semantic layer is aptly describer in its name. All data warehouse platforms are interrogated through some type of code. The universe generates code (hence semantic) so that a query can be issued against the data warehouse. This code will be in the form of Structured Query Language (SQL) for relational platforms and Multi Dimensional eXpressions (MDX) for OLAP based universes.

The powerful feature of the semantic layer is that the business information consumer does not need to understand data warehouses or write code. The business user simply drags and drops objects of interest from a side panel on to their report page, e.g. sales revenue and location objects to generate a table displaying sales revenue by location. The semantic layer generates the code automatically behind the scenes.

However, the optimization of the code generated will be dependent upon the structure of the data warehouse tables and semantic layer configuration for relational tables and the cube and query design for OLAP sources.

Whilst it is possible to generate a semantic layer in a few minutes using an automation wizard, the resultant performance of queries may be disappointing. By performance is meant fast queries that return accurate data sets.

With experience it is possible to gain a tool box of best practice techniques to build a semantic layer designed for performance. As a semantic layer designer a good starting place is to build a query and then to ask:

What does the code generated by my semantic layer look like?

If I were to write it freehand in an optimised way, would it be different?

Do I need to change the semantic layer configuration or data warehouse structure to achieve optimised query code?

Leave a Reply