Technology Comparison of BI Architectures

 

Business Intelligence Technologies Overview and Comparison

QlikView

ROLAP

OLAP

Reporting

Examples:

QlikView

MicroStrategy, Oracle Discoverer 

Cognos, Hyperion, Panorama, Microsoft  Analytical Services

Business Objects, Crystal Reports, Oracle Reports

Technology:

 Associative Database Layer: Data stored in  Associative database in memory (RAM) and all aggregations/calculations created dynamically as needed.

On Demand Queries: Complex queries broken into simpler SQL queries and pushed down to source database in real time.

Impact on Source DB:

Worst - Every new view of the data creates a separate Best - Records pulled and involved query that straight across with minimal runs against the source processing, data stored in a database, data not stored snapshot until refreshed. and must be requeried every time it is accessed.

Performance Refreshing the Snapshot:

Best - Data is pulled from the ODBC or OLE/DB connection with minimal processing. The limiting factor is typically the speed at which records can pulled out of the ODBC or OLE/DB connection.

N/A - Data not stored in a snapshot. Every new request directly hits the source database servers.

Performance Analyzing Data:

Best - Data is stored in memory (RAM) and calculated as needed without any disc-reads or network traffic.

Worst - Each change in view of the data results in a query that will have to be executed on the database server and the results transferred back across the network to the analysis tool. Queries are complicated and can take a long time to run.

Flexibility - Adding Dimensions and Measures

Best - Any field in the source data is can be added as a dimension instantly. Likewise new measures can be added on the fly.

Worst - New dimensions Best - Any field in the and measures must be hard source data can be added coded in to the cube as a dimension instantly. definitions (an IT task) and Likewise new measures can then the cubes must be refreshed. be added on the fly.

Worst - New aggregations must be hard coded into reports (an IT task) and reports must be refreshed.

Offline Analysis

Best - QlikView applications compress data to less than 8% - 3% of original source data size. QlikView apps can be analyzed disconnected from the network and many apps are small enough to be sent via email.

Worst- OLAP cubes exponentially increase the Worst - ROLAP needs to data size from source data. execute queries against the Some vendors will provide source database in real tiny trivial cubes and claim time. Offline analysis is offline analysis capabilities. impossible. True offline analysis is not pratical. Drill down to detailed data impossible.

Poor - Reports are generally availible offline but are static and do not allow interactive analysis.

OLAP Cubes: Static cubes built to store preaggregated data on hard discs.

 Average - Cubes must be refreshed but data is processed (aggregated) on server which impacts DB server, data stored in a snapshot until refreshed..

Worst - Very complex demanding queries must be run against the source databases in order to preaggregate the data. This data must then be written out to a huge multidimensional cube stored on a hard disk.

Best - Data is stored in a cube in a pre-aggregated format and displayed as needed.

Static Database Queries: Predefined queries run against source database.

Poor - Each report contains one or more queries that must be executed against the source database every time an instance of a report is refreshed, data is stored in each individual report file until refreshed.

Poor - Each report must be refreshed individually one after the other. Calculations are performed at the time the reports are refreshed.

Worst - Data is static and can not be a nalyzed interactively. Each report must be rerun against the source database servers to be updated.