I just completed my SAP BI-BW Module form a private institute in Bangalore.
I feel my consultant gave me a clear cut idea about SAP BI-BW. Here I am gonna post something informative from my Doubt session 😕 which I had last day (After the Course) with my consultant. 🙂
The first part is some important terms which I have been using from past two months. I just wrote it the way I could understand. Hope you also won’t feel much tricky to understand.
At last I have given few questions arisen in my mind and got solution by my consultant.
Kindly provide your feedbak 😎 😳
Terms Which we use in SAP BI/BW environment very frequently
Business Intelligence:-SAP Business Objects business intelligence (BI) solutions simplify data manipulation, allowing users to access, navigate, analyze, format, and share information across a corporate environment.
Data Warehouse: – Data Warehouse is a dedicated reporting and analysis environment based on the star schema (Extended) database design technique and requiring special attention to the data ETTL process.
ETTL: – Extraction, Transformation, Transferring, Loading.
SAP R/3:- SAP R/3 is based on client/server architecture and uses a relational database to track all information related to a corporation. All data is stored in a database, and the data is processed in the application layer on the application servers, and the final result will be displayed in the presentation layer.
Enterprise Resource Planning:-Enterprise Resource Planning Systems (ERP) are commercial software packages that facilitate the seamless integration of all the information flowing through a company. ERP provides information management and analysis to organizations
SAP BI/BW Process Flow deals with the Modeling, Extraction and Reporting of available data.
Modeling: – Data modeling is a method used to define and analyze data requirements needed to support the business processes of an organization and by defining the data structures and the relationships between data elements.
Extraction: – To report on data, data must be extracted from a database using an extraction tool. The process of data extraction involves retrieval of data from disheveled data sources. The data extracts are then loaded into the staging area of the relational database. Here extraction logic is used and source system is queried for data using application programming interfaces.
Reporting: – Reports are the formatted result of database queries and contain useful data for decision-making and analysis. In other way, data reporting is the process of converting data into usable, actionable information.
InfoObjects:- In BW, key figures and characteristics are collectively called InfoObjects.
KeyFigures:- Key figures are numeric values or quantities, such as Per Unit Sales Price, Quantity Sold, and Sales Revenue. The maximum number of characters allowed for the technical name is 9. Reference Characteristic
Characteristics: – Characteristics are descriptions of fields, such as Customer ID, Material Number, Sales Representative ID, Unit of Measure, and Transaction Date. The maximum number of characters allowed for the technical name is 9.
Unit Characteristics: Unit characteristics provide a means of key figures values, stores currencies or units of measure. Example: Currency unit (0CURRENCY) Holds the currency of the transaction ($, EUR, and so on) Value unit (0UNIT) Hold the unit of measure (Gallon, Inch, cm, PC)
Time Characteristics: –Time characteristics form the time reference frame for many data analyses and evaluations. They are delivered with BI Content.
Technical Characteristics: Technical characteristics have administrative purposes. Eg-(Req Id)
Data Store Objects: – A DataStore object serves as a storage location for consolidated and cleansed transaction data or master data on a document level. This data can be evaluated using a BEx query. A DataStore object contains key fields (such as document number, document item) and data fields that, in addition to key figures, can also contain character fields (such as order status, customer).
Types of DSOs: – Standard DSO, Write-Optimized DSO & Direct Update DSO
Info Cubes: – An InfoCube is a fact table and its associated dimension tables in the star schema. The maximum number of characters allowed for the technical name is 30
- Fact Table
The Fact table is the central table of the InfoCube. Here key figures & pointers to the dimension tables are stored. There are two fact tables: the F-fact table and the E-fact table. If you upload data into an InfoCube, it is always written into the F-fact table. If you compress the data, the data is shifted from the F-fact table to the E-fact table.
- Dimension table/ Dim table
Part of the Star Schema structure for InfoCubes. Dim tables contain pointers to the Fact tables & to the Master Data (SID tables)
InfoSets:- InfoSet describes data sources that are defined as a rule as joins of DataStore objects, standard InfoCubes and/or InfoObjects (characteristics with master data). An InfoSet is a semantic layer over the data sources.
Multi-Providers:- A MultiProvider is a special InfoProvider that combines data from a number of InfoProviders and makes them available as a whole to reporting. A MultiProvider allows reporting using several InfoProviders
Virtual Provider:- A Virtual Provider is a type of InfoCube where the data is not managed in BI. Only the structure of the VirtualProvider is defined in BI, the data is read for reporting using an interface with another system.
Process Chain: – A process chain is a sequence of processes that are scheduled to wait in the background for an event. You can include process chains in other process chains to manage a group of them, known as meta chains.
Business Content:- Business Content is a complete set of BW objects developed by SAP to support the OLAP tasks. It contains roles, workbooks, queries, InfoCubes, key figures, characteristics, update rules, InfoSources, and extractors for SAP R/3, and other mySAP solutions.
Metadata Repository :- Metadata repository contains information about the metadata objects of SAP NetWeaver Business Intelligence, important object properties and their relationships with other objects. Metadata contains data about data.
PSA : –The Persistent Staging Area (PSA) is a data staging area in BW. It allows us to check data in an intermediate location, before the data are sent to its destinations in BW..
Transformation: – The transformation process allows you to consolidate, cleanse, and integrate data. You can semantically synchronize data from heterogeneous sources.When you load data from one BI object into a further BI object, the data is passed through a transformation. A transformation converts the fields of the source into the format of the target.
DTP:- The Data Transfer Process (DTP) transfers data from source objects to target objects in BI 7.x. You can also use the data transfer process to access InfoProvider data directly. Prerequisite: a transformation to define the data flow between the source and target object.
DataSource:- A DataSource is not only a structure in which source system fields are logically grouped together, but also an object that contains ETTL-related information. Four types of DataSources exist:-DataSources for transaction data, characteristic attributes,characteristic texts & characteristic hierarchies
Aggregates:-An aggregate is a subset of an InfoCube .The BW OLAP processor selects an appropriate aggregate during a query run or a navigation step. An aggregate is made up of characteristics and navigation attributes belonging to an InfoCube.
Indexing :-An index is a copy of a database table that is reduced to certain fields. This copy is always in sorted form. Sorting provides faster access to the data records of the table.
Rollups:- When you load new data packages into the InfoCube, these are not immediately available in reporting for use in an aggregate. To fill the aggregate with the new data from the InfoCube, you first have to load these into the aggregate tables for a set time frame. This process is known as roll up.
Navigational Attributes:- A navigational attribute indicatesa characteristic-to-characteristic relationship between two characteristics. It provides supplemental information about a characteristic and enables navigation from characteristic to characteristic during a query. Used for drill down reporting.
Transfer Rules :- Transfer rules specify how DataSource fields are mapped to InfoSource InfoObjects. Transfer rules transform data from several transfer structures into a single communication structure. In BI 7.0, transfer rule and update rule are replaced by transformation.
Change Log :- A change log is a text file created automatically by the system that contains all revisions (e.g.,additions, deletions, updates, etc.) that have occurred since the last data load from SAP R/3.
Calculated key figure:- A calculated key figure is data that are computed prior to, and in anticipation of, ad-hoc requests. Calculated key figures can be based on key figures available in the query, constants, formulas, and variables.
Restricted key figures:- Restricted key figures are key figures that are associated with an InfoCube and used for reporting purposes. They are restricted by one or more characteristics such as incoming orders for selected time periods, sales for selected materials, and so on.
SID (Set ID or Surrogate ID):- SIDs are surrogate IDs that provide the pointer/technical link to the master data (attribute, hierarchy,and text) tables. A SID table contains the SIDs. SID tables play an important role in linking the data warehouse information structured to the subject-oriented InfoCubes.
- SAP BW Technical Architecture
The SAP BW architecture is based on a three-tier architecture with the following key
- 1. Database server. Where the data is physically stored (e.g., ODS, PSA, InfoCube, and metadata repository)
- 2. Application server. The application server is based on the OLAP processor. It is used to
retrieve the data stored in the database server (it does not store data except in transient memory).
- 3. Presentation layer. Manages reporting and data access
- SAP BW System Landscape
- a) SAP BW Development
- b) SAP BW Test
- c) SAP BW Production
Advanced Business Application Programming (ABAP) is the programming language developed
by SAP for application development purposes. All SAP R/3 applications are written in ABAP.
ABAP is used by the developers of SAP and by customers to customize the SAP R/3 application
and provide additional business functionality. The most common uses for ABAP programs are:
-Producing custom reports
– Developing custom interfaces for SAP
– Adding custom routines during the transfer and update rules processes within SAP BW
Star Schema:- In business terms, a star schema is a method of organizing information in a data warehouse that allows the business information to be viewed from many perspectives. In STAR SCHEMA, A FACT Table in center, surrounded by dimensional tables and the dimension tables contains of master data
Extended Star Schema:- In Extended Schema the dimension tables does not contain master data, instead they are stored in Master data tables divided into attributes, text & hierarchy. These Master data & dimensional tables are linked with each other with SID keys. Master data tables are independent of Infocube & reusability in other InfoCubes.
Some of the W-H Questions Arisen in my mind & got solution from my Consultant
1- When are Dimension ID’s created?
Ans: When Transaction data is loaded into Infocube
2- When are SID’s generated?
Ans: When Master data loaded into Master Tables (Attribute, Text, Hierarchies).
3- How would we delete the data in ODS?
Ans: By request IDs, Selective deletion & change log entry deletion.
4 Partitioning possible for ODS?
Ans: No, It is possible only for Infocube and PSA in BW
5-T-Code to delete BEx query in production system?
6- What is the use of Filters?
Ans: It Restricts Data.
7- What is the Difference between Transfer Rules and Update Rules?
Ans: Transfer Rules: Which specifies how the mapping between the Data Source fields with the Infosource fields (So called Communication Structure).
Update Rules: Which specifies how data will be updated into the Data Targets (ODS or InfoCube). (This internally does some technical name mapping)
8- What is Open Hub Service?
Ans: The Open Hub Service (process) enables us to distribute data from an SAP BW system into external system. In BW 3.5 version an object called Infospoke is used to send data from BW to the external systems. In BI 7 version we use open hub destination (object) for this purpose.
9- What is ASAP Methodologies?
Ans: ASAP stands for Accelerated SAP methodology which is used to implement and deliver the project efficiently.
The five stages of ASAP methodologies are-
Project plan, Business Blue print, Realization, Final preparation & Go-Live – support.
- 1. Project Preparation: In this phase the system landscape will be set up.
- 2. Business Blueprint: It is a detailed documentation of your company’s requirements. (I.e. what are the objects we need to develop are modified depending on the client’s requirements).
- 3. Realization: In this only, the implementation of the project takes place (development of objects etc)
And we are involved in the project from here only.
- 4. Final Preparation: Final preparation before going live i.e. testing, conducting pre-go-live, end user training etc.
End user training is given that is in the client site you train them how to work with the new environment, as they are new to the technology.
- 5. Go-Live & support: The project has gone live and it is into production. The Project team will be supporting the end users.
10- Why we delete the setup tables (LBWG) & fill them (OLI*BW)?
Ans: For the first time when we are loading the historical data in the setup tables as a caution we perform deletion for the setup tables in order to avoid any junk data residing in it. Also when a DataSource is enhanced we will go for deleting the setup tables followed by the statistical setup (filling setup tables)
11- What is the significance of KPI’s?
Ans: KPI’s (Key Performance indicators) indicate the performance of a company. These are key figures
12- What is the use of Process Chain?
Ans: The use of Process Chain is to automate the data load process. Used to automate all the processes including Data load and all Administrative Tasks like indices creation deletion, Cube compression etc.
13 What is compression?
It is a process used to delete the Request IDs and this saves space.
14- What is partitioning and what are the benefits of partitioning in an InfoCube?
Ans: Partitioning is the method of dividing a table (either column wise or row wise) based on the fields available which would enable a quick reference for the intended values of the fields in the table. By partitioning an infocube, the reporting performance is enhanced because it is easier to search in smaller tables. Also table maintenance becomes easier.
15- What is time dependent master data?
Ans: Time dependant master data are one which keeps changing according to time. For example: Assume a Scenario, Sales person A works in East Zone till (Jan 30th 2005), and then moves to North Zone from Jan31st 2005.Thus the master data with regard to Sales person A, should be changed to different zone based on a time.