SAP BI REPORTING:




Sap Netweaver is an integration architecture for all the business applications

and processes in a modern enterprise. This architecture integrates people,

information and processes within a framework that provides for incremental

implentation of its capabilities.



Business Intelligence is a primary component of the Information Integration

layer of SAP Netweaver. The sap Netweaver Portal is a component of the

People Integration layer.



Business Information Management is about managing all types of information

i.e., Structured and Unstructured from integration to delivery - with a

focus on achieving transparency and broad reach within and outside of the

organization. In the Business Information Management IT practice, there are

three IT scenarios.



1. Enterprise Reporting, Query and Analysis

2. Business Planning and Analytical Services

3. Enterprise Data Warehousing



Enterprise Reporting, Query and Analysis - Extemd the reach



High Focus on User Productivity

Formatted Reporting

Intuitive Ad-hoc Analysis

Advanced Excel Integration

Model-driven BI Applications



Business Planning and Analytical Services - Integrate Planning



Business Planning Integrated in BI Platform and Suite



Enterprise Datawarehousing - Strategic data warehouse platform.



High Performance with BI Accelerator

Flexibility by enhanced data modeling capabilities

Data Transfer Process

Real-Time Data Acquisition

Web-based Administration cockpit.



Some Issues might arise when using a data warehouse in the Retail Sector:



Checking ranges of goods in order to identify slow moving items and big sellers.

Analyzing regional locations to investigate the profitability of difference branches.

Investigating the effectiveness of market analyses.

Evaluating Customer surveys and complaints

Analyze warehouse stocklevels.

Analyzing shoping carts using cash register receipts.





A concrete query might be:



What were the sales volumes for the Cosmetics, Electrical Goods, and Household

Goods departments in the years 2005 and 2006 in New York and Washington, DC?



In a brief, a data warehouse can:



Pool data from different, mostly heterogeneous sources

Overcome this heterogenity on different levels( system,schema, and data)

Stage data in the form required by the user



Decison-makers urgently need reliabile information from Production, Purchasing,

Sales and Distribution, Finance, and Human Resources departments.



Some demands are made on a datawarehouse:



Standardized structuring and presentation of all company information.

An easy-to-use, single point of access to all company information

Sophisticated business reporting methods for self-service analysis at all levels

Fast and cost-effective installation

A high performance environment

Data staging from heterogeneous environment

Release of source systems and IT organization

Data access independent of the source system

Analysis of data over a specific period of time, and the storage of historical

information(time-dependent master data)





If a characteristic( for example, zip code) is assigned to another

characteristic(for example, sold-to-party), the first characterstic becomes

an attribute of the second.



Business analysis- objects( customers, sales volumes, and so on) are

called InfoObjects in SAP BI. These InfoObjects can be divided into

characteristics and keyfigures.



The central data containers that form the basis for reports and analyses

in SAP BI are called Infocubes. They contain key figures( Sales volumes,

incoming orders, actual costs, and so on) and a link to the characteristic

(master data of the SAP BI system such as cost centers, customers, materials,

and so on). Key figures and characteristics are infoobjects.





An Infocube consists of several database tables that are linked according

to the star schema. They include a fact table that contains the Infocube

key figures, as well as several surrounding dimension tables that store

the links to to the characteristics.



Each infocube has one fact table and a maximum of 16 dimensiont tables.

Each fact table can contain a maximum of 233 key figures.

A dimension can contain up to 248 freely available characteristics.



Infoprovider is the super-ordinate term for an object that you can use

to create reports in Business Explorer(BEx). Infoproviders are objects or

views that are relevant to reporting.



They ae two types of data stores.

Physical stores and Logical views of physical data stores



Physical stores include:

Infocubes

Data store objects

Infoobjects



Logical views of physical data stores include:



Infosets

Remotecubes

Virtual Infocubes

Multiproviders



DataStore Object:

A datastore object stores data at the basic level(document level). It is

normally used to resolve and consolidate datasets. These datasets ae often

