Skip to Content
Author's profile photo Matthew Shaw

Use of Semantic Layer over ‘free hand SQL’.

Web Intelligence doesn’t support ‘free hand SQL’ and for some that use Desktop Intelligence in XI3, this is a problem to migrate to BI4 and Web Intelligence. Yet, if the semantic layer was used, this problem wouldn’t be so much of a problem . So why are some report developers still not using the semantic layer?

Well the answer, I believe, is that they don’t fully appreciate the benefits the semantic layer has over ‘free hand SQL’. This blog talks about the benefits of the semantic layer over the problems of ‘free hand SQL’.

When documents (Desktop Intelligence or even Crystal Reports) are built on ‘free hand SQL’, columns that are not used in the report have the following problems:

  • Document sizes are much larger bigger than they need to be. This is because it’s not easy to remove columns from the SQL as you might have to change, within the SQL, ‘joins’ and ‘group by’ clauses etc.
  • Instead of aggregation occurring in the database, it’s occurring in the report at a cost of the infrastructure. SAP holds the patent for ‘semantically dynamic’ and this technology ensures that as much of the aggregation that could occur, does occur in the database.  Free hand SQL isn’t semantically dynamic, so the load on the BI Platform is greater than it needs to be and the underlying database isn’t used for used for all its ‘power’. The free hand SQL isn’t enabling the right tiers of technology to be used in the right way.
  • The agility of the report to be manipulated is reduced, simple because of its unnecessary size. User’s need to wait longer to open and display a document than is necessary.
  • There is no guarantee the SQL or the results are really accurate. (If the ‘semantic layer’ has been built correctly, the SQL and the results are always correct)

Removing a ‘column’ (an object) from a document built of the semantic layer is trivial and has none of the problems above.

Besides these problems, there are many benefits of using the semantic layer:

  • The SQL generated by the product can be far superior to most experts SQL! SQL manually generated tends to be ‘easier to read’ than ‘more performing’. The SQL generated by the toolset is quite spectacular and will often outperform free hand SQL simple because it has so many performance optimisation capabilities. With most or all these capabilities enabled, the SQL generated can be very complex and actually quite tricky to read, as ‘power’ is the priority compared to readability.
  • Ease of creating and changing the query to ask the database different questions is almost impossible with free hand SQL unless you are an expert in SQL and you know the underlying database exhaustively. Compare this to using a semantic layer that any business user can consume and no technical knowledge is required. The business user is completely shielded from the technical complexity and the users are guaranteed the right results every time. Indeed with free hand SQL it’s not easy to build, understand or maintain the SQL, yet with the semantic layer the opposite is true and additional analytical capabilities are easier to implement such as
    • Ranking
    • Sampling
    • Query merging
    • Complex sub queries to meet complex business questions
  • The semantic layer does more than just generate SQL; it informs the product how ‘BusinessObjects’ (objects) should behave within the report itself. For example the semantic layer defines the drill path and how aggregation should be performed within the report. These features ease the building and interaction with the report, so improving the overall engagement and adoption levels and reducing technical complexity.  This isn’t possible with free hand SQL.
  • There is no central control over the SQL with free hand SQL. Should the database change, corrections needs to be made, or improvements made, then each and every document containing that free hand SQL needs to be inspected and manually updated. Compare this to the semantic layer where one change is made and that change is automatically propagated to all related documents.
  • There is no meta-data associated with free hand SQL unlike the semantic layer that provide users with information such as a description. This provides the user with essential information to what a column (object) means. The value of a ‘description’ should never be underestimated! Additional meta-data such as lineage information can also be provided and directly accessible from the report. Lineage information can be provided automatically if the Metadata Manager tool (for XI3) or the Information Steward tool (for BI4) is used within the platform for Web Intelligence documents. These tools provide lineage information (where did this information come from) and impact analysis (what and who will be affected if I change this source system)
  • The semantic layer is the underpinning of the platform. So it’s easier to adopt other tools on the BI Platform. These tools provide greater accessibility to Business Intelligence and these tools are likely to be new users to the BI Platform; thus more of your business will have access to Business Intelligence to make more informed decisions. Examples of these tools are: Live Office, BI Mobile, Explorer and Xcelsius (Dashboards).

All these points above add to the cost of ownership of the free hand SQL compared to the semantic layer. So if you’re using ‘free hand SQL’, think again! Think ‘semantic layer’!

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Andreas J A Schneider
      Andreas J A Schneider

      As much as I share the pro stance Semantic Layer attitude I have come to appreciate the flexibility offered by Freehand-SQL. I have long dismissed this and rallied for the Semantical Layer, but .... my views on this have changed.

      To offer customers flexibilty I do support the notion of Freehand-SQL data providers in Webi. After all SAP Lumira offers it as well and so has Crystal done for ages.
      It is important though to also offer the following functionalities when introducing Freehand-SQL data providers for Webi:

      1. Being able to audit/list all Webi documents, which use a Freehand-SQL data provider
      2. Being able to audit the SQL code of any Freehand-SQL data provider (as text for further parsing to search for example for certain keywords such as table names, columns, etc.) in any Webi document. This will enable impact analysis.
      3. Freehand-SQL data providers should be forced to only use a secured "universe" connection". In other words, a central connection (a.k.a. as universe connection) has to be created first, which can then be used by a Freehand-SQL data provider. This allows for central administration of all database connectivity used by Webi.
      4. To be able to grant or deny rights (via CMC) to create Freehand-SQL data providers on a per user and per connection basis.

      I believe the added value of Freehand-SQL data providers together with items 1. - 4.could be a compelling reason/use case for some customers.

      Author's profile photo Matthew Shaw
      Matthew Shaw
      Blog Post Author

      Many thanks for your comments.

      Please be assured these comments have been forwarded, acknowledged as received and added into the overall 'market requirements' pot by the appropriate Product Owner.

      Seems that your items numbers 1 and 3 is well acknowledged and high on the list! 😉

      Author's profile photo Matthew Shaw
      Matthew Shaw
      Blog Post Author

      Free hand SQL is now available in Web Intelligence BI 4.1 Support Pack 5