How Business Intelligence How Business Intelligence Software WorksSoftware Works

and a Brief Overview of and a Brief Overview of Leading ProductsLeading Products

Jai WindsorJai Windsor

MIS 5973MIS 5973

December 8, 2005December 8, 2005

What is Business Intelligence What is Business Intelligence (BI)?(BI)?

• Yet another IT buzzword?Yet another IT buzzword?• ““Software that enables users to obtain Software that enables users to obtain

enterprise-wide information more easily” enterprise-wide information more easily” • A strategy to ensure the right information A strategy to ensure the right information

is available to the right decision makers at is available to the right decision makers at the right timethe right time

• Working definition- products that are Working definition- products that are designed to extract and present data from designed to extract and present data from a data warehouse (ideally)a data warehouse (ideally)

Extract and present data…Extract and present data…

• Querying capabilitiesQuerying capabilities

• Reporting capabilitiesReporting capabilities

• AnalysisAnalysis– Using reportsUsing reports– Using ExcelUsing Excel– Using OLAPUsing OLAP

• Ideally from a data warehouse, but can be Ideally from a data warehouse, but can be from other databases, spreadsheets, flat from other databases, spreadsheets, flat files, etc.files, etc.

BI VendorsBI Vendors

• Business ObjectsBusiness Objects• CognosCognos• ActuateActuate• ApplixApplix• Information BuildersInformation Builders• InformaticaInformatica• MicroStrategyMicroStrategy• MicrosoftMicrosoft• OracleOracle• SASSAS

Querying Capabilities-Querying Capabilities-The Business ViewThe Business View

• ““Business view”, or metadata layerBusiness view”, or metadata layer

• Safe, user friendly environment for Safe, user friendly environment for business usersbusiness users

• Generates SQL queries for the userGenerates SQL queries for the user

• IT users can modify or write their IT users can modify or write their own queriesown queries

• ““Universe”, “Package”, “Project”Universe”, “Package”, “Project”

Querying Capabilities-Querying Capabilities-Prompting the UserPrompting the User

• Helps users design their own queriesHelps users design their own queries

• Asks users what data they want to Asks users what data they want to see and how they want to filter itsee and how they want to filter it

• Standard report templates with Standard report templates with hundreds of variationshundreds of variations

• Do’sDo’s

• Don’tsDon’ts

Querying Capabilities-Querying Capabilities-Complex Business QuestionsComplex Business Questions

• Might seem innocent enough but Might seem innocent enough but cause a huge problem for the BIcause a huge problem for the BI

• Limited SQL functionality in some Limited SQL functionality in some cases- subqueries, multipass SQL, cases- subqueries, multipass SQL, new functions like RANKnew functions like RANK

• Proprietary methods are sometimes Proprietary methods are sometimes slow and inefficientslow and inefficient

Reporting CapabilityReporting Capability

• Reporting transforms query results Reporting transforms query results from raw data to meaningful from raw data to meaningful informationinformation

• Report designReport design

• ChartingCharting

Interactive Reporting as an Interactive Reporting as an Analysis ToolAnalysis Tool

• The least complicated method of The least complicated method of data analysisdata analysis

• Investigate trends or exceptionsInvestigate trends or exceptions

• OLAP-liteOLAP-lite

• Formulas and functionsFormulas and functions

Report Delivery – Push Report Delivery – Push StrategyStrategy

• Report generation scheduled by ITReport generation scheduled by IT

• ““Pushed” to usersPushed” to users

• BurstingBursting

• Users can be overwhelmed and stop Users can be overwhelmed and stop reading reportsreading reports

• AlertsAlerts

Report Delivery- Pull Report Delivery- Pull StrategyStrategy

• Users log on and request (“pull”) Users log on and request (“pull”) reportsreports

• Can create a burden on serverCan create a burden on server

• Schedule-and-pull recurring, heavily Schedule-and-pull recurring, heavily used, complex reportsused, complex reports

• Web portals and interactivityWeb portals and interactivity

Analysis by ExcelAnalysis by Excel

• Excel remains the most popular BI Excel remains the most popular BI tool in usetool in use

• FamiliarFamiliar

• ““Massage” dataMassage” data

• BurstingBursting

• Cheap!Cheap!

OLAP OLAP

• OOnnlline ine AAnalytical nalytical PProcessingrocessing

• MOLAP – Multidimensional OLAPMOLAP – Multidimensional OLAP

• Multidimensional data cubesMultidimensional data cubes

• Multidimensional and cross-Multidimensional and cross-dimensional calculationsdimensional calculations

• Response times are fast and Response times are fast and predictablepredictable

Other variationsOther variations

• ROLAP – relational structureROLAP – relational structure

• HOLAP – hybrid between MOLAP and HOLAP – hybrid between MOLAP and ROLAPROLAP

• DOLAP – dynamic OLAP, “personal” DOLAP – dynamic OLAP, “personal” temporary cubestemporary cubes

More on OLAPMore on OLAP

• Drill down, drill acrossDrill down, drill across

• Drill to detail Drill to detail

• Reporting integrationReporting integration

Cognos Enterprise BI Series Cognos Enterprise BI Series 77

• Query Studio and Report Studio tools Query Studio and Report Studio tools

• Web-basedWeb-based

• Automatic data rankingAutomatic data ranking

• No problem with date dimensionNo problem with date dimension

• Good user-defined report schedulingGood user-defined report scheduling

Actuate Actuate 8Actuate Actuate 8

• Analytics Cube DesignerAnalytics Cube Designer

• Cross-join data from heterogeneous Cross-join data from heterogeneous sourcessources

• eSpreadsheet analysis tooleSpreadsheet analysis tool

• No problem splitting data fieldsNo problem splitting data fields

• Good user-defined report schedulingGood user-defined report scheduling

Information Builders WebFocus Information Builders WebFocus 77

• No OLAP architecture, no cubesNo OLAP architecture, no cubes

• Must learn native scripting languageMust learn native scripting language

• ““Accordion reports”Accordion reports”

Applix TM1Applix TM1

• Newcomer to marketNewcomer to market

• Seamless integration between OLAP Seamless integration between OLAP and reportingand reporting

• Displays data in ExcelDisplays data in Excel

• Must supply own SQL to build cubesMust supply own SQL to build cubes

MicroStrategy 8MicroStrategy 8

• Heavily dependent on proper data Heavily dependent on proper data warehouse design. Must have star warehouse design. Must have star schemas to properly create cubesschemas to properly create cubes

• Must use lookup tables to create the date Must use lookup tables to create the date dimensiondimension

• Not tolerant of dirty dataNot tolerant of dirty data• Integrates directly with Microsoft OfficeIntegrates directly with Microsoft Office• Better for data warehousing and data Better for data warehousing and data

mining than reporting and OLAP analysismining than reporting and OLAP analysis

Microsoft SQL Server 2000 Microsoft SQL Server 2000 Reporting Services and Reporting Services and Analysis ServicesAnalysis Services• Analysis services supports MOLAP, Analysis services supports MOLAP,

ROLAP, and HOLAPROLAP, and HOLAP• Easy to configure and define cubesEasy to configure and define cubes• One fact table per cubeOne fact table per cube• Reporting Services is an extension to Reporting Services is an extension to

Visual Studio.NET 2003 and best left to Visual Studio.NET 2003 and best left to developersdevelopers

• Surprisingly poor integration with Surprisingly poor integration with Excel!Excel!

Business Objects Enterprise Business Objects Enterprise 6.16.1

• Unique DOLAP architectureUnique DOLAP architecture

• Flexibility and low IT maintenanceFlexibility and low IT maintenance

ConclusionConclusion

• Frees IT staff from being report writesFrees IT staff from being report writes

• Business power users may know Business power users may know better what they want to know than IT better what they want to know than IT cancan

• Business users can explore data in Business users can explore data in new waysnew ways

• Pure players vs platform vendorsPure players vs platform vendors

Any questions?Any questions?