1 Introduction to the Semantic Layer

29

1.1 What Is a Semantic Layer?

29

1.2 A Well-Designed Semantic Layer

33

1.2.1 Characteristics

33

1.2.2 Designing a Semantic Layer

34

1.3 Semantic Layer Components

35

1.3.1 The Universe

36

1.3.2 The Information Engine

38

1.3.3 Information Design Tool

40

1.3.4 Client Tool Technology

40

1.4 Deploying the Semantic Layer in SAP Business­Objects BI 4.0

42

1.5 SAP Business­Objects BI Applications that Consume the Semantic Layer

44

1.5.1 SAP Business­Objects Web Intelligence

44

1.5.2 SAP Crystal Reports for Enterprise

46

1.5.3 SAP Business­Objects Dashboards

47

1.5.4 SAP Business­Objects Explorer

48

1.5.5 SAP Lumira

50

1.5.6 SAP Predictive Analysis

52

1.6 Summary

53

2 Introduction to Information Design Tool

55

2.1 Installing Information Design Tool

56

2.2 Getting Started

58

2.2.1 Welcome Page

58

2.2.2 New Universe Wizard

60

2.3 Information Design Tool Interface

62

2.3.1 Menu and Toolbar

63

2.3.2 Working with Views

64

2.3.3 Working with Editors

65

2.4 Help

67

2.4.1 Context-Sensitive Help

67

2.4.2 Cheat Sheets

68

2.4.3 Online Tutorials

70

2.5 Preferences

71

2.5.1 Business Layer Editor

72

2.5.2 Check Integrity

72

2.5.3 Data Foundation Editor

73

2.5.4 Languages

76

2.5.5 Online Tutorials

76

2.5.6 Secured Connections

76

2.5.7 Show Values

76

2.6 Data Preview

77

2.6.1 Raw Data Tab

79

2.6.2 Distinct Values Tab

81

2.6.3 Analysis Tab

82

2.7 Summary

83

3 Local and Shared Projects

85

3.1 Authoring Resources in Local Project

86

3.1.1 Local Project and File System

86

3.1.2 Local Project Lifecycle

89

3.1.3 Resource Dependencies

90

3.2 Managing Local Projects

91

3.2.1 Creating a Local Project

91

3.2.2 Closing and Opening a Local Project

92

3.2.3 Managing Resources and Folders

92

3.2.4 Filtering Resources in Projects

94

3.2.5 Viewing Local Dependencies

95

3.2.6 Deleting a Local Project

95

3.3 CMS Repository

96

3.3.1 Session to CMS Repository

96

3.3.2 Predefined Sessions

98

3.3.3 Authentication Modes

99

3.3.4 Folders and Objects

100

3.3.5 Predefined Groups

101

3.4 Managing Sessions

102

3.4.1 Opening a Session

102

3.4.2 Using the Session

103

3.4.3 Creating a Predefined Session

104

3.4.4 Opening a Predefined Session

105

3.4.5 Closing a Session

106

3.4.6 Deleting a Predefined Session

106

3.5 Shared Projects

106

3.5.1 Shared Project Definition

107

3.5.2 Synchronizing

107

3.5.3 Security

108

3.5.4 Locking and Unlocking Resources

109

3.6 Managing Shared Resources

109

3.6.1 Creating a Shared Project

109

3.6.2 Opening a Shared Project

113

3.6.3 Synchronizing Resources

113

3.6.4 Locking and Unlocking Resources

114

3.6.5 Deleting a Shared Project

114

3.7 Summary

115

4 Connecting to Data Sources

117

4.1 Data Sources Typology

117

4.1.1 Relational Connections

118

4.1.2 Relational Connections to SAP NetWeaver BW and SAS

121

4.1.3 OLAP Connections

121

4.2 Connection Persistence

123

4.2.1 Secured Connections

124

4.2.2 Connection Shortcuts

125

4.2.3 Local Connections

126

