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:
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.
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.
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.
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.