Page Comparison

...

• Integer-type Primary Key Required: External data needs a unique integer-type field that can be aliased as an ID.
• External Data Not on Local Server Needs a Linked Server Relationship: If the external data is not physically stored on the local SQL server where Aptify is installed, then you need to - create a Linked Server relationship.
• External Data Not on Local Server Must Map to a View in a Database on the Local Server: If the external data is not physically stored on the local SQL server where Aptify is installed, then you must also create a view in a local database that points to the virtual entity's data source.

Anchor
_primaryKey _primaryKey

Integer-type Primary Key Required

Data displayed in Aptify must have a unique integer-type field (int, smallint, bigint) that can be used as the ID value for the record. Aptify requires that primary keys use an integer type for ID values.

...

Note that this requirement is already met if mapping Aptify sub-type data to a virtual entity, since sub-type entities already have an ID field.

Anchor
_linkedServer _linkedServer

External Data Not on Local Server Needs a Linked Server Relationship

A virtual entity can display external data that is not stored physically in the local SQL Server. However, to display this data in Aptify, the external data source must have a Linked Server relationship with the SQL Server that hosts Aptify.

Therefore, the external data must be in a format and location that is supported by SQL Server's Linked Server functionality. Examples of supported data sources include other SQL Server machines, OLE DB or ODBC data source as well as Jet/MS Access. Oracle, DB2, and other relational databases can be connected via OLE DB or ODBC.

Follow these steps to create a Linked Server relationship:

...

• Refer to Microsoft's Help documentation if you need assistance completing this information.

1. Click the Security page and specify connection credentials for the data source, as appropriate.

• For example, if connecting to another SQL Server, you must provide login information for this server.
• Refer to Microsoft's Help documentation if you need assistance completing this page.

1. Click the Server Options page and modify the default options, as necessary.

• Refer to Microsoft's Help documentation if you need assistance completing this page.

...

See Creating a Linked Server Relationship for a Virtual Entity for more information about establishing this relationship.

Anchor
_databaseMap _databaseMap

External Data Not on Local Server Must Map to a View in a Database on the Local Server

A virtual entity in Aptify obtains data from a view that is stored in a database on the local SQL server, either in the APTIFY database or another local database. Therefore, if you are creating a virtual entity for external data that is not on the local server or if you creating a virtual entity of Aptify data (such as a sub-type entity), then you need to create a view in a local database from which Aptify can pull the virtual data. The steps below describe how to create this view using Microsoft SQL Server Management Studio in a local database other than APTIFY. The screen captures included with these steps illustrate how to create a view of data from a LibraryInventory table that exists in a database named Vault on another SQL server on the network. This other SQL server (named, EXTERNAL_DATA) has a Linked Server relationship with the local SQL server.

Best Practice Tip: If you want to create the view of the external data directly in the APTIFY database, Aptify strongly recommends that you add the view to the database using a Database Objects record rather than creating the view using Microsoft SQL Server Management Studio. See Database Objects Service for information on using the Database Objects service.

• The second example creates a view of Aptify's ProductPrices sub-type entity in another database on the local server.

Follow these steps to create a view to external data in a local database other than APTIFY:

1. If not already open, launch Microsoft SQL Server Management Studio and connect to the SQL server on which Aptify is installed.
2. In the Object Explorer, expand the server's Databases heading.
3. Select a database on the local server other than APTIFY. You can create a new database also, as necessary.

• The example in this section use a local database called VirtualData.

1. Click the New Query button in the toolbar to open a blank SQL query window.

Image Removed

1. Enter a SQL statement to create a view of the desired data.

• Specify the Linked Server you created -previously (see External Data Not on Local Server Needs a Linked Server Relationship for details) as the location for this data.
• The following is a sample statement you can use to create the view and grant -access to a database role:
• CREATE VIEW [ViewName] ASSELECT * FROM [LinkedServerName].[database on linked server, if -applicable].dbo.[ExternalTableName]GOGRANT SELECT ON [ViewName] to [database role]
• The statement shown in the figure below will create a view named -vwLibraryInventory to display all of the data from the LibraryInventory table, which is located in the EXTERNAL_DATA linked server's Vault database.

Image Removed

• If creating a view of data stored in Aptify, specify a view name that will not be confused with Aptify's standard naming convention for views (which is, vw[EntityName]).

The following is a sample statement you can use to create the view and grant -access to a database role:
CREATE VIEW [ViewName]
AS
SELECT * FROM APTIFY.dbo.[TableName]
GO
GRANT SELECT ON [ViewName] to [database role]
The statement shown in Figure 4.60 will create a view named -"vwVirtualProductPrices" to display all of the data from Aptify's ProductPrices sub-type entity.

• Creating a View of Aptify Data

Image Removed

For the sake of simplicity, the sample view above grants access to the public database role. However, when creating a database view, you should follow your organization's standard security practices and grant SELECT access only to those roles that require it.

...

Creating a virtual entity to retrieve data in this way requires first that a linked server relationship be in place. See Creating a Linked Server Relationship for a Virtual Entity for more information about establishing this relationship. With that relationship established, the view needs to be created in the local database, as described in Creating a View in a Local Database to Point to a Virtual Entity Data Source.