from various data sources and/ or source systems.



The Data Warehousing Workbench is a workplace for SAP BI administrators,

Data Warehousing Workbench functions are used to configure, control, and

administrate SAP BI.



Question and Answers:



What type of source systems can be connected to SAP BI?

Data sources based on XML

SAP Systems

Databases



Fax machines cannot be directly connected to SAP BI as a source system.

A facsimile image could be stored in SAP BI as a document through document

integration.



Navigation:



When you begin an analysis in the Web Analyzer or the Bex Analyzer, you

begin with the Open dialog. In this dialog, you are able to choose:



The system from which you will retrive data

The object type( query, query view or infoprovier) you will access

The grouping objects of the chosen type( History, Favourites or Infoareas)

to be displayed

Any filtering value to limit which objects are displayed.



Navigating Options:



Basic Navigation:



Back

Back to start

* Select filter value

*keep filter value

*remove filter value

filter and drilldown

*drill down

*drill across

*remove drill down

change drill down



Other Options

* Exchange with

Swap axes

sort

goto

convert to formula

add local formula

explain

properties

query properties

save view

broadcast and export

documents



Note: The star indicates that can be done via drag and drop.



The list of the first level navigation options you can access from both\

the Bex Analyzer and the Web analyzer. Bear in mind that not all options

are available in all context menus, and that many of the first level

choices have additional options available.



Bex Analyser Navigation Buttons:



1. Chart

2. Filter

3. Information



In the Bex analyzer, three buttons are provided to the user for

frequently-used navigations. The Filter button, when selected, opens a

Navigation Area within the analysis. This navigation area allows the user to

change the drilldown, select filter values and swap the location of

characteristics and key figures in the analysis.



The chart button, if selected, changes the analysis table into a chart.

Once the chart is displayed, the title on the button changes to Table in

order to toggle the display back to a tabular view.



The info button, when selected, displays a variety of text elements which

serve to give the user a better context for the analysis results.



Exchange..... with....



Any time you want to swap the location of one object in the analysis with

another object, you may simply drag one item on top of the other and the

change will be made automatically. The user swaps Distribution Channel for

Sales Group. The user could have also brought up the context menu for

either characteristics and selected Exchange--- with... to effect the swap.



Drill down:

One of the most common navigation function is the Drill Down (or Drill Across).

In the graphic above, the user drags division from the navigation area and

drops it on top of the values for Distribution channel. This has the

effect of adding an additional level of detail for Division to the analysis.



Keep filter value:



You can select as filter value directly from the results area of an executed

report. To do this, you could position the cursor on the filter value required,

and choose Keep Filter Value from the context menu. The filter value is

then displayed in the report and the report results are restricted to

the filter value in question. Or you could, as the graphic shows, simply

drag the characteristic value you want to use as a filter to the right side

of the navigation area and drop it. The value will automatically be used

as a filter for the analysis.



Remove Filter value



With the Remove Filter Value option, you can exclude certain values from

appearing in the analysis. Often, this is easier than trying to include

many values as filter values.

simply drag the characteristic value outside of the analysis area and it

will exclude any data for the characteristic value.





QUERY DESIGNER:



In the query designer open dialog box, choose of the three button on

the left side of the dialog box:

Find(use search options to locate an infoprovider)

History(Your most recently used infoproviders are listed)

Infoareas( A list of all infoproviders organized by infoareas is shown)



Some properties for Query designer:

New query

save query

open query

publish query on web

check query

query properties

cut

paste

insert

infoprovider(pane open)

Filter(pane open)

table view

rows/collumns(pane open)

cells

conditions

exceptions

properties

tasks

Messages(open pane)

where used

documents

technical names



Table View: we can create a query for tabular reporting( each query

element in a separate column), as well as for online analytical processing

(olap) reporting, by activating or deactivating the table view mode in

the query definition when defining a query. this function is only available

for queries with one structure. You can only have queries with two structures

in the multi-dimensional display. They are not suitable for olap reporting.