4.2.4 Connection Usages

127

4.3 Authentication Modes

127

4.3.1 Fixed Credentials

128

4.3.2 Credentials Mapping

129

4.3.3 Single Sign-On

130

4.3.4 Using Credentials Mapping for Single Sign-On

132

4.4 Client and Server Deployment

133

4.4.1 Middleware and Drivers

133

4.4.2 Connection Server

134

4.4.3 Download Connection Locally

135

4.4.4 Relational Connections to SAP NetWeaver BW and SAS

136

4.4.5 OLAP Connections

136

4.5 Connection Parameters

137

4.5.1 Authentication and Data Source Parameters

137

4.5.2 Connection Server Configuration Parameters

138

4.5.3 Connection Server Custom Parameters

139

4.5.4 Configuration Files

140

4.6 Managing Connections

141

4.6.1 Creating a Connection in a CMS Repository

142

4.6.2 Creating a Local Connection

145

4.6.3 Publishing a Connection

146

4.6.4 Creating a Connection Shortcut

146

4.6.5 Editing a Connection

147

4.6.6 Switching Server/Client Middleware

148

4.6.7 Testing a Connection

149

4.7 Data Preview

150

4.7.1 Relational Database

150

4.7.2 OLAP Connections

152

4.8 Summary

155

5 The Data Foundation

157

5.1 Creating a Data Foundation

159

5.2 Identifying Tables, Columns, and Keys

162

5.2.1 Database Catalog Browser

163

5.2.2 View Table Data and Information

165

5.2.3 Insert Tables in the Data Foundation

166

5.2.4 Search and Filter

168

5.3 Identifying Joins, Keys, and Cardinalities

169

5.3.1 Joins and Cardinalities

169

5.3.2 Join Editor

172

5.3.3 Join Types

175

5.3.4 Detect Joins, Keys, and Cardinalities

178

5.3.5 Joins Strategy

183

5.4 Derived Tables

184

5.4.1 Create a Derived Table in the Data Foundation Schema

185

5.4.2 Create a Derived Table from a Selected Table

189

5.4.3 Replace a Table with a New Derived Table

189

5.4.4 Merge Multiple Tables and Derived Tables in a New Derived Table

190

5.5 Calculated Columns

191

5.6 Solving Loops (Alias Tables, Contexts, Shortcut Joins)

194

5.6.1 Resolving Loops by Eliminating Joins

196

5.6.2 Resolving Loops with Alias Tables

197

5.6.3 Resolving Loops with Contexts

198

5.6.4 Create and Edit Contexts

200

5.6.5 Shortcut Joins

203

5.6.6 Detection Tools

204

5.7 Chasm Traps and Fan Traps

206

5.7.1 Fan Traps

206

5.7.2 Chasm Traps

209

5.8 List of Values

212

5.8.1 Static List of Values

213

5.8.2 List of Values Based on Custom SQL

215

5.8.3 List of Values Parameters and Options

216

5.8.4 Customize List of Values Columns

217

5.9 Parameters

218

5.9.1 Parameter Definition

219

5.9.2 Prompt Text

220

5.9.3 Data Type

220

5.9.4 Allow Multiple Values

221

5.9.5 Keep Last Values

221

5.9.6 Index Aware Prompt

221

5.9.7 Associated List of Values

221

5.9.8 Select Only from List

222

5.9.9 Set Default Values

222

5.9.10 Parameter Custom Properties

222

5.9.11 Parameters Usage

223

5.10 The SQL Editor

223

5.11 Built-in Functions

226

5.11.1 @derivedtable

226

5.11.2 @execute

227

5.11.3 @prompt

227

5.11.4 @variable

230

5.12 Families, Comments, and Data Foundation Views

231

5.12.1 Families

231

5.12.2 Comments

234

5.12.3 Data Foundation View

235

5.13 Data Foundation Search Panel

238

5.13.1 Table and Column Names Selection

240

5.13.2 Table Types Selection

