563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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!
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|
25 Aytoun Street