What is Data Modelling?

Understanding Data Modeling

Data modeling is a process used to define and analyze the data requirements needed to support the business processes within the scope of corresponding information systems in organizations. It involves the creation of visual representations of data (data models) to communicate connections between data points and structures. The goal is to use these models to guide the development of databases and ensure they are well-structured, efficient, and able to handle the business operations they are designed to support.

Different Types of Data Models

1. Conceptual Data Models: High-level, business-focused models that show how data is connected within the major business areas.
2. Logical Data Models: More detailed, showing the specific entities, attributes, and relationships between entities in the system.
3. Physical Data Models: The most detailed models, showing how data is stored and accessed in the physical storage media.

Dimensional Data Modelling – Star Schema

Star Schema is a popular data modeling technique used in dimensional data models commonly used in data warehouses.

It is named for its resemblance to a star, with a central fact table surrounded by dimension tables.

• Fact Table: The center of the star schema, it contains quantitative data for analysis and is connected to various dimension tables.
• Dimension Tables: Surround the fact table, containing descriptive attributes related to the facts. These tables are used to filter, group, or label the facts.

Fact Tables

Fact tables are the primary table in a star schema of a data warehouse. They hold quantitative information for analysis and reporting and are often populated with numeric measures that are the results of transactions.

• Characteristics of Fact Tables:
• Contain keys to dimension tables.
• Contain numeric facts about a business.
• Usually have a large number of rows.
• Example of a Fact Table:
• Sales Fact Table:
  • Dimensions: Date, Product, Store, Customer
  • Facts: Quantity Sold, Sales Amount, Tax Amount

Dimension Tables

Dimension tables store the context for facts and measures in the fact table. They are descriptive and contain attributes that are textual fields.

• Characteristics of Dimension Tables:
• Contain descriptive information.
• Textual fields are common (e.g., names, descriptions).
• Have a primary key that is a unique identifier for each dimension record.
• Examples of Dimension Tables:
• Date Dimension Table: May contain date, day, month, quarter, year, holiday, weekend.
• Customer Dimension Table: May contain customer ID, name, address, phone number.

Putting It All Together

In a star schema, the fact table sits in the center with its keys linking to multiple dimension tables. The star schema is advantageous due to its simplicity and easy understanding. It allows for efficient querying and reporting as the structure is straightforward and queries often involve only joining the fact table with the relevant dimensions.

Example of Star Schema Implementation

Suppose a business wants to analyze their sales data. They could create a star schema with:

• Fact Table: Sales Data
• Contains records of sales transactions.
• Fields might include DateKey, ProductKey, StoreKey, CustomerKey, QuantitySold, and SalesAmount.
• Dimension Tables:
• Date Dimension: Contains date, day of week, month, quarter, year.
• Product Dimension: Contains product ID, name, category, price.
• Store Dimension: Contains store ID, location, type, size.
• Customer Dimension: Contains customer ID, name, demographic details.

In this schema, analysts can easily query the database to find out things like total sales per month, average sales per store, or sales trends over time. The star schema’s simplicity and effectiveness make it a popular choice for many business intelligence applications.

Relational Data Modelling

Relational data modeling is a technique used to structure data in a way that recognizes and leverages the relationships between different sets of data. It’s the foundation of a relational database, which is a collection of data items with pre-defined relationships between them. These items are organized as a set of tables with columns and rows. Here are the key concepts and components involved in relational data modeling:

1. Tables (Relations): In a relational database, tables represent entities or objects. Each table typically corresponds to one particular kind of entity (like customers, products, or orders). Each row in the table represents an instance of that entity, and each column represents values attributed to that instance.
2. Columns (Attributes): Columns in a table represent the attributes of the entity. For example, a customer table might include columns for customer ID, name, address, and phone number.
3. Rows (Tuples or Records): Each row in a table represents a specific instance of an entity, containing a unique dataset. For instance, each row in the customer table represents a single customer.
4. Primary Key: A primary key is a unique identifier for each row in a table. No two rows in a table can have the same value for the primary key column. It is essential for ensuring that each record can be uniquely identified.
5. Foreign Key: A foreign key is a column or a set of columns in one table that uniquely identifies a row of another table or the same table in case of self-referencing. It’s a way to enforce referential integrity within your SQL database.
6. Relationships: Relationships are associations between tables. The most common types are:
   • One-to-One: A single row in table A is related to a single row in table B.
   • One-to-Many: A single row in table A may be related to many rows in table B.
   • Many-to-Many: Many rows in table A may relate to many rows in table B. This typically requires a junction table.
7. Normalization: This is a process used to minimize redundancy and dependency by organizing fields and tables in databases. It involves dividing a database into two or more tables and defining relationships between the tables. The goal is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
8. ER Diagrams (Entity-Relationship Diagrams): These are used to visually represent the layout of databases. ER Diagrams show entities in a database and relationships between tables within that database. It’s a helpful tool for designing a relational database and for understanding the relationships between the tables.

Relational data modeling is fundamental in creating a well-structured and functional relational database. It involves careful planning and understanding of the data needs of the business or application to ensure that the database is optimized, both in terms of storage and query performance.

Our final thoughts on why Data Modelling is the foundation for great AI success

Data modeling, specifically using the star schema, is a critical process in managing and analyzing vast amounts of data. It helps organizations make sense of their data and derive meaningful insights from it. By understanding and implementing star schemas, businesses can significantly enhance their data warehousing and analytical capabilities, leading to more informed decision-making and strategies.