Login failed error while processing OLAP cubes

I was requested to help with solving a processing error from SQL Server Analysis Services today.

While processing the engine fails to retrieve data from the Dynamics AX transaction database and throws the following error:
Login failed for user "DOMAIN\SERVERNAME$". Reason: Could not find a login mathcing the name provided (CLIENT: )"
(DOMAIN refers to the actual Active Directory Domain Name and SERVERNAME$ refers to the name of the server.

From the error it seems like the machine account is unable to login. Now in my scenario the SSAS service runs on the same server as the SQL Server Engine and the instance with the Dynamics AX database. In addition the SSAS service is NOT running using a dedicated Domain Service Account, but rather a local service account. The error then becomes sort of misleading if you read off the account name, because it is actually referring to machine account name.

The solution is simple, though.

Open the SQL Server Configuration Manager, find the Analysis Service you are using when processing the OLAP.

Open the properties and copy out the "Account name" which the service runs under. Normally this would be something like "NT Service\MSOLAP$INSTANCE_NAME" (INSTANCE_NAME refers to the name of the SSAS instance).

Now open SQL Server Management Studio, open Security and Logins. Add a new Login and paste in the Account name from previous step as the Login name.

Before you save this new login, open "User Mappings" and find the Dynamics AX database you are trying to use as source for your OLAP. Tick the checkbox in front of the database name and grant the login "db_datareader" role membership.

Now save the Login and jump back to the processing step and try run it again.

Setting up scheduled processing of cubes in AX2012

Here is a quick guide on how you can easily and quickly setup scheduled processing of the cubes for Microsoft Dynamics AX 2012. The same approach works for other Analysis Databases, but sometimes it helps to put things in context in order to make it easier for you AX geeks out there.

I'm assuming you have setup and configured the standard Dynamics AX Analysis Database. Preferably also run a manual processing and analysed any errors or warnings. My example is from AX 2012 R2 on SQL Server 2012, and I just want to have it process without any error logging.

Start off with logging on to the Analysis Instance using SQL Server Management Studio. Find the "Dynamics AX initial" OLAP Database. If you have created multiple partitions, they will have their separate OLAP Database.

Right click and choose Process to open the processing dialog:

In the process dialog click the "Change Settings..." button:

A new windows will pop up. Head over to the tab "Dimension key errors" and click "Use custom error configuration". Choose to "Ignore error count" and define error conditions to "Ignore error". Here you can opt in for "Report and continue" and also have any issues be reported to a Log file, but do a test and pay attention to the Log filesize. It may grow rapidly, and even become several GB. 

Press OK, but don't start the processing. Now with the current setup, choose to Script the Action to a New Query Window:

The Action will be scripted as a XML structure. We will now use this command in a SQL Server Agent Job.

Head over to SQL Server Agent and create a new Job:

On the section for Steps, add a new Step:


The new Step needs to be of type SQL Server Analysis Service Command. It will be run under the SQL Server Agent Service Account, so make sure this service account has Read access to the Dynamics AX Transaction Database. Paste in the XML command from previous steps and save the step by pressing OK:


Now you can chose the Schedule section and create a new schedule for this job. You may chose to have it process on a daily basis, or multiple times per day. Choose whatever is suitable for your needs.

Finally, do a test of the Job and make sure it runs through. If it doesn't, use Job History to find the cause of any errors. Feel free to comment or ask, if you get stuck.

Thanks for reading!

Dimension Permission Error after successfully processing the Dynamics AX OLAP

Introduction

I have recently been involved in hunting down a strange "bug" in all the Reporting Services reports which reported data from the General Ledger Cube. The error message itself was "The 'Measure' attribute for the 'Measure' dimension has generated dimension security expression that is not valid. DimensionPermission (1, 209) the member '[Cheque Transaction Amount - Bank]' was not found in the cube when the string, [Measures].[Cheque Transaction Amount - Bank], was parsed.". Any users who were not members of the Local Administrators Group on the SSAS server would get this error on reports trying to report from the General Ledger Cube.

Configuring the Default OLAP cubes

Those who have worked with the deployment of standard OLAP cubes for Dynamics AX knows the main job is to make the OLAP process. This can only be achieved by removing any elements in the Analysis database that still reference missing elements from the OLTP (the Dynamics AX relation database). You probably also know the fact that if you turn off configuration that represents database elements like tables and/or fields, these elements will effectively be removed upon database synchronization. So basically any table or field removed from the OLTP will result in possible errors when processing the OLAP. If you can process the OLAP without errors, you're good to go (well, almost).

The 'Measure' referenced in my error was in fact based on the "BankCheque" Configuration, which was turned off in this particular installation. I could verify the measure missing from the OLAP as expected. So why did the report throw this error?

The solution

I'm not entire sure why, but the solution was in the definition of each Role connected to the General Ledger Cube. With the default OLAP there are numerous predefined Roles. These roles more or less resembles the default installed Roles in Dynamics AX. They have a semantic relation, but that's all. Adjustments to role membership in Dynamics AX are not automatically copied to the Roles in the OLAP. They are separate. The Roles in the OLAP contains various settings for limiting the access to the analysis data. There are settings for limiting access to cubes and all the way down to limiting access to specific dimensions. Given all this, there was indeed a setting on several Roles naming this particular dimension in its security context.

Under Properties for the CFO role and under Dimension Data, you'll find a dropdown for available dimensions you may specify security restrictions for. These dimensions are limited to those common for the entire OLAP, and those specific for any cubes this role has access to. Under each Cube there is even a Measures Dimension which allows you to define security context down to attributes. This is the source for the reference to the invalid 'Measure' attribute, namely the [Measures].[Cheque Transaction Amount - Bank]. After taking this reference out the "Allowed member set", and saving the Role, everything was working perfectly. Well, I actually had to remove this reference from all Roles granting access to the General Ledger Cube.

Finally...

Why the users in the Local Administrators Group are not effected by this, and why the OLAP may process without errors even when the Roles reference invalid dimensions, that I do not know. Please feel free to message me or comment under this article.

I hope this helps someone!