Thursday, February 19, 2009

How to integrate QlikView with Excel or any MS Office products



Most of my customers ask "Is it possible to integrate QlikView reports to Microsoft office Product Suite?". My answer is Yes we can integrate QlikView with MS office products like Excel, Power Point, MS Word , Outlook Express etc.

The easiest way to integrate is using OCX Components. With earlier versions of QlikView it was required to register OCX components at each user machines but with new 8.5.xx version it is not required to register OCX components. I will take excel as an example.

There may be two kind of users, first the person who is having QlikView licenses in his machine and second who don't have QlikView licenses (Thus he cannot use OCX capabilities). First kind of user can easily drag and drop QlikView objects in Excel sheet and he can use dynamic capabilities of reports in 100 % same way as he can do in QlikView itself. But if he needs to send these reports in excel to third party i.e. users of second kind who does not own QlikView licenses then what he need to do? Traditionally one can export data to excel from QlikView but it converts dynamic reports in static one and it is not acceptable everywhere.

What is the solution for above problem??

Solution is writing macro. One can write a macro in QlikView that will integrate QVW file to excel and whatever analysis based on clicks can be mapped directly to excel. Actually that macro can write a parallel program in excel and since data is available in QlikView application a click can push data with filters in excel and that excel can be shared to any one and it would remain dynamic even if user is not owning QlikView licenses.

Same way one can integrate QlikView with MS outlook or with any MS office product.

In next blog I will cover how effectively PDFs can be used to share QlikView reports to n number of users automatically.


I invite my readers comments.

Friday, February 13, 2009

QlikView vs. BO

Dear Friends,

A Most Awaited Comparison of QlikView with BO is ready for you. While comparing QlikView with BO and Cognos I was really wondering if you have to buy either BO or Cognos which one you will opt for you. Why I am wondering for because it is really hard to find difference between BO and Cognos technically both the architectures are almost same. Then you left yourself on the sales person's experience (I mean now it depend on the sales persons of both products how better they can explain their product and convince you).

I suggest before you decide for a BI ,you must have a look on QlikView and ask your QlikView vendor for a proof of concept (POC), it will help you to explore capabilities of QlikView and a POC will not take more than 3 hrs to make decent no. of reports. Hope this Comparison with BO will help you while deciding a BI tool for you.

I welcome your suggestions.

Happy Reading.

#

Product

Business Objects

Qlik View

A

Product

1

Product

Business Objects

QlikView: World's Fastest Growing BI tool. With 10.3 customers being added every working day.

2

Vendor

SAP

QlikTech

QlikView is an IN-memory based BI tool which can be deployed in a week's time. It comes at HALF the TCO and delivers TWICE the value compared to any OLAP based BI Solution In QUARTER of the TIME

C

Applications

1

Business intelligence

Yes

QlikView is a single integrated tool for ETL, DW, Multi Dimensional Analysis, Dash boarding, Reporting, as per the Butler group user takes couple of hours training instead of weeks taken by traditional OLAP based tools

2

Multi dimensional view

3

Strategic planning

Yes

4

Budgeting

Yes

5

Forecasting

Yes

6

Consolidation

Yes

7

Score carding

Yes

D

Hardware and Licenses

1

Warehousing is (Mandate/ Optional)

Mandate

NOT REQUIRED, One of the major advantage of using QlikView is, it does not require database which saves money and very well skilled human resource. With no data warehouse required QlikView can be implemented in a couple of weeks saving a lot of TIME, MONEY AND RESOURCES.

2

Server Licenses(Named/CPU)

Named

Named

3

User Licenses (Name/Concurrent/Unlimited)

Named

Named

4

Web Users

Unlimited

User lic

5

If preparatory Database specify (Technical and Methodology)

NO

NO: QlikView uses its integrated Robust ETL to Extract data from multiple data sources and forms a data repository using its patented Technology (AQL: Associative Query Logic) This technology overcomes the efforts and time required to build data warehouse, normalization, schema writing, creation of cubes. And Empowers the customer to be independent of the vendor to create and use analytics

E

Technology

1

Supported database

Any OLEDB, ODBC complains which covers althea database in the world today + FLAT FILE, WEBFILE. TEXT FILE DELIMITED FILE. ALSO WORKS WITH SYBASE

Microsoft SQL Server

Y

Y

Oracle

Y

Y

Pervasive SQL

Y

Y

IBM DB2

Y

Y

Informix

Y

Y

Access

Y

Y

FoxPro

Y

Y

Progress

Y

Y

MySQL

Y

Y

2

Supported networks

Microsoft

Y

YES

Linux

Y

Y TO CONNECT TO THE DATABASE

Unix

Y

Y TO CONNECT TO THE DATABASE

AIX

Y

Y TO CONNECT TO THE DATABASE

Novell Netware

