With this post I begin a series of blog posts covering one of the most talked about features of SQL Server 2012: Tabular Modeling. Being new to this like most of us are, I hope to learn much about Tabular Modeling as we walk through the basics of building your first Tabular Model. In this installment, we’ll talk about what a Tabular Model is, when a Tabular Model is the right choice, and of course how to create a Tabular Model.

What is a Tabular Model?

With the release of SQL 2012, we are (re)introduced to tabular modeling. If you’re familiar with Power Pivot, you’re going to notice many similarities and will most likely pick up the tabular modeling aspect of SSAS pretty quickly. Basically a Tabular Model is an in-memory database in SQL Server Analysis Services. The Vertipag engine that was previously only used in Power Pivot is now utilized within Power Pivot and SSAS 2012 Tabular as xVelocity. The xVelocity technology allows you to perform complex analytics of your data all in-memory while making use of column store indexes. This eliminates expensive IO unlike SSAS Multi Dimensional Modeling where IO is a viable concern.

The Tabular Model also allows us to bring together multiple data source types very easily, similarly to Power Pivot. Bringing together data stored in a SQL Server Database, Oracle, Excel, and Access is not only possible but straight forward.

Once you’ve imported your data from whatever sources you need, defining relationships is very easy. Simply dragging and clicking an arrow from one object to another is all that is required here.

When Do I Choose Tabular Over Multidimensional Modeling?

You might be asking yourself, “Self, if Tabular is so fast and great, why would I ever use Multidimensional Modeling?” That’s a valid question, so lets go over some of the perks of each and when one or the other would the optimal choice.

1. If you need access to many different external data sources, choose Tabular. Multidimensional can do this to an extent, but if you need to relate an Excel spread sheet, a text file, an SSRS Report Feed, and your database data, Tabular is the way to go here.
2. If you need complex calculations, scoping, and named sets, choose Multidimensional.
3. If you need mind numbing speed and consistently fast query time, choose Tabular.
4. If you need Many-to-Many relationships, choose Multidimensional. You can model this relationship type in Tabular, but Multidimensional is still easier to create and manage these more complex relationships.
5. If you are planning on using Power View, choose Tabular. At this time its impossible to build Power View reports against a Multidimensional model, but that could change in the future.
6. If you don’t know DAX and want to use Tabular, either take the time to learn or use Multidimensional ; ) .
7. If your solution requires complex modeling, choose Multidimensional.

Take these points into consideration when choosing Tabular vs. Multidimensional. This isn’t every single consideration to think about, but should at least get you started in understanding the differences between Tabular and Multidimensional.

How Do I Create a Tabular Model?

So now that we have a general understanding of what the Tabular Model is and what are some of scenarios we should choose to use the Tabular Model, lets start creating our first Tabular Model.

For you to play along with my example, you’re going to SSAS 2012 installed in Tabular Mode.

If you’re not sure if your instance of AS is in Tabular Mode, just connect to Analysis Services in SQL Server Management Studio and look at the icons next to your instance of SSAS.

The Tabular SSAS instance has the nifty little blue icon and the Multidimensional instance has the same icon as before in 2008.

You will also need SQL Server Data Tools and the AdventureWorksDW2012 sample database.

So first things first. Open SQL Server Data Tools.

Then go to File, select New, then click Project.

Under Business Intelligence, highlight Analysis Services, and select Analysis Services Tabular Project. I’m naming my project FirstTabularProject. Then click OK.

After clicking OK you can see the new project in the Solution Explorer with an empty model, Model.bim.

With the project created, your empty model should be open in the Designer Window. So now its time to create a connection to our data source(s). In the top left of the menu tool bar, click the Import From Data Source icon.

Then select the type of data source you want to connect to. In my case, I’m connecting to a SQL Server database. Select Microsoft SQL Server and click Next. Specify the Server name, the credentials, and the Database.

After clicking Next, we must specify the Impersonation Information. These are the credentials that Analysis Services will use to access the data source when importing and processing the data. We can either specify specific credentials or tell it to you use the AS Service Account. I’m specifying credentials.

On the next screen, we need to choose how to import the data. We have two options: We can either select from a list of the tables and views which objects we’d like to import or we can write a query to specify the data to import. I’m selecting from the list of tables.

On the Select Tables and Views screen, you’ll see a list of the Tables and Views in your database. I can browse through this list and places checks next to all the tables and views I’d like to import. Or I can select a table and then click the button Select Related Tables. This will use the referential integrity of the database to determine which tables to check for you. Be careful clicking Select Related Tables. If you accidently click the button and the wizard selects 20 other tables, there’s no easy way to unselect the newly selected tables. I’ve selected FactInternetSales and allowed the wizard to select the dimensions based on the referential integrity.

Important:

Before clicking Finish, you’ll want to make sure that you highlight each table you want to import and then click the Preview & Filter button. This will allow you to not only preview the data, but also uncheck any fields that you do not wish to import into your model. This is important since the database will be stored in memory. We do not want to store any unnecessary data that we do not have to. You can see that I’ve gone through the FactInternetSales table and unchecked the fields I don’t want to import.

After filtering out the unnecessary fields, click Finish. The importing of the data will begin.

Once it is finished, click Close. You’ll notice the data has been imported and is now viewable in the Designer Window.

If after importing your data you decide you need to bring in another table from the same data source, click the Existing Connections icon.

Then click Open and you are able to add new tables, views, or named queries to your model.

In the Designer Window we have two views. The Grid view allows us to see the imported data, with each table on an individual tab.

We can also switch to the Diagram View by clicking the Diagram View icon at the bottom right of the Designer Window. The Diagram View is ideal for viewing all the imported tables and their relationships at one time.

So now that we’ve imported in our data, we need to add some measures to our model. Switch back to the Grid view and click over to the tab for the fact table, FactInternetSales. Select the first text box in the Measures Grid directly below the Sales Amount field. If the Measures Grid is not visible, just click the Show Measures Grid icon to toggle it back on.

After highlighting the text box beneath the Sales Amount field, click the Sum (Sigma) icon. This will automatically create a measure with an aggregation type of Sum. Then go into the properties of your new measure and give it a friendly name since this is the name that your users will see when browsing the cube.

Then do the same for the Order Quantity field.

Now lets deploy and process our model. By default, the model will be deployed to default instance of SSAS on the local machine. We can change the server we want to deploy to by right-clicking the project in the Solution Explorer and selecting properties. You can also change the name of the database that will be created when you deploy the Model.

In the Menu bar, click Build, then click Deploy ;.

This will begin the deployment and processing steps. If you specified specific credentials to use for impersonation, you’ll need to enter the user’s password during this step.

Once the model is deployed, we can now view our model deployed to the AS server and browse it with Excel. Click the Analyze in Excel icon and your model will open in Excel so you can browse it.

We’ve created our first Tabular Model. I hope this gives you a good introduction on what Tabular is, when Tabular is the right choice, and the basics of creating a Tabular Model.

In the next article, we’ll get more into modifying our model by building hierarchies in our dimensions and other more advanced topics so stay tuned for the next article. And as always, post any questions or comments here and I’ll answer them as best I can.