When you are creating relationships between tables, the unique values for each row are known as the

Power Pivot is part of the Excel Data Model. It allows you to report on multiple tables at once and create special calculations called Measures. Measures extend what calculations are possible within Excel’s PivotTable reports, and are written in a language called DAX (Data Analysis Expressions).

The Data Model, Power Pivot and DAX are a huge topic. In this article I will introduce some of the basic features, which are still extremely powerful and flexible. Power Pivot and DAX are also part of Power BI. Anything you learn in Power Pivot can be applied to Power BI.

Loading Power Pivot

To see if you have Power Pivot, click the File tab and then Options (bottom left). Click Addins on the left side. In the Manage: Drop Down (bottom of dialog) select COM Add-ins and click Go. Make sure Microsoft Power Pivot for Excel is ticked and click OK. Power Pivot has its own tab.

Example

We will use four Excel tables, one each for Sales, Dates, Products and Customers. All four tables have been defined as tables using the Format as Table option on the Home ribbon.

Terminology

In Excel tables, we refer to “columns” and “rows”. In databases, these are called “fields” and “records”, respectively.

There are two common table types in the Data Model.

• Fact or Data table: This table tends to have the largest number of records, and it may be transaction-based. This table typically has the values you want to report on. In our case, the Sales table is the Fact table.
• Dimension or Lookup table: This type of table has fewer records and is related to a specific subject. Each record is dedicated to a single item and has a unique identifier. The Dates, Customers and Products tables are all Dimension tables. There is typically a relationship between each Dimension table and the Fact table. Relationships use unique codes to relate the tables together.

Loading the tables

Click a cell in the Sales table. In the Power Pivot tab, click the Add to Data Model icon. This opens the Power Pivot window. This window can stay open while you continue to work in Excel. If you have two screens, you can have Power Pivot on one screen and Excel on the other. Repeat this step for each of the other three tables.

In the Power Pivot window in the Home tab, click the Diagram View icon (right-hand side of tab). This shows a field (column) listing for each table (see Figure 1).

We need to create a relationship between each Dimension table shown at the top in Figure 1 and the Sales table shown at the bottom. To create a relationship for the Dates table, use the mouse to click, hold and drag the Dates name from the tblDate listing to the Dates name in the tblSale listing. Repeat this step for the Product ID field in tblProduct and the Customer ID field in tblCustomer.

In this chapter, we will sharing information about database relationships and how relationships are defined from one table to another. The Relationships Tool is used to define relationships between tables based on common fields. Relationships defined using the Relationships Tool are important as they help ensure integrity of data and they provide us with default join criteria for queries involving more than one table.

In this section, we will use the University and the Library databases in our examples.

Consider the University database that contains a Department table and a Course table. These two tables have the deptCode field in common:

In the Department table, deptCode is the primary key and is used to identify a specific department.

In the Course table, the deptCode field is a part of the primary key and indicates the department to which the course belongs.

To ensure that a row in Course is related to an existing row in Department, we can use the Microsoft Access -Relationships Tool to define a relationship between these two tables based on this common field. Using a diagram, we can illustrate this connection between these two tables:

In this situation, we say that deptCode in Course is a foreign key referencing the deptCode field in Department.

Now, consider the Library database:

The Loan table has a callNo field as well as the Book table; the callNo field identifies a specific book.

The Loan table has an id field as well as the Member table; the id field identifies an individual member.

In the Library database, we can establish a relationship between the Loan table and the Book table based on the callNo field. A second relationship can be established between the Loan table and the Member table based on the id field. Using a diagram, we can illustrate these two relationships:

The Loan table has two foreign keys identified as callNo and id:

The callNo field in Loan references the primary key (callNo) in Book.

The id field in Loan references the primary key (id) in Member.

5.1 Database Integrity In Relational Database Design

Primary Key

Recall that a table’s primary key (PK) is a field (possibly composite) that has unique values. Each record row has a PK value different from any other row in the table. Primary key is a field with a unique identifier. If a query were designed to retrieve a row of that table based on a value of the PK, then at most one row of the table will be retrieved.

Foreign Key

