DoubleClick Campaign Manager data is organized into various metrics (Clicks, CostPerClick, Impressions, etc.), which can be queried over various dimensions (Country, Month, etc.). Auto-discovering all existing reports for your DCM account is extremely expensive. To work around this, the adapter exposes five generic tables, one for each type of report, which allow for ad-hoc reporting as well as running your existing reports. There is also the option to create custom schema files for your existing reports so they can be queried as their own tables. These sections will discuss the different strategies for querying your DCM data with the adapter.

Built-In Reporting Tables

There are five built-in tables in the driver: CrossDimensionReachReport, FloodlightReport, PathToConversionReport, ReachReport, and StandardReport. These generic tables include all possible dimensions and metrics for the given report type but not all combinations of dimensions or metrics are valid in DCM. Therefore, 'SELECT *' queries to these tables are not permitted normally. Instead, you have several options of pulling data from these tables:

1. Ad-hoc reporting is available by running a query selecting any valid combination of dimensions or metrics from the table. For example:
   

   SELECT Campaign, CampaignId, Impressions, Clicks, ClickRate, TotalConversions FROM StandardReport

   This query involves a multi-step process. First, the report definition is created in DCM and the processing is started. Note that DCM reports can take a very long time to process especially if you have a large volume of data. Once the processing is finished, the report is downloaded and output by the adapter. Finally, the report definition is deleted from DCM.

2. You can supply a ReportID in the filter for the query to run an existing report. This query allows for 'SELECT *' but note that only the fields that are actually included in the report will be populated with data with the rest returning NULL. For example:
   

   SELECT * FROM StandardReport WHERE ReportID='123456789'

   The report is executed in real time so note that DCM reports can take a very long time to process especially if you have a large volume of data. The report definition is not deleted in this case.

3. You can supply both a ReportID and a FileID in the filter to download a previously run report. This query allows for 'SELECT *' but note that only the fields that are actually included in the report will be populated with data with the rest returning NULL. For example:
   

   SELECT * FROM StandardReport WHERE ReportID='123456789' AND FileID='987654321'

   Since the report has already been executed in this case, results will return immediately. The RunReport and CheckReportStatus stored procedures along with this method of querying can be used for asynchronous processing of the report in your application.

Existing DCM Reports

The adapter also offers the CreateCustomSchema stored procedure for creating new table definitions for your existing reports in DCM. The stored procedure takes a table name, the report ID, and an output folder as inputs. Calling it will create a new schema file that you can query like any other table. You will need to set the Location connection property to the folder containing the new script files in order to access them after they are created. For example:

EXEC CreateCustomSchema TableName='MyCustomReport', ReportID='123456789', OutputFolder='C:\Users\Administrator\Desktop'