QlikView's Design Allows Flexibility
QlikView (QlikTech Inc., 888/828-9768, www.qliktech.com) turns out to be worth a look. Though little known in the United States, it has a major presence in Europe with 2,000 clients and 100,000 end users worldwide. The software provides low-cost, easy-to-use data analysis with greater flexibility, scalability and speed than competitive systems. The difference results from its underlying technology.
This system uses an "associative" database design, meaning each piece of information is stored independently along with links to related information. This is more flexible than conventional approaches, which embed information in larger structures such as records and tables. But it also requires time-consuming data reads to assemble related information.
QlikView overcomes this by storing all data in memory, making such reads very fast. Loading data into memory imposes size limits, which QlikTech overcomes by compressing the input data while loading it. This compression itself can be slow, so QlikTech has built technology to store and reload previously processed data and to add new data to existing files without reloading everything from scratch.
This is complicated stuff, but it's mostly behind the scenes. From the user's perspective, building a QlikView application involves connecting to a data source, writing load scripts and defining the user interface. The system can import data through OLE DB or ODBC drivers. It also has special connectors for SAP, Microsoft Excel and structured text files such as delimited, XML and Web pages. Load scripts select data elements and define transformations and calculations, drawing on more than 150 transformation functions. When more than one input source is involved, the system automatically links them by joining fields that have the same name. Scripts may rename the input fields so this works correctly.
QlikView's main user interface is a list box. This presents a single type of data, say, a list of customer names. List boxes show only one entry for each value and can show the number of times each value appears. A typical application will contain multiple list boxes - for example, customer names, order dates and products ordered. When the user selects an item in one box, the system can automatically highlight related items in the other boxes. Selecting a customer name would show the dates that customer had ordered and the products she had purchased. The numbers would adjust to show how many times that particular customer has ordered each product.
Simple list boxes can be supplemented by more complex components. Users can see statistics on the contents of a field, such as count, sum, average and standard deviation. They can stack multiple elements in a single box, with drop-down lists to select the contents, to define complex queries. They can display elements in columns, similar to a conventional spreadsheet or database table, or in pivot tables with unlimited numbers of dimensions. Pivot tables and drill-down sequences can cut across logical hierarchies - for example, from region to date range to product line. Data can be presented on graphs and charts, which can be used to make further selections. Users can define calculations to execute on selected data and call external programs for additional calculations. Results can be exported to Excel or PDF files. Combinations of boxes can be displayed on different tabs within the same application.
Much of this will sound familiar to users of standard query and reporting tools such as Cognos and Business Objects. But there is an important difference: Standard analysis tools require carefully designed data schemas while QlikView does not. Answering a single new question in a standard tool often requires creation of a new schema. In practice, dozens or even hundreds of data cubes are needed for a reasonably complete set of reports. Because QlikView holds the actual raw data in memory rather than precalculated summaries, it can answer any question whether or not it was defined in advance. This means the detailed, time-consuming and often agonizing design processes required to set up a conventional system can be skipped altogether in QlikView - allowing an installation to be useful in hours or days, not weeks or months.
A few caveats are in order. Most of the work in building a major data warehouse or marketing database is data extraction and preparation. QlikView's load scripts and transformations can do some of this, but not necessarily any faster than other tools. Nor is QlikView something end users can set up themselves: Building an application requires a fair amount of technical skill and needs someone familiar with the underlying data sources and business rules.
Compression notwithstanding, QlikView is still constrained by its need to load data into memory. A standard Windows server is limited to 2 gigabytes of memory. Even with QlikView's compression to 10 percent to 30 percent of the original volume, this means a maximum of a dozen or so gigabytes of input. Newer 64-bit systems allow much more memory but still rarely exceed 32 gigabytes. That's a lot of data, but nowhere near the terabyte-plus size of even a modest enterprise warehouse.
QlikView won't solve all of your reporting problems overnight, nor does it claim to. The company is quite clear that it is targeting midmarket applications that are too expensive to implement in conventional systems.
QlikView's cost is remarkably reasonable: U.S. licenses start at $22,000 for 10 named users, and it can run on low-cost Windows servers and workstations. Browser-based and standalone implementations are also possible. A free, fully functional, 15-day trial version is available for download from the QlikTech Web site.