The CData Power BI Connector for Adobe Target models Adobe Target objects as relational tables and views. The following sections show the available API objects and provide more information on executing SQL to Adobe Target APIs.

Stored Procedures

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

Tables

All Adobe Target data is displayed as tables based on the endpoint used to get it, and the object name inside the API's response. As an example, the "offers" endpoint on the API is directly translated into the Offers table that this driver creates. This is also true of Audiences, Propeties, and Mboxes as well. All of these tables contain every data point that the API would return if a user were to directly query Adobe Target.

API Endpoint Tables

These tables have their own specific API endpoint and have a 1-to-1 relationship between objects returned by the API and row results found in the ResultSet. To find any data point from Offers, Audiences, etc. you can perform a query like:

SELECT * FROM Offers

. Additional conditions, joins, etc. can be used such as in the below example, but they do not lower the amount of data transferred by the API since the filtering occurs within the driver.

WHERE Offers.NAME = 'Special Offer 1'

Activities

Data coming from the Activities endpoint follows a different set of rules because of the structure and amount of data present in the API response. The response contains information relating to Metrics, Experiences, and more, in an array that contains many data points and may repeat. This would make any Activity table containing that information non-normalized and very difficult to read. It is because of this that they were broken up into smaller and more manageable sub-tables (Experiences, Metrics, etc.).

The /activities endpoint provides a basic set of data including such things as id, name, and type, but does not contain all of the information necessary to define an "activity" or any of the sub-table rows such as Experiences or Metrics. To get that information we need to reach the id-specific activity endpoint that takes the form of "/type/activities/id" for each row in the activity(or activity-sub) table.

To understand this better, this is an example of the flow specifically for querying the Activities table:

1) Since the wildcard (*) operator would include columns that are not present in the /activities endpoint we must first transform the query so we can get the information necessary to query the more-specific endpoint

SELECT * FROM ACTIVITIES

2) These two data points are necessary to get the additional columns

SELECT * FROM ACTIVITIES WHERE id, type IN (SELECT id, type FROM ACTIVITIES)

3) The WHERE clause is executed first, and then the query is broken up into a series in this format. Each iteration of this series is represented as a row in the Activities table

SELECT * FROM ACTIVITIES WHERE id = %d AND type = %s

The information gathered from the above series is then formed into the ResultSet that is passed back from the driver.

Activity Sub-Tables

Similarly to the Activities table, the sub-tables such as Experiences or Metrics must get their data from the type-and-id-specific activities endpoint. The difference is that there may be multiple Experiences or multiple Metrics per Activity, so there is not necessarily a 1-to-1 relationship between an API operation and a ResultSet row.

These tables follow a similar activity flow: 1) Since there is no Experiences endpoint, the query needs to be changed to reflect the endpoint

SELECT * FROM EXPERIENCES

2) The driver will remember only to return the Experiences data and not the Activity data

SELECT * FROM ACTIVITIES

3) The driver needs to get the ID and Type in order to query the more-specific endpoint

SELECT * FROM ACTIVITIES WHERE (id, type) IN (SELECT id, type FROM ACTIVITIES)

4) This will repeat for all activities returned by step 3's WHERE clause

SELECT * FROM ACTIVITIES WHERE id = %d AND type = %s

Note: Conditions "activityType" and "activityId" can be used to target the sub-table data of a specific activity. ID and other filters will not affect the data returned by the API or change the endpoint hit since they will be applied by the driver client-side.