Getting Started with Business Intelligence Semantic Model (BISM) in SQL Server 2012

The Business
Intelligence Semantic Model is one of the most significant enhancements in SQL
Server 2012. BISM allows aspects of the traditional multidimensional model to
coexist with the relational model in a format called the tabular model and can
be used with all client tools in the Microsoft BI stack.

The
tabular model has its roots in the relational model and can be easier to
comprehend for users without a BI Development background. For further
details on BISM model you can go through the blogs on msdn at http://blogs.msdn.com/b/analysisservices/archive/2011/05/16/analysis-services-vision-amp-roadmap-update.aspx.

Now let
us look at how to create a BISM model.

1. For BISM to
work, your SQL Server Analysis Services instance should be installed in
Tabular mode. When installing SQL Server Denali CTP3 or RC0 you are given an
option to install the Analysis services engine in Tabular or Multidimensional
mode. I have installed the instance with name localhost\SQLDenali.

2. Open Business
Intelligence Development Studio. Open a new Analysis Services Tabular project
as shown below:

Description: https://people.avanade.com/personal/divya_agrawal/Blog/Lists/Photos/120711_1256_GettingStar1.png

3. If you do not
have the Tabular instance name as default local instance name then it will show
an error Workspace database server localhost not found.  You can
change the workspace database server by selecting Properties of Mode.bim The
Workspace Server is localhost by default. We can change from localhost
to the current instance name which is localhost\SQLDenali for my
machine. Select Apply the changes.

                                    

4. Double click
on Model.bim. This will open the browser for all tables. Select Model from Menu
bar and click on Import from Data Source. A Table import Wizard will open as
below with various Data sources.

Description: https://people.avanade.com/personal/divya_agrawal/Blog/Lists/Photos/120711_1256_GettingStar4.png

Description: https://people.avanade.com/personal/divya_agrawal/Blog/Lists/Photos/120711_1256_GettingStar5.png

5. Next, select
Microsoft SQL Server from the list of Databases. Connect to the Relational
Database server instance. Select the tables from relational database. For this demonstration, I
will select the AdventureWorksDWDenali database. Select the appropriate tables to be used for the analysis.

Description: https://people.avanade.com/personal/divya_agrawal/Blog/Lists/Photos/120711_1256_GettingStar6.png

Description: https://people.avanade.com/personal/divya_agrawal/Blog/Lists/Photos/120711_1256_GettingStar7.png

6. Switch to the
Diagram View. If the tables already have foreign key relationships established they will be maintained, otherwise you can create relationships between the
tables manually.

Description: https://people.avanade.com/personal/divya_agrawal/Blog/Lists/Photos/120711_1256_GettingStar8.png

7. Switch to
Data View and select the FactProductInventory table. You can select the column of a
table in which you wish to create the measure. Here, I am selecting the
UnitsBalance column and selecting Sum from the Toolbar. This will
create a measure for Sum of Units balance. Any measure can be created
with any type of calculation.

Description: https://people.avanade.com/personal/divya_agrawal/Blog/Lists/Photos/120711_1256_GettingStar9.png

Pages: 1 2




Array

4 Responses to “Getting Started with Business Intelligence Semantic Model (BISM) in SQL Server 2012”

  1. Great intro article…thanks for sharing!

  2. Thanks! I’d like to see an article contrasting this new mode to traditional multidimensional to explain why someone would or would or would not want to use this over the traditional analysis server mode/project. Now that we know how to install it, an article about differences, pros and cons would be great!

  3. About time!,

    Business Objects had their Semantic Layer called Universe since 1990.

    We actually had the Microsoft people come to my company and try to bad mouth the “centralized and monolithic” concept of the Business Objects Universe.

    Oh Well…

  4. Thanks You so much for your aticle.

    Could you please add more articles on the similar topics in sql server 2012

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |