Appendix C – Intro to Excel 2016 Data Analysis

​Appendix C Data Connection Basics

Connecting to external data is carried out daily on a wide-scale basis, using various types of data. Some of the popular types of data involved include:

  • SQL Server Databases
  • Microsoft Access Databases
  • Microsoft Excel Services
  • Microsoft Excel Workbooks

​And there are a host of others not mentioned.
Why do we continually create and maintain external data connections? There are benefits to maintaining data connections, some of which include:

  • Analyzing data without having to repeatedly re-copy data to an Excel workbook saves time, commands a high convenience factor, and reduces cost. This is a rather significant benefit since due to very large datasets, recopying data can be potentially very time-consuming and a number of errors are possible.
  • Creating and maintaining data connections is a process that can be and is very duplicatable, reducing development time and cost.
  • Connection information is stored in the workbook and can also be stored in a connection file, such as an Office Data Connection (ODC) file (.odc) or a Data Source Name file (.dsn).

​ODBC and OLE DB Components (Open DataBase Connectivity and Object Linking and Embedding)

​Microsoft Data Access Components (MDAC) 2.8 is included with Microsoft Windows . With MDAC, you can connect to and use data from a wide variety of relational and nonrelational data sources. You can connect to many different data sources by using Open Database Connectivity (ODBC) drivers or OLE DB providers, which are either built and shipped by Microsoft or developed by various third parties. When you install Microsoft Office, additional ODBC drivers and OLE DB providers are added to your computer.
To see a complete list of OLE DB providers installed on your computer, display the Data Link Properties dialog box from a Data Link file, and then click the Provider tab.
To see a complete list of ODBC providers installed on your computer, display the ODBC Database Administrator dialog box, and then click the Drivers tab.
You can also use ODBC drivers and OLE DB providers from other manufacturers to get information from sources other than Microsoft data sources, including other types of ODBC and OLE DB databases. For information about installing these ODBC drivers or OLE DB providers, check the documentation for the database, or contact your database vendor.

Using ODBC to connect to data sources

In the ODBC architecture, an application (such as Excel) connects to the ODBC Driver Manager, which in turn uses a specific ODBC driver (such as the Microsoft SQL ODBC driver) to connect to a data source (such as a Microsoft SQL Server database).

To connect to ODBC data sources, do the following:

Ensure that the appropriate ODBC driver is installed on the computer that contains the data source.
Define a data source name (DSN) by using either the ODBC Data Source Administrator to store the connection information in the registry or a DSN file, or a connect string in Microsoft Visual Basic code to pass the connection information directly to the ODBC Driver Manager.
To define a data source, in Windows click the Start button and then click Control Panel. Click System and Maintenance, and then click Administrative Tools. Click Performance and Maintenance, click Administrative Tools. and then click Data Sources (ODBC). For more information about the different options, click the Help button in each dialog box.

Machine data sources

Machine data sources store connection information in the registry, on a specific computer, with a user-defined name. You can use machine data sources only on the computer they are defined on. There are two types of machine data sources — user and system. User data sources can be used only by the current user and are visible only to that user. System data sources can be used by all users on a computer and are visible to all users on the computer.

A machine data source is especially useful when you want to provide added security, because it helps ensure that only users who are logged on can view a machine data source, and a machine data source cannot be copied by a remote user to another computer.

File data sources

File data sources (also called DSN files) store connection information in a text file, not the registry, and are generally more flexible to use than machine data sources. For example, you can copy a file data source to any computer with the correct ODBC driver, so that your application can rely on consistent and accurate connection information to all the computers it uses. Or you can place the file data source on a single server, share it between many computers on the network, and easily maintain the connection information in one location.
A file data source can also be unshareable. An unshareable file data source resides on a single computer and points to a machine data source. You can use unshareable file data sources to access existing machine data sources from file data sources.

​Using OLE DB to connect to data sources

In the OLE DB architecture, the application that accesses the data is called a data consumer (such as Excel), and the program that allows native access to the data is called a database provider (such as Microsoft OLE DB Provider for SQL Server).
A Universal Data Link file (.udl) contains the connection information that a data consumer uses to access a data source through the OLE DB provider of that data source. You can create the connection information by doing one of the following:
In the Data Connection Wizard, use the Data Link Properties dialog box to define a data link for an OLE DB provider. 
Create a blank text file with a .udl file name extension, and then edit the file, which displays the Data Link Properties dialog box.

​Key points about data connections.

  • There are a variety of data sources that you can connect to: Analysis Services, SQL Server, Microsoft Access, other OLAP and relational databases, Excel workbooks, and CSV/text files.
  • Many data sources have an associated ODBC driver or OLE DB provider.
  • A connection file defines all the information that is needed to access and retrieve data from a data source.
  • Connection information is copied from a connection file into a workbook, and the connection information can easily be edited.
  • The data is copied into a workbook so that you can use it just as you use data stored directly in the workbook.

​Export Connection File

A good time-saving idea is to export the data connection file to either your PC’s My Data Sources subfolder in the Documents folder or to a network folder that can be accessed by a workgroup of people you are a member of. As a result, multiple people can access and update the document when the appropriate time arisesSelect Data tab. 1. Select the Existing Connections icon from the “Get and Transform Data” Ribbon section.​
2. Select the data connection file; right-button mouse click it and select “Edit Connection Properties”(top-left graphic)
3. In the Query Properties dialog box, select the “Definition” tab and select the “Export Connection File” button at the bottom of the dialog box. (top-right graphic)
4. Save file to the “Documents/My Data Sources” subfolder or save to a Network folder. (bottom graphic)