Gaining an Overview on Relations

Objective

After completing this lesson, you will be able to differentiate between Star Schema and Entity Relation Schema.

The Star Schema Overview

Star Schema in Data Warehousing and Business Intelligence

In this lesson, we explore the star schema within the context of data warehousing and business intelligence. Understanding its usage and benefits help you appreciate why it is favored for certain types of data operations and queries.

Star Schema Overview

Design Philosophy: The star schema is primarily employed in data warehousing and business intelligence environments. It is designed to facilitate heavy operations and analytical queries, categorizing it as an Online Analytical Processing (OLAP) system.

Key Features:

OLAP System Usage: The star schema is particularly useful in environments such as SAP BW (Business Warehouse) and other data spheres that require robust analytical processing capabilities.
Fact Table-Centric Design: At the core of the star schema, is the central fact table, which contains quantitative data (for example, sales amount, total revenue). This table is linked to multiple dimension tables through primary key-foreign key relationships.
Denormalized Data: Dimension tables in a star schema contain denormalized data. This means they have redundancies, which are purposely introduced to optimize query performance.
Primary Key-Foreign Key Relationships: These relationships connect dimension tables to the fact table, simplifying the data retrieval process and making the schema easy to navigate and understand.

Advantages:

Simplified Queries: The design of the star schema allows for simplified queries, making it easier for users to extract and analyze data without requiring deep technical knowledge.
User-Friendly Navigation: Due to its straightforward structure, navigating through the star schema is intuitive, which enhances user experience in data analysis tasks.
Practical Insight: Consider a scenario where a retail company must generate extensive sales reports. Using a star schema, the central fact table holds sales transactions data, and dimension tables could include data related to time, products, customers, and store locations. This setup allows analysts to easily query and generate reports based on different dimensions such as sales by product, sales by region, or sales over time, thus enabling insightful business intelligence.

The Entity Relationship Schema Overview

ER Schema Overview

Design Philosophy: An ER Schema is foundational for Online Transactional Processing (OLTP) systems. It focuses on structuring data in a way that ensures integrity and minimizes redundancy through normalization.

Key Features

Transactional Database: ER Schemas are ideal for databases that handle day-to-day business transactions.
ER Diagram Representation: The schema is often depicted using an Entity Relationship Diagram (ERD), visualizing entities, attributes, and relationships.
Real-world Entities: Entities represent real-world objects such as customers, products, and employees.
Relationships: Defines how these entities interact with each other (for example, a customer purchasing a product).

Details and Examples

Entities and Relationships: Entities in an ER Schema correspond to individual objects in the real world. Each entity is represented by a table.
Customer: Represents individual customers, with attributes such as CustomerID, Name, and Contact Details.
Products: Represents items available for purchase, with attributes like ProductID, ProductName, and Price.
Relationships: Entities relate to one another in defined ways, such as one-to-one, one-to-many, or many-to-many relationships.
Customer-Product Relationship: A customer may purchase multiple products, illustrating a one-to-many relationship.
Attributes in Relationships: An entity may have composite attributes (for example, full names split into FirstName and LastName) and multivalued attributes (for example, multiple phone numbers).
Composite and Derived Attributes: Attributes can be simple, composite, or derived.
Simple Attributes: Directly stored data values (for example, Age).
Composite Attributes: Structured attributes that can be subdivided (for example, FullName divided into FirstName and LastName).
Derived Attributes: Attributes calculated from other data (for example, Age derived from DateOfBirth).
Normalization: ER Schemas are normalized to avoid redundancy and maintain data integrity. Tables are designed to minimize duplicate data and ensure that dependencies are logical.

Tips and Tricks for Effective Entity Relationship (ER) Modeling

Designing an Entity Relationship (ER) model involves a series of well-defined steps to ensure that the database structure is logical, efficient, and capable of handling the required transactions. Here are essential tips and tricks to help you create robust ER models.

Entity Identification and Attribute Definition

