This makes no sense what so ever, nowhere in my query shown above(the query generated from excels search) do I mention the item name, and you clearly see what ".Members" should be returning in the screenshots above(and what is returned by the multidimensional cube), it just doesn't in tabular, worst of all I captured(with SQL profiler) the query from excel on searching the item hierarchy of both my identical tabular and multidimensional versions of my cube and they generate the same exact MDX query with the only difference being the name of the cube. I can actually demonstrate this in excel as well: Ok I've made a bit of a breakthrough, I've altered my query to see if any combination of input would allow it to work, and I've found one, if I change my filter from "Per" to "GL" I get a result, it turns out that the query outlined above is actually NOT returning results from "I1 - Item Category", it's actually returning results from ".Members" which contains a distinct list of item name's. Has anybody seen this before? Is there something I can change with the cube to not have this occur? Is it a syntax issue with the query, if so why would excel generate an invalid query?Īnother interesting item of note, I noticed that the search function for a leaf level hierarchy works in excel(as noted above) so I swapped out "I1 - Item Category" for "IA - Item Name" and the query runs without issue (of course we're now searching the leaf level), it's something about running this query on a non-leaf level in this case "I1 - Item Category" that is causing this error to occur. I think it's related to something in the query or the format of the cube but I'm not quite sure which is the issue, and I'm not super familiar with MDX. I'm quite sure I don't have a permission issue, I can reproduce the error by running the query as an admin on that server. It's a interesting error message, I've actually never seen it before, and there's not much on the internet about it, just a few dead ends. MEMBER_TYPE ON COLUMNS FROM īut it appears to be erroring out (would explain why I'm not getting any results in excel) when run against my cube:Įither you do not have permission to access the specified member, or Hierarchize(Generate(FilteredMembers, Ascendants(.currentmember))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME, Set FilteredMembers As 'Head (Filter(AddCalculatedMembers(.Members), InStr(1. cChildren As 'AddCalculatedMembers(.currentmember.children).count' I've been doing a bit more digging, I setup SQL Profiler to run against my SSAS server while I performed the search, seems it's generating a MDX query(this time on my "item" hierarchy): WITH MEMBER. Is this some sort of configuration in the model I can change? Or something in excel I can change? Update 1 Has anyone else experienced this issue before, I'd love to post the model of the cube but it's rather proprietary, I have experienced the same issue in multiple types of proprietary cubes on multiple servers and clients. I can get it to work if I'm looking at the leaf level of the hierarchy, but it appears as though the node level of the hierarchy search feature for tabular cubes in excel doesn't work. I receive no results, the search doesn't appear to function correctly. However if I filter the same hierarchy in the tabular cube, the results are different: The level below will be filtered by the text and the results will be returned. (MultiDimensional - In Excel as a filter) If I connect to the cubes with excel and drag the top level of a hierarchy(in this example "Company" into my filter category in the Multi-Dimensional cube, then enter some text in the search bar in excel: I have two cubes with the same data backing them, one a Multi-Dimensional cube, the second a Tabular cube.
0 Comments
Leave a Reply. |