oracle bi 12c: create analyses and dashboards · pdf filelearn more from oracle university at...

Learn more from Oracle University at education.oracle.com

Oracle BI 12c: Create Analyses and Dashboards Student Guide - Volume I

D95505GC10

Edition 1.0 | April 2017 | D97820

Copyright © 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Disclaimer This document contains proprietary information and is protected by copyright and other intellectual property laws. You may copy and print this document solely for your own use in an Oracle training course. The document may not be modified or altered in any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform, reproduce, publish, license, post, transmit, or distribute this document in whole or in part without the express authorization of Oracle. The information contained in this document is subject to change without notice. If you find any problems in the document, please report them in writing to: Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not warranted to be error-free. Restricted Rights Notice

If this documentation is delivered to the United States Government or anyone using the documentation on behalf of the United States Government, the following notice is applicable: U.S. GOVERNMENT RIGHTS The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or disclose these training materials are restricted by the terms of the applicable Oracle license agreement and/or the applicable U.S. Government contract.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

iii

Contents

1 Introduction

Agenda 1-2

Introductions 1-3

Training Site Information 1-4

Audience 1-5

Course Goal 1-6

Course Objectives 1-7

Methodology 1-9

Schedule 1-10

2 Introduction to Oracle Business Intelligence Enterprise Edition

Objectives 2-2

What Is Business Intelligence? 2-3

Oracle Business Intelligence Enterprise Edition 2-4

Oracle BI 12c Highlights 2-5

Many More Usability Improvements 2-6

New Advanced Analytics Capabilities 2-7

Prebuilt Functions 2-8

Oracle Mobile BI 2-9

More Data Sources Supported Now 2-10

OBIEE 12c In-Memory Analytics 2-11

Visual Exploration for Everyone 2-12

Rich Data Mashup 2-13

Oracle BI 12c Architecture 2-14

Oracle BI Components 2-15

BI System Components 2-17

BI Presentation Services 2-19

Oracle BI Presentation Server and Catalog 2-20

BI Server 2-21

Oracle BI Repository 2-22

Data Sources 2-23

Sample Analysis Processing 2-24

Oracle BI EE Dashboard 2-25

Oracle BI EE Terminology 2-26

Signing In to Presentation Services 2-29

iv

Oracle BI Enterprise Edition Home Page 2-30

Global Header 2-31

Administering Objects in the Presentation Catalog 2-32

Essbase: Overview 2-33

Essbase: Key Features 2-34

Quiz: Overview 2-35

Quiz 2-36

Summary 2-39

Practice 2: Overview 2-40

3 Working with Oracle Business Intelligence Analyses

Objectives 3-2

Oracle Business Intelligence Analysis Editor 3-3

Key Terms 3-4

Subject Areas 3-7

Columns 3-8

Hierarchies 3-9

Oracle Business Intelligence Analysis 3-10

Creating and Editing Analyses 3-11

Analysis Editor 3-12

Subject Areas and Catalog Panes 3-13

Analysis Editor Tabs 3-14

Working with Analyses in Oracle Business Intelligence 3-15

1. Construct an Analysis 3-16

1. Construct an Analysis using Multiple Subject Areas 3-17

2. Modify Analysis Criteria 3-18

2. Modify Analysis Criteria (Sorting Columns) 3-19

2. Modify Analysis Criteria (Formulas) 3-20

2. Modify Analysis Criteria (Style) 3-21

2. Modify Analysis Criteria (Column Format) 3-22

2. Modify Analysis Criteria (Data Format) 3-23

2. Modify Analysis Criteria (Conditional Format) 3-24

2. Modify Analysis Criteria (Interaction) 3-26

2. Modify Analysis Criteria (Write Back) 3-27

3. Change Column Order 3-28

4. Display Analysis Results 3-29

5. Save an Analysis 3-30

Setting Analysis Properties 3-31

Analysis Properties: Results Display Tab 3-32

Analysis Properties: Interactions Tab 3-33

Analysis Properties: Data Tab 3-34

v

Adding Basic Formatting to Table Views 3-36

Formatting Containers 3-37

