• Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About
Microsoft Data & AI

SQL Server Analysis Services

Excel Error: The item could not be found in the OLAP cube

11/1/2015

2 Comments

 
Issue: When working in Excel 2013 Prof Ed and a SQL Server 2012 multidimensional cube, you receive the following error when adding a hierarchy or attribute to the FILTERS section of the PivotTable Fields GUI.
Picture
The above error is usually caused by an object that was removed or renamed in the cube.  However, this was not the case for a recent client.  I Googled the error (like you probably did to even arrive at my blog site), but found nothing except that an object was deleted or removed from the cube.  As this was not our scenario, I'm doing this little write-up hoping that is will be helpful to someone in the same predicament.

Summary Solution: 
  1. Remove the offending dimension from the multidimensional cube
  2. Save your Visual Studio solution
  3. Rebuilt your Visual Studio solution.
  4. Add the dimension back into your multidimensional cube.
  5. Deploy and reprocess the cube.
  6. Test both the hierarchy and a couple of independent attributes in Excel's PivotTable Fields <FILTERS> section.

Cause of the Error: Unknown.  I have found on more than on one occasion that the underlying XML of multidimensional cubes becomes "out of sync".  I realize this isn't a very technical response, but if you have spent time comparing "working" XML code to "unworking" code, you know that sometimes it is easier to just remove and readd the object.

Resolution Logic:  In the latest  occurrence of this error, I worked through the following steps trying to find the cause before throwing in the towel and removing the dimension from the cube.  It was determined that ..
  • This error only occurred with one of two role playing dimensions.  15+ other dimensions in the cube did not produce the error.
  • This behavior happened with both hierarchies and independent attributes.
  • This error only occurred in Excel's FILTERS section of PivotTable Fields.  There was no problem filtering through COLUMNS or ROWS.
  • This behavior did not happen in a SSMS (SQL Server Management Studio) cube browser.   Both hierarchies and attributes filtered just fine.
  • This error was not measure group specific – it occurred with no measure group selected (i.e. no measures dropped into VALUES).
(And now I began looking for property differences that might cause this error...)
  • The role playing DIM that did work properly, had even a longer name than the non-functioning role playing DIM.
  • The role playing DIM that did work used an alias name.  The non-functioning role playing DIM did not.
  • The role playing DIM that did work had HierarchyUniqueNameStyle set to the default value of IncludeDimensionName.  The non-functioning role playing DIM had the default value of ExcludeDimensionName.  I found a 2nd DIM that also was set to ExcludeDimensionName, and it worked just fine in Excel's FILTERS section.
Conclusion:
It was a bit of work to remove a shared dimension from a large cube and re-add it, checking to make sure all cube relationships and properties remained the same, but in the end, this is what fixed the error.  if you have come across this same problem and have found a better solution, please post it in blog comments.  I'd like to hear your creative solution!
2 Comments
Miki Kaufhold
3/31/2017 05:04:35 am

Thank you for this nice post.

In my case the issue was only temporary (I assume bound to Cube rebuild).

I was setting via VBA the PivotField.VisibleItemsList = aFilterValues property.

The cause of this issue in my case was difference in letter case of a filter.
Accepted to set VisibleItemsList was let's say aFilterValue(1) = "Backlog" even though the value in VisibleItemsList was "backlog".

To put it differently a command like
aPivotField.VisibleItemsList = aPivotField.VisibleItemsList
would have (and did) yield that error.


Note: The PivotItem.Caption was also "Backlog".

Solution was to use proper letter cases, i.e. "Backlog" in my case.

Reply
TS Massage Florida link
12/26/2022 02:36:59 am

Awwesome blog you have here

Reply



Leave a Reply.

    Categories

    All
    Cache
    DBA
    Excel
    Extended Events
    HOLAP
    MOLAP
    Multidimensional
    ROLAP
    Server Mode
    SSAS
    Tabular

    RSS Feed

    Tweets by @SqlServerSME
Powered by Create your own unique website with customizable templates.
  • Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About