Identify Entities: Start by identifying the entities that represent real-world objects or concepts relevant to the system you are designing. These entities will form the foundation of your database.
Example: If designing a retail database, entities might include Customers, Products, Orders, and Employees.
Ensure Distinction: Make sure each entity is distinct and represents a unique aspect of the business domain. Avoid overlapping definitions to maintain clarity.
Define Attributes: Once you have identified the entities, define their relevant attributes. Ensure each attribute is well-defined and accurately represents the characteristics of the entity.
Set Valid Values: For each attribute, specify a valid range or set of values (e.g., Age should be a positive integer, DateOfBirth should follow a standard date format).
Example: For an Employee entity, attributes could include EmployeeID (primary key), Name, DateOfBirth (from which Age can be derived), Address, and ContactNumbers. Distinguish between work and home addresses and ensure attributes like Name are composite fields (FirstName, LastName).

Establish Relationships

Define Relationships: After defining entities and their attributes, establish relationships between entities. These relationships help illustrate how entities interact with each other.

Types of Relationships:

One-to-One (1:1): One instance of an entity is related to one instance of another entity.
One-to-Many (1:N): One instance of an entity is related to multiple instances of another entity.
Many-to-Many (M:N): Multiple instances of an entity are related to multiple instances of another entity. These are often broken down into two one-to-many relationships using a junction table.

Example: In a retail system, a Customer may place multiple Orders (One-to-Many relationship). Each Order can include multiple Products (Many-to-Many relationship, managed through an OrderDetails junction table).

Universal Model Concepts: Composition and Association

Composition and Association

	Definition	Example
Composition	This denotes a strong ownership relationship where the child entity cannot exist independently of the parent entity.	An Order and its OrderItems. OrderItems do not exist without the Order.
Association	It indicates a weaker relationship where the entities can exist independently but are associated with each other.	An Employee and a Department. Employees can change departments, and departments exist independently of the employees.

Key Considerations

Primary Key: Always define a primary key for transactional data or master data. The primary key uniquely identifies each record in the table.
Example: CustomerID in a Customer table, OrderID in an Order table.
Association Fields and Alternative Keys: Identify association fields and manage any alternative keys if necessary.
Example: In addition to a primary key, you may need an alternative key such as a Social Security Number (SSN) to ensure uniqueness.
Visual Representation: Use diagrams to visually represent relationships. Symbols, arrows, and cardinality indicators (1:1, 1:N, M:N) help clarify the relationships and structure.

Reducing Redundancy and Ensuring Data Integrity

Avoid Redundancy: Ensure that each piece of data is stored only once to avoid duplication. This is achieved through normalization, ensuring data integrity and logical dependencies.
Validate the Model: Regularly review and validate the ER model to ensure it meets the use case requirements. Document the ER diagram for future reference and to facilitate understanding among team members.

Importance of Data Modeling

Clear Representation: Clearly represent relationships at the start of any project to ensure an accurate model. This is crucial for maintaining performance and addressing potential issues.

Documentation: Documenting the ER diagram helps in the future for maintenance, understanding, and further enhancements of the database model.

Tools for ER Modeling

Free Tools: There are various free tools available for creating ER diagrams. These tools can help you visualize and design your schema effectively.

The Difference between Star Schema and Entity Relationship Schema

Understanding the difference between Star Schema and Entity Relationship (ER) Schema is crucial for designing effective database systems tailored to different business needs. Let's delve into the specifics of these two models to comprehend their strengths and use cases.

Star Schema in Data Warehousing

Overview: The Star Schema is a popular data warehouse design that centralizes data, making it accessible and actionable for growing businesses. In this design, a central fact table is surrounded by dimension tables, forming a star-like pattern.

Key Features