A foreign key is a field (or combination of fields) in a table B that is associated with a primary key field in a table A through a relationship (A and B can be the same table). Data redundancy is eliminated by having a foreign key in one table related to a primary key in another table.

Entity Integrity

When we define a primary key for a table, we are enforcing entity integrity. Entity integrity means that each row in the table is identifiable through its primary key. Microsoft Access requires a value for a primary key in a newly added row, and Access enforces uniqueness of those values.

Referential Integrity

Suppose we have two tables, table A and table B, where a relationship is defined between the primary key of table A and a foreign key in table B. We say referential integrity exists for this relationship if each row in table B has either:

• a foreign key (FK) that does not have a value at all (i.e. it is null) or
• a foreign key (FK) that has a value that exists as a primary key (PK) value in a record row in table A.

5.2 Relationships

Tables can be related through one-to-one, one-to-many, or many-to-many relationships. If you open the Access Relationships Tool for the University database, you will see the following diagram showing two tables and a one-to-many relationship:

There are two symbols on the relationship line which inform us the table relationship is one-to-many for which there are two rules that are in place:

• • • For each department there will be zero or more courses for that department, and,
    • Each course is for exactly one department.

To create a relationship in Microsoft Access, you must

• Open the Relationships Tool located in the Database Tools tab.
• Add the pertinent tables to the diagram if they are not there already
• Click, hold, and drag a field (normally this is the PK) of one table to the related field (to become a FK) in the other table.

You will be asked whether or not Referential Integrity is to be enforced. As a general rule-of- thumb, you should select Yes. There must be some exceptional circumstance that makes you select No.

Once relationships are established using the Relationships tool, they are used by Microsoft Access when you create queries. These relationships are then used as your default table joins.

5.2.1 One-To-Many Relationship

In relational databases, one-to-many relationships is one of the most common type of relationship between two tables. As described in the previous example, there is one record (also known as parent record) in a table which must be associated with one or more records (child or children records) in a second table to establish a one-to-many relationship. You will need to verify that both the PK field and FK field have the same data type to create this relationship even though these fields have different names.

In Access, you can create relationships by selecting the Relationships command and displaying your tables. You drag the primary key (PK) field of one table to the other table. The primary key field in the first table must contain unique values. If the foreign key (located in the second table) does not have unique values, then you are creating a one-to-many relationship. The relationship between the two tables will be illustrated by then having Access display a relationship line.

To summarize one-to-many relationships:  For each row in the referenced table, there can be several related rows in the other table. For a primary key (PK) value, there can be many rows in the other table with that value stored as the foreign key (FK). Let’s look at the following example to illustrate a one-to-many relationship using the University database.

Practice Your Skills Example

The Department table and Course table are related through the common deptCode field. Both of these fields have the same data type declared in these tables. You can practice your skills in this exercise by creating the relationship between these two tables:

1. Before re-creating the relationship to practice your skills, you must first remove the current relationship between the Department and Course tables.  Display the Access Relationships window.
2. Delete the existing relationships line (use your mouse to point and click on the relationship line, press delete, and follow through with the dialog box to delete the relationship).
3. Now, click and drag the deptCode field in the Department table and drop it on top of the deptCode field in Course table. On releasing the mouse, Microsoft Access will display the following dialog box:

1. At this point, Access is requesting the user to confirm the proper fields are being related. The user needs to make a choice regarding Referential Integrity and ‘Cascade’ options.

• • You should choose Enforce Referential Integrity in almost all cases as this helps reduce the chance of corrupting data.
  • We will not be currently discussing ‘Cascade Update/Delete Related Fields’ in this chapter.
  • Close the Relationship window.
  • From the above example: When the user clicks Create, Access shows the relationships line with 1 on the one side and an infinity symbol on the many side of the relationship:

5.2.2 One-To-One Relationship

If you drag a primary key field of one table to another table, and if the foreign key has unique values (a unique index exists for it) then you are creating a one-to-one relationship. For each row in the first table, there can be at most one related row in the other table. A row in the referenced table has a primary key value that equals the foreign key value in at most one row of the referencing table.

5.2.3 Many-To-Many Relationship

