Preparing Data for a Qlikview Business Intelligence Strategy

So you own or work for a business that processes 2 million line items per year, you have 2000 customers, and 65 000 products. This makes up a pretty large amount of data. To analyze this data you will need a Business Intelligence tool, such as Qlikview, Cognos, Business Warehouse, Pentaho or the like. Whilst all these solutions deliver outstanding results, my personal favorite is Qlikview. However, before you do this you will need to ensure that your database design is set up in such a way that it will be optimal when using this data in a BI tool.



Data in a business can be basically broken down into the following categories:

Transactional data

This data is the base of any business. In most cases the line for line transaction detail of all incoming and outgoing revenues. For example, when you order a book from a store, the line item of the book transaction will be stored in this table.

Customer data

This is the data about your customers. Basic detail such as the sector they fit into, their contact detail, physical address, account status etc. In most cases these link to transactional data

Product data

This is the data all about your product. There will be an line item in this table for every product you sell, and it will include detail such as price, type, category, description, and various other meta data about the physical characteristics about the product.

There are many more types of data in any business but for now we will just look at these three to help you get started with setting up your strategy for ultimate business intelligence.

Before you start with designing reports for any business, you need to look at the data you have to start off with. What I always do when designing a database capturing system is ask the business leads what they would want to see in their reports or business intelligence strategy. This helps determine what they will need to capture to get what they want in the reports. It happens too often that a business will design the most fantastic database system, but only think about the reports after all the development has been done, only to find there is key information they did not capture. This ultimately extends the development time of the project and costs money.

We will look at some of the key steps to get data prepped for a business intelligence strategy.

    Choose a BI tool that will suit your needs. For the purposes of the following steps I am just going to assume we choose Qlikview as our BI tool of choice. Qlikview is an in memory BI tool which delivers a fast user experience even when dealing with large amounts of data.

    Try think about the reporting aspect during the design of the ERP system you want to use. This will help to get the database structure layout in the most optimal format. If you don't do this you will have to join, and concatenate tables to get to the end result. This is not always optimal and when dealing with big data can significantly affect performance even when using in memory BI tools such as Qlikview.

    If you are building a strategy after the ERP system has been put in then you can design database views to get the base information you need. This can be done by using SQL views to query data in the database before putting it into Qlikview. Then Qlikview will just query the view, which will have all the data in it already. This will prevent having to massage the data in the Qlikview itself.

    And lastly, the most important step, use Qlikview to create mini models with each type of data in it, such as your transactional, customer and product information. Give it to the business leads to verify that the data is correct. Make sure that each model has the necessary keys in each table to link each table together. E.g. a transaction record should have the customerId and the productId in it. This is critical when you go live with you model because if your model has inconsistencies in it from the outset, it could negatively affect your BI strategy forever.

With a little planning and some good data governance you can get the Business Intelligence strategy you need to take your business to the next level.