Table of Contents

Open all
Close all
Preface
23
Acknowledgments
27
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
The Authors
717
Index
719