Connecting to Data Sources

A data source is an external data repository—typically a large database—whose data you want to analyze by using Essbase. You must connect Integration Services to all data sources that you plan to use in creating OLAP models and metaoutlines and in loading data into an Essbase database.

Note:

You can connect to more than one relational data source to create OLAP models.

To connect to a data source:

In the Integration Services Console Welcome dialog box, click the appropriate icon to create a new OLAP model or metaoutline. Alternatively, select the Existing or Recent tab and double-click an OLAP model or metaoutline to open it for editing.
The Data Source dialog box is displayed.
In the Data Source drop-down list, select the data source to be used; for example, TBC in the sample application.
An ODBC data source must be created on the computer that is running Integration Server for any external data sources that you want to use. If the data source that you need is not visible in the scroll list, contact the Integration Services system administrator.
For more information about troubleshooting server and data source connections, see Troubleshooting ODBC and Connections.
The following example is a Net Service Name stanza that defines TBC in the tnsnames.ora file:
```
TBC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = labmachine2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orasid)
    )
  )
```
For Oracle using onames, in the example above, TBC is the Schema Name used to connect to an Oracle database. This is the database identifier that you use when you are using SQL *Plus to connect to a database.
DataDirect Drivers: If you are using DataDirect drivers with Oracle, pick a data source name from the Data Source drop-down list.
In the Code Page drop-down list box, select the code page of the language you want to use during the current Integration Services Console session, and click OK.
The code page is a portion of the locale which identifies the local language and cultural conventions such as the formatting of currency and dates and the sort order of data.
In the User Name drop-down list box, type or select your user name.
Note:
If you are using a flat file data source, no user name is required.
In the Password text box, type your password.
Note:
If you are using a flat file data source, no password is required.
Click OK.
The left frame of the OLAP Model main window initially displays information about the first data source to which you connected. Data source information is displayed hierarchically by data source name and owner name.
For each owner name, data is further sorted and organized by tables, views, and synonyms. You can use the listed source tables to create an OLAP model or use additional source tables by connecting to other data sources.
Information about subsequent data sources to which you connect is displayed in the same manner as information about the first connected data source was displayed; that is, by data source name and owner name, then tables, views, and synonyms.
Expand the plus sign, , to display tables, views, and synonyms contained in the data source.
If you want to connect to additional data sources, complete the steps in the following procedure:

To connect to additional data sources:

Select Connections, then Add Data Sources.
The Data Source dialog box is displayed. You can connect to any number of available data sources without closing this dialog box.
In the Data Source drop-down list, select the additional data source to which you want to connect.
If you are using DataDirect drivers with Oracle, pick a data source name from the Data Source drop-down list.
In the Code Page drop-down list box, select the code page of the language you want to use during the current Integration Services Console session, and click OK.
In the User Name drop-down list box, type or select your user name.
It is necessary to type a user name the first time it is used. After a user is successfully connected to a data source, the user name is then displayed in the User Name drop-down list.
Note:
If you are using a flat file data source, no user name is required.
In the Password text box, type your password and click Connect.
Note:
If you are using a flat file data source, no password is required.
The left frame of the OLAP Model main window displays information about the data source to which you just connected along with information about the first data source to which you connected.
Repeat Step 2 through Step 3 for each data source to which you want to connect.
As you connect to additional data sources, the left frame of the OLAP Model main window displays information about all data sources to which you are connected.
When you finish connecting to all appropriate data sources, click Close.