Central Fact Table: The fact table is at the core and contains measurable, quantitative data (for example, sales, revenue).
Dimension Tables: They surround the fact table and hold descriptive attributes related to dimensions like time, geography, products, and so on.
Simple Joins: The primary key of each dimension table corresponds to a foreign key in the fact table, enabling straightforward joins.
Denormalization: Dimension tables are typically denormalized, containing redundancies to optimize read performance and simplify queries.
Importance for Growing Businesses: The Star Schema is essential for businesses looking to centralize their data across the enterprise. It facilitates:
- Efficient Data Retrieval: Faster and simpler queries, suitable for analysis and reporting.
- User-Friendly Design: Easier for end-users to understand without deep technical knowledge.
- Multidimensional Analysis: Supports complex queries and ad-hoc analysis, crucial for data-driven decision-making.
- Business Example: A retail company uses a Star Schema to analyze sales data. The central fact table includes sales transactions, while dimension tables cover products, customers, time, and store locations. This setup allows the company to quickly generate comprehensive reports on sales performance across different regions and time periods.

Entity Relationship (ER) Schema in Transactional Systems

Overview: ER Modeling, used primarily for Online Transaction Processing (OLTP) systems, emphasizes the relationships between entities and aims to minimize data redundancy through normalization.

Key Characteristics

Entities and Relationships: Entities (for example, customers, products) are represented as tables, with relationships defined between them.
Normalization: Data is organized to reduce redundancy and improve data integrity. Tables are narrow with fewer columns.
Comprehensive DML Operations: Supports all types of Data Manipulation Language (DML) operations, including insert, update, delete, and select.
Point and Reporting Queries: Ideal for detailed and precise queries involving single transactions or small sets of data.

Transactional Schema Characteristics:

No Data Redundancy: Designed to avoid data duplicates and ensuring data integrity and consistency.
Narrow Tables: Due to normalization, tables typically have fewer columns and are joined through relationships.
Lock Management: Transactions that involve writing data hold locks on table rows briefly to avoid conflicts, as writers block other writers.
Real-time Data: Serves as the source of truth, capturing current, real-world data.
Live Current Data: The data represents the current state of affairs, updated constantly to reflect real-time information.

Overview of the Modeling Diagram

Once you are on the Manage Functions screen with the Environment filter set, you can view the diagram section. This section visually represents all functions within a specific environment using icons and connects them via arrows to indicate relationships and data flow.

An image of a user interface titled Manage Functions in SAP, displaying a diagram that outlines the relationships between different models, including Document Aggregations, Document Items, Allocation, and Companies. Text boxes provide explanations for various components, such as modeling documents, adding lines for document items, and allocating profits based on legal entities.

Accessing the Diagram

Key Components of the Diagram

Profit Allocation Application: In the Profit Allocation application, you will encounter two primary model entities:
Currencies: Represents the currency data used for profit calculations.
Companies: Represents different company entities involved in profit allocation.

Master Data Model Entity

Documents: This entity holds master data for documents, including details on various income values.

User Interactions in the Model

Entering Additional Document Items: Users can enter additional document items, specifying other income values. These values are crucial for accurate profit allocation.
Aggregation of Income Values: The model view aggregates all other income values at the document level. This aggregation forms the basis for the sender in the allocation process.
Example: If a user enters additional income values for various documents, the system aggregates these values to determine the total income per document, which will be used for further allocation.
Defining Ratios for Distribution: Users can specify ratios for distribution from one company (the sender) to the target company (the receiver). These ratios determine how profits are allocated between different legal entities.

Model Allocation Process

Profit Distribution: The model allocation process uses the specified ratios to distribute all profits between the legal entities. The arrows in the diagram represent the flow of data and the allocation paths, illustrating how profits move from the sender to the receiver according to the defined ratios.

Example: If Company A needs to allocate a portion of its profits to Company B and Company C, users can enter the ratios that define what percentage of profits each company should receive. The model allocation process will then distribute the profits accordingly, ensuring each company gets the correct share.

Visual Representation

The diagram provides a visual summary of the entire process:

Icons: Represent different entities (e.g., currencies, companies, documents).
Arrows: Indicate the flow of data and relationships between entities.
Aggregation Points: Highlight where data is combined or aggregated.
Distribution Paths: Show how data and profits are allocated according to user-defined ratios.

