There are lots of reporting options and I watch SQL Server move up the Gartner magic quantrant, I saw another reporting tool moving up as well–Qlik. In this episode we will start by sharing information about Qlik, what it is and some background in the event it gets adopted at your company. Today we are delighted to have Michael Armentrout as our guest. Michael is a Microsoft SQL Server DBA as well as the developer of QlikView and we discuss the fundamentals of QlikView such as the associative model, in-memory, compression and sharing among others. Also we will hear from Michael the difference between QlikView and QlikSense, and some of the “competitors”.
Michael share some of his thoughts on using the technology and how it is different from the traditional Microsoft stack. We think you will find it interesting.
“I think a lot of others are starting to inch towards that in-memory model…it’s a new silver bullet.”
“The biggest thing is understanding of the data obviously.”
“‘No one will care about your career more than you, so it’s up to you to advance your career to whatever level you want it to be.”
Listen to Learn
– Qlik technologies/products
– QlikView functionalities
– Qlik products and their associative engine
– Differences between QlikView and QlikSense
– How they differ from the Microsoft Stack
SQL Server in the News
About Michael Armentrout
Michael S. Armentrout is a 17 year Sybase/Microsoft SQLServer DBA working in the legal, healthcare and financial industries including versions SQLServer 6.5 – 2016. The past few years I have focused, primarily, on Qlik development, while providing DBA support for a healthcare provider. Currently I on contract with Summit Healthcare, in Chambersburg, PA, providing QlikView services as well as DBA services. In my downtime I enjoy my wife, my four kids and playing guitar.
Transcription: Qlik Technologies
*Untranscribed introductory portion*
Carlos: Michael, welcome to the program!
Michael: Thank you! It’s an honour to be here.
Carlos: Thanks for coming on the show today and we’re hoping that you can give us some insight into this new technology that I’ve been seeing a lot about and that I’m actually curious about its name. Steven and I, we’re kind of talking about this and we were like quilk, click, click, clock. Tell us how you pronounce it.
Michael: It’s pronounced “click”.
Steve: Wow! That’s so easy to say that way compared to what we’re trying to do.
Michael: Actually I’ve seen it spelled many ways so…
Carlos: So ultimately the interest and I think one of the reasons we wanted to have you on, we’ve been talking a little bit about reporting options and we like to try to get around the edges if you will of the SQL server community from time to time. Just to talk about what others are seeing and what they might be experiencing. Last year as Microsoft, Eclipse, Oracle, on the Gartner Magic Quadrant, you could see where other technologies were in relation to that and so they continue to publish this out and one that I saw that was moving up the ranks was this Qlik Technology. And of course been talking with some folks, you being among them, that are listeners of the show but and then also users of this product, I thought, oh, ok, we should probably have the conversation because if people were being exposed to it, it’d be nice to at least talk about the what’s and the why’s and how that it might play into their environments. So I guess give us the nickel tour of what is Qlik and QlikView, give us the nickel tour.
Michael: Qlik, the company, was founded in 1993 in Sweden and the creators wanted to create software that mimics the way humans intuitively think which is through associations. So in 1996 they released the first version of QlikView.
Carlos: Is this a reporting software or…
Michael: It is a. So it’s ETL along with storage or EW and then producing UI/UX on the backend. So you don’t need an SSIS although you can leverage them but it’s self-contained.
Carlos: Interesting. So then it would connect directly to your SQL server box and then just kind of take care of the rest?
Michael: Correct. I mean there’s a way to load data, and it’s pretty much load column names from SQL and then a select statements. And it’s basic level.
Carlos: So tell us how you got started with it. I mean you were using some SQL server tools if you will. We talked a little about SSRS and whatnot. You mentioned SSIS. So how does an organization or maybe take us a little bit of history, how did you decide or what was the thought process like, hey, we should be using Qlik.
Michael: Originally, the company I’m at wanted to go the Microsoft BI Stack route. And then were very rural area so they learned pretty quickly that we’re not going to be able to staff up to leverage Microsoft. So we worked with another tender. We’ve had another product that worked with Meditech, our EMR, and we’ve developed an app for about a year and a half and it just didn’t pan out, it wasn’t robust enough. So they went back to drawn board and brought Qlik View back in or brought it in, excuse me. They were considered originally but…
Carlos: Didn’t make a cut, another, getting a second look.
Steve: So then, what type of things are you doing with QlikView or where’re you doing with QlikView in an environment?
Michael: Ton of clinical reporting but I guess now might be a time to sort of, pull back to covers, and maybe explain why the Qlik Technologies, what their advantage is. So they have an associative engine. So as it calls data in, it compresses it with
cardinality so once you like 47500 zip codes, 43000 zip codes, I believe, in the US. So if you have five million customers, you don’t store five million zip codes. You store for 43000 zip codes. Now if you have male and female, you store two values for that. Just got an example, we have an app that calls in the last 30 days of nursing orders. So in SQL Server that’s 557 MB. But when we call that in in Qlik View, it’s 27 MB. That’s same information.
Carlos: Interesting. There’s some compression going on.
Michael: Massive compression. And then both Qlik products, QlikView and QlikSense, we can get on the differences later, are all in memory.
Carlos: Got you. Interesting. We’ve seen some of that played out here as some features in the SQL Server realm. So this is interesting, QlikView may be one of those helping pushing Microsoft in that direction. Almost sounds like.
Michael: Correct. And I think a lot of others are starting to inch towards that in-memory model.
Carlos: Sure. Sure. It’s just so sexy, right. It’s in-memory, right.
Michael: It’s a new silver bullet.
Carlos: That’s right. That’s right.
Steve: So then with that, once you’ve got all that data in memory, and then earlier you mentioned building UI/UX, are you then using that to build just the reporting UI/UX or you actually building applications that can change and manipulate data?
Michael: Good question. So, the QlikView, the Qlik Products are sort of read-only. So you pull from your source and then ultimately the biggest, the thing that makes everything else easier is your data model which you shoot forward as a start schema. So once you bring that data in through your ETL and you manipulate it, you can do it in the frontend or on the ETL loads and there’s places to do that, and you bring it into a start schema. And so Qlik’s model is everything, is associated. Each data piece is associated with every other piece of data. So your tables, you have one column that they join on your primary key and it could only be one column. So then through the visual you will be able to see associations. So Qlik is known for their green, white, gray color scheme. So if you select a value in say, in the UI, and it’s green, that’s the slack value. So you might choose all males. And so you’ll have, on your other screens, you’ll have values that are white which means they are associated, and you’ll have values that are gray which means they are not associated. So it’s an associative data model.
Carlos: Just take an example a little bit further just to make sure that I’m understanding. Again, we’re talking about a medical facility here. So I’m kind of querying the data and I’m looking for patterns basically, right? So I want to know the males that have broken legs, just use as an example, or what other things might those people experience, and then it’s going to show that to me without having to build the report or build all the logic to show that data?
Michael: Correct. Absolute correct. So I just, I mean, I love SQL Server so what I’m about to say is not necessary a knock on it, it’s just a comparison. So query-based tools like, for example, Cubes, you sort to have a, you have to pre-build everything. You have to know what questions users going to ask, and you have to build answers to it. If they change something, you have to rebuild the cube, they have to wait a day, two days, whatever might be. So with query-based tools, you have pre-defined joins, pre-aggregated hierarchies, and its only part of the story. If you forget to add a column into your query or into your results or your cube or whatnot, and you’ve lost it until someone recognizes that and you go back.
Carlos: Right. You know, I think you bring up a very interesting point. And one that actually people have asked us to talked about and that is how the relational, the database landscape, if you will, in general, is just changing. And you have these
other database technologies that are coming up to help solve some of those exact problems. Because of the team of people, not generally, that have data warehousing experience, they put that all together, so to have tools that make it easier like I don’t need to be a data warehouse expert to get in there and start playing around my data is very attractive.
Michael: Yes. And that’s sort of the selling point and then whenever we get in the differences between the QlikView and QlikSense, what audience each of those serves.
Carlos: Sure. Yeah. So let’s get into that now.
Michael: Ok. Sure. So, QlikView is what they call, is what they term guided analytics. So you sort of, you build the UI and that could be scatter plots, bar charts, pie charts, pivot tables, there’s lots of objects within QlikView that you can build which is sort of guide the user. We kind of nudge them along and then tell them what you expose, they could go, “Oh, I didn’t know that males with broken arms, they all get some certain medicine or most of them get a certain medicine.” So unless you’re asking that question beforehand, you won’t know that in SQL.
Carlos: Right, got you.
Michael: So Qlik exposes those, QlikView exposes those through the UI.
Carlos: And then QlikSense?
Michael: QlikSense is what’s called, it’s their newest product, they’re morphing they’re web-based, cloud, it is what’s called self-service analytics.
Carlos: Aha, there’s another buzz term.
Michael: Yes. So in QlikSense, I mean, the syntax is, you could take QlikView syntax, drop it into a QlikSense application and it’ll work the same. You have a different sort of lay-out and constraints in your UI but ultimately what people would do, would build, is what’s called a master library of items. And these are, this’s when data governance comes in to. If the organization decides this is the definition of length of stay of point X to point B, or A to B, then they might build that and then users on the back end could theoretically drop that unto a chart that they want as a measure or as a dimension or whatever or how they want to use it. And then it ultimately ends up being self-service.
Carlos: Got you. Now, you’ve thrown out terms: dimensions, columns and joins and setting kind of some of the stuff, I guess take us through some of that and well, it’s been a while since you’ve used SSRS, you mentioned it in our, as we were getting ready to go live here. What are some of the hurdles potentially, or maybe some of the differences that you’ve seen in, what’s ramp up is more my question, to start using a tool like this?
Michael: The ramp up is, the biggest thing is understanding of the data obviously.
Carlos: So that doesn’t go away?
Michael: No, not at all. You have to understand the data and then for DBAs, for SQL people what was really hard for me was I wanted because it loads columns from SQL statement. And I wanted to write this big SQL statements and they could tell me just suck it all in and work it within QlikView, it’s much easier. And I’ve learned, they’re actually correct, it’s much easier. So just bring in all the tables you need and then you have to model it, you might do some clean up. Ultimately, I’m thinking appear some less. Everything is in one environment. You don’t have to go to multiple environments or use sort of multiple tools. I can have an SSIS package write on some table and your data warehouse that SSRS pulls from. You could theoretically do it all in one product. A lot of companies I guess are using this as sort of replacement for data warehouse. You could use Qlik as a replacement for data warehouse.
Carlos: Right, again, that self service model, just suck the data in let the tool figure it out and do some of those hard things for you; totally understandable.
Steve: So then with that if you’ve got, I mean there’s a listener out there who’s a DBA, or BI developer, or someone who wants to try it out for the first time. Is there a developer or trial edition or something like that that they could try out as a proof of concept?
Michael: Yes. Everyone can go to qlik.com and can download QlikView or QlikSense and it’s a fully functional, no limitations product. What you can’t do is I can’t create an application and send it to you Steve or Carlos and you guys open it up on yours and have it to be working, so that the limitation. But on your desktop it’s fully functional.
Steve: Ok, interesting. So then let’s just explore that comment you made about if you create an application and send it. So if you build an application in the paid version or the full featured version, you create an application that something you want to share with Carlos and I for instance, and would that application then contain a copy of all the data at the point you built it and you ship it off to us or would it be something where we would have to be on the same network with access to the SQL server and just be querying the data as it’s needed?
Michael: Good question. Again, we get back to both products, so I’m not aware of if I create a QlikView application I can share it with my organization because I put on the publisher server and then we have security and then it can be shared, or it can be accessed to any groups whatever your permissions are. I’m not aware of being able to share that with you. There might be some external phasing option but I’m not aware of it.
Carlos: So you think that should be a kind of shared repository, right, because even though the data is “in-memory” it could probably just bundled that up.
Steve: So when you talk about sharing then it’s really internal sharing inside of your organization?
Michael: With QlikView it is internal. With QlikSense they have QlikSense cloud and they have a business model which is more from enterprise side. So if I create an app in QlikSense I can upload it to my QlikSense cloud and I could invite you and Carlos to view it or to utilize it and you could, if you have QlikSense on your desktops or had an account on QlikSense cloud you could use it.
Steve: Ok, so then do you see Power BI being a direct competitor of this sort of they do different things?
Michael: They are competitor. They end up at a similar point just in a different way and I’m not super versed in Power BI. That’s one of the things on my list sort of like Tableau which is the other competitor. So I need to spend some time and just understand the differences so I’m not super versed in the Power BI stuff.
Carlos: Well, I think that answers your question. If Tableau is a competitor then Power BI is definitely the mix.
Michael: Absolutely, oh yes.
Carlos:I think there are different comparisons from Tableau to Power BI, probably with that strength, the association strength and again also just coming from Power BI guru but while it definitely suck in that data and you can play with it. We’ve talk with some of the folks about doing like mobile reporting you definitely want to limit some of that data it sounds like QlikView is a little but more robust in that sense. But yeah, I mean I would think that it’s kind of what tool other people want to get comfortable with and does kind of suit their needs.
Michael: Right, and it ultimately boils down to a culture is what I found if you have a, do I say, younger culture that is open to different delivery methods then it’s more accepted I think. I think when you have an older culture they go, “Wow, that’s really cool. Can you email me that every morning?” “Yes, we can.” But that’s not the point of it. The point is for you to go and you discover on your own to click around, no pun intended, but click around and make the associations and discover things.
Carlos: Got you. So that’s an interesting little tidbit you brought up. So you can schedule reports and kind of send snapshots if you will of, “Hey, here’s your data.”
Michael: Yes. They have a product called Inprinting and they purchased it probably about a
year ago and then having incorporated it. So that’s where you schedule, so you might have an entire application that may have 50 objects in it and you might schedule everyday at 8:00 AM at least five people get these three objects emailed to them. So there’s that functionality that’s built in to the products.
Steve: Okay, so on your comparison to Tableau you mentioned earlier, I know one of the things I’ve seen with Tableau is it’s often driven from the sort of a business side of an organization rather than the IT side of the organization, and do you see the same thing happening with Qlik where it would be somebody purchase it to analyze your data and then it sort of ends up in IT’s lap later?
Michael: In my n equals one experience, we say hey we have this tool that can do all these great things here are all the bells and muscles, and I go yes we want that and we want to do an application around, say sepsis. So then it comes back to IT, so in the Qlik view model, it all comes back I guess to IT, for your developers to create the solution. whereas they’re moving towards the QlikSense model, which is self-service. So still IT’s involved and we’re creating the master items that, you know there might be 50, could be 200, but users can then go in and drag and drop to the various bar charts or pie charts, scatter plots, whatever it might be and then we have predefined, agreed upon governed measures. So it’s an iterative process and it takes time to build a library like that, but that’s the ultimate goal.
Carlos: Give us a quick overview of the architecture. We’ve talked about it a little bit, right? Just from our components perspective, does it, do I have a Qlik view server and I connect to my SQL server and then it does the rest of it so like the matching of the data and the UI components. Give us an overview of that architecture.
Michael: Ok, so what I would do on my local PC, I would connect my data sources and I would bring the data in locally. I would develop a full application so the UI and that would be the modelling, and the ETL, the modelling, the cleanup, the UI, everything. And then what I would do is I would then pour that application to the publishing server and that exposes it out on our internal network and then those folks that have the correct AD credentials can see it, so that’s sort of security model, one of the security models with it. In QlikSense we have not purchased on enterprise level and I imagine it’s a similar concept. One of the things with QlikView is if I expose a QlikView application the end users can only consume it but that’s only they can do. Now in QlikSense there is the ability to basically download my application and they could create their own tabs or dashboards or what not. Sort of two products, the old school and the new school and they’re sort of morphing towards each other.
Carlos: Last question here, from a job perspective or a demand perspective, you mentioned you actually transitioning as we record, as well as your transitioning but it comes out you will be at your new location, you’ve put in a couple of years into learning this and you mentioned that you’re small mid-western town, are there opportunities or you see, when you decided to make the transition here, are you seeing opportunities you think this is still kind of a growth area?
Michael: It’s still a growth area but there are a lot of opportunities and I only said that based on the emails I received and it’s a lot of contract opportunities and then there, right exactly that’s where people reach out, so there is based on my emails there’s demand or normally QlikSense because that’s kind of what folks are pushing nowadays because it’s a new product. And one of the difference is where I mentioned earlier was QlikView you kind of build it and you design it for a resolution and it can be consumed on a mobile device but QlikSense is all HTML 5 and so I build an app and it renders in sizes whether on my laptop or on a iPhone,
on a iPad, doesn’t matter it’s all that.
Carlos: Yeah, it’s responsive?
Michael: Yes and so what Qlik has done is they build a base product which I keep brooding upon but they expose their API’s and so now there is a market growing for folks creating extensions. There’s certain kind of a chart that maybe QlikSense doesn’t come without a box, there’s a big market, boutique market if you will springing at creating different extensions.
Carlos: Interesting, now we should know and again, obviously you’re moving to a new place we can’t speak to that necessarily just yet but at least in your current experience and some of the discussions you’ve had as a Qlik developer it sounds like very similar to the SQL side while you can have some reporting experience there is still some administration components associated with it or I guess I should say in essence the database administration and the reporting are still kind of under the same umbrella or responsibility. Is that fair?
Michael: In my case, yes, because we’re a small shop so there are multiple hats. But I can’t say if that’s the norm. I’ve said there are larger shops that obviously might have a more definitive separation of duties. We’re like, “Okay, here, you know we love you and then SQL server here, DB data reader there, suck it all in and you want that into your app because you store those QVDs or data files compress on your sever not mine.
Steve: So from the perspective of the DBA who’s responsible for that server that all the data that is being pulled from are there any gotchas or anything that they should really be aware of as they’re allowing access to that data to be used through Qlik?
Michael: Yes and we learned this the hard way. So being both the DBA and the QlikView developer, we had a lot of projects we’re doing Agile methodology and so we’re relatively quickly cranking our applications, so everyone or the two of us in a vacuum created our own queries back to database. So what DBA’s should be aware of is the same queries coming back, so both of us might be pulling the last 30 days of orders which is bad in the sense of rather than doing a full sort of data warehouse model where every night there is a separate job that pulls in the changes in the last 24 hours and then we’re hitting that to pull our information. So that’s one thing I would sort of caution or with DBAs is to get that sort of governance and make sure it’s only pulling once.
Steve: So does that means that the different developers just have to sort out the data that’s being pulled in using the same data that’s there rather than writing their own queries to get it? Or is it more complex for the Qlik developer than that?
Michael: it really depends, so there’s nothing stopping each developer from pulling their own set of data in a perfect world you would have a governed set of data. So for example you might all agree that, okay here are the 30 fields that constitute patient data of any patient and so every night we’re going to go out and pull the changes into this one QVD which is our QlikView data file and then, “Hey, developers you’re going to write your applications against these QVD’s. You’re not going to hit the server, the SQL server 24 times, 20 times you’re all going to pull from this agreed upon governed set of data.” Didn’t go either way like currently we’re all just pulling what we need when we need it and it took contention at times.
Steve: Alright good stuff there.
Carlos: Alright, cool, I guess last words on Qlik.
Michael: I would say just like to give an example sort of that DBA’s might able to relate to. So you can create an app that goes out and pulls backup file and back up set, let say you look at the average size of the back up so what not. Well, back to our modelling so when you bring those two tables in they have about six or seven columns in common so as a QlikView developer what I would do is I would go in and rename the columns in one table so there’s one column that they have in common that’s sort of a key to this all, so like your back up set ID. So we do that and then I have unique data that’s joined by one column and then I start creating my UI pieces, so for example I have a bar chart, so what’s the average back up size for every database and creates a bar chart. Now, I can create what are called list boxes which is just a unique list of values, so for example database, gives me all the unique database names and then in our case there might be different compatibility modes. So I’ll have a list box for compatibility modes and so through the GUI or through the application I might say okay list all the databases descending and the upper size but how many are 100 compatibility level, so once I click 100 I can visually see all two of my database on this server or three of them are at compatibility level 100. So the other ones which are grey which means they are not associated, well they are not at that level, so even I may not have known all that got different compatibly levels but through a tool like everything is associated, again green, associated white and then grey is not associated. I can visually see that might lead me down to another path to ask more questions. That’s just one example that I tried created the sequel space that DBAs might be familiar with.
Steve: Yeah, I think that’s a great example on something that I’m familiar with and it makes total sense to that point. But do you find that DBA actually end up using Qlik to do that type of work or is that just more of a theoretical type example?
Michael: It’s probably more theoretical, I again n equals one unfortunately is my sample pull, so I use it for things like that just to kind of okay I’m going to quickly throw together these 2,3,4,5 tables, and I’m going to look for something, and then nine times out of ten I’m looking for something, I discover something else. So in the example I gave you another list box could be what type of backup was it, was it date or a log and I may click a database to see what it’s average backup size is and the L may turn gray and that tells me there’s been no log backups on this. Now there are other methods to figure that out but just you know you can leverage visually and go, “Oh wow I didn’t know that” then you take some action.
Carlos: I think what’s cool about that is and again we all have our preferred ways to go about that but if you have that skill and the tools there it will be very easy to go around and start like, “Let me start asking some of these questions that maybe I wouldn’t be asking because I have to go figure out what a column name is”, or whatever. I could just go in and start picking at it without having to read a lot of documentation or figure that out afterwards once I start seeing some correlations or whatever.
Michael: Right, so there’s definitely a learning curve in the syntax which is not difficult but you know there’ll be a learning curve but I want you to be comfortable with that, you can just bring data in. So for example, I created a, so Meditech is a old school 60’s, 70’s programming application but they put all the data every night to a SQL server which is the Meditech Data Repository. So a lot of the times the analyst who know Meditech inside and out don’t know SQL server. Now, I know SQL server but I don’t know Meditech inside and out, so there are tables within Meditech that map columns to tables within Meditech and can give you hints about where it came from. So I build an application off of two tables that brings in and maps them so if I want to search for a module like I want to see all the lab modules. Well in my application just click lab and it shows me all the lab tables and then if someone says it has to do with admissions or admitting time, well okay I can search admit and it brings columns or tables that share that name and then I can use that to sort of narrow down where to find data on the SQL server that they’re seeing in the application. It’s kind of a data dictionary if you will. It’s probably better with visuals.
Steve: Alright, any last thoughts as we wrap it up?
Michael: No, I just SQL server DBA, side base DBA for many years and stumbled across this and saw the power and once you get past couple months of the learning curve, being able to leverage the Qlik either View or Sense solutions to visualize data even just one person, a lot longer with entire organization that is all ordering up the same menu if you will and see the data the same way and gives them the ability to ask their own questions in QlikSense.
Carlos: Awesome, should we do SQL family?
Steve: So Michael how did you first get started using SQL server?
Michael: I was the typical DBA but I started in the side base world and did that for a couple of years and notice my other partners all had many more years of experience than me. So I had an opportunity at a company that was on Microsoft SQL Server 7.0 before it went public so we were sort of a beta site and thought, “Alright, that’s it easy transfer skills that’s relatively the same just with the GUI on top”, and made the move and been in SQL Server ever since 1999.
Carlos: Now if you could change one thing about SQL Server what would it be?
Michael: This was the toughest one. Nothing really major I guess like some minor things that I would probably change. Sometimes it irritates me when the different either size or time increments are in different values milliseconds, seconds, gigabytes, megabytes, kilobytes and just hadn’t do all the conversions gets to be annoying at times.
Carlos: Oh, got you. You’re being a little bit standardizing the some of the reporting data.
Michael: Correct, here’s the formula I have to use on every size field because they’re all in kilobytes. And then the one today we had an issue at work today with some deadlocks and keep going into the SQL Server through the GUI, went into the log and soon as you expand the error log it pops up and defaults to the SQL Server agent log, so you have to uncheck that and check the current SQL Server log, so just a little you know, no shows stoppers, but these kind of annoying little things.
Steve: What is the best piece of career advice that you’ve ever received?
Michael: I had a CIO at a company years ago he was Marine Corps reservist who since gone to retire as a General but point being is that he said that, “No one will care about your career more than you”. So it’s up to you to advance your career to whatever level you want it to be.
Steve: I like that, so true.
Carlos: Michael our last question for you today, if you could have one superhero power what would it be and why do you want it?
Michael: I’ll probably go vanilla here and say to fly.
Steve: Don’t be ashamed. Flying is a great superhero power.
Michael: Predicting the future and all that stuff like already cool but I would say the flying. I do a fair amount of driving and so it would be nice to get to places I grow up.
Carlos: There you go.
Steve: Alright, very good.
Carlos: Well awesome, Michael. Thank you so much for being on the show today.
Michael: Thank you for having me. I appreciate it and hope I have provided value to the companeros out there.
Carlos: Hey, companeros. That’s great! Well, if you didn’t comapneros, you let us know, I will let Michael know. That would be very nice.
Steve: I know I learned something along the way, thanks, Michael.
Michael: Thank you guys! I appreciate it.