Development of a reporting system through SAP BO and SAP NetWeaver BW platforms

(1)

U

NIVERSITÀ

DI

P

ISA

F

ACOLTÀDI

I

NGEGNERIA

C

ORSO DI

L

AUREA

M

AGISTRALE IN

I

NGEGNERIA

I

NFORMATICA PER LA

G

ESTIONE D’

A

ZIENDA

TESI DI LAUREA

Development of a Reporting System through

SAP BO and SAP NetWeaver BW platforms

RELATORI

Prof. Roberto Chiavaccini Ing. Mario G.C.A. Cimino

CANDIDATO Matteo Righini

(2)

(3)

3 To Arianna, my family, and my friends.

(4)

4

ABSTRACT

The main scope of this thesis is to successfully face a Business Intelligence data integration problem of the IT Function of an Italian company.

After a brief introduction who faces the Decisional Thinking problem inside a business company, I mentioned the extraction and understanding problems of information from the data: these processes are vital for every company heading to success.

The current company’s landscape before the project’s kick-off is the following: this company has an ERP, an SAP® R3® System, handling the gather of the data from all over the world and which towards it to a Business Warehouse to maintain it in an efficient way. This allows IT Specialists to mine information vital for the company success. The central chapters of this Thesis expose the process of gathering the data from the ECC system, to the storage of that data in the company’s Business Warehouse and the extraction and aggregation for the final presentation, step achievable with Business Intelligence reporting tools.

The purpose of the project carried out in this thesis is to develop several dynamic report families through the integration between the SAP NetWeaver™ BW (Business Warehouse) and the main SAP reporting suite called SAP Business Objects™, and to explain the integration process with the related technologies.

The project was completed with the creation of publications for the Summary report family. A publication is a way to deliver a report to a receiver by e-mail, file system or other means. A publication can be scheduled, any time with different options, with the aim to reduce the amount of work on IT specialists and to make automatic the procedure to create the report with updated data and send them to the company controllers. These publications are one of the true strength of a reporting tool suite such as SAP Business Objects.

(5)

5

INDEX

ABSTRACT ...4

INTRODUCTION ...9

I.THESIS PRESENTATION ...9

II.REVIEW OF THE LITERATURE ...9

III.THESIS CONTENT ...10

1 A BUSINESS INTELLIGENCE PROBLEM ...12

1.1 THE RIGHT BUSINESS DECISION ...12

1.1.1 Decisional challenges...12

1.1.2 Fully Informed Decision-Making ...13

1.2 A MATTER OF “BIG DATA” ...17

1.2.1 The Digital World ...17

1.2.2 Rethinking BI with “Big Data” ...18

1.2.3 Defining a new Data Wisdom ...19

1.2.4 Jobshifts and Mindshifts ...20

1.3 ABUSINESS INTELLIGENCE PROJECT ...21

1.3.1 Introduction to the Business Problem ...21

1.3.2 Project Development Requests ...23

1.3.3 Proposed Solution ...25

2 SAP NETWEAVERTM_{BUSINESS WAREHOUSE ...27}

2.1 THE NEED OF A DATA WAREHOUSE ...27

2.1.1 The masses of information in the enterprise ...27

2.1.2 The Data Warehouse component of Business Intelligence ...29

2.2 SAPBUSINESS WAREHOUSE AND ITS COMPONENTS ...30

2.2.1 The SAP BW landscape ...30

2.2.2 SAP NetWeaver BW Architecture ...31

2.2.3 SAP NetWeaver BW InfoObjects ...32

2.2.4 SAP NetWeaver BW InfoProviders ...33

2.2.5 Extraction of Data from Source Systems ...35

2.3 THE SAPNETWEAVER BWDATA FLOW ...36

2.3.1 How the Data Flow Works ...36

2.3.2 An SAP Source System Master Data Load Scenario ...38

2.4 THE SAPBW REPORTING SIDE ...39

2.4.1 SAP Business Explorer Suite ...39

(6)

6

2.4.3 Transports Connection ...41

3 SAP BUSINESS EXPLORERTM_{QUERY DESIGNER ...42}

3.1 BEX QUERY DESIGNER...42

3.1.1 Introduction to the tool ...42

3.1.2 Calling the BEx Query Designer ...43

3.2 FUNCTIONS OF THE BEX QUERY DESIGNER ...43

3.2.1 Toolbar functions ...43

3.2.2 Filter View Functions ...45

3.2.3 Rows/Columns View Functions ...46

3.3 FILTERING DATA IN THE QUERY DEFINITION ...47

3.3.1 The need of Filtered Data ...47

3.3.2 Define selection conditions for Filter Values ...48

3.4 DEFINING RESTRICTED KEY FIGURES...49

3.4.1 The need of Restricted Fey Figures Values ...49

3.4.2 Defining Restricted Key Figures ...49

3.4.3 Determining Constant Selection...50

3.5 DEFINING OBJECTS ...51

3.5.1 Defining Calculated Key Figures ...51

3.5.2 Defining Calculated Key Figures at the InfoProvider ...51

3.6 STRUCTURES AND HIERARCHIES ...52

3.6.1 Structures in Queries ...52

3.6.2 Key Figure Structure ...54

3.6.3 Working with cells ...54

3.6.4 Displaying Characteristics hierarchically ...55

4 SAP BUSINESS OBJECTSTM_{WEB INTELLIGENCE ...57}

4.1 KEY CONCEPTS...57

4.1.1 Introduction and BICS Connectivity ...57

4.1.2 Interface ...58

4.2 BASIC NOTIONS ...63

4.2.1 Create a Query ...63

4.2.2 View or change the properties for a query ...65

4.2.3 Drag and drop objects to create a report ...66

4.2.4 Save a document as other formats ...67

4.3 CONNECTING TO DATA SOURCES ...67

4.3.1 Add another query to a document ...67

4.3.2 Change the data source on which a query is based ...67

4.3.3 Combine multiple queries ...69

(7)

7

4.4 TABLES, CROSSTABS AND CHARTS ...72

4.4.1 Create a table ...72

4.4.2 Change the layout of the table ...73

4.4.3 Delete columns and rows from a table...73

4.4.4 Create a chart...74

4.5 SETTING UP INTERACTIVE ELEMENTS ...74

4.5.1 Add a hyperlink to a document ...74

4.5.2 Drill down into a table ...76

4.6 SELECTING AND SORTING DATA ...76

4.6.1 Select a subset of data based on a custom filter ...76

4.6.2 Calculate totals and subtotals for data ...77

4.6.3 Sort the data in a document ...78

4.7 FORMULAS AND CONDITIONAL FORMATTING ...79

4.7.1 Create a variable based on a formula ...79