Master Data and Association

The focus will be on the differences between standard models and the Universal Model (UM) approach to handling master data and creating association fields. Understanding these concepts is crucial for designing a robust and scalable data architecture.

Differences Between Standard Model and Universal Model

Standard Model vs. Universal Model: In the Universal Model (UM), master data is structured differently compared to standard models. Here's how:

Model Entity in UM: In UM, master data is organized as a Model Entity.
Key Field: Identifies the unique records (can be String or UUID).
Description Field: Provides descriptive information about the key field.

Importance of Master Data

Master data represents the core data concepts critical to your operations and analytics. It includes essential entities like customers, products, currencies, and locations. Handling master data effectively ensures:

Consistency and Accuracy: Centralized master data ensures all systems refer to the same data, reducing discrepancies.
Reuse in Transaction Data: Master data can be reused across multiple transactional processes, ensuring consistency and saving time.

Master Data

Company ID	Company Description
Moonlight	Moonlight Holding
Sunshine	Sunshine Holding
Starlight	Starlight Holding

Using Master Data in Transaction Data

Creating master data follows a structured approach:

Define Key and Description Fields
- Key Field: Unique identifier for each record (e.g., CurrencyID).
- Description Field: Describes the key field (e.g., CurrencyName).
Create Model Entity
For example, create a Model Entity CURRENCIES with fields CurrencyID (Key) and CurrencyName (Description).
Using Master Data
Once created, master data can be integrated into transaction data for seamless operations. For instance, in financial transactions, the currency codes from the CURRENCIES Model Entity can be used to tag transaction records, ensuring standardized currency references across the system.

Creating Association Fields

How to Create Association Fields: Association fields link different entities, enhancing the relational capabilities of the data model. Here's how to create them:

Create Key and Description Fields:

ID/CODE Field: Unique identifier for the association.
Description Field: Provides descriptive information.

Show Example with Model Entity CURRENCIES:

ID Field: CurrencyID as the Key.
Description Field: CurrencyName as the Description.
Create Association Field: An example of how you can link different types of documents to currencies. This establishes a relationship between documents and currency types, facilitating more comprehensive and cohesive data management.

In this video you will be taught on how to create master data model entity and connect an association (master data) field to it.

Overview of Relations

Configuring Relationships

Relationships between model functions can be configured in the Relation section of the model function's initial configuration screen. Understanding and correctly setting up these relationships are crucial for ensuring data integrity and optimizing data interactions within your SAP environment.

Types of Relationships

There are three primary types of relationships you can configure:

Association of One
Association of Many
Composition of Many

Let's look into each type to understand their specific use cases and benefits.

Association of One

The Association of One is used to represent 1-1 relationships. This type of relationship is common when you have a direct link between two entities, often involving Master Data and Backlinks.

Key Characteristics:

1-1 Relationship: Each entity instance in one model relates to a single entity instance in another model.
Master Data: Often used to link primary key data between two models.
Backlinks: Provides a bidirectional reference, ensuring that changes in one entity reflect appropriately in the associated entity.
Practical Insight: Consider a scenario where an employee record in the HR Model needs to be linked to a corresponding user account in the Access Management Model. Using the Association of One, you can ensure each employee has a unique, directly linked user account, maintaining data integrity and simplifying user management.

Association of Many

The Association of Many is used to represent 1-n relationships between business objects that are independent. This type is vital for high-end data validation and for scenarios where one entity relates to multiple instances of another entity without ownership hierarchy.

Key Characteristics:

1-n Relationship: One entity instance links to multiple instances of another entity.
Independence: The related entities function independently of each other.
High-End Data Validation: Useful for validating data across multiple records, ensuring consistency and accuracy.
Practical Insight: In a retail environment, an order (Order Model) might be associated with multiple products (Product Model). Using Association of Many, you can validate that each product in an order meets required criteria, such as availability or compliance, while maintaining the independence of product records.

Composition of Many

