Columns, Rows, or Data Disappear With SSAS 2005 Cubes
Columns, rows, or data can disappear if you set the default member of a hierarchy to a member that does not exist with every other member in the dimension. To avoid this problem, change the default member that caused the disappearance to a member that exists with all other members in the dimension.
Columns, rows, or data can also disappear if members are specified that result in one or more non-existent tuples. There is currently no workaround for this scenario. For more information, see Microsoft Knowledge Base article #944527 at http://support.microsoft.com.
You may also encounter unexpected results if the default member of a hierarchy is a member that doesn't also exist in all other hierarchies in the dimension, and if you query members from different hierarchies in the same dimension.
For example a crosstab includes the following (using the Adventure Works cube):
- Rows: Generate([Adventure_Works].[Account].[Accounts],set([Balance
Sheet],[Units])) nested with
children([Adventure_Works].[Department].[Departments]->:[YK].[[Department]].[Departments]].&[1]]])
- Column: [Adventure_Works].[Account].[Account Number].[Account Number]
- Measure: [Adventure_Works].[Measures].[Amount]
You run the report and notice that the query renders with some blanks cells. You then apply the simple detail filter [Amount]>1 and run the report. Only row labels are displayed and all data and columns are missing.
In the Adventure Works cube, the [Account].[Accounts] attribute has a default member set to [Net Income]. When evaluating the GENERATE set expression, SSAS looks in the entire cube space and looks at all coordinates for the [Account] dimension. These coordinates include both [Account][Account Type].&[] and [Account].[Accounts].[Net Income]. Because these two coordinates don't exist within the same hierarchy, SSAS returns an empty set.
To avoid this problem the SSAS administrator must set the default member in the cube to a member that exists in all other hierarchies.