Y TO CONNECT TO THE DATABASE

IBM AS/400 (OS/400)

Y

Y TO CONNECT TO THE DATABASE

3

Development tools used to build application

NO THIRD PARTY DEVELOPMENT TOOL REQUIRED. THE CUSTOMER BECOMES VENDOR INDEPENDENT FOR ANY NEW APPLICATION/ CHANGES REQUIRED

4

Report writer used - which one(s)?

Crystal

NO THIRD PARTY TOOL REQUIRED

5

Data Extract Transform Load (ETL) tools - which one(s)?

All Major ETL Tools (bobj Data Integrator)

NOT REQUIRED: QLIK VIEW HAS ITS OWN ROBUST ETL INTEGRATED AS A QLIKVIEW SUITE. THE ETL IS WIZARD DRIVEN CAN BE LEARNT IN A FEW DAYS AND OPERATED BY CUSTOMER HENCE EMPOWER THEMSLEF TO CREAT OWN APPLICATION AS AND WHEN DEMAND ARISES

6

Cubes supported - which one(s)?

All Major OLAP

IN ADDITION TO ITS OWN DATA REPOSITORY QLIKVIEW SUPPORTS ALL MAJOR OLAP CUBES

7

Existing integration to accounting and ERP systems - which systems?

SAP, PeopleSoft, JDE, SSA Global/BAAN, Portal integration kits

ALL ODBC COMPLAINT DATABASE(all the current database in the market) IRRISPECTIVE OF THEIR FRONT END, SAP USING A SAP CERTIFIED CONNECTOR

8

Existing integration to customer relationship management systems - which systems?

Siebel

BY MEANS OF THE GLOBAL STANDARD ODBC, OLEDB CONNECTIONS (all major crm system)

9

Existing integration to payroll systems - which systems?

All Payroll systems on an ODBC data source.

BY MEANS OF THE WORLD STANDARD ODBC, OLEDB CONNECTIONS( all major payroll system)

10

Existing integration to HRMS systems - which systems?

All Payroll systems on an ODBC data source.

BY MEANS OF THE WORLD STANDARD ODBC, OLEDB CONNECTIONS (all major hr system)

F

Features

1

Analytic functionality

Yes

YES

2

Bubble up exceptions

Yes

This feature is better handled where the exceptions are managed at the lower level and not popped to the upper layer and confuses the user. However all precautions are taken to handle the exceptions efficiently.

3

Drill down

Yes

YES

4

Drill through to source data

Yes

DRILL DOWN TO THE TRANSACTIOANL LEVEL. AS AGAINST TO THE AGREGATED DATA IN CASE OF OLAP BASED APPLICATIONS

5

Ranking

Yes

YES

6

Sorting

Yes

YES

7

Restrict results by date range

Yes

YES

8

Includes dashboard

Yes

YES

9

Time trending (for example, over 12 months)

Yes

YES AS MANY YEARS AS THE DATABASE HOLDS

10

Pareto filtering (automatically groups the categories that amount to less than 20% of a row or column total into "other")

Yes

YES

11

Calculations

Yes

YES

12

Counts

Yes

YES

13

Percentage of total

Yes

YES

14

Allocations

Yes

YES

15

Total and Sub-total

Yes

YES

16

Create OLAP cubes

Yes

NOT REQUIRED

17

Data extraction and transformation tools

Yes

INTEGRATED

18

Incremental extraction

Yes

YES

19

Impact analysis - objects impacted by change

Yes

YES

20

Can schedule process to run overnight

Yes

YES

21

Formatting

Yes

YES

22

By dimension

Yes

YES: N DIMENTION

23

By level

Yes

YES

24

By cell

Yes

YES

25

Zero-row suppression

Yes

ZERO/NULL FIELD SUPPRESSION

26

Conditional formatting by cell, e.g., highlight cells below threshold

Yes

YES

27

Returns text (e.g., if value > 90, returns "excellent")

Yes

YES

28

French version

Yes

UNICODE: CAN BE IMPLEMENTED IN ANY LANGUAGE

29

Screens

Yes

YES

30

Reports

Yes

YES

31

Documentation

Yes

YES

32

Metadata

Yes

YES

33

OLAP functionality

Yes

NOT REQUIRED

34

Skip missing levels - support ragged dimensions

Yes

YES

35

Write back

Yes

NO

36

With audit trail

Yes

YES

37

Includes charting tools

Yes

YES

38

Access multiple cubes at a time

Yes

YES

39

Excel client

Yes

YES

40

Internet Explorer client

Yes

YES

41

Netscape client

Yes

Firefox

42

Geographical representation

Yes

YES

43

Drag and drop

Yes

YES

44

Attach notes to cube

Yes

CUBES NOT REQUIRED SAVES TIME AND MONEY

45

Attach notes to cells

