Jun 23, 2014

Why master data harmonization is so important when uploading data from external sources


In order to ensure that a cross-data source reporting is possible after uploading all the different data sources like Point-of-Sale data (e.g. from retailers) or Market Research data (e.g. a Retail Panel from syndicated data providers), it needs to be taken care of that the data itself is consistent across the sources.
This is done through data harmonization. It establishes the link between the internal views of products and locations to those of the retailers, distributors and syndicated data providers. Such a linkage comes along with some key benefits when talking about enterprise reporting capabilities:

  1. Consistent, shared data and best practices that can be leveraged across retail accounts and internal departments' lead to more process efficiency… during operations and decision making.
  2. Improved collaboration with the retailers by talking the language of the retailer, using his product or store ID and names e.g. during negotiations.
  3. Reduced costs through better internal and external collaboration that is based on all participants sharing a common view of the business.  

With that, harmonized data creates a 360 degree view of the business.

Easy example:
While a certain product that is produced by the manufacturer "Taste" is called "Chili Chocolate" in-house at the manufacturer, the different retailers and syndicated data providers might have other names, such as "CHILI_CHOC_1254", "Chocolate_Taste_Chilli","03-TAS-chil", etc. How does the manufacturer know which ones represent his internal product so that he can later on be sure to report based on the same data?

Some use cases for cross-data source reporting

  1. Sell-in vs. Sell-Out Comparison:
    e.g. the manufacturer wants to analyze deviations between the quantity that he shipped to his retailers compared to what has been sold to the end consumer. This helps to uncover situations in which the retailer build up stock on purpose e.g. during trade promotions as well as provides answers to the questions "Did the sales to the end consumers increase accordingly?" Or "Did the retailer just increase stock level on their side and the manufacturer can expect a decline in his own sales soon?"
  2. Cross-Country sales performance of acertain retailer
    e.g. the manufacturer wants to get an aggregated view of the retailer's sales performance in U.S., Canada and U.K. He also wants to compare KPIs and trends for the different countries. For this, a harmonized reporting based on the retailer's POS data of the three countries is needed.
    Benefit:  Internally, the manufacturer can view the data in his own view = his own product IDs and descriptions….When talking to retailer, he can also see the data in the language of retailer by using the Product IDs and descriptions of the retailer.
  3. Brand performance in a certain country
    e.g. the manufacturer wants to report the performance of the different products that belong to a certain brand in a certain country but which is split in between different product categories and therefore is split in different Market Research databases.
    Questions like "What is the Market Share of the brand in this country?" or "Who are the main competitors and how are they performing?" can be answered with this reporting.
  4. Sales performance in one country but across retailers
    e.g. the manufacturer wants to analyze the sales performance of the different retailers in Canada and compare their Sell-Out data with his own Sell-In data (e.g. Shipments). For that, a combination and harmonization of the various retailers' POS data with his own ERP sales data in Canada is needed.

Challenges of external master data harmonization
Different master data keys and number ranges are usually used by different external (e.g. Retailers, Syndicated Data Providers) and internal (e.g. ERP) sources. Furthermore, GTINs (Global Trade Item Number) provided by these sources might not always be correct or are even not provided at all. What is missing in such a case is a "golden key" that links all data that is of the same kind (e.g. the same product or the same retailer store) to one single data set which exclusively identifies the product, no matter in which data source it appeared and independent from its description, GTIN or ID that was provided by the different sources.

How it is done in SAP Demand Signal Management
The data harmonization within SAP Demand Signal Management determines which products belong together by creating a GPID (Global Product Identifier) and linking all the different external numbers representing a single manufacturer product together. This enables a holistic mapping of all external master data against internal master data coming from the manufacturers ERP system, for example. Moreover, the manufacturer can now compare different internal and external views on data from different sources based on a single product or loc ation.
More information can be found here: http://help.sap.com/saphelp_dsim100/helpdata/en/c5/361326822249afa7de3f4a12b93439/frameset.htm

Jun 16, 2014

Real-Time Reporting


Real-time reporting is one of the important issues for an organization, especially, online reporting on very sensitive data, for example sales and distributions etc.
There are several standard options in SAP BW for online (real time or almost real time) reporting.
In the first part of the article I will give a briefly explanation of some standard methods, like RDA or Virtual Cube, pointing on disadvantages of these methods.
In the second part of the article I will show another, combined, method which I use for some online reports.

RDA and Virtual Cube
 
There are two general ways, the most popular, for online/near online reporting:
  1. RDA (Real Data Acquisition)
  2. Virtual Cube
Eac h of them has advantages and disadvantages.
Beside the main advantage of these methods, which is online reporting, there are a lot of disadvantages.
I guess that these disadvantages, which will be described later, may "kill" the whole online concept and, actually, have more weight on possible advantages of the methods.

RDA

First, actually, this is not really real time, but almost real time (RDA daemon might have minimum one minute interval). 
In addition, small time intervals between loads may lead (and surely will lead) to overlapping loads.
Therefore loading using RDA with one minute interval in "hot" periods (the end of a quarter) does not seem to be working.

Virtual Cube

One of the main problems of using Virtual Cube is extremely poor performance. I guess most of ABAP and BASIS professionals won't accept an idea of performing direct RFC SELECT from VBAK (for example).
Such model might work on small volume of a data, but RDA as well as standard ETL processes work fine with small volume of data.
In addition, writing complex and performance optimized ABAP code for Virtual Cube based on function modules requires strong ABAP skills.

In conclusion, I guess implementing online reporting model on big volume of a data, sales orders for example, is very complex and even impossible using RDA or Virtual Cube.

Combined Delta Method
 
I n the next part of the article I would like to suggest another, combined method for online reporting.
Suppose we have the next scenario:
  1. There is some standard ETL of sales order items (2LIS_11_VAITM standard extractor) which runs hourly. Each load brings about 5000 lines approx).
  2. Between hourly loads the data (delta) is collected in the delta queue in ECC.
As mentioned early RDA of Virtual Cube won't be useful in this case.
But what if I will bring just deltas from such delta queue using SAP standard function module without resetting delta queue and finally summarize the deltas with the existing records in a cube/DSO?
For example, sales order 123 has booked value 100 EUR and it was loaded to BW at 12:00 using standard hourly ETL.
At 12:15 sales order was updated to 120 EUR.
In this case we have an order with 100 EUR in BW and delta +20 in delta queue (till the next hourly loa d at 13:00).
If I would retrieve such delta value (+20), instead of retrieving whole data from VBAK or running RDA daemon each minute, and add it to the existing  value (100) just at the report level, on demand, so my report give me the most updated value - 120 EUR.
In this case we won't use a complex ABAP code or retrieving a data from huge tables such as VBAK.
In addition, we won't use RDA mechanism, but still getting online values - really online.
Last, but not the least, 2LIS_11_VAITM extractor does not support RDA without adjusting the extractor. In most organizations SD is very sensitive area, and getting an approval of principal level for changing such important extractor won't be easy mission.

Implementation

First of all, I assume that you have a cube (standard or not) which has been loaded every hour with 2LIS_11_VAITM extractor.
The first step is creating the function module for online extractor. 
Such function module should use SAP standard function module for reading delta queue RSFH_GET_DATA_SIMPLE with the following parameters (I mention here only an important part of the parameters): i_osource = '2LIS_11_VAITM' and i_updmode = 'D'.
For detailed implementation of the function module based extractor you suppose to turn to your ABAP team.
The extractor fetches those records which are currently in delta queue.
The next step is creating the cube which has a similar structure as a main cube loaded each hour.
This cube is a virtual cube based on DTP for direct access with an Info Source 3.X.
Finally we create a Multi Provider based on these 2 cubes (hourly and virtual).
A report built on such Multi Provider will show online data.

Conclusion

I've shown several standard options in SAP BW for online (real time or almost real time) reporting such as RDA and Virtual Cube.
Each of the options separately has very serious disadvantages such as RDA may lead to overlapping loads and Virtual Cube may have a performance troubles.
I've suggested another, combined method - using standard hourly loads and, on other hand, adding the records which are in delta queue fetched by SAP standard function module.
The result is online report with a minimal impact on performance, with very simple extractor based on function module.