4.7.2 Add and format text in a document ...80

4.7.3 Format tables and graphs ...82

4.7.4 Conditionally format data...83

5 BRANDABC PROJECT DEVELOPMENT ...85

5.1 R01-MONTHLY WORLDWIDE BUDGET (EURBDG RATE) ...85

5.1.1 Report features ...85

5.1.2 Business Explorer Query ...86

5.1.3 Web Intelligence Document Development ... 104

5.1.4 WebI Conditional Formatting ... 111

5.1.5 Output ... 118

5.2 R02-MONTHLY WORLDWIDE BUDGET (EURBDG RATE)DEPARTMENT ... 119

5.2.1 Report features ... 119

5.2.2 Business Explorer Query ... 120

5.2.5 Output ... 128

5.3 SUMMARY CURRENT YEAR ... 129

5.3.1 Report features ... 129

5.3.5 Report Publication ... 172

5.3.6 Output ... 175

5.4 SUMMARY NEXT YEAR ... 177

(8)

8

5.4.5 Report Publication ... 189 5.4.6 Output ... 190 6 CONCLUSIONS ... 192 REFERENCES ... 194 APPENDIX ... 195 FIGURES INDEX ... 199 ACKNOWLEDGEMENTS ... 207

(9)

9

INTRODUCTION

I. Thesis Presentation

This thesis has the focus to solve a Business Intelligence problem. In these years, companies worldwide realized the importance of understanding their customer needs using all the information they can collect. To be competitive a company must access and elaborate an extensive amount of data, the so-called “big data”, to execute their Business Process with proper knowledge of the facts.

How companies can acquire knowledge? Today information are coming from multiple sources and the company must be ready to emerge victorious from this challenge. Therefore they must be ready to present to management controllers the right data with the desired format and layout to be sure to take the right business decisions, in the shortest time possible.

The Chapter 5 of this thesis are the answers of several Business Intelligence requirements of an Italian company, who understood those needs and desired to establish an integration scenario between the corporate data, already organized in their Business Warehouse, and the Reporting tools necessary to present the information to their controllers.

The project had the main objective to develop in a new format several Report families, realized periodically in the past from IT specialists of the company until now, and integrating them with the company Warehouse to build an effective and efficient Reporting System, using the already existing SAP NetWeaver BW platform to preserve the data and the newly acquired and introduced SAP Business Objects, with the Web Intelligence instrument to present information in the needed form.

II. Review of the literature

I principally used a “digital” version of various forms of literature to realize this work. Indeed, a consolidated and essential source of knowledge about SAP World came obviously from

(10)

10 internet, in particular from the SAP Development Network (http://sdn.sap.com) and SAP Help Portal (http://help.sap.com), where I found a solid know-how and experts of the Business Intelligence, reporting and SAP world. For this reason it’s important to specify that most of the content of the Chapters 2 – 3 – 4 come from the sources of that network, and all that material is owned by SAP AG.

Another important source of knowledge came from OpenSAP courses (https://open.sap.com). OpenSAP is an online platform for e-learning that delivers periodically to it subscribers courses of the SAP world; it’s the 2014 winner of the Bronze Medal for “The learning Award”, a prize assigned yearly to the best learning platforms and organizations (http://www.learningandperformanceinstitute.com/lpilogo.htm).

I took part of a 4 weeks online course, the “BI 4 Platform Innovation and Implementation” that helped med to develop the skills and the ability to face the instruments and the technology related to this project with a successful approach. I took part to every lesson, passing the final exam and gaining a Record of Achievement, for having successfully completed the OpenSAP course.

Other sources of inspiration came from the articles “Understanding BEx Query Designer” (by Shyam Uthaman, 2011): it helped me understanding SAP Business Explorer Query Designer and its many functionalities to proceed with enthusiasm on the project. Another element that made me think is the book “Reason in the Balance: An Inquiry Approach to Critical Thinking” (McGraw-Hill Ryerson 2010), by Mark Battersby and Sharon Bailin, two philosophy professors. With the book, is noticeable their contribution to the concept of Decisional Thinking that I included in the first part of this work and that in my opinion is an integral part of the entire decision-making process, which begins with the information hidden in the data and ends with the implementation of the taken decision.

III. Thesis content

The Chapter 1 of this thesis talks about the Decisional Thinking process, and faces the problem of taking the right business decision from the information obtained from business data.

(11)

11 After the first theoretical part, I tried to expose the Business Intelligence data problem like a process from the source - the Business Warehouse on the ECC R3 system - to its recipient, the Reporting tool and the end-users. This works is composed mainly by these principal aspects.

In Chapter 2 I introduced and explained the functioning of SAP NetWeaver Business Warehouse, the main platform I interfaced with using the reporting tool SAP Business Objects Web Intelligence. BW is a very complex landscape and explaining how it works is essential to understand the data gathering, processing, and organization.

The subsequent part is concerning the selection of the correct data. These essential aspects are realized and explained by the SAP Business Explorer Query Designer, Chapter 3, tool that I used to understand and in some cases to realize the queries to interrogate the Business Warehouse, with the objective to collect and present the right information to various reports.

The SAP Business Objects Web Intelligence in the Chapter 4 contains the main information and procedure to create and organize objects in a document, in order to realize a Report with certain desired data. This chapter offers various procedures to create documents with a reasoned methodology and to solve problems in an effective and efficient way.

After all the essential theoretical foundations, Chapter 5 is about the development of the Reporting System for the company. I organized a sub-chapter for each report I had to realize. Each report sub-chapter includes an overview on the report format, the objects and composition in the Web Intelligence document format and the query composition analysis.

It’s important to understand that, due to the preciousness and the sensitiveness of the data handled during the development of this thesis, every reference to the company identity has been eliminated and all the sensitive information showed in this work are obscured to avoid a possible misuse of them by unauthorized persons.

(12)

12

1 A Business Intelligence Problem

1.1 The right Business Decision

1.1.1 Decisional challenges

A common primary business challenge is to improve the company situation making good decisions and relevant judgments. Businesses have started focusing on Business Intelligence and Data Driven Decision Making because they recognize that human intuition is not enough - from some decades to nowadays - and that decisions need to be based on data and information.

There’s the need to provide the correct view of the meaning “Business Intelligence” from the business world’s optic. For Gartner Group 1_{“Business intelligence (BI) is an umbrella term that} includes the applications, infrastructure and tools, and best practices that enable access to and analysis of information to improve and optimize decisions and performance”.

There is an aspect that is redundant in every Business Intelligence problem we are going to engage: the amount of data at our disposal. The abundance of data, the so called “big data”, and powerful analytical software has now made the use of such data for decision making problems an easier possibility. Powerful as these tools are, the decisional process still require that the user think carefully and critically about their use and meaning.

This is not a challenge which is bound only in the field of business. It’s a version of the challenge that we all face in many circumstances of our daily lives, where we’re constantly confronted with decisions, for example about health, social issues, politics, and where print and digital media inundate us with tons of information as well as with conflicting views and claims.

The real challenge in both these contexts is to think critically about the reliability of the data and its relevance and significance for producing better decisions. Critical thinking means coming to a reasoned decision or judgment based on a critical and reflective evaluation of

(13)

13 relevant reasons and data. And critical thinking is something leaders in business are constantly seeking in their employees.

In fact by a recent Forbes article 2 the three job skills that are considered to be the most important are:

1) Critical Thinking, the use of logic and reasoning to identify the strengths and weaknesses of alternative solutions to problems

2) Complex Problem solving, identifying complex problems and reviewing related information to develop and evaluate options and implement solutions

