In several previous “recipes” I wrote about how to connect to Microsoft SQL Server from Excel in order to retrieve data to a worksheet. These were all “innocent” operations, since you could only read the data from the database, not to change them. Sometimes it is necessary to create a program that performs more complex manipulation over the database by running appropriate SQL query, which will be discussed in the text that follows.
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.
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.
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.