MGS 4020 Business Intelligence Business Intelligence Environment

1
MGS 4020Business Intelligence Business
Intelligence Environment ToolsJuly 5, 2007
2
Agenda
Business Intelligence Tools - Business Objects
Business Intelligence Tools - MS Excel
Business Intelligence Tools - Webi
Business Intelligence Tools - Cognos
3
Business Intelligence Environment
Internal Source Systems
Extract, Transformation and Load
External Data Sources
4
Business Intelligence Environment
Microsoft Excel
5
Agenda
Business Intelligence Tools - Business Objects
Business Intelligence Tools - MS Excel
Business Intelligence Tools - Webi
Business Intelligence Tools - Cognos
6
Microsoft Excel

• Why Excel is an effective BI Tool
• Extensive knowledge base
• Very good for what-if analysis
• Flexible
• Feature rich
• Great single user analysis tool
• Open Architecture support powerful Add-ins
• Ability to import data for analysis
• Where Excel falls short
• Some flexibility requires programming knowledge
• Database ability limited by Dfunctions
• Limited meta-data capabilities
• No server based components
• Mainly a document centric approach
• Interface to database has limitations

7
Microsoft Excel

• Features that support Business Intelligence
• Data-Import External Data (MS Query)
• Charting
• Pivot-Tables
• Database Functions (Dfunctions)
• If-Then-Else Logic
• VBA interface
• Uses Excel Database Functions to work with
  results
• Can uses VBA script to allow for Dynamic Queries

DAVERAGE DCOUNT DCOUNTA DGET DMAX
DMIN
DPRODUCT DSTDEV DSTDEVP DSUM DVAR
DVARP
8
BI Query Analysis Tool

• BI Query Analysis Tools
• Provides an end user the ability to query a
  database and analyze information
• BI Query Analysis Tool Vendors
• Business Objects
• Cognos
• Brio
• MicroStrategy

9
BI Query Analysis Tool

• Common Features
• Meta-Data Layer - Insulates the end user from the
  complexities of the data, database and technology
• Easy to use query interface
• Built in data functions
• Slice-and-Dice Capabilities
• Charting and Graphing
• Report Development
• Security
• Dashboarding
• BI Query and Analysis Meta-Data functions
• Defines table relationships
• Provides business naming
• Pre-defines calculations
• Reorganizes data fields
• Assists in data security
• Provides help text on tables, columns
  calculations

10
Agenda
Business Intelligence Tools - Business Objects
Business Intelligence Tools - MS Excel
Business Intelligence Tools - Webi
Business Intelligence Tools - Cognos
11
Business Objects

• What is Business Objects?
• BUSINESS OBJECTS is an integrated query,
  reporting and analysis solution for business
  professionals that allows you to access the data
  in your corporate databases directly from your
  desktop and present and analyze this information
  in a BUSINESS OBJECTS document.
• BUSINESS OBJECTS makes it easy to access this
  data, because you work with it in business terms
  that are familiar to you, not technical database
  terms like SQL. You dont need any knowledge of
  the database structure or technology. Once
  youve used BUSINESS OBJECTS to access the data
  you need, you can present

12
Business Objects What data sources are
available?

• BUSINESS OBJECTS let you access data from
  a wide range of sources. You can access data from
  
• Relational databases (RDBMS), such as ORACLE,
  Microsoft SQL Server, Informix and IBM DB2.
• Multidimensional (OLAP) databases, such as
  Microsoft OLAP Services, Hyperion Essbase, and
  ORACLE Express.
• Text files and spreadsheets
• Packaged applications such as SAP
• Virtually any data source using Microsoft Visual
  Basic for Applications (VBA)

13
Business Objects New Report Wizard

• To help you build a data provider when you create
  a new document, BUSINESS OBJECTS launches the New
  Report Wizard when you start the application for
  the first time

14
Business Objects Specify Data Access

• Select an option for the report layout, then
  click Begin. The Specify Data Access dialog box
  appears

15
Business Objects Select A Universe

• The next step depends on what you want to do
• If you chose to build a query on a universe in
  the previous step, the Select a Universe dialog
  box now appears

16
Business Objects What Exactly is a Universe?

• BUSINESS OBJECTS universes make it easy to access
  data, because they contain objects of data in
  business terms that are familiar to you. Whats
  more, you need no knowledge of the database
  structure, or of database technology, to be able
  to create powerful reports with data that is
  relevant to your work.