Cells:

This function is only available for queries with two structures. You can

define formulas and selection conditions for cells explicitly. In this

way, you control the valus of cells that appear at the intersections of

structural components. This function enables you to access individual

cells in queries or to assign special values to these.



Conditions: we use this function to define conditions for a query. For

each characteristic, you can give limit conditions to the key figure values

in order to determine, for example, all sales revenues above or below

a specified threshold value. The chosen characteristics are displayed

int he query with restricted key figures.



Exceptions:we use this function to define exceptions for a query. Exceptions

are deviations from normal key figure values, as defined by you, and are

highlighted in color in the query view.



Query properties: Choose this function if you want to change the

descriptions of the query or define the settings for the result position,

display options, numeric display, zero display and key date of the quey.



QUERY DESIGNER MENUS

QUERY EDIT VIEW TOOLS HELP

NEW

OPEN

CHECK

SAVE

SAVE AS

DELETE

PROPERTIES

EXECUTE

PUBLISH

END AND THROW OUT CHANGES



Query designer layout (filter view)



1. Directory tree of the selected infoprovider

2. Characteristic Restrictions

3. Default values

4. Properties

5. Messages



Query designer layout(rows/columns view)



6. Free characteristics

7. Columns

8. Rows

9. Preview

10.Tasks

11.Where used



Some example defined query(filter view)



1. The characterist Cal Year/Month(0CALMONTH) was chosen as a global

filter and restricted to the range of months January to April

2005 in the Characteristic Restrictions pane.

2. The characteristic Material was restricted to a small number of the

single values that will appear as the default filters when the

query is executed.



Examples defined query(rows/column view)



1. The characteristic Sold to countery was selected as a free characteristic.

2. The keyfigures Incoming Orders(CV) and Incoming orders were placed

in the columns.

3. The sold-to party characteristic, with attributes for address and

account group, was included in the rows. The material was also

included in the rows. This was restricted to a range of materials

in the default fitler pane.

4. The preview area shows how the report is structured.



Once we have saved the query,we have the following options;



1. we can execute the query in the Bex Analyzer and include it in a

workbook. To do this, choose Quit and Use Query However you must first

have launched the query designer from the Bex Analyzer in order to see

the required function(right mark)

2.we can display the query on the web via the Netweaver portal. To do this,

choose execute function.

3. we can use the query in the WAD as a data provider for web items.

4. we can use the query in the report designer as a dataprovider.

5. we can use the query in the tabular display in crystal reports as

a data source for formatted reports.



Filtering Data in the Query Definition:



The filters are defined under the tab filter. Be sure to select this tab

before attempting to define any filters. Once you are displaying the \

filterstab you can define the filters in either the Characteristic

Restrictions pane or the Default values pane.



1. Filters set in the characteristic restrictions pane are always applied

to the query result and cannot be modified by the user in the result.

These are often regarded as a global or static filters.



2. Filters set in the Default values pane are applied immediately once

the query is executed. However, the user is able to navigate freely within

these filters. The user cannot add additional filters to the default values

i.e. the filters in the query definition set out the maximum range of the

result data a user can display, this may produce more data than the user

requires so the user simply navigates to a reduced result set by modifying

the filters in the report.



There are two basic ways to restrict a characteristic in the query definition:

1. By selecting a characteristic value from the Infoprovider tree(on the

left side of the query designer). Simply open the tree to display the

required characteristic within the appropriate dimension and the open the

characteristic node to reveal the sub folders, one will be called

characterstic values. Open this folder to display the values and then drag

and drop these to the required pane. You don't need to have previously

dragged the characteristic into the pane beforehand, the act of dragging

the first characteristic value will also drag the characteristc into

the pane.

2. We can also restrict a characateristic you have already included in

the query definition by calling the context menu for the characteristic

and choosing Restrict. We could also highlight the characteristic and then

display the Tasks tab to show the suitable tasks, one of them will be

restrict. And finally a double-click on the characteric would also open

the default task which for a characteristic in the filter view would be

