Transformation – Creating a Reference Table

I’m going to ignore events for a post due to popular demand, or at least one polite request and talk briefly about transformations.  This isn’t intended to be a detailed discussion of all the wonderful things transforms can do but a quick demo.  A few weeks ago I posted a simple demonstration that automated creating a reference table.  The code I used for that post originally started as a transform, so I’m going to demonstrate how I create lookup tables when generating a PDM from a CDM.

First, the basics.  In order to use transforms, you need 2 things.  An actual transformation, and a transformation profile.  Transformations fall into two categories.  Pre-Generation and Post-Generation.  The documentation has a lot of information on both, but the important thing to remember is that pre-generation transformations are defined on the source model and modifies the source model before the transformation process starts.  Post-generation transformations are defined on the target model and modify the target model after it’s created.

So, for our example, we’ll create a pre-generation transform that will create a generic lookup table.  Our code will be a little different from the event code earlier because we’re working with a CDM instead of a PDM (table becomes entity, etc.).  We’re going to key our transform off the column domain.  So instead of cluttering up your conceptual data model with a lot of lookup tables, we’ll simply define them as being in the “Lookup” domain.

First step, add a transformation to the entity attributes  metaclass and add some code.  I won’t elaborate on the code since we’ve gone through this before.  I’ll call my transform GenerateReferenceTable.

Now the code.  Note that I can define the CDM datatype as “Variable Character” and let PowerDesigner take care of transforming that into the appropriate datatype for the target RDBMS.

Sub %Transformation%(obj, trfm)
   ' Implement your transformation on  here

   dim domainObj
   dim domainName
   dim newAttr
   dim newRefTable
   dim baseTable

   dim newRef

   set domainObj = obj.Domain

   if domainObj is nothing then
      exit sub
   end if

   if domainObj.Name = "Lookup" then

   domainName = obj.name
   output domainName

   ' Step 1 - Create a standard Reference Table

   ' Step 1.5 - Check to see if the table already exists (hey it happens)
   ' create it if you need it, otherwise - reuse it

   set newRefTable = getEntity(domainName)

   if newRefTable is nothing then

          set newRefTable = activeModel.Entities.CreateNew()

         with newRefTable
            .Name = domainName
            .SetNameAndCode .Name, "", true
         	.Comment	= obj.Comment
         End With

         ' Step 2 - Perhaps some columns would be nice?

         set newAttr = newRefTable.Attributes.CreateNew()
         with newAttr
            .Name = domainName & " Key"
            .SetNameAndCode .Name, "", true
            .DataType = "Integer"
            .PrimaryIdentifier = true
         End With

         set newAttr = newRefTable.Attributes.CreateNew()
         with newAttr
            .Name = domainName & " Code"
            .SetNameAndCode .Name, "", true
            .DataType = "Variable Characters(40)"
            .PrimaryIdentifier = false
            .Mandatory = false
         End With

         set newAttr = newRefTable.Attributes.CreateNew()
         with newAttr
            .Name = domainName & " Name"
            .SetNameAndCode .Name, "", true
            .DataType = "Variable Characters(50)"
            .PrimaryIdentifier = false
            .Mandatory = false
         End With

         set newAttr = newRefTable.Attributes.CreateNew()
         with newAttr
            .Name = domainName & " Description"
            .SetNameAndCode .Name, "", true
            .DataType = "Variable Characters(100)"
            .PrimaryIdentifier = false
            .Mandatory = false
         End With

      end if

      ' Step 3 - Create a relationship between the new lookup table and the table containing the original column

      set baseTable = obj.Parent

      set newRef = activeModel.Relationships.CreateNew()

      with newRef
         .object1 = newRefTable
         .object2 = baseTable
         'Set the optionality using the property from the original column
         .Entity2ToEntity1RoleMandatory = obj.Mandatory
      end with

      ' Step 4 - Remove the original column from the base table

      obj.Delete

      ' Step 5 - Have a beer
   end if
End Sub

Now we have a transform, we need a profile to include it in.  There are only a few options to choose from.  When the model type is Physical Data Model, you can select a Family and Sub-Family which correspond to the target RDBMS and sub-family in the target model.   This tells PowerDesigner which transformations to allow/deny (show/hide) when you’re actually generating a data model.  I’m using Oracle here (mainly to make it easy to find the right box in the picture below), although you can leave them blank and have them apply universally.  I’ve named my profile toPDM.

Once you’ve got a transformation profile, you need to tell it which transformations are in the profile, we’ve only created one, so this should be pretty easy.  Click on the Add Transformations box and check the box next to GenerateReferenceTables.  Click OK.  Your profile should look like the sample below.

To use it you just need to create an entity and assign an attribute in it the Lookup domain.  Each attribute tagged as a lookup should result in a reference table and a foreign key back to the original table.  If multiple attributes have the same name, they should point to the same lookup table.  This script will NOT add the additional tables to the diagram, so you’ll need to add them manually and choose complete links to finish it up.

The only thing left is to generate the PDM.  You won’t need see the transformations by default but you’re just a few clicks away.  On the PDM Generation Options dialog, you’ll need to go to the detail tab and click on the “Enable Transformations” box.

Once that’s done, you’ll have additional tabs for pre-generation and post-generation transformations.  Go to the pre-generation tab and check the box next to our toPDM profile.  Notice that if you have multiple transformations in a single profile, they can be toggled on and off independently.

Now, set the remainder of your generation options as you would normally (that’s a topic for another day) and choose OK.  Next post we’ll be back on events – assuming no more polite requests arrive.