• Universes provide the business-intelligent,
  semantic layer that isolates you from the
  technical issues of the database. A universe maps
  to data in the database, in everyday terms that
  describe your business situation.
• Universes are made up of classes and objects. For
  example, the objects in a human resources
  universe would be Names, Addresses, Salaries,
  etc. Classes are logical groupings of objects.
  Each class has a meaningful name, such as
  Vacation (for objects pertaining to employees
  vacations). Each object maps to data in the
  database, and enables you to retrieve data for
  your reports.

17
Business Objects Classes Sub-classes
18
Business Objects Dimension objects, measure
objects and detail objects

• Dimension objects retrieve the data that will
  provide the basis for analysis in a report.
  Dimension objects typically retrieve
  character-type data (customer names, resort
  names, etc.), or dates (years, quarters,
  reservation dates, etc.)
• A detail object is always associated to one
  dimension object, on which it provides additional
  information. For example, Address is a detail
  object that is associated to Customer. Address
  provides additional information on customers,
  i.e., their addresses.
• Measure objects retrieve numeric data that is the
  result of calculations on data in the database.
  In the demo universe, Revenue is the calculation
  of number of items sold multiplied by item price.
  Measure objects are usually located in the
  Measures class.

19
Building a Query on the Query Panel
20
Building a Query on the Query Panel

• Classes appear as folders.
• Objects appear as cubes (for dimensions), spheres
  (for measures) or pyramids (for details).
• This button displays the universes predefined
  conditions.
• This button, selected by default, displays the
  universes classes and objects.
• Information on the selected class or object
  appears here.
• This button enables you to set options before
  running the query, for example to specify a
  maximum number of rows.
• The Result Objects box displays the objects that
  are included in the query.
• When you click Run, the query connects to the
  database and the data appears in the

21
Building a Query on the Query Panel

• Save and Close lets you save the query you have
  defined without running it. You can run it later
  on by using the Refresh command.
• When you click View, the raw data retrieved by
  the query appears in the Data Manager. From the
  Data Manager, you can edit, accept or cancel the
  query.
• When you click Run, the query connects to the
  database and the data appears in the

22
Building a Query on the Query Panel
Click Run. BUSINESS OBJECTS retrieves the data
for Resort, Year and Revenue, and displays this
in a new document
23
Creating User Objects What is a User Object?

• A universe consists primarily of classes and
  objects, created by the universe designer. If the
  objects in a universe do not meet your needs, you
  can customize the universe by creating your own
  objects, which are called user objects.
• User objects appear in the User Objects class in
  the universe. You include them in queries in the
  same way that you include regular objects.
  Moreover, you do not need to define a connection
  to a database to define a user object.

24
Creating User Objects

• To create a user object in any universe available
  to you
• Select the Universes command on the Tools menu.
  The Universes dialog box appears.
• Select the universe in which you want to create
  the user object, then click User Objects. The
  User Objects dialog box appears.
• Click Add. The User Object dialog box appears
• In the Definition tab, type the name of the user
  object in the Name field.

25
Creating User Objects

• In the Type list, select the type of the user
  object (Character, Number or Date).
• In the Description field, type a help text on the
  object. The help text appears when you select
  the user object in the Query Panel.
• In the Qualification box, click a radio button to
  qualify the user object as a dimension, a measure
  or a detail.
• Click the Formula tab to write the formula for
  the user object.
• Click OK. The user object you have created
  appears in the User Objects dialog box. The next
  time you build or edit a query on the universe,
  the user object you have created will appear in
  the User Objects class.

26
Creating User Objects - Formula

• Click the Formula tab, then check Get Assistance
  on Functions
• Double-click the objects, functions and operators
  you want to use in the user
• object's formula.
• When you double-click an object or an operator,
  it appears in the Formula box. The functions and
  operators you can select depend on the database
  at your site.
• When you double-click a function, the
  FunctionFunctionName dialog box appears.

27
Creating User Objects - Formula

• In the FunctionFunctionName dialog box, type an
  argument in each field.
• If the function's arguments include objects, user
  objects, functions or operators, you can
  double-click these in order to insert them in the
  function's formula.