Formatting Column Headers and Values 3-38

Setting Table Properties 3-39

Data Viewing Properties 3-40

Other Style Properties 3-41

Advanced Formatting 3-42

Saving Column Settings as Systemwide Defaults 3-43

Copying and Pasting Formatting Attributes 3-44

Using a Saved Analysis as a Style Template 3-45

Customized Date and Time Format Strings 3-46

Applying a Customized Format String to a Column 3-47

Quiz: Overview 3-48

Quiz 3-49

Summary 3-51


4 Filtering Data for Analyses

Objectives 4-2

Filters 4-3

Applying Filters to Attribute Columns to Affect Hierarchical Columns 4-4

Inline Objects vs. Named Objects? 4-5

EVALUATE_PREDICATE Function with a Filter 4-6

Using Filters to Limit Data in Analyses 4-7

Creating and Editing Filters 4-8

Grouping Filters 4-11

Dynamic Filtering 4-12

Using a Saved Analysis as a Filter 4-13

Editing SQL for a Column Filter 4-14

Drilling and Filters 4-15

Analysis Prompts 4-16

Adding an Inline Prompt to an Analysis 4-17

Defining Prompt Properties 4-18

Multiple Column Filter Prompts 4-20

Quiz: Overview 4-21

Quiz 4-22

Summary 4-25


vi

5 Selecting and Grouping Data for Analysis

Objectives 5-2

Using Selections to Limit Data in Analysis 5-3

Creating Selection Steps 5-4

1. Create a New Step 5-5

2. Select the Step Type 5-6

Step Types: Selecting Members 5-7

Step Types: Adding Groups and Calculated Items 5-8

Step Types: Applying Conditions 5-9

Step Types: Selecting Members Based on Hierarchy 5-10

Step Types: Selecting Members Based on Family Relationship 5-11

Saving Selection Steps as a Group 5-12

How Do Filters and Selection Steps Differ? 5-13

Groups 5-14

Creating Groups 5-15

1. Create a New Member Group 5-16

2. Define the Group Properties 5-17

3. Apply Formatting 5-18

4. Verify the Results 5-19

Calculated Items 5-20

Creating Calculated Items 5-21

1. Create a New Calculated Item 5-22

2. Define the Calculated Item Properties 5-23

3. Verify the Results 5-24

Quiz: Overview 5-25

Quiz 5-26

Summary 5-30


6 Advanced Visualization in Analyses

Objectives 6-2

Oracle Business Intelligence Views 6-3

Descriptions of Views 6-4

View Editors 6-6

Compound Layout Pane 6-7

Working with Views in Compound Layouts 6-8

Directly Editing Table and Pivot Table Views in Compound Layouts 6-9

Recommended Visualizations 6-10

Creating and Copying Compound Layouts 6-12

Views Pane 6-13

Descriptions of Graph Views 6-15

vii

Graph Editor 6-17

Graph Slider 6-18

Graph Properties: General 6-19

Graph Properties: Style 6-20

Graph Properties: Scale 6-21

Graph Properties: Titles and Labels 6-22

Formatting Graphs by Position 6-23

Formatting Graphs Conditionally 6-24

Performing Common Tasks in Views 6-25

Specifying Totals 6-26

Managing Aggregation 6-27

Combining Values into Bins 6-28

Creating Bins: Add Bins 6-29

Quiz: Overview 6-30

Quiz 6-31

Summary 6-37


7 Showing Results with Pivot Tables

Objectives 7-2

Pivot Table Views 7-3

Analyzing and Displaying Data in Table and Pivot Table Views 7-4

Pivot Table 7-5

Creating a Pivot Table 7-6

1. Select Columns on the Criteria Tab 7-7

2. Click the Results Tab and Select the Pivot Table View 7-8

3. Open the Pivot Table Editor 7-9

4. Rearrange Elements as Necessary 7-10

Showing Results in Pivot Tables 7-12

Sorting Data 7-13

Sorting Data in Views 7-14

Using Hierarchical Columns 7-15

Hierarchical Columns 7-16

Overriding Default Aggregation Rules 7-18

Using Totals 7-19

