How to create a data model in SSAS tabular
Part three of a seven-part series of blogs

The first - and most important - part of using Analysis Services (tabular mode) is to create a data model, incuding only those tables and columns you need. This lengthy blog shows you how to go about creating the perfect data model!

  1. Creating a data model in Analysis Services tabular
  2. Creating a connection to your data source
  3. Choosing tables, columns and rows to import (this blog)
  4. Tidying your data model (grid/diagram view, joins, etc)
  5. Hiding tables and columns from client view
  6. Creating simple measures for our pivot table
  7. Making changes to a model (adding tables and columns)

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 16 November 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Choosing tables, columns and rows to import

This blog page shows how to choose which tables, which columns and which rows you want to import.

As a general principle, you should import into your model only those tables, rows and columns that you actually need for reporting.  The rest of this blog explains what you might and might not need to import.

Choosing tables

A good place to start for our database is with the transactions table, which contains details of who bought which products:

The transactions table

Tick the first table that you want to import, and give it a friendly name (users of your data model don't need to know that the table is actually called tblTransaction).


In fact, Transaction is a reserved word in SSAS Tabular, and is not an ideal friendly name to use, as you'll find you always have to include it in inverted commas in expressions.  C'est la vie.

To save time, you could now choose to include related tables:

Select related tables

If you click on this button, SSAS tabular will include those tables immediately connected to the one you have selected.


Here's what you'd get if you clicked on the Select Related Tables button once with the Transaction table selected:

Related tables

The tblPos and tblProduct tables are directly connected to the tblTransaction table.


Here's why this happens:

SQL Server Database Diagram

The database diagram in SQL Server shows that there is a direct relationship between the tblTransaction table and other tables.

The tables you should select to follow this tutorial are as follows:

The tables to select

Rather tediously, we've removed the tbl prefix from every one of these 10 ticked tables to give them friendlier names.


Choosing rows for each selected table

Having chosen tables for the model, you could now filter the rows included.  For this example, suppose we only want to include transactions where at least 2 items were bought at a time.

To filter rows for a table, select the table then click on this button:

Preview button

With (for example) the transactions table selected, click on this button to filter its rows.


Click on the drop arrow to the right of any column that you want to filter by, and choose your filter:

Filtering rows

Here we're going to show only the transactions where the quantity is more than 1.

You can now complete the dialog box which appears (it's pretty obvious what to do):

Custom filter dialog box

Choose to show transactions where the quantity is more than 1.

You can tell you've applied filters to a table by the link which appears:

Applied filters link

Clearly we're not going to import all of the transactions.

When you click on this link you can see what filters you've applied:

Filter details dialog box

SSAS tabular shows a list of filters applied to the selected table.


If you set filters for a table, the excluded rows won't be imported into your data model, and won't therefore be available for further analysis.  Then again, they won't clog up your workspace server either.

Choosing which columns you want to import

You can usually reduce the size of your data model by excluding some of a table's columns.  Because of the way SSAS tabular stores data, it is particularly important to exclude columns with a high level of granularity (ie with lots of different values).  What this means is that the TransactionId column will take up lots of space:

TransactionId column

There are 4,234 transactions where someone bought 2 or more items at a time, and each has a separate id number.  This will take up a lot more space than a column which contains lots of duplicate values (such as the price or the quantity columns).


You can choose to exclude columns by clicking on the same Preview & Filter button as you used for excluding rows:

Preview & Filter button

Select the table whose columns you want to choose or exclude, then click on this button.

You can now widen the columns to see the names properly:

Widening columns

Use this double-headed arrow to widen each column.


Untick the columns you want to exclude, using the table below as a guide:

Unticking columns

You can use the tick box at the top left of a table to include/exclude all columns simultaneously.

For the above table, here's what you should include/exclude:

Column Include or exclude?
TransactionId Untick (exclude), as you're unlikely to want to sum, count or perform any other aggregation based on the unique transaction numbers!
PosId, ProductId Include, because even though you don't want to be able to aggregate these numbers, they are the only way for each transaction that you can find out which point-of-sale and product record it is to do with.
Price, Quantity Include, because you've very likely to want to create measures (calculations) showing things like average price or total quantity sold.

As another example, here's what you'd include in the tblAnimal table:

The animal table

See below for why!

And here's notes on each column:

Column Include or exclude?
ProductId Include, because this links to the transaction to tell us what each person bought.
ProductName Include, because we may at some point want to show (for example) total sales by product.
AnimalId Include, because it links to the animal, and hence the species.
FullPrice Exclude, unless we think that we may at some point want to aggregate this field.
TargetGenderId Exclude, because we're not including the tblTargetGender table in our model and hence won't want or be able to report by target gender.
AgeRangeId Exclude, because we're not including the tblAgeRange table in our model, and so won't want or be able to report by target age range.

Processing your tables (importing their data)

When you've finished and choose to go on to the final stage of the wizard, SSAS tabular should import the rows/columns you've chosen:

Processing tables

You should see something like this. Although there are over 13,000 transactions in the underlying database, only 4,234 have been imported because of the row filter we set.


You can now tidy up your data model, as described in the next part of this tutorial! 

This blog has 0 threads Add post