PowerDesigner

logo powerdesignerIn this chapter the PowerDesigner add-ons for the ExMeX Framework Core are described.

The section Plugins describes all available plugins for PowerDesigner provided by TEDAMOH. 

The ExMeX Plugin - Data Vault supports the data modelers in their task of designing physical data models and provides all needed stereotypes for the generation and automation tasks of the ExMeX Framework Core.

The ExMeX Plugin - Metadata Export extracts all metadata necessary for the ExMeX Framework Core from PowerDesigner. These metadata are the source for the generation of all Load Process Steps (LPS) in the framework.

Scripts (vbs)

How to copy satellites with mappings to a new datasource

Summary Script: CopySatellites.vbs The aim of the automation script “CopySatellites.vbs” is to copy satellites with all columns and specific configurations…

Summary

Script: CopySatellites.vbs

The aim of the automation script “CopySatellites.vbs” is to copy satellites with all columns and specific configurations and create new satellite mappings and integrity load mappings for them connecting to a new data source.

 The script was tested with PowerDesigner Version 16.7.3.0

The script was tested with ExMeX Version 2.13

Input Parameters

Parameter Description
scriptMode

Script has Test and Execution mode:

  • Set variable "T" for Test mode
  • Set variable "E" for Execution mode
copyToSourceSpecificObject

Script can copy satellites, that are referencing a source specific hub

  • set variable to "True" if Satellites are referencing a source specific hub
  • set variable to "False" if not
copyAllSatellitesFromSource

Script can copy all satellites from the datasource at once

  • set variable to "True" all Satellites from the Source should be copied
  • set variable to "False" if not

 filenameTargetModel

The file name of the target model, in which the script should copy the satellites.

Example: "C:\Repo\Models\Core\Production"

 filenameNewSourceModel

The file name of the source model, which is the input source of the new mappings.

Example: "C:\Repo\Models\Stage\ProdLineB"

 filenameOldSourceModel

The file name of the source model, which is the input source of the existing mappings.

Example: C:\Repo\Models\Stage\ProdLineA

 arrayOfSatellites
(only CopySatellitesByList)

An array of satellites the script should copy. Each array Entry consists of three positions:

  1. Code of the old satellite (to be copied)
  2. Source of the new satellite
  3. Boolean variable (“true” or “false) if the source is a view

One example entry for view source might be:
arrayOfSatellites = AddItem(arrayOfSatellites , Array("Production_Item", "V_ProdLineItem", "true"))

 Replace_String_Source

A string the script should replace from the old satellite and the old mappings. This String gets replaces with “Replace_String_Target”

Example: "A"

 Replace_String_Target

A string the script should insert instead of the upper variable “Replace_String_Source”.

Example: "B"

 oldDatasourceCode

The code of the datasource of the existing mappings.

Example: "STAG_PRODLINEA"

 newDatasourceCode

The code of the datasource of the new mappings.

Example: STAG_PRODLINEB

hashkeySuffix

The suffix of the hashkey naming convention

Example: "_HK"

Preparation

At first, open all necessary models in the PowerDesigner that the script should use – these are the target core model and the old and new stage models. Before using the script, you have to define the new data source in the core model.

Make sure that the datasources are named identical in the stage models and core model!

Using the script

Open the script via

     Tools --> Execute Commands --> Edit/Run Script

and Click on the folder Button (or CTRL + O) to open the vbs file or copy the script into the editor.

Before executing the script, you have to define the input parameters at the top of the script.

Now you can execute the script. If the script runs into an error state, PowerDesigner will show you an error message and in which line the error occurred. There are also some info boxes added to give you a more precise help where you may search for the mistake. If an error occurs, PowerDesigner should roll back all changes made by the script.

Sometimes if your model or your satellites are very big, the PowerDesigner Application seems to be bugged and stops working (Not responding) – then wait until success or error state. The Application is still working!

The script will show some output for your information in the bottom script message tab. It also outputs a WARNING if there is a column in the old stage table, which is not in the new stage table. The script then creates the mapping, but you have to verify this column mapping manually.

Remember: The automation script only copies things and renames them – If the objects to copy are already wrong, then wrong things are copied! Make sure you always adhere to your naming convention to have copied satellites that follow also to the naming convention.

Final manual steps

After executing the script in execution mode, the script created the new satellites with their corresponding mappings. Of course, it is always a good task to validate the results.

