Understanding data models in Microsoft Excel 2019

In a nutshell, data modeling is when you combine data from multiplesources to analyze the data further using various tools in Excel 2019.By multiple sources, we mean all the import options offered under theGet & Transform feature and in theanalysis tools used in Excel 2019. In the previous sections, you learnedhow to use the PivotTable and PivotChart tools and you were introducedto the Data tab of the Excelenvironment, where you can get and transform data from differentsources. Data models are created in a workbook; you can only have onedata model per workbook. Any table that exists in Excel can be added tothe data model and table relationships can be defined across them. So,essentially, a data model is the loading of tables into Excel’s memory.It is important to note that you do not physically see a data model inyour worksheet.

Large data models affect the performance of memory negatively,impacting reports and applications that share the same resources. Makesure that the data you need to use is clean and does not contain anyirrelevant fields or data that you do not need to compute in the datamodel. 

The following list outlines the benefits of using a data model:

  • Data models can have unlimited rows of data in memory, whereas Excelhas a limit. The only limiting factor in a data model is the systemresources and the memory available on the system.
  • The actual manipulation of data in a data model is much faster thanis possible in Excel, as it can handle large volumes of data andcalculations without slowing down the computer system.
  • New data is automatically loaded into an existing data model.

Let’s create a data model from some Excel data as a simple example ofhow it works:

  1. Open the  SSGSchool.xlsx workbook. This workbook consistsof a number of sheets with fictional data relating to students andmarks. We want to analyze this data across multiple fields from thedifferent worksheet’s data. Note that the data on each worksheet isalready formatted as a table (you learned about this skill already inprevious topics). Formatting as a table is recommended to allow any newdata entered into the worksheets to be included automatically in thetable range. The data in the worksheets is not yet related.
  2. To create a data model, let’s send the data to Power Query. Power Query is a tool used to transform andshape data, as well as to produce queries. Click on any cell in theworksheet. Then, go to Data | From Table/Range, located under the Get & Transform Data group:
ms office 465
  1. The Power Query interface opens ina separate window and the table data loads as a query in the middlesection of the interface. This tool has vast capabilities as you can useit to discover, connect, combine, and refine data. The aim of thisexample is not to teach you everything about Power Query, but to show you how data isloaded into a model behind the scenes in Excel. We will, however, make afew small tweaks to the data and then send it back to Excel.
  1. Right-click on the Class column and select Transform | UPPERCASE. This converts all the data in thecolumn into uppercase letters:
ms office 750
  1. Next, we will split up the EnrolDate column so that wecan separate the date and time. Select the EnrolDate column,then go to Home | Split Column | ByDelimiter:

ms office 860

  1. Now, choose Space asthe Select or enter delimiter optionfrom the drop-down list and then Left-mostdelimiter for the Splitat option:
ms office 834
  1. Click on the OK button to confirmthese settings and the column splits into two columns. Right-click onthe EnrolDate.2 column and select Remove from the shortcut list to delete it.Double-click on the EnrolDate.1 column heading and rename itto Enrol Date.
  2. We have made changes to the data and it is now time to send it backto Excel. This is where the data model comes into play. Go to File | Close& Load To….
  3. In the Import data dialog box,select Only create connection toindicate that this is how you would like to view your data in Excel.Locate the Add this data to the DataModel checkbox and make sure it is selected as this is the optionthat lets you place the data into the data model in Excel. Note that weare only working with the table on the first worksheet in Excel and theprocess needs to be repeated for all the other worksheet data to load itinto the model:
ms office 270
  1. Click on OK to confirm thesettings.
  2. You are now taken back to Excel and the Queries & Connections pane appears onthe right-hand side of the workbook, which now displays the connection.Repeat the preceding steps to add all the other tables from theremaining worksheets into the data model. There are, of course, easierways to complete this task, but the aim of this example is to understanddata models, so manually adding the remaining worksheets to the datamodel gives you good practice. 
  3. You should now have four items listed in the Queries pane and one item listed in theConnections pane in Excel. This meansthat you have four queries in one connection loaded into the datamodel. 
  4. As mentioned previously, the data in each worksheet is not yetrelated. Relationships do not currently exist between the tables. Wewill now create these relationships using Power Pivot so that we can query data acrosstables and pull out information from more than one table at one time. Click on Power Pivot | Manage from theManage Data Model group:
ms office 41
  1. When Power Pivot loads in aseparate window, click on Design |Manage Relationships to see anyexisting relationships. Click on Create to add a new relationship between twotables linking a common field. In our scenario, we will use the studentCode as the link. ElkPupilTbl will be present atthe top of the Create Relationshipdialog box. Choose PupilMarks from the drop-down list in thesecond part of the window. Make sure that both of the tables haveCode selected, as in the following screenshot, as the columnto connect between the two tables:
ms office 183
  1. Click on OK to confirm therelationship, then repeat the process for the other tables, finding acommon field between two tables such as ResultsTbl andSubjCodes, linking the SubjCodes field between thetwo, then link the Code field between ElkPupilsTbland ResultsTbl. Click on Close when done to complete therelationships.
  1. To view the relationships you have created visually in Power Pivot,go to Home | Diagram View. Click on Home | DatasheetView to see the table data once again:
ms office 271
  1. Click on the Save icon to updatethe data or use the Ctrl + S keyboardshortcut. 
  2. To return to the workbook, click on the Switch to Workbook icon or go to File | Close to exit Power Pivot and return toExcel:
ms office 324

Now that you have your worksheet data loaded into the data model andhave set relationships between tables, you are ready to createPivotTables and PivotCharts, as well as to query the data model inmemory using Power Query. 

Leave a Comment