Jun 9, 2014

BI 4.1 on BW 7.x Performance Improvements


The SAP BusinessObjects Business Intelligence Platform 4.1 includes significant performance benefits when one reports on SAP BW. These benefits are a great reason to upgrade for those of you using BW. Specifically there are enhancemets:

On report creation or edit with Web Intelligence or Crystal Reports for Enterprise documents which are based on BEx Queries.
  
On report refresh with Web Intelligence or Crystal Reports for Enterprise on BEx queries with variables.
  
On addition of a BEx Query to an Analysis for OLAP workspace
 



Web Intelligence documents based on BEx Queries via BICS

A big advantage here is that when you select a BEx query with mandatory variables which do not have default values, a variable screen (shown below) will display BEFORE the query panel. This will save some time.

webivar.png

Additionally, you should see a substantial performance gain for the report refresh portion once you've entered the variable values via this prompt window. Note that this particular gain is not seen when user- defined prompts are combined in a document with BEx variables.

View time also brings quicker rendering of the prompt window (screenshot below) as well as a quicker refresh once the mandatory prompt is entered for your end users.

54560748-58F3-4932-8FAC-796708DF3468.png

In addition, there is now a new "Design Time" API that has been implemented at design / edit time. This particular feature requires that your SAP BW server is updated to the level indicated at the bottom of this blog. This Design Time mode allows for the fetchin g of metadata and master data with far less RFC calls. Specifically, the new API is used when you select queries while designing a report.

How does that look in actual practice? Well as an example, when you select your query

QuerySelect.png
You'll see a lot less of the loading screen Loading.png

before the BEx queries available objects are displayed (shown below). The first query you select on a particular server will take more time than the rest due to the initial handshake / warm-up process. Afterwards, it should be very fast. You'll still get some of these benefits even if your BW server isn't of the version listed below.

QP.png


Crystal Reports for Enterprise

Report creation or modification on BEx Queries
   Simi lar to the workflow in Web Intelligence, Crystal Reports also shows a variable screen before the query panel when the query contains mandatory variables without default values. Due to this, the variable screen should load more quickly. This allows the query panel itself to load more quickly. Finally the report itself is refreshed more efficiently.


The report consumers will also see benefits at view time. The prompt screen (shown below) will render more quickly. In addition, there are improvements with the generation of lists of values in this variable screen. The gain increases as the list grows larger. Finally, the report should refresh more quickly.

Crystals.png



Analysis OLAP - selection of BEx Queries

The Open Data Source interface has changed slightly. It now opens to the Find tab instead of the Folders tab. This is helpful when there are a large number of queries on the system as you can immediately search without waiting for  the initial folder / query view to load.

AOLAP.png
FAQ:

What are the BW server requirements to take advantage of the Design Time improvements?

As mentioned earlier, you should still see improvements without the following in most areas. However, to get the maximum you must meet the following criteria (subject to change):

The BW backend needs the following service pack levels and SAP notes in order for the design time improvements to be enabled and fully functional:

BW 7.30 SP8 or BW 7.31 SP5+, plus all the SAP notes listed below.

For critical cases, there is also a collec tion of notes for 7.30 SP7 available (excluding the notes mentioned below, so you need to apply them as well).

Even better, the versions which already contain everything are 7.30 SP10, 7.31 SP08 and 7.40 SP05

Additional SAP Notes:

1771995 – Wrong hierarchy level
1750788 – Meta data extension (contains a collection of extensions and fixes)
1767351 – Missing meta data of characteristics in the fix filter - o nly relevant for characteristics that do not support drill down (i.e. are only in the fix filter of the query).
1776999 – Sorting type of hierarchies is always "HIERARCHY" although it was changed in the query designer
1777544 – Missing meta data of hierarchy node type attributes
1778347 – Hierarchy node type attributes are not read
1770434 – Correction for selection state of compound characteristics
1762156 – Correction for nodes in selection space
1776688 – Optimization for reading hierarchy nodes, too many hierarchy levels are read
1798297 – Correction for issue found on Samsung queries
1806813 – Correction for missing text for members used as default variable values
1809517 – Correction for invalid variable order when retrieving the list of variables through the design time services
1811124 – This note fixes the sorting of the of drill down characteristics returned by the design-time API so that it matches the runtime sorting
1812142 – This note fixes the sorting of the hierarchies returned by the design-time API so that it matches the runtime sorting
1817482 – This note adds the feature for the Design Time API to choose whether members s hould be read initially or not. By default, there are not read; this provides a performance enhancement for SL.

