Create Tabular Project (for newbie)

Tabular model is very new to most of the developers and if someone wants to create his/her first tabular model then this article will help. Following are the steps to create a tabular model. I am giving an example considering only 2-3 dimensions and one fact.

1. Open "Microsoft SQL Server 2012" folder and launch "SQL Server Data Tools"

2. When you launch the wizard, you will get the Start Page of Microsoft Visual Studio. Click on "New Project" and you will get "New Project" wizard. Expand "Business Intelligence" node and click on "Analysis Services" node.

3. Click on "Analysis Services Tabular Project" and give appropriate name and Location to your project. Click on OK button.

4. Click on "Model" menu from a menubar and click on "Import From Data Source.." option.

5. When you click on "Import From Data Source.." option, you will get "Table Import Wizard" wherein you can see different relational databases options which you can use to create your tabular model.

As I am using AdventureWorks sample database, I am selecting "Microsoft SQL Server" option. So select "Microsoft SQL Server" and click on "Next" button.

Note: You can download a sample database named AdventureWorksDW2012 Data File from a link.

When click on "Next" button, you will be get "Connect to a Microsoft SQL Server Database" wizard. Give server name on which you have your database restored and select "Database name"

6. Click on Next button and select the Impersonation information i.e. you can give your windows credentials or you can use service account. Click on Next button.

When you click on Next button, you will get "Choose How to Import the Data" wizard. As I am going to demo this project through tables, I am selecting option "Select from a list of Tables and views to choose the data to import". you can even write a query to import data. Click on Next button.

When you click on Next button, you will get "Select Tables and views" wizard. Select tables which you want to use to create your tabular model. For sample purpose, I am selecting "DimDate", DimProduct, DimProductCategory, DimProductSubCategory and FactInternetSales. After marking the tables as checked, Click on "Finish" button.

When you click on Finish button, you will get following "Importing" wizard.

Your Tabular Project is ready to use. Bydefault you will get Data View of your tabular model. You can check the model by selecting "Diagram View" from Model View option of Model menu.

7. You can create measures on the columns of fact table. let me show you one example. toggle to Data view model. go to FactInternetSales table and select "SalesAmount" column. Click on summation icon and select SUM option. this will create a measure on SalesAmount column with SUM as aggregate function.

8. Open the properties of measure and change name to "Internet Sales Amount". Save the changes. Right click on project and select Deploy option.

9. After successful deployment, you can browse the data using Excel pivot tables similar to multi-dimensional cube. You can hide attributes, measures which you don't want to show to client using "Hide from Clients tools". Right click on column and select option "Hide from Clients tools".