Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips







Learn more about SQL Server tools








Learn more about SQL Server tools


Understanding the SQL Server 2012 BI Semantic Model (BISM)

MSSQLTips author Arshad Ali By:   |   Read Comments (6)   |   Related Tips: > Analysis Services Administration
Problem

SQL Server 2012 introduced an unified BI Semantic Model (BISM) which is based on some of the existing as well as some new technologies.  This model is intended to serve as one model for all end user experiences for reporting, analytics, scorecards, dashboards, etc. In this tip, I will talk in detail about the new BISM, how it differs from earlier the earlier Unified Dimensional Model (UDM) and how BISM lays down a foundation for future.

Solution

SQL Server 2005 and 2008 had the Unified Dimensional Model (UDM) for creating OLAP and data mining solutions.  SQL Server 2008 R2 introduced the VertiPaq engine for storing data in a highly compressed format in memory at runtime.  This technology improved the performance of analysis significantly faster in Excel with PowerPivot. Further, PowerPivot could be deployed to SharePoint for collaboration and to convert personal Business Intelligence (BI) solutions to team/organization BI. SQL Server 2012, has introduced an unified Business Intelligence Semantic Model (BISM) which is based on some of the existing as well as some new technologies.  This model is intended to act as one model for all end user experiences for reporting, analytics, scorecards, dashboards, etc. whether it is personal BI, team BI or organizational BI.

SQL Server Analysis Services Vision

SQL Server Analysis Services has became a market leader due to its performance, scalability, accessibility, reduced total cost of ownership and relative ease of solution development. Analysis Services in SQL Server 2012 has broadened its capabilities, features, ease of development and scope of reach even further. To define the vision for this next generation product release, the Analysis Services product group has decided on a set of key guiding principles for broader adoption:

  • Build a BI platform based on the strengths and successes of Analysis Services and expand its reach to a much broader user base
  • Bring together the relational and multidimensional models/projects under a single unified BI platform - best of both worlds together under an unified BI Semantic Model platform
  • Embrace the relational data model - which is easily, well and widely understood by developers and IT Pros around the world
  • Provide flexibility in the platform to suit the diverse needs of BI applications
  • Continue to make use of its rich existing ecosystem of tools, developers, their skillset and partners for ease of development

Business Intelligence Semantic Model (BISM)

The Business Intelligence Semantic Model (BISM) is a single unified BI platform which has both multi-dimensional as well as tabular data modeling capabilities to offer best of both worlds and choice for the developer. As we all agree, the relational data model is relatively easier to understand and used by a larger group of developers than multi-dimensional models, hence it makes sense to embrace the relational/tabular data modeling for broader adoption and to ensure utilization of customers' existing investments and skill available with them.

The model based on BISM can integrate data from heterogeneous data source including traditional data sources like relational databases, LOB applications or un-traditional sources like data feeds, text files, Excel, cloud services, etc. and users can work with the data stored in the model in all of these ways regardless of how the model (whether it's multi-dimensional or tabular) was developed. Having said that, the BI Semantic Model is the one model that powers all end-user experiences and can be accessed in an intuitive way using Reporting Services, Power View, PowerPivot, Excel, SharePoint, etc.

Any model based on BISM can be conceptually divided in three layers: Data Model, Business Logic and Queries and Data Access as described below.

Any model based on BISM can be conceptually divided in three layers: Data Model, Business Logic and Queries and Data Access

Data Model - BI Semantic Model developers can choose between multi-dimensional or tabular projects based on the needs of their application, their skill set and the client tool can consume the model using a multi-dimensional or a tabular interface.

  • Multi-dimensional - BI Semantic Model allows traditional ways of creating multi-dimensional model; it allows creating a model with a cube and dimensions normally based on dimensional data model/star-snowflake schemas of a relational data warehouse.
  • Tabular - BI Semantic Model also allows creating a model based on relational data sources and makes the development much easier as it is easier to understand.

Business Logic and Queries - Again, BI Semantic Model developers and client tools can choose between MDX and DAX based on application needs, skill set, user experience, etc.

  • MDX - MDX (Multi Dimensional Expression) was introduced with Analysis Services long back and now has become the de-facto BI industry standard for multi-dimensional business logic, calculations and queries for OLAP.
  • DAX - DAX (Data Analysis Expression) is an expression language based on Excel formulas that was introduced initially with PowerPivot and is built on tabular concepts (tables, columns, and relationships). DAX can be used to support model development (like creating calculated columns, measures, and KPIs for tabular models and PowerPivot models), relationship navigation, context modification and time intelligence.

Data Access - The Data Access layer integrates data from heterogeneous data sources; there are basically two modes for data retrieval and management as discussed below. Though there is a tradeoff between the two modes and the BI Semantic Model developer needs to make the choice based on application needs, local data storage requirements and data latency.

  • Cached - Cached data mode retrieves data from all the sources and stores it in a compressed data structure that is optimized for high speed data access. MOLAP is the storage format that has been used in Analysis Services cube (for multi-dimensional model) for many releases of Analysis Services. It is optimized for OLAP and uses techniques such as pre-built aggregates, bitmap indexes, and high degree of compression to deliver great performance and scale. Whereas VertiPaq is an in-memory column store engine (for tabular model) that combines state-of-art data compression and scanning algorithms to deliver blazing fast performance with no need for indexes, pre-calculated aggregates or tuning.
  • Pass-through - Pass-through mode pushes query processing and business logic down to the underlying data sources, thereby exploiting the capabilities of the source system and avoiding the need to copy the data as part of the model. ROLAP is complementary to MOLAP of multi-dimensional model whereas DirectQuery is complementary to VertiPaq of tabular data model. Both attempt to push query evaluation, as much as possible, down into the underlying data sources. ROLAP is optimized for large fact tables and relatively small dimension tables (star schema), while DirectQuery is mostly neutral towards the backend database structure though in SQL Server 2012, DirectQuery is supported only for models that are based on a single SQL Server relational data source.

Analysis Services Deployment Mode

Now with SQL Server 2012, an installation of Analysis Services can be done either in multi-dimensional, tabular or PowerPivot for SharePoint mode. As discussed above each of these deployment modes uses a different engine (Analysis Services engine for multi-dimensional whereas VertiPaq engine for tabular or PowerPivot for SharePoint) and works differently by using different storage structure and memory architecture. Each Analysis deployment mode supports different set of data sources, development/management tools, query language, etc.

Check out this table outlining all of the details.


Feature Multidimensional Modeling Tabular Modeling PowerPivot for SharePoint
Data Sources Relational databases Relational databases, Analysis Services cubes, Reporting Services reports, Azure DataMarket datasets, data feed, Excel files and text files Relational databases, Analysis Services cubes, Reporting Services reports, Azure DataMarket datasets, data feed
Excel files and text file
Development Tool SQL Server Data Tools (SSDT) SQL Server Data Tools (SSDT) PowerPivot for Excel
Management Tool SQL Server Management
Studio (SSMS)
SQL Server Management
Studio (SSMS)
SharePoint Central
Administration
PowerPivot Configuration Tool
Application Programming Interface AMO, AMOMD.NET and PowerShell AMO, AMOMD.NET and PowerShell No support
Reporting and Analysis Tool Report Builder, Report Designer, Excel PivotTable
and PerformancePoint
dashboard
Report Builder, Report Designer
Excel PivotTable, PerformancePoint dashboard and Power View
Report Builder, Report Designer, Excel PivotTable, PerformancePoint dashboard and Power View
Query and Expression Language
DAX - Data Analysis Expression
MDX - Multi Dimensional Expression
DMX - Data Mining Expression
MDX for calculations and
OLAP queries
DMX for data-mining
queries
DAX for calculations and
queries
MDX for queries
DAX for calculations and queries
MDX for queries
Write-back Support Yes No No
Security Role-based permissions in SSAS and cell-level security Role-based permissions in SSAS and row-level security File-level security using SharePoint permissions
Data Compression Ratio 3+ times 10+ times 10+ times

During the installation of Analysis Services, on the Setup Role page of the SQL Server Setup, you need to select the SQL Server Feature Installation option for Multi-dimensional and Data Mining or Tabular mode, or PowerPivot For SharePoint option. Once you are done with the installation of Analysis Services, you cannot change the deployment mode of an existing Analysis Services instance (from multi-dimensional to tabular or vice-versa). Though multiple instances of Analysis Services can co-exist on the same server, each running a different deployment mode.

Why the BI Semantic Model? Why Microsoft BI Stack?

First, the BI Semantic Model is a highly performing and scalable. The performance and scalability comes from the MOLAP and VertiPaq storage engines that enable data volumes all the way from a few megabytes up to multiple terabytes. MOLAP used bitmap index, pre-calculated aggregates in compressed formant for high performance whereas VertiPaq uses in-memory column store engine that combines high compression and scanning algorithms to deliver blazing fast performance with no need for indexes, pre-calculated aggregates or tuning.

Second, the BI Semantic Model is feature rich.  The value lies in the sophisticated data modeling capabilities and complex business logic that can be expressed using MDX and DAX languages.

Third, the BI Semantic Model offers flexible modeling capabilities. In other words, Model developers can choose between the multi-dimensional or tabular data modeling based on their experience and skill-set, between MDX and DAX for business logic, and between cached and pass-through modes for data access, data latency and storage. Regardless of the model developer's choices, client tools see a single model and can consume it using the multidimensional or tabular interfaces and send MDX or DAX queries for accessing the model.

In this tip, I talked in detail about new BISM, its architecture, how it differs from earlier UDM and how BISM lays down a foundation for future. In the next tip I am going to talk in detail about differences between multi-dimensional model vs. tabular model, when to choose one model on another and step by step guide on creating tabular model project, please stay tuned.

Next Steps


Last Update: 11/19/2012


About the author
MSSQLTips author Arshad Ali
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Wednesday, February 20, 2013 - 7:12:26 AM - Bisi Onaolapo Read The Tip

Hi Arshad,

I need a pdf link or tutorial to deploying BI Solutions in SQL Server 2008 R2 for a company.

Regards,

Bisi.

 

 


Friday, March 22, 2013 - 8:49:14 AM - Michael Wehr Read The Tip

Thank you for taking the time to create a concise introduction to BISM!!  This is a great handout for a discussion as to why we may need to upgrade to SQL Server 2012 sooner than later.  I am new to SQL Server and the last 5 years have introduced a lot of new conceptual design.  I appreciate the design of this summary.


Sunday, March 31, 2013 - 4:23:37 PM - Alexandr Savinov Read The Tip

Thank you for a very good explanation of the BI Semantic Model. Another example of a unified model is the concept-oriented model (COM). It also combines features of conventional models (relational, multidimensional, semantic, ER) and unifies data modeling construts and patterns. COM provides its own query language, called the concept-oriented query language (COQL), which is somewhat similar to DAX. 


Wednesday, November 20, 2013 - 8:10:08 AM - Ismail Mohammed Read The Tip

As Salamu Alaikum

I have installed SQL Server 2012 choosing Multidimensional Mode for the SQL Server Analysis Services.

Then for some requirement on Tabular model, I selected a Tabular Project but I am unable to open it, as it is saying, Your instance is in Multidimensional mode.

So, Should I have two different instances or is there any alternative.

Sir, Can you please help me in sorting out this issue.

 

Jazakallah

Ismail Mohammed


Wednesday, November 20, 2013 - 12:46:27 PM - Arshad Read The Tip

Yes Ismail, you are right multidimensional and tabular are two separate engines and hence you need to install both of them (one instance for multidimensional and one instance for tabular) if you want to work on both type of projects.


Thursday, February 13, 2014 - 11:54:23 AM - RAJU Read The Tip
HI Mr. Arshad Ali
I have one issue , for creating stock report from  multiple  warehouse to a single sheet. 
say we have 5 warehouse, where they recived goods, and pass the quality. the pass pcs will go to stock and they ca issue 
to other purpose.
we need to show item wise stock status as below between the selected period.

itemcode | Item description | opening qty | Recived Qty | Issue Qty | Stok in Hand|

we are getting performace issue , if i wan to see the over all ( all warehouse) stock.
to get this , i have 3 table
1. item master
2. Quality ( after received we check and pass pcs only will get updated in this)
3. issue
now we get time out error if i want to get all warehouse stock in single report.

need your help to get this report faster. You early reply will be highky appricated.
thanks
Raju



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.