Jun 3, 2014

10 Best Modeling Strategies in SAP BW


I have divided modeling strategy into different categories. In part 1 I will start with modeling strategy for info cubes with 2 key points.
 
A. Info cube
 
1. Dimension vs. Fact table Ratio
 
As re commended by SAP and most of SAP BW consultants are aware of general rule that Dimension table should be within 15% of fact table size.  It is recommended for good performance.
But while designing a data model, how we would determine dimension's design to maintain a healthy dimension vs fact table ratio?
 
Below are some small tips which will help to achieve it.
 
- Keep the dimensions small.
- Check the Info cube design with sample data using tcode RSRV.
    RSRV→ All Elementary Tests →Database.
S1.png
Highlight the Database Information about InfoProvider Tables, right-click (context menu), and choose Select Test.
 
Based on test result adjust your dimension design.
 
Note: If you would like to add sample records without uploading from ECC or flat file, you can use Program "CUBE_SAMPLE_CREATE". It will provide ALV grid to input your sample records.
Caution: Use it ONLY in development or testing environment.
 

2. Navigational Attribute vs Dimension Info object

A great challenge when designing a data model is to decide whether to store data as a Characteristic in a dimension table (and therefore in the InfoCube) or as an attribute in a master data table and use it as Navigational attribute.

Navigational attributes instead of a simple dimensional attribute always introduce a performance penalty in terms of query execution.  It's suggested to avoid if possible activating a large number of navigational attributes and keep them only if there are business requirements.
 
Reason:
 
- The fact table contains one foreign key column per Info Cube dimension and a column per key figure of the Info Cube.
-The dimension table consists of a dimension id (DIMID) column which constitutes the primary key of the dimension plus a column per characteristic in that dimension. Those columns hold SID (surrogate id) values of the corresponding characteristic.
- In the third layer, there are SID-tables of the characteristics. This can be a standard S-table , containing only relationship between SID and characteristic key, an X-table (SID-key relationship plus SID columns per time-independent navigational attribute), or a Y-table (SID-key relationship, timestamp, SID columns per time-dependent navigational attribute).
- In the fourth layer, there are standard S-tables for navigational attributes.
Example:
 
Material Group as Navigational Attribute of Material
In this case during query execution, data will be read up to fourth layer as per below diagram.

S2.png

S3.png  

May 30, 2014

ABAP on HANA - enhance performance through code push down


With SAP Netweaver AS ABAP 7.4, I thought of a simple ABAP program to compare the performance between ABAP program consisting of classic SQL(multiple joins & currency conversion)  against the ABAP program calling HANA calculation view directly (classic SQL with joins & currency conversion logic converted into HANA calculation view).

  • ABAP with SQL embedded  – SQL query with multiple joins & currency conversion in ABAP program. vs
  • ABAP program calling HANA calculation view directly  a.k.a code push down to HANA DB.

You can manage ABAP program, modeling & ABAP profiling all in one place with SAP HANA studio. You need to install additional ABAP development plugins for eclipse and I used SAP HANA Studio version:  1.0.58.




Below is the screen I captured while profiling ABAP program from HANA studio. ABAP program uses classic SQL to fetch data from BKPF, BSEG (multiple joins) and perform currency conversion using standard function module. Actual database time that it took to execute ~ 24 seconds.

From the image below

Screen 1:
SQL statement navigated from hit list (Screen 3) shows more time consumed at DB during execution. SQL query from screen 1 is later converted into calculation view in HANA DB to improve the performance drastically.

Screen 2:
Snapshot from ABAP profilin g analysis done in HANA studio with database time: ~ 24seconds

