QVSource SSL Issue And Fix

Yesterday morning it became apparent that our Twitter Connector was broken and there were also issues with our Facebook Connectors.

We immediately posted regarding the issue on Twitter's developer support forum and soon discovered that it had been caused by Twitter turning off support for SSL3 due to the recently discovered POODLE vulnerability in SSL3 (see here and here).

Further, it turns out that Facebook (and likely many other services) have also turned of SSLv3 support (necessarily, without being able to give much notice).

Unfortunately, there was some (unnecessary) code in QVSource which set our SSL mode to SSLv3 only (rather than the default of SSLv3 or TLS) whenever an OAuth1 request is made via the Twitter Connector.

What This Means

If you are using a version of QVSource lower than 1.5.6.0 then:

  • Twitter Connector requests will now be failing.
  • Facebook Connector (Fan Pages or Insights) will likely be failing (if QVSource has also made at least one request to the Twitter Connector before any requests to Facebook have been made. If the Facebook requests were made first then they will likely still work).
  • Other Connectors may also be failing.

We have a fix now which we will push out officially soon via a newsletter and blog post highlighting the other updates. However if you read this and need it immediately please email us.

Lastly, we apologise for the inconvenience. We were able to supply a fix to customers who noticed the issue internally within hours of being aware of the issue. We value the relationship we have with our customers, partners and trial users and welcome any feedback you have with how we have dealt with this unusual incident.

Quick Analysis of Videos on YouTube using QVSource & Qlik Sense

Important Update (15/10/2014): This blog post and associated application was written using Qlik Sense Desktop version 0.96.0. Since this release, Qlik have introduced a 'feature' where the default load script behaviour is to prohibit making load requests from arbitrary URLs. This means that a technique that we discuss below using variables to build a URL isn't possible unless you make a change to a Qlik Sense configuration file to enable it. We've put together a page that explains how to enable this and we are also actively engaging with Qlik to understand how this situation might improve.

Over the last few months Qlik has had some key releases; on the 24th of July Qlik Sense was released, followed by the full Qlik Sense server product on the 17th of September. Finally, at the end of September, Qlik Cloud was announced.

So, we've put together this short tutorial to show how you can use Qlik Sense with the QVSource YouTube Connector to build a dashboard that looks at videos on YouTube that have been published relating to Qlik Sense, since it was released in July.

(As a reminder, QVSource does work already with Qlik Sense as discussed previously and we are also working on a new improved browser based version which will make it even easier to use in the furure as discussed here).

Getting Started with Qlik Sense

There are lots of tutorials (and videos it seems) showing you the basics of using Qlik Sense, so we won't go into too much detail here, but you should be able to see how easy it is to create a QVSource-based dashboard.

So, to begin, launch Qlik Sense and on creating a new app and giving it name you're presented with 2 options to load data:

Pick 'Data Load Editor' and you will be taken to the load script editor.

The QVSource YouTube Connector

Now launch QVSource and from the list of connectors, double click on 'YouTube Connector (v2)' so that it can be configured. 

Firstly, authenticate the connector with a Google account and then in the 'Search Term' box enter "Qlik Sense". When performing a search on You Tube you can pick up some noise because towards the end of the results they become less relevant, so we can tighten the search a little by additional parameters. In the 'Additional Query Parameters" box, we enter "?Category=Tech" so that only video that are under the tech category will be returned.

Next, you can go to the 'Connector Data' tab in QVSource and from the list of tables, select 'Video Search'. QVSource will now query YouTube's search for "Qlik Sense" videos that are categorised as 'Tech' and present them in the table on the right:

The results on the right show a row for each video and there's a wealth of information available, from the author's name and description to the number of views and duration. Now we've got these results we can get some script to add to our Qlik Sense application - to get this we go to the 'QlikView Load Script' tab, where we'll be presented with the script. Click 'Copy' at the top.

You can now go to Qlik Sense and in the load script editor, after the declarations at the top, just paste in the script from QVSource and click save.

