Creating Universes with SAP BusinessObjects

By Taha M. Mahmoud

Creating a Universe using SAP BusinessObjects is your ultimate guide to mastering the development of BusinessObjects Universes using the SAP Information Design Tool. You will find many hands-on exercises as well as hints and best practices within this guide.

The book starts with an introduction to Business Intelligence and where BusinessObjects Universes fit into the big picture. Then, we will go through the Universe creation process. You will then learn how to create a Data Foundation (physical layer) and how to establish a new relational database and OLAP connection. Following this, you will learn how to create a Business layer. You will also learn how to handle security using user profiles and how to use functions such as @Aggregate_Aware(). This is a must-have book for any SAP BO Universe developer and will act as a reference for beginners and experts alike.

• Language: English
• Publisher: Packt Publishing
• Release date: Sep 25, 2014
• ISBN: 9781782170914

Book preview

Table of Contents

Creating Universes with SAP BusinessObjects

Credits

About the Author

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Instant updates on new Packt books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Introduction to BI and the Semantic Layer

What is Business Intelligence?

Business Intelligence concepts

The knowledge pyramid

Hindsight, insight, and foresight

BI and DWH

BI reporting tools architecture

The one-tier architecture model

The two-tier architecture model

The three-tier architecture model

What is a semantic layer?

Introduction to Universes and the Information Design Tool

History of SAP Business Objects

Summary

2. Aligning BI Solutions with Business Demands

Northwind

The BI Maturity Model

Configuring and setting up Northwind's database

Getting the Northwind sample database

Creating the ODBC Connection

The Northwind data model

Dimensional modeling terminologies

Order information

Inventory information

Summary

3. Creating Our First Universe

Information Design Tool

The main features of the IDT

The Information Design Tool interface

Local Projects

Repository Resources

Security Editor

Project Synchronization

Check Integrity Problems

Creating your first Universe step by step

Creating a new project

Creating a new data connection

Creating a new Data Foundation

Creating a new Business layer

Saving and publishing the NorthWind Universe

Finding help while creating a Universe

The Universe wizard

Cheat Sheets

Summary

4. Creating the Data Foundation Layer

Working with data connections

The relational connection

The OLAP connection

Building the Data Foundation layer

Types of Data Foundation

Single Source

Multisource-Enabled

Physical tables and views

Alias tables

Derived tables

Using the @Prompt functionality in a derived table

Nested derived tables

List of derived tables

Joining tables

Cardinality

Key types

A primary key

A foreign key

Join types

Equal joins

Theta joins

Outer joins

Self joins

Data Foundation menus and functions

Detecting joins

Detecting cardinalities

Detecting the row count

Refreshing the structure

Managing families

Save the view as an image

The search panel

Auto-arranging tables

Controlling the Data Foundation display

Insert View

Data Foundation visualization

Data Foundation object manipulation

Insertion

Table manipulation

Add to View

Edit

Merge

Delimit

Set Case To

Replace by

Detect

Change Qualifier/Owner

The table's values and display

Count Rows

Show Table Values

Select Related Tables

Highlight Related Tables

Display

Centre on Selection

Show Local Dependencies

Highlight Aliases

The table's basic functions

Summary

5. Creating the Business Layer

Gathering requirements

The Business layer's main window

The left panel

The Properties panel

The Data Foundation panel

Business Layer

Managing Business layer views

Showing/hiding the search panel

Expand and collapse all

Display Options

Inserting objects

Business objects

Folders

Dimensions

Main properties

SQL Definition

The Select property

The Where property

The Extra Tables property

Keys

Controlling advanced properties

Access Level

Objects can be used in

Database Format

List of Values

Display

Source Information

Custom Properties

Attributes

Measures

Difference between the database and projection functions

Filters

How to create a filter

Filter properties

Summary

6. Testing Your Universe

The Universe development life cycle

Initiation

Planning and design

Implementation and development

Quality assurance and testing

SIT

UAT

Closing

Using the Check Integrity wizard

Queries in the Business layer

Summary

7. The Data Foundation Layer – Advanced Topics

Design concepts

Information systems

Online transactional processing

Online analytical processing

The DWH schema types

The snowflake schema

The star schema

The relational model

Advanced design techniques of Data Foundation

Alias tables

Context

The shortcut join

Advanced Data Foundation properties

Description

SQL Options

Summary

Parameters

SQL design traps

The fan trap

Solving a fan trap

The chasm trap

Solving the chasm trap using context

Solving the chasm trap using an alias table

Loop

Fixing the loop using the shortcut join

Fixing the loop using context

Fixing the loop using an alias table

Summary

8. The Business Layer – Advanced Topics

List of values

List of values based on a dimension object

List of values based on the query panel

List of values based on a custom hierarchy

Static list of values

List of values based on custom SQL

Navigation paths

The default navigation path

The custom navigation path

Index awareness

Primary keys

Foreign keys

Aggregate awareness

Inserting aggregated tables

Defining the aggregated objects

Detecting the aggregation navigation

Common business objects' specific functions

@Aggregate_Aware()