3) Judgment and decision making, the process to consider the relative costs and benefits of potential actions to choose the most appropriate ones

These are all really aspects strictly related to Critical Thinking. The ability to think critically is central to making well informed, sound decisions in business and in life.

1.1.2 Fully Informed Decision-Making

Even if we don’t realize that, Critical Thinking is something we all do every day. We ask questions, we compare information, we evaluate alternatives, we make decisions and solve problems (or at least we try to do that). And we do this with varying degrees of skill and consistency. But like any skill, critical thinking can be enhanced and we can improve ourselves to make better decisions. There are common errors in reasoning, known as fallacies, which have been identified in critical thinking research that we can learn to recognize and try to avoid. And there are several strategies and procedures that we can learn to use which can help us to adopt a more systematic, more thorough, more reflective and consistent approach in our thinking.

The ideal approach we should take to critical thinking is an” inquiry approach.” The main idea is simple: to keep an open mind to every possible solution and collect any relevant information before making a decision. The goal is to ensure that the information and data are not just used to justify a decision already made, but are actually used to enrich the decision making process.

(14)

14 While data is crucial to reasonable decision-making, data doesn’t speak for itself. It can’t on its own tell us what to do or what to think to solve a problem or to make an improvement action. We also need to carefully assess the data and other information that we acquire. And finally we need human judgment to decide what actions to take.

The debate between data-driven decision-making and intuitive decision-making creates a false dilemma: data without intuition is ineffectual, intuition without data is unreliable. We need critical thinking to be led towards fully informed decision-making.

We can decompose the inquiry process into five steps, each beginning with a question: 𝐅𝐢𝐯𝐞 𝐬𝐭𝐞𝐩𝐬 𝐟𝐨𝐫 𝐚𝐧 𝐈𝐧𝐟𝐨𝐫𝐦𝐞𝐝 𝐃𝐞𝐜𝐢𝐬𝐢𝐨𝐧 − 𝐌𝐚𝐤𝐢𝐧𝐠 𝐏𝐫𝐨𝐜𝐞𝐬𝐬: 𝟑

1. What’s the question? 2. What do we need to know?

3. How good is the data/information? 4. What have we ignored?

5. What should we do?

1. What’s the question?

As crucial as data is, in the age of “big data” we often have abundant data before we have even thought about what to do with it. In addition, data collected for one purpose can often be re-used for another. But the real value of data comes into play by providing answers to insightful questions. So the first step in making a decision is to be clear about the question we want to ask to ourselves or the organization. What is it we want to know? It’s important at this stage to try to figure out what the real question is that we want answered.

It’s also imperative to avoid the fallacy of false dilemma – framing the problem as if it involves a choice between two and only two alternatives. If we do this, we may excessively narrow our search too early. And, although it is important to get clear about the question at the outset, the process isn’t always straight forward. As we work through it, we may discover that the question as we originally framed it isn’t the important question, or other options or alternatives may come up.

(15)

15 2. What do we need to know?

Once we’re clear on what question we want to answer, the next step to achieve is to know what we need to know in order to answer this question. We need to harvest the data and information that are relevant for answering the question. This may seem obvious, but it’s amazing how commonly people make the error of looking for non-relevant information or offer reasons that don’t have anything to do with the question that’s under consideration.

While data is vitally important for making decisions, there are always other aspects of context which should also be considered, for example the political context, the business environment, ethical issues, and even in some cases aesthetics.

3. How good is the information?

Imagine to have gathered relevant data and information to be used as the basis for making our decision. What we need to think about now is just how good the information and data are and how much we can rely on them as a guide. But first it’s important to distinguish between data of various sorts and other types of information.

Traditionally most data has been derived from sampling, using surveys, polls etc., which provide the basis for generalizations about a population (we might call this “small data”). But we now have “big data”, which is based on huge volumes samples, indeed sometimes the whole target population of our research or harvest. We’ll look at how to evaluate data, small and big, below.

4. What have we ignored?

At this point, we will have collected the data and information relevant to our question and evaluated it for its reliability and its generalizability to our context. But before going on to the step of making a decision, there’s another important question that we need to consider. What have we ignored? Is there counter evidence that we haven’t looked at? Are there downsides to the house we like that we haven’t thought about? Are there possible negative consequences of our investment proposal that we haven’t taken into account? We also need to think about whether there are other choices that we have not considered and what are the

(16)

16 pros and cons of our favored option compared with the alternatives. We can’t really evaluate potential decisions or solutions without seeing how they compare to other possibilities.

Two cognitive biases that have emerged from the study of intuitive decision are relevant here. One is confirmation bias, which is the tendency to look for or notice evidence that confirms or supports our beliefs and to ignore or downplay evidence that contradicts them. Another common cognitive bias is overconfidence, which is the tendency to make confident judgments or decisions on the basis of limited evidence. People tend to jump to conclusions, and then be very confident about these premature conclusions.

5. What should we do?

At this point in the process, we’ve collected credible and relevant data and other information and taken care to look at possible downsides of the options under consideration and alternative possibilities. The question now is how we bring all this together to make a decision. There will be a number of considerations on the table and we will have the pros and cons of the various options. The challenge now is to weigh and balance these considerations in order to come to a reasonable decision.

Making reasonable decisions is not necessarily an either-or process. We don’t always have to choose between two distinct options. Sometimes it may be possible to come up with a solution or decision that brings together the strengths of several options and we need to be alert to this possibility. The process described here involves using data and information to make decisions. But it’s not a mechanical or mathematical process; it doesn’t eliminate human judgment. We need to draw on our intuition (intuition has never really been absent as our experience and judgment enter in at all stages, but it plays a particularly prominent role here).