Screen 3:
Complete Hit list of ABAP program showing BKPF SQL fetch and currency conversion (Perform CONV_CC) which consumed most of the time. Both the SQL statement and currency conversion is pushed down to calculation view for performance boost.

Screen 4:
Currency conversion code inside - Perform CONV_CC.



ABAP code optimization using HANA 24 seconds to 2 seconds:

SQL query & currency conversion is converted into HANA calculation view and called from SQL query as shown below: ZBC_BSEG_DOC is the dictionary view created from HANA calculation view.




Dictionary view: ZBC_BSEG_DOC

dic_view.jpg

HANA Calculation view:




HANA views can be accessed by standard SQL With ABAP 7.40 they are natively supported in ABAP
  • Mapping to DDIC types possible

ABAP profiling & performance after code push down to HANA

Screen 5: ABAP code optimized for HANA. All SQL joins & currency conversi on pushed down to HANA DB as calculation view CA_BSEG_DOC.

Screen 6:
Proxy ABAP dictionary view generated from HANA calculation view so it can be used in ABAP SQL.

Screen 7:
ABAP profiling snapshot showing approx. 2 seconds spent in database during execution compared to 24 seconds with classic SQL.

Screen 8:
HANA calculation view inside HANA DB.

Screen 9:
Hit list fr om ABAP profiling.

hana_optimized.jpg

May 27, 2014

How to group free characteristics in Portal or Web Analyzer


Introduction

                      We use several free characteristics in our reports for drill down purpose. At times, we are forced to use huge no. of drill down chars. It will be cumbersome to view all of them together in a single screen. There are chances for our users to get confused by looking all of them like below.

    Grp1.JPG
                  To deal this issue in a better way, I am going to demonstrate on How to group these free chars as per the entity type
in WAD by using Navigational Block Web Item.

1.     Open WAD and add different "Navigational Block" items to your web template
         
2.    If you want to group as per the Header Customer entity type, then click on the Navigational Block Web item from above and specify the Entity name in the Title like below

  Grp5.JPG

3.     You can select the required Chars and Nav Attrs in the  in Web Item properties like below.

Grp3.JPG

4.     You can create separate groups with different web items in the above fashion and your users can select the required drill down char f rom the Nav Block web item in the Portal like below

  Grp4.JPG

Note : You can correlate with Dimensions in your cube. But with above steps, you can also add Nav Attrs to the group along with dimension chars. You can create total new groups also without concerning the dimension design in the Cube.

May 20, 2014

0 records problem in Master Data load

Via Content in SCN

On a rainy noon of Friday faced strange type of problem while loading master data text in SAP APO server (EHP2 for SAP SCM 7.0).

Problem: There is one generic transaction data source for Master data which was coming from BW side. We have to load its data in different-different info objects like plant, division, sales organisation etc. by putting selection of particular info object in Info package. e.g. - Division
After schedule the Info package we were able to see the 76 records of division at PSA level. Then putting the same selection at DTP level
DTP+0+recorde.PNG
                     
After executing the DTP we was getting 0 records at manage level instead of 76 records for Division at PSA level.

Manage+0+recorde.PNG


Solution:

1. Check the data at PSA level,how it looks

Data in PSA.JPG
                         
2. At DTP level filter is same as data in PSA not as selection of info package(all upper case letter)

DTP+for+Division.PNG
                 
3. After executing the DTP we were able to got 76 records of division at info object manage level.

Manage+recordes+added.PNG

The solution is very simple but some times simple things do wonderful things.

Note: While doing master data load check Handle Duplicate records in update tab of DTP and activate master data after loading.

setting for master.PNG

Thanks for reading..

May 13, 2014

SAP BW/BO data flow and separation between front end and back end with the help of a iceberg


When I was a absolute beginner in the SAP world a while ago, the relations between the Data from the operative source systems, the BW system and the various BI visualization tools was not so simple explainable for me.

The dataflow from the source systems over the SAP BW and the Query Design to the Data visualization was for me a long time a black box.

To make the start for beginners into the world of SAP BI easier and to show the relations other colleagues from SAP ERP modules (e.g. FI, CO, SD, HR, etc.) and decision makers as primary users of BI in a transparent and easy way, some time ago I have designed the following figure:
 
