On my last post I wrote about the tabular model of SSAS 2011 (Denali) and the way I believe it will change the SSAS BI system.
On this post I like to show how to build a tabular model on SSAS 2011.
There are two ways to build a tabular model:
1. Build a PowerPivot model on Excel 2010 and deploy it into SQL Server Analysis Services 2011 server.
2. Use Visual Studio 2011 to build the tabular model like we build SSAS good old multi-dimensional model.
Typically, the busyness user will use Excel and the BI developer will use BIDS. This is actually the first time that the business user does have the power to develop new model on the SSAS server. The ability of the business user to develop model, put Microsoft BI system on the same line with IBM TM1, Power OLAP, Palo and other in-memory BI products.
On this post I will describe how the developer can build a new tabular model.
First start the Visual Studio 2010. Choose to start a new project and choose a Tabular project.
Please be aware: Your SSAS 2011 server must run on a Tabular project mode. That's, should have been configured while installing the SSAS server and it can't be changed after install is complete.
After clicking OK, Visual Studio open the new Tabular project and try to connect SSAS. Unfortunately, it tries connecting to the localhost and unless you develop on the server, it fails with the error that you may see on this screen shot:
This error message appear immediately after choosing the tabular project and not while trying to deploy the project to the server as is was with SSAS 2010 OLAP Project.
To change the server that is connecting to the project you develop, right click on the bin file on your project. The default name of this file is model.bim . Choose properties and on the window that will open, write your SSAS 2011 server. On the example below, I put my server name which is an instance named SQL2011.
I hope that on the RTM version, Microsoft will let us choose the server that the project will connect instead of starting each project with an unpleasant error window. You may help change this by voting it on Microsoft Connect.
To change the server which will host the tabular project (after deploying to SSAS server), right click on the project name on the solution explorer and choose properties. On the window that will pop, look for the field Server and write your server name. On the screen shot below I wrote my server that is instance name SQL2011.
Next, we have to put some tables (tabular project, remember?) into the project. Double click on the BIM file.
On the top menu choose Model and then Import from Data Source. On our example we choose SQL Server data source. On the next window we choose the database Adventure Works.
After entering user and password (or use the service user name), on the next window you have to choose between entering SQL query or choose tables from the database. I choose to select tables from Adventure Works database. I choose FactResellerSales and clicked on the button select related tables. All dimensions tables that are connected to the fact table I choose were selected as well.
I clicked finish and my model was processed (data was imported from the SQL server into the tabular model).