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!

Troubleshooting Kerberos on Enterprise Portal – Wrong NTAuthenticationProviders

I’ve been helping with setting up Kerberos authentication for a couple of installations so far. This is normally just a matter of reading the installation guide and also the Kerberos setup guide. If you’re reading this post, you probably already know why you need to set up Kerberos; it’s because Enterprise Portal is running on another server than your SQL Server. This is a pretty normal setup. The configuration needed to get this to work might be tedious, but when you’ve done it a couple of times, it isn’t really that hard.

What if it still doesn’t work?

Kerberos errors are by default not logged, so you will have to do some modification in the Registry to turn on logging. This is not difficult. You would want activate this on the Enterprise Portal Server. The procedure is described on Microsoft’s Customer Support site, and it’s a matter of adding LogLevel (DWord) with the value of 1 to the Registry location HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters
When this entry is saved, the logging will start immediately. No restart of server or services is needed.

Now there are quite a few errors that might throw you off, so keep that in mind when you open the Systems log in Event Viewer.

Remember to turn off the debugging when you’re done.

NTAuthenticationProviders matters

The procedure for setting NTAuthenticationProviders is located on Microsoft Customer Support site. It is quite similar to the instructions on how to setup IIS when SSRS is running on SQL 2005. The topology I was troubleshooting had Windows 2008 Server on both the SharePoint server (where Enterprise Portal was installed) and on the SQL 2008 Server. When loading Enterprise Portal in a browser on the SharePoint server everything was working perfectly. The SSRS reports were loaded and displayed KPIs and fine graphs. If we tried to load the same page from any other machine all the webparts displaying reports showed an error saying it could not load the report and I should “Validate that the Report Manager URL is correct”. Obviously the setup was correct since I could load the reports perfectly if I tried load the page from the SharePoint server. This was a Kerberos problem!

Since we had already checked the installation and setup several times and searched for any discrepancies, we had to involve Microsoft to hunt down the problem. For some reason the NTAuthenticationProviders on the SharePoint server was set to “NTLM” only, and not “Negotiate, NTML”. After setting it to “Negotiate, NTLM” everything was working as expected.

If you don’t want to read the support article at Microsoft, here’s what you need to do:
1. Click Start, click Administrative tools, then click Internet Information Services (IIS) Manager.
2. In the left pane, click the Web sites directory and locate the SharePoint web site hosting EP on the right-hand side.
3. Locate the ID (Identifier) column and write down the ID of the web site
4. If running Windows 2008/R2 then run an elevated cmd prompt
5. Change folder to c:\inetpub\adminscripts folder by entering the following command and press Enter: cd \inetpub\adminscripts
6. Use the following command to determine if both the "Negotiate,NTLM" authentication providers are setup or not. In the command, replace <identifier> with the ID identified in step 3 above. Then enter the commands in the command prompt and press Enter:
cscript.exe adsutil.vbs get w3svc/<identifier>/root/NTAuthenticationProviders
7. If the response does not show "Negotiate, NTLM" then use the following commands to set it:
cscript.exe adsutil.vbs set w3svc/<identifier>/root/NTAuthenticationProviders "Negotiate,NTLM"
8. The providers are now set. We need to restart the IIS services. In the command prompt, type iisreset.exe and then press Enter.

So basically, this needs to be checked on the SharePoint server if you are setting up Kerberos as authentication to the Enterprise Portal. I hope this helps someone.