ssascube

SSIS Cube process fail

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.
Solution

  • 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:

    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.