SQL Server 2012 arrives a new model for create analysis services solutions, this new model is called tabular model and is part of the new BI semantic model composed also for the traditional multidimensional model.
In this opportunity we are going to talk about the new Tabular model. We can say that the new tabular model is an evolution of power pivot; it has the same orientation about flexibility and in-memory access of the data, and use the same query language DAX.
For create a Tabular model solution we need to use the SQL Server Data Tools (before business intelligence development studio) and create a data model in the same way we did it using power pivot, once create a data model we can deploy this model to an instance of SQL Server Analysis Services so the model can be used as a data source for create reports using tools like Reporting Services, Excel and PowerView.
To be able to deploy a tabular model to SQL Server Analysis Services you need to create an instance of SQL Server Analysis Services using Tabular Model, to achieve this you only need to select Tabular Model when you are installing the instance of SQL Server Analysis Services.
Now let’s see how to create a Tabular model and how to deploy it to SQL Server.
First open the SQL Server Data Tools and select new project in the business intelligence template choose Analysis Services Tabular Project.
You will be prompted to indicate the Workspace server (Instance of analysis services tabular model)
Now you need to configure the data source.
Choose the data base and server that you want to connect. In this case I’m using the AdeventureWorkDW2012
Indicate the credential that will be used to connect to the data source.
Indicate if we want to select a table or write a query. In this case I’m selecting a list o tables.
Select the tables you want to use. In this example I choose FactInternalSales and then select “Select related tables”.
After you click Finish the tables and data will be imported.
After import the tables we can deploy our model to Analysis Services but before we will open the Role Manger and create a new role. I created the role ALL and in the Members section I added everyone.
Now we can go to the Build menu and make click on Deploy solution.
If you go to SQL Server Management Studio and connect to the Tabular instance of Analysis Services we will find our database.