Skip to content

source-data-query.md

Latest commit

 

History

History
69 lines (52 loc) · 3.69 KB

source-data-query.md

File metadata and controls

69 lines (52 loc) · 3.69 KB
title <source data query> | Microsoft Docs
ms.custom
ms.date 03/02/2016
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
analysis-services
analysis-services/data-mining
ms.tgt_pltfrm
ms.topic language-reference
dev_langs
DMX
helpviewer_keywords
data sources [DMX]
predictions [DMX]
source data query element
queries [DMX], source data
external data access [DMX]
<source data query> element
training mining models
ms.assetid 9dce5e37-1354-4d28-87c2-f9c419cb5b09
caps.latest.revision 41
author Minewiskan
ms.author owend
manager erikre

<source data query>

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]

To train a data mining model and create predictions from a mining model, you have to access data that is external to the [!INCLUDEmsCoName] [!INCLUDEssNoVersion] [!INCLUDEssASnoversion] database. You use the <source data query> clause in Data Mining Extensions (DMX) to define this external data. The INSERT INTO (DMX), SELECT FROM <model> PREDICTION JOIN (DMX), and SELECT FROM NATURAL PREDICTION JOIN statements all use <source data query>.

Query types

The three most common ways to specify source data are:

OPENQUERY (DMX)
This statement queries data that is external to an instance of [!INCLUDEssASnoversion], by using an existing data source.

While OPENQUERY is similar in function to OPENROWSET, OPENQUERY has the following benefits:

  • A DMX query is much easier to write with OPENQUERY. Instead of creating a new connection string every time that you write a query, you can take advantage of the existing connection string in the data source. The data source object can also control data access for individual users.

  • The administrator has more control over how the data on the server is accessed. For example, the administrator can manage which providers are loaded into the server and which external data can be accessed.

OPENROWSET (DMX)
This statement queries data that is external to an instance of [!INCLUDEssASnoversion], by using an existing data source.

SHAPE (DMX)
This statement queries multiple data sources to create a nested table. By using SHAPE, you can combine data from multiple sources into a single hierarchical table. This lets you take advantage of the ability of [!INCLUDEssASnoversion] to nest tables by imbedding a table within a table.

To specify the source data, you can also use the following options:

  • Any valid DMX statement

  • Any valid Multidimensional Expressions (MDX) statement

  • A table that returns a stored procedure

  • An XML for Analysis (XMLA) rowset

  • A rowset parameter

See Also

Data Mining Extensions (DMX) Data Manipulation Statements
Data Mining Extensions (DMX) Statement Reference
Nested Tables (Analysis Services - Data Mining)