SQL Server 2012 has
provided customers with large-scale data warehousing and analytical solutions
backed by IT management and insights. New and enhanced self-service
capabilities allow users to explore and unlock new insights from information
spread across structured and unstructured data sources, both on-premises and in
the cloud.
Why BI Semantic Model (BISM)
The BI Semantic Model is a new hybrid data model that offers the
powerful analytical capabilities of multidimensional data models as well as the
simplicity and familiarity of tabular (that is, relational) model.
Why BISM Tabular Model
As part of the BI
Semantic Model, SQL Server 2012 Analysis Services introduces a new tabular
modeling capability that allows users to build a BI model using exactly the
same processes that were introduced in PowerPivot for Excel. Users can quickly
bring together data in tables and build relationships using the DAX query
language to apply business logic.
The following section explains simple steps involved in developing a BISM tabular model.
The following section explains simple steps involved in developing a BISM tabular model.
Steps to develop your first BISM Tabular model
The following are the
steps involved to develop your first BISM Tabular Model.
1. Install an Analysis Services
Instance Running in Tabular Mode:
Either you can add new features to the existing instance or create
a new instance for Tabular Model.
a a)
To add new instance
kindly follow the steps:
b) Select the Server Mode:
Tabular Mode and add yourself as the administrator.
c) Verify if the tabular
model is installed by connecting to the analysis server instance name that you
have just created.
2. Kick Start:
The following steps
needs to be followed to create a new BISM project:
a) Creating a BISM project: Open BIDS(Business
Intelligence Development Studio) and click on new project. There are three
types of project template:
i) Analysis Service Tabular Project: Use this
template to create an Analysis Service project with tabular models.
ii) Import from PowerPivot: Use this template to create a tabular
project by extracting the metadata and data from an existing PowerPivot
workbook.
iii) Import from Server
(Tabular): Use this template to create a tabular project by extracting the
metadata from an existing tabular AS server
For our development
choose Analysis Service Tabular Project.
b) Importing data: Double click the model.bim. This will open a
container to browse all the tables in the model. At present its empty as we
have not imported any data from database.
To import data select Model from Menu bar and click on Import from Data Source.
To import data select Model from Menu bar and click on Import from Data Source.
A Table import Wizard pops up with different data sources to which the model can be connected.
Select Microsoft SQL Server and click next.
Provide the required details to connect to the relational database.
Next, choose the dimension and fact tables and click finish.
Now you have successfully imported data into your model.
3. Creating Relationships:
You can create relationships between tables by switching to the diagram view (You can switch from grid view to diagram view by selecting the icon at the bottom right corner). If the tables already have foreign key relationships established they will be retained, otherwise you can create relationships between the tables manually.
4. Creating Measures:
Measures are created in the grid view. Switch to grid view and select the fact table. Choose the column for which u want to create the measure and select Sum from the Toolbar. This will create a measure for Sum of fact value, here sum of SalesAmount in my case. Any measure can be created with any type of calculation.
5. Make your Model User Friendly:
Hide the columns which is not meaningful for the user.
For example: Surrogate keys, foreign_Keys, audit columns, etc.
You can do this either by setting the hidden to true in column property window else right click the column and select "Hide from Client Tools"
For example: Surrogate keys, foreign_Keys, audit columns, etc.
You can do this either by setting the hidden to true in column property window else right click the column and select "Hide from Client Tools"
Give user friendly names to the columns and tables.
6. Creating Hierarchy:
Switch to diagram view. Select the columns that needs to be added to the hierarchy, right click and select Create Hierarchy if the hierarchy is been newly created else choose Add to Hierarchy and choose the hierarchy name.
7. Create Calculated Column and Measures:
We make use of DAX (Data Analysis Expression) to create calculated columns and measures. There are more than 30 new functions added like distinct, count, sum, rank, lookupValue etc.
To create a calculated column in the grid view add another column with the calculation. Its similar to named calculation in OLAP database and evaluated at the row context.
To create a calculated column in the grid view add another column with the calculation. Its similar to named calculation in OLAP database and evaluated at the row context.
For example: [User Full Name] := [User First Name] + " " + [User Last Name]
To create a calculated measure, add your calculation at the measure grid. The values are evaluated at the filter context. These are placed at the values area of Power Pivot tables.
For example: [Average Sale] = AVERAGEX ( Sales, Sales[Amount])
8. Changing the Source Query and Adding New Tables:
We can also have the flexibility to change the source query of each table. To attain this switch to Data View and select the table. In the property window select Source Data (...) and switch to query editor and write your query here.
9. Deploying Model:
Ensure the deployment options are set correctly. To check right click the solution and select properties. Edit the deployment options and click ok. Now your model ready and you can deploy by choosing Deploy in the solution right click options.
10. Reporting:
You can also analyse the data in excel by selecting Excel symbol (Analyse in Excel) in the Toolbar. A pivot table is created by connecting to the semantic model just created. You can create excel dashboard reports, perform analysis of data easily.
Conclusion
The user experience of creating tabular data models in PowerPivot is similar to the user experience of creating an SSAS tabular project in SQL Server Data Tools. BISM Tabular models are similar to Excel and include easy-to-use formulas which make them useful for creating solutions with high agility and time to availability. For more informantion and additional resources follow the link below.Reference Sites
- www.kohera.be
- http://www.powerpivot-info.com/
- Power Pivot Overview
- Denali Breakthrough Insight
- BISM model