It’s a lot more than a simple feeling: It can be defined Informed Intuition. In the end, any decision that we make needs to feel right. And we need to be able to justify it in light of all the data and information we’ve considered and in light of the reasoning process that we’ve gone through.

(17)

17

1.2 A matter of “Big Data”

1.2.1 The Digital World

Figure 1.1 - Trends in the Information Technology field.

In the next future we can’t see an end in sight for the proliferation of data. Enterprise data volumes are moving from terabytes to tens of petabytes and more. This chance it’s seen by business and IT leaders for face unique opportunities to capitalize on this data for advantage in respect to competitors. Companies that align their processes, operations and corporate culture to embrace and exploit big data will gain the benefit of timely, differentiated insight; those that do not will consider this aspect will risk to fall down.

Web sites alone generate staggering amounts of data. An example of the importance to handle Big Data comes from the most famous Social Network: Facebook. Facebook has more than 1.23 billion active users 4_{, and there are more than 900 million objects (pages, groups,}

events and community pages) that people interact with. Facebook users spend over 700 billion minutes per month on the site, creating at least on average 90 pieces of content and sharing 30 billion pieces of content each month. Facebook’s data infrastructure team is responsible for quickly analyzing all of that data to present it to users in the most relevant way, and to understand preferences, uses and sentiment as a basis for launching new products and overall to make profitable advertisement.

(18)

18 As Facebook demonstrates, big data enables innovative business models, accelerates the development of new products and services. It gives companies a way to outperform the competition and to get to the customer opinion in a fast way, and this it’s only the beginning. And by a recent study the Big Data universe will reach the 44 trillions of GB 5_{in the next future.}

1.2.2 Rethinking BI with “Big Data”

The term “Big Data” includes more than the only structured and transaction-based data. It also includes new terms of “data” likes social network conversations, videos, RFID logs, search indexes, environmental conditions, medical scans and more. Anything that can be digitized can produce data about who is using it, how they are using it and possibly even why they are using it. Big data isn’t always new data; sometimes it’s only existing data looked at in a different, digitized, way. Today there is more data being produced than computer networks are capable of transporting.

Big data techniques can support business intelligence (BI) tools to unlock value from enterprise information. Whereas BI traditionally performs structured analysis and provides a rear-view mirror into business performance, big data analytics provides a forward-looking view, enabling organizations to anticipate and execute on opportunities of the future.

Reporting, Excel Workbooks and even sophisticated drill-down analyses in the dimensions of the data have become a standard expectations of Business Intelligence. However, there are several types of analyses that BI can’t afford yet, in particular when data sets become increasingly different, more granular, near real-time and iterative, requiring organizations to capture in-depth information from a specific moment in time before conditions change. These types of fast-changing and unstructured data, which has high volume, breaks the tradition of the simple relational database model. Such data requires a new family of technologies and analytic methods to extract information and values. For example, big data approaches are essential when organizations want to engage in predictive analysis, natural language processing, image analysis or advanced statistical techniques such as discrete choice modeling and mathematical optimization—or even if they want to mash up unstructured content and analyze it with their BI mix.

(19)

19 Business Companies that blend synergistically BI with big data are preparing to gain a more complete view of business. The result for those is a high-definition visibility into business conditions that contains rich and accurate insight that can help them to understand address customer needs, operational risks and performance opportunities, both within the enterprise and the extended supply chain. With big data analysis, companies can gain understanding not just about what’s happening with the business and why, but to also comprehend what else is possible to do.

1.2.3 Defining a new Data Wisdom

The old way of thinking in the IT Business World says that too much data is a bad thing, because accumulating data rises up infrastructure costs and becomes extremely difficult to manage and mine it to extract useful information. Today, with the enhancements of the computing landscape, companies are realizing that more is better, as big data offers new ways of making money, driving efficiency, and gaining a competitive advantage.

In this global economy, every company can take various benefits from the realizing of a dedicated infrastructure to manage Big Data. However, there will be sectors where the realizing process will be harder and costly than others. Those industries that tend to invest more in IT will be better equipped to handle the technology shift. And they will adapt more quickly to big data and will be more practiced at converting that data into an insight.

But handling Big Data don’t stops to taking useful insights from data. To have a successfully approach it’s necessary for business leaders to have an open mind in front of every possible advantage Big Data can carry inside the organization. So it’s necessary for business leaders to consider, in addiction to an appropriate infrastructure, also a cultural opening from them and the organization personnel towards every changes that derive for managing Big Data.

(20)

20 1.2.4 Jobshifts and Mindshifts

The most visionaries IT specialists believe most organizations will find the hardest part of adopting big data analytics is not the technology itself to handle the amount of information, but cultivating and training the human capital to take advantage of it. Data analysts will also require additional training to understand a new world of analytics with big data.

Even more challenging than the shortage of skilled and trained data analysts is cultivating the collective imagination of an organization to leverage big data for business insight. This obstacle would prove to be far more enduring and intractable than any technology considerations.

It’s permissible to prepare for a mindset shift, not just a technology shift if our company will try to understand and manage Big Data as its best. Unlike previous trends, the adoption of information derived from big data will likely be felt by many departments in an organization, not just IT. In the future, data analysts in all departments of an organization will focus on data from mixed sources to improve decision making.

It’s not so hard to foresee a day in which big data tools will be deployed to almost every business users across the organization areas, empowering them to self-provision data sets and conduct queries without IT intervention, by more user-friendly reporting tools. IT departments will be also able to train business workers on analytical tools so that reports, dashboards, and other instruments of information can be updated directly by the workers, leaving IT to focus on more strategic elements of technology.

By doing this, IT departments empower business workers to create their own knowledge. When analysis happens at various levels in the organization companies promote self-service solution finding. And by allowing queries to be generated by business workers who are closer to the data in the first place, a whole new range of question possibilities and points of view generate richer, more contextual solutions.

(21)

21 Working with business users will expand the capabilities of IT workers, bringing them closer to an alignment of business and IT with strategic goals in every function or process of the company. Business workers will gain a better understanding of the capabilities and limitations of technology, also.

1.3 A Business Intelligence Project

1.3.1 Introduction to the Business Problem

As already said, the focus of this Thesis is the development of a Reporting System. The company that started this project had the objective to develop a Business Intelligence System able to deliver periodically to its predefined recipient several reports with up-to-date data from all over the world.

This company desired several Reports that can be collected in two large families: the first family is the Monthly Worldwide Family and the second is the Summary Family. Each family contains within itself 2 different reports. Further details will be delivered in the next sub-chapters.

Those reports aggregate an enormous amount of data, and until now they were realized using the SAP NetWeaver BW platform by IT specialists of the company. These specialists were periodically hijacked from their core business activities to develop the reports, with weeks of work to develop and deliver these documents to their respective recipients.

