Database creation fails during Analysis Services project deployment

(0) Share

Report

Posted on by Community Member

Good afternoon,

We have 2 AOSes (Development and Pilot) with more or less identical configurations on different machines connecting to same SQL Server.

When I create SSAS project (cube) on Development AOS - everything works fine, but when I do the same on Pilot AOS - Analysis Services project is being created, but the actual SQL database is not, what returns following error:

No database named SalesAnalysis was found on server AXSQL3. Errors occurred while processing the Analysis Services database: SalesAnalysis

Both AOSes are using same AD users, etc.

If the database with the same name already exists, it gets deleted, but new one is not created, so I guess the user should have enough access rights on SQL Server.

Thank you!

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

Community Member on at

Like (1)

Report

hi,

From my experience, this is related to security.
I've bumped into it before and made the following changes:
- Accounts added to SSAS :AOS service account, Business connector service account, SQL server database enginge service account, SQL Server Analysis Services account

- Made the SSAS service run under the SQL server database enginge service account

- Manually configure the connection string on ‘Dynamics Database’ datasource in the Dynamics Ax database on the SSAS server and link it to the Ax database. The connection string should look like this: Provider=SQLOLEDB.1:Data Source=DBServer\Instance;Integrated Security= SSPI;Initial Catalog=AXDBName’

I'm aware this is probably not the most secure and state of the art security set up, but it got my SSAS going ...
Any comments or tips on how it is set up appropriate (security-wise) is welcome ...

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you Tom, but I already had those security settings sorted.

However, after deployment failed on Visual Studio 2008 and I got error "Errors in the metadata manager. The data type of the 'Total customer sales Customer account' measure is not valid because the data type of the measure is a string type.", I went back to my Views in the Perspective and changed AccountNum analysis usage type from Measure to Attribute, because apparently SSAS doesn't like strings to be used as measures.

Then I redeployed the project from AX and this time the database has been created, although now I got this nasty error:

Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: The SELECT permission was denied on the object 'DIMENSIONATTRIBUTE', database 'AxSQL3', schema 'dbo'.; 42000.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'FRAMEWORKDIMENSIONSATTRIBUTEVALUESSET', Name of 'FrameworkDimensionsAttributeValuesSet' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'OLAPDEPARTMENT' attribute of the 'FrameworkDimensionsAttributeValuesSet' dimension from the 'SalesAnalysis initial' database was being processed.
Server: The current operation was cancelled because another operation in the transaction failed.
Errors in the high-level relational engine. The database operation was cancelled because of an earlier failure.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'FRAMEWORKDIMENSIONSATTRIBUTEVALUESSET', Name of 'FrameworkDimensionsAttributeValuesSet' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'OLAPMAINACCOUNT' attribute of the 'FrameworkDimensionsAttributeValuesSet' dimension from the 'SalesAnalysis initial' database was being processed.

Will try to sort this one out now.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (1)

Report

Ok, solved.

The last error was because of SSAS service account being changed. Once I set it back to previous one, everything was fine.

So in summary:

1. Don't use measures for strings

2. Use same account for SSAS service as you set it in AX

Was this reply helpful? Yes No
Dick Wenning 8,705 Moderator on at

Like (0)

Report

is the olap server url correct in ax

Was this reply helpful? Yes No