A last task is to add the new generated satellite tables to the desired diagram. The script only creates the tables in the model, but doesn´t add it to a diagram.

Posted 2 years agoby Stephan.Volkmann

How to check for missing and incorrect mappings?

Summary The aim of the automation script “CheckMappings.vbs” is to search for missing or incomplete mappings in the current active…

Summary

The aim of the automation script “CheckMappings.vbs” is to search for missing or incomplete mappings in the current active model in PowerDesigner.

Input Parameters

hashKeySuffix: The suffix for your hashkey naming convention, for example *_HK

Preparation

Only preparation is to open the model you want to check in PowerDesigner. Make sure it is the current active model (any diagram of the model is the active diagram window).

Using the script

Open the script via

     Tools --> Execute Commands --> Edit/Run Script

and Click on the folder Button (or CTRL + O) to open the vbs file.

Now you can execute the script. If the script runs into an error state, PowerDesigner will show you an error message and in which line the error occurred. If an error occurs, PowerDesigner should roll back all changes made by the script.

The script will show some output for your information in the bottom script message tab.

Following things are checked

  • Warnings: missing table mapping, missing column mapping
  • Errors: missing MappedTo in table mapping, missing MappedTo in column mapping

The script ignores following fields: "HashDiff" ,  "LoadEndDate" , "LoadDate" , "AuditId" , "AUDIT_ID" , "EntityId"

For a Hub the script checks, if there is no mapping to a column with code %_HK.

Final manual steps

After executing, the script outputs the warnings and errors in the output log. The user then can manually verify these messages and correct the model.

Posted 2 years agoby Stephan.Volkmann

Tipps, Tricks And Fun

How to design linked but independent data models?

What is actually the background to this question? In some data solution projects, the BI teams would like to work…

What is actually the background to this question? In some data solution projects, the BI teams would like to work on "the data model" with more than one data modeller. One possibility is to create several, separate data models for this purpose and thus be independent of other data modellers with the particular data model. So is the idea.

But every now and then you need a hub (in the case of Data Vault) or a table from another model. 

Most modellers instinctively use a so-called Shortcut for this.

The consequence of this, however, is that the necessary Integrity Load Mapping (ILM, which the ExMeX Framework Core requires) for the source table to be connected to the hub or table must be done in the respective "home data model" of the hub or table.

To do this, the modeller must keep the other model open in order to connect the source table there as well and create the ILM. In this way, he also blocks this data model for the activities of other data modellers.

The solution? Replica

These work similarly to Shortcuts, but give the data modeller the ability to model the ILM within their current model. The “home data model” of the hub or table is no longer needed.

TIP: With a replica it also works if the original of the replica is actually in a different physical database or schema than the tables of the current data model!

Great, isn't it?

Ok, what do you have to do for that?

Well, here's a step-by-step guide:

First: Drag and Drop - Right-click on the object that you want to reuse in the Browser or a diagram, drag and drop it into the diagram (or onto a model or package in the Browser) where you want to create the replica, release the right mouse button, and select Replicate Here (Quote from the PowerDesigner documentation).  More background information on this can be found in the PowerDesigner documentation (State 2021-03-11).

Create Replication of Table by Drag And DropCreate Replication of Table by Drag And Drop

Second: Now you have to do some steps to not mess up things in the meta data:

  • Open table properties (e.g. double-click on hub) of your Replica.
  • Now you have to decouple the "Generate attribute" from the replication: Go to Tab Version Info -> Replication Properties -> Tab Attributes and a) deselect checkbox Generation and b) select checkbox Owner.

    Table Properties - Version Info TabTable Properties - Version Info Tab

    Replication PropertiesReplication Properties
  • Go to Tab General and deselect checkbox Generation. Why? The table or hub has already been generated as a DDL with the "home data model" and the corresponding metadata has been created.

    Table Properties General TabTable Properties General Tab

Third: Business as usual:

  • Create Link to Hub or add other objects to your Replica.
  • Map ILM from your given source table to Replica (Table / Hub)
  • Generate meta data - at least version 1.7.0 of ExMeX Plugin - Metadata Export is needed!
  • Install meta data

That’s it. No need to open any other referenced data model again. And the Replica will synchronize all changes in the original object.

By the way: with the latest plugins provided by TEDAMOH you will recognize which objects are Replicas!

Data Vault Data Model With ReplicaData Vault Data Model With Replica

Posted 1 year agoby Dirk.Lerner