The Business Warehouse platform is provided with basic tools for reporting, analysis and information exploring. Those tools are grouped in a suite called Business Explorer (BEx) Tools: these tools are able to gather the information from the right InfoProviders (using queries realized with the BEx Query Designer) and the exploration of the information and the dimensions of data harvested in those queries (using the BEx Analyzer or the BEx Web Analyzer).

(22)

22 Figure 1.2 – The SAP Business Explorer Suite tools. [Content Owned by SAP AG]

IT specialists, using the BEx Analyzer (that is nothing else that a Microsoft Excel 2003/2007 Add-In), were able to collect the data from the query and present that in a MS Workbook. After a relevant formatting and presentation work, those specialists were able to send the reports to their recipients.

Figure 1.3 – The Business Explorer Analyzer, a Microsoft Excel Add-in.

What drove this company (that from here on we will call BrandABC) to the realization of this project is the desire to automatize this process using the SAP Business Objects tools, to free the IT Specialists to this heavy-duty works and to move them to other occupations, more strategically critical to the Information Technology function and for the company’s success.

(23)

23 1.3.2 Project Development Requests

This project is composed by several steps. Those steps can be reassumed in: 1. Realization of the queries for the Monthly Worldwide Family Report 2. Realization of the Monthly Worldwide Family Report

3. Realization of the queries for the Summary Family Report 4. Realization of the Summary Family Report

5. Realization of the Publications for the Summary Family Report

The Monthly Worldwide Family is composed by two different reports: I. R01- Monthly worldwide (EUR Bdg Rate)

II. R02- Monthly worldwide (EUR Bdg Rate) Department

The R01 Report aggregate the data of stores around the world and present them in this report, presenting the monthly budget assigned to every stores of the world. This report contains a sheet for the actual year’s budget and for the next year’s budget.

The R02 Report aggregate the data of stores around the world and present them in this report, but the difference between the R02 report and R01 is that this report organize the monthly budget assigned by Department (also known as Product Family, such as Prduct1 family, Product2 family, and so on). This report contains a sheet for the actual year budget and for the next year budget.

The Summary Family Report is also composed by two different reports: I. Summary Current Year

II. Summary Next Year

Each report must collect the data of a particular family product for two different brands and their selling all over the world, and each report present them for selling areas (Continents) and the relative nations. These information are presented for the current year in the Current Year Report, while in the Next Year Report information are displayed in respect of the budget

(24)

24 information. The products for which these reports must be made are for two BrandABC controlled company, with the following specifics:

BrandABC-ControlledCompany1: (Code: Description)

AA10: ARTICLE1 TYPE A CONTROLLED1

AB20: ARTICLE2 TYPE B CONTROLLED1

AC22: ARTICLE4 TYPE C CONTROLLED1

AAS45: ARTICLE7 TYPE A CONTROLLED1 SPECIAL LINE

ABS45: ARTICLE7 TYPE B CONTROLLED1SPECIAL LINE

ABS25: ARTICLE8 TYPE B CONTROLLED1 SPECIAL LINE

BrandABC- ControlledCompany2: (Code: Description)

AC22: ARTICLE4 TYPE C CONTROLLED2

AAS45: ARTICLE7 TYPE A CONTROLLED2 SPECIAL LINE

(25)

25

ABS25: ARTICLE8 TYPE B CONTROLLED2 SPECIAL LINE

The Summary Report Family must also have a Publication system in order to automatize the procedure of collecting, presenting and delivering the reports by mail to their respective company’s controllers. 

1.3.3 Proposed Solution

The optimal solution to this Business problem can be achieved using the SAP Business Objects Suite that offers a wide radius of solution with its various tools.

In fact, Business Objects is comprehensive of five major areas tools: Reporting tools, Dashboard & Visualization, Interactive Reporting, Analysis and Search & Exploration.

Figure 1.4 – The SAP Business Objects Suite tools solution spectrum.

The ideal tool to use for the reports development in respect of the end-users needs is Web Intelligence - also called WebI. WebI is an SAP Business Objects strategic web-based tool for ad hoc analysis, of the Reporting branch of SAP Business Objects. It provides access to Universes or queries (both are structured objects for accessing data stored in the Business Warehouse) that have been created to meet the needs of persons who access specific data collections in the Warehouse, and provides extensive query construction and report

(26)

26 formatting capabilities, all in a web environment and without the need for desktop client software. We’ll talk about WebI in Chapter 4.

Some of the main features of Web Intelligence are:

 Enhanced and intuitive on-report filtering ability and control

 Additional prompt features including the ability to make prompts options

 Enhanced variable creating and editing capability

 Additional on-report formatting functions

 Improved report linking management

The necessity to use the Hierarchies (we will know what they are in the chapter 3.6) to delivering to WebI structured data forces us to use Queries to supply WebI reports. In this case, SAP Business Explorer Query Designer is the right tool for the queries construction and modeling: BEx Query Designer is in fact the tool used to create ad hoc queries. BW/BI Users can create ad hoc queries in BEx Query Designer by using the Drag-and-Drop method of adding query data to the Rows, Columns, Filters and Free Characteristics section of the query. We will go deeper on the BEx Query Designer in Chapter 3.

To allow those report to use queries, my colleagues above all created a link between the Business Objects Server and the BW repository using the Business Objects CMC (Central Management Console). Using this instrument, my teammates created an OLAP Connection from the Business Objects clients directly to the BW repository. This allowed me to create multiple BI documents based on the same connection, against any cube or query on the BW server, which made it easier the creation of them and also simplifies future maintenance.

Finally, the Business Objects server delivered us the Publication tool that can definitely helped us in the automation process of making and delivery reports. This powerful element can manage the Business Object server for the planning and scheduling of reports or sets of reports with the objective to organize work, update reports with the most recent data and send them to their recipients, in a unique end-to-end process.

(27)

27

2 SAP NetWeaver

TM

_{Business Warehouse}

2.1 The need of a Data Warehouse

2.1.1 The masses of information in the enterprise

The goal behind the implementation of classic data processing systems has primarily been the acceleration, cost reduction, and automation of processes in individual business areas. Enterprise Resource Planning (ERP) systems and other software tools now do this in most companies. The result is that these systems have exponentially increased data volumes needing analysis, as mentioned in the previous chapters. As said before, in the past it was considered a negative thing; but in the present this enormous amount of electronic information is a huge benefit.

Due to continuous innovation in data processing, more and more information is stored in a more detailed format. As a result, there is a need to both reduce and structure this data so it can be analyzed meaningfully. The analysis necessary to create “business intelligence” from the collected raw data requires a varied tool set.

