Qlik Healthcare User Group

13 Posts authored by: Dalton Ruer

This blog post is related to the overall General Hospital project and will be used to post links to examples, tricks and training related to Data Modeling using the General Hospital data set.


Training Videos:

Dalton Ruer

General Hospital

Posted by Dalton Ruer Nov 13, 2017

General_Hospital_Qlik_Healthcare_Solutions_Logo_1.jpgHave you ever had to continually generate fake data sets that you could use to perform training with, do workshops with, do demo applications from or use as examples when learning new features/functions/visuals?


Well that's pretty much the life of the Qlik Healthcare Solution Architect team. So we decided to solve that problem for me and any others that may face the same issue.


We utilized a combination of data generated by Mockaroo as well as other data sources to form the base files of a fictitious health system we are calling General Hospital.


The goal of the project is to grow this to include many other resources that surround it like additional data sets, workshops, and applications.


How can you play along? Glad you asked.


Want to see a walkthrough video of everything and how this can be used? Watch this session on the Qlik General Hospital: Qlik Technical Thursday - November.


If you want to practice your Data Modeling skills you will want to download the base CSV files. Click here to get the base CSV data set:


If you want to have some example code that pulls the base CSV files and implements various data modeling techniques to see one approach we have prepared a QlikView and a Qlik Sense solution you can download:

Click here to get the QlikView application that will load the base CSV files and create Layer 2 versions of the tables.

Click here to get the Qlik Sense application that will load the base CSV files and create Layer 2 versions of the tables.


If you want to skip the data modeling step and jump right into visualizing by using the layer 2 data, we have prepared both a CSV and QVD version of the data for you:

Click here to get the Layer 2 CSV files.

Click here to get the Layer 2 QVD files.


But wait there's more! We have already built very basic load scripts for you to load the Layer 2 QVD data files in both QlikView and Qlik Sense if you want to start there:

Click here to get the QlikView application that will load the Layer 2 QVD files.

Click here to get the Qlik Sense application that will load the Layer 2 QVD files.


But wait thanks to marmentrout5 we now have a SQL Server BAK file with the data so you can play in SQL Server environment.

Click here to to get the General Hospital data set in a SQL Server BAK file


Applications: As we create applications that utilize this data we will make them available here:

On Demand Application Generation - Click here to get access to the General Hospital ODAG application samples that were used as part of the March 15, 2018 webinar.


Just because we love you ... we have also created several image files that you can download to utilize for your own resources.

Click here to get the base General Hospital image

Click here to get the General Hospital Logo image

Click here to get an image of the General Hospital data model

Many who make requests seem to have a belief that Business Intelligence is magic. They loose their ability to listen to logic and reason and simply ask you to do the impossible.


Pulling data from 18 different sources, many of which that you don’t even have access to. Childs play like pulling a rabbit from a hat.

Turning bad into good and interpreting the meaning of the data. A little tougher kind of like making your stunning assistant float in midair.

Creating a readmissions dashboard. Hey we aren’t Houdini.

That data doesn’t even really exist. Oh sure it exists in the minds of the people who want you to produce it out of thin air, but I’ve yet to see a single Electronic Health Record that stored readmission data. They only store admission data, not RE-admission data.

Patient NameAdmission DateDischarge Date
John Doe1/1/20161/4/2016
John Doe1/7/20161/10/2016
John Doe1/30/20162/4/2016

Those who want dashboards for Readmissions look at data like the above and talk to you like you are insane because in their minds it is clear as day that John Doe wasreadmitted on 1/7, 3 days after their first visit, and was then readmitted again on 1/30, 20 days after his second visit.

You can read the complete article at my QlikDork blog site and I will share how Qlik's ETL will help you do the "data magic" necessary to build a Readmission Dashboard.

Dalton Ruer

It's not an Ugly Duckling

Posted by Dalton Ruer May 19, 2016

UglyDuckling.jpgI recently worked with others within Qlik to analyze a massive amount of data from ClinicalTrials.Gov using Teradata Aster.


One of the many analytical functions we ran calculated the probability of success for active trials based on numbers of factors.


As the Qlik Dork I naturally wanted to visualize the numbers in a way that was stunning. But to my dismay as I created the Scatter Diagram it was far below my expectations. In many ways I thought "wow that's an ugly duckling."


It was only after I dug in further did I realize that this chart was no ugly duckling, it was in fact a magnificent and incredibly intelligent swan.


As we progress forward in our industry in our ability to obtain and consume massive amounts of data our perspectives are going to have to change in terms of what is beautiful and what is ugly. By nature Qlik Sense charts are built to be able to display massive amounts of data. The question many of us face is "are we ready to behold their beauty or are we going to cast them aside because they don't look like what we've been using for many years?"



I'm really hopeful that this post isn't just me blabbering. That others will use to share and collaborate on their experiences.


What massive sets of data have you displayed?

How did your end users initially take it?

Any lessons you've learned in displaying thousands of data points instead of dozens?

What extensions have you begin using and how are you training your end users internally to understand them?

Ever felt a little let down when trying to implement Section Access and all of the examples focus solely on INLINE statements?


Ever wish for once you could see an example that was built using a database?


Well my friends search no longer your help has arrived. In this brief 5:30 video I demonstrate how to utilize a database to source your security reductions. Since we are going to be using SQL anyway we might as well do something slick like a UNION statement to build all of the necessary rows for accounts that will need "all" of the values. That way you don't have to actually worry about maintaining those values as your system grows. You gotta love that.


What's that you say? You don't have time to watch the video and just want to get the script code to do it. No problem, here you go:



