irregular.bi blog

A Blog about BI and Data Integration

[irregular-bi]

Augment Intelligence with Graph Power

In my two decades of working in the Data Warehousing, Business Intelligence and Analytics field, nothing has bothered me more than being tied to a relational data model.

Read more about the it here: https://medium.com/neo4j/augment-intelligence-with-graph-power-878bce611a8f"

[irregular-bi]

Do my Tables associate?

Having my hands on the Quick Table Viewer extension for Qlik Sense recently to make it work for data fields from multiple tables, I encountered to this roadblock:

When creating a HyperCube from fields of multiple unconnected tables, a memory blowup and a potential crash can happen since Qlik’s engine (QIX) tries to create a cartesian product of the rows of all used tables.

Read more about the it here: https://medium.com/@irregularbi/do-my-tables-associate-dc249d59ee89

[irregular-bi]

SENSE DEEP DIVE 2018 (1) - LONDON

It’s a real pleasure to announce our second Sense Deep Dive will take place in London in March! And I’m very glad to meet the Qlik folks over there - some of you are friends already - not to mention the lovely Brexperts!

We have learned from the 1st Sense Deep Dive Berlin 2017 and we will split our Advanced Qlik Sense Extension Development Dive in two: one for beginners and one for experts!

I like to welcome well known Speros Kokenes the Visual Analytics Practice Lead from Axis Group who will run the Extension Development Dive for Beginners.

There will be more details soon on our website:

https://sensedeepdive.com/

So, stay tuned and register ASAP!

Don't know what I want. But I know how to get it!

[irregular-bi]

Multiple identities in Qlik Sense

Working with Qlik Sense extensions for a while, at least since 2014, I had to deal a lot with the sheet object’s ID you can obtain from layout.qInfo.qId in paint() function for classic extensions, or from $scope.layout.qInfo.qId in AngularJS extensions (I recommend)...

Read more about the object ID here: https://medium.com/@irregularbi/multiple-identities-in-qlik-sense-47083fda631a

[irregular-bi]

SENSE DEEP DIVE 2017 - BERLIN

I’m pleased to announce akquinet and TIQ Solutions are starting a new Qlik Sense Premium Master Class Series under the label Sense Deep Dive. Our first event takes place in Berlin at 27th November 2017.

So, first we take Berlin, then we take ... ?

“I'm guided by the beauty of our weapons..”

Feel free to suggest us next locations, content or speakers. UK and the US are already in our plannings.

Looking forward seeing you soon! I’m curious who will take what dive.

Register here for your attendance: Sense Deep Dive 2017 - Berlin.

[irregular-bi]

Analysis of Very Large Graph Data

TIQ Solutions started a R&D project for the visual analysis of very large graph data with InfAI. Read more about the project details here:

https://medium.com/@irregularbi/tiq-started-r-d-project-for-the-visual-analysis-of-very-large-graph-data-with-infai-1bcc8c0886de

[irregular-bi]

Helping the UN Explore & Predict Conflicts

I wrote a blog post on Branch about my Qonnections 2017 hackathon contribution. Please read the full post here:

https://branch-blog.qlik.com/helping-the-un-to-explore-predict-conflicts-7ecf16ae87e

[irregular-bi]

Graph visualization in Qlik Sense with Ogma

I wrote a blog post for Linkurio.us about my Qlik Sense Ogma integration. Ogma is the Javascript library for large-scale graph visualization and interaction. My example shows how you can analyse the connections in data with graph data analytics:

Link to the GIF:

https://s3.eu-central-1.amazonaws.com/irregularbi/tumblr/pics/OgmaQlikSense.gif

Link to the blog post:

https://linkurio.us/qlik-sense-graph-visualization-ogma/

[irregular-bi]

Qlik REST Connector behind a Proxy

The Qlik REST Connector is a young and versatile connectivity solution so it’s still lacking some features. One missing point is a proxy authentication so all your REST calls get blocked if your Qlik server (or desktop) is behind a proxy. Btw. Qlik Web Connectors already provide this proxy configuration option.

But there is help if your Proxy works with HTTP Basic Authentication. You can just use the HTTP header Proxy-Authorization within your REST connection.

Copy out this HTTP header from your browser session and use it in the REST connectior dialog. Alternatively, you can encode your proxy credentials (user:pw) yourself with Base64:

Example: user:pw = test:test

HTTP Header: Proxy-Authorization: Basic dGVzdDp0ZXN0

Now use the header in the REST connector dialog:

Then, test your load script and cross fingers.. ;-)

[irregular-bi]

Qlik Sense Widgets – my very first time

After doing Qlik Sense extensions for a couple of years now I was allowed to touch the Widgets – a user friendly concept of simplifing the creation of visual components for Qlik Sense - promising not to code, at least no JavaScript.

Well, I think it’s not. Might be true for some very simple vizzes with few data like KPIs or input controls like buttons or sliders. But, when it comes to more complex use cases and larger data sets you have to deal with limitations and rethink.

So, I started my first own widget library. It contains only one widget for now, a table widget to provide dynamic labels (and more), a missing feature in Qlik Sense:

https://github.com/ralfbecher/irregular-widgets

Fortunately, a Widget is nothing else than an AngularJS extension. Now it gets interesting here since we can use JavaScript code snippets in AngularJS expressions to access underlaying properties and data, also to add processing logic.

This is very simple: in the expression snippet we can access the widget’s properties we have defined thru the object settings and the widget’s data thru the object data. To investigate these object structures just add following console outputs to you template:

<qw-console log="settings" /> <qw-console log="data" />

Please have a look into the expressions in the template of the table widget to get an idea what’s possible.

What I haven’t thought would be possible at the beginning was to extend the HyperCube definition itself.

The widget’s underlaying HyperCube is defined with a fixed size of the initial data page in qHyperCubeDef.qInitialDataFetch to 10 columns and 50 rows and we have no access to adjust it. How can we exceed this limit if we need more than 10 fields? There are no properties in the panel or code artifacts we can use for these settings. In my opinion a clear lack of features here, but there is help. After a few investigations and a steep AngularJS learning courve I found a way quickly:

We can use the AngularJS ngInit directive initially to call the applyPatch method of Enigma - the new Qlik engine API module (still not released nor documented) - to patch options of the hyperCubeDef we need:

data.model.enigmaModel.applyPatches([ {qPath: '/qHyperCubeDef/qInitialDataFetch/0/qHeight', qOp: 'replace', qValue: settings.rowsPerPage.toString()}, {qPath: '/qHyperCubeDef/qInitialDataFetch/0/qWidth', qOp: 'replace', qValue: settings.maxColumns.toString()}]) settings.rowsPerPage.toString()},{qPath: '/qHyperCubeDef/qInitialDataFetch/0/qWidth', qOp: 'replace', qValue: settings.maxColumns.toString()} ])

(Watch out not to break the line of an expression. Not all browsers can handle this correctly.)

It’s probably a rude way to get things work and not the intension of the Widget concept but who said we need to be polite? However, I’m curious if someone else found a better and more conform solution..

With the understanding of the widget’s object model and how to use the AngularJS directives and expressions in the template we have all we need to start new Widget ventures!

Good luck! Hope to see some more new stuff out in the wild.

[irregular-bi]

Pretty fast Pattern Collector

Inspired by Barry’s great post “A very elegant link table script” about speeding up link table generation in Qlik by using the FieldValue function I looked for other use cases. Operations on the symbols of loaded tables in Qlik are blazing fast compared to record based operations (join, group by, etc.)...

Considering an ETL chain with Data Quality in mind one typical task is to check the data pre-load to discover issues (eg. unallowed formats or patterns in data, value ranges, thresholds, missing dimensional values etc.) to have an ability to stop loading processes in an early stage. Data Profiling is a usual task to solve these kind of problems.

This sparked the idea in me to create a Pretty fast Pattern Collector based on the above principle:

Looping over all fields and symbols of a loaded table to collect all formats and patterns from the data values

The performance of this simplified Data Profiling approach does not depend on the amount of records but on the amount of symbols: distinct values per field

The trade off is, you do not have a relation from the symbol to the record and so you cannot do aggregations like count, avg or sum, although min/max will work.

I was surprised about the load speed during my testings! Just try yourself and download the QlikView app here. The Load Script code will work in Qlik Sense also.

I’m curious what other use cases would exist for using the speed of Qlik’s symbol processing.

[irregular-bi]

Qlik Luminaries trip to Lund

It is certainly a desire of every enthusiastic QlikView / Qlik Sense Developer, once to be allowed to visit the "fabled" Qlik Labs in Lund to get a deeper insight into the software development of the great Qlik Data Analytics products.

Friday last week, it happens to me and other Luminaries: After an intensive planning and preparation by Viktoria Lindback (Global Customer Communications Manager, Qlik) and Torben Seebach (Qlik Luminary, itelligence DK) our journey  could begin in Copenhagen. Some Qlik Luminaries were attending the "Masters Summit for QlikView” event in Copenhagen the days before so we met on Thursday evening to a cozy sit down in the city. On Friday morning, then drove eight, partly far traveled, Qlik Luminaries in a minibus over the impressive Øresund Bridge from Copenhagen to Lund:

Alejandro Martinez (Spain)

Barry Harmsen (The Netherlands)

Karl Pover (Mexico)

Masaki Hanai (Japan)

Matthieu Burel (France)

Ralf Becher (Germany)

Rob Wunderlich (USA)

Torben Seebach (Denmark)

All luminaries were certain to found little green Oompa Loompas in the Lund Labs, but of course it turned out quite differently: We were welcomed in the Qlik Office Lund by Victoria Lindback and Henric Cronström (Technical Product Advocate), well known for his exciting Qonnections lectures on the "inner workings" of QlikView and its Qlik Design Blog articles.

Here we expected an exciting agenda, which was accompanied by Qlik representatives from the departments Research & Development, Product Management and Pre-Sales. Everyone presented its area of responsibility and discussed with us Luminaries about our product experience, product roadmaps for Qlik Sense and QlikView 12 and the Qlik Deployment Framework.

I was impressed by the serious and constructively discussion about our experiences and ideas. Many of our proposals have been agreed and will probably be discussed internally later. I am sure that our Luminary feedback is important for future improvement of Qlik products. Following our discussions, we have compiled a topic list, we would like to share with you:

Topic List

Our expectations of this trip were more than fulfilled. Many thanks to all responsible of Qlik, especially to the organizers!

In the afternoon we went back to Copenhagen. In Qlik Office Copenhagen - the office is located in the impressive National Football Stadium - then took place the first Qlik Developer Group Denmark / South Sweden meeting. It was held with a number of interesting lectures and an international exchange of experiences among Qlik Luminaries and developers. A nice ending to a great trip on which we were able to win new and interesting insights into the world of Qlik.

[irregular-bi]

New Timeline Extension for Qlik Sense

I’ve build a new extension for my presentation at the first QlikDevGroup.dk meeting in Copenhagen.

This extension implements vis.js timeline http://visjs.org/docs/timeline/. It is an interactive chart to visualize data items in time. The items can take place on a single date, or have a start and end date (a range).  You can freely move and zoom in/out the timeline by dragging and scrolling.

You can download and try it out here: branch.qlik.com

Update:

There is a new and supported version with more features on the Vizlib Marketplace

[irregular-bi]

Recent Extensions Work for Qlik Sense

During the last months I spend a bit of my spare time to build and improve extensions for Qlik Sense. My motivation is to learning how to leverage the new Qlik Sense APIs, to learn how to use JavaScript for data analytics, to understand the usage of visualization libraries like d3.js to create data driven documents, to give the new Qlik product some more use cases and also, of course, to have some fun with data gymnastics.

The following screenshots and links are some of them. Feel free to try them out and you’re welcome to collaborate via Branch or GitHub.

Two-Dimensional Heatmap:

It shows a matrix of colored tiles by two dimensions. You can use it to display two-dimensional measures as colored tiles (heat) to find patterns in data or to use it as kind of a switch-board for value selection.

HexBinnig:

This one, started by Brian Booden, can be used to show immense amounts of data points (+10k) like a scatter chart, but it uses hexagonal bins to cluster the values by two measures (x- & y-axis). Then, you can use a color or size of the bin to show the clusters relevance. Not only that, you can select a cluster by one click in the hexagon or use the lasso to select multiple. This truely can compete with the Qlik Sense built-in scatter chart, even with the new data compression. You can test it yourself..

This nice piece has evolved into an international collaboration started by Brian where I contributed to fix some basic but complex issues and to add new features. The latest cool thing was adding lasso selection by getting help from Speros Kokenes and integrating his d3 lasso plugin.

Word Cloud:

