Creating relationships with roles

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


No Data
  • 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)