Gaining further insights from your data is a must-have in today’s enterprise. Whether you call it analytics, data mining, business intelligence or big data – your task will still be to gain further insights from the massive heap of data. But what if your data has already been archived? What if your data now resides in your long term archiving platform? Will you be able to use it in all analytics scenarios? Let me demonstrate how easily it can be done if your archiving platform is OpenText™ InfoArchive (IA).
A customer recently requested a demonstration of integration with analytics/BI tools in a workshop we were running. The question asked was about the possibilities in InfoArchive to integrate with third party analytics engines? The answer is – everything in InfoArchive is exposed to an outside world in the form of REST API. When I say everything I mean every action, configuration object, search screen – literally everything. So we decided to use REST API for the analytics integration demo to the customer.
What Analytics/BI tool to pick? Quick look at the Gartner Magic Quadrant has some hints. I’ve been using Tableau with InfoArchive in the past so let’s look at another option in the Gartner list: Qlik. OpenText™ Analytics (or it’s open source companion BIRT) is my other choice – for obvious reasons. Let’s get our hands dirty now!
Qlik Sense Desktop seems to have a simple UI but there are some powerful configuration options hidden behind the nice façade. In Qlik to query a third party source simply open the Data load editor and create a new connection. Pick Qlik REST Connector and configure it.
The connection configuration screen enables you to specify the URL of the request, request body and all necessary header values. All you need for a quick test. Now that the connection is configured you’ll have to tell Qlik how to process the IA REST response. Click the “Select data” button in your connection and Qlik will connect to InfoArchive, execute the query and show you the JSON results in a tree and table browser. All you need to do is to pick the column names that you want Qlik to process as shown below:
Since the IA REST response columns are stored in name-value elements we have to transpose the data. This can be easily done with 20 lines of code in the Qlik data connection:
Table3: Generic LOAD * Resident [columns]; TradesTable: LOAD Distinct [__KEY_rows] Resident [columns]; FOR i = 0 to NoOfTables() TableList: LOAD TableName($(i)) as Tablename AUTOGENERATE 1 WHERE WildMatch(TableName($(i)), 'Table3.*'); NEXT i FOR i = 1 to FieldValueCount('Tablename') LET vTable = FieldValue('Tablename', $(i)); LEFT JOIN (TradesTable) LOAD * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; NEXT i
We’re almost done. Let’s visualize the data in a nice report now. Select “Create new sheet” on the Qlik “App overview” page and now add tables and charts to present your data. My example can be seen below:
Just click “Done” on the top of the screen and you’ll be able to see the end user view: browse the data, filter it and all charts will dynamically update based on your selection. Job done!
Continue reading on Page 2 by clicking below.