LIB CONNECT TO 'Security Database (qtsel_drr)';









  FROM [QlikView_DemoData].[dbo].[SecurityTestValues]



from [QlikView_DemoData].[dbo].Specialties;





For that 1 person out there tempted to simply take my code and run it ... don't do that. You need to use your own database connection, your own database, your own table(s) and your own data.


I would love to know if this kind of thing is helpful for you.

There are lots of posts within the general Community site regarding Section Access. They will help you figure out how to limit a Doctor to seeing just their patients. You will also find posts regarding ways to use the OMIT keyword to ensure that a user can't see any patients PHI information or analysts can't see an employees SALARY information.


But what happens in the healthcare world when you want a physician to be able to see lots of patients but they should only see the PHI for their patients? Or a department manager should be able to see employees in other departments but they should only see the SALARY information for their specific employees? Being able to meet those kind of exacting specifications and needs is where Qlik earns it's marks. In these cases we need to forget about the word OMIT and simply make some slight changes to our data model and utilize just the data reduction aspects of Section Access.


For simplicity we will focus on just the PHI limitation but you will be able to quickly correlate to salary as well. Imagine we have a data table with patient information like the following:










    "Age Groups",


FROM [lib://HospitalDataDirectory/Patients Details Dalton.qvd]



The first thing we need to do to support our use case is separate the PHI field(s) into another table and force the EpisodeID field to be upper case so that Section Access will work:



    EpisodeID as EPISODEID,






    "Age Groups"

FROM [lib://HospitalDataDirectory/Patients Details Dalton.qvd]




    EpisodeID as PHI_EPISODEID,


FROM [lib://HospitalDataDirectory/Patients Details Dalton.qvd]


Now we have the infrastructure in place to construct section access in a way that allows users to see patients demographics without seeing their PHI or vice versa potentially. The following code is built for Qlik Sense and includes some basic internal users as well as my own login to ensure I don't lose access have access to ALL patients handled as you will see by listing all of the patient ID's. Doctor A get's access to whatever patients are listed in the A group and Doctor B gets access to the patients in the B group.





    ACCESS, USERID,                                 USERRIGHTS

    USER, ADMIN,                                         ALL



           USER, {my own login so I always have access},       ALL

    USER, USRAD-DRR\doctora,                    A

    USER, USRAD-DRR\doctorb,                    B


The next section of code that is needed is the association of the USERRIGHTS to the patients they should actually see and please keep in mind this is built by hand simply to demonstrate what can be done. In your system you will load this data from a SQL select statement or Excel file so don't get overwhelmed thinking about typing. For the user group ALL I've listed the 3 patients and the episode id's that match both of the tables above. In others words the users assigned to the group all can see the patient demographics as well as the PHI data. DoctorA who was assigned to userrights group A can also see all 3 patients but can only see the PHI data for the first 2 patients. While DoctorB has permissions to see only 2 of the 3 patients and they can only see the PHI for 1 of them.







    ALL, 'Pat00073','Pat00073'

    ALL, 'Pat00147','Pat00147'

    ALL, 'Pat00240','Pat00240'

    A, 'Pat00073','Pat00073'

    A, 'Pat00147','Pat00147'

    A, 'Pat00240',

    B, 'Pat00147',

    B, 'Pat00240','Pat00240'



Via the application here is what I get to see, what Doctor A sees and finally what Doctor B sees:





Section Access can be tricky business to begin with, and sometimes the requirements we face in the healthcare field just complicate it even more. Hopefully, this post will help you should you ever find yourself in a situation where you need to selectively show or hide field values based on the users permissions and it's not as simple as just using the OMIT keyword along with Section Access.


Note: Keep in mind the code for QlikView might be slightly different than above.

Dalton Ruer

A Bunch of Whiny Brats

Posted by Dalton Ruer Jan 28, 2016

Ever have one of those days where you feel like you are surrounded by a bunch of whiny brats?

No I’m not talking about your children (or grand children in my case.) I’m talking about your leadership team.

You beat your head against the wall to surface data from a cocktail napkin and merge it with 147 other data sources from database systems, Excel sheets and external data sources on the web and you make it work. You put all of the data into an amazing analytical application that is truly Functional Art that even Alberto Cairo would give you two thumbs up for. But without even so much as a pat on the back for the great job the first response is “We want something simpler. We already have Executive Portal can’t you just embed those charts into the site we are already have a link to?”

A bunch of whiny brats right. It’s just one more link to save to your favorites. It’s just one more application to learn. But noooooo they want to press the easy button because unlike you that has to learn 189 things per day to stay current they don’t want to change their delicate little processes.

Embedded Analytics

Well don’t be dismayed my friend there are whiny brats like that all over the world and the Qlik platform enables you to support them. I’m not joking. The Qlik API’s enable you to take the gorgeous work you’ve done and embed the KPI’s or charts directly into your existing portal and this quick 6 and a half minute video I show you exactly how to do that.


Ok now how could anyone could complain about this right? You can embed your genius analytical solutions right into the portal they use every day. You can embed Finance related data right into their Sharepoint page and it relates and allows interaction.

C’mon even your leadership team has to stand back in awe. Amazed at your skill and the innovation of Qlik’s platform to support that kind of functionality. Right?

Wrong! These are whiny little brats you are dealing with. Their first reactions are “That’s pretty nice but I don’t want to see the same 5 charts that Bob sees. I need to control my own dashboard because I’m the center of my universe.”

Are you kidding me??? They have access to key information on their mobile device from their executive portal and that isn’t enough?

No it’s not enough.

The reality is that your leadership team aren’t whiny little brats they are saavy business people who need to constantly push the threshold. They need access to the company data that has been kept from them for years. For crying out loud their mothers use Pinetrest everyday to “pin” recipes and come back to them whenever they want. Yet there you stand telling them that every time they want something added/removed from the portal they have to fill out a ticket request and wait for you to be the bottleneck in their accessing the information they need to do their job?

Self Service Dashboards

C’mon this is Qlik we are talking about. A company named by Forbes as one of the Top 10 Innovative Growth companies. Of course they can provide Self Service Dashboard capabilities. What do you think they are doing just helping you visualize data on your own workstation?

How simple can they make it? You know that Pinetrest site that has had “pins” pressed over 50 Billion times … yeah … they’ve made it that simple and in this short 4 minute video I’ve made it that simple for you to see how.


An Innovative Platform

“There are no dreams to large, no innovation unimaginable and no frontier’s beyond our reach.” – John S Herrington.

“There’s wa way to do it better – find it.” – Thomas Edison

Unless your leaders can consume it your companies data is not an asset it is a very expensive liability. Qlik is providing you a platform that allows only your mind to limit how you surface it. You have right now at your disposal the tools to surface your data via embedded analytics on your existing portals as well as allowing your staff to surface only the data they are actually interested in via their own personalized dashboards by simply “pinning” objects.

Just building data visualizations isn’t the answer. Presenting Actionable Intelligence in a way that can be consumed and acted upon is the goal. Now that you know what’s available it’s just a matter of whether you want to innovate the way data is consumed within your company or not.

We would all love to hear your thoughts on how your organization is moving away from the demand driven reporting to allow a more self service consumption. Just be really careful calling out the "whiny brats" in your organizations that are driving the charge. Be sure to check out other posts with my special brand of insight and ramblings via QlikDork.com.


Portrait of William Shakespeare

Portrait of William Shakespeare

At least it’s the question that we in the business intelligence community should be focusing on. Why weave my title so closely to one of the most famous lines by William Shakespeare?

Simple. Our ability to drive actionable intelligence relies heavily on our ability to weave a story around the data insights that we have discovered.

Discovering that we have 10 serious issues in our company and having $5 in your pocket will get you a cup of coffee at Starbucks. But being able to share the information about even 1 of those issues in a way that leads to actual change will put such a spring in your step that coffee will be unneeded.

In her fantastic book “Storytelling with Data” author Cole Nussbaumer Knaflic introduces two great phrases which really brought about great clarification to me. Exploratory Analysis vs Explanatory Analysis.

Exploratory Analysis are the actions that we take to do data discovery. It’s the drilling around. Poking under the hood. Using our human intuition to question the data. And the lights that dawn as a result.

Explanatory Analysis on the other hand is the art of being able to use the data to communicate a story that helps induce actions from those that have the power to make them. It involves our ability to use one of the oldest forms of human communication, storytelling, that has sadly become a lost art.

Emotional Call to Action

Storytelling can involve some very in your face kind of messages as a way to ensure that leadership has a call to action. For example imagine that we’ve spent a few days consuming clinical and financial data using a dashboard similar to the following that has multiple linked screens that we utilized to find an issue with a particular set of selections.


We could hold a meeting and put leadership to sleep showing them how cool our ability to navigate is or we can simply lead with a slide like the following that grabs attention.


You probably don’t want to use humorous sarcasm in your presentation to point the finger at a group but I think it works for this post as you kind of expect it from me. The slide includes enough details to insight some action and by all means include the actions you want to see taken. Of course you may have to prove your details and that’s exactly why the Storytelling feature in Qlik Sense is so valuable you can jump in and out of your story to do demonstrate the exploratory analysis you have done to support the explanatory analysis you are using in the meeting.


Perhaps your data doesn’t really require such an emotional tug to ensure action is taken. Perhaps all you are trying to do is provide some narration to help draw attention to help explain the data.

Consider the following chart before and after adding a few narrative elements are added to help the audience focus on the important things:





As I share on my About page I am far from an expert on any of the things I write about. I’m reading. Learning. Growing. Every single day just like you with the help of many others in the industry. Data is my thing and I own that. But I will be honest and tell you that providing narration for my stories is not something that comes naturally to me.

In fact the key points above … yeah I stole them. Well not actually stole them so much as I copied them to the clipboard and pasted them into my storyboard from what I think is one of the coolest new elements of technology that I’ve seen in a long time. It’s a narration extension for Qlik Sense that you simply tell which chart you want it to consider and it does the narration for you. That is a serious help to someone like me who is trying to learn how to help my audience understand the data that I’m presenting to them.

The fact that Qlik chose to construct it’s architecture using an Open API and the fact that anyone who can code can gain access to the patented Qlik technology while adding value through their secret sauce is what makes it possible for a group like Narrative Science  who is blazing trails in the field of natural language to build such an awesome extension.

The following video will let you see the narrative science extension in action. If you are a Qlik customer you will get all of the instructions you need and can download this exciting new object from this download location that includes instructions on how to install and has it’s own video that demonstrates it’s powerful capabilities. .


To achieve, or not to achieve action

There was a day when all we had to do in our field was surface data. Yeah those days have long since passed. Our jobs now entail not only finding the needles in the data hay stacks but helping our leadership teams understand them so that they can take action. I challenge you today to grow not only in the field of Exploratory Analysis but also in the emerging field of Explanatory Analysis.

Become a storyteller.

Add narration to your charts rather than just pasting them into presentations because you think they look pretty.

Use your newly developed skills to “incite action” and effect real change in your organizations.

Finally quit being selfish and keeping my tips to yourselves. For crying out loud start sharing these pages with others and help them find my QlikDork.com blog page.

Besides helping customers by day and being an all around Qlik Dork at other times I happen to have a very strongpassion for helping fastpitch softball players elevate their game. When I say elevate the game I mean getting over their greatest fears so that they can play the game like they OWN IT.
I have zero interest in spending hours of my life working with players on how to improve the minutia of their game (foot work for a double play, where to go to receive a cutoff, etc), that’s where their coaches and hours and hours and hours of practice come in. What I teach them to do is dive. Head first. All out. No fear. Diving aggressively with no fear. The change in every aspect of their game is so astronomically improved once they overcome that fear the rest of their game falls into place.  Click this link and watch the intro to one of my instructional videos to see what all out speed and a lack of fear looks like exploding through the air
You are still reading because you know me well enough by now to realize that there is a solid point to why I brought up what I do in softball. If you are going to set goals to improve it seems only reasonable that you figure out how to make the biggest impact with your time. Whether it is in the lives of young ball players, whether it is with your own actions or whether you are trying to improve quality at a health facility to help improve the health of your patients.

Clinical Quality Measurements

CompliancePercentageI recently had the pleasure of working with a large health system who wanted to focus on analysis of their Clinical Quality Measurement data. To set the stage they had 62.5 million quality measurement records covering 35 different measures across 8 systems and involving 511 practice groups and covering 2,241 providers.
Naturally we needed to illustrate some “dashboardy” type deal to reflect their starting point. They happened to be at 56.01%. Is that good or bad?
That my friends is a trick question. Starting points are neither good nor bad they are simply starting points. So as you consider your improvement efforts don’t judge yourself based on some myth in your head of where you should have been simply measure and report where you are. Then we look at going forward.
The next logical step of course is to begin analyzing the data. In a traditional report driven world we would ask for some details based on the different Compliancequality measures. So we did that we create a very simple chart that showed the name of the quality measure, the # of members involved (patients), the number of quality measurements taken for the measure as well as the % of the measurements that were compliant.
You know the typical stuff that emulates what you could get out of any $9.95 report writing tool. Then we sorted the chart in order of the % of records that were compliant. It’s where they were.
Naturally we also added the ability to change the dimension (Measure Name) to System, Practice or Provider.

Visualizing How to Improve

Naturally the purpose of the project was to improve their compliance percentages as an organization. So here is where my opening point comes in. What should they spend their time on? Who should they speak to?
The natural inclination for folks is to start with the worst on the chart and go from there. The compliance for “Seizure – New Onset” was at 30.18%. Again neither good nor bad, just where it was. So the natural, but wrong, inclination would be to start there.  My friends the biggest bang for the buck isn’t to charge down the halls trying to improve the compliance of a measurement that only has 45,255 out of the 62.5 Million overall records. So if we’re going to help them determine how to best spend the time of their valuable human resources I better create a visualization that actually does that.

Visualizing what Isn’t Right

The visualization that I believes help most with where to spend time is a Pareto Chart. Instead of looking at compliance percentages a Pareto Chart does the opposite it looks at what isn’t compliant. More to the point a Pareto Chart looks at each Quality Measure (or any dimension) and looks into how many non-compliant measurements it has versus all of the non-compliant measurements in the entire data set.
It also visualizes the cumulative effect as you proceed through the list. Sometimes graphics in posts are simply to add a little excitement like the girl diving, but in this case a picture is needed to really understand the tremendous impact of what a Pareto Chart can do for you.
In the image below you will see that “Colorectal Cancer Screening” by itself as a measure makes up 37% of all of the non compliant measurements. Why? If you look at the detail chart above closely it has over 15 million measurements for it’s members. It’s the biggest piece of the pie by a long shot. Followed by “Cervical Cancer Screening” and then “Diabetes Management.” The red line indicates the cumulative affect and you’ll see that if you focused your time on simply the top 6 of the 35 measurements you would be effecting a cumulative 80% of all measurements.

How did you do that?

That Pareto dealio is pretty powerful isn’t it? Kind of illustrates how to improve in a slick and easy method so I know you want to jump right into your systems and add it so the question you may be asking is “How did you do that?”
I start be creating a combo chart. The bars simply represent the total number of items that are not compliant divided by the overall total of non compliant items which is handled using simple SUM functions and the wonderful key word “TOTAL” that tells the system to ignore the dimension that the current row may represent. (The IsNotCompliant field is simply a bit field with a 0 or 1 value indicating if the measure was not compliant or not)

SUM(IsNotCompliant)  / SUM(TOTAL IsNotCompliant)

The cumulative line is simply the exact same expression and the clicking of the radio button that says “Full Accumulation.”
IFullAccumulationt’s really that easy.
It’s really that powerful.
The question now is simply “Where can you use a Pareto Chart to help your organizations ”
Meaningful Use? Absolutely!
CPOE? Absolutely!
What I love about this profession is that we have the tools that make visualizing how to improve so easy. What would take weeks in an old fashioned report writing and hours and hours of old fashioned human analytic skills can be created in a single chart that instantly identifies where people need to spend their time if they want to improve the numbers and not just measure the numbers.
Stop just reading and start participating. What visualizations have you used to accomplish the same challenges? What other areas do you think this type of visualization helped you or might help others? Need help implementing this in your environment ... just ask.
As always be sure to check out my blog at QlikDork.com and bookmark it so that you don't miss any of my posts and to show me some love via social media by posting from my site.
Dalton Ruer

Avoiding a Data Tornado

Posted by Dalton Ruer Dec 22, 2015
tornadoYou know I love to go out on a limb using data metaphors. Sometimes they are my own and sometimes I flat out steal them from others. (Imitation is the sincerest form of flattery you know.) I’ve wanted to continue my series on The Data Consumption Continuum for a few weeks now. But just writing my thoughts? That’s crazy. I’ve had to show great patience in waiting for just the right metaphor to come along to catch your attention and draw you in.  The “what in the world is Qlik Dork up to now” kind of lead. Recently inspiration struck as I came across this beautiful data metaphor “Data Tornado” from Tyler Bell.
In his post “Big Data: An opportunity in search of a metaphor” he introduces the concept as one of the major thought processes that surrounds data consumption in this great big data world we now find ourselves. He frames data as a problem of near-biblical scale, with subtle undertones of assured disaster if proper and timely preparations are not considered. (Don’t worry it’s not all doom and gloom he also introduces several positive metaphors but hey read those on your own time I’m trying to make a point here.)
We are at an age in the history of information where many analysts and businesses are begging for Self Service. Screaming if you will at IT “Just give me access to the data it belongs to the company I’m tired of waiting for you to write a report.” They are savvy and they know full well that the data is just sitting in a database or on a file share somewhere so why can’t they have access to it?
So why doesn’t IT want to just turn over the data and stop listening to the griping? Because the IT leadership team is worried about the Data Tornado that will ensue from all of these yahoos just randomly grabbing data and reporting 18 versions of the truth. You wondered where I was going with it didn’t you? And who can really blame them. You immediately understood the term “18 versions of the truth” because you’ve been burned by it in the past … multiple times.
DataFluencyYou can’t get any more succinct than Zach and Chris Gemignani in their book “Data Fluency” — “You can’t dump data into an organization and expect it to be useful. Creating value from data is a complex puzzle; one that few organizations have solved.” The answer to why not is found partly in another of their excerpts “The goal of a data fluent culture, in part, is to ensure that everyone knows what is meant by a term like customer satisfaction. A data fluency culture breaks down when people spend more time debating terminology, calculations, and validity of data sources rather than discussing what action to take based on the results.”

Enter Governed Self Service

Rest easy my friend. My post isn’t about the wide spread panic currently surrounding “self service” and that terms association with a “data tornado.” It’s about how to AVOID it. It’s about a new phrase you should repeat to yourself in the mirror a few dozen times until you begin believing your own facial expressions when you say it “Governed Self Service.”
The word “governed” seems to have negative connotations by many and those thoughts need to change. It doesn’t (have to) mean that IT is restricting you from accessing data. It can and should mean that IT is adding value to the data to ensure that the right data is used by the right people at the right times. They don’t want to be storm chasers or fire fighters dealing with the carnage after a data tornado has struck. Data Governance is a way for them to prevent the tornado in the first place by ensuring that you fully understand what you are surfacing.

Enter Qlik Sense

Self Service is a technology agnostic term. Many high quality tools are in the market that allow you to display data. Qlik Sense goes beyond the ability to display data and allows you to build in the governance that is so desperately needed to avoid data tornadoes and satisfy the well phrased concerns needed for a truly data fluent organization through the use of pre-defined Dimensions and Measures.
Imagine that we have a set of data that surrounds customers and the analyst needs to display a count of the customers. Easy enough … after we define what the term “customer count” means. If we are just looking at table that has customer demographics the count is obvious. But what if we are looking at a table of data that is all of the customer orders. Is the count the literal count that 100 customer (orders) were placed or should we display the unique count of customers so that we know we only had 76 different customers that placed those 100 orders?
Dimensions and Measures allow IT to build a framework of understanding to help analysts surface data in a way that avoids confusion. This screen shot illustrates how much metadata IT can add to a measure that can be used by an analyst in a way that ensures they use something as simple as a count correctly. You will see that the measure can contain a name, it shows the expression, it contains a description and holy cow it can even have tags associated that analysts can search for desired measures in a world where there might be thousands.


Enter Architeqt

As I’ve literally crisscrossed the country this year presenting to potential (and existing) Qlik customers they love this concept. But many in IT have begged for even more governance. “Dalton that’s great but Dimensions and Measures are only defined within single applications. What happens if we make changes? How can we apply changes across all of the applications? What if we need to add more as we develop more sources of data? After 30 years in the IT trenches I can do nothing but whole heartedly agree with them because maintenance is one of those things that IT considers but many analysts don’t.

No problem because that’s where Architeqt comes in. Architeqt is the framework for providing serious data governance across all of your Qlik Sense applications and is the brain child of Alexander Karlsson. It provides you the ability to create what he calls “Blueprints” which are the dimensions/measures/visuals that you need to share across all of your applications and then … oh this is so cool … use those blueprints in any of your Qlik Sense applications. And keep them in synch when you make changes.


There are many very small incremental steps that I’ve seen in my career. But my hat goes off to him because Architeqt isn’t one of those things. To me what Alexander has created provides the infrastructure that IT has been clamoring for. It provides them the assurance that they can maintain all of those vital formulas across all of the applications while still allowing analysts to freely access data. Combined with the ease of use of Qlik Sense provides to analysts to grab data and go forth with consuming data it finally provides a framework for … say it with me … Governed Self Service.

Exit Stage Right

While I would love to go and on with lots of additional information I know this is the right time for me to step off the stage and allow you to dig into Architeqt for yourself. Simply click this link and it will take you directly to this phenomenal new extension. The site will contain all of the information you need to download and configure this Qlik Sense Extension as well as a nifty You Tube video where you can see it all in action.

Like the thought process? Check out all of my musings at QlikDork.com

I’ve enjoyed my career in Business Intelligence but after seeing the following visualization which shows the amazing potential for earning profit in the home flipping business I think it’s time I became a real estate mogul.

FlipChartUnless you’ve been under a rock or you are probably aware of the blitz of television shows dedicated entirely to showing us how easy it is. The underlying needs for house flipping is the startup capital to make purchases with, and the keen eye of a designer to help you choose the right colors to slap on the walls. I’ve got like $12 saved up which is probably more than enough to get started and fortunately I’m blessed with a wife that has a great eye for design. If you aren’t as fortunate as me you may need to find a business partner and a designer who you will more than likely have to pay.


Getting started

As business intelligence professionals I think it’s only good common sense for us to get started by playing to our strengths … use analytics to help us make our home purchases. After all as advocates of actionable intelligence certainly we would trust our own life savings in our analytical hands. Right???


The first thing we would want to do is figure out what aspects of a home are most responsible for attracting the highest price. Those data science types call what we are trying to do a “multiple regression.” In real estate mogul language it means – “Hey dingbat before throwing all $12 down on the table to buy a home you probably need to know whether it’s the homes square footage or the lot size or the number of bathrooms or the number of bedrooms or the amount of taxes or the proximity to schools that has the most impact on the sales price.”


Multiple Regression

Not too hard to understand the importance that knowledge would have on our ability to turn a profit. But how does that data science multiple regression stuff? It’s simple you fire up R, load your data, you run the LM function and let it give you the answers.

Seriously it’s that easy. Here is how we would load our previous home sales data:

Housing = read.table(“C:/RealEstateMogul/housing.txt”, header=TRUE)

Then if we want R to tell us what the correlation is between the Price of the home and the Size (of home) and the Lot (size) we simply type the following

Results = lm(Price ~ Size + Lot, data=Housing)



Iterating combinations

R very well may tell you that there is a really strong correlation between the home size, lot size and the price. But unless you are lazy you would probably also want to know if there is an even stronger correlation. In other words is the size of the home and the number of bathrooms more important? Or perhaps lot size and number of bedrooms? In our case all we would have to do is go through every possibility of 2 variables. Then all combinations of 3 variables. Then all combinations of 4 variables. Then all combinations of … you get the idea.


As you can imagine it’s this manual coding of all of the combinations, this grunt work, that those data scientists don’t really enjoy. Fortunately as a budding tycoon I’m also a Qlik Dork and I have full intentions of using QlikView as well as R.


QlikView and R Integration

You see this is kind of the perfect use case for the QlikView and R integration. Not only do I want to be able to simply check whatever combination of variables I want to use, I also want to be able to filter the data and choose what is passed to R. That way I can verify the best combination of variables as well as confirm that the correlation holds true across time periods, across zip code ranges etc. Or I may determine the variables that are best suited to 30542 versus 90210.


Behind the scenes there are only a handful of lines of vbscript code behind the button that says “Run in R.” Basically it outputs the data from a table so that whatever you have filtered is put into a CSV type file. Then it calls R tells it to read the file it just output, then tells it to run the LM function using the variables you’ve checked and asks it to output the results to a file and then reads that data back in to QlikView so you can see the results. Including a scatter plot output showing relationship between all of the variables.



Some aren’t even aware that QlikView integrates with R. Others that do know figure “I’m going to do the modeling in R anyway and figure there really isn’t much that the QlikView integration can do for them.” Hopefully both types of people end up stumbling on this post. Feel free to nudge them by passing on the link. You see the beauty isn’t just that QlikView can call R. It isn’t just that you can check variables on a screen. You are more than free to write additional code that would literally iterate through every potential combination, and instruct R to write the results to filenames that match the combinations so that in 1 button press you get all of the results for all combinations.


So what? So what!!! The “so what” here is that so many of you out there are thinking “data scientists are seriously expensive and we can’t afford them in our company.” You are so right. You can’t afford to pay a data scientist full time to sit and iterate through every combination of your data. After all housing variables are mere child’s play compared to the massive amount of variables in healthcare for instance.


But you can afford to consult with one. You could have them build a model and then you simply use QlikView to iterate through all of the variables and then send them the output to review. Or what about that grad student in data science who has a few days in which to get some “real world experience” would QlikView’s integration to R allow you to take advantage of them?

Predictive Analytics is an important part of the overall data consumption continuum. The integration and what QlikView offers you sitting on top of R may be just what your organization needs to jump start your ability to reap huge rewards that predictive analytics offers.


As for me, it was fun using house flipping as a great use case to help me convey how to use predictive analytics. As you guessed though it turns out that $12 isn’t even enough to buy a gallon of paint to slap on walls. So I guess I’ll just have to continue doing what I love … helping others consume data.


Hungry for more on how the integration between QlikView and R can help you become a real estate mogul or better yet help you reshape how your organization views predictive analytics? Of course you are. Check out this post on my blog for several additional resources including a Qlik Dork exclusive world premier Yout Tube video.

We humans love to consume.Coneheads[1].jpg


And my personal favorite Chocolate.
You name it we want to consume it. Health warnings have little effect at deterring us from consuming mass quantities of the wrongs things. Yet sadly there is one thing that we need to survive and it seems that no matter how hard we try we simply can’t consume enough of it … DATA.
We need to consume data to survive in our own lives, as well as within our occupations. Yet try as we may we seem to be starving for it. I believe part of the problem is that unlike eating chocolate there is no “right” way to consume data. We humans are all over what I call the Data Consumption Continuum and can’t figure out how to accommodate one another’s ferocious appetite for this particular commodity.

Static Data

Reports are the most basic form of data consumption. Static reports have been around much longer than we have had computers and or a specialty field called Business Intelligence. They are a wonderful thing in situations where the subject matter doesn’t change. Whether it be workflow reports, ie lists that I mentioned in my last post we love to consume or whether it is truly static data like reports of what our Accounts Receivable was as of a particular date.
So there are good reasons to have the 3,209 static data reports your company has. But it simply doesn’t make sense to have business meetings end with the conclusion “Yep our company is losing money. Let’s all have a great week and enjoy the last days before we are unemployed.” Someone in the meeting is inevitably going to ask the “Who, what, when, where and why” questions. Thus report 3,210 ends up being born over the course of the next several hours, days, weeks or months.

Guided Analytics

Many including yourself may think of guided analytics as the polar opposite of static reporting. I’m not one of those people.
I believe guided analytics are a wonderful way to speed up the process of asking questions and getting answers. They allow you to “drill, drill, drill” until your heart is content. My posts have praised and demonstrated the many different ways that visualizations enable us as human beings to more rapidly perceive and consume massive quantities of this wonderful commodity we call data.
For some guided analytical applications are like a sudden speed reading course. The applications allow them to consume the data from 3,209 static reports in 1 sitting. For others it’s like handing their companies 0’s and 1’s to Michelangelo and suddenly a beautiful portrait of their company appears.
The definition of the word continuum is “a continuous sequence in which adjacent elements are not perceptibly different from each other, although the extremes are quite distinct.”
For all of the wonderful things guided analytics offer I see them as a mere single step away from static reporting for one reason. They only allow you to answer the questions that you already had in mind when the applications are built. If you think you will want to see how surgical costs relate to length of stay you build your application to contain that data. It’s great that we have the technology now that enables us to consume that many 0’s and 1’s at the same time, in really slick visual ways. But is it really so magical that we are able to answer the questions we had when we asked for the application to be built?

Self-Service Analytics

Hopefully you now understand why I’ve used the phrase “data consumption continuum” and why I believe guided analytics are but a single step away from static reporting. If you don’t then you stopped reading already, so those that are still tracking with me are ready to take another step forward on the human consumption continuum to self-service analytics.
I’m honestly baffled by the concept that self-service analytics are a way to allow end users to quickly visualize data from some a single data source. Woo-hoo look at the pretty pictures I can create for you from your XLS file or a single SQL query. Really??? That’s a leap forward in technology? I’ve never known a version of Excel that didn’t offer charting right inside the tool itself. If all you want are pretty graphics from a single data set just use the tool your data is in which is likely Excel.
I believe self-service analytics enables us to answer the questions that we didn’t have when we built our guided analytics solutions. It enables us to consume all of the data we built into our application and then begin consuming more data. Data that perhaps wasn’t even available when our guided analytics applications were constructed.
he important thing in my mind is that self-service analytics must offer the ability to consume new data as well as our existing data sources. When I get to the end of the road and can’t get answers just looking at my cost and length of stay data I need the ability to now consume readmission data along with that data not instead of it. To consume patient vital information along with it. To consume patient satisfaction data along with it. Whatever the new data may be self-service analytics should be an additive process. A step forward on a continuum of our consumption of mass quantities of data. One that allows us to grab the data and move on rather than having to go through a long requirements and prioritization exercise with IT.

Predictive Analytics

For many data science is very hard to understand. It seems that they think Data Scientists go into a room with their magic potions and terabytes of data and emerge with all of the answers to the company’s problems. That’s simply not the case.
Data scientists simply apply age old statistical formulas to data. The same data that we display in static reports. The same data that we display in guided analytics applications. The same data that we consume in self-service analytics. But they do so in a slightly advanced and more scientific approach.
You or I as mere mortals say “My spidey senses are tingling. I think there may be a relationship between our profit and the patient’s length of stay.” We ask for a report, we use guided analytics or perhaps self-service analytics and if we see even a minor trend we immediately jump to a conclusion there is a cause and effect, not just a relationship and we say “Aha I’m a genius! Quick change everything in our company I’ve found the problem.”
Data scientists say “give me the data for every variable we have and I will help you find the BEST correlations. The ones that statistically have the highest probabilities. The factors that actually lead to patients being readmitted for instance.”
That is a great thing. But it’s not like they simply run a magic statistical formula and come to the answers because that isn’t how statistics works. They methodically run formulas on different combinations of the variables. What about A, B, C, D and E? Nothing there so let me try A, B, D, E and F. Nothing there. Let me try this. Let me try that. Let me try the other thing. And they churn and churn and churn very methodically until months later they provide an answer. An answer that in the past could have been achieved with more people and more time.
So my assertion is that like the steps forward from static reporting to guided analytics and then to self-service analytics predictive analytics is a step forward simply in that it enables us to do things faster.
Each phase I’ve discussed allows us to take a step forward. A step that speeds things up. A step that allows us to consume more data. But the incremental steps are obvious to follow from one to the other. Yet if you look at how far apart static data is from self-service analytics you see that those extremes really are quite different. Static data reports identify the historical data we already had in our systems. While the result of predictive analytics is even more data that we can use and when combined with our current data can identify alternative actions we can take. Prompting us to take action rather than simply reporting to us. In other words a Data Consumption Continuum.
I have two reasons for this post:
  • To help you realize that wherever you may happen to be along the continuum in your ability to either produce or consume data you should consider the fact that the others in your company who may be at a different stage aren’t “dinosaurs” and they aren’t just “resistant to change”  and aren’t really that different from you. If you change your focus and understand the key concepts that differentiate the stages you will be better equipped to communicate with them so that you can both move forward as a team.
  • To help you realize that instead of looking at each phase as something completely different that requires it’s own tools you should consider thinking of implementing a data consumption “platform” rather than implementing new tools for each stage you progress.  A platform that enables you to surface your valuable data one time and reuse it over and over along the various stages.
Dalton Ruer

Crawl. Walk. Run. Fly???

Posted by Dalton Ruer Aug 21, 2015

CrawlingIn 30 years as a parent I can tell you I’ve had many memorable moments with my 2 amazing daughters. As humans, most of our memorable moments around babies involve movement. Let’s face it as cute as they are it gets old just watching them lay on their backs and smile. So when they first get the desire to start moving and can make themselves roll over we get excited. When they can finally control the movement of their hands and their legs and can crawl to us our hearts jump for joy. When they can take those first Frankensteinish steps to our waiting, open arms we get overwhelmed. Once they’ve mastered balance and movement and begin running we get to play our first games of chase and tag with them.



Crawling. Walking. Running. Memorable moments indeed. Those movements enable us accomplish so much. How much more could we accomplish if we ever learned how to fly. Don’t look at this post like I’m crazy … we’ve all jumped off the porch and flapped our arms wildly. Some of you jumped from heights higher than a porch attempting to fly and broke some bones in the process. You know who you are.



Yes there is a point to all of this. As the parent of 0’s and 1’s (data) for the past 30 years I’ve seen it crawl, walk and run and I’m beginning to see it fly. No I’m not talking about the Cloud, I’m talking about Self Service Data Visualization. I suppose I should back up a bit.


Those who work in IT, specifically those who work in data administration type roles work with data at least 8 hours a day. For most of us that is more time than we spend with our own kids. We have principles, best practices we follow to protect the data. In many ways we are more highly trained to protect the data than we are to protect our own children. So it should be of no surprise to you in business roles when you deal with IT folks that are a little over protective of their data children.




I just love checking off items in lists. I think we all do. There is a certain sense of accomplishment each time we check an item off. Which is why I think so much of Business Intelligence surrounds simply generating lists and static reports. Don’t believe me? The Checklist Manifesto: How to Get Things Right by Atul Gawande, is a best selling book that shows the power that simple checklists have in the complexity of our lives and how they can help us avoid failures. Kind of like allowing our “data babies” to crawl. We control how much of it goes out and to whom it is allowed to “crawl.” Don’t get me wrong there is a huge part of the workforce in many companies who rely on those lists to do their job processes. IT management also loves lists and static reports. They can easily be used to justify more head counts. If each report takes 1 week to build and you want 52 reports … boom 1 person year totally justified. Wait you want thousands of reports … “woo hoo” an entire data services team springs into being. You get to see your data. IT data workers still control the data. IT management grows its organization. It’s a win-win-win.




Sooner or later though executives start asking for more. They want KPI’s, Dashboards and Scorecards. For some these are the Holy Grail in a matter of speaking in the field. But when you really think about all of these, they are simply the next incremental step in growing up. All the check marks, arrows and circles simply tell them the answers to the questions that they previously asked. Unlike crawling though, I think this is kind of like taking the first actual steps because at least the pretty pictures allow them to consume the data faster than they could if they had to read 317 separate static reports. IT is still central in the process, they get more staff, they get more resources and now there is direct interface with executives. It’s an ever bigger win-win-win. Our data babies are growing up indeed. DataConsumption



More than once in my posts I’ve shared the concept that Analytics allows the end user to answer the question that they had in their head when they started, but it also allows them to ask the next question. Analytics allow business users to not only see issues but drill into them. Find the roots of successes or problems. So you probably aren’t surprised that I consider Analytics to be like running in terms of consuming data and I consider it a thing of beauty. Kind of like watching my granddaughter sprint across the soccer field and score a goal. It’s also a little scarier for those data parents in IT, because Analytics can’t be done without a lot of input from the actual business users and without a lot of learning about the business processes by the IT staff constructing the analytics applications. Our data babies sure have grown, and very rapidly indeed. Where has the time gone?




If you consider how much we can accomplish simply by running just imagine what we as humans could accomplish by flying. Similarly Self-Service Data Visualization has that same great potential compared to just crawling with lists/static reports, walking with dashboards or running with analytics applications. Unfortunately for those of us in IT this goes against everything we’ve been trained to do. Everything we’ve spent our entire career perfecting … controlling the data. Self Service Data Visualization means trusting others to do the right thing with the right data. Trust me when I say “that is as hard for data parents to swallow as it was for me giving my daughter’s hands in marriage.” But can you really blame IT for fearing that? For 30 years I’ve seen business users combine data from cocktail napkins, flat files, spread sheets and personal hunches then deliver numbers in a meeting that directly conflict with those before them. So have executives. Which is exactly why so many companies are stuck watching their data crawl, walk or run … but never get to see it fly.




So what’s the answer? In my humble opinion it involves a marriage between IT and the business community. We’ve all seen that in human marriages opposites attract. So why do we allow them to repel and work against each other in offices? IT has the staff to properly govern and protect the data to ensure a single source of truth. That has to be respected. The business community on the other hand has the knowledge about their processes that in most cases IT completely lacks. That also has to be respected.



Companies can continue to allow rebels do self-service from untrusted sources and continue to plummet to the ground as a result. Companies can continue to allow IT to completely control all access and enforce that all data requests have to be resolved by them and continue to plummet to the ground as a result. Or they can arrange a marriage between the two. One in which IT is trusted to provide a single source of truth data libraries where the business users can then serve themselves. One in which we see our “data babies” leave the nest and fly.



But hey what do I know, I think I’m the parent of a couple of trillion 0’s and 1’s.


You can read all of my musings on the subject of Data Visualization at QlikDork.com of course. Feel free to show me some love and spread the word via social media if you really want to.

Filter Blog

By date:
By tag: