The CData SSIS Components for Adobe Analytics models Adobe Analytics objects as relational tables and views. The following sections show the available API objects and provide more information on executing SQL to Adobe Analytics APIs.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to access additional capabilities of the Adobe Analytics API.

Views

Reports are shown as views, tables that cannot be modified. Unlike traditional database views, however, it is not very helpful to select all metrics and dimensions in a given table. Date is the default dimension for every report, so the query:

SELECT * FROM MyReport

will become:

SELECT Date, {all the metrics here} FROM MyReport

But if the query has criteria, then the default dimensions will be the dimensions used in the criteria, like:

SELECT * FROM MyReport WHERE Country = 'England'

will become:

SELECT Country, {all the metrics here} FROM MyReport WHERE Country = 'England'

Additionally, the Adobe Analytics API limits the number of dimensions you can request data for per REST API call to one. This means that the only way for the provider to generate reports with multiple dimensions is to divide dimensions into parts.
Let's take a query example:

SELECT Country, City, Visits FROM MyReport

• Firstly, the provider requests all the values of the first dimension, Country, suppose they are: England and Germany.
  
• Secondly, the provider tries to divine England into parts with the dimension City, and it gets, let's suppose: London, Liverpool.
  
• Finally, it divides Germany into parts with City and gets: Berlin, Frankfurt.
  

If we would have a third dimension, the provider would divide every city into parts with the third dimension (which means would make one request per city).

Now, imagine a four dimensional report where dimensions have many values. The generation of that report would require a lot of API calls.
However, the Adobe Analytics API allows us to provide some filters that can shorten the execution time of the query, tremendously.

Adobe Analytics API uses the following logical operators: 'AND', 'OR', 'NOT'
and also uses the following operators: 'MATCH', 'CONTAINS', 'BEGINS-WITH' and 'ENDS-WITH', which means criteria such as those below will be handled server-side:

... WHERE Dimension = 'Value'
... WHERE Dimension LIKE '%value%'
... WHERE Dimension1 LIKE '%value' AND Dimension2 = 'Value2'
... WHERE Dimension LIKE 'value%'
... WHERE Dimension1 = 'Value1' AND Dimension2 = 'Value2'
... WHERE Dimension = 'Value1' OR Dimension = 'Value2' OR Dimension = 'Value3'
... WHERE Dimension IN ('Value1', 'Value2', 'Value3', 'Value4')
... WHERE (Dimension1 = 'value1' OR Dimension1 = 'Value2') AND (Dimension2 = 'value3' OR Dimension2 = 'value4')

It is strongly recommended to use as many filters as possible, otherwise queries with many dimensions can take a lot of time.

Defining Custom Reports

To create a report view on a query, you can use the CreateReport stored procedure. The CreateReport stored procedure provides an easy way to generate new view definitions with a custom combination of Dimensions and Metrics. Calling it will create a new schema file that you can query like any other view.

The stored procedure takes a view name, a comma-separated list of metric names, a comma-separated list of metric ids, a comma-separated list of dimension names, a comma-separated list of dimension ids, and an output folder as inputs. You will need to set the Location connection property to the folder containing the new script files in order to access them; the Location can be set instead of the output folder.
You can get the metric/dimension Ids by querying Metrics/Dimensions views.

SELECT Id, Name FROM Dimensions
SELECT Id, Name FROM Metrics

For example, to use a new schema along with the default schemas, set the Location property to the db subfolder in the installation folder and make the following call:

EXEC CreateReport Dimensions = 'Country, City', Metrics = 'PageViews, Visits', TableName = 'MyCustomReport'