Managing Data Sources, Models, and Data Connections
The Report Center is designed to store data connections and data sources so that users can access those data sources without having to know all the details about server, database, and security settings. There are three types of data connections that are supported by the Report Center:
Office Data Connection files (ODC): The ODC definitions enable Office applications to connect to your SQL Server, OLEDB, ODBC, or XML data sources.
Universal Data Connection files (UDC): Connection file format for applications such as InfoPath to store their data connections. In an InfoPath scenario, the UDC allows browser forms to work across domain boundaries. UDC files are XML files with a particular namespace and schema.
Reporting Services Data Sources (RSDS): The Reporting Services Data Sources is a new file format for data sources that are designed in SharePoint and are used to support models, reports, and report subscriptions.
Creating and uploading data connections
Data connections are stored in the data connection library for your Report Center site. We use Excel to create the (ODC) file and then upload that to a Data Connection library. You can configure whether you want to specify credentials for the connection or use Windows authentication so that the user’s credentials are necessary for access to the data.
To create an ODC file, follow these steps.
1. | Open Excel 2007 and select the Data tab. |
2. | Select whether you would like to connect to a SQL Server, Analysis Services, XML file, OLEDB, or ODBC source in the From Other Sources menu in the Get External Data section. |
3. | Type the server or filename and connection credentials on the Connect to Database Server screen of the Data Connection Wizard, as shown in Figure 15.4, and then Click Next. |
4. | Select the database and table or cube in the Select Database and Table screen of the Data Connection Wizard, as shown in Figure 15.5. Click Next. |
5. | Type the filename and description for your data connection (ODC) file. The description should be used to let users know the details of the connection so that they can accurately pick the connection that meets their needs. If you do not select a filename location by using the browse button, the file will be saved to your My Data Sources folder under [My Documents]. Click Finish. |
6. | In the Import Data prompt that appears once you finish, select Only Create Connection as shown in Figure 15.6. Figure 15.6. Creating a connection file using Excel 2007
|
7. | Use a Web browser to navigate to the data connection library in which you want to store the ODC file. This will most likely be in the Report Center for your site collection. |
8. | Select Upload from the top navigation bar and browse to or enter the location of your ODC file; click OK. |
9. | Provide a description for the ODC file so users know to what cube they will be connecting and select Check In. |
Note
Files that are uploaded to data connection libraries will need to be approved unless approvals have been disabled on the library.
You can create a UDC file by using Notepad to create and edit the XML according to the UDC file schema or by using InfoPath to create a data connection and converting to UDC file format. To create a UDC file using InfoPath, follow these steps:
1. | Open InfoPath 2007 and launch the Data Connection Wizard by selecting Data Connections from the Tools menu. |
2. | If you have an existing data connection that you want to convert, select that data connection. There are many options for data sources depending on the type of data you are connecting to and whether you will be reading and/or writing to the source. In the steps below, we create a data connection to receive data from a SQL Server database and they will vary slightly if you a submitting data or have a different type of data source. Follow these steps:
|
3. | Select the data connection that you want to convert and click Convert, as shown in Figure 15.8. Figure 15.8. Converting a data connection to UDC file formatting
|
4. | Type the URL for your SharePoint data connection library including the filename but not the extension that you want your UDC named, as shown in Figure 15.9. Figure 15.9. Publishing your converted data connection to your SharePoint data connection library
If you are writing to a data connection library that just supports your site, select Relative to site collection (recommended). If you are writing a data source that you will upload to the document library that supports InfoPath Forms services in SharePoint Central Administration, select Centrally managed data connection library (advanced). Click OK. |
5. | If you are creating a centrally managed data connection, you will need to copy the file from the data connection library that you are publishing to the Central Administration Data Connection library. To do this, follow these steps:
|
6. | To approve your data connection file, browse to your data connection library and left click on the file you want to approve. Select Approve/reject from the menu, as shown in Figure 15.10. Files that are uploaded to data connection libraries will need to be approved unless approvals have been disabled on the library. Figure 15.10. Approving your data connection file
|
7. | Select Approved and click OK. |
Creating data sources and data models
Data sources support reporting services reports and models. If you are creating a new Report Builder report, you will need to have a data model that has been generated for your data source. You must first define your data source and then you can generate the data model. If you are uploading a different type of reporting services report (.rdl), such as one that has been generated by report designer, you do not need to have a data source or data model configured.
To create a data source in your report library, follow these steps:
1. | Use a Web browser to navigate to your reports library. |
2. | From the New menu, select Report Data Source. If this option is not available, you must add the report data source content type. For information on how to add this content type, see the “Adding Reporting Services Content Types” section earlier in this chapter. |
3. | Type a descriptive name for your data source in the Name field. This name helps users to identify if this is the appropriate data source for their needs. |
4. | Select whether you want to connect to SQL Server, SQL Server Analysis Services, Oracle, ODBC, OLE DB, XML, SAP Netweaver BI, or Hyperion data source in the Data Source Type drop-down menu. |
5. | Type the connection string for your data source in the Connection string area, as shown in Figure 15.11. |
6. | Select whether you want to use Windows authentication or stored credentials, or prompt for authentication in the Credentials section. Alternatively, you can select to use the Unattended report processing account, which uses the account credentials configured in the Reporting Services Configuration Manager. This authentication option is required if you want to add subscriptions for the reports that will be using this data source. See the Configuring the Execution Account procedure below for more information on how to set this account. |
7. | Confirm that the Enable this data source option is selected. |
8. | Click OK. |
To generate a report builder model for your data source, follow these steps:
1. | Use a Web browser, to navigate to your reports library. |
2. | From the New menu, select Report Builder Model. If this option is not available, you must add the report data source content type. For information on how to add this content type, see the “Adding Reporting Services Content Types” section earlier in this chapter. |
3. | Type a descriptive name for your data source in the Name field. This name will help users to identify if this is the appropriate data source for their needs. |
4. | Type the path or browse to the data source on which this report builder model will be based. |
5. | Click OK. |