BI Tools comparison/ Qlikview features understanding!!Please advice!!!
Hi ,
Could you guys please comment on the following features of Qlikview? I am actually doing a comparison of BI tools ( mainly Cognos) and different vendors for one of our clients. I do not have extensive knowledge of Qlikview and would appreciate your help on these details.
1. Data Handling capabilities: Can we approximate by rule of thumb know how much data can Qlikview handle? Like for Cognos cubes on a 32 bit machine its 2GB max.. I do know that it depends on the hardware and configuration but is there any rough estimate?
2. Emailing capabilties: I know Qlikview can be used to email but does it have advanced options of robust/report bursting like in Cognos? Also does it have some simple interface like Cognos has where in you just go and type the email address and schedule the task without the use of any macros or etc like in Qlikview?
3. Email Report versions: Can we email a report in pdf, csv, excel, 2002,2007, HTML etc or is it just PDF? Also what all versions can we see the report in? (apart from PDF)
4. Integration with Web components and customizing content on IE or any other client with objects from web: I heard there is something in version 10? Can somebody elaborate please? I know it is major pain in Cognos atleast...or used to be...
5. Data integration and migration: How is it in Qlikview if we have to migrate tons of data? Lets say there is a merger ( one is using Qlikview and the other some other BI tool) . Do we still plug into source db for Qlikview and then build reports ? Or is there is a smarter way to do it in Qlikview? What then about the Data models and other aggregations already available already? do we need to re model in any specific way or is it case to case basis?
6. What happens to traditional modeling concepts: RDBMS, , Dimensioanlly modeled relational database etc? meaning do we need to do these at all in db or we just leave it as it is and Qlikview will take care of rest? What then of all the database traps we might face lets say in a badly modeled db? How does Qlikview handle all this without data duplication or data quality issues? what then of OLTP, star schema, snow flaked schema? Do we still need to do all this for better performance?
7. Complex data and report functions: I know that Cognos has quite rich functions in the way data can be changed before reporting? How does Qlikview stack up against it? Or is it still for simple analysis and we can do only simple analytical functions on the data?
8. No of concurrent users/ Total no of users: Is there is set number by your experience for a 32 bit or 62 bit machine?
9. How is enterprise wide scheduling of jobs/Cognos reports/ tasks handled? Have we seen any issue with the scalability on that?
10. Montioring tools for performance: Do we need to use some 3rd party tools( Keynote, Mercury etc) to measure some important KPI's w.r.t to Qlikview applications availability, its performace for certain documents or is there a feature inbuilt in Qlikview which can be sued to do this reporting? I am talking about how long a certain Qlikview took to run on server or Qlikview access point? I have seen some 3rd party tools being used o Cognos applications for measuring these parameters.
11. Audit : Is there is a audit db that needs to be created for Qlikview like in Cognos? to basically log in all the details of all the transactions? How is it done in Qlikview? Any easy interface to pull that data out? Its quite a pain in Cognos to configure the audit db and then build reports out of it.
12. How are deployments handled in Qlikview? I mean is there a way IT can control it and organize it? How can quality be ensured after deployments? Also how do we handle cases lets say the db goes down or just crashes...The reports can be still read from RAM I understand ...but is there a robust way of managing it? Like lets say we a .mdc file in Cognos which is the aggregated cube?
13. Language support: Does it support all languages and diff language settings on different db's with a single server but different UI for different Language user?
14. How are all the new features in Qlikview accessed by Partners?
15. Support in Qlikview: Do we have like standard support like all other major vendors in Qlikview for their products? Is it free for any partner who has bought the software and Licenses?
Too many questions but would really appreciate your help on this.
Rough estimate is that you'll get about 10 to 1 compression on your raw data, then you'll need another 5-10% for every concurrent user of the data. You need enough RAM to hold it all, because swapping to disk is the death of an in-memory model.
2. Emailing capabilties:
Not familiar with Cognos and not sure what you mean by robust/report bursting. You can email reports as PDFs. You can send alert emails. Assuming you have QlikView Publisher, this sort of thing should be configurable. You shouldn't need to write macro code.
3. Email Report versions:
PDF is what Publisher directly supports. Excel is handled as an export, so it might take some custom code to email the Excel version of your data to people. But I'm already getting the feeling that you're approaching this the wrong way - emailing everyone some flat files to look at is NOT what QlikView is for. While it'll support that, its main strength is in its interactivity. To turn it into a report writer would be a waste, and you might be better off with a different product.
4. Integration with Web components and customizing content on IE or any other client with objects from web:
Not sure about version 10 as we're not on that version yet. We've experimented with IE and AJAX distribution, but have not yet moved to web clients, even though that's our eventual goal. AJAX seems to have some formatting and control issues still. I've not seen issues with the IE plugin. We've fiddled with integrating QlikView applications into web pages that include other information, but are so far not really satisfied with our efforts.
5. Data integration and migration:
While not strictly required, a good rule of thumb for handling the data loads in QlikView is to create special purpose applications that handle your ETL needs, storing the results as QVD files. User applications then load from the QVD files, which function as a sort of QlikView data warehouse. The ETL applications could be database reads, flat file reads, or whatever works for you.
You generally won't NEED to remodel your data if you're happy with your existing data model. QlikView will play ball with a wide variety of data models. But most of us slowly move towards a star or snowflake schema approach for our QlikView data. That just seems to be the consensus approach.
6. What happens to traditional modeling concepts:
Traditional modeling concepts remain valuable.
While not technically how QlikView stores data, it is very useful to think of data in QlikView as being in a relational DBMS. QlikView will even give you a relational database view of your data if you ask it for one, and I often do.
You generally won't be making any changes to your database to support QlikView, though the cleaner the source, the less work it will take in QlikView. But you would normally just write a QlikView load script to handle all your data cleaning and transformation needs, like removing duplicates, filling in missing data based on rules, and so on.
Not familiar with OLTP, but star schema and snow flake schema are ideal for QlikView. Not necessary, but ideal.
Having a clean, well-thought-out data model will definitely help with performance. Most of the time, though, you probably won't have to think much about performance.
One gotcha with badly-modeled data is that you absolutely cannot have any loops. If you load a loop into QlikView, it will arbitrarily break one of the connections, probably in a way you do not want. You'll want to break loops manually.
7. Complex data and report functions:
No idea how QlikView stacks up to Cognos, but there's a huge number of functions you can use either in the load script or in the UI itself while building charts and the like. QlikView is not for just simple analysis.
One big missing piece, at least in my mind, is support for regular expressions. Its text-handling capabilities do not include that.
I'm sure there are other areas where it is weak as well, but nothing that has caused me problems in practice. The function set has seemed complete enough for my needs.
8. No of concurrent users/ Total no of users:
We were probably supporting a few hundred users (granted, probably only a few concurrent users) on a 32 bit server for quite some time. We upgraded to 64 bit not because of number of users, but because some of the documents were just getting too big to fit in 32 bits.
9. How is enterprise wide scheduling of jobs/Cognos reports/ tasks handled? Have we seen any issue with the scalability on that?
Well, if you have QlikView Publisher, you configure tasks that are associated with the applications. They can be scheduled, can depend on completion of other tasks, or can be initiated by external events. It's mostly point and click. Visually, it looks fine to me. But at least on our machine, it's SLOW, and there are some lingering annoyances that can make it difficult to track things down on occasion. Still, very little of my time is spent on that compared to anything else, so I guess it's fine. I've not had any real scalability issues. There could be display issues, at least at my monitor's resolution, if I scheduled more than maybe 15 interdependent tasks. But so far, everything we actually do seems fine. Just a possible worry.
10. Montioring tools for performance:
I haven't looked into this much. We do have several monitor sorts of applications built in QlikView itself, I believe extracting data from logs. The one monitoring the scheduled runs has quite a bit of detail. But I'm not sure we have anything monitoring uptime and availability, for instance. Maybe it could be done, or maybe third party tools would be easier.
11. Audit :
Not sure what you'd be auditing. We don't have any sort of audit database.
12. How are deployments handled in Qlikview?
We copy the file to the production server using a script that also handles making backups and the like. Then we reload and distribute it to the users with QlikView Publisher. This is all under IT control. Not sure what you mean by ensuring quality after deployment. I periodically look at the applications that I'm in charge of, but don't have any, say, scheduled jobs to somehow analyze the data in QlikView and compare it back to the source database in some way. When I've needed to verify against a source, I do that during the testing of the application. If a database goes down, the QlikView load will fail, and the users will be stuck using the most recent good version of the data that was loaded. If desired, you can send emails about load failures, you can indicate to the users that their data is stale in the user application, or whatever seems appropriate. When you fix the database, you can either rerun the reload manually (point and click) or just wait for the next schedule reload. Or if you mean how do you handle it if the QlikView application goes down, well, click on it again and it should come back up. The data isn't JUST in RAM, it's also stored in the QVW file that you deploy. Worst case scenario, reboot the server, and everything should come back up.
13. Language support: Does it support all languages and diff language settings on different db's with a single server but different UI for different Language user?
Unclear exactly what you mean here, but I don't think it has particularly strong support for localization. It will probably take some effort to get a clean UI from the same application for users worldwide. But I don't deal with localization at my job, so perhaps it is stronger than I suspect.
14. How are all the new features in Qlikview accessed by Partners?
Not sure what you mean. I'm also not a partner, just a customer. We download new versions from the web site, test them out, then deploy them. As a developer, I can download a newer version than our official version if I want to play with it. Best to continue development in whatever we're officially at, of course, even if most things remain compatible from version to version.
15. Support in Qlikview:
I'm not involved in purchasing, but I believe that support is included, and at least in my experience, support is quite good, though I have little to compare it to. I generally report bugs via email, but I could do it by phone or through their web site if I preferred. They usually get back to me quickly enough. I generally submit requests for new features through the web site. The best support seems to be through the forums, which are very active.
1. Emailing: What I mean by report bursting is in Cognos you can have like hundreds of email Id's in a table, map it and then a certain report can be emailed to those hundred of them without any worries. This is used quite heavily in Financial applications where emailing becomes a key point as it needs to be reliable and scalable.
Apart from the individual users can just go ( if they have access) modify the properties of a report, schedule it and enter their email id in the filed and it will be emailed to their inbox. Very simple.
Does Qlikview offer that kind of scalability and ease?
2. Emailing report versions means we have some users who want the data in a certain way ( Excel 2007 format, PDF, HTML) etc which can be confiugred quite easily in Cognos. This is apart from the adhoc analysis they can do going online.
Does Qlikview offer anything like that?
3. " One big missing piece, at least in my mind, is support for regular expressions. Its text-handling capabilities do not include that." could you please elaborate on this?
4. Language support: By this I mean lets say we have configured the Oracle DB to handle arabic or hebrew or chinese characters apart from English for certain fields. Will we have an issue when we are reporting in Cognos? I mean these special characters , will they get jumbled up or messed up in qlikview documents?
Also lets say I am configuring a Qlikview application for Chinese client, can I configure every single character, tab, navigation in Chinese? I mean Language support across geographies...
5. Data handling capabilities: Lets say I have a 40 TB data ...assuming I have to report everything in a single qlikview document ( hypothetical case) what is the RAM needed?
It's hard for me to say, but it sounds like Cognos might be more flexible on the emailing and report formats. I've never done an email except for sending one to myself just to see it in action. It appears that a QlikView administrator (someone with the authority to change how documents are published and distributed) sets up a list of recipients selected from, in our case, all available email addresses and security groups defined to Windows. So you basically have two ways to manage lists - one in QlikView itself, one in Windows. Emailing to hundreds of people shouldn't be a problem, but this approach doesn't look like something users could modify.
I think there's another option, though. I should also note that you CAN email different reports to those hundreds of users in some cases. For instance, your users might be your customers. You don't want to send one customer's information to another. I haven't made sure this works, but you should be able to tell QlikView to loop and distribute based on the customer ID in the document, for instance. I haven't played with that other than in a very rudimentary fashion. It's possible that there would be some way to set that up to be more dynamic, with users entering their email information, that going into a table, that table being loaded into QlikView, and then Publisher looping through the values in that table to distribute.
So far as I know, you can only email the reports in PDF. I may be mistaken. I'm sure there's a way to do other formats with a bunch of custom code, since you can export to Excel and/or publish to the web, but I don't think they're supported in any simple fashion.
But again, if you're just emailing static reports, I'm betting there's some other software package that is better at it. I haven't been impressed by QlikView's static reporting functionality. On the other hand, once we got the users used to navigating QlikView, requests for static reports pretty much dried up.
Regular Expressions:
Quoting from Wikipedia, (http://en.wikipedia.org/wiki/Regular_expressions), "regular expressions... provide a concise and flexible means for matching strings of text, such as particular characters, words or patterns of characters."
To me, lack of support for regular expressions can in theory make extracting and transforming your data more difficult, in some cases much more difficult. However, in practice, I've never needed it and haven't had any problems. There are a lot of text-manipulation functions, and they've always been sufficient for my needs. I've only seen maybe one or two examples on the forums where regular expression parsing would have vastly simplified the task. So maybe not a practical problem most of the time, but an example of something that I consider fairly basic that a robust BI tool should include.
Language Support:
I haven't done much with language support since our only users are inside of our non-international company. Just poking around, QlikView supports the UTF-8 character set, and mentions supporting "unicode", but that doesn't say which character set, so maybe it's only UTF-8. So if you're using UTF-8 for the Arabic characters, I'd think it could read them in just fine. I wrote an example where a user chooses one of several countries, and the report then displays in the appropriate currency and in the appropriate date format. Assuming you've stored textual information in all languages of interest, it should be a simple matter to also display all text everywhere in the appropriate language. I don't consider this a trivial operation, at least until you've done it a few times and it's become cut and paste easy, but it's certainly doable. Someone with actual international experience could probably give a better answer, though.
RAM for 40 TB of Data:
10x compression of 40 TB of data is 4 TB of data, though there's a big margin of error knowing how well it will compress your data. Then it's down to how many concurrent users you have. Figure 8 TB for, say, 10-20 concurrent users of the same application. And that's as much memory as a 64-bit Windows box is going to be able to address.
In practice, though, 40 TB of data is going to scale down MUCH more than that, because you're typically not going to design a single monolithic QlikView application that reports on every single aspect of your business from the highest level summaries down to the lowest-level details of interest only to a particular person in QA for widget X. You're typically going to have a lot of different QlikView applications, each addressing the needs of different aspects of the business. Instead of one 4 TB application, you might have 100 applications averaging 20 GB, some much smaller, a few somewhat bigger, and maybe one that's 400 GB. Your concurrent users are going to be spread across those applications. Since you have multiple documents, you should also be able to spread them across multiple servers, though probably any server beefy enough to handle that biggest document can probably handle the rest. That's still a lot of horsepower that you need, but you ARE asking to host 40 TB of data using an in-memory model. It's doable, but you're going to have to pay for it in hardware.
I believe QlikView was designed for gigabyte-scale data instead of terabyte-scale data. But other than hardware limitations, I'm not aware of anything that would keep it from scaling up.
Excellent!!! I think I have answers to most of the questions...Thanks John...
Language support I am still waiting for some more comments who have had the opportunity to work with this.....
Yes UTF 8 is supported in Cognos as well...works well with some of the tools of Cognos but their cube builder ( Transformer) had issues while building he cube in Arabic.. It used to jumble the data up...Very recently that issue has been fixed by Cognos...
That is their tools which just work with relational data sources works fine as it is just providing the db view and if db supports then cognos can support...but they had some issues with their this middleware sort of tools....but thats resolved now in Cognos...
Also what I was looking for is whether the whole of Qlikview lets say deployed for Chinese client...can show all the buttons in Chinese...i mean a sort of chinese interface where its all chinese...
One last point ...do we have any areas where Qlikview needs improvement from technical stand point that you have come across in your experience of working with the tool? Which you feel can be improved?
In number 12, John mentioned an automatic email alert if a document fails to load properly. What is the best way to implement this? Thanks, and I do like reading these posts with a lot of info in them!
On the dowloads tab, it looks like QlikView itself is available in:
Chinese Dutch English French German Italian Japanese Portuguese Spanish Swedish
So in any of those languages, all of the buttons and menus and so on ought to be correct. The application itself would have to be specifically coded to support multiple languages. But thinking about it, there must be some way to detect which language the user is using in their client, and use that language in your application as well, saving the step of even selecting their language.
On the technical front, one basic problem with QlikView is that it is a bit unstable. It's improved over the years, but it has been very crash-prone in the past, and it does still crash more frequently than I think a mature piece of software should.
I do have a fairly fundamental issue with how it behaves, though it's difficult for me to articulate, and I don't know that I've ever heard anyone else complain about it in this way. When you specify a field name, what that means can depend a great deal on context. So I refer to field "Customer" in an expression. Do I mean the set of customers that are currently selected? Do I mean the specific customer on the row of the table I'm on? Do I mean a set of customers that I've explicitly specified, different from what the user selected? Do I mean just one of those customers? For the most part, how the code interprets a reference to "Customer" is entirely dependent on context. You can't just TELL QlikView what you mean - you instead have to learn which meaning it will assume in each context, and if you need a different meaning, you have to figure out how to set up a different context so that it will interpret it correctly. I feel that the product should be more explicit. I suspect that the current situation came about as a desire to be more friendly to new people. The less you have to explicitly specify, the more the product can figure out for itself, the easier it is to get started and produce something useful. But once you're an experienced developer, I think this lack of specificity makes more advanced visualizations more difficult, not easier. In my opinion, QlikView tries too hard, and would have been a better product with a more explicit, more constrictive syntax.
There are also cases of the syntax itself meaning different things in different contexts. A dollar sign ($), for instance, can mean the current set, or it can be specifying a parameter for a custom function, or it can be specifying a bit of the code that is to be evaluated first, and the result inserted directly into the remaining code. So as with a reference to "Customer", what the $ syntax means depends on where you're using it. I haven't seen this particular thing confuse very many people, but if I were designing QlikView, I'd probably have used different symbols for these three very distinct meanings.
Let's see, while I consider it superior to Excel as a BI tool, it does NOT allow you to treat it like a spreadsheet. You can't just click in a cell and edit a formula for that cell. Generally speaking, it wants to apply the same formula to every cell in a row or column. If you want a specific formula for a specific cell, you CAN do it, but you pretty much have to fake the product out to accomplish it, and maintaining it is significantly more difficult than it would be in Excel. Honestly, I've never needed this feature personally, but I've demonstrated it at least a couple of occasions to help other people on the forum, and I'm pretty sure I've heard people complain as well. I think it would be a stronger product if you had the option to directly and easily maintain expressions at the cell level instead of at the row/column level.
Not thinking of anything else to complain about. On the whole, I love the product. There are just some things I'd have done differently had I designed it myself.
shanrahan wrote:In number 12, John mentioned an automatic email alert if a document fails to load properly. What is the best way to implement this?
I honestly don't know. I didn't work on this part. I believe it was done with a visual basic script, but I could be wrong. I also suspect it could be handled in QlikView itself, but can't say for sure.
To raise an alert when script fails, in script set ERRORMODE=2, then use SCRIPTERROR and SCRIPTERRORDETAILS to create an alert that check for errors and send a mail with details.
