24.7.2. Using external data sources

<< Prev   Next >>

If one external data source contains both data from relational data sources and data from analytical data sources, they cannot be used simultaneously. It happens because one external data source can be connected to either a relational DBMS or an analytical DBMS at one time.

24.7.2.1. In queries

24.7.2.1.1. To relational external sources

External data source tables can be used as query sources. At this, the table of the external data source is described as follows:

ExternalDataSource.<source name>.Table.<table name>

Example:

SELECT
Products.Code,
Products.Name,
Products.Article
FROM
ExternalDataSource.DBF.Table.Products AS Products

In this example, the Code, Name, and Article fields are selected from the Products table of the external DBF data source.

Temporary tables can be used in a query to an external data source. At this, an attempt to generate a temporary table directly in the database related to the external data source is made. If DBMS does not support generation of external data sources, an exception will be thrown. The name for the temporary table, when accessed, is generated as follows:

ExternalDataSource.<Name of the external data source>.TemporaryTable<Name of the temporary table>

Example:

SELECT Name, ProductID
INTO ExternalDataSource.AdventureWorks.TemporaryTable.Rest
FROM & ValuesTable

SELECT Name, ProductID
INTO ExternalDataSource.AdventureWorks.TemporaryTable.Rest
FROM ExternalDataSource.AdventureWorks.Table.Production_Balance

When you use external data sources in queries, keep the following limitations in mind:

• One data source can be used in one query. For example, an external data source and 1C:Enterprise infobase data cannot be used simultaneously.

24.7.2.1.2. To analytical external sources

Objects of analytical external data sources may be used as sources of queries. At this, the cube of the external data source is described as follows:

ExternalDataSource.<Name of the source>.Cube.<Name of the cube>

Example:

SELECT FIRST 10
AdventureWorks.InternetSalesAmount
FROM
ExternalDataSource.OLAP.Cube.AdventureWorks AS AdventureWorks

In this example, first 10 resources of InternetSalesAmount are acquired from the cube AdventureWorks of the external data source OLAP.

The dimension table for the cube of the external data source is described as follows:

ExternalDataSource.<Name of the source>.Cube.<Name of the cube>.DimensionTable.<Name of the dimension table>

Example:

SELECT
AdventureWorks DimensionTableProduct.Link
FROM
ExternalDataSource.OLAP.Cube.AdventureWorks.DimensionTable.ProductAS AdventureWorks DimensionTableProduct

In this example, the list of links to members of the dimension table Product is acquired from the cube AdventureWorks of the external data source OLAP.

Temporary tables may be used in the query to the external data source. At this, the attempt to generate the temporary table directly in the database related to the external data source occurs. If DBMS does not support generation of external data sources ‑ exception will be called. The name for the temporary table, during its calling, is generated as follows:

ExternalDataSource.<Name of the external data source>.TemporaryTable<Name of the temporary table>

When you use external data sources in queries, keep the following limitations in mind:

• One data source can be used in one query. For example, an external data source and 1C:Enterprise infobase data cannot be used simultaneously.

24.7.2.2. In the data composition system

In the data composition system, you can use data sets that receive data from various uniform data sources. Thus, you can generate a data set that describes a list of products. The data in this set will be acquired from the 1C:Enterprise infobase. Another data set can describe data acquired from an external data source, for example, a table of product sales from an external database.

Then you can set up data set links in the data composition schema wizard and get the information in the report from both 1C:Enterprise and the external data source.

<< Prev   Next >>