Building Basic Dimensions and Cubes - Expert Cube Development with SSAS Multidimensional Models - page 39

Analysis Services must also be given permission to access the data source, and how
it does so depends on the type of data source you're using and how its security
is set up. If you're using Windows authentication to connect to SQL Server, as
Microsoft recommends you to, then you should set up a new Windows domain
account specifically for Analysis Services, and then use the SQL Server Configuration
Manager tool to set the Analysis Services service to run under that account. You
should then give that account any permissions it needs in SQL Server on the tables
and views you'll be using. Most of the time, Read permissions will be sufficient.
However, some tasks, such as creating Writeback fact tables, will need more. You'll
notice on the Impersonation Information tab in the Data Source Designer dialog in
SSDT that there are some other options for use with Windows authentication, such
as the ability to enter the username and password of a specific user. However, we
recommend that you use the Use Service Account option so that Analysis Services
tries to connect to the relational database under the account you've created.
If you need to connect to your data source using a username and a password (for
example, when you're using SQL Server authentication or Oracle), then Analysis
Services will keep all sensitive information, such as passwords, in an encrypted
format on the server after deployment. If you try to script the data source object out,
you'll find that the password is not returned, and since opening an Analysis Services
project in online mode essentially involves scripting out the entire database, you'll
find yourself continually re-entering the password in your data source whenever
you want to reprocess anything when working this way. This is another good reason
to use project mode rather than online mode for development and to use Windows
authentication where possible.
Creating Data Source Views
In an ideal world, if you've followed all of our recommendations so far, then you
need to do very little work in your project's Data Source View ( DSV )—nothing
more than selecting the views representing the dimension and fact tables and setting
up any joins between the tables that weren't detected automatically. Of course, in the
real world, you have to compromise your design sometimes and that's where a lot of
the functionality available in Data Source Views comes in useful.