If you attended my DAMA UK webinar today, thank you. If you didn’t, where were you? <grin>
The webinar description was :
“To be successful at Data Architecture, organisations have always needed to understand what data they have, why they have it, how they use it, where it is, how it got there, and what happened to it along the way – forming a ‘pipeline’ of information. Very often, this pipeline is managed and documented using a mish-mash of tools and methodologies, often resulting in conflicting and contradictory information, making it difficult for Data Governance to be effective.
In this webcast, George McGeachie will demonstrate the key features of SAP PowerDesigner that support the pipeline. A set of requirements and business rules, and an existing Conceptual Data Model, will be used to create Logical and Physical Data Models. This will include the generation of JSON structures from both Logical and Physical Data Models.
Some of what you will see is not out-of-the-box – it has been built using PowerDesigner’s powerful customisation features.”
The webinar was recorded – take a look – https://www.brighttalk.com/webcast/12405/311023
Well, I think I demonstrated that pipeline okay, though I did make one slight mess-up on the JSON PDM (see below). That’s the way it goes with one-off demos, the old memory plays you tricks. Here’s a quick reminder of the steps I followed
- Import Requirements from a Mind Map – Via OPML and a Word document – then create a Conceptual Data Model (CDM) from the Requirements. The OPML import was built by me using the PowerDesigner XML Import feature, and the Word import is part of the product.
- Merge this CDM into the existing Hotel Loyalty CDM
Normally I would be more selective about the changes that I carry over from one model to another, today I carried over every change that didn’t delete something in the target model.
My objective was to show that the links back to the original requirements were still maintained. Here is the link from one the Hotel Loyalty CDM Entities, back to the original requirement:
I also showed the additional Entity properties that I added via a model extension:
This included a link to a new type of object I’d added to the model – the Data Steward. Here we can see the list of entities that one of the Data stewards is responsible for:
I didn’t show you this, but the model also contains a Dependency Matrix, which can be used to edit the links between entities and data stewards. It can also be used for simple validation, such as showing only those entities that do not yet have a Data Steward.
There is also a matrix that allows you to edit the links between Data Items and Domains.
- Generate LDM from the CDM
I generated a Logical Data Model (LDM) from the CDM, making sure not to run a model check, as errors would prevent the new model being generated. I didn’t show you the model options, where the option to allow many-to-many 
relationships was disabled.
There is a many-to-many relationship in the CDM, between Facility and Reservation – I’ve called this “Facility Reservation”:
In the LDM, an entity was automatically created to replace the relationship:
It’s worth mentioning here that I can do this at any time – any relationship can be converted into an entity, preserving the overall optionalities and cardinalities.
The LDM has a couple of model extensions attached – one of them generates JSON and CSV, and the other generates facts about the model as text. Both involved writing some Generation Template Language, which allows me to mix boilerplate text and information from the model, in much the same way as when we generate Data Definition Language (as SQL) from a Physical Data Model.
Here’s a JSON sample:
Some CSV:
An entity summary:
- Generate JSON PDM from LDM, then generate SQL Server PDM from LDM
I could have used a subset of the LDM as my source for generating the two PDMs, but I chose to generate the whole model.
In the LDM, I could now see (though I didn’t show this at the time) the links from the LDM to the objects in the PDM. Here’s an example what happened to one of the entities:
I didn’t look at the two PDMs in any detail, as the purpose of the webinar was to show the pipeline of artefacts and models. I did look at the Preview tab for one of the tables in the JSON model, which was where I made my mistake. A JSON model represents a complete JSON structure, possibly a message, so I should have looked at the Preview tab on the model. Here it is:
- Use the Mapping editor to create mappings from the JSON model to the SQL Server model
In the interest of saving time, I allowed PowerDesigner to populate the default mappings, which mapped everything in each model to the equivalent in the other model.
- Load the JSON-SQL mappings into a Data Movement Model
The Data Movement Model already contains some detailed data movements for MDM and a Data Warehouse:
I used the built-in wizard to import the mappings from the SQL Server PDM, and create a single transformation task in the model. This links back to the tables and columns in the two PDMs, and allows me to describe the transformation actions in detail:
I also showed an alternative view of the Data Warehouse Data Architecture, in an Enterprise Architecture Model, which is linked to the Data Movement Model. As an architecture model, it doesn’t contain the level of detail that the Data Movement Model does; what it adds is the ability to connect this data architecture to business functions, servers, locations, and other enterprise architecture artefacts. The Enterprise Architecture model also links to the Physical Data Models, ensuring traceability at the model level.
Finally, I created a Project, and added all the models to it, so we can visualise the links between models. each symbol on this diagram represents one of the models. I can open a model by double-clicking the symbol.
One thing left, which I’m sure I showed somewhere near the end. 
I showed the Impact and Lineage analysis for one of the tables in the JSON PDM. This followed all the links from object to object, to the entity it was generated from, to the source-to-target mappings, to the mappings in the Data Movement Model, and kept on going. For example, we can see that the table was generated from an entity in the LDM, and the LDM entity was generated from a relationship in the CDM (remember the many-to-many relationship we saw earlier), which is connected to the Facility and Reservation entities in the CDM. I haven’t expanded far enough in the diagram above to show the link to one of the requirements we started with, but it’s there.
I clicked on the ‘Generate Diagram’ button, which created an Impact Analysis Model from the above analysis. As this is a model, I could save it for future reference, and refresh the contents in the future. In this diagram, changes to the objects on the left could affect the table. each of the symbols is a link to the actual object, so I can access the CDM and LDM entities, the Data Transformation, and the related tables.
Again, thanks for attending, or reading this far if you didn’t attend the webinar. Please use the ‘Contact me’ option on the blog to send me a message, or find me on Twitter , Xing or Linked In.