So, what have we done? We've simply pasted in some script that loads in a table, by requesting the data from QVSource, the details of which are defined by the parameters. So, if you look at the next image, which is a simplified view, you'll see the search term 'Qlik Sense' and Tech category are passed in:

The last thing we need to do to this table is only load videos that have been published since the 24th of July, which is when Qlik Sense Desktop was released. To do this we add a 'where' clause to the end of the table's FROM statement:

Finally in QVSource we want to get all the comments for the published videos. To do this in QVSource we follow similar steps to the search term - but this time we take a 'VideoID' from the previous results and use the 'Comments' table, copying the script into Qlik Sense.

So, copy a VideoID from the search results:

And paste it into the Video ID input box on the connector's configuration panel:

Finally, pick the 'Comments' table then copy and paste the load script into Qlik Sense:

Using Variables and For...Loops

Looking at the load script we've just pasted in, you'll notice that the VideoID, highlighted, has a hardcoded value in it

Having a hardcoded value here isn't very practical, so we need to use a scripting technique where we loop through each of the video, peek in and get it' Video ID, setting it as a variable and using this in the load script.

Looking at the screenshot below, you can see how we do this. Firstly, we get the number of rows in the first results table and use a For...Loop to go through each of these. Next, we 'Peek' into the row and extract the VideoID and number of comments, setting them each to a variable. If a video doesn't have a comment we don't want to attempt to get it's comments as there's no point, but if there is one we want it. So, we add the variable and VideoSearch_videoID field to what we've pasted in - this helps create a relationship with the search results table. Finally, we add use the variable in the FROM statement.

(NOTE: QVSource also supports an alternative way of achieving the above which avoids for loops in the load script and can also be more performant - you can read about this here).

That's all the scripting done and doesn't take very long at all to do. So all that' left to do is just save and load the data.

Building the Dashboard

For those of you who are used to creating dashboards with QlikView, Qlik Sense does take a little getting used for example putting the dashboard into 'edit mode' and coming out of it. But having said that it's very intuitive and the grid layout does make it easy to get objects to line up.

The first table we add is a list of Authors, so from the list of charts we add a table to the sheet, selecting VideoSearch_author_name as the dimension then resizing and positioning it. Simple!

Next we want to show the top 5 Publishers along with the number of videos and how many views, which is really easy to do. From the list of charts, again we add a table and again add the VideoSearch_author_name dimension. Along the right edge we can add more columns, so we add 2 more columns with expressions - one to give the count of VideoIDs and the sum of the video views. To show just the top 5, in the right panel, select Author and change the 'Limitation' drop down from 'No Limitation' to 'Fixed Number', pick Top and enter 6 - although we want the top 5, we include a 6th entry for a total of the others.

Next we'll add a time-sorted bar chart showing the number of releases over time - this is simply setting the Dimension to the VideoSearch_published_qvdate field and add a count of the VideoSearch_id for the measure. One thing we want to do is highlight some of the peaks, so if there are more than 7 videos published in a day, we want to change the colour slightly - 7 is just an arbitrary number of videos. Anyway, to change the colour of a bar if it meets a certain condition is quite simple. On the right panel, pick 'Appearance' and then 'Colors and Legends' - flick the colors selector from 'Auto' to 'Custom' and from the drop down pick 'Expression'. In the expression box enter the expression, =if(Count(VideoSearch_id)>7,rgb(44,100,155),rgb(124,160,195)) i.e. if there are more than 7 videos show one colour, otherwise another.

To complete the dashboard we add a few more charts and objects and end up with this simple, but informative dashboard:

 

Telling a Story

So, what can we learn from this simple app? Well, 80 videos have been published - the biggest publisher of videos has, unsurprisingly, been Qlik with over 40 videos followed by Qlik France with 8 - then it's down to partners creating and publishing videos. From the bar chart you can also identify 2 peaks, which correspond quite closely to the official releases of Qlik Sense Desktop and the full edition of Qlik Sense. There have also been over 50,000 video views of these videos, along with 45 comments, 190 likes and just 6 dislikes. 

