Welcome Guest, you are in: Login

QVSource: The QlikView & Qlik Sense API Connector (Docs)

NOTE -This Connector is now deprecated as it is replaced with this new version of the connector.


This connector allows you to download data from the Omniture/Adobe Analytics API (Adobe Marketing Cloud).

Usage

To use the omniture connector you should first enter your username, secret and select your environment in the screen shown below:

Image

You should find that if your credentials are accepted you can select the TokenCount table on the data tab and get a result back:

Image

Once you have verified this you should be able to start building your report definition.

One way of doing this is going to:
https://marketing.adobe.com/developer/en_US/get-started/api-explorer

Where you should see something like the following:

Image

You should then selecting the 'REST' version and enter your same username, secret and password.

You should then find that selecting the method:
Report.QueueRanked

Shows the following report definition template.


{
	"reportDescription":{
		"reportSuiteID":"<string>",
		"date":"<string>",
		"dateFrom":"<string>",
		"dateTo":"<string>",
		"dateGranularity":"ENUM( 'hour', 'day', 'week', 'month', 'quarter', 'year')",
		"metrics":[
			{
				"id":"<string>",
				"segment":{
					"type":"ENUM( 'visitor', 'visit', 'pageView', 'event')",
					"invert":"<boolean>",
					"operator":"ENUM( 'and', 'or')",
					"conditions":{
						"operator":"ENUM( 'and', 'or')",
						"conditions":[
							{
								"operator":"ENUM( '=', '!=', '>', '>=', '<', '<=', 'contains', 'not-contains', 'contains-all', 'not-contains-all', 'contains-any', 'not-contains-any')",
								"invert":"<boolean>",
								"element":{
									"id":"<string>",
									"classification":"<string>",
									"top":"<integer>",
									"startingWith":"<integer>",
									"search":{
										"type":"ENUM( 'and', 'or', 'not')",
										"keywords":[
											"<string>"
										],
										"searches":[
											"<<reportDefinitionSearch>>"
										]
									},
									"selected":[
										"<string>"
									],
									"expanded":{
										"rows":[
											"<integer>"
										],
										"search":{
											"type":"ENUM( 'and', 'or', 'not')",
											"keywords":[
												"<string>"
											],
											"searches":[
												"<<reportDefinitionSearch>>"
											]
										},
										"selected":[
											"<string>"
										]
									}
								},
								"values":[
									"<string>"
								]
							}
						]
					},
					"segments":[
						"<<reportDefinitionSegment>>"
					],
					"events":[
						{
							"id":"<string>",
							"conditions":"<<reportDefinitionSegmentEventList>>"
						}
					]
				}
			}
		],
		"sortBy":"<string>",
		"elements":[
			{
				"id":"<string>",
				"classification":"<string>",
				"top":"<integer>",
				"startingWith":"<integer>",
				"search":{
					"type":"ENUM( 'and', 'or', 'not')",
					"keywords":[
						"<string>"
					],
					"searches":[
						"<<reportDefinitionSearch>>"
					]
				},
				"selected":[
					"<string>"
				],
				"expanded":{
					"rows":[
						"<integer>"
					],
					"search":{
						"type":"ENUM( 'and', 'or', 'not')",
						"keywords":[
							"<string>"
						],
						"searches":[
							"<<reportDefinitionSearch>>"
						]
					},
					"selected":[
						"<string>"
					]
				}
			}
		],
		"locale":"ENUM( 'en_US', 'de_DE', 'es_ES', 'fr_FR', 'jp_JP', 'ko_KR', 'zh_CN', 'zh_TW')"
	}
}

You should be able to edit this to your requirements, the documentation here might be useful in terms of establishing the elements and metrics you need.

For example one possible simple definition might be:


{
	"reportDescription":{
		"reportSuiteID":"[YOUR REPORT SUITE ID HERE]",
		"date":"2012-06-26",
		"metrics":[
			{
				"id":"pageViews"
			}
		],
		"elements":[
			{
				"id":"page"
			}
		]
	}
}

NOTE that you can get a value to enter for the reportSuiteID from the ReportSuites table in the connector.

To test this, paste it into the 'Report Definition' field in QVSource:

Image

Then go over to the data tab and test the response:

Image

If this comes back as expected you can then copy and paste the load script into your QlikView application.

NOTE That you have two main options with regard to getting the actual report data:
  1. You can use the QueueAndWaitForReport table which queues the report and then tries immeditately, then after 250ms, then 500ms, then 750ms then 1 second to retrieve the report.
  2. You can use QueueReport which returns immediately and then use GetReportFromId subsequently in your load script to get the data.

If you are using option 1 above, you could then rewrite the load script as follows, essentially extracting the report definition into a separate variable that can then be manipulated and tweaked in the QlikView load script.

Here for example we illustrate how we could loop through the first 27 days in a month to extract a report from omniture for that day. But at this stage it is entirely up to you how you use the report definition and results.


Sub urlEncode(str)

	let str=replace(str, '%', '%25'); // should be first
	let str=replace(str, '#', '%23');
	let str=replace(str, ' ', '%20');
	let str=replace(str, '$', '%24');
	let str=replace(str, '&', '%26');
	let str=replace(str, '+', '%2B');
	let str=replace(str, ',', '%2C');
	let str=replace(str, '/', '%2F');
	let str=replace(str, '\', '%5C');
	let str=replace(str, ':', '%3A');
	let str=replace(str, ';', '%3B');
	let str=replace(str, '=', '%3D');
	let str=replace(str, '?', '%3F');
	let str=replace(str, '@', '%40');
	let str=replace(str, '[', '%5B');
	let str=replace(str, ']', '%5D');
	let str=replace(str, '>', '%3E');
	let str=replace(str, '<', '%3C');
	let str=replace(str, chr(10), '%0A'); 	// Line feed.
	let str=replace(str, chr(39), '%27');	// 39 Apostrophe
	
	call=str;
		
End sub

set errormode=0;
	
for i=1 to 27

	let vDefinition = '{' & 
		'"reportDescription":{' & 
			'"reportSuiteID":"[YOUR ID HERE]",' &
			'"date":"2011-09-$(i)",' & 
			'"metrics":[' & 
				'{' & 
					'"id":"pageViews"' & 
				'}' & 
			'],' & 
			'"elements":[' & 
				'{' & 
					'"id":"page"' & 
				'}' & 
			']' & 
		'}' & 
	'}';
		
	call urlEncode(vDefinition);

	Report:
	LOAD
		'2011-09-$(i)' as date,
		name as Report_name,
		url as Report_url,
		counts as Report_counts
	FROM
	[http://localhost:5555/QVSource/OmnitureConnector/?table=Report&format=qvx&ReportDefinition=$(vDefinition)]
	(qvx);

next

Advanced Example Script

This sample shows how to use the ReportSuites table to loop through all the report suites you have access to and extract data based on a certain report definition template.

Pay particular attention here to the loop: for j = 1 to 10

And the section where we build the report definition using this. '"date":"2011-10-$(j)",' &

This script is hard coded to loop through the first 10 days in October 2011 and should be tweaked to your needs.

Note that this date is also repeated in the Report load statement.


Sub urlEncode(str)
 
    let str=replace(str, '%', '%25'); // should be first
    let str=replace(str, '#', '%23');
    let str=replace(str, ' ', '%20');
    let str=replace(str, '$', '%24');
    let str=replace(str, '&', '%26');
    let str=replace(str, '+', '%2B');
    let str=replace(str, ',', '%2C');
    let str=replace(str, '/', '%2F');
    let str=replace(str, '\', '%5C');
    let str=replace(str, ':', '%3A');
    let str=replace(str, ';', '%3B');
    let str=replace(str, '=', '%3D');
    let str=replace(str, '?', '%3F');
    let str=replace(str, '@', '%40');
    let str=replace(str, '[', '%5B');
    let str=replace(str, ']', '%5D');
    let str=replace(str, '>', '%3E');
    let str=replace(str, '<', '%3C');
    let str=replace(str, chr(39), '%27');
     
    call=str;
         
End sub

OmnitureConnector_ReportSuites:
LOAD
	rsid as ReportSuites_rsid,
	site_title as ReportSuites_site_title
FROM
[http://localhost:5555/QVSource/OmnitureConnector/?table=ReportSuites&format=qvx]
(qvx);

LET noRows = NoOfRows('OmnitureConnector_ReportSuites'); // get the total number of rows in the table
   
for i=0 to $(noRows)-1 // loop through every row

	let id = peek('ReportSuites_rsid',$(i),'OmnitureConnector_ReportSuites'); // get the value for "text" field on each row

	for j=1 to 10
	
		let vDefinition = '{' & 
			'"reportDescription":{' & 
				'"reportSuiteID":"' & id & '",' &
				'"date":"2011-10-$(j)",' & 
				'"metrics":[' & 
					'{' & 
						'"id":"pageViews"' & 
					'}' & 
				'],' & 
				'"elements":[' & 
					'{' & 
						'"id":"page"' & 
					'}' & 
				']' & 
			'}' & 
		'}';
		
		call urlEncode(vDefinition);
		
		set ErrorMode = 0;
		// Some dates return no data so we still want to continue.
				
		Report:
		LOAD
			'$(id)' as ReportSuites_rsid,
			'2011-10-$(j)' as date,
			name as Report_name,
			url as Report_url,
			counts as Report_counts
		FROM
		[http://localhost:5555/QVSource/OmnitureConnector/?table=Report&format=qvx&ReportDefinition=$(vDefinition)]
		(qvx);
	
	next
	
next

Change Log

0.9.3.1 - 01/09/2014
  • Added Portland environment.
  • Added GetRealTimeReport table.

0.9.3 - 21/08/2012
  • Added Singapore - Production end point.
  • Upgraded to use version 1.3 in requests.
  • Report table renamed to QueueAndWaitForReport (old table name will be automatically mapped to this).
  • QueueReport and GetReportFromId tables added.
  • Performance improvements.

0.9.2- 01/06/2012
  • Upgraded to use Host.GetHttpConnector (rather than base.HttpHelper in APIConnectorBase which is now removed).

0.9.1 - 19/03/2012
  • IndustrialCodeBox_OmnitureConnector renamed to OmnitureConnector in generated load script.
  • Fixed tab order.

0.9.0 - 20/10/2011
  • Added ReportSuites table.
  • Now tracks API calls.
  • Minor performance improvements.

0.8.0 - 27/09/2011
  • Initial release.


(QVSource works with Qlik Sense as well as QlikView - See this page for notes.)
QVSource - The QlikView & Qlik Sense API Connector | © Copyright 2011 - 2016 Industrial CodeBox Ltd