If you are reading this book, then you are already familiar with the power of QlikView. You are on your way to utilize the in-memory and associative power of QlikView to build better insights for your organization. Business intelligence applications developed in QlikView are helping organizations worldwide in transforming their raw data into useful information.
Before jumping into creating data transformations and visualization in QlikView, we will cover the basics of this book. This book will cover all the essentials of QlikView designer and developer concepts. It will start with basics of QlikView, and then dive into loading data, performing transformations, creating visualizations, and deployment of the application. In each chapter, you will understand the key concepts and follow step-by-step exercises.
The first chapter will cover the development life cycle of QlikView, an introduction to a fictitious company Adventure works Inc., and an introduction to the adventure works data model.
In this chapter, we will learn about:
QlikView components and installation
QlikView basics
Problem definition of a fictitious company
Understanding existing data model and tables
Development environment setup
To explore the power of QlikView, you need to install QlikView desktop. A personal edition of QlikView desktop can be downloaded from http://www.qlik.com/us/explore/products/free-download?ga-link=navbtn.
You will be asked to register, or login if you are already a registered user.
The installation file comes in 32-bit and 64-bit editions. Install the version based on your computer's specification. Installation is very straightforward. You need to just follow the default options. The personal edition has the full capability of QlikView desktop. The Personal edition works with local files only; you cannot share your application design file (qvw) with another unregistered user, or load a design file from another user.
Installation comes with an Examples folder that contains QlikView documents. You can review this folder to learn more about QlikView. This folder is located in your installation path under C:\Program Files\QlikView.
Before moving further, we will learn the basics of QlikView:
Technology: QlikView uses an in-memory data model. It stores all the data in RAM instead of disk. RAM storage results in faster response time.
Associative experience: In QlikView, data is always associated. Association is automatically created between two tables having common field names. The associative technology results in an enhanced data discovery experience. Traditional BI solutions follow a predefined path to navigate and explore the data. QlikView allows users to take any data path of their choice.
Power of green, white, and grey: In a QlikView application, selected data elements are displayed in green, associated data is displayed in white, and non-associated data is shown in grey.
All the data is always present.
A QlikView application development utilizes QlikView desktop. It involves, connecting to any format of data, extracting and transforming data by writing scripts, designing interactive dashboards by creating different visualization objects, and deploying applications on the server. Users access this application via access point.
A QlikView design file has the extension
.qvw. Qvw is also used to create QVD (QlikView data file). QVD stores the data extracted from the data source. You will learn about the power of QVDs in the subsequent chapters. A QlikView design file is referred to as qvw, document, or application, but they all mean the same.
This book uses a data model based on Adventure works 2012 database.
Joe Smith is being hired as a QlikView consultant by Adventure Works Inc.. Adventure works has recently purchased QlikView. Their employees got an overview of QlikView, but they need help in enhancing their QlikView knowledge and implementing the QlikView dashboarding application.
This book will take you on a wonderful journey with Joe Smith and will provide you with QlikView essentials, which you will require as a successful QlikView consultant.
Joe Smith, being a seasoned QlikView consultant, knows that for successful implementation he needs to follow the development life cycle of QlikView. At a high level, he will do the following:
Gain an understanding of Adventure works' business
Gather user requirements
Analyze data model/data sources
Follow data modeling best practices
Load data
Follow visualization/dashboarding best practices
Create dashboard
Deployment
Adventure Works Cycles, is a large, multinational manufacturing company. The company manufactures and sells metal and composite bicycles to North American, European, and Asian commercial markets.
Coming off a successful fiscal year, Adventure Works Cycles is looking to broaden its market share by targeting their sales to their best customers, extending their product availability through an external website, and reducing their cost of sales through lower production costs.
At Adventure works, executive management wants to utilize QlikView to address the following:
Create an enterprise wide, scalable Business Analytics platform where the information is easily available, shared, and collaborated
Integrate data from different data sources
Gain visibility into the company's key performance indicators
Comparative analysis of data by different time periods
Access relevant information quickly and efficiently
Gain business insights to make better business decisions
After understanding the business and business requirements, it's time to analyze the underlying data.
Adventure works is a relational database.
Management is interested in utilizing the data elements stored in the following tables. Tables are sourced from relational database, Excel files, and text files.
Product
ProductSubcategory
Product Category
Order Header
Order Detail
Customers
Territory
Employees
Shippers
At high level, the tables from the source system have the following relationships:
QlikView can handle Star schema and Snow flake schemas effectively. Star schema is simple to understand. It is good for reporting as number of joins are reduced.
Star schema consists of dimensions and facts. It has a fact in the middle and dimensions surrounding the fact. The schema shapes like a star and hence the name star schema.
Fact tables contain foreign keys of dimension tables. The following schematic represents the relationship between the fact and dimension tables:
In a snow flake schema, a dimension is not connected directly to the fact. It is connected to another dimension.
In Adventure works source data model, the dimension tables are:
The fact tables are:
Order Header
Order Detail
To set up the QlikView development environment, download the code bundle for this book from Packt Publishing's website (http://www.packtpub.com/support) and then unzip the downloaded file in your C drive within a folder with the same name as the ZIP file (QlikViewEssentials). The unzipped folder (C:\QlikViewEssentials) should now contain the following folder structure in it:
Apps: This folder contains the.qvwfiles. Any design file created in QlikView has.qvwextension. These files are referred as document, app, or qvw.Data: This folder contains data files required by the application. It includes the access database.mdbfile. Also, it contains excel and text files. This folder also contains a subfolderQvdsto store QVDs.Images: This folder contains the images required by the application.Includes: This folder contains any files to be included in the application, for example, data connection.
This chapter familiarizes us with QlikView. You learned about the different components of QlikView and QlikView installation. You are now equipped to learn subsequent chapters by gaining knowledge about the fictitious data model of Adventure Works Inc. and learning about data modeling best practices.
In the next chapter, you will dive more into data modeling by loading data from disparate datasources. You will also learn about resolving synthetic keys. You will learn about various data transformation techniques. It will help you in becoming an expert QlikView developer.
