Why use a Semantic Layer?

Recently I had a customer ask me about using a semantic layer.

They said:

I am unaware of anyone using a metalayer (Universe) for Business Objects Enterprise XI. Most developers will build the report connecting directly to Teradata (or other sources; Oracle, SQL Server) and then schedule the report on XIR2. I will normally build the SQL first and then plug it into a Command Statement in Crystal Reports using dynamic date ranges to eliminate user selectable parameters for dates. Others do use parameters, but it’s discouraged due to both the response time from Teradata and processing overhead can not be managed as effectively.

I thought this was a great question. Sometimes you are get so comfortable with the technology that you think everyone recognizes the elements of a good Business Intelligence system and understands the advantages and disadvantages of different infrastructure elements. The semantic layer is definitely secret sauce of BusinessObjects and it is the primary reason why it was able to grow to become the largest provider of Business Intelligence software.

What is the Semantic Layer?

The semantic layer is a business translation layer that sits between the database and the end user. This means that user can interact with their data using familiar business terminology instead of having to understand where the data resides and what business rules to apply to the data.

What are the Advantages of a Semantic Layer?

Having a semantic layer on your database allows IT organizations to:

• Guarantees Correct Results – by applying rules to define database complexity and ambiguity. These rules drive the generation of the SQL and guarantee that if two users ask for the same information, they will get the same results.
• Guarantee Database Performance – by always generating the best SQL possible
• Guarantee User Understanding and Acceptance – by allowing users to understand how modifying their query will result in different results, while at the same time giving them independence from IT. The #1 complaint from most business organizations is the amount of time it takes IT to build reports for them. They want the independence to be able to build their own reports and know that the results will be correct.

A semantic layer will be able to create sophisticated SQL and in many instances may need to generate multiple SQL statements in order to return the correct results (chasm trap/fan trap). The semantic layer must understand how to deal with database loops, complex objects, complex sets (union, intersect, minus), aggregate table navigation and shortcut joins.

For more information about some of these concepts, there is an excellent presentation by Integra Solutions here

Most importantly, the semantic layer allows an organization to define a single version of the truth. That means that regardless of what application or user pulls information from the database, as long as they use the semantic layer, they will always get the same answer. Now that we are in a post-Enron/WorldCom age of corporate governance and Sarbanes-Oxley, it is more important than ever to make sure that there are clear definitions for revenue, margin %, churn rate, turnover, etc.

Implementing a semantic layer will put an end to conference pickering over who’s numbers are correct because now everyone can make sure they are using the same numbers.

What are the Disadvantages of a Semantic Layer?

Now, as much as I love all the business value that comes with the semantic layer, there is a cost. Prior to working for Business Objects, I was with Crystal Decisions and we didn’t have a semantic layer, so we had to figure out all the reasons why not the use it. (We did eventually release a semantic layer called Business Views in Crystal Enterprise 10). Therefore, I thought it would be good to share with you what we felt were the problems with a semantic layer.

The main contention was that the semantic layer was an “extra layer”. It’s not free. It has to be created, maintained and managed. It must be kept in sync with any database changes that occur. The other point was that you can use database constructs such as database views or stored procedures to accomplish much of what the semantic layer provides.

The final disadvantage to the semantic layer or at least the semantic layer of BusinessObjects was that it could only connect to one database at a time. In other words, if you had data in MS SQL Server and Oracle, you needed a semantic layer for both.

Semantic Layer Rebuttal

Although all these points were true, in fact the arguments were quite week. Although the semantic layer does have to be created, maintained and managed, it’s a lot easier to maintain 2 or 3 semantic layer definitions than it is to maintain 1,000’s of reports.

It also means that you don’t have to engage the database administrator directly to create views and stored procedures. Most large organizations would not be nimble enough to make changes quickly to constructs that live within the database. Typically these would not be able to be added or modified in a production database unless the next “major” database application release. In addition, you still have the issue of chasm traps and the need to generate multiple SQL statements if data exists in multiple fact tables. This type of complexity could not be handled by a database view alone and stored procedures are very inflexible. They must be programmed. The bottom line is that the SQL generated by a stored procedure or view is much more likely to be less efficient than the semantic layer.

It is true that BusinessObjects can only connect to one data source per semantic layer, but our report tools provide two alternatives you can either:

1. C reate a report pulling data from two universes and merge it in the report
2. Implement Data Federator which will pull information from two different data sources in real time using a single semantic layer definition.

Finally, a good semantic layer allows you to add query governors onto your queries so you can limit:

• how much data is pulled back
• how long a query will run
• who can connect to which dataset
• what row and column level security might be applied (e.g. I can only see “NY” data because I’m a manager in NY, or I cannot see the salary column in the HR database because I’m not a supervisor.)

Moving Beyond Relational Data

What about non-relational data sources like cubes? Well, cubes have their own built in metadata. Hyperion Essbase calls it an “Outline”. Its a definition of how the cube is constructed. The person who designs a cube must define what the dimensions and facts of the cube are going to be. Because the predescribed definition is required for a cube, the semantic layer in essence already exists in a cube.

In the case of BusinessObjects, we read in the metadata from the cube and build the equivalent structure within our semantic layer. In addition, we will change the type of data querying we perform. Instead of generating SQL for example, we will generate MDX for MS SQL Server and SAP BW.

Conclusion

A semantic layer is a key component is any truly successful Business Intelligence implementation. It will help you to deliver a single version of the truth to your business users, while providing the safety and security to your IT department to guarantee that users will only access data they are allowed to access, while now allowing them to accidently run a table scan against a 100 million row fact table. One of the hardest things about creating reports is knowing where to get the data and validating that the data is correct. The semantic layer delivers both of these in spades. Report developers can let the semantic layer ‘find’ the data for them and because it’s coming from the semantic layer, it must be correct because it’s already been validated by IT.

Remember: The semantic layer as the semantic center between your two halves of your BI cookie.

Hmmm. Good.