Adding Formatting: Overall Appearance 7-20

Adding Formatting: Pivot Table Positions 7-21

Adding Formatting: Section Options 7-22

Showing an Item’s Relative Value 7-23

Using Calculations in Pivot Tables 7-24

Building Calculations by Using Functions 7-25

viii

Building Calculations by Using Formulas 7-26

Displaying Running Sums in Pivot Tables 7-27

Pivot Table with a Graph 7-28

Quiz: Overview 7-29

Quiz 7-30

Summary 7-33


8 Working with Additional Views in Analyses

Objectives 8-2

Trellis View 8-3

Creating a Simple Trellis View 8-4


2. Click the Results Tab and Add a Simple Trellis View 8-6

3. Open the Trellis Editor 8-7


Advanced Trellis View 8-10

Creating an Advanced Trellis View 8-11


2. Click the Results Tab and Add an Advanced Trellis View 8-13

3. Open the Trellis Editor 8-14


Performance Tile View 8-17

Creating a Performance Tile 8-18


2. Click the Results Tab and Add a Performance Tile 8-20

3. Open the Performance Tile Editor 8-21

4. Make the Formatting Changes 8-22

Treemap View 8-23

Creating a Treemap 8-24


2. Click the Results Tab and Add a Treemap 8-26

3. Open the Treemap Editor 8-27


Heat Matrix View 8-29

Creating a Heat Matrix 8-30


2. Click the Results Tab and Add a Heat Matrix 8-32

3. Open the Heat Matrix Editor 8-33


Linking Master–Detail Views 8-35

ix

Defining the Master View 8-36

Defining the Detail View 8-37

Master–Detail Linking of Views 8-38

Quiz: Overview 8-39

Quiz 8-40

Summary 8-42


9 Visualizing Data on Maps

Objectives 9-2

MapViewer 9-3

Map Views 9-4

Map View: Example 1 9-5



Oracle Spatial Components 9-8

Manage Map Data 9-10

Import Layer 9-11

Edit Layer 9-12

Import Background Map 9-13

Edit Background Map 9-14

Images 9-15

Creating and Editing Map Views 9-16

1. Add a Map View 9-17

2. Set Properties 9-18

3. Edit Formats: Format Options 9-19

3. Edit Formats: Define Formats 9-21

Interacting with Map Views 9-22

Drilling in Map Views 9-23

Master–Detail Linking in Map Views 9-24

Action Links in Map Views 9-26

Other Applications for Map Views 9-27

Other Points of Integration 9-28

Quiz: Overview 9-29

Quiz 9-30

Summary 9-31


10 Creating Oracle Business Intelligence Dashboards

Objectives 10-2

Dashboards 10-3

x

Dashboard Pages 10-4

Creating and Editing a Dashboard 10-6

Creating a New Dashboard from Global Header 10-7

Global Header: Setting Preferences for Your Default Dashboard View 10-8

Using Dashboard Builder 10-10

Using the Dashboard Builder Toolbar 10-11

What Can I Add to a Dashboard? 10-12

Adding Objects to a Dashboard -14

Dashboard Builder Tools Toolbar: Layout 10-15

Tools Toolbar: Dashboard Properties 10-16

Dashboard Properties: Dashboard Report Links 10-18

Dashboard Properties: Dashboard Page Controls 10-19

Tools Toolbar: Print Options 10-20

Tools Toolbar: Page Report Links 10-21

Tools Toolbar: Prompts Buttons on Current Page 10-23

Tools Toolbar: Allow Saving Personal Customizations 10-24

Tools Toolbar: Publishing Dashboard Pages 10-25

Tools Toolbar: Advanced Page Properties 10-26

Exploring Dashboard Object Properties 10-27

Exploring Column Properties 10-28

Exploring Section Properties 10-29

Exploring Section Properties: Arranging Sections Horizontally 10-31

Exploring Section Properties: Controlling the Drilldown Display 10-32

Section Properties: Conditions 10-33

Section Properties: Adding an Inline Condition for an Analysis 10-35

Section Properties: Adding an Inline Condition for a KPI 10-37

