Error message: Data shows up in the Excel cube file that doesn't exist in Catalyst.
Where is this data coming from? Cube showing non-existent data
Description:
Users will unexpectedly see data that doesn't exist in Excel cube files within a pivot table. This data can't be vetted elsewhere, such as in the Catalyst website.
Solution:
Please check to see if the above "Pivot Table Option" is enabled. If it is, then you may see rows for data that is no longer present in the cube. Deselect the option to fix. For context, clicking that option is going to do exactly what is describes, it will list all items in the hierarchy regardless if there is data or not. If there are two or more hierarchy's in the same cube you're going to see quite a few results not affected by the presence or lack of data. There are definite use-cases where it makes sense to use the "show items with no data" option, but EBM Software can't support such a function directly.
Context:
A user might opt into ticking that box (again see screenshot above) in an attempt to make the cube mimic the hierarchy exactly. But that likely won't work because Excel explodes the pivot table based on the field(s) you select, then reduces it for combinations where data exists.
In terms of Catalyst, when two fields are used, data can only exist if that combination exists in the hierarchy. However for Excel purposes, all combinations are possible - it takes all options in field #1 and combines it with all options in field #2.
This seems to be a limitation of the 'show all data in rows/columns' button in Excel. There currently isn't a way in the cubes to limit the fields shown to only combinations that exist in Catalyst, but not limit it to those that have values. The only way to do that is a hierarchy export back in Catalyst.