How to document role playing?

We have a date dimension we use over and over. We typically just name the foreign key in the FACT table that connects to the primary key of the date dimension appropriately without using views. If we were to join the date dimension multiple times for a report, we’d alias it at that time in the BI tool. I’ve been trying out various modeling software, and I’m wondering what the proper way is to model the use of the date dimension.

I’ve found ER/Studio the most intuitive so far, and I’ve been working with a “Dimensional” model. My question is how do you document this type of role playing? Do I just leave the date dimension disconnected and everyone assume if they see something named containing “date” and “sk” that it is to connect to the date dimension table. I couldn’t find a way to get the software to let me connect a field from a dimension that doesn’t match the name of the field in the fact table. Perhaps this is because I’m using the “dimensional” model. Should I have used “relational”? I’m not sure if this is a short coming of the software, or if I shouldn’t diagram it out this way. How do you document the re-use of a dimension? I even tried creating a view in the diagramming software based off of the date dimension to see if views would work, but the “view connector” didn’t let me pick which fields the view of the dimension and fact table were joining on.

Thanks,
shelzalee.

You need to role name the relationship which will rename the FK on the target table. Or just rename the FK column to the appropriate role name.

BoxesAndLines,

Thank you. Your response made me dig a little deeper, and I found the option for the "Logical Rolename" that will allow me to rename what the FK value will be on the Fact table. This will allow me to connect to my date dimension with different foreign key names. However, I'm still wondering about the proper way to document role playing in an ERD. If I'm reusing this date dimension, I may join to it from the same fact table multiple times. We do the joining and aliasing of the re-used dimension in our BI tool, but I want to perform the joins in the ERD for documentation purposes, what should an ERD of a role playing dimension look like?

Thanks,
shelzalee.

The proper way to do it is to simply role name the relationships coming in from the dimension. For example, if I have an address dimension with two roles, Billing and Service address. I create two relationships and role name the FK's billing_address_sk, and service_address_sk.

I understand renaming the FK field in the FACT table to an appropriate name for the role. But what do you do with the connection to the dimension? How do you document it? You are connecting to the exact same table twice or that's how a diagram of it would look. In some cases, you may create a view and connecting to it for the role (that leads to another set of questions about showing the relationship of a join to a view which I couldn't see a way to do in ER/Studio), but in our case with the date dimension, we do not use views. Say of I have an orderdate_sk and a duedate_sk in the same fact table that both connect to dim_date.date_sk, what is the best way to represent this in an ERD?

Thanks,
Shelly Lee

The best way to show it is two relationship lines. You can display the relationship name or FK constraint name in the diagram if it's important to know which line goes with which foreign key attribute.