Linking data in the model

When you start using Excel business intelligence tools you’ll realize that they enable connecting with data from multiple sources. This can be an Excel spreadsheet or text files, databases, data from the Internet, data warehouse “in the cloud” … If you have ever used MS Access, you will easily understand how to add and link tables within the model. If not, read this article and you will see that it is easy and quite intuitive task.

After you have added at least two tables in Model, from one or more sources, you can connect them in order to create reports. Databases are typically designed with the aim of achieving a high level of normalization, i.e. little less redundant data (the data to be repeated). If you want to record the business transactions that will work in the table which boasts only codes partners, sales, items … All other information can be found in the associated tables, and by adding and connecting with a table that stores transaction data we’ll create a collection on which could build reports.

By selecting option Manage in the Power Pivot toolbar or Manage Data Model in Data toolbar (this is a novelty that brought with Excel 2016), the window of the Data model will open. It behaves as a separate application, opens as a separate window and has its own toolbar with multitude of of options for working with data. In the Home toolbar, to the right, you will see options Data View and Diagram View. Clicking on any of them change you can change the view of the data. In addition to being located in the Home toolbar, icon shortcuts to the views of the Data View and Diagram View are located in the lower right corner of the window.

Data View enables you to view data tables, calculated columns and measures. Each table is represented by tab that is located at the bottom of the window, similar to the organization of worksheets in Excel. Right-click on one of the tabs opens the context menu, which contains several options for manipulating tables. In the central part of the window you can see a data table, with banded rows to make them more transparent. Under the table there is an empty space in which to create measures. Directly next to the option for changing the view in the Home toolbar there are two options. Clicking on the Calculation Area displays or hides area in which we create measures. Option Show Hidden is used to enable or disable hidden objects within the model.

Diagram View allows you to view a diagram of Data model which consists of tables and connections between them. The tables are linked by using the columns-keys. Clicking on one of them and dragging to key in a related table, which has to represent the same type of data, you can create connections between the tables. It is understood that you will connect the keys in order to establish meaningful connections, much like when you create links between tables in database. Although drag&drop technique is first choice to create links between tables you can also create them by using the option Create Relationship on Design toolbar. Manage Relationships is an option used to modify existing connections.

Let’s see how this works in practice! In following example we have a couple of structured tables (Transactions and Items) in an Excel document. By using option Add to Data Model, which is located in PowerPivot toolbar, we have added them to Data model. Clicking on the Diagram View displays these two tables. We create a connection between them by clicking on the field ID-ART (item code) in the table Items (Artikli), and then dragging it to the ID-ART field in the table of transactions (Transakcije). If we did it right you will see the line that indicates the relationship between the tables has been made. Double-clicking on this line opens the window Edit relationship in which we can see and modify links. The process of adding and linking tables within the Data model is continued until we get a set of data required for reporting.