• The arguments appear in the Formula box.
• If necessary, type text, numbers or dates in the
  formula. You must type quotes (") before and
  after text and numbers, but type a single quote
  (') before and after dates.
• Click OK, then click Test to check the syntax of
  the formula.

28
Applying a complex condition on a query

• Applying a complex condition requires
  three steps. First, you select the object you
  want, then the operator (e.g., greater than),
  then the operand (e.g., values that you type, or
  another object). The following procedure explains
  how to do it, and gives information to help you
  choose the operator and operand you need
• In the Query Panel, drag the object you want to
  use from the Classes and Objects list to the
  Conditions box. The Classes and Objects list
  turns into the Operators list

29
Applying a complex condition on a query
30
Applying a complex condition on a query

1. Double-click the operator you want to use. The
   Operators list turns into the Operands list

31
Applying a complex condition on a query

1. Double-click the operand you want. The following
   table helps you select the operand you need and
   tells you what to do next

32
Agenda
Business Intelligence Tools - Business Objects
Business Intelligence Tools - MS Excel
Business Intelligence Tools - Webi
Business Intelligence Tools - Cognos

• EIS / Dashboarding

33
EIS / Dashboarding

• To provide real time view of KPI and metrics
• EIS / Dashboarding vendors
• Cognos
• Business Objects
• http//www.businessobjects.com/products/dashboard_
  manager
• Brio
• Pilot Lightship

34
Business Objects - Dashboard
35
Agenda
Business Intelligence Tools - Business Objects
Business Intelligence Tools - MS Excel
Business Intelligence Tools - Webi
Business Intelligence Tools - Cognos
36
Webi 6.1 Reporting featureshttp//webiwh/wiasp/

• Report creation features new to WebIntelligence
  6.1
• Multi-block creation
• Multi-report creation
• Add pre-defined blocks and cells
• Local Variables
• Better control when formatting and positioning
• Improved layout properties
• Define Sections through Drag Drop (or Report
  Panel)
• Insert breaks sorts, calculations, rows,
  columns
• Filtering blocks and report simple filters
  compound filters
• Drag-and-drop report editing, turn to

37
Completely New Query Window

• Three modes
• Edit Query
• Edit Report
• View Results

38
Webi 6.1 Query Users

• For query users, the differences will be greater.
  Look and feel of the new query panel is much
  more user friendly and powerful, but will take
  some time to become familiar with
• The following slides will take you through some
  basic report creation and the new features of
  Webi 6.1
• Please Note
• When entering in a date range, you now must enter
  in the entire year(eg. 2003) and not just 03(eg.
  4/20/2003 as opposed to 4/20/03)
•  

39
Webi 6.1 Query Features
Edit Query create/edit a query, define query
filters, query properties, and/or scope of
analysis.
Edit Report change document format including the
structure (e.g multiblocks), layout and colors.
40
Webi 6.1 Edit Query
All New Documents start here, but users can
return to edit the query at any time
41
Webi 6.1 View and Display Modes

• When editing the report users may view a report
  in
• Structure or Results
• Normal or Page Layout
• Drill mode (View Results)

42
Webi 6.1 Edit Report
Normal Page Display(Page Layout button is
Toggled off)
43
Webi 6.1 Edit Report
Page Layout Display button is Toggled on
44
Webi 6.1 Tabs

• Report Manager Data, Templates, Properties, Map

The Properties tab displays is relevant to a
selected object, block, etc. The Map tab
facilitates navigation.
45
Webi 6.1 Edit Report Toolbar
User can toggle between View Structure andView
Results
Top Bar General Document-related features
Bottom Bar Selection specific features
(including Drill)
46
Webi 6.1 Report Pane
47
Webi 6.1 Filter Pane

• Filter zone Refining the display of report data

48
Webi 6.1 Feedback and Tool Tips

• Tool tips on icons mouseover help
• Tips feedback when dragging object (pre-drop)

On Mouseover Icons indicate filters, sorts
breaks
Clicking on these icons enables you to view their
properties
49
Webi 6.1 Show Me How Help

• Show me how icon,
• Top right corner of Java Report Panel
• Opens a page with links to flash demos
• Help menu

50
Webi 6.1 Multi-Block
Create Multi-blocks with new block Templates
Alternatively, Drag Drop existing block and
Turn to
51
Webi 6.1 Multi-report

• Reports may be added in
• Structure or Results
• Normal or Page Layout Views
• When in Drill mode

Inserting (or Duplicating) a Report
52
Webi 6.1 Block Cell Templates

• Block templates

53
Webi 6.1 Block Cell Templates

• Cell templates
• Blank cell
• Drill Filters
• Last refresh Date
• Page Number
• Page Number/Total Pages
• Total Number of Pages

54
Webi 6.1 Local Variables
Click Create Variable button
Save variable to local data objects
Select a cell

• Same workflow as in Business Objects

55
Webi 6.1 Layout Positioning
User Options button

• With user-options button
• Control layout and positioning of objects
• Define measurement unit
• Grid on/off, Snap to, Spacing

56
Webi 6.1 Inserting Sections

• Create sections with Drag Drop (as
  BusinessObjects)
• Sections sliders to denote the beginning/end
• Can be resized! (cant be done in 2.7 nor
  BusinessObjects!)
• Sections can be independent even better!
• Cells in between sections enables creation of
  sub-reports

Dragable sliders
Example of URL providing a logo to separate
sections
57
Webi 6.1 Inserting Breaks Sorts

• Breaks (Workflow like WebI and BusinessObjects)
• Sorts can be added (Workflow like
  BusinessObjects)
• and Sorts can be removed from breaks
• (can not do this in WebIntelligence 2.7 nor
  BusinessObjects)

58
Webi 6.1 Inserting Calcs, Rows, Cols

• Calculations can be selected from a menu

59
Webi 6.1 Filtering Reports Blocks

• Quick filters can be applied to
• Blocks
• Object
• Sections

Select object and click Quick Filter Button
Choose from LOV and filter is displayed
60
Webi 6.1 Normal Filters

• Drag drop object to the filter zone launches
  editor
• Filter defaults to Constant for performance
  reasons

Describes where the filter will be applied.
61
Webi 6.1 Simple Filters LOVs

• List of Values (with chunking feature)
• Users can search the entire list of values
• Users can navigate by chunk

Chunking feature
Search among entire list
62
Webi 6.1 Compound Filters

• Drag Drop to filter zone for compound filters
• Adding a simple filter to a simple filter or
  dragging a simple filter onto another simple
  filter creates a compound filter
• Clicking the AND/OR toggles between the two
  operators

63
Webi 6.1 Turn to
Right-click on table/chart to get a Turn To menu
Choose table/chart from dialog
Or, Drag Drop from Report Templates menu
64
Building Queries
65
Webi 6.1 Overview

• Familiar presentation (Full-Client
  WebIntelligence Mix)

Result Objects Data for report creation
Universe Objects Dimensions, Measures,
Details Predefined Filters
Query Filters Refining data to answer business
questions
Scope of Analysis Extend data for analysis
66
Webi 6.1 Objects

• Dimensions, Details Measures
• Defined in the universe in business terms

• Predefined Filters
• Facilitate access (different than with
  WebIntelligence 2.7)
• Also using everyday business terms

67
Webi 6.1 Hierarchical View
Hierachical view
68
Webi 6.1 Result Objects

• Displays Objects to be available in the report
• Semantic change from WebI 2.7 Full-Client
  Reporter
• With new WebIntelligence, Filters on measures are
  applied only to Result objects. Objects in the
  Scope of Analysis do not affect the query results
  when using filters on measures.
• In previous products, Filters on measures are
  applied to Results ObjectsScope of Analysis
  objects.

69
Webi 6.1 Overview

• Simple to understand manipulate

A simple dialog box provides the filter settings
70
Webi 6.1 Creation

• Drag Drop into Query Pane to display Filter
  dialog box

71
Webi 6.1 Creation via Drag Drop

• Drag Drop objects to create combination filters

Query Objects can be dragged to redefine their
logical context
72
Webi 6.1 Basic Choices

• All Simple Filters may be one of these types
• Constant user-defined text entry within Filter
  dialog box
• Values from list choose when displayed in
  Filter dialog box
• Prompt choose when list of values appears at
  refresh time
• Predefined Filters defined inside the universe,
  when added, no Filter dialog appears
• Simple Filters can be combined as And/Or operands

Clicking on And with toggle the operand to OR,
and vice-versa
73
Webi 6.1 Operators and Operands
Complete set of operators
Operand types Constant Values from List Prompts
74
Webi 6.1 Filter on Measures

• Filters on Measures Change from FC previous
  WebIntelligence
• The Scope of Analysis is not part of the
  measures aggregation

• with WebI
• Sales are filtered by Region, Customer and City
  (Results Objects Scope)

75
Webi 6.1 Advanced Filters

• When combined with simple filters, Advanced
  filters enable more complex business questions

Results Objects for this Query
Show me all Cities, Stores and Revenue for
Countries WHERE Revenue gt 1m for the Year 2002

• Such Filters are Impossible to do with simple
  Query Filters because they require a Sub-Query

76
Webi 6.1 Advanced Filters

• Another example

Results Objects for this Query
Show me all Years, Products, Sales FOR
(City,Year)WHERE Units Sold gt 10,000
Again, Advanced filters enable Sub-Queries, i.e.
more complex business questions
77
Webi 6.1 SQL Subqueries

• Advanced Filters change a filters aggregation
  level
• The SQL the query generates uses a subquery
• Advanced filters can be nested as well

Advanced Filter with Two Objects and a simple
combination filter
Advanced Filter with an Advanced Filter embedded
(nested Advanced Filter)
78
Webi 6.1 Prompts List of Values

• Prompts Used with query filters (not report
  filters)

Prompt with previously selected values
Select default values that report viewers will see
79
Webi 6.1 Prompts List of Values

• 2 different types of prompts
• Defined in the query itself (are prompted first)
• Defined in Universe (in objects, join, Custom
  List of Values these are prompted after Query
  prompts)
• Prompts Report creators can define prompts
  order for prompts defined in the query panel
• 1st Country, 2nd Region, 3rd City, then Store
• Expected format (Date Number) helps ease-of-use
• Any operator (e.g. except, isnull, etc) can be
  used to define a prompt

80
Webi 6.1 Prompts List of Values

• List of Values features
• LOV are used both during Report Creation and also
  at Run-Time by report viewers
• Custom List of Values can be predefined within
  Designer
• Single or multiple column
• Full Compatibility with Designer 5.x
• Nested LOV (or cascading prompting) enables a
  user-oriented workflow
• Plus new extra features oriented performance
  scalability

81
Webi 6.1 Prompts List of Values

• List of Value Enhancements
• Ascending/Descending sorts
• Search values (wildcards)

• Uses batch display to increase performance time
  for very large lists

82
Webi 6.1 Prompts List of Values Browsing

• Browse List of Values by Batch
• Allow to handle very huge LOV
• The default batch size is a server parameter,
  allow to size according to the server
  configuration (default at 1,000)
• The entire values of the LOV is cached during the
  user session (per document)

83
Webi 6.1 Prompts List of Values Sorting

• Sorting the List of Values
• Ascending, Descending sort
• The batch display is still on, when sorting, the
  LOV is positioned to the first batch
• For Custom LOVs, the sort is available for each
  column

84
Webi 6.1 Prompts List of Values Searching
Without search

• Search Values through a search pattern
• Use to filter values (pattern matching)

With search

• The batch display is still active even when
  searching
• Results are batched to match the pattern

85
Webi 6.1 Query Panel Basic Prompt Workflow

• At Design-Time

Filters can include many prompts
Users can define prompt order for the refresh
workflow
86
Webi 6.1 Query Panel Scope of Analysis

• Scope of Analysis pane same as WebIntelligence
  2.7
• Define the scope either by levels (1,2,3) or
  create a custom Scope of Analysis

The Scope is deduced from the Results Objects and
the Hierarchies defined within the universe
Year, Product Family, Country are in the Results
Objects (white). Objects in Gray are the extended
scope
87
Webi 6.1 Query Panel Query Properties
Query Properties available in the second tab

• Limits
•  Max rows retrieved  limits the data in the
  document
•  Max retrieval time  limits the execution time
  of the query (in seconds)
• Data
• Retrieve duplicate rows Checked by default
• Security
• Allow other users to edit the query Checked by
  default
• Prompt Order (refer to Prompt chapter)
• Context
• Reset contexts on refresh contexts will be
  prompted for each refresh. If Checked, the preset
  contexts are reused without prompt during refresh
• Clear Contexts allows to reset the preset list

88
Webi 6.1 Query Panel View SQL

• Show the generated SQL
• Displays mono- and multi-flow Synchronization,
  Join, Union, Intersection, Minus
• Can copy and paste elsewhere

89
Agenda
Business Intelligence Tools - Business Objects
Business Intelligence Tools - MS Excel
Business Intelligence Tools - Webi
Business Intelligence Tools - Cognos
90
Cognoshttp//hoteleservices.sabre.com/booking.htm
l