Decision makers in modern, globally operating enterprises frequently realize that their survival depends on the effective use of this information. Unfortunately this information is often spread across many systems and sometimes many countries, thus making effective use of information extremely difficult. This is precisely the challenge that modern Business Intelligence systems attempt to meet. Extensive solutions are required to cover the entire process, from the retrieval of source data to its analysis. Enterprises must be concerned with metadata (business and technical attributes and descriptions of objects) across the enterprise. In addition, they need to consolidate and create homogenous global master data, as well massive amounts of transaction data in differing degrees of aggregation.

(28)

28 Figure 2.1 - ERP systems and Business Intelligence systems. [Content Owned by SAP AG]

In heterogeneous system landscapes, a particular challenge lies in the extraction and preparation of consolidated transaction data and master data from different source systems.

The Business Intelligence software relies on the data that comes from multiple source systems, and for this reason before trying to extract information it’s to initially cleanse and technically and semantically prepare (homogenized) data. The data is then stored in the Data Warehouse component of the Business Intelligence software. Analyzing this information with means of strong and flexible reporting tools then helps to better understand the enterprise information and create knowledge. This knowledge may help the organization to redefine or improve its business strategy and support the inherent business processes.

The transaction-orientated OLTP environments (like an ERP) and the analysis-orientated OLAP environments (like Business Intelligence systems) are interdependent entities. The figure below outlines relations and differences between the two environments.

(29)

29 Figure 2.2 - OLTP and OLAP landscape. [Content Owned by SAP AG]

2.1.2 The Data Warehouse component of Business Intelligence

A Data Warehouse can help to solve the problems mentioned so far. It brings together all operative data sources (these are mostly heterogeneous and have differing degrees of detail). The job of the warehouse is to Extract, Transform and Load (ETL Process) and to provide this data in a usable form to the organization.

A warehouse has the following properties: • Data Warehouse data is stored persistently. • Data is stored for long-term purposes.

• Designed for efficient query processing: The technical environment and data structures are optimized to answer business questions, not to manage atomic transactions.

(30)

30

2.2 SAP Business Warehouse and its Components

2.2.1 The SAP BW landscape

Successful analysis of data relies on the Data Warehouse to load, cleanse, and manage the data for an enterprise’s reporting needs. It enables us to analyze data from operative SAP applications and from external data sources, such as databases, online services, and the Internet.

The following needs were taken into account when designing the SAP Business Warehouse: • A Data Warehousing system needs optimized data structures for reporting and analysis. • A Data Warehousing system is typically a separate independent system

• A Data Warehousing system needs to be based on a comprehensive Data Warehouse architecture.

• A Data Warehousing system needs automated Data Warehouse management functionality.

Against this background, SAP has developed through years SAP NetWeaverTM_{BW. To}

circumvent the numerous disadvantages associated with reporting directly in an ERP system, the data storage, management, and reporting of SAP NetWeaver BW normally takes place on a separate server installed with just the necessary components. This becomes the BW server. As a component of SAP NetWeaver, the BW software is delivered with SAP NetWeaver release versions. The version installed on the company’s server is SAP NetWeaver BW 7.5.

The BW server allow us to examine the relationships of data across all areas of our organization through OLAP technology and multidimensional analyses according to various business perspectives

(31)

31 2.2.2 SAP NetWeaver BW Architecture

Figure 2.3 – The SAP NetWeaver BW position in the global system landscape.

The SAP Business Warehouse architecture is structured in three layers: sourcing the data, storing it in the warehouse, and reporting on it with reporting tools. When working with the SAP Business Warehouse we have to concentrate on the three layers. We have to analyze: • Where data is currently stored, how we can access this data, and how we can upload it to SAP NetWeaver BW.

• How we will store the data in SAP NetWeaver BW, which InfoProviders we will need, and which levels of storage.

• How the data will be accessed by users, for example, which reporting tools will be used.

The graphic below shows a rough draft of the three layers the SAP NetWeaver BW architecture.

(32)

32 2.2.3 SAP NetWeaver BW InfoObjects

Figure 2.5 - An SAP NetWeaver BW InfoObjects example. [Content Owned by BWArea.com]

InfoObjects are Business analysis objects (customers, sales volumes, and so on) and are the smallest available information modules or fields in SAP NetWeaver BW. They can be uniquely identified by their technical name. A technical name is a string of characters unique for an object; every object has one and only one technical name. The InfoObjects can be divided into characteristics and key figures. Characteristics can be further divided into units, time characteristics, and technical characteristics (for example, request ID). Key figures are all data fields that are used to store values or quantities (sales volumes, kilowatt-hours, costs, and so on). Characteristics describe the affiliation of key figures.

For example, costs belong to a cost center, so the cost center is a characteristic. Characteristic InfoObjects can store master data like texts, attributes, and hierarchies. Basically, InfoObjects are the smallest components of SAP NetWeaver BW and are used to build InfoProviders.

They are divided into several different types:

- Key figures normally take the form of a number or a percentage and are expressed in a currency or unit of measure. (for example, Amount)

- Unit characteristics can be specified along with the key figures. They enable key figure values to be paired with their corresponding units in evaluations (for example, a weight unit)

(33)

33 - Time Characteristics form the time reference frame for many data analyses and evaluations (for example, fiscal year). They are delivered with BI Content - a pre-configured set of task-relevant information models based on consistent metadata.

- Technical Characteristics are used for the internal processes within SAP NetWeaver BW. - Characteristics are business reference objects that are used to analyze key figures. (for example employee, materials, color)

As components of the Metadata Repository (the storage area for all SAP NetWeaver BW objects), InfoObjects contain technical and business analyst information for master and transaction data in SAP NetWeaver BW. InfoObjects are used throughout the system to create structures and tables where data is stored. They enable information to be modeled in a structured form and they can used to define reports and to evaluate master and transaction data.

2.2.4 SAP NetWeaver BW InfoProviders

After the data is extracted from a source system into the BW system and initially stored in the PSA (Persistent Staging Area) tables we need to permanently store it in SAP NetWeaver BW.

For permanent storage of those tables we need to create InfoProviders. InfoProviders are comprised of multiple InfoObjects, like key figures (sales volumes, incoming orders, actual costs, and so on) and a link to the characteristics such as cost centers, customers, materials, and so on).

SAP NetWeaver BW offers a range of different InfoProviders for various purposes. Some store data physically, such as InfoCubes, DataStore Objects, and InfoObjects (Characteristics with Attributes or Texts), others are only providing an additional view on the data, such as MultiProviders. Thus, the term “InfoProvider” is the generic term used to describe any objects for which we can create and execute queries in SAP NetWeaver BW.