Section Properties: Adding a Named Condition 10-39

Section Properties: Formatting and Renaming 10-40

Miscellaneous Dashboard Page Functionality 10-41

Page Options Toolbar: Creating Personal Customizations 10-42

Page Options Toolbar: Creating Shared Customizations 10-43

Page Options Toolbar: Creating Links to Dashboard Pages 10-44

Page Options Toolbar: Adding Content to a Briefing Book 10-46

Page Options Toolbar: Export to Excel 10-47

Quiz: Overview 10-48

Quiz 10-49

Summary 10-55


xi

11 Configuring Oracle Business Intelligence Dashboards

Objectives 11-2

Dashboard Objects 11-3

Alerts Section 11-4

Action Link and Action Link Menu 11-5

Link or Image 11-7

Embedded Content 11-10

Text 11-11

Text: ActiveX 11-12

Text: Scripts 11-13

Text: Audio 11-14

Folder 11-15

Breadcrumbs 11-16


Quiz 11-18

Summary 11-19


12 Creating Dashboard Prompts and Variables

Objectives 12-2

Variables 12-3

Session Variables 12-4

Repository Variables 12-5

Presentation Variables 12-6

Request Variables 12-7

Referencing Variables 12-8

Syntax for Referencing Variables 12-9

Dashboard Prompts 12-11

Dashboard Prompts: Column Prompts 12-12

Dashboard Prompts: Other Prompts 12-14

Adding a Named Column Prompt to a Dashboard 12-15

1. Create a New Named Column Prompt 12-16

2. Select the Prompt Type 12-17

3. Select the Subject Area and Column 12-18

4. Select the Operator and Other Options 12-19

5. Save the Prompt 12-23

6. Preview the Prompt 12-24

7. Add the Prompt to the Dashboard 12-25

8. Set the Scope for the Prompt 12-26

9. Test the Results 12-27

Adding a Hidden Named Prompt to a Dashboard 12-29

xii

1. Edit Dashboard Properties 12-30

2. Navigate the Catalog to Locate the Named Prompt 12-31


Creating a Second Prompt -33

1. Add a New Prompt to an Existing Prompt Page 12-34

2. Insert a Page Break 12-35

3. Edit the Dashboard and Add the Prompt 12-36


Setting Preferences for a Prompt Page 12-39

Presentation Variables and Prompts 12-41

Request Variables and Prompts 12-42

Adding a Variable Prompt to a Dashboard 12-43

1. Add a Variable to an Existing Analysis 12-44

2. Create a New Variable Prompt 12-46

3. Select the Prompt Type 12-47

4. Define the Prompt Properties 12-48

5. Save the Prompt 12-49

6. Select the Dashboard 12-50

7. Add the Prompt to the Dashboard 12-51

8. Navigate the Catalog to Locate the Named Prompt 12-52


Adding a Hierarchical Prompt 12-54

Applying Prompts with Default Values 12-55

Wiring Dashboard Prompts 12-56

Auto-Complete for Prompts 12-58

Optional Apply and Reset Buttons for Prompts 12-60


Quiz 12-62

Summary 12-64


13 Scorecards and Key Performance Indicators

Objectives 13-2

Oracle Scorecard and Strategy Management 13-3

Balanced Scorecard 13-5

Scorecard Editor 13-6

Scorecard Objects 13-8

Scorecard Documents (Views) 13-10

Strategy Tree: Strategy Tree Diagram 13-11

Strategy Tree: Contribution Wheel Diagram 13-12

Strategy Map 13-14

xiii

Cause and Effect Map 13-15

Creating a Scorecard 13-16

1. Create a New Scorecard 13-18

2. Create Perspectives 13-19

3. Create KPIs 13-20

3.1 Create a New KPI 13-21

3.2 Define General Properties 13-22

3.3 Set the Data Format and Enable Trending 13-23

3.4 Define the Dimensionality 13-24

3.5 Define States and Thresholds 13-25

3.6 Define Related Documents 13-27

3.7 Define Custom Attributes 13-28

4. Create KPI Watchlists 13-29

5. Create KPI Smart Watchlists 13-30

