In data warehousing, the date dimension is an essential component. When building a cube for a data warehouse in SQL Server Analysis Services (SSAS), creating a date dimension is a common requirement. In this article, we will explore various aspects of creating a date dimension in SSAS, including handling multiple date dimensions and managing unknown rows.

Role Play Dimension

In SSAS, we can have the same dimension added to the cube multiple times with different names. This is known as a “role play” dimension. The purpose of having a role play dimension is to have identical dimensions in the cube. These cube dimensions have the same attributes, members, hierarchies, sorting order, properties, default member, and display folders. By using role play dimensions, we can ensure consistency and maintain identical dimensions across the cube.

For example, in a retail banking scenario, we may have a transaction date dimension and an effective date dimension in a checking account cube. Both dimensions have attributes such as date, month, quarter, and year. The formats of these attributes are the same, and both dimensions have members from 1993 to 2010. They also share a Year-Month-Date hierarchy. Any changes made to one dimension will automatically be reflected in the other, ensuring consistency.

Multiple Named Queries

In SSAS, we can create several named queries from the same date dimension table in the Data Source View (DSV). This allows us to select different ranges of data or have different sets of columns for different date dimensions. For example, in a credit card cube, the start date dimension may have a different date range compared to the expiry date dimension. Additionally, different date dimensions may require different formats and hierarchy structures.

By creating separate named queries in the DSV for date dimensions, we can configure dimension properties differently, such as unknown member, default member, error configuration, and display folder. We can also specify dimensional security differently if needed. In some cases, we may even need to create multiple date dimensions from a single named query on the DSV to achieve the desired configuration.

Unknown Member

An “unknown row” refers to a row on the dimension table to which orphaned fact rows are assigned. In SSAS, we can handle unknown rows by mapping them to the dimension’s unknown member. This allows us to capture orphaned rows and ensure correct totals of measures in the cube.

By default, SSAS maps orphaned rows on the fact table to the dimension’s unknown member. However, we can customize this behavior by setting the UnknownMember and ErrorConfiguration properties. It is important to note that changing the unknown member and error configuration properties will affect all dimensions, not just the date dimension.

Date Hierarchies

Building a hierarchy and hiding the composing attributes is considered a best practice in SSAS. This helps improve performance and organization of the cube. In SSAS 2008, it even provides a warning if we haven’t hidden the members used in the hierarchy.

When creating date hierarchies, it is recommended to use a clear and standardized date format, such as the ISO 8601 format (yyyy-mm-dd). This format eliminates confusion caused by country-specific date customs. Additionally, it is advisable to name hierarchies properly, reflecting the levels and avoiding abbreviations to ensure clarity for users.

Conclusion

Creating a date dimension in SQL Server Analysis Services is a crucial step in building a data warehouse. By understanding concepts such as role play dimensions, multiple named queries, handling unknown members, and designing date hierarchies, we can create a robust and efficient cube that meets the needs of our business users.

Stay tuned for part two of this series, where we will explore more advanced topics related to date dimensions in SSAS.