Data model

Data model is used to add and connect tables in a whole, in order to retrieve the data needed for reporting. This is a pseudo-base, part of Excel document. By adding a table into the Model data are automatically compressed; later, when we need them for reporting, they are decompressed and used as a reporting dimension in the pivot table. Data model is basis for use of “Power” technologies, i.e. “self-service” business intelligence tools.

After this „complicated“ introduction I’ll try to slow down a bit and better explain the purpose of Data model. Until the advent of PowerPivot reports being made by using Pivot tables were mainly originated from the single data table (data source). It was a range of cells, structured table or connection to an external source (database) data such as Access, SQL, Oracle … This was a problem for developers of business reports that are using Excel as a reporting platform for two reasons. First, it is not easy to connect different data sources. And then, all imported data are stored in RAM. When it comes to huge number of transactions (as it usually happens in the medium and large companies) this can significantly burden the system. As soon as the number of rows increases over 100,000 Excel starts getting slower to work, and eventually it becomes completely unusable. PowerPivot has emerged as an add-in to Excel that is designed to use xVelocity, the same technology for data processing built within modern Microsoft business intelligence solutions. It performs automatic data compression, and they are stored and searched in a different way compared to the data stored in traditional tables, which in addition to features that connect data from different sources allows for faster and more efficient work.

After importing data from different sources is often necessary to “refine” them. For this purpose there is a tool called PowerQuery. „Refined“ data tables are added to the Model, connected, and they are foundation for creating PowerPivot reports. They can be also used for creating specific reports. In that purpose we use PowerView and PowerMap BI tools. „Power” technologies are also called Self-service BI tools. They are both effective and easy to use, good alternative for Microsoft SQL Server Analysis Services when they are used within small and medium-sized companies. This do not change the fact that „Power“ tehcnologies turned Excel into a mighty reporting platform.

„Power Pivot“ and its siblings are all Excel add-ins. When they appeared PowerPivot and PowerView were not included in the application. Excel 2010 demanded their installation, in a version 2013 it was necessary to enable these add-ins in order to use them. Since version 2016 they are much better integrated into the application environment and, if you have the appropriate version of Excel (Office 2016 Pro Plus, Office 365 Pro Plus, Office 365 E3 or E5), it should be enough to click on the icon to launch Data model.

The Data model can be filled with data from different sources. On this occasion will be shown how to add transactions originating from an Excel worksheet. First, click on the worksheet and then create structured table. It’s easiest to do this by clicking on a cell belonging to the table and pressing Ctrl + T. A small dialog box appears where you need to confirm data range that makes the table, and be sure to mark the box My Table has headers to point out that the first line in the table is header. Once you create a structured table it would be convenient to activate the Name Manager (Ctrl + F3) and provide table with a meaningful name. Finally, the table is added to the data model by first positioning the PowerPivot toolbar, and further clicking Add to Data Model.

This operation may take some time because Excel is importing and performing compression of the data. After that Data model and Power Pivot working environment will appear in separate window in which you can see the table that has just been added to the model. Working environment somehow resembles at different Windows application, with options shown within ribbons. The articles that follow will be talking about how to arrange and connect the data within the Model, and how to create Power Pivot reports.