6. Create and Arrange Objectives 13-31

7. Create and Arrange Initiatives 13-32

8. Assign Weights to Individual Objectives and Initiatives 13-33

9. Set Assessment Mappings 13-35

10. Graphically Depict Relationships 13-36

11. Use Comments 13-37

12. Add Scorecard Views to Dashboards 13-38

13. Define Mission and Vision Statements 13-39

14. Set Up Point of View Controls 13-40

Attach KPI Watchlist to Dashboard 13-42

Working with Objects in the Scorecard Editor 13-43


Quiz 13-46

Summary 13-52


14 Visual Analyzer, BI Ask, and Data Mashup

Objectives 14-2

Visual Analyzer 14-3

Visual Analyzer Login Page 14-4

Visual Analyzer Projects 14-6

Creating Visual Analyzer Projects 14-7

Select the Data Source 14-8

Select Content 14-9

Set Visualization Type 14-11

Add More Visualization 14-12

Rearrange the Visualizations in a VA project 14-13

xiv

Filter the Data in Visual Analyzer 14-15

Create Calculations 14-16

BI Ask 14-17

Data Mashups 14-18

Adding a New Data Source 14-19

Subject Area in the Visual Analyzer 14-22


Quiz 14-24

Summary 14-26


15 Working with BI Content in Smart View

Objectives 15-2

Setting Up Smart View 15-3

Setting Up a Smart View Connection 15-4

Working with Views in Smart View Clients 15-5

Document Contents Pane 15-6

Working with Analysis Prompts 15-7

Working with Page Prompts 15-8

Refreshing Views 15-9

Setting Refresh Preferences 15-11

Masking Data in Views 15-13

View Properties 15-15

Customizing Data in Office 15-16

Editing Microsoft Office Charts 15-17

Changing Chart Layout and Style 15-18

Changing PowerPoint Themes 15-19

Copying and Pasting BI Objects 15-20

Copying and Pasting BI Objects Between Smart View Clients 15-21

Copying and Pasting Graph Views 15-22

Copying and Pasting Table or Pivot Table Views 15-23

Copying and Pasting Views from Oracle BI to Smart View 15-24

Editing Analyses in Oracle BI EE 15-25

Creating and Publishing Views Using the View Designer 15-26

Creating a View Layout and Type Steps 1–3 15-27

Creating a View Layout and Type Steps 4–5 15-28

Creating a View Layout and Type Step 6 (Optional) 15-29

Creating a View Layout and Type Step 7 (Optional) 15-30

Defining Filter Expressions in the View Designer 15-31

Publishing Views to the BI Catalog 15-32

Guidelines for Publishing Ad Hoc Views 15-33

xv

Federating Data 15-34


Quiz 15-36

Summary 15-39


16 Oracle Business Intelligence Mobile App Designer

Objectives 16-2

BI Mobile App Designer 16-3

BI Mobile App Designer Trial Edition 16-4

Creating Basic Mobile Apps 16-5

1. Starting Oracle BI Mobile App Designer 16-6

2. Exploring the Mobile App Designer Interface 16-8

3. Optional: Modifying the Cover Page 16-9

4. Adding Pages to the App 16-10

5. Previewing the App 16-11

Tile Pages 16-12

Designing Apps with Tile Pages 16-13

1. Inserting Tile Pages 16-14

2. Adding Stoplight Formatting 16-16

Subpage 16-17

Navigation Pages 16-18

Creating Navigation Pages 16-19

1. Inserting Navigation Pages 16-20

2. Creating a Navigation Hierarchy 16-21


Quiz 16-23

Summary 16-26


17 Using Oracle Business Intelligence Delivers

Objectives 17-2

Oracle Business Intelligence Delivers 17-3

Delivery Devices 17-4

Delivery Profiles 17-5

Creating a Delivery Device 17-6

Creating a Delivery Profile 17-7

Business Intelligence Alerts 17-8

Adding an Alert Section to a Dashboard 17-9

Agents 17-10

How Agents Work: Simplified Format 17-11

xvi

Configuring an Agent 17-12

1. Configure the General Tab 17-13

