5.2.1 Physical Design Process

5.2.1 Physical Design Process

We all have the concept of dimensional modeling technique. Logical model can be built from dimensional modeling technique. Physical model can be derived from logical model. Computing platform, the database software, hardware, and any third-party tools need to be considered while designing physical model.

Logical Data Model: It comprises of all entities and their relationships. Each entity includes their attributes and primary keys. Foreign keys are specified in this level. It is the base for creating physical data model. Figure 5.2.1 represents logical data model.

Physical Data Model: It signifies database-specific implementation of a logical data model. This model alters entities to tables. It specifies all tables and column names with their data types. Figure 5.2.2 represents the physical data model.

i) Physical Design Steps

Figure 5.2.3
Thus it will be feasible for retrieving data in a single query. For example, Suppose sales is measured very often by product category with the combination of customer age to analyse the variety of customer buying a specific product. Here, data can be clustered in the table using the column order (product category and customer age) so that the column which has the information about this detail can be referred in a single command rather than duplicating the number of queries. Figure 5.2.7: Cluster Table

Prepare Indexing Strategy: This is an important factor while designing physical model. Indexing for each table is required for the performance improvement of data warehouse.

Figure 5.2.8: Index Table

Assign Storage Structure: Storage structure plan includes the planning and assigning of physical storage for data warehouse tables, storage for temporary data extraction, staging area and storage for front-end