(34)

34 Figure 2.6 - InfoProvider Environment Overview. [Content Owned by SAP AG]

The table below shows the most important InfoProviders that are available in SAP NetWeaver BW and some of their key characteristics:

InfoProvider Characteristic

Characteristic InfoObject It stores master data. Examples: customer, material, cost

center. Is used, together with the Key Figure InfoObject, to build the tables of the other InfoProviders.

DataStore Object It stores transaction data on the desired detail level.

Example: sales order data on item level is normally used to resolve and consolidate datasets.

InfoCube It stores transaction data on aggregated level.

Example: sales amounts and quantities per month.

MultiProvider It provides view on data of several InfoProviders.

Example: sales amounts in France (InfoCube A) and Canada (InfoCube B).

The graphic below gives us an idea how the different InfoProviders can be used in SAP NetWeaver BW. Each InfoProvider accomplish a separate role and can be loaded with data from the source systems. So, in this manner the data can be accessed and reported via the SAP NetWeaver BW reporting tools.

(35)

35 Figure 2.7 - SAP NetWeaver BW InfoProviders. [Content Owned by SAP AG]

2.2.5 Extraction of Data from Source Systems

A source system provides the SAP NetWeaver BW with data. We have to distinguish between different types of source systems:

SAP Source Systems, for example: • SAP ERP R3

• Others SAP NetWeaver BW • Others

Non-SAP Sources, for example: • Databases from different vendors • Webservice that transfer data

• Files that hold relevant data, called “flat files”

A large advantage of SAP NetWeaver BW is the fact that is has an open architecture face-to-face to external OLTP providers and other legacy systems. It is therefore possible to connect SAP NetWeaver BW to the great majority of possible source systems and to use SAP NetWeaver BW as a consolidated data base for reporting that covers the organization, particularly in a heterogeneous system landscape.

The graphic below gives us an overview of the different source system types that can be connected to SAP NetWeaver BW and outlines the technological interface that is used for the

(36)

36 connection and data staging. Depending on the type of data involved, it is loaded with different technologies to SAP NetWeaver BW. The connectors, of which I will not explain further details, are of 6 different types: DB Connect, UD Connect, BW Service API, File Interface, Web Service and BAPI.

Figure 2.8 - SAP NetWeaver BW Source Systems and Staging Technology. [Content Owned by SAP AG]

2.3 The SAP NetWeaver BW Data Flow

2.3.1 How the Data Flow Works

After the previous introduction to SAP NetWeaver BW Elements, Source systems and connectors, there is the need to understand how data is transferred from the source systems to the SAP NetWeaver BW InfoProviders.

SAP NetWeaver BW supports a data flow that can be used to extract data from the various sources, can cleanse, and aggregate data if necessary and stores the data in the indicated InfoProviders. The graphic below indicates the main objects that are required for a simple SAP NetWeaver BW data flow.

(37)

37 Figure 2.9 - The SAP NetWeaver BW Data Flow. [Content Owned by SAP AG]

In the following the main elements of such data flow are quickly explained:

Source System: Is the source of the desired data.

Data Source / PSA: A DataSource is the object in SAP NetWeaver BW that is created for data extraction from the source system. The DataSource holds information on the “location” of the required data and about the structure of the data, it’s the logical structure of the PSA. Instead, the PSA is the table that stores the required data initially in SAP NetWeaver BW. It holds the data in the source format.

Transformations: With transformations we can transform and change the data that we extracted from the source system. This may be necessary to homogenize data of different source systems.

InfoProvider: InfoProviders are the objects that are used to store the data permanently in SAP NetWeaver BW. They are the Provider of the information on which we base our reporting requests.

InfoPackage: Info Packages are created to schedule the data extraction from a source system to SAP NetWeaver BW. Whenever we execute an InfoPackage a process will access the data in our source system and load it to the PSA table in SAP NetWeaver BW.

(38)

38 Data Transfer Process: The Data Transfer Process is a job that when started loads the data from the PSA table to the respective InfoProvider. Most processes that are required to run on a regular basis can be scheduled and monitored by process chains, automatized processes.

2.3.2 An SAP Source System Master Data Load Scenario

Figure 2.10 - Data Flow for master-data bearing InfoObject. [Content Owned by SAP AG]

The previous figure represent the major steps in the process of loading SAP source system’s master data in the Business Warehouse. The Loading of information in the attributes table of a generic InfoProvider is composed by several steps. Those steps are:

1. Create a DataSource, in this example, on SAP ECC side to define which fields we want to upload to SAP NetWeaver BW

2. Replicate the DataSource to SAP NetWeaver BW to make the fields available 3. Activate the DataSource to generate the PSA

4. Insert as a target of the transformation the InfoProvider

5. Create a Transformation to define how the fields of the DataSource shall be mapped with the attribute fields of the characteristic

6. Create and execute the InfoPackage to load the data, in this example from the SAP ECC table, to the PSA in SAP NetWeaver BW

(39)

39 7. Create and execute the Data Transfer Process (DTP) to load the data from the PSA to the attributes table.

2.4 The SAP BW reporting side

2.4.1 SAP Business Explorer Suite

The Business Explorer Suite (BEx Suite) of SAP NetWeaver BW provides reporting and analysis tools targeted at both power users and end users. We can use the tools of the BEx Suite for strategic analysis and to support the decision-making process in our organization. The tools include query, reporting, and analysis functions. In addition, many output options are supported, including formatted Microsoft Excel, formatted Web output and Adobe PDF documents.

The SAP NetWeaver BW provides InfoProviders through which the data stored in the BW database can be accessed. We can analyze the data of BW by defining Queries against these InfoProviders using the BEx Query Designer. We can determine the way in which the data from our chosen InfoProvider is displayed and analyzed by composing characteristics and key figures in a Query. We can analyze data with the tools of the Business Explorer Suite as shown on the graphic below.

Figure 2.11 - The Business Explorer Suite within the SAP NetWeaver BW Architecture. [Content Owned by SAP AG]

(40)

40 Mainly, the tools I studied are:

BEx Query Designer

By this tool we can use the BEx Query Designer to define Queries for the various InfoProviders. By selecting and combining InfoObjects, characteristics and key figures we determine the way in which we evaluate the data in the selected InfoProvider.

BEx Analyzer

By this tool we can use BEx Analyzer to analyze and present data in a Microsoft Excel environment. Queries, Query views, and InfoProviders that are created with the BEx Query Designer are embedded in workbooks. We can modify the layout and interaction of workbooks and use the Microsoft Excel formatting and formula functions.

2.4.2 Hierarchies