restict.



QUERY PROPERTIES:



General:

Description,Technical Name,Infoprovider,Keydate



It time dependant master data is used in the query(attributes,hierchies,

text) this date(which can be fixed or supplied by a variable) is used to

select the correct master date. The check box Use Standard date is used

to tell the query to use the current date at query run-time.



Variable Sequence Tab:



Variable Sequence



We can change the sequence of the variables to ensure they are presented

to the user in a logical order



Display Tab:



Adjust Formating after Refreshing

Hide Repeated key values

Display scaling factors for keyfigures

Document Links



ROWS/Columns Tab



Result Position(Rows and Columns)

Supress Zeros



Value Display Tab:



Display of the +/- sign

Decide how you would like to present the negative values.

Zero value Display



Planning Tab

Startup view

This setting determines whether the query is started in display or change

(planning) mode. Planning mode allows the results cells to be changed.



Extended Tab:



Release for OLE DB for OLAP

If you want to expose the data from this query to a third-party BI tool

then this check box must be marked so the Microsoft ODBO interface is filled.



Infoproviders In SAP BI Reporting:



Infoproviders are objects for which you can create and execute queries

in SAP BI. These include objects that physically store data - the data

targets, such as infocubes, datastore objects, and Infoobjects(characteristics

with attributes or texts). They also include objects that do not contain

any physical data, such as Infosets, Virtualproviders and Multiproviders.

Infoprovider is the generic term used to describe any objects or views

relevant to reporting.



Data Targets:

Data targets are SAP BI objects that have their own data store(which means

they physically contain data). These objects include infocubes, Datastore

objects and Infoobjects(characteritstics with attributes or texts). The

system supplies data targets with data from the source system using a

load proces (or by writing directly into the tables for real-time object

types). Data targets become infoprovidrs if they are used in Bex reporting.



Infocubes that have a datastore in the SAP BI system are called Standard

Infocubes(type of infocube that distinguishes them from virtual types)



Characteristic infoobjects have to be included in the infoprovider tree

in the data warehousing workbench to make them available as data targets

for flexible updates and as infoproviders for reporting.



Data store objects store consolidated and cleansed data on a detailed

(atomic) level. In reporting, you use datastore objects for detailed

reporting in SAPBI, for example, for reporting on individual business

documents(orders, invoices etc).



In contrast to multi-dimensional data stores for infocubes, data in

Datastore Objects is stored in flat database tables. A datastore object

contains a key (for example, document number, item number), as well as

data fields, which can contain key figures or characteristics (for example,

customer, invoice quantity). You can transfer data store object data into

connected Infocubes or into additional datastore objects in the same

system or even in a different system.



A virtual Provier is an infocube whose transaction data is not physically

managed in SAP BI, but stored externally in the source system.



Only the multi-dimensional structure of the virtual provider is defined

in the SAP BI System. The data for reporting is read onlilne from another

system. In reporting, you define queries on virtual proviers in the same

way as you define queries on Standard Infocubes.



However, since the data is read online from the source system, the performance

differs when you execute reports for Virtual provider and, therefore,

we recommend that you only use virtual prover in specific, well planned,

scenarios(with small quantities of data, relatively infrequent data

requests with a restricted number of users).



BI Infosets do not have a data store of their own, but instead connect

flat tables using joins and, therefore, provide an additional semantic

layer(reporting-relevant view) for BEx reporting.



We can create BI Infosets from Infocubes(only standard type), Datastore

objects and characteristic infoobjects with master data.



BI Infosets enable temporal joins with which you can create reports

depicting changes over time. For example, a link from document data in an

Datastore object to time-dependent master data (for example, link a sales

transaction to the master data of the person who was head of the department

at the time of the transaction).



Multiproviers are based on a union connection (union), unlike joins

in BI Infosets(intersection).



An infocube can contain global elements, such as, calculated key figures

(as can all other info proviers) that are available for all query

definitions for this infoprovier. These elements are defined on a global