Of course, this is a very simple application and it could be expanded so much further - for example, you could use several search terms, show a lot more statistics and use our Sentiment and Text Analytics connector to examine the comments further.

So, hopefully you can see that with QVSource, you can easily open up a whole new range of data discovery and, not only that, but it works with Qlik Sense in the same way as it did with QlikView.

You can download this Qlik Sense application (.qvf file) on GitHub here.

If you'd like to try QVSource, then you can get a free trial, which includes all our connectors. If you've already tried QVSource, but would like an extension, please get in touch.

Qlik Sense Launched

It’s been a busy time for Qlik! Here at QVSource, we were delighted to see the announcement that Qlik Sense had been launched to market, and we have been following the positive coverage with interest. Qlik was hosting an analyst “unsummit” in New York at the time of launch, and on the whole, the analyst community has been very positive about Qlik’s new data visualisation platform.

And we’re very excited about Qlik Sense too – this is the first “device-independent, self-service visualisation and discovery” product available on the market. Essentially, Qlik Sense is the first offering to deliver self-service BI based on a server-side development and distribution model, empowering every user with the full capability to create, customize, or extend visualisations from any device, at any point in time. We also believe it's the first product written from the very start with mobile devices as first class clients.

As I mentioned in our blog post last month, we have been working hard on QVSource V2; a browser-based version of QVSource that will interconnect with the new Qlik Sense in an even more seamless way (note that the current version of QVSource does work with Qlik Sense already anyway). I’m pleased to say that development is going well, and we should have a demo version ready to share soon.

For many years, we have been working alongside Qlik to help make its BI platform the best connected on the market. QVSource allows businesses to analyse the impact, not only of social media, but also many other channels, on revenue stream performance, and feed that business critical data into both QlikView and Qlik Sense.

Below, I’ve highlighted just a few of the problems that we help our customers to solve, but with over 30 connectors available, the possibilities for QVSource getting web-based data into QlikView or Qlik Sense are almost endless:

Facebook: Are my Facebook pages experiencing trends around user growth or demographics? How and where is content consumed? Can I compare brand to brand? Which are my successful pages and posts?

Twitter: How do I measure the value of Twitter for each of my brands? Who are the most active users and how does the sentiment change over time?

Google: How can I compare my company’s web traffic analysis with other business data, such as revenue performance? How can I see Google’s rich web analysis in a Qlik platform in real-time?

YouTube: How can I analyse the strengths and weaknesses of my company's video strategy? How can I review the financial performance of our YouTube channel?

Please contact us if you would like to know more, or check out the many customer success stories on our website. You can also try QVSource for yourself using our fully functional free trial.

Introducing QVSource V2 - Browser Based Version of The QlikView & Qlik Sense API Connector

We are really pleased to be able to publicly announce development of version 2 of QVSource!

The new version is being written with a html front end which means that QVSource will be web hostable, opening up a powerful new set of options for deployment and usage, particularly in conjunction with the new Qlik Sense and also for existing customers running the product on their internal networks.

Due to the way QVSource is built, very little rework should be required on the connectors themselves meaning that changes to the generated QlikView/Qlik Sense load scripts should be minimal to none.

The UI should also remain very familiar (although we expect that using html should open up new ways of making the interface more clean, intuitive and helpful) and this means that the learning curve for users transitioning to the new version should be very small too.

Below is an early screen shot (running on an iPad!) and we are really excited about the future for QlikView, Qlik Sense and QVSource.

If you are an active QVSource user and would like to be involved in providing testing and feedback of the new version we would love to hear from you.

QVSource 1.5.4 Now Available

We have just released version 1.5.4 of QVSource - The QlikView and Qlik Sense API Connector. This blog posts outlines the main changes.

As always, we would also recommend you read the notes on the "Change Log" tab of any connectors you are using for a comprehensive overview of changes.

Qlik Sense

