There are multiple databases, with transformation of the data between them and the continuous changes made to the data transformation logic has led to the actual system being very different from how it was originally documented. Various business rules can get lost if there is no single version of the truth maintained. The business needs a better picture of the database, the business rules and the transformations in order to initiate changes in the future in an orderly manner. Power Designer is a great tool to bring to light the present state of the enterprise system and show all the business rules as well as dependencies between the various databases.
Using an ODBC connection, a Power Designer can connect to the databases (SQL, Oracle etc) and reverse engineer all the tables, relationships and dependencies into its local workspace. This reverse engineered model is called the Physical data model (PDM). The business does not need to see the technical name or structure of the tables and the various fields or attributes contained in those tables. Hence Power Designer has other models available to depict different levels of detail and functionality. These are the Logical data models (LDM) and the conceptual data models (CDM). The CDM is the graphical representation of the system; it helps you identify the entities important to the business, its attributes and the relationships between them. The LDM is similar to the CDM but has more details with respect to the resolution of referential integrity as well as many relationships. Using the PDM we can generate the Data Definition Language or “DDL” which defines the schema for a database. The business fairly technical user’s not actual end users can look at the CDM and get a better understanding of the overall system. For the actual business users there is another model available in Power Designer called the Data Movement Model. This model provides a global view of movement of information in the organization. You can see which database the data is coming from, how it is transformed (including ETL and data replication) and which database (or excel or flat file output) is it being stored to.
As part of the data discovery process we have to collaborate with the owners of each of the databases and obtain documentation about the data flows in and out of the database. Being an enterprise wide effort and keeping in sync with the concept of showing results to businesses quickly. The approach adopted is to tackle on information flow at a time. It’s relatively easier to try and figure out at one time what is happening to say the item streams instead of trying to figure out what is happening to the item, customer, and vendor all at once. The Power Designer Portal is a quick way to make information available to the businesses. The business’ users can see the data models as we build them and can leave their comments for a particular model.
- LÛCRUM is an information, intelligence and analytics consulting firm comprised of business and technology professionals focused on making data meaningful.
