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.

If we do not add information from Excel spreadsheets we should open the Data model. We can perform this action in two ways: by selecting Data ribbon, and ther Manage Data Model option, or the PowerPivot ribbon and then Manage option. Excel has the ability to connect with a number of different sources, and in this recipe will be talking about how to connect with a Microsoft SQL database. In the Home ribbon of Data model window there is a group of options named Get External Data. In order to connect to SQL database it is necessary, within a menu, to select option From Database. Clicking it will open a menu where you should select the option From SQL Server. After we launch this action a Table Import Wizard dialog box will appear. In several steps it leads us to the process of creating database connection.

First, it is necessary to choose the name of the server we want to to connect. Its name is entered in the text box labeled Server Name. We can create our own name to the connection, which is entered in Friendly Connection Name field. Then, choose the method of application to the server. In most cases it is necessary to choose Windows Authentication option. If this does not work, consult a database administrator who will give you a username and password that you are required to sign. Then in the field Database Name enter the name of the database. Clicking on the button Test Connection helps you to verify if you have created propper connection.

We continue by clicking the Next button and then a window will appear with two options. If the first connection process continues in the direction of the selection of one or more tables in the database. If you choose the second you will need to write a SQL query that creates a dataset.

If you choose to add tables from a database, after you click on Next, you’ll open a window in which can see a list of available tables and views in the database. In the list of tables, by clicking a box left from the name, we choose one or more tables. By clicking Preview & Filter can briefly take a look at its contents before the import. Clicking on the Select Related Tables we also choose all tables that are related to those that we have previously selected. Import process ends with a click on the Finish button, after which it will open a window in which we can see if import process has finished and were there any errors.If everything went right you will be notified. Clicking on Close button closes this window to finish the process of connecting to a database.