Upon modeling my very first data warehouse model (for historical stock market data), I stumbled across a data relationship that I don't know how to best account for. The fact tables include multiple years of end-of-day data and intraday data for various assets and exchanges. Now I expect to filter the data by date, which is why I created a date dimension. Similarly, I want to filter intraday data by time, which includes a date. This far it seems like a usual Snowflake Schema to me.
However, trading days depend on both the date and exchange. Similar is true for opening hours of an exchange. Both of these dimension tables would mean a join of two snowflake leaves. Is this the standard way of doing this or is there a better design pattern?
Best Answer
This kind of dimension is called Role-Playing Dimension.
This is tipical of Multi-Dimensional SSAS modeling.
However SSAS Tabular modeling does not support role-playing dimensions; you can overcome that limitation by using DAX, enabling inactive relationships for specific calculations (you can define multiple relationships between the same tables in Tabular). However, if you want to offer navigation in different role-playing dimensions to the user, the best practice for both Multidimensional and Tabular is to import the same table multiple times, renaming data and metadata.