241

5.13.3 Column Types Selection

241

5.13.4 Families Selection

242

5.13.5 Contexts Selection

242

5.13.6 Possible Actions after Tables Selection in a Data Foundation View

243

5.14 Data Foundation Editor

243

5.14.1 Possible Actions in the Data Foundation Schema

245

5.14.2 Possible Actions in the Data Foundation Pane

248

5.14.3 Possible Actions in the Aliases and Contexts Pane

248

5.14.4 Possible Actions in the Parameters and List of Values Pane

249

5.14.5 Possible Actions in the Connection Pane

250

5.14.6 Possible Actions in the Data Foundation Toolbar

252

5.14.7 Possible Actions in the Data Foundation View Tab

253

5.14.8 Possible Actions in the Actions Menu

254

5.14.9 Data Foundation Actions

255

5.14.10 Data Foundation Properties

259

5.15 Checking Integrity

260

5.15.1 Run the Check Integrity

261

5.15.2 Understand the Result of the Check Integrity Process

262

5.15.3 Analyze and Fix Errors and Warnings

263

5.15.4 Customize the Validation Rules

265

5.15.5 Validation Rules Definition

266

5.16 Previewing Data

267

5.17 Data Foundation Refresh Structure

269

5.17.1 Data Foundation Impacts after Refresh Structure

272

5.17.2 Business Layer Impacts after Refresh Structure

273

5.18 Showing Dependencies

274

5.19 Data Foundation Parameters

277

5.20 Summary

282

6 Multisource Data Foundations

285

6.1 The Federation Technology

286

6.1.1 Merging Information from Multiple Data Sources

286

6.1.2 Federation Query Server

288

6.1.3 Supported Data Sources

289

6.2 Multisource Data Foundations

290

6.2.1 Choosing the Data Foundation Type

290

6.2.2 Creating a Multisource Data Foundation

291

6.2.3 Multisource Data Foundation Editor

293

6.3 Working with a Multisource Data Foundation

294

6.3.1 Creating Multisource Joins

294

6.3.2 Creating Multisource Derived Tables

296

6.4 Federated Tables

301

6.4.1 Federated Tables Usage

301

6.4.2 Federated Table Definitions

303

6.4.3 The Federation Layer Editor

306

6.4.4 Creating Federated Tables from Scratch

311

6.4.5 Creating a Federated Table from a Source Table Template

314

6.4.6 Defining Mapping Rules

315

6.4.7 Defining Pre-filters

320

6.4.8 Defining Post-filters

321

6.4.9 Defining Source Table Relationships

323

6.4.10 Understanding Core and Non-Core Tables

326

6.4.11 Using Federated Tables in the Data Foundation

332

6.5 Multisource Scenarios

334

6.5.1 The Union Scenario

334

6.5.2 The Mixed Sources Scenario

338

6.5.3 The Data Quality Scenario

342

6.6 Data Federation Administration Tool

344

6.6.1 Data Federation Administration Tool User Interface

344

6.6.2 Query Auditing

346

6.7 Optimization Techniques

349

6.7.1 Preparing the Federation Query Server Environment

349

6.7.2 Gathering Statistics

352

6.7.3 Deployment Factors Impacting the Performance

352

6.7.4 Optimization by Parameter Settings

353

6.7.5 The Semi-Join Functionality

355

6.7.6 The Merge-Join Functionality

357

6.7.7 The Source Discriminating Filter

357

6.8 Summary

358

7 The Business Layer

361

7.1 The Business Layer Objectives

361

7.2 Creating the Business Layer

363

7.2.1 OLAP Direct Access

363

7.2.2 Business Layer Entities and Concepts

364

7.2.3 Multidimensional Business Layer Creation

367

7.2.4 Relational Business Layer Creation

373

7.2.5 The Business Layer Editor

375

7.3 Objects

376

7.4 Folders

378

7.5 Dimensions

379

7.5.1 Dimension Definition: Select Clause

