The CData Power BI Connector for Microsoft Ads models entities in the Microsoft Ads API as database views and stored procedures. These are defined in schema files, which are simple, text-based configuration files.

The following sections provide information on executing SQL to the available API objects. The connector offloads as much of the SELECT statement processing as possible to Microsoft Ads and then processes the rest of the query in memory; API limitations and requirements are documented in this section.

Views

The connector Microsoft Ads objects as Views, including reports.

You can query Reports based on an aggregation and time period. The default time period is this year; to change the time period, see the following Time Filter section.

When retrieving only some report columns, you should select all columns marked as 'Select Requirement' and at least one of the columns marked as 'Metric' to issue a valid report request. On the other hand, if you are retrieving all columns, then the driver defaults to including the minimally required columns in the request.

Specifying a Time Filter

Date Ranges

You can specify date ranges in the WHERE clause using the "Date" field with these operators: =, <,>

These predefined date fields are supported:

• Today
• Yesterday
• LastSevenDays
• ThisWeek
• LastWeek
• LastFourWeeks
• ThisMonth
• LastMonth
• LastThreeMonths
• LastSixMonths
• ThisYear
• LastYear

The default DatePreset value is "ThisYear".

To search on a predefined date range, set the DatePreset pseudo column in the WHERE clause to one of the predefined date fields.

SELECT Monthly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM [ AccountPerformanceReport ] WHERE DatePreset = 'LastYear'

To specify a custom date range, specify the StartDate and EndDate pseudo columns in the WHERE clause.

SELECT Monthly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM [ AccountPerformanceReport ] WHERE StartDate = '2014-01-01' AND EndDate = '2017-09-19'

Aggregation

Aggregation is the process of reducing and summarizing data. You can aggregate the report data by selecting one of the following aggregation columns:

• Daily: Each row of the report identifies the month, day, and year when the transaction occurred. The report data is aggregated by each day. The report includes a column named Daily that contains the day formatted as yyyy-mm-dd.
  

  SELECT Daily, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM AccountPerformanceReport

• DayOfWeek: Each row of the report identifies the day of the week when the transaction occurred. The report data is aggregated by each of the seven days in a week. The report includes a column named DayOfWeek, and the possible values are 1 - 7 where 1 represents Sunday and 7 represents Saturday.

  If the report time spans multiple weeks, then the performance data across all weeks for a given day of the week is aggregated in one row. For example, if Campaign A has 5 impressions every Monday (day 2) throughout each of the 3 weeks included in the report time range, then the report includes one row with DayOfWeek set to 2 and impressions in that row totaling 15.
  

  SELECT DayOfWeek, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM AccountPerformanceReport

• Hourly: Each row of the report identifies the hour when the transaction occurred. The report data is aggregated by each hour of the day. The report includes a column named Hourly, and the possible values are 0 - 23.
  

  SELECT Hourly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM AccountPerformanceReport

• HourOfDay: Each row of the report identifies the hour of the day when the transaction occurred. The report data is aggregated by each of the 24 hours across all days. The report includes a column named HourOfDay, and the possible values are 0 - 23.

  If the report time spans multiple days, then the performance data across all days for a given hour is aggregated in one row. For example, if Campaign A has 5 impressions during hour 7 on each of the 3 days included in the report time range, then the report includes one row with impressions for HourOfDay totaling 15.
  

  SELECT HourOfDay, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM AccountPerformanceReport

• Monthly: Each row of the report identifies the month when the transaction occurred. The report data is aggregated by each month. The report includes a column named Monthly that contains the first day of the month formatted as yyyy-mm-dd.
  

  SELECT Monthly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM AccountPerformanceReport

• Weekly: Each row of the report identifies the week when the transaction occurred. The report data is aggregated by each week. The report includes a column named Weekly that contains the date of the Sunday for each week formatted as yyyy-mm-dd.
  

  SELECT Weekly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM AccountPerformanceReport

• Yearly: Each row of the report identifies the year when the transaction occurred. The report data is aggregated by each year. The report includes a column named Yearly that contains the year formatted as yyyy.
  

  SELECT Yearly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate FROM AccountPerformanceReport

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.