Started by Clever Anjos, I could fix some layout issues and added selection capability, So, now you can start with some text analysis if you prepare your data model the right way:

I hope you find this inspiring and it encourages you to dive into the new world of Qlik Sense capabilities. Just open your mind to imagine what else you could build to solve new analytical problems and to create new visualizations.

Have fun and contribute!

[irregular-bi]

Extending QlikView QVPR Analysis with a Graph Viz

Rob Wunderlich (http://qlikviewcookbook.com) provides this QlikView application:

QVPR Analysis is a tool that will load a Qlikview Repository (QVPR) and allow you to analyze the contents.With this app you can do Qlikview Analysis of the Reload and Distribution tasks defined on your server.

As an addition to it I created a QlikView application which binary loads QvprAnalysis.qvw and additionally loads some aspects (docs, tasks, users) into an adjacency list table to display it as a graph:

You can download my QlikView IconGrap extension and the application from my GitHub repo:

https://github.com/ralfbecher/QlikView_Extension_IconGraph

My QlikView IconGraph extension can be easily used for other use cases, too. Using graphs as an additonal layer to show and investigate complex dependencies in data will come more natural in the futire.

Let me know what do you think.

[irregular-bi]

Neo4j Meetups - Using Graph Databases in Business Intelligence

I'm attending the following two Neo4j Meetups as a speaker:

Amsterdam, 11.02.2015

Brussels, 12.02.2015

Stockholm, 03.03.2014

Oslo, 04.03.2015

The Business Intelligence world has been providing insight into many different data sources for years. However, most of the data sources have traditionally been based on relational databases. Recently, the NOSQL movement has also exposed other data sources, like our beloved Graph Databases, which now need to be integrated into the Business Intelligence toolset.

In this meetup, Ralf Becher will explain how graph databases like Neo4j can be an excellent complement to the BI toolset, where and how it could be used, and demonstrate some specific integrations with BI tools like QlikView / Qlik Sense and Tableau. After that, we will of course have plenty of opportunity to discuss and exchange ideas on this topic.

[irregular-bi]

AutoNumber got me, again

tl;rd: Yes, AutoNumber() created fields in QlikView script will save memory by omitting a symbol table. Surprisingly, storing these fields into a QVD file will create a symbol table for each field and take memory like every other field. If you do the full read on this post you will learn two other ways to create memory saving fields.

-----

After a confusion I created with my partly false statement in Oleg's performance session at the QlikView Masters Summit in Amsterdam recently ;-) I decided to do a short dive into the matter to clarify and to show you some usable insights.

QlikView has the ability to recognize fields containing consecutive integer values and to optimize the symbols out. That means no symbol table is created in memory for the field, only bit stuffed indexes are used on a record level. So, it's a very good way to optimize your QlikView application.

Worth to mention here is that it doesn't really matter if the consecutive integer values are created by AutoNumber(), RowNo() or from an INLINE Load.

Here is a QlikView script to show the three different possible ways to create a symbol-less field (a key):

//AutoNumber() and RowNo() both create consecutive values and therefore a field w/o symbols (key): Data1: LOAD RowNo() as rowno_key, AutoNumber(value) as autonumber_key, value; LOAD Rand() as value AutoGenerate 100; //This will break the autonumber_key (no consecutive value anymore): //Concatenate LOAD 101 as rowno_key, Floor(Rand()*1000) as autonumber_key, Rand() as value //AutoGenerate 1; //This way (and only this way) can create a consecutive value key "manually" like with autonumber by using Floor(): Data2: LOAD Floor(F1) as inline_key INLINE [     F1     1     2     2     3     4     4     4     5     6 ]; //No Floor() used therefore no symbol-less key created (it even stores number format as string in QVD!): Data3: LOAD F1 as no_key1 INLINE [     F1     1     2     2     3     4     4     4     5     6 ]; //Floor() used but no consecutive values therefore no symbol-less key created: Data4: LOAD Floor(F1) as no_key2 INLINE [     F1     1     2     2     2     4     4     4     5     6 ];

The memory usage of the created fields you can see in the memory file:

As you can see we have three fields created where symbols take no memory. I wonder if this will be happen if we load a autoincrement field from a database. I will figure this out in a next post.

Happy Qliking!

Download above QlikView example about AutoNumber: AutonumberTest.zip