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




Related Articles :

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 |