A look into the BI Semantic Model of SQL Server 2012

A semantic layer is a business translation layer which sits between the database and the end user; providing the ability to interact with data using familiar business terminology instead of having to understand where the data resides and what business rules to apply to said data. The BI Semantic Model serves all of the end-user experiences for Microsoft BI, including reporting, analysis and dash boarding. The BI Semantic Model retracts data from multiple databases; ranging from LOB applications to text files and spread-sheets. The model extracts enriched data; and provides fast-tracked access to the data, using an in-built interface that is made up by all tools in the Microsoft BI stack: Reporting Services (including Power View), Excel, Power Pivot, and SharePoint. Exhausting the BI Stack, this Model thus allows users to work with data from a wide range of client tools and the full spectrum of BI applications; regardless of how the model was developed; with the ability to migrate from one application to another.

The spectrum of BI applications includes the following:

• Personal BI applications:  created to meet individual specific needs;
• Team BI solutions: ability to create and share with colleagues within small organisations or departments;
• Corporate BI applications: are developed, managed, and sanctioned by corporate IT and rolled out to a large user base.

During the development of your semantic layer, decisions on the following are critical in terms of meeting your organizational needs:

• Data Model: is determined by your choice of modelling experience, thus,  you must define relationships among your source data tables:
• One-to-many relationships: single record from one table relates to multiple records from another table. Both tabular and multidimensional data models natively handle one-to-many relationships.
• Many-to-many relationship: many records from one table relate to many records from a second table. Atabular model does not support many-to-many relationships. However, you can use the DAX language to create formulas that handle these.
• Reference relationships: in multi-dimensional modelling, a dimension containing common attributes is created and then reference dimension relationships to each related dimension is identified.
  • In tabular modelling there is no need to create reference relationships.
• Business Logic and queries:Business logic adds value to any data model in the form of calculations and business rules; enhancing data for end-user analysis.  The BI Semantic Model offers both MDX and DAX for querying.

o   MDX is a calculation and query language based on multidimensional constructs (cubes, dimensions, hierarchies, levels, members)

o   DAX has evolved into a query language in the SQL Server 2012 release. It uses tabular constructs (tables, columns, and relationships) and includes functionality to support model development, including relationship navigation, context modification, and time intelligence.

• Data access:

o   Both MOLAP and ROLAP storage modes support this functionality for multidimensional models.In MOLAP, data is stored on disk in an optimised multidimensional format with typical 3x compression. In ROLAP, data is stored in the source relational database.

o   Tabular models use the xVelocity analytics engine providing in-memory data processing that caches all of the data in memory, organises it by column, and uses state-of-the-art compression and querying techniques. Alternatively, there is a Direct Query storage mode, which passes queries to the source database to leverage its query processing capabilities.

Business Intelligence Semantic Model (BISM) supports a broad range of reporting and analysis needs using either, multidimensional or tabular modeling. These provide complementary features that enable you take advantage of capabilities that best meet your needs. The benefits of using a semantic layer are:

• Relatively simple, flexible model: no cubes, dimensions, measures where every column has a measure and attribute.
• Guananteed correct and fast response no matter the granularity of query by applying rules to define database complexity and ambiguity.
• Guarantee User Understanding and Acceptance:relational model and result format is more understandable and user-friendly to majority of people. Additionally DAX calculations are similar to Excel formulas, thus enhanced understanding.

A semantic layer is a key component is any successful Business Intelligence implementation as it delivers a single version of the truth to your business users, while providing the safety and security to your IT department. Report developers can let the semantic layer source data and because it’s coming from the semantic layer, it must be correct because it’s alre