BW_BO_iceberg.png

The aim of this figure is to simplified illustration of the data flow starting with the different possible source systems (e.g. ERP, CRM, 3rd party, XLS, etc.) over the data modelling and data management with respect to the LSA principles of Layered, Scalable Architecture (by Juergen Haupt as godfather) in the Data Warehouse (SAP NetWeaver Business Warehouse) and the Query creation in the BEx Query Designer to the data visualization with the tools of the SAP Business Explorer (SAP BEx) and BusinessObjects BI for SAP.

But above all, the picture is supposed to show the separation between BI front end and back end. The decision makers as the consumers of the reports always see only the tip of the iceberg where they use the frontend tools of BEx (Analyzer, Web Application Designer) and BO (SAP BusinessObjects Design Studio, SAP Lumira (formerly SAP Visual Intelligence), SAP BusinessObjects Analysis, edition for Microsoft Office, SAP Crystal Reports, SAP BusinessObjects Dashboards etc.).

The back end area begins (water surface) with the creation of BEx Queries. The Queries are based on the MultiProviders of the SAP BW system which bring data from different InfoCubes together. The data from the different source systems passing through the ETL-process (extraction, transformation, loading) and provided in a consolidated and adjusted form in the InfoCubes.

Ultimately, with the help of this figure should be underlined the „Extract once - deploy many" principle of the Corporate Information Factory (CIF) Resources by Bill Inmon, Inmon Data Systems.
Note: As mentioned in the beginning, these form of the iceberg figure should make an easy overview of the SAP BW/BO dataflow and the separation between front end and back end. Further possibilities, such as BW on HANA or operative Reporting in the ERP system are not taken in this figure.

May 6, 2014

Success Story: How SAP consulting helped a retail giant realize their HANA journey


While 2012 was all about closing BW on HANA deals, 2013 has been all about BW on HANA implementations. This article describes the journey of my recent BW on HANA success story at a retail customer in Nordics.

Customer Use case:
The customer has implemented SAP ERP and SAP BW systems. Recently, they have incorporated point-of-sale data analytics in the BW system. In brief, the use case was as follows:
8PM: Retail stores across the country closes.
9PM: Point-of-Sale (POS) data is ready to be loaded in the BW systems.
9PM-3AM: Data should be loaded in the BW system and KPI calculations need to be performed in the BW system.
3AM: BW system must be ready by this time to load the data back in the ERP system. This aggregated data with the KPI calculations is then used in the ERP system to execute more application logic.
7AM: All the processing must be complete by this time in the ERP system so that store managers have relevant information available before sto res open.

Challenges
The challenge with the above timeline is that there is only a six hour window to:
  1. Get the data transferred from the SAP ERP system to the BW system.
  2. The process chains in the BW system must be run where the data is loaded from the PSA to DSO and Infocubes and propagated through various complex application logic layers.
  3. Various KPI calculations are performed in the BW layer issuing many look-ups
  4. Once the calculations are performed, the data must be fed back to the source system.
As the entire chain depends to getting the KPIs from the BW system, if the load fails or the processing takes too much time, the store managers won't have the needed information when stores open the following day.

Set up and Performance Results:
We installed BW on HANA and connected it to the source ERP system. There was an existing BW on a traditional DB (BW on ODB) system already existing. This arrangement made it possible to load the data in parallel in the two BW systems and compare the performance numbers.

DSO Activation Times:
We loaded same number of records on both the systems and ran the process chains and recorded the below numbers:

BEX Query Response times:
During the realization, our BW on HANA expert found possibilities to optimize customer's current ABAP code and BW modeling. Just with these optimizations, customer may roll out their existing implementation to a nother smaller market without any issues in near future, though they would need HANA as soon as they want to include bigger markets.

Conclusion:
As proven during the performance tests, there were huge gains in DSO activation times. Usually, in a multi-layer architecture, DSOs feed other DSOs, which was also the case here. The activation gains for each DSO got multiplied and this resulted in great performance improvements.
We also demonstrated loading more than 600 million records in the BW on HANA system without any issues with only 3 parallel jobs. By this we showed that, the system can handle heavy loads without any issues.

