End Users Cannot Access OLAP Cubes from Excel
Symptoms
Situations may occur where the Jet Analytics cubes have been deployed on the production server, but end users are not able to access them from their local machines. This article will detail some of the most common causes of this as well as recommended solutions for each.
Resolutions
-
Windows Firewall
It is possible that the Windows Firewall is preventing inbound connections to the Analysis Services database, which will not allow end users to communicate with Analysis Services from their local machine. In most situations, this can be resolved by adding an Inbound Rule to the firewall to open TCP Port 2383. Microsoft has compiled an article regarding this that can be accessed from the link below:
Configure the Windows Firewall to Allow Analysis Services Access
-
Domain
If the Analysis Services database is on a separate domain than the end user(s), communication may not be allowed across the domains. Configuration of domain trusts should be done by a network administrator. Microsoft has compiled an article regarding the creation of trusted domains which can be accessed from the link below:
-
Cube Processing
It is possible that the OLAP cubes have not been processed, which would present the user with a blank window when they attempt to access the OLAP database. To determine is a cube has been processed, you can take the following steps:
- Open the Jet Data Manager
- Navigate to the Cubes tab
- Expand the Cubes node
- Right click one of the cubes and go to Advanced -> Cube Information
The State at the top of the Cube Information screen should say "Processed". If it says "Unprocessed", the cubes should be processed through a normal execution of the OLAP database from the Jet Data Manager.
-
Are the Cubes Accessible from the Server?
It is possible to determine whether the connectivity issue resides with the OLAP database itself or with network access to Analysis Services by testing access to the OLAP database on the server on which the OLAP database resides. This can be done by creating a connection to the cubes through Excel or by connecting to the OLAP database directly through the SQL Management Studio. If using SQL Management Studio, the user should log on to Analysis Services (not the Database Engine), and take the following steps:
- Expand the Databases Node
- Expand the desired OLAP database
- Expand the Cubes Node
- Right click the desired cube and select Browse
If the measures and dimensions list appears, SQL Management Studio was able to connect to the cube and issues with the OLAP database itself can generally be ruled out. -
Have OLAP Security Roles Been Defined and Deployed for the User?
Only users that have been specifically granted access to the OLAP database through the Jet Data Manager will be able to see and use the OLAP cubes. As an exception, users who are Administrators on Analysis Services will always be able to see all cubes and OLAP databases. If a user has not been added to the OLAP security settings, they will need to be added before they will be able to access the OLAP cubes from any machine. Detailed instructions on defining and deploying OLAP security are covered in the KB article below:
-
Client Machine Throws the Error: An unexpected error has occurred: Could not load file or assembly 'Microsoft.AnalysisServices.AdomdClient, Version=9.0.242.0...
This error will occur when the ADOMD.NET client has not been installed on the client machine. For the resolution, please reference the knowledgebase article below: