Techbubbles

Tabular Modeling in SQL Server 2012

 

Tabular model is a new database structure in SQL Server 2012 Analysis Services. When you create tabular model project, SQL Server Developer Tools creates a Model.bim file to the project and creates workspace database in Analysis Services instance. Workspace database acts as a temporary storage for data when you develop a model in SSDT.

Workspace Database

Workspace Database stores the data in memory , SSDT retrieves the data from Workspace database when you view data in the diagram view.

The below are the Model.bim properties which can use for setting values

  • Workspace Server – use this property to set the value for server where you want to host the database.
  • Workspace Database – You can not assign value, Analysis Services assigns a name to it.

You must be an administrator for the analysis services instance hosting the workspace database.

Table Import Wizard

You can import the data into model using Table Import Wizard from one or more data sources. If you are using a relational data source then you can select from list of tables and views. You can filter the data before you importing into the model.

image

Table Model Designer

After importing the data into the model, designer shows the data as shown below

image

When you import the data from relational datasource, process identifies the relationships and adds them to the model. To view the models switch to the diagram view

image

You can add new relationship by clicking a column in one table and dragging the cursor to the corresponding column in the second table.

Calculated Columns

Using Data Analysis Expression (DAX) formula, you can concatenate values from two columns into a single column.

image

Adding a Measure to model

To add a new  measure , click the cell in calculation area then type the DAX formula in bar.

image

More about the using Tabular Model in project can be read here


Share this post :





Related Posts:

%d bloggers like this: