Creating a Data Source in SSAS 2008

Creating a Data Source in SSAS 2008

Creating a Data Source in SSAS is a seemingly straight forward topic, but I still get questions from our clients and conference session attendees.  We'll walk through it together  to eliminate any inconsistencies in what you've been told.

Step 1

This is the first screen in the Create Data Source Wizard.  Here is where you will create or reuse a data connection to enable your project to connect to a relational data source.  I have one already here for my demos but we will create a new one by selecting New….

As you can see there are a large number of providers you have to choose from:

We will choose SQL Server Native Provider 10.0  and select our AdventureWorks Database.

You can also select the ALL option on the left and get access to all settings for the connection. Important ones here include the timeout settings, connections pooling, configuration applications names, etc..

 Next, you will choose what security method you want to use. We'll go through those below:

Use a specific Windows user name and Password: This will tell SSAS to use the specified credentials for processing, ROLAP queries, out of line bindings, local cubes, mining models, and remote partitions, among other things. Format is: <Domain Name>\ <User Account Name>

Use the Service Account: This will use the service account for all the same items as the previous option, except it is not supported for out of line bindings.

Use the Credentials of the Current User:  This will use the current user's specified credentials for out of line bindings, DMX OpenQuery, local cubes and mining models. It is NOT supported for processing, ROLAP queries, remote partitions, or synchronization from target to source.

Inherit: The inherit option will use the credentials of the default user account for analysis services (the service account) and will manage the account used between Current User and Service Account to ensure the least privileges required are use.

Once you're done with this setting click FINISH and you're all set. You're data source is created ! Congratulations !

As always, please post your questions in the BIDN forums to have access to the top resources on Business Intelligence and best practices!

Thanks - Adam