Model and a database link

In one of previous recipes I described how to make an Excel database connection. Similarly you can create a connection between Data model and various data sources. Main difference is in fact that Data model uses technologies for compressing data, during their import, and decompressing them only when used in the report, which saves memory and allows you to work with large amounts of data. You can create a link to a single table, multiple tables or to a data set made by running SQL queries.

Corporate reports

Working in large companies, especially multinational corporations, often involves periodically sending the standardized Excel reports that are intended to provide management with information about the business. These are usually consolidated balance sheets and other reports that display key performance indicators, and the employees who should fill them have a task to enter data into ready-made templates.

Connecting with a database

Excel is a software with lots of features, but perhaps its best application is data analysis. In order to have something to analyze, we must retrieve the data. Operational data are held in relational databases, so we should connect to DBMS. Most of the time it is and istance of Microsoft SQL Server. When connecting we’ll importing a content of a table, view or result of a query into Excel worksheet.