September 7, 2017 at 12:01 pm
I have an OLAP cube that I can display data from using an Excel Pivot Table and there seems to be no connectivity issues between Excel and Analysis Services server (2012). However, when I try to create an offline version of the cube in Excel, I receive an error message. Among other things, it says: "Could not connect to the redirector. Ensure that the SQLBrowser service is running on the **** server.; Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQL Browser service on **** server.; Either a connection cannot be made to the **** server, or Analysis Services is not running on the computer specified:"
Note that I contacted the DB unit and the SQLBrowser service is running on the Analysis Services server.
If anyone has any ideas as to what is causing my problem, please let me know. Any help would be appreciated. Thank you!
October 18, 2017 at 2:20 pm
Hi PB_BI,
Thanks for responding. Are you referring to the "Drillthrough" or the "Drillthrough and Local Cube" permission for the Local Cube/Drillthrough Access property of the cube?
It seems at the moment I only have "Drillthrough" permissions. However, a co-worker does have full rights to the cube which are listed below and yet he also gets that error I mentioned.
Full control (Administrator)
Process database
Read definition
October 19, 2017 at 12:45 am
Edding Jan - Wednesday, October 18, 2017 2:20 PMHi PB_BI,
Thanks for responding. Are you referring to the "Drillthrough" or the "Drillthrough and Local Cube" permission for the Local Cube/Drillthrough Access property of the cube?
It seems at the moment I only have "Drillthrough" permissions. However, a co-worker does have full rights to the cube which are listed below and yet he also gets that error I mentioned.
Full control (Administrator)
Process database
Read definition
Okay, well you definitely need Drillthrough and Local cube access. Just to check, this is a multidimensional cube?
I have to admit that this is the first time I have looked at this too deeply (it has always just worked for me) so what follows is a "best guess" at what I think is going on.....
I ran a Profiler trace and created a local cube from an online cube. It runs tons of queries for even the smallest of offline cubes, as well as the CREATE GLOBAL CUBE statement.
From what I saw this suggests that if you have any security settings in the lower levels (denied members, attributes, or sets) then this may stop you from being able to create the local cube since it cannot run all of the queries successfully.
When it runs the CREATE LOCAL CUBE statement, it passes a storage location. It'll look like this in the trace:CREATE GLOBAL CUBE [Your Cube] STORAGE '\\some\folder\yourcube.cub' FROM [Your Cube]
Make sure you have read/write access to that folder - I know the error you mention is not related to this directly, but you mention that it is "among other things".
Looking to MSDN, there are some restrictions:
Distinct count measures are not supported.
When you add a measure, you must also include at least one dimension that is related to the measure being added. For more information about dimension relationships to measure groups, see Dimension Relationships.
When you add a parent-child hierarchy, levels and filters on a parent-child hierarchy are ignored and the entire parent-child hierarchy is included.
Member properties are not created.
When you include a semi-additive measure, no slices are permitted on either the Account or the Time dimension.
Reference dimensions are always materialized.
When you include a many-to-many dimension, the following rules apply:
You cannot slice the many-to-many dimension.
You must add a measure from the intermediary measure group.
You cannot slice any of the dimensions common to the two measure groups involved in the many-to-may relationship.
Only those calculated members, named sets, and assignments that rely upon measures and dimensions added to the local cube will appear in the local cube. Invalid calculated members, named sets, and assignments will be automatically excluded.
I would check all of this and if possible run a trace yourself whilst attempting to create a local cube.
Failing all of that, if you have a development environment then try the following:
1. Make a backup of the msmdsrv.ini file on your development instance
2. In SSMS, right click on the instance node and go to properties. On the General page, click Reset to Default.
3. Restart the Analysis Services Service.
4. Restore a copy of your SSAS database to the development instance.
5. Assuming that you are able to do all of the above it means you are probably an admin on the development instance, so you need to either connect to the cube using credentials of someone in the same role as you are on the server where you are having the issues, more here (http://www.sqlchick.com/entries/2012/10/7/unit-testing-role-security-in-analysis-services.html )
6. Try to create a local cube
If that works then examine the differences between the properties on your development instance and the instance where you are getting the failure. This may give you the answer that you need.
Failing all of that it may be that you have some sort of internal network security setting which is preventing you from being able to create a local cube.
Good luck.
October 19, 2017 at 7:54 am
Hi PB_BI,
Thanks so much for the in depth response. It may take a while for me to digest as I'm not exactly an expert when it comes to cubes and there are some other things I'm working on at the moment. But I appreciate your thoughtful/detailed response. I'll let you know if I end up getting it to work.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply