A physical data model (PDM) helps you to analyze the tables, views, and other objects in a database, including multidimensional objects necessary for data warehousing. A PDM is more concrete than a conceptual (CDM) or logical (LDM) data model. You can model, reverse-engineer, and generate for all the most popular DBMSs.
Depending on the type of database you want to design, you will use different types of diagrams in the PDM:
-
Operational PDM - You use PDM to design the structure of an operational database. Usually, in data modeling, the physical analysis follows the conceptual and/or logical analysis, and addresses the details of the actual physical implementation of data in a database, to suit your performance and physical constraints.
-
Business intelligence PDM - You can use a PDM to design the structure of a data environment, which consists of:
-
Data warehouse or data mart database – are populated with data transferred from operational databases, and gather together all the information that may be needed in an OLAP database, where queries for business analysis and decision making are performed. The data warehouse database gathers all the data manipulated in a company for example, whereas the data mart focuses on smaller entities in the company.
You use physical diagrams to design a data warehouse or data mart database. Since these databases usually contain very large amounts of data for storage, you do not need to design them for performance. You may assign types (fact and dimension) to the database tables to have a preview of the multidimensional structure in an OLAP database.
-
A multidimensional OLAP database - which is generally populated with data that has first been aggregated in a data warehouse or data mart (though sometimes it is transferred directly from operational databases), and in which information is organized to facilitate queries performed by different tools. Business analysts use OLAP databases to send queries and retrieve business information from the different dimensions existing in the database.
You use PDM multidimensional diagrams to design the different dimensions and cubes within the OLAP database.
-
To create Physical Diagrams, see Building Physical Diagrams. To create Multidimensional Diagrams, see Building Multidimensional Diagrams.