Finding the right URL isn’t always easy, even with Google, so we’ve registered a couple of useful domains.
Finding the right URL isn’t always easy, even with Google, so we’ve registered a couple of useful domains.
On Twitter earlier today, I exchanged tweets with Jane Roberts (@redjane) about finding myself in the phone book. Well, now I’ve just found myself on YouTube!
I was actually curious about videos of a different George McGeachie, a retired footballer who’s a tad older than I am, and searched for my name in YouTube. I didn’t find what I was looking for, but I did find a video that had previously been published on mypowerdesigner.com, which SAP closed down years ago; it was recorded at a SAP conference in the Venetian Hotel in Las Vegas.
I thought it had disappeared without trace, but there it is. It’s me (of course), talking about my soon-to-be-published book “Data Modeling Made Simple with PowerDesigner” – this was before the editor suggested drastic re-ordering of the chapters, so my chapter references are wrong, but hey, I’ve re-discovered myself!
At the end of March, my friends at Sandhill Consultants and Axis Software Designs ran a great webinar which was based on SAP PowerDesigner, but was also addressed to a more general audience, who would be interested in various aspects of data management, such as data quality, data governance, and metadata management. Largely, it was about converting “What to do” (our wonderful data management strategy and intentions) into “How to do it” (governance, tools, and best practices). One key topic is the need to be able to trust our metadata (otherwise it could be useless).
Here’s the link to the webcast – https://www.youtube.com/watch?v=RRrj43tkOFI.
For quite a while now, Marcie Barkin Goodwin (from Axis Software Designs) has been helping organisations hit the ground running, with a managed Modelling Environment (known as Enterprise Modelling SOS), which can support the top three data modelling tools (erwin, ER/Studio and PowerDesigner), and includes
The whole thing revolves around the strategy of reusing modelling artefacts – which is vital for successful modelling within the enterprise. The modelling tool that they use in this webcast to illustrate the strategy is SAP PowerDesigner, a tool with a long history of providing comprehensive traceability within and between models of various types, not just data models. A key tool feature mentioned is the “Change List” feature, which allows users to check model changes into the Repository to be reviewed – until those changes are approved, the ‘real’ repository model is not updated. This is a much better method than emailing models to your friends, or asking them to have multiple versions of the same model on their workstation at the same time.
For reuse, the PowerDesigner Library is essential – it pushes reusable resources out to users when they connect to repository. These could be tool-specific resources (such as Report Templates), models, or model templates.
Intrigued? Take a look at another recent Sandhill webcast – “The Convergence of Data Modeling and Data Governance”
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
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.
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:
An entity summary:
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:
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.
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.
Thank you to everybody who has watched the videos I published on YouTube last month, and for the ‘Likes’. I have had one detailed response to those videos, which appears to arise from a combination of misunderstanding the purpose of the videos, and a wish to promote another modelling process and the accompanying tool. I have no problem with the latter, of course, I welcome such discussions 😊.
The comments came from Charles Meyer Richter, and can be found here on Linked IN. Charles originally made his point in response to the video being shared on Linked In by Oliver Cramer (thanks, Oliver), and I replied to that with a brief comment:
The purpose of this blog post is to provide a longer answer to his longer comment, though I shan’t address every point Charles makes individually. He addresses several key areas in his comments:
Unfortunately, Charles viewed my chain of three models as a methodology statement which it is not.
I’ve just listened again to what I said in the first video – “in PowerDesigner a Conceptual Data Model is something that can look a lot like a Logical Data Model, with a couple of differences”. This is true, the key differences are that the PowerDesigner Conceptual Data Model doesn’t migrate attributes along relationships, and every attribute is supported by an underlying Data Item. Data Items can be used to provide a set of atomic data definitions, which are sometimes known as Data Elements. You don’t have to actually reference Data Items in Entities if you don’t want to – that’s the thing I like about PowerDesigner, the fact that, by varying model options, and by building simple customisations, you can tailor the tool to support your own Data Governance and Data Modelling approach.
Charles uses the examples of drawing entities, laying out diagrams, linking attributes to business Rules, and linking Data Items to CDM Entities. He also asks if PowerDesigner can support 9 levels entities-within-entities. I remember a Logical Data Model with a lot of sub-type levels in the past, but I don’t think it went down 9 levels. Anyway, as a quick test, I created a CDM in Barker notation with 13 levels:
In “Entity-Relationship” notation, such a hierarchy is even bigger – see the diagram on the right.
If I wanted to build a taxonomy using PowerDesigner, I might use a combination of packages and entities to build it, possibly in a CDM, or perhaps as UML Classes in the Object-oriented Model.
Going back to how long things can take – I intended to show a variety of editing and object creation techniques in the tool, and I probably didn’t include all of them. Using object lists to create and edit objects can be a real time-saver, as can editing using Dependency Matrices, and importing from Excel.
Charles envisaged a real-world data model with 185 entities in it, using that to scope a ‘release’ that required 93 of them. In the video, I didn’t show how to filter a list of entities, drag them onto a diagram, and then use that diagram as the scope of a new model that I’m generating, again due to lack of time.
I could have demonstrated the diagram auto-layout capabilities, but there just wasn’t time to do that. The same is true for changing the details included on entity symbols – PowerDesigner gives me more control over symbol content than any other tool I’ve used.
Charles proposes something very similar to what I showed, which I’ve drawn up in a PowerDesigner Free Model.
I like what he suggests – I’ve certainly seen a double-layer of physical data models like that in PowerDesigner. The Knowledge Model includes Concepts, Goals, Benefits and Values, KPIs, Business Functions, and the ability to produce a first-cut cost-benefit report.
In my videos, I only mentioned three types of PowerDesigner model, out of a total of nine. The PowerDesigner “Enterprise Architecture” Model provides most of the capabilities that Charles mentions, though some customisation would be needed.
In his final point, Charles says (I agree with this) that you need a powerful set of tools to “store and manipulate the 16 goals, something like 19 performance indicators, the 185 knowledge classes, 30 attributes and 24 conceptual classes that will create the 24 logical and physical database tables”. He’s correct, and PowerDesigner is one of several tools to provide that capability; the difference is that it isn’t a “set of tools”, it’s a single tool.