Firstly, we'd like to make another quick mention of Qlik Sense, Qlik's exciting next generation in self-service visualisation and analytics, which became available for download this week. You'll be pleased to know that QVSource will work with Qlik Sense - you can read our blog post about it here.

New Connectors

Google DoubleClick for Advertisers (DFA) - We have developed this connector to compliment our Google DoubleClick for Publishers (DFP). This is an early build, so we'd welcome any comments on it.

Breaking Changes Or Possible Breaking Changes

  • Sentiment Analysis & Text Analytics Connector V2 - Unfortunately we have had to remove Sentimental (formerly Chatterbox) connectivity from our Sentiment and Text Analytics Connector. This is because Sentimental have now removed their API from Mashape and as such we are no longer able to offer it either. If you were using this API from your applications via QVSource it will unfortunately no longer work.
  • Google DFP Connector (POSSIBLE BREAKING CHANGES) We have upgraded to v201405 of API (see release notes here for items which may affect you).
  • MailChimp V2 Connector - This connector has been updated both to the new QVSource layout style but also to use the latest V2 of the MailChimp API. Unfortunately this means that it is not backwards compatible with the previous version and you will need to regenerate your load script. The new version also includes a number of new tables, for example you can now see who's unsubscribed from a list. V1 of this Connector is now deprecated and will be removed in a future version.
  • Removed Connectors
    • V1 of the Facebook Insights Connector has now been removed after a long deprecation period - please upgrade to the much improved V2 of this Connector.
    • V1 of the MongoDB Connector has now been removed after a long deprecation period - please upgrade to the much improved V2 of this Connector.
    • V1 of the Google AdSense Connector has now been removed after a long deprecation period - please upgrade to the much improved V2 of this Connector.

Updated Connectors

We've made a number of changes to some of our connectors, including:

  • Facebook Fan Pages V2 Connector - This now reflects our new connector layout making it much easier to know what parameters each table needs in order to get the data. This connector will replace the existing Facebook Fan Pages Connector, so you should upgrade as soon as possible using the instructions here. V1 of this Connector is now deprecated and will be removed in a future version.
  • General XML/JSON/SOAP Web Connector - S/FTPListFilesAndFolders has been added to the connector, making it easier to work with those locations. Quick Intelligence, one of our partners, recently blogged about using the General Web Connector to pull data from a secure web portal, where this feature was used.
  • File Transfer (FTP/SFTP) Connector - We have updated a key component of this connector and it also now supports specifying port numbers. It also has new tables to list files and folders within a specified directory.
  • Google AdWords Connector - This has been upgraded to use the latest v201406 version of the AdWords API - you can see the API change log here for any updates which may impact or be of use to you.
  • Google BigQuery Connector - the timeout has been increased to 10 mins.
  • MongoDB Connector - This has been upgraded to use the very latest MongoDB driver. 

Download Latest

Where do I find this new release? If you are a QVSource customer or have requested a trial in the past you should see this new release in the personalised download link we should have sent you via email.

If you are new to QVSource you can download a fully functional free trial from our website.

QVSource Works With Qlik Sense