382

7.5.2 Dimension Definition: Where Clause

384

7.5.3 Validation

385

7.5.4 Extra Tables

385

7.5.5 Preview Data and List of Values

387

7.6 Attributes

387

7.6.1 Attribute Definition: Select Clause and Where Clause

389

7.6.2 Preview Data and List of Values

390

7.6.3 Attribute Validation and Other Properties

391

7.7 Measures and Calculated Measures

391

7.7.1 Measure Definition: SELECT Clause and WHERE Clause

395

7.7.2 Preview Data and List of Values

395

7.7.3 Projection Function

396

7.7.4 Measure Validation and Other Properties

399

7.8 Calculated Members

399

7.8.1 Solve Order

402

7.8.2 Format String

402

7.8.3 Scope Isolation

402

7.8.4 Language

403

7.9 Named Sets

403

7.10 Analysis Dimensions

406

7.11 Hierarchies

408

7.11.1 Level-Based Hierarchies

408

7.11.2 Parent-Child or Value-Based Hierarchies

408

7.11.3 Hierarchy Creation

409

7.12 Levels

410

7.13 List of Values

411

7.13.1 List of Values Based on Business Layer Objects

415

7.13.2 List of Values Based on the Query Panel

415

7.13.3 List of Values Based on a Custom Hierarchy

416

7.13.4 Static List of Values

417

7.13.5 List of Values Based on Custom SQL

418

7.13.6 List of Values Parameters and Options

419

7.13.7 Customize List of Values Columns

422

7.14 Parameters

423

7.14.1 Parameter Definition

424

7.14.2 Prompt Text

425

7.14.3 Data Type

425

7.14.4 Allow Multiple Values

425

7.14.5 Keep Last Values

425

7.14.6 Index Aware Prompt

426

7.14.7 Associated List of Values

426

7.14.8 Select Only From List

427

7.14.9 Set Default Values

427

7.14.10 Parameter Custom Properties

427

7.14.11 Dependent Parameters

428

7.14.12 Parameters Usage

429

7.14.13 Mandatory and Optional Parameters

430

7.14.14 Parameter Dialog Box

431

7.15 Filters and Mandatory Filters

431

7.15.1 Native Filter Validation and Extra Tables

433

7.15.2 Filter Properties

433

7.16 Navigation Paths

435

7.16.1 Default Navigation Paths

436

7.16.2 Custom Navigation Paths

438

7.17 Index Awareness

439

7.17.1 Primary Key

440

7.17.2 Foreign Key

440

7.18 Aggregate Awareness

441

7.18.1 The Aggregate Aware Process

442

7.18.2 Aggregate Navigation Incompatibility

442

7.18.3 Aggregate Navigation Set on Measures

444

7.18.4 Aggregate Navigation Set on Dimensions

445

7.19 SQL and MDX Editors

447

7.19.1 The SQL Editor

447

7.19.2 The MDX Editor

450

7.20 Object Common Properties

453

7.20.1 Data Types

453

7.20.2 List of Values

454

7.20.3 Access Level

456

7.20.4 Usage

457

7.20.5 Source Information

458

7.20.6 State

459

7.20.7 Custom Properties

460

7.21 Object Formatting

461

7.21.1 Display Format

461

7.21.2 Custom Format

462

7.21.3 Database Format

464

7.22 Business Layer Queries

464

7.23 Built-in Functions

466

7.23.1 @aggregate_aware

467

7.23.2 @execute

467

7.23.3 @prompt

468

7.23.4 @select

471

7.23.5 @variable

472

7.23.6 @where

473

7.24 Business Layer Views

473

7.25 Business Layer Functionalities

476

7.25.1 Preview Data

476

7.25.2 Search, Filter, Show, and Hide

479

7.25.3 Find and Replace

482

7.25.4 Business Layer Lifecycle Functionalities

483

7.26 Check Integrity

486

7.26.1 Run the Check Integrity

