Symptom
Environment
- SAP Analytics Cloud
- SAP Business Warehouse
- SAP BW/4HANA
Reproducing the Issue
- Create a Story based on BW Query with Date field like: "Calendar Year/Week" or "Calendar Year/Month"
- Try to create a Filter by Range on this dimension
- Open filter dialog for this characteristic and confirm the Filter by Range option is not available
Cause
To be compatible with SAC range filters, SAC requires the type of a date dimension to be 0DATE or a reference to a characteristic 0DATE.
The date type dimension needs to follow the format described in the following SAP help article: Restrictions to SAP HANA and BTP Live Data Connections - Stories
(Note that the document refers to HANA connection but the restriction date ID format equally applies to BW non-hierarchical dates as well.
According to the SAP Help page chapter mentioned above, only 0CALDAY is a reference characteristic to 0DATE, and that is why this is the only characteristic able to provide the filter by range option.
In short, the "Calendar Year/Week" and "Calendar Year/Month" dimensions don't have the properly ID format, that is why the range filter option is not available.
In addition to making sure the semantic type is correct in order for the dimension to be compatible with date range filter feature, the structure of the dimension itself also needs to be correct. E.g:
For the "Calendar year" and "Calendar year/week" dimensions, their ID consist of just the year number (e.g. 2020) or the year-week number (e.g. 01.1995). These are not compatible with date range filter either (unlike the "Calendar day" dimension).
Resolution
For the characteristic to have the range filter option, make sure the dimension is of type date or reference to 0DATE and not other types like text or string.
Usually "Calendar day" (0CALDAY) has the the semantic type "date" or is a reference to characteristic 0DATE, this is why the range filter option will be enabled in SAC.
Keywords
KBA , LOD-ANA-ML , SAC Modelling , BW-BEX-OT-BICS-INA , BW INA provider , How To