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
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:
Data store objects
Logical views of physical data stores include:
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
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
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.
Back to start
* Select filter value
*keep filter value
*remove filter value
filter and drilldown
*remove drill down
change drill down
* Exchange with
convert to formula
add local formula
broadcast and export
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:
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.
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.
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.
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:
publish query on web
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.
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
END AND THROW OUT CHANGES
Query designer layout (filter view)
1. Directory tree of the selected infoprovider
2. Characteristic Restrictions
3. Default values
Query designer layout(rows/columns view)
6. Free characteristics
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
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
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:
We can change the sequence of the variables to ensure they are presented
to the user in a logical order
Adjust Formating after Refreshing
Hide Repeated key values
Display scaling factors for keyfigures
Result Position(Rows and Columns)
Value Display Tab:
Display of the +/- sign
Decide how you would like to present the negative values.
Zero value Display
This setting determines whether the query is started in display or change
(planning) mode. Planning mode allows the results cells to be changed.
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 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.
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
A currency translation in the Business Information Warehouse can take
place at two points:
@update rules and In reporting.
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
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.
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
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.