level for the infoprovider and you can use them in many different reports.



Infosets in the Query Designer



The system also automatically generates the key figure Number of Records

(1ROWCOUNT) for infosets



Two dimensions are provided for each Infoprover inthe infoset. Attribute

and Keypart. Therefore, for an Infoset composed of an Datastore Object

connected to a characteristic, four dimensions display. The respective

charcteristics are then assigned to the dimensions.



In the Key figure folder of Multiprovider, you can find the generated

key figure Number of Records(1ROWCOUNT), if one of the infoproviders

included already contains this keyfigure, for example, in datastore objects.



Restricted keyfigures are(basic) keyfigures of the infoprovider that

are restricted(filtered) by one or more characteristic selections. The

key figure that is restricted by one or more characteristic selections

can be a basic keyfigure, a calculated key figure, or a key figure that

is already restricted.



We can create restricted key figures at the infoprovider level, or locally

in the query definition. Restricted keyfigures that are defined at the

Infoprovider level are available in every query definition of the

infoprovider in question.



Variables:



Instead of selecting absolute filter values we can choose to use a

variable which will be filled at run time, perhaps from a user input.



In the query designer, we can use a formula to calculate key figures

that are not in the infoprovier by using basic key figures, restricted

keyfigures and existing calculated key figures int he formula definition.



The new restricted key figure will appear in the key figure structure

and therefore will be part of the result(as well as the new formula).

If you don't want to see the restricted key figure in your result simply

use the hide feature under the properties.



Keyfigures always have the same units when a query is executed. This

means that the formula is semantically incorrect if we add a currency

unit( for example, EUR) to a unit of weight (for example, kg). If you want

to calculate values without using units, you should use the value without

dimension function.



Currency Translation:

A currency translation in the Business Information Warehouse can take

place at two points:

@update rules and In reporting.

Local currency

company currency

transaction currency



Exception Aggregation:

Use exception aggregation to derive additional information out of a limited

numbr of key figures provided in the infocube we want to analyze. Using the

new functions of exceptiion aggregation, we can create calculated key

figures using a formula that uses exception aggregation itself(this is

nested exception aggregation). We can also use calculated key figures

created on cell level(using the cell editor) with the new functions of

exception aggregation.



Defining Nested Exceptions:

As a sales manager, we want to analyze the number of products sold and

the average number of different products sold to different customers for

each sales year. In our infocube only a key figure of the net weight for

the different sold products is provide. Using the new functions for

exception aggregation, you can derive the additionally required two

keyfigures just from the net weight for sold products.



STRUCTURES IN QUERIES AND REUSE OF STRUCTURES

Structures are combinations of characteristics and key figures(of basic

keyfigures, calculated key figures, or restricted key figures) of the

infoprovier. A structure can be used in a planned/actual comparision or

in a contribution margin scheme.

Structures can also be used in several queries for the same infoprovider.

In order for this to be possible, we need to save them as reusable structure.

In order to make a distinction, we calls structures that are not stored

as reusable structure local structures. Local structures are only used

in one query.



A query can have a maximum of two structures, there is no restriction as

to where these structures appear in the layout, i.e. we could put

both structures in the columns. We can create some very flexible layouts

using two structures.



Changes made later to global structures affect all queries in which they

were used. If you only want to make local changes in a particular query,

we can choose Remove Reference in the context menu for the structure,

and thereby, change the reusable structure in to a local structure.

changes made later do not affect the definition of the reusable structure.

nor the queries that you made with this reusable structure.



Formula Collision:

A formula collision occurs when the query uses two structures and there

are formula in both structures. The point at which the formula intersects

is called a formula collision. We need to tell the system how to resolve

the collision(only one formula can be carried out for the cell) by making

the appropriate setting in the properties dialog.



The formula collision function only displays in the properties dialog

box if two structures are used in the query definition and both

contain formulas.



In places where the two formulas collide, it is not clear how the system

should calculate. We can therefore choose the formula we want to use to

calcualte the value.