Project Botticelli

Using SQL Server Data Tools-BI Purchase this course

26 February 2015 · 4 comments · 1912 views

SSAS Cube Design Series

Chris Webb Explains the Data Source Wizard in SSDT-BI

SQL Server Data Tools-BI (SSDT-BI, previously called BI Development Studio or BIDS) is the name for the Visual Studio templates used for building SQL Server Analysis Services projects, which are fully introduced in this 40-minute video by Chris Webb, expert on all things SSAS.

SSDT-BI should not be confused with SSDT, which is the Visual Studio experience for building SQL Server relational databases. SSDT-BI is a separate download, available free of charge from Microsoft.

There are two ways of working with SSAS in SSDT-BI: Online mode, where every time you save your project the changes are saved back to the deployed SSAS database, and Project mode where, when you save, those changes are only saved to the project and you have to Deploy your changes to the server separately. Project mode is the safest option, mainly because it allows you to use source control.

There are a number of useful properties you should set on a project when you first create it (07:00), such as Deployment Server Edition, the Target Server and Database, and Processing Option.

Once you have created a new project, the first thing you need to do is to create a new Data Source to connect to a relational database. Doing this involves two things: giving SSAS all of the details it needs to generate a connection string, and telling SSAS which credentials to use when it connects back to the relational database. Next, you need to create a Data Source View. A Data Source View (DSV) allows you to choose which tables you want to work with in your SSAS project, and also to tell SSAS about any relationships between those tables. You can also create Calculated Columns (similar to derived columns) and Named Queries (similar to views) in your DSV, although you would be better off doing this work in the relational database itself if that is possible for you.

Finally, on any new SSAS project you should also be using BIDS Helper, a free add-in for Visual Studio that adds a lot of useful functionality for Microsoft BI developers, available from https://bidshelper.codeplex.com/

If you are planning to become a cube expert, make sure to watch the remaining videos in our cube design and MDX courses.

Log in or purchase access below to the premium version of this content.

Purchase This Course or Full Access Subscription

Single Course

$250/once

Access this course for its lifetime*.
Purchase
Subscription Best Value

$480/year

Access all content on this site for 1 year.
Purchase
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.

Comments

mandeep.goraya · 1 March 2015

Hi Chris,

Thanks very much for excellent start to this training module, there are hidden gems throughout the training video.

I would be great if you could keep the momentum going, waiting for the next one.

Chris Webb · 1 March 2015

Don't worry, all of the course has already been recorded. It's being published as soon as it is edited etc (which takes a lot more time and effort than I had realised).

mbustin · 19 March 2015

Very Nice. Are there plans for someone to produce a course like this for the tabular model? Thanks.

Rafal Lukawiecki · 30 March 2015

Thanks for asking! Yes, we have the plans, but it will take a good few months while we await the next version of the technology. In the meantime, have a look at the modules 3 & 5 in this course, as they cover the basics of setting up SSAS Tabular models, and then the whole course on DAX, as it covers the modelling part, too.

Online Courses