Importing Column Documentation from a CSV into a Sybase PowerDesigner Data Model

(Update – I have a more efficient version of this script posted here)
I’m a big fan of data modeling tools. I used to chafe against them as so often it was easier for me to think in code. But the advantages of using tools like E/R Studio, Toad Data Modeler and Sybase PowerDesigner became apparent quickly. Being able to visually look at your data model through diagrams and automatically generated reports allowed you to get a general sense of what was going on in the database as a whole. And of course, it was much easier to document the database through the tool as you created or modified your tables with the same tool. And many of these tools have built in checks and warnings that can be very useful.

Still, certain repetitive tasks are easier to do through scripts. Or even through scripts generated by scripts. Right now I’m working with PowerDesigner and it has some built in scripting capabilities but there were some aspects of it that I just couldn’t get used to. And I already knew PowerShell pretty well so I decided to try and see if I could use PowerShell to do some grunt work.

Here’s the scenario. A non-technical subject area expert is asked to review the documentation of one of the key entities in your model. You are asked to give them a spreadsheet with all the attributes/columns as well as the description of each columns. The subject area expert makes some improvements in the spreadsheet and sends it back.

Now you need to import the spreadsheet and update the data model with the revised documentation.

For the sake of this example, we’ll assume that you want to store the column-level documentation in the Comment field of the column. You might want to put it in the annotations or somewhere else depending on your situation.

$PathToModel holds the path to the data model file.

$pathToDocumentation is the path to the CSV file you are importing. It should have a column called Column which has the column names and Description which has the new comment you want in the data model.

$tableToUpdate should have the name of the table in the model you want to update.

Obviously, this isn’t a production utility. It’s just a quick and dirty script to give you ideas for building more useful, flexible tools. I will probably use this as a basis for a PowerDesigner library if I end up doing a lot of PowerDesigner work.

Here is the code:


Function GetComment($table, $column){

$MyModel.Children | where-object{$_.Name -eq $table} | foreach-object{$_.Columns} | where-object{$_.Name -eq $column} | foreach-object{$_.Comment}

}

Function SetComment($table, $column, $comment){

$MyModel.Children | where-object{$_.Name -eq $table} | foreach-object{$_.Columns} | where-object{$_.Name -eq $column} | foreach-object{

$_.Comment = $comment

}

}

$PathToModel = "C:\my_model.pdm"

$pathToDocumentation = "C:\updated_documentation.csv"

$PDApp = new-object -comobject PowerDesigner.Application

$tableToUpdate = "ps_import_test"

$MyModel = $PDApp.OpenModel($PathToModel)

$documentaion = Import-Csv($pathToDocumentation)

Foreach($comment_entry in $documentaion){

SetComment -table $tableToUpdate -column $comment_entry.Column -comment $comment_entry.Description

}

$MyModel.Save()

$MyModel.Close()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($PDApp)

Remove-Variable PDApp

The big problem with this code is that for each update you may end up iterating over the whole model, so it might be slow for large models. It looks like there are methods in the COM interface that might avoid this but I had no luck calling them from PowerShell. Anyone know the PowerDesigner object model well enough to help with this?

Anyone else have some good automation tips you’d like to share?

Advertisements
This entry was posted in Automation, Data Modeling, PowerShell, Sybase PowerDesigner, Tools. Bookmark the permalink.

4 Responses to Importing Column Documentation from a CSV into a Sybase PowerDesigner Data Model

  1. Pingback: Broaden your vision | SELECT STUFF FROM SQL

  2. Nick says:

    Fantastic script. I was wondering if you could have something of this kind, for importing relationships between columns, for example a foreign key of field “Residence” in file “Customer” to the primary key of table “Countries” etc.
    Thanks for sharing !

    Like

    • Richard says:

      The object model is pretty comprehensive. I’m using ERStudio at my current job and don’t have access to PowerDesigner at the moment so I can’t be sure, but my guess is that you could make a few minor changes and have something up and running quickly.

      PowerShell is pretty awesome, isn’t it?

      Like

  3. Pingback: Automating ER/Studio with C# code. | SELECT STUFF FROM SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s