@DerivedTable()

@Prompt()

@Select()

@Variable()

@Where()

Multilingual Universes

Advanced Business layer properties

Description

Summary

Change Data Foundation

Parameters

Summary

9. Data Security and Profiles

Understanding the security model

Working with the Security Editor

Universes / Profiles

Users / Groups

Understanding Data Security Profiles

Connections

Controls

SQL

Rows

Tables

Understanding Business Security Profiles

Create Query

Display Data restriction

Filters

The security matrix

Summary

10. A Multiuser Development Environment

Working with projects

Local projects

Creating a local project

Opening a local project

Deleting a local project

Shared projects

Creating a shared project

Renaming and deleting a shared project

Synchronizing projects

Promotion and the life cycle management console

Promoting a Universe from one environment to another

Promoting NorthWind using the LCMBIAR file

Promoting NorthWind using the Live to Live method

The Upgrade Management Tool

Converting UNV to the new Universe UNX extension

Summary

Index

Creating Universes with SAP BusinessObjects

---

Creating Universes with SAP BusinessObjects

Copyright © 2014 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: September 2014

Production reference: 1180914

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 978-1-78217-090-7

www.packtpub.com

Cover image by Tony Shi (<[e-mail]>)

Credits

Author

Taha M. Mahmoud

Reviewers

Dmitry Anoshin

David Lai

Didier Valat

Commissioning Editor

Edward Gordon

Acquisition Editor

Gregory Wild

Content Development Editor

Madhuja Chaudhari

Technical Editors

Mrunmayee Patil

Siddhi Rane

Copy Editors

Karuna Narayanan

Laxmi Subramanian

Project Coordinator

Akash Poojary

Proofreaders

Simran Bhogal

Stephen Copestake

Maria Gould

Lawrence A. Herman

Paul Hindle

Indexers

Hemangini Bari

Tejal Soni

Production Coordinators

Saiprasad Kadam

Nilesh R. Mohite

Alwin Roy

Cover Work

Nilesh R. Mohite

Alwin Roy

About the Author

Taha M. Mahmoud (PMP, TOGAF, ITIL, and CSM) is a senior BI consultant, BI project manager, and a solution architect. He has more than 7 years of experience working on, consulting for, and deploying successful business objects' projects in the banking and telecom industries.

I would like to thank my mother and father for raising me and helping me to be different. I love you and will always do. I would also like to thank my wife, Rasha, for supporting me during the period of writing this book—there is always a woman behind every great man. Special thanks to my son, Ali, daughter, Ruqayyah, and my family and friends.

About the Reviewers

Dmitry Anoshin is a datacentric technologist and recognized expert in building and implementing Business Intelligence solutions. He has a successful track record of implementing BI/DWH projects in numerous industries, such as retail, finance, and e-commerce industries.

He has an in-depth knowledge of BI technologies and is experienced in various projects as a BI consultant and a hands-on ETL/DWH developer.

He has more than 7 years of experience in BI/DWH. In addition, he is certified in Teradata database, SAP BusinessObjects, SAS BI, and Pentaho DI.

He has also reviewed the book, SAP BusinessObjects Reporting Cookbook, Packt Publishing.

I would like to thank my wife, Svetlana, and my children, Vasily and Anna, for their patience while I was reviewing this book. I love them very much. Thank you, my beloved family.

Didier Valat is a certified technical leader in Business Intelligence. He graduated from Paul Sabatier, a French University, with a Master's degree in Statistics and Business Intelligence in 2009.

After completing his Master's degree, he joined an IT service company as a BI consultant in Toulouse. He worked for major clients such as EADS Airbus and Toulouse town council, and developed SAP expertise both on BusinessObjects and Business Warehouse. He worked as the Lead of Projects in the finance and supply chain domains, where his hard work and strong team spirit were recognized and appreciated.

In 2012, he joined the Teradata company in Paris to become an expert in data warehouse solution implementation. He was quickly identified as a technical and functional team leader when he worked for the main French lottery company, and he was involved in each part of the project: architecture, modeling, and development.

He is now taking part in data science challenges, mainly on R and Big Data solutions.

I would like to thank all the people behind this project who trusted me and made this book possible. I would also like to thank my family for all their love and support.

www.PacktPub.com

Support files, eBooks, discount offers, and more

You might want to visit www.PacktPub.com for support files and downloads related to your book.

Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at for more details.

At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.

http://PacktLib.PacktPub.com

Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can access, read, and search across Packt's entire library of books.

Why subscribe?

Fully searchable across every book published by Packt

Copy and paste, print, and bookmark content

On demand and accessible via web browser

Free access for Packt account holders

If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view nine entirely free books. Simply use your login credentials for immediate access.

Instant updates on new Packt books

Get notified! Find out when new books are published by following @PacktEnterprise on Twitter, or the Packt Enterprise Facebook page.

Preface

This year (2014) was an amazing year for German football fans as their national team won the World Cup 2014 title. A few days later, I received a subscription e-mail from SAP with the title How SAP helped Germany win the World Cup. It was very interesting for me to know how. As SAP is one of the leading IT companies in the world, it was not clear to me how IT can help a sports team or even a football team to win a title or a medal. You can have a look at the e-mail body in the following screenshot:

