Tuesday, November 5, 2013

Comparison of JasperSoft , QlikView and PowerPivot BI Tools

A Comparison of BI Tools is presented below

Aspect
JasperSoft  BI
QlikView
PowerPivot
Completeness of the BI Stack
Complete BI stack along associated components from partner vendors

Mature Analysis and Visualization stack. Lacks completeness in OLAP and ETL capabilities.

A isolated solution that requires several other components (SSIS, SSRS, SSAS, Office 2010, Sharepoint 2010)  to make it a complete stack.
Roadmap
Rated in Gartner’s magic quadrant 2011-2012 as a niche player

First BI player to deliver scalable BI over cloud

Rated in Gartner’s magic quadrant 2011-2012 as a visionary
?
Supported platforms
ALL – owing to being  a java application
?
Windows only.
Architecture
Traditional BI
Non-traditional BI that does not need any dimensional modeling. Uses a technique called Associative Query Logic (AQL) that tries to reason/infer data relationships. But this may not always be correct.
Mimics QlikView
Report delivery & accessibility
Web Portal.
No deployment overheads
Web Portal
No deployment overheads
MS Excel 2010+
(or requires Sharepoint 2010)
More administrative overhead.
Quality of User Interface
Moderate
Sleek interface
Sleek interface delivered over Silverlight plugin.
End User friendliness
Medium
High
High
True OLAP
YES via Mondrian Engine. Supports MDX Querying
NO
NO
Report scheduling, email etc..
YES
NO
NO
Self Service BI
YES
YES
YES
Supported Endpoints
WEB
WEB, MOBILE etc..
EXCEL 2010
Deployments
175,000 in 100 countries; over 14,000 commercial customers
19,000 customers in more than 100 countries
?
TCO
HIGH
LOW
MODERATE
Cost
LOW
VERY HIGH
?
In Memory Capabilities
YES
BY DEFAULT
BY DEFAULT
Performance
ACCEPTABLE
GOOD
GOOD
Flexible Licensing
Has both Community and Enterprise Editions. OLAP requires Enterprise Edition.
?
Has a personal edition that is free. But users cannot share insights and this leads to very fragmented BI deployment across the enterprise.
Scalability
GOOD
LOW because of problems with the in-memory model that fails under higher loads. Also is a RAM guzzler.
LOW
Development Effort
Moderate because of it’s traditional BI nature. (build model – deploy model). But TCO will be lower because the cost spent upfront in building a true OLAP model will reap benefits later on.
Low
Low if end user has sufficient Excel skills.
Maintainability
Good
Cannot say
Low because of data and analysis fragmentation.
Supportability
Very high. Due to it’s open source nature, there will always be a plethora of trained manpower available.
Commercial support option available from JasperSoft.
Additional supportability via excellent documentation, knowledge repositories and books.
Cannot say
Cannot say
Marginal Cost to implement changes etc.
Moderate.
For Ad-Hoc reporting, no additional development time/cost required.  Additional OLAP data mart development will require development efforts
Moderate
Low.
Since this is pure Self Service, it is left to the capabilities of the end user to be able to develop his own reports.

In order to generate the comparison report between the various BI tools, the following articles have been referred to.



JasperSoft Comparison between various Editions

QlikView
No OLAP-style analysis

QlikView does not offer the richness of MDX and other logical query languages provided by traditional on premise BI products. These languages enable end users to create far more powerful calculations (e.g. via addressing cells positionally in either a materialized or logical cube) than are possible in QlikView. QlikView provides a user interface for users to interactively explore and analyze information based on associations between different data sources. This is a unique approach but because it is not a logical query language, it has limitations.
No Pixel-perfect report writing

QlikView lacks some of the advanced capabilities required for creating highly formatted reports. QlikView is designed for interactive analysis and not for report writing. To create a formatted report in QlikView requires the use of macros and the duplication and maintenance of QlikView objects. Previously created QlikView objects need to be cloned and placed into a “Report Section” in QlikView.
QlikView takes shortcuts that can result in more work

QlikView makes no distinction between columns that are facts and columns that are attributes. While this is designed to encourage data exploration, it makes the creation of well-defined formatted reports more complicated and time consuming.


Mongo

D:\tools\mongodb\bin>mongo 100.10.1.17:27017
MongoDB shell version: 2.2.2
connecting to: 100.10.1.17:27017/test
mongos>
mongos>
mongos> sh.status();
--- Sharding Status ---
  sharding version: { "_id" : 1, "version" : 3 }
  shards:
        {  "_id" : "PPSHRD01",  "host" : "PPSHRD01:10001" }
        {  "_id" : "PPSHRD02",  "host" : "PPSHRD02:10002" }
  databases:
        {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
        {  "_id" : "test",  "partitioned" : true,  "primary" : "PPSHRD01" }
        {  "_id" : "mktpdm",  "partitioned" : true,  "primary" : "PPSHRD02" }
        {  "_id" : "*",  "partitioned" : false,  "primary" : "PPSHRD01" }

Start
D:\mongo\bin>mongod --dbpath=D:\mongo\bin\data\db

Import
mongoimport --db test --collection gtin --type csv --file c:/country.csv --fields item_sync_fact_id,sync_status,sync_cnt,unique_syncnt,frmcount,frmstatus,frmorgname,frmglobal,tocount,tostatus,torogname,toglobal,day,month,itemname,desc,brickcd,clscd

D:\mongo\bin>mongoimport --db test --collection gtin --type csv --file d:/mongo/
country.csv --fields item_sync_fact_id,sync_status,sync_cnt,unique_syncnt,frmcoun
t,frmstatus,frmorgname,frmglobal,tocount,tostatus,torogname,toglobal,day,month,i
temname,desc,brickcd,clscd
connected to: 127.0.0.1
imported 17430 objects

query

{
collectionName : 'gtin',
sort:{frmcount':1},


}

Datatype Limits in ORACLE

DatatypesLimitComments
BFILEMaximum size: 4 GB
Maximum size of a file name: 255 characters
Maximum size of a directory name: 30 characters
Maximum number of open BFILEs: see Comments
The maximum number of BFILEs is limited by the value of theSESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.
BLOBMaximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Foot 1 ).
CHARMaximum size: 2000 bytesNone
CHAR VARYINGMaximum size: 4000 bytesNone
CLOBMaximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1).
Literals (characters or numbers in SQL or PL/SQL)Maximum size: 4000 charactersNone
LONGMaximum size: 2 GB - 1Only one LONG column is allowed per table.
NCHARMaximum size: 2000 bytesNone
NCHAR VARYINGMaximum size: 4000 bytesNone
NCLOBMaximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1).
NUMBER999...(38 9's) x10125 maximum value
-999...(38 9's) x10125 minimum value
Can be represented to full 38-digit precision (the mantissa)
Can be represented to full 38-digit precision (the mantissa)
Precision38 significant digitsNone
RAWMaximum size: 2000 bytesNone
VARCHARMaximum size: 4000 bytesNone
VARCHAR2Maximum size: 4000 bytesNone