SSIS Cube process fail

1
votes

I am fully appreciated someone could help me with this problem. I create and try to public a very simple Microsoft Cube on a local SQL server, but for some reason, I could deploy the cube but fail to process it.

The error message says: Internal error: The operation terminated unsuccessfully. The following system error occurred: The user name or password is incorrect. Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Adventure DS', Name of 'Adventure DS'. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Sales Order Header', Name of 'Sales Order Header' was being processed. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Sales Order Header', Name of 'Sales Order Header' was being processed. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Sales Order Header', Name of 'Sales Order Header' was being processed. Errormessage

I read a lot of topics, and I am sure

  1. I use the SA/administrator account in both Data source and Public server. All test connection was successed.
  2. I check the SSAS Analysis server and found my cube was there without a problem.
  3. I set up a role for/with sa account.
2
ssascube

2 Answers

2
votes

According to the error message, your processing cannot access the DB. Most likely the problem here lies in Data Source Impersonation settings.
Briefly, each Data Source has two tabs - the first define connection settings, and the second - Impersonation mode, like in the screenshot below.

enter image description here

This impersonation defines a Windows user which is used to access the Datasource; is important because you have to use Windows accounts for accessing the DB. That is why it is called "Impersonation" - handling identity of connecting user. When you design a cube - your account is used for data access, but once you deploy your cube to the SSAS Server - there is no current user account. The Server has to deduce it from Impersonation tab. Here you have four options:

  • Specific Windows user name and password. Specify Windows user name and password here. Please note - Windows user name, you cannot use SQL user names here.
  • Use the service account - self-explanatory, use SSAS service account
  • Use the credentials of the current user - dangerous, uses current logged user account. Cannot be used for processing.
  • Inherit - use currently logged User account, if not accessible - use Service Account.

So, for your case - I would go for the most feasible solution - will be to set up a Windows account on the DB server, grant DB access to this account, and specify it in the first Impersonation option - Use specific Windows user name and password.

0
votes

I had the same problem actually. Choose the first option from Impersonation Information:

  • Use a Specific Windows user name and password.

It is very important to use the exact current Windows user name. You can find the current Windows user name by opening cmd and running the command 'whoami'