How to document role playing?

View previous topic View next topic Go down

How to document role playing?

Post  shelzalee on Mon May 20, 2013 9:20 am

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.

shelzalee

Posts : 6
Join date : 2011-12-06

View user profile

Back to top Go down

Re: How to document role playing?

Post  BoxesAndLines on Mon May 20, 2013 10:35 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

role play the relationship

Post  shelzalee on Mon May 20, 2013 11:43 am

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.

shelzalee

Posts : 6
Join date : 2011-12-06

View user profile

Back to top Go down

Re: How to document role playing?

Post  BoxesAndLines on Mon May 20, 2013 12:43 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Role Playing in an ERD

Post  shelzalee on Mon May 20, 2013 12:51 pm

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

shelzalee

Posts : 6
Join date : 2011-12-06

View user profile

Back to top Go down

Re: How to document role playing?

Post  BoxesAndLines on Mon May 20, 2013 3:46 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: How to document role playing?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum