Background:
The client utilizes an SSAS Multidimensional cube for sales reporting and analytics, with the primary means of harnessing this data being through Excel Pivot Tables.
Summary:
The client's request involves utilizing the Hierarchy field from the requested shipping date dimension to display sub-values beneath the respective date rows. These sub-values may originate from dimensions other than the requested shipping date dimension.
Furthermore, the desired format comprises a single column containing both dates and sub-values, with a second column dedicated to displaying quantities, such as sales quantity, as illustrated below (Image 1). For instance, collapsing the month value would sum all quantities for the month while concealing the sub-value (salesID). If the year row were collapsed, it would encompass the months and salesID values within that singular row. Image1
Current behavior
The hierarchy feature is failing to collapse sub-values under year, month, and date as intended.
This results in sub-values being displayed outside the collapsed row, beneath the corresponding dates. Furthermore, this behavior does not enable the concealment of sub-values like SalesID when collapsing dates.
The below image (image 2) depicts the current hierarchy behavior. In this case the year 2022 is collapsed while the sub value (SalesID) remains visible and disjointed from the appropriate date, additionally the quantities are not summed on collapsed year row as it should.
image2-subvalues not collapsed within dates
IMPORTANT NOTE: When using the hierarchy attributes as individual row items (not consolidated into a singular hierarchy field) the pivot table behaves exactly as desired. See image3
Additional Notes:
Reviewed the dimension attribute relationships between the requested shipping date dimension and the salesline dimension to make sure they were logical. The current relationship keys are based on "Shippingdaterequested" from the saleline dimension to the "Date" in the requested shipping date dimension. This relationship makes sense and is a single attribute relationship between the two dimensions.
Tested other hierarchies already constructed in the cube, all of the hierarchies demonstrated the same undesired behavior.
Reviewed the attribute formats of shippingdaterequested and Date. Both attribute formats matched.
Reviewed the data sources for both dimensions and did not observe any unusual data entries in the date fields.
Checked the data type of both fields and determined they are correctly typed as date values.
Reviewed the hierarchy format in the requested shipping date dimension, the format was Year, Month, date. This appears to be correct.
Reviewed the hierarchy relationships within the requested shipping date dimension to ensure the proper relationships. The relationships were correct: Year->Month->Date
Reviewed all settings for the hiearachy as well as the individual attributes (year, month, date).
Deleted and rebuilt (several times) the requested shipping date hierarchy. The effort was not successful in producing a different outcome.
Most testing used "SalesID" as the additional row value but I did use other salesline attributes as well as other dimension attributes, all variations resulted in the same hierarchy behavior.
To reduce variables, I updated the salesline datasource query to JOIN on the source table of the requested shipping date dimension. This was an attempt to eliminate the concern of two dimensions possibly not having the proper relationships or keys. This test allowed for the year, month, date to be available within the same dimension as the "salesID". The result was the same.
Dimension Attribute Relationships Review:
Reviewed the dimension attribute relationships between the requested shipping date dimension and the salesline dimension to ensure logical coherence. The current relationship keys are based on "Shippingdaterequested" from the salesline dimension to the "Date" in the requested shipping date dimension, forming a single attribute relationship between the two dimensions.
Hierarchy Format and Structure Analysis:
Analyzed the hierarchy format within the requested shipping date dimension, confirming the structure as Year -> Month -> Date. Additionally, reviewed the hierarchy relationships to ensure the proper relationships: Year -> Month -> Date.
Data Source Examination:
Reviewed data sources for both dimensions, verifying attribute formats and data types. No unusual data entries or discrepancies were observed in the date fields.
Hierarchy Rebuilding Process:
Deleted and rebuilt the requested shipping date hierarchy multiple times. Despite these efforts, the outcome remained consistent with undesired behavior.
Impact of JOIN Operation:
Updated the salesline datasource query to include a JOIN operation with the source table of the requested shipping date dimension. This test aimed to eliminate concerns of improper dimension relationships or keys. However, the result remained unchanged.
Alternative Solutions Consideration:
Explored various alternative solutions and workarounds, but none provided a satisfactory resolution to the hierarchy collapse behavior.