[EDIT: 8th October 2014 - It appears that in a recent Qlik Sense release, by default all reloads have to be done to a [lib:// .....] which does not support building URLs on the fly (e.g. with load script variables) - something which in our experience QlikView users do often with web based reloads (not just via QVSource). You can still enable the previous behaviour as explained here and here. We are also trying to input into this decision which we currently believe will impact a large number of Qlik users negatively.]

We are pleased to see that Qlik have just launched Qlik Sense Desktop. From their press release Qlik Sense Desktop is:

"a free version of its next-generation data visualization application that delivers a simple drag-and-drop interface for business users to rapidly create interactive visualizations, reports, and dashboards. Qlik Sense Desktop is the first commercially available release from the QlikView.Next project. It includes the Qlik patented data indexing engine to give users the unrestricted ability to explore their intuition and the relationships that exist in the data, uncovering the hidden associations and insights that may otherwise be overlooked."

For existing or prospective commercial customers of QlikView and QVSource (we do not have a version of QVSource for free or personal editions of QlikView or Qlik Sense at this point in time) we just wanted to re-iterate that QVSource is fully compatible with the new Qlik Sense platform and we have just put a step-by-step tutorial on our wiki which shows how you can load data from one of the many connectors into your Qlik Sense applications.

We are also prototyping a new version of QVSource which will look and feel more aligned with the new Qlik Sense product and hope to post more details on this in the future.

QVSource - The QlikView API Connector - has over 30 connectors to various online social media and business APIs, is the highest rated product on Qlik Market and built by the winners of Qlik's Technology Partner Of The Year Award 2014. You can grab a fully functional free trial here.

QVSource On QlikMarket - Still Number One!

We are delighted to say that after more than two years on QlikMarket, QVSource continues to maintain its position as the highest rated product, and is doing so by a significant margin with almost 60 five star reviews!

The chart below tracks the accumulation of these ratings since early 2012 when we were invited to be one of the first products on the then beta version of QlikMarket.

 

What we are particularly pleased to see is that the ratings are steady and consistent over time, which we believe reflects the way we have continually been both pushing the product forward with new connectors and other innovations. Following the success of winning the Qlik Technology Partner of the Year 2014 Award, we're focused on continuing to deliver an excellent solution and service to our customers helping them to derive even more value from their QlikView investment.

Below are a couple of sample quotes from the ratings - you can read more here.

If you would like a free trial of QVSource - The QlikView API Connector and highest rated product on QlikMarket, please just visit our website.

Brilliant...QVSource fills a very needed functionality for Qlik.
Innovative Product with Great Support....I feel QVSource is an innovative product that is unique in the QlikView market place, and it fills a valid need in the world of QlikView data connectivity and extraction.
Excellent tool and first class support...I love QVSource, plain and simple. I work frequently with open data and find QVSource a perfect fit to the visual capabilities within QlikView. The ease with which public and corporate API's can be accessed, configured and presented to QlikView is something which makes this product, in my mind, a must for any QlikView developer.
Excellent connectivity system...This has to be the easiest way of connecting QlikView to a whole stack of web data sources. The installation couldn't be easier. QVSource makes connecting to web services as easy as possible and writes the QlikView script elements to access the data for you. The support material is very comprehensive and the support response is quick and intelligent.
Great connector for Social Media...Great product and great team ! Easy to install and easy to use... The best way to load and manipulate all social media data in a single QlikView app.Love it :)
Works like a charm...Installed one more connector and it worked as well as the other ones. Great job

QVSource 1.5.3 Now Available

We have just released version 1.5.3 of QVSource - The QlikView API Connector. This blog posts outlines the main changes

As always, we would also recommend you read the notes on the "Change Log" tab of any connectors you are using for a comprehensive overview of changes.

New Connectors

  • Dropbox Connector: We have a brand new connector (beta) for Dropbox which allows you to list your files and folders, download file contents directly to QlikView as well as upload files to Dropbox via your QlikView load script.
  • Google BigQuery Connector: We have a brand new (beta) connector for Google BigQuery.
  • Sentiment Analysis & Text Analytics Connector:  We have added a new Regular Expression option to this Connector which allows you to perform RegEx analysis on any textual data in your QlikView application - you can see some examples here. Note that this is implemented locally in QVSource (it doesn't go via a Web API) so is very fast.

Updated Connectors

  • Twingly (Blog Search) Connector: We have an updated version of our Twingly Connector which allows searching for results from millions of blogs from around the world. With the new version you now get a small number of free API calls whilst trialling the connector and a larger number as a commercial user and there is no longer any need to contact Twingly separately for an API key.
  • Google Drive Connector: This now has a new table to return the contents of a file as XML (useful for example if you are hosting a QVD file on Google Drive).
  • Twitter Connector:  The Tweet Lookup table uses a new feature of the Twitter REST API which means it can now lookup up the data for up to 100 comma separated Tweet IDs per request (and 60 of these requests can be made per 15 minutes meaning ). We have also added the following trend related tables: TrendingLocations, TrendsForPlace, LocationsWithTrends. Finally, we have fixed a bug in the UserSearch table which was returning duplicated rows in some instances.
  • File Transfer (FTP/SFTP) Connector: FTPDownloadAndList and SFTPDownloadAndList tables have been added which list which files were downloaded and a FileCount column has been added to the simpler FTPDownloadFile/SFTPDownloadFile tables.
  • Sentiment Analysis & Text Analytics Connector:  We have added tables to support the new semantic API features from Repustate (Themes, Entities and Expansions). Please note also the old V1 of this connector is now deprecated and all users should move to the new version.
  • POP3/IMAP Mailbox Connector: We have added a new ImapMessageHeaders to allow you to access the emails headers.

Connector Fixes

Connectors Out Of Beta 

We've a few connectors that are moving out of beta, as such, if you're a customer and using these, please get in touch as they are now on our price list. 

Download Latest

Where do I find this new release? If you are a QVSource customer or have requested a trial in the past you should see this new release at the personalised download link we should have sent you via email.

If you are new to QVSource you can download a fully functional free trial from our website.

QVSource And Lintao Partner To Offer Turnkey Dashboards

We are delighted to announce that we have just partnered with Lintao who are working on a number of pre-built dashboards (to add to their existing LinPack offering) based on the award winning suite of connectors from QVSource.

Lintao will be starting out with a dashboard for Google Analytics analysis which should be available in a couple of weeks and you can see a couple of preview screen shots below!

You can see Lintao's own announcement here.

Industrial CodeBox Wins the Qlik Technology Partner of the Year 2014 Award

Qlik’s Annual Partner Awards Honour Outstanding Achievement and Innovation  

Worcestershire, UK – 16th April 2014 – Industrial CodeBox, a specialist in the design and development of QlikView API connectors, today announced that it has been awarded Qlik Technology Partner of the Year. 

The announcement was made at Qlik’s annual Global Partner Summit, Qonnections, April 6th – 9th, in Orlando. The awards recognize Qlik’s partner community for excellence in one of eleven different categories. “Technology Partner of the Year” is a new category for 2014. Industrial CodeBox, who has been providing QlikView integration-related products and services for many years, scooped the prize in the very first year. 

Industrial CodeBox was selected over and above the many other technology vendors that were present at the Qlik Partner Summit because its easy-to-use solutions have been designed to solve real business problems and make a real difference to the Qlik community. 

QVSource is Industrial CodeBox’s flagship product. It is a suite of over 30 connectors that communicate with a wide range of online services that aren’t natively supported by QlikView. QVSource allows customers to analyse the impact that these online services – such as social media and other channels – have on revenue stream performance, by interconnecting with the services and feeding their data in to QlikView. 

Darren Ball, Co-founder, Industrial CodeBox, commented; “Since we launched QVSource in 2011, we have received hundreds of online recommendations from QlikView users and partners, including being the highest rated product on QlikMarket, with over fifty 5* reviews.  We’re seeing fantastic opportunities this year with QVSource and our booth was buzzing with interest throughout the Qonnections Partner Summit. We are absolutely delighted that Qlik has formally recognised the popularity of our product, and the momentum we are generating in the market.”

Michael Foster, Strategic Alliances Technology Officer, Qlik, added; “Industrial CodeBox designs ground breaking technology that adds real value to QlikView’s business discovery platform. The Qlik global partner network clearly acknowledges this, and I’m really pleased Industrial CodeBox’s valuable contribution to the QlikView ecosystem has been recognized, by presenting such a hard working team with a well-deserved award.” 

More information:

###

About QVSource
With over 60 partners in more than 30 countries, QVSource makes QlikView the best connected business discovery platform available, allowing an analysis of the impact of social media and other channels on revenue stream performance, by interconnecting with online services, and feeding data that aren't natively supported, into QlikView.

Contact:
Darren Ball
Co-Founder
Industrial CodeBox
email@industrialcodebox.com

Page List