The Composition of Many is used to represent 1-n relationships between business objects that are dependent. This type of relationship is ideal for creating Parent-Child relations, where the child entity's lifecycle is dependent on the parent entity.

Key Characteristics:

1-n Relationship (Dependent): One parent entity instance relates to multiple dependent child entity instances.
Parent-Child Relationship: The child entities cannot exist independently and are intrinsically linked to the parent entity.
Lifecycle Dependency: Changes in the parent entity directly affect the child entities.

When dealing with multinational companies, each company typically operates in a specific local currency. To accurately model this within your SAP environment, it is crucial to use the Association of One relationship type to link each company to its respective local currency.

Output - Associate to One

Key Points:

Unique Local Currency: Each company has a unique local currency.
1-1 Relationship: The relationship between a company and its local currency is one-to-one.
Data Consistency: Ensure that each company is linked to only one local currency, maintaining data integrity and simplifying financial transactions.
Practical Application: Setting Up Local Currency Relationships

Steps to Establish a Local Currency Relationship:

Identify the Models: Determine the relevant models (e.g., Company Model and Currency Model).
Configure the Relation: In the Company Model, navigate to the Relation section of the initial configuration screen.
Set Association Type: Choose Association of One to establish a 1-1 relationship.
Link Local Currency: Ensure each company instance links to its corresponding local currency instance.

Example Scenario:

Consider the following scenario for better understanding:

Company A operates in the UK and uses GBP (British Pound) as its local currency.
Company B operates in the USA and uses USD (US Dollar) as its local currency.

Using the Association of One relationship type, you can link Company A to GBP and Company B to USD. This setup ensures each company has a unique and correctly associated local currency.

Image shows a 1-1 relationship between Company and Currency entities based on ID and Local Currency. Below, a table displays the output, listing ID, Description, and Local Currency (Euro) for Twilight and Sunshine companies in various countries.

Association to one relation can help you identifying desired values stored in another model entity. This video is step-by-step guidance on how to establish this relationship.

Output - Composition of Many

The Composition of Many is used to create parent-child relationships where child entities are dependent on the parent entity. This type of relationship is essential when the lifecycle of the child entities is tied directly to the parent entity.

Key Characteristics:

Parent-Child Dependency: Child entities cannot exist independently of the parent entity.
Data Integrity: Ensures that child entries are created and managed alongside their parent.
Simultaneous Creation: When a parent entry is created, its dependent child entries are also created simultaneously.

Practical Example:

Consider a Document Model and a Document Line Item Model. When you create a new document entry, you simultaneously create associated line items that detail the document's content:

Parent Entity (Document): Represents the overall document with attributes such as Document ID, Title, and Date.
Child Entities (Line Items): Represent individual line items within the document, each with attributes such as Line Item ID, Description, and Amount.
Practical Insight: When you add a new document entry, the line items depend on the existence of the document. If the document is removed, the line items should also be removed, as they have no independent meaning without the parent document.

Image shows a 1-n relationship between Document and Document Items entities based on ID and Description fields. Below, a table lists general information for the document including Description, Year, Company, Tax Free Income, Capital Gain, Dividend, and Other Income. Items section lists details for Twilight Holding's 2023 document.

Composition to many relationships in the Universal Model enables us to establish parent–child relation between 2 model entities. This video is step-by-step guidance on how to establish this relation.

Image shows a 1-n relationship between Document1 and Document Items1 entities based on ID and Description fields. Below, a table lists general information for the document including Tax Free Income, Capital Gain, Dividend, Company, Year, and Description for Sunshine Holding's 2023 document. Items section lists details for three document items with Sunshine Holding's income in 2023.

Create Model Entities DOCUMENT1 and DOCUMENTITEM1 with above fields as per previous slide
Create RELATION between entities as Association to Many
In this we observe, both are independent tables – we enter data independently in this relation
Show the data of DOCUMENT1 and DOCUMENTITEM1 tables
Also show how we can establish relation adding using ‘Adapt UI’ in the show data

Next lesson