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

No comments yet... Be the first to leave a reply!