Creating Data Objects From Remote Table
You can create objects in OneData from remote databases. Add from Remote System generates and executes the DDL in the OneData data repository. It reads the data dictionary of the remote database for the physical properties of the object.
Important:
When you perform an Add from Remove System action from SQL Server, make sure that you create the base table using SQL Server data types supported in OneData. For example, when you add a table that has a Varchar column in the work area to OneData through an Add from Remote System action, the table that is created in the Release area will have the column data type NVarchar. To avoid this, make sure the table in work area is created with SQL Server data types supported in OneData (in this case, NVarchar).
Note:
In SQL Server, if you added a numeric identity column in the remote database, after add the table you must change the data type of that column to Database Sequence. This is necessary because OneData interprets this column as a numeric column when you perform Add from Remote System.
During Add from Remote System for objects in non-Oracle databases, the defaults are translated during Oracle specific defaults. For example, date in Teradata is sysdate in Oracle.
If you use the Data Direct driver class for a remote connection to an Oracle database, adding an object from an Oracle database into OneData using Add from Remote System converts a source column of Float data type to Double. The change in the data type alters the length or precision of the value in the source column. To avoid this, you must add a Data Type Translation with the following values:
Source Data Type: Double
OneData Data Type: Numeric
OneData Length: 38
For more information about data type translation, see the section Translating Data Types.
To create data object from a remote table
1. On the Menu toolbar, click Define > Objects.
2. Navigate to the folder or create a new folder in which you want to create the object.
3. Click Add Object.
4. Click Add from Remote System.
You will see a list of remote connections setup in OneData.
5. Select the Remote Connection that points to the database where the remote table resides.
6. Click Get Remote Tables.
In the Tables tab there is a list of schemas and the tables available through the connection.
7. Select the required Schema and the table from the list Tables available.
Note:
Select Auto generate column caption to copy the existing column captions to the new object.
8. Click Get Table Structure.
9. In the table structure shown, you can:
Exclude any column in the new object by selecting the check box under
Delete.
Choose which column(s) cannot have null values by selecting the check box under
Required.
Provide
Default Value and
Format (available only for date/timestamp values) for a column.
10. Click Next.
11. In the object definition screen, select the Object Type as Remote Object and enter other details as required.
12. Click Save.
You can change the name of the physical table created.
Note:
When importing remote tables, both structure and data are imported into the work area and release area.
13. You are prompted on the following scenarios if primary key is not detected on the remote table.
Choose the unique constraint to use as primary key, if unique constraint(s) are found on the remote table.
Manually choose the columns that make up the primary key if unique constraints are not found on the remote table.