Microsoft now offers two types on analytic database. There is the “traditional” BISM OLAP and the new BISM Tabular version. The 2012 version of BISM OLAP is very similar to SQL Server 2008 R2 Analysis Services. But the new “tabular” version is quite different and offers some advantages and disadvantages to the current “cubes”. Having a “choice” means that companies will now be able to apply the best technology fit for their applications. But it also means that before you begin bulding your BI application, you will need to decide which platform you will be using. In general, the new BISM Tabular offers advantages for the following types of applications:
- Users like to query large amounts of detail data
- The cube has a lot of “distinct counts”. (These query much faster than in BISM – OLAP)
- The cube has a lot of “degenerate fact table” dimensions. (this is automatic in BISM – TABULAR)
- Query performance is of great importance
- Customer Segmentation Analysis
- Events in Progress Analysis
The objective of the BISM is to have one model for all user experiences – reporting, analytics, scorecards, dashboards, and custom applications. All client tools in the Microsoft BI stack – Excel, PowerPivot, SharePoint and Reporting Services– operate on this model. BI professionals can create the model in Visual Studio and deploy it to an Analysis Services server. Interestingly, the model can also be created by business users with PowerPivot for Excel and shared via PowerPivot for SharePoint.
In SQL Server 2012, the concept of the BI Semantic Model (BISM) is introduced. It includes:
- PowerPivot for Excel
- PowerPivot for SharePoint
- Analysis Services Tabular
- Analysis Services Multidimensional
PowerPivot and Analysis Services are two different products; one targets business users and the other targets BI professionals and IT. However both use the BI Semantic Model under the covers. In the case of PowerPivot,the model is embedded inside an Excel workbook. In the case of Analysis Services, the model is stored and managed on the server. Having the same model behind these experiences enables seamless transition of BI applications from Personal BI to Team BI to Organizational/Professional BI. For example, a PowerPivot workbook built by a business user can be used as a starting point by a BI professional building an Analysis Services application. The SQL Server Data Tools (formally called BIDS) make it very simple to import a PowerPivot workbook into Analysis Services Tabular model only. Because PowerPivot uses the vertipaq in memory engine so in result you can’t able to import into Multidimensional model (conventional SSAS).
The BISM is a business translation layer that sits between the database and the end user. The primary goal of the BISM is to allow users to interact with their data using familiar business terminology instead of having to understand where the data resides, the naming conventions and what business rules to apply to the data. In other words, the BISM provides the linkages between the reporting tools and the physical data models.
Simply The BISM to provide common business terms, rather than data language to access, manipulate, and organize information, it simplifies the complexity of business data
You can create a BISM using Excel PowerPivot or SQL Server Data Tools (formerly BIDS). You create one of three things: a multidimensional model, a tabular model, or a PowerPivot workbook. Each of these items is a BI semantic model as shown in the above snapshot.
The BI Semantic Model can be considered as a three-layer model as the above diagram suggests:
- The Data Model layer is the conceptual data model that the model developer and the model consumer work with. The BI Semantic Model is a hybrid model supporting both multidimensional and tabular data modelling.
- The Business Logic layer that encapsulates the intelligence in the model. An author creates a business logic model using either DAX (Data Analysis Expressions) or MDX(Multidimensional Expressions). The beauty of the new model is that DAX is an expression language based on Excel formulas that was introduced in PowerPivot and built on relational concepts. While DAX doesn’t give the raw power and flexibility that MDX offers, it can be written by trained users, does not generally require a background in development, and infrequently requires tuning.
- The Data Access layer integrates data from multiple sources, including relational databases, business applications, flat files, OData feeds and Cloud Services.
As many of the BI professionals agree, based on what we know and seen in PowerPivot, BISM has a huge potential and will bring welcome enhancements when using the tabular models. These benefits include:
- Schema simplification – There is no need to define explicit cubes, dimensions, measures, eliminating the perceived complexity of implementing an OLAP solution.
- Improved performance – At first glance it may not be obvious, but as the BISM utilises the VertiPac engine (a proprietary columnular data store engine) – which works well for BI-type querying.
- Flexibility – There will be no distinction between measures and dimensions. Every attribute can be used for aggregating and slicing. This is
one PowerPivot feature that I really like.
- The DAX expression-based language removes much of the complexity of MDX.
- Possibility of real-time data access.
- It allows solutions to scale from tens of millions of rows (using Excel PowerPivot), to hundreds of millions of rows (using Microsoft SharePoint) to billions of rows (with Microsoft SQL Server 2012).
From a business perspective the BISM enables end users (both technical and non-technical) to use any Microsoft client tool to self-serve BI content via a consistent semantic model, reducing their requirements for training and support. It also enables subject matter experts to architect semantic models relevant to their end users, further removing the dependency on specialised IT skills which more often than not impacts a business’s ability to support the demand for information.
In summary the BISM that is coming with Microsoft SQL Server 2012 is a great evolution of the BI stack, adding capability while simplifying the process for creating and using BI content.