How to create a data model in SSAS tabular
Part two 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 (this blog)
  3. Choosing tables, columns and rows to import
  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.

Creating a connection to your data source

Assuming you've set up the MAM database and created a project, you're now in a position to import data into your model:

Edit your model

First double-click on the model in your project to edit it.


To start importing data, do one of these two things:

Import tool Import menu
Either click on this tool ... ... or choose this menu.

You can now double-click on the data source you want to use:

Choosing a data source

For the moment we'll assume that you're getting data from SQL Server, although other parts of the SSAS tabular tutorial show how to use other data sources.


You should now complete the dialog box which appears as follows (instructions on each of the numbered steps appear below):

Table import wizard

See below for how to complete each step.

The steps to follow are:

Step What to do
1 - friendly name If you get everything right first time, there'll be no need to re-use your data source, and it won't matter what you call it.  However, it's likely that you'll forget a table or two and have to revisit the data source to import the tables you missed first time round.  It'll then be much easier to find your data source if you've given it a nice easy-to-remember name like MAM (as here).
2 - server name This will be the name of the SQL Server database to which you want to connect.  The "." character is used to show that the server is running on the current computer, under a named instance called sql2012 (so we could also have typed in localhost\sql2012).
3 - authentication mode There are two ways in which you can get access to a SQL Server database: using the credentials given to you by the fact that you've logged on to Windows, or by typing in a name and password which gives you the right to view the database tables.  For most people it will be sufficient to use the former (Windows authentication).
4 - database name If you've answered the other questions correctly, you should be able to click on the drop arrow which appears (not shown above) to choose the database to which you want to connect.

When you go on to the next stage of the wizard, you can choose what security you want to use to access the data:

Impersonation information

Here we're choosing to use our Windows name/password to give access to both the relational database containing the tables and the Analysis Services workspace containing the model data.

Note that you have to use a Windows account or a service account which has authority to import tables from the MAM database, and also which has authority to write to the Analysis Services workspace database, otherwise you'll get this:

Errors processing data

If you see a dialog box like this when you try to finish importing tables into your model, you have authority to read your relational database but don't have the authority to add the imported data into your SSAS data model.

Security is covered in more detail towards the end of the SSAS tabular tutorial of which this blog forms a part.  The best help I've found in resolving security problems (for a non-expert) is

If all goes well, you'll now be in a position to confirm that you want to import tables:

Table import wizard 2

Confirm that you want to import tables into your model.

You can now go to the next part of this blog to see how to choose tables, rows and columns to import:

Choosing tables

This dialog box is explained in the next part of this blog.

This blog has 0 threads Add post