Next step at the customer would be to bring in data in the the BW on HANA system from more countries and be able to run the process chains in the BW system with hundreds of millions of rows empowering the store manager more and more.

Apr 29, 2014

Useful ways to format Bex results in Webi


Running webi reports off of Bex queries can present interesting results due to the nature of using a relational tool to consume OLAP results. Here are some useful formulas/tips to help present Bex results in a cleaner manner.


Remove Values using SUBSTR():
A SUBSTR() formula in Webi is used to strip out unnecessary details from a particular field. A classic example is when dealing with compounded infoobjects. For example when there are reports created for financial and accou nting information a report user is interested in seeing the G/L Account number. However G/L Account Number is compounded with the company code that is configured in the backend. The account number is shows up in the following format CODE/number.

Lets say the G/L Account Number is COMP/13550563 but the user is only interested in seeing 13550563. We can use the substr formula.

=Substr([FIELD];6;8)

Substitute [FIELD] with your object. This formula says we will begin our field at the 6th character by ignoring t he first 5 which is COMP/ and the 8 in the formula says G/L account numbers are always 8 characters long and we need to display all 8. It is recommended you look at the actual Infoobject data length to determine the length of the object.

Remove/Replace Values using REPLACE():
We could have removed the company code from the above example using a REPLACE() formula as well if we are sure that our company code will always be the same. If the G/L account number shows up as COMP/13550563 we can write the replace formula as follows:

=Replace([FIELD];" COMP/";"")

This formula now strips out the unwanted COMP/ value from our G/L account number. This formula is also useful to remove additional details from timestamps and any details from an address field. This can also be used to replace existing data with data that might make more semantic sense to the user. For example changing a meaningless 'X' flag value to either YES or NO.

Create Custom Number Formats:
Different clients have different needs on how they view numbers and dollars on their reports. Custom number formats can easily be created in Webi. When yo u go into format for a number you can define your 'custom' format. Here is a sample for a standard accounting format where thousands are separated by a comma, null values are treated as zeroes and negative values are treated in RED and contained in parenthesis. All numbers follow a 2 decimal notation.

POSITIVE = 0,.00
NEGATIVE = (0,.00)[RED]
EQUAL TO ZERO = 0.00
UNDEFINED = 0.00

The comma allows us to seprate the thousands indicators. To apply to integers remove the decimal.

Use system variables to provide additional meta-data
There are a variety of formulas that are available that call the system specific fields. For example you may want to display the last date the report was executed. You can achieve it using  LastExecutionDate(). CurrentUser() gives you the username of the person accessing the report currently. These become more powerful when you use it along with CurrentDate() and CurrentTime()

Calculate Days between two dates:
It takes a significant amount of work to calculate the number of days between two date ranges in Bex but Webi makes it very easy for us to acco mplish the same. Suppose we want to find the number of days between our Transaction Date and our Posting date simply create a new variable of type Detail and use the DaysBetween() formula to calculate the number of days between the range.

=DaysBetween([Activity_Date];[Posting_Date])

Counting Members:
Webi also makes it very easy to create different counters. Suppose we want to compare number of business partners and the number of contract accounts it is very easy to accomplish by using the Count() formula. Create a variable of type measure and use the formulas as such:

=Count([Business Partner])

You can count different dimensions for a good comparative analysis.

Nest Concatenation() to present free text:
Webi contains a Concatenation() formula to join two strings however it is limited to only joining two strings at a time. This formula becomes really useful if you can nest them inside each other. For example lets say we want to display a free text from CRM on the Webi report but the free text is split into 4 fields due to BW's limit of 60 characters per infoobject of type CHAR. The free text is split into FIELD1, FIELD2, FIELD3, and FIELD4. In order to proper nest the concatenations remember this quick formula. If you wish to join 'N' number of fields then you need to use a total of 'N-1' Concatenation() formulas. In this case beause we want to join 4 fields we will need to use the Concatenation() 3 times.

=Concatenation(Concatenation(FIELD1;FIELD2);(Concatenation("FIELD3";"FIELD4")))