Yes

CELLS NOT REQUIRED SAVES TIME AND MONEY

46

Password security

Yes

YES: UP TO THE FIELD LEVEL( BETTER THAN THE LISTING BELOW)

47

By company

Yes

YES

48

By department or division

Yes

YES

49

By menu

Yes

YES

50

By function (view, add, change, delete)

Yes

YES

51

By dimension

Yes

YES

52

By level

Yes

YES

53

By cell

Yes

YES

54

Respect active directory authentication

Yes

YES: CAN BE INTEGRATED WITH ACTIVE DIRETORY AUTHENTICATION FOR A SINGLE LOG ON PER USER

55

Respect database security

Yes

YES

56

Portal generation that includes HTML reports

Yes

YES

57

Report writer

Yes

NOT REQUIRED: INTEGRATED FEATURE

58

Web-based

Yes

YES

59

Generate cube

Yes

NOT REQUIRED: OVERCOMES CUBE DRAWBACKS

60

Drag and drop

Yes

YES

61

WYSIWYG report design

Yes

YES

62

Technology

Yes

YES (AQL)

63

API-enabled (SDK provided)

Yes

YES

64

Web-based (only need browser at client)

Yes

YES

65

For entire application

Yes

YES

66

Support for .NET web services

Yes

YES

67

Support for Java web services

Yes

YES

68

Transfer data to:

Yes

yes

69

Report writer

Yes

QLIKVIEW IS NOT A REPPORT WRITER HOW EVER IT SUPPORTS GENERTING REPORTS AND THIS FEATURE IS INTEGRATED PART OF QLIKVIEW.

70

Excel

Yes

YES

71

PivotTable

Yes

YES

72

HTML

Yes

YES

73

PDF

Yes

YES

74

PowerPoint

Yes

COPY PASTE (CTRL+C, CTRL+V)

75

XML

Yes

YES

76

Views

Yes

YES

77

Crosstabs

Yes

YES

78

Charts

Yes

YES

79

Both crosstabs and chart at the same time

Yes

YES

80

Work flow

Yes

Open to function with third party tools

81

Alerts can be automatically triggered

Yes

YES IN THE FORM OF POPUPS AND CAN BE USED TO AUTOMATE SENDING OF MAILS, ACTIVATE/RUN OTHER APPLICATIONS

82

Dynamically summarizing data without having to rebuild data?

ANALYSIS AT TRANSACTION LEVEL, WITH DRILL DOWN CAPABILITY FROM THE CONSOLIDATED ANALYSIS

83

Ability to pre-calculate results (aggregate) when they are available, or dynamically generating result from atomic information when necessary

YES

84

Ability to control and design cube aggregations

DOES NOT REQUIRE CUBES WHICH CAN HANDLE 3-10 DIMENTIONS ONLY. QLIKVIEW IS BASED ON AQL THAT CAN HANDLE AS MANY DIMENTIONS AS THE FILEDS AVAILABLE

85

Ability to monitor cube performance and tune the cube aggregations accordingly

DOES NOT REQUIRE CUBES WHICH CAN HANDLE 3-10 DIMENTIONS ONLY. QLIKVIEW IS BASED ON AQL THAT CAN HANDLE AS MANY DIMENTIONS AS THE FILEDS AVAILABLE

86

Can the cube be built using either of ROLAP, MOLAP, HOLAP techniques

DOES NOT REQUIRE CUBES WHICH CAN HANDLE 3-10 DIMENTIONS ONLY. QLIKVIEW IS BASED ON AQL THAT CAN HANDLE AS MANY DIMENTIONS AS THE FILEDS AVAILABLE

87

A hybrid database that stores data in relational form and multidimensional form, as appropriate

QLIKVIEW DOES NOT NEED THIRD PARY DATABASE.

88

Is 'What-if" analysis supported

YES

89

GUI based Data Mining Capabilities

YES

90

Enterprise Platform

YES

Statistical Analytical Modeling

YES

91

Statistics

YES

92

Data & Text Mining

YES

93

Model Management & Deployment

94

Forecasting

YES

95

Econometrics

YES

96

Quality Improvement

97

Operations Research

98

Data Quality

AQL TAKES BUSINESS INTELEGENCE ONE STEP FURTHER THAN THE TREDITIONAL DATA WAREHOUSE ARCHITECHTURE - OLAP BASED SOLUTIONS

Initial implementation time

1/10 time than Cognos and BO

Initial implementation cost

1/6 time to implement than Cognos or BO

Time to proficiency

Only five day Application development training required. Time to become proficient is in days not in month that is 1/6 than Cognos and BI

Project Success rate

98 % in Comparison to 38 % in OLAP based BI Software.

TCO

1/2 than any BI tool

Value

Twice than Cognos and BO

Search more on BI on Web

Know the Blogger