Connecting to Microsoft Advertising via SQL

You can use SQL to connect to Microsoft Advertising in the Data Virtuality Studio or the Code Editor in the Web UI.

Pre-requisites

You need a ClientId, ClientSecret, RefreshToken for gaining access to the API.

How to Connect

Before you start retrieving data, you need to run the connector script for Bing Ads in the Data Virtuality Studio or the Web UI and enter your credentials in the following lines:

SQL

CALL SYSADMIN.createConnection (
    name => 'bing_ads_src'
    ,jbossCLITemplateName => 'ws'
    ,connectionOrResourceAdapterProperties => 'ClientId=<Client ID>,ClientSecret=<ClientSecret>,RefreshToken=<RefreshToken>,redirectUri=https://login.live.com/oauth20_desktop.srf'
);;

CALL SYSADMIN.createDataSource (
    name => 'bing_ads_src'
    ,translator => 'bing_ads'
    ,modelProperties => 'CustomerId=<CustomerId>,DeveloperToken=<DeveloperToken>,CustomerAccountId=<CustomerAccountId>,multitenancy_tablename=<multitenancy_tablename>'
    ,translatorProperties => ''
);;

Example

Here is an example of a multi-tenancy table:

SQL

CREATE VIEW bingads.Credentials
AS
	SELECT 
		'<label_1>' AS label, 
		'<ClientId_1>' AS clientid,
		'<RefreshToken_1>' AS refreshtoken,
		'<CustomerId_1>' AS customerid, 
		'<DeveloperToken_1>' AS developertoken, 
		'<CustomerAccountId_1>' AS customeraccountid
	UNION ALL
	SELECT 
		'<label_2>' AS label, 
		'<ClientId_2>' AS clientid,
		'<RefreshToken_2>' AS refreshtoken,
		'<CustomerId_2>' AS customerid, 
		'<DeveloperToken_2>' AS developertoken, 
		'<CustomerAccountId_2>' AS customeraccountid
	-- You may add additional records using 'UNION ALL', making sure labels are unique
	;;