Hierarchies are used in analysis to describe alternative views of the data. They serve a grouping function just as they do in other SAP products, like SAP ECC. A hierarchy consists of several nodes and leaves, forming a parent-child relationship. The nodes represent any grouping we desire.

(41)

41 The hierarchy leaves are represented by the characteristic values. Hierarchies provide flexible easily changed roll up groupings for reporting in BEx and SAP Business Objects reporting tools. If we have thousands of characteristic values to report, hierarchies can be used to obtain a better structuring of the data. The characteristic values of a characteristic are displayed in a tree structure and we can drill down just by clicking on the little triangle. There are several types of hierarchies.

They can be of three different types:

1. Version-Dependent Hierarchy: Characteristic hierarchies can be used in different hierarchy versions. For this, different hierarchy versions existing in the source system can be modeled in SAP NetWeaver BW.

2. Time-Dependent Entire Hierarchy: On the InfoObject Hierarchy tab, we can define that the entire hierarchy is allowed to be time-dependent. In other words, there are different versions for this hierarchy that are valid for a specific time interval only.

3. Time-Dependent Hierarchy Structure: On the InfoObject, we could determine (for example) that a hierarchy node is to be time-dependent; the hierarchy is then constructed for the current key date or for the key date specified in the query.

Example: During restructuring of an organization’s sales districts, it was found that an employee is assigned to different cost centers at different times.

2.4.3 Transports Connection

An important technical requirement in all SAP products is managing the metadata and configuration settings between our development, testing, and production environments. The transport tools that are generic to all SAP products are enhanced with special functions to support the SAP NetWeaver BW. We use the Transport Connection to collect objects that have recently been created or modified. We use the Change and Transport Organizer (CTO) to transport these objects into other SAP NetWeaver BW systems (for example, from the development box to our quality assurance system box, then on to the productive system).

(42)

42

3 SAP Business Explorer

TM

_{Query Designer}

3.1 BEx Query Designer

3.1.1 Introduction to the tool

Figure 3.1 - BEx Query position on the global landscape of BO-BW integration.

The Business Explorer (BEx) Query Designer is used to develop and maintain queries, which are an extremely important reporting objects within SAP NetWeaver BW. Within a query we can define:

• The source of the SAP NetWeaver BW data (InfoProvider) • Required characteristics and key figures (InfoObjects) • Filters for the selection of required data

• Default presentation settings

Once the query is built it can be used to pass SAP NetWeaver BW data to a number of BEx reporting tools, or via OLAP Connections to the various SAP Business Objects reporting tools. It is important to remember that a single query can be reused in many different front end applications. So queries are reuseable objects; in fact we should be planning our query development carefully so that we enjoy maximum reuse of the queries in our SAP NetWeaver

(43)

43 BW environment. This approach will reduce maintenance effort when changes need to be implemented to these reports.

3.1.2 Calling the BEx Query Designer

Using the SAP NetWeaver BW reporting functions, we can evaluate a dataset from an InfoProvider according to various characteristics and key figures. To do this, we define a query for our chosen InfoProvider in the BEx Query Designer. By selecting and combining the InfoObjects in a query, we determine the way in which data from the chosen InfoProvider is evaluated.

Figure 3.2 - BEx Query Designer Main Screen.

3.2 Functions of the BEx Query Designer

3.2.1 Toolbar functions

The following figure gives an overview of the BEx Query Designer functions that we can call from the Query Designer toolbar. The functions are described within the context of query definition. Toolbar functions are context sensitive, which means that certain buttons may be inactive if they are not valid for our context.

(44)

44 Figure 3.3 - BEx Query Designer Toolbar.

1. New Query: With this function, we are able to define a new query. 2. Open Query: Choose this function if we want to open an existing query. 3. Save Query: We can use this function, to save the query.

4. Save All: We can use this function to save the query and all of its components. 5. Execute: Choose this function if we want to display our query results via the portal.

6. Check Query: This function will perform a validity check on our query. In case of errors, they will be showed in the Messages pane.

7. Query Properties: If we want to visualize or change the properties of the query we must click this button.

8. Cut: By this we can remove an object and probably to move it to another location. 9. Copy: By this we can copy an object and probably to duplicate it to another location. 10. Paste: This function is used to insert either the copied or cut object.

11. InfoProvider: This function will open the InfoProvider pane related to the query. 12. Filter: This function will open the query filter pane.

13. Rows/Coulmns: This function will open the Rows/Columns pane.

14. Cells: This function is only available for queries with two structures. Using this button we can define formulas and selection conditions for cells explicitly, cell by cell. In this way, we control the values of cells that appear at the intersections of structural components.

15. Conditions: We canuse this function to define conditions for the query.

16. Exceptions: We use this function to define particular exceptions for the query.

17. Properties: This function opens the Properties pane, where we can define properties of the query and its components.

18. Tasks: Here we can display the actions, which are valid for the query object we have highlighted.

19. Messages: This function opens the Messages pane, where we can find messages relating to the status of the query.

20. Where-Used: Using this function, we can find out in which other objects the query is used. 21. Documents: Using this function we can open the Documents pane where we can edit and view documents for the query definition with help of the Document Browser.

(45)

45 3.2.2 Filter View Functions

Figure 3.4 - Query Designer Layout (Filter View).

The Query Designer contains several different panes and some of them are only displayed when a function button is pressed. In the filter view we can see:

1. Directory tree of the selected InfoProvider: Once we have selected the required InfoProvider, all available objects display in the left screen area of the Query Designer. 2. Characteristic Restrictions: Here we define the characteristic filter values

3. Default Values: In this pane we define the characteristic filter values, which should be used for the initial view of the result set.

4. Properties: Here is where the settings relevant to the currently highlighted query object are displayed. We can also make changes to the setting here; often there will be multiple tabs used to organize the settings in this pane.

(46)

46 3.2.3 Rows/Columns View Functions

Figure 3.5 - Query Designer Layout (Rows/Columns View).

6. Free Characteristics: In this pane we can put the characteristics which we want to offer to the user for navigation purpose. These characteristics do not appear in the initial view of the query result set, the user must use a navigation control to make use of them.

7. Column: Here is where the query objects (key figures or characteristics) must be placed if we want them to appear in the columns of the results set.

8. Rows: Here is where the query objects (key figures or characteristics) must be placed if we want them to appear in the rows of the results set.

9. Preview: This pane gives us an idea of what the layout of the results set will look like when we execute the query.

Other useful information for a better use of the BEx Query Designer are:

• We can use Drag&Drop to transfer the characteristics, key figures, and structures of the InfoProvider into the various panes within the Query Designer.

• We can use the right mouse button to call all of the valid functions that are in the current context menu. To do this, first select the required query component, then select a menu option from the context menu.