Re: DW & OLAP Server
Coming to grips with the way that MSAS handles credentials and processing is a challenge when first starting out. I'll try to go over what i understand and save you some time.
When you create data sources in MSAS try and make (if at all possible) a concerted effort not to use windows authentication. Until you come to grips with how the MSAS service uses credentials once something is deployed, you'll be pulling your hair out trying to figure it out.
If you can, make SQL server accounts and use those users names and passwords for the data sources. These will be used by the AS service on the server to which it's deployed.
The way that impersonation information (on the data source property dialog) tab is set up is confusing to say the least, and it kind of leads you to think that those settings will work for a projects ultimate deployment desination. Which it doesn't. Avoid the confusion by leaving the option set to default.
When processing a remotely deployed solution, the activity takes place under the account credentials used by the Analysis Services Service itself. If you leave your data sources set to windows authenticationon the remote system will try to authenticate against your data source with the credentials of the service, not your credentials.
Generally the Analysis Services Service runs under one of the system accounts, or a domain service account. Generally these (for good reason) accounts don't have access to databases, and any process activity relying on windows authentication will fail.
Hope this Helps.