مشاركة عبر

  • مقالة

OPENROWSET (DMX)

Replaces the source data query with a query to an external provider. The INSERT, SELECT FROM PREDICTION JOIN, and SELECT FROM NATURAL PREDICTION JOIN statements support OPENROWSET. For more information about the syntax for specific providers, see OPENROWSET (Transact-SQL).

Syntax

OPENROWSET(provider_name,provider_string,query_syntax)

Arguments

  • provider_name
    An OLE DB provider name.

  • provider_string
    The OLE DB connection string for the specified provider.

  • query_syntax
    A query syntax that returns a rowset.

Remarks

You can set a property on the Analysis Services server to enable ad hoc queries using OPENROWSET. However, we recommend that you use OPENQUERY instead. By using OPENQUERY, you can control the data sources to which users have access, as users with OPENROWSET can potentially view database table that are unrelated to data mining tasks.

If you choose to enable ad hoc OPENROWSET queries, you should limit the providers that can be used to instantiate a connection to the server and database by specifying a provider ID. For more information, see Data Mining Properties.

The exact syntax for OPENROWSET depends on the provider that you have specified. In general, the data mining provider will establish a connection to the data source object by using provider_name and provider_string, and will execute the query specified in query_syntax to retrieve the rowset from the source data.

Examples

The following example shows a PREDICTION JOIN statement that retrieves data from the ProspectiveBuyers table of AdventureWorksDW2008 database by using a Transact-SQL SELECT statement and predicts a cluster for each new customer.

The user ID, DMUser, is a SQL login that has been given read-only permissions on the data table used for storing new customers. Note that passwords are presented in clear text in OPENROWSET and therefore present a security risk. We recommend that you se OPENQUERY instead and secure relational data sources with the lowest possible level of access.

The first three fields in the SELECT clause of the OPENROWSET statement are not used by the model for prediction, but are useful for identifying customers. Any columns that are used for prediction must be mapped to input columns in the model by adding them to the ON clause.

SELECT
  t.[ProspectiveBuyerKey], t.[LastName], t.[FirstName],
  Cluster()
FROM
  [TM_Clustering]
PREDICTION JOIN
  OPENROWSET('SQLOLEDB','localhost';'DMUser';'&IZqw3x&',
    'SELECT
      [ProspectiveBuyerKey], [FirstName],[LastName],
      [MaritalStatus],
      [Gender],
      [YearlyIncome],
      [TotalChildren],
      [NumberChildrenAtHome],
      [HouseOwnerFlag],
      [NumberCarsOwned]
    FROM
      [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
    ') AS t
ON
  [TM_Clustering].[Marital Status] = t.[MaritalStatus] AND
  [TM_Clustering].[Gender] = t.[Gender] AND
  [TM_Clustering].[Yearly Income] = t.[YearlyIncome] AND
  [TM_Clustering].[Total Children] = t.[TotalChildren] AND
  [TM_Clustering].[Number Children At Home] = t.[NumberChildrenAtHome] AND
  [TM_Clustering].[House Owner Flag] = t.[HouseOwnerFlag] AND
  [TM_Clustering].[Number Cars Owned] = t.[NumberCarsOwned]

Example results: