This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Creating a data model in Analysis Services tabular Creating a connection to your data source (this blog) Choosing tables, columns and rows to import Tidying your data model (grid/diagram view, joins, etc) Hiding tables and columns from client view Creating simple measures for our pivot table 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.

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!

Creating a data model in Analysis Services tabular
Creating a connection to your data source (this blog)
Choosing tables, columns and rows to import
Tidying your data model (grid/diagram view, joins, etc)
Hiding tables and columns from client view
Creating simple measures for our pivot table
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

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:

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

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


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

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

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):

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:

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:

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 solidq.com.

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

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:

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

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Creating a connection to your data source

Head office

London

Manchester