If you create a relationship in Microsoft Access where both fields you associated (via the click, hold, and drag sequence) do not have unique values (i.e. neither have unique indexes) then Access creates an ‘indeterminant’ relationship. In this situation, a row in one table, A, may be related to multiple rows in the other table, B, and where a row in table B may be related to multiple rows in the table A.

This is not done very often and corresponds to a many-to-many relationship. Most database designers would avoid this in their database designs. If a database designer is faced with two tables, A and B, that are related via a many-to-many relationship, the designer would likely introduce a third table, say C, where A and C will be related via a one-to-many relationship and similarly, B and C will be related via a one-to-many relationship.

Later in these notes, we discuss database design. We will see how many-to-many relationships can be decomposed into two one-to-many relationships.

Exercises

For these exercises, use the Company database which does not have any Access relationships defined for the Employee and Department tables. The first few rows of Employee table and Department table data are as follows:

Employee

empId

firstName

lastName

supervisor

dept

1

Tanya

Dickson

2

Heidi

Herring

1

1

3

Hiroko

Hawkins

1

2

4

Emmanuel

Watkins

1

3

5

Oliver

Holt

2

1

6

Raphael

Delaney

3

2

7

Basia

Franks

2

1

8

Bruno

Pena

2

1

Department

deptId

department

manager

phone

1

Marketing

2

(204) 999-4444

2

Human Resources

3

(204) 999-3333

3

Sales

4

(204) 999-2222

1. Consider the Employee and Department tables. Note: the Employee table has a field named dept which indicates the department where the employee works. The relationship can be stated:
   • Each department has zero or more employees, and,
   • Each employee works in at most one department.

Create a one-to-many “works in” relationship between Employee and Department. Enforce Referential Integrity between these tables.

1. The Department table has a field named manager which indicates the employee who is the head of the department. The relationship is stated:
   • Each department has one employee who manages that department, and,
   • An employee may manage at most one department.

There is a unique index defined for the manager field and so you can create a one-to-one relationship “has manager” between Department and Employee.

• • In the relationship window, add a second Employee table to the relationship window.
  • There will be two (2) Employee tables on the diagram.
  • Drag the PK empId field from Employee_1 table to the supervisor field of the Employee table.
  • Enforce Referential Integrity between these tables.
  • Save the Relationships that you have created for your database structure.
    Note how Microsoft Access represents the relationships between these tables.

1. Consider the empId and the supervisor fields of Employee. Most employees report to someone – someone who is their supervisor. Only employee 1 does not report to anyone else. The supervises relationship can be stated:

• • • An employee may supervise many other employees, and,
    • An employee reports to at most one other employee.

a) Create the supervises relationship. If you are doing this exercise after Exercise 2 then your relationship diagram has 2 copies of the Employee table. You may proceed onto understanding the hierarchical reporting structure in Step C.

b) If you are not doing this after exercise 2, then you must add Employee to the diagram twice so there are 2 copies of Employee on the diagram. Drag the PK empId field from Employee_1 table to the supervisor field of the Employee table. Note how Access draws this diagram.

c) Save the Relationships that you have created for your database structure.

d) Open the Employee table in Datasheet View to view the data representing the hierarchical reporting structure. The supervisor field correlates with the empID field.

e) The supervisor field is an implementation of a hierarchical reporting structure for our company. Use a piece of paper and draw the reporting structure for the company (for the data given at the start of these exercises).

We have started this exercise showing the reporting structure for the first 4 employees. For example, Tanya is the supervisor for Heidi, Hiroko and Emmanuel.  As you analyze the data further, Heidi, Hiroko and Emmanuel would then supervisor additional employees in the database.

Queries

The following query exercises depend on the relationships diagram from the above exercises. When developing a query, you will see that MS Access will include relationships when you add tables in the upper pane of the Query Design window for a query.

Evaluate your table relationships carefully that your previously created. This will ensure that the tables included your  queries will then have the correct one-to-many relationship between the Department and Employee tables. In these exercises, you will also need to create an inner join for Employee and Employee_1 tables. The displayed join between these Employee tables will only display rows where the joined fields from both tables are equal.

How do you create a relationship between two tables?

Which type is used to create a relationship between tables?

What is a relationship between tables?

What are the three types of relationships between tables?