487

7.26.2 Understand the Result of the Check Integrity Process

488

7.26.3 Analyze and Fix Errors and Warnings

489

7.26.4 Customize the Validation Rules

491

7.26.5 Validation Rules Definition

492

7.27 Show Dependencies

492

7.28 Business Layers Built on Multisource Data Foundations

495

7.29 Multidimensional Business Layer Refresh Structure

497

7.30 Business Layer Parameters and Query Governors

500

7.30.1 Business Layer Parameters

500

7.30.2 Query Governors

503

7.31 Recommendations for Building a Business Layer

507

7.32 Summary

508

8 Universe Query Panel

511

8.1 The Query Panel Interface

512

8.1.1 Launching the Query Panel

512

8.1.2 Query Panel Parts

512

8.1.3 Query Panel Toolbars

514

8.2 Creating Queries

516

8.2.1 Selecting the Result Objects

516

8.2.2 Applying Filters to the Query

524

8.2.3 Sorting Result Objects

528

8.3 Setting and Answering Prompts

530

8.3.1 Choosing Contexts

530

8.3.2 Customizing a Prompt in the Query Filters

530

8.3.3 Answering a Prompt

533

8.4 Setting the Query Properties

536

8.5 Advanced Functionalities

538

8.5.1 Adding a Subquery Filter

539

8.5.2 Adding a Ranking Filter

541

8.5.3 Using Combined Queries

543

8.5.4 Setting a Custom Query Script

545

8.6 Summary

546

9 Publishing and Retrieving Universes

547

9.1 Publishing a Universe Locally

548

9.1.1 Connection

549

9.1.2 Security

549

9.1.3 Web Intelligence Rich Client

550

9.2 Publishing a Universe in a CMS Repository

551

9.2.1 Connection Shortcut

553

9.2.2 CMS Repository

554

9.2.3 Managing Published Universes

555

9.3 Retrieving a Local Universe

556

9.3.1 Retrieved Resources

557

9.3.2 Folder

558

9.4 Retrieving a Universe from the CMS Repository

558

9.4.1 Connection Shortcuts

559

9.4.2 Security

561

9.5 Information Design Tool Workflows

562

9.5.1 Publishing a Universe Locally

562

9.5.2 Publishing a Universe in a CMS Repository

564

9.5.3 Managing Universes Published in a CMS Repository

565

9.5.4 Show Repository Dependencies

565

9.5.5 Retrieving a Local Universe

566

9.5.6 Retrieving a Universe from a CMS Repository

567

9.6 Summary

569

10 Securing Universes

571

10.1 Rights

572

10.1.1 Information Design Tool Rights

573

10.1.2 Universe Rights

574

10.1.3 Relational Connection Rights

575

10.1.4 OLAP Connection Rights

576

10.1.5 Data Federator Data Source Rights

577

10.2 Object Access Level

577

10.3 Security Profiles

579

10.3.1 Assigned Users and Groups

580

10.3.2 Consumption

581

10.4 Data Security Profiles

581

10.4.1 Connections

583

10.4.2 Controls

584

10.4.3 SQL

585

10.4.4 Rows

585

10.4.5 Tables

586

10.5 Business Security Profiles

587

10.5.1 Connection

589

10.5.2 Create Query

590

10.5.3 Display Data

593

10.5.4 Filters (Relational Universe)

595

10.5.5 Filters (Multidimensional Universe)

596

10.6 Security Profiles Aggregation

598

10.6.1 Priority Aggregation

599

10.6.2 AND, ANDOR, and OR Aggregation

600

10.6.3 Supported Aggregations

601

10.7 Managing Security Profiles in Information Design Tool

604

10.8 User Attributes

606

10.8.1 Defining User Attributes

607

10.8.2 Using User Attributes

607

10.8.3 User Attributes Substitution

608

10.9 Running a Secured Query

608

10.10 Summary

609

11 Working in Multilingual Environments

611

11.1 Languages and Locales

612

11.1.1 Language

612

11.1.2 Locale

612

11.1.3 Dominant Locale

613

11.1.4 Product Language

613

11.1.5 Preferred Viewing Locale

614

11.2 Translation Management Tool

615

11.2.1 Locales in the Translation Management Tool

616

11.2.2 Translation Management Tool

616

11.2.3 Resources in the CMS Repository

617

11.2.4 Translation Status in the Translation Management Tool

618

11.2.5 Running the Translation Management Tool

619

11.2.6 Translating Properties in the Translation Management Tool

621

11.3 Translating Universe Metadata

622

11.3.1 Universe Translation Workflow

623

11.3.2 Publishing Universe

624

11.3.3 Data Foundation Translatable Properties

625

11.3.4 Business Layer Translatable Properties

626

11.4 Multilingual Data in Relational Universes

627

11.4.1 Multilingual Patterns in Relational Database

627

11.4.2 Retrieving a Locale in a Universe with @Variable

629

11.4.3 Using @Variable in Different Patterns

630

11.5 Multilingual and Multidimensional Universes

632

11.5.1 Metadata

633

11.5.2 Data

634

11.6 Summary

634

12 Connecting to SAP ERP, SAP NetWeaver BW, and SAP HANA

635

12.1 Access to SAP NetWeaver BW

636

12.1.1 SAP NetWeaver BW Interfaces

636

12.1.2 Connection Parameters to SAP NetWeaver BW

638

12.1.3 Creating an SAP NetWeaver BW OLAP Connection

640

12.1.4 Creating a Relational Connection to SAP NetWeaver BW

643

12.1.5 Creating a Multisource Data Foundation Automatically

648

12.1.6 Creating a Business Layer Automatically

649

12.1.7 Creating a Data Foundation and a Business Layer Manually

651

12.1.8 SAP NetWeaver BW Relational Universe Performance

652

12.2 Access to SAP ERP

652

12.2.1 SAP ERP Connection Parameters

653

12.2.2 Creating a Relational Connection to SAP ERP

654

12.2.3 Data Foundation on SAP ERP

656

12.2.4 Single-Source Data Foundations on SAP ERP

659

12.2.5 Multisource-Enabled Data Foundations on SAP ERP

661

12.2.6 Business Layers on an SAP ERP-Based Data Foundation

663

12.3 Access to SAP HANA

664

12.3.1 Creating a Universe on SAP HANA Views

665

12.3.2 Creating a Data Foundation and a Business Layer Automatically

671

12.3.3 SAP HANA Views: Recommendations and Constraints

676

12.3.4 Creating a Multidimensional Access on SAP HANA

679

12.4 Migrating a Universe to SAP HANA

681

12.5 Summary

685

13 Comparing the Universe Design Tool and Information Design Tool

687

13.1 General

687

13.2 Connections

689

13.3 Data Foundation

692

13.4 Business Layer

693

13.5 List of Values and Parameters

695

13.6 Security

697

13.6.1 Access Restrictions and Security Profiles (Relational Universe)

698

13.6.2 Access Restrictions and Security Profiles (OLAP Universe)

699

13.6.3 Aggregation

700

13.6.4 Security Editor

701

13.6.5 Central Management Console Rights

702

13.6.6 Connection Rights

703

13.7 Miscellaneous

704

13.8 Universe Conversion

705

13.8.1 Universe Conversion Scope

706

13.8.2 Local versus Secured Universe Conversion

707

13.8.3 Data Foundation and Business Layer

708

13.8.4 Linked Universe

709

13.8.5 Universe Rights Conversion

710

13.8.6 Access Restriction Conversion

710

13.8.7 Access Restriction Aggregation Option Conversion

711

13.8.8 Object Access Level

712

13.9 Converting Universes in Information Design Tool

712

13.9.1 Converting a Local Universe

712

13.9.2 Converting a Secured Universe

714

13.10 Summary

715