2. Configure the Schedule Tab 17-14

3. Configure the Condition Tab 17-15

4. Configure the Delivery Content Tab 17-16

5. Configure the Recipients Tab 17-17

6. Configure the Destinations Tab 17-19

7. Configure the Actions Tab 17-21

Subscribing to an Agent 17-23

Listing Your Agents 17-24

Using Agents to Deliver Content 17-25

Using Conditions to Deliver Content 17-26

1. Create a New Agent and Assign an Analysis for Its Condition 17-27

2. Define How the Condition Evaluates to “True” 17-28


Using Agents to Proactively Respond to KPI Performance 17-30

1. Create a New Agent and Assign a KPI for Its Condition 17-31

2. Define How the Condition Evaluates to “True” 17-32



Quiz 17-35

Summary 17-38


18 Administering the Presentation Catalog

Objectives 18-2

Security Goal: Access and Actions 18-3

Oracle BI Security and Catalog Security 18-4

Default BI Roles 18-6

Presentation Catalog Folder and Object Permissions 18-8

Permission Inheritance 18-9

Default Application Role Hierarchy: Example 18-10

Setting Object Permissions 18-11

1. Click Permissions 18-12

2. Add Roles 18-14

3. Set Permissions 18-15

Recommendations for Setting Permissions 18-16

Viewing and Setting Properties 18-17

Setting System Privileges 18-18

1. Click the Administration and Manage Privileges Links 18-19

2. Pick Users and Roles 18-20

xvii

Catalog Groups 18-21

Archiving Catalog Items 18-22

Oracle BI Briefing Books 18-24

Working with Oracle BI Briefing Books 18-25

Add Content to a New or Existing Oracle BI Briefing Book 18-26

Edit an Oracle BI Briefing Book 18-29

Download an Oracle BI Briefing Book 18-30

Working with Favorites 18-31

Adding Objects to Favorites 18-32

Accessing Favorite Objects 18-33

Organizing Favorites 18-34

Removing Favorites 18-35


Quiz 18-37

Summary 18-39


19 Direct Database Analysis

Objectives 19-2

Direct Database Analysis Privileges 19-3

Changing Direct Database Analysis Privileges 19-4

Creating and Executing a Direct Database Analysis 19-5

1. Create a Direct Analysis 19-6

2. Construct a Direct Database Analysis 19-7

Editing XML and SQL 19-8

Using the Advanced Tab to Modify XML 19-9

Using the Advanced Tab to Modify SQL 19-10

Using the Advanced Tab to Include Advanced SQL Clauses 19-11


Quiz 19-14

Summary 19-18


20 Expressions

Objectives 20-2

SQL Syntax and Semantics 20-4

Aggregate Functions 20-5

Running Aggregate Functions 20-7

Time Series Functions 20-8

Conversion Functions 20-9

Database Functions 20-10

xviii

Advance Analytics Internal Logical SQL Functions 20-12

BIN Function 20-13

WIDTH_BUCKET Function 20-14

TRENDLINE Function 20-15

Advance Analytics External Logical SQL Functions 20-16

EVALUATE_SCRIPT 20-17

FORECAST 20-18

CLUSTER 20-19

OUTLIER 20-20

REGR 20-21


Quiz 20-23

Summary 20-25


A Oracle Business Intelligence Mobile

Objectives A-2

Oracle Business Intelligence Mobile A-3

Getting BI Mobile A-5

Launching the BI Mobile Application A-6

Setting Up User Credentials A-7

First Time Help Screen: Home Page A-8

Oracle BI Mobile User Interface A-9

First Time Help: Settings A-10

Configuring Oracle BI Mobile A-11

Navigating BI Mobile on Your Mobile Device A-12

Navigating the Carousel, Tile, and List Subviews A-13

Navigating to Dashboards A-14

Working with Favorites A-15

Working with Local Content A-16

Performing Searches A-17

Adjusting Search Settings A-18

Working with BI Content A-19

Working with Object Options A-21

Oracle BI Mobile Security Toolkit A-22

Quiz: Overview A-24

Quiz A-25

Summary A-27