Say I have 2 entities (showing entity/table and attribute/column names):
"Date Dimension"/D_DATE. Primary key = "Date SK"/date_sk
"Transaction Fact"/F_TRANSACTION. Attribute = "Keyed Date SK"/keyed_date_sk
How do I create a relationship between these two attributes via a macro?
I've tried using the AddWithRoleName function but I cannot get it to work - probably because I do not know how to create the PKPairList value correctly
Just to be clear, I want to create a relationship where the parent = D_DATE.date_sk and the child = F_TRANSACTION.keyed_date_sk
There's an example in the help documentation, I'll paste it here as well. You need to instantiate a Relationships Collection object, and then use the .Add method to add a new relationship between the child and parent table. The PK columns from the Parent will migrate to the child, as PK cols or as FK cols depending on the Relationship type you choose. You may have some issues because your child column is a different name, so the code below will create a date_sk column in your child table. If the column didn't exist, you could use the macro to provide the RoleName of the child column through the AttributeObj.RoleName Property. I'm not sure if that would work if your new rolename already exists in the table...
Dim MyModel As Model
Dim MyRel As Relationship
Dim strParent As String
Dim strChild As String
Dim RelType As Integer
' Naming the parent and child in the relationship
' Setting RelType to 5 indicates that the relationship type is mandatory
strParent = "authors"
strChild = "titles"
RelType = 5
Set MyRel = MyModel.Relationships.Add(strParent, strChild, RelType)
Apologies if I wasn't clear but you seem to have missed the key point in my post..
I need to create a role based relationship where the column in the child table will not be the same name as the PK column in the parent I.e. to automate through a macro the manual process of editing a relationship, going to the role name tab and picking an existing column as the child in the relationship.
The addwithrolename function seems to be capable of doing this but I cannot get it to work and the documentation doesn't help
Have you tried changing the AttributeObj.RoleName property after creating the relationship? I don't think it will work if the column name already exists, but might be worth a try.
I don't know whether you solved your issue, but I was also struggling with this. I found the answer when I clicked the "?" button on the browse objects window for Relationships. It brings up a bigger help window which has notes on the PKPairList syntax and rules. The basic format of the PKPairList argument is a string of the format "PKname1,Rolename1;PKName2,Rolename2", etc.. There are some limitations and I've just started experimenting. I can at least get it to create a new relationship, though I think in my case it will not work because I am trying to use an alternate unique index. Oh well. Hopefully it helps you.