So it is all about Business Intelligence (BI). Now, we can see BI participating in almost every single aspect of our life, even in sports. SAP used the Big Data concept to handle vast amount of data, SAP HANA to assure optimum response time, and SAP BO BI 4.x to make data available to the right users in the right format (reports, dashboards, and analysis), as we can see in the previous example. The results were amazing, Germany won the World Cup title with the aid of BI. This era is not the hard work one, but rather it is the intelligent work one.

SAP BO BI 4.x is a complete BI solution with many tools bundled together to satisfy different needs of BI. One important tool is Information Design Tool, which is used to create and build the BO semantic layer (Universe). Universe is an intermediate layer between database and BI tools that will transform database entities and joins into business objects stored in the Business layer. These business objects can be consumed later on by different BO reporting solutions.

In this book, we will learn how to create a Universe from scratch. We will cover all related topics in detail along with step-by-step hands-on and real examples. We will discuss design best practices, the Universe development life cycle, Universe testing, security, and the multiuser development environment.

Congratulations on taking a step toward learning how to create a Universe using SAP BO BI 4.x! Are you ready? Let's go…

What this book covers

Chapter 1, Introduction to BI and the Semantic Layer, introduces Business Intelligence (BI), BI concepts, and Information Design Tool (IDT).

Chapter 2, Aligning BI Solutions with Business Demands, explains how to download the Northwind MS Access database and configure it in order to use it in the remaining chapters of this book. We will also discuss the business case for Northwind and will explain its data model.

Chapter 3, Creating Our First Universe, facilitates a quick walkthrough that will guide you to build your first Universe from start to finish. We will talk about the main building blocks for any Universe and we will start creating a project, connection, as well as the Data Foundation and Business layers; then we will publish this Universe. We will continue building and enriching this Universe in the remaining chapters of this book.

Chapter 4, Creating the Data Foundation Layer, introduces us to our core chapter that will cover in detail all you need to know about data connection and Data Foundation resources.

Chapter 5, Creating the Business Layer, covers in detail all that you need to know about the Business layer. Here, you can also find many best practices on how to deal with business requirements.

Chapter 6, Testing Your Universe, discusses the Universe development life cycle, how to use the Check Integrity wizard to complete the System Integration Test (SIT) for the Universe, and how to use the Queries tab in the Business layer resource to complete the User Acceptance Test (UAT).

Chapter 7, The Data Foundation Layer – Advanced Topics, covers advanced design topics that we need to take care of during the Data Foundation design phase. We will start with design concepts, then we will cover Data Foundation advanced techniques that we will use to enhance our Data Foundation design. Finally, we will talk about SQL design traps and how to fix and avoid them.

Chapter 8, The Business Layer – Advanced Topics, discusses Business layer topics such as list of values (LOV), navigation paths (hierarchies), index awareness, aggregate awareness, and Business layer's BO advanced functions (@functions).

Chapter 9, Data Security and Profiles, covers the important topic of security in detail. We will start with differentiating between the two main types of security profiles (Data Security Profile and Business Security Profile). We will learn how to use the Security Editor to create security rules and how to assign them. Finally, we will talk about security implementation best practices and how to create the security matrix.

Chapter 10, A Multiuser Development Environment, explains how designers can work more efficiently in teams and how to use Information Design Tool in the multiuser development environment using the synchronization wizard to synchronize local and shared projects. It also explains how to migrate and upgrade a Universe among BO environments.

What you need for this book

You need to have the SAP BO BI 4.x client installed on your machine. You also will need to have access to the BO BI 4.x server to be able to complete some parts that require establishing a session with the BO server. It is also important to complete the setup and configuration steps mentioned in Chapter 2, Aligning BI Solutions with Business Demands, in order to be able to complete the hands-on and real examples described in this book.

Who this book is for

This book is a step-by-step walkthrough supported with real-life examples and illustrator diagrams that will help you to build robust Universes. This is the perfect book for you if you are a beginner and want to learn how to use SAP BO Information Design Tool to create a Universe from scratch. You will also find all that you need to upgrade from Universe Design Tool (BO 6.x, BO XI2.x, and BO XI3.x) to Information Design Tool (the new SAP BO semantic layer tool introduced since BO BI 4.0). You will find information about how to map your current knowledge and experience in the UDT in a very fast and efficient way and to start using the new IDT. This book is also for professional Universe designers who work with the IDT to build SAP BO Universes. As a professional IDT designer, you will still find many tips, tricks, and best practices. Finally, this book is a complete guide that you can always refer to irrespective of your current level of experience.

Conventions

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: To convert a .unv Universe, let's perform the following steps:

A block of code is set as follows:

SELECT A.CUSTOMER_ID, COUNT (C.CREDIT_CARD_NO), COUNT (B.ACCOUNT_NUMBER)

FROM CUSTOMERS A, CURRENT_ACCOUNTS B, CREDIT_CARDS