Aggregate Awareness in Universe

Lets see what is this aggregate awareness functionality and how it is useful in universe.

Lets say I have two tables customer and daily fact  with below data:

If I create the report of customers daily sales, it will join customer and daily fact table using cid column and will give me 24 rows of data.

But if my requirement is to create only quarterly sales or just monthly sales than the data will still traverse through 24 rows of daily fact table.

For eg.  Monthly sales will give me 12 rows of data while quarterly will be 4 rows of data. But this is still getting data from detail fact which has 24 rows, which if hold million facts can slow down the performance.

So we create materialised views or summary tables in our database for better performance.

And I created monthly and quarterly fact in my sample database. Below are the aggregated tables that I can use in my universe.

Now, the question is, we have aggregate tables but how to use the same in my universe based on user queries dynamically.

Aggregate awareness functionality will help me in achieving the same.

It is a two step process:

1    Use aggregate aware function in objects that needs to be made aggregate aware.
2       Setting incompatibilities of objects with tables in aggregate navigation section.

Below is the structure of my universe now:

Now, I have made time dimensions i.e. Sales Dt, Sales Month and Sales Qtr and measue Sales as aggregate aware.

Aggregate function has syntax like:

@Aggregate_Aware(sum(aggregate table1),...,sum(aggregate tableN)) Defines a measure object using precalculated aggregate tables.

 Where table1 is highly aggregated(in our case quarterly fact),........and tableN is the least aggregate or    detailed one(in our case daily fact)

If you observe the definition in the select statement of Sales measure here, it is from highly aggregated table to detail one , same is done for the dimension table as well.

Now month is present in only two tables, hence only monthly and daily fact is used.

Sales Qtr object, quarter is present in all three fact tables; hence all the three are used.

We have used aggregate function in these tables, so we hope that system automatically will decide which table to refer dynamically, when a user selects or tries to run a query.

That means if user wants daily data than daily fact should be used, if monthly than monthly fact or if quarterly sales than quarterly fact.

Now, we have to set the incompatibilities.

This is done from Aggregate Navigation section under tools, in menu bar.

We have to make objects incompatible to tables.

For eg. Sales dt is incompatible to quarter and months table, similarly Sales month object is incompatible to quarter table.

You have to click the table and check the box in front of object to make it incompatible with the selected table.

Lets see how the queries behave, when you select different set of objects:

Scenario 1:

I selected customer name and sales, and if you see the below query , it is behaving same as the one i expect it, as it is taking data from quarterly fact table.

Scenario 2:  I added sales month object to it, and will remove quarter object.(it should only now point to monthly facts)

And if you observe, it is now using monthly facts only.

Scenario3:  Using sales dt object.

This also worked correctly.

Note: if aggregate navigation have not been used than the measure sales will always point to quarterly fact, even if you use sales dt object in your query, which is wrong behavior.

Also it is required to link aggregate fact to dimension tables, else it would fetch Cartesian product for table which are not joined. In efashion universe the aggregate table are not joined to other because the table itself has all the dimension objects, which is not always the case. In real time aggregate fact will hold aggregate measures and foreign keys of dimension table only, that is why it has to be joined to dimension tables.

Difference between Break and Section in Webi report

What are Web Intelligence Sections?
In Business Objects XI Web Intelligence sections are easily created and often even by
mistake (drag a single object to the report and you will see what I mean). They can be
made more complex and wider (taller) with greater scope. Sections apply to the entire
horizontal area of page over which they are defined (best viewed/managed in Structure
View). In other words, you can have multiple tables, graphs, cells in a section if you want.
The section is also defined usually by a dimension so that each unique value of the dimension
 becomes an instance of the section in which all content placed in the section is repeated.
All of the content in that instance is filtered to that unique dimension value.  

So what are Breaks then?
A break is something that I rarely seem to get these days. No seriously, a Business Objects
WebI report break has similar properties to those of a section. It is a sort of pivot around each
distinct value of a dimension or formula. However, a break occurs only in a single table/block.
Breaks cause the table to be broken up in instances of the break, sub-tables usually. These are
easily created, usually by highlighting the desired dimension’s column and then right-clicking or
 section the top-menu icon.  

A Distinction between the Two
Sections can be applied and formatted to function more or less like breaks; however, breaks
can never function as sections do, they are limited to the scope of the block on which they are
defined. I tend to prefer sections as they allow for greater flexibility and they seem to have more
options. Nevertheless, breaks can be excellent for grouping relatively small amounts of data and
 then aggregating the measures at the bottom of the table.
  

Main Difference:

1.Section is made outside the block where as Break is made inside the block.
2.Section is available in Navigation Map where as Break is not visible in navigation map.
3.Section changes the format of table block where as break group the values within the table block
without changing the structure of the table block.
1.with sections you can “break” charts,with Braeks,only table. so if you want to “break” your
chart into groups you will have to use the section.
2.With section it is much easier to understand data in hierarchal view since the sections can be placed
under each other.
3. In sections you get an easy navigation through the map, in breaks you don’t.
4. In breaks however if you filter out a specific value in the break, the break will disappear, in sections,
 the section will stay, leaving the report with sections that you don’t really want to see since the section
value remains.
5. section’s navigation map actually exports to PDF, breaks have no such thing. By the way, breaks can
be nested hierarchically as well
6.Break functionality breaks up the data in a table or crosstab by grouping the data according to a
selected value and it allows you to display all the data for each value of a dimension variable together
mainly it displays SUBTOTALS. COLUMN BREAK’s are used to avoid the repetetive value occurences
Section is obtained in the Master/detail reports which enable you to split large blocks of data into
sections. This type of presentation allows you to minimize repeating values and to display subtotals

Ex: Creating a Break and a Section

When you create a break on Year, the data remains as one block. The year values are
repeated for each corresponding value of State and Sales Revenue.
When you create a section on Year, the data is no longer contained in one block, but divided
into sections. One section for each value of Year. Each section is a separate block

How to Combine Breaks, Sections, and Charts in your Business Objects Web Intelligence 
Document Breaks

In WebI, you apply a break to a single table or block of data. When you apply a break to a
dimension in a table, each unique value of the dimension is sub-grouped within the table.
This helps make the data easier to view and interpret. A break also inserts a blank row or
column after each sub-grouping, providing an area to insert aggregates, (Sum, Count, Min,
Max, etc.). You can configure the visibility of this blank row (footer) in the break properties.
Since Breaks are contained within a table, they have no effect on other objects within the
document such as charts or other blocks of data.
Breaks are part of the block or table on which they are applied. They only affect that block
and the objects that define that block. They are extremely useful for grouping limited amounts
of data with aggregated measures at the end of each grouping. Tables that contain hierarchically
related data are perfect candidates for using breaks to make the table easier to view and interpret.
In the example below, the table on the left is a simple table containing the columns Year,
Quarter, and Sales Revenue. The same table is shown on the right but becomes much clearer
when you apply a break on the Year column.





You can also nest Breaks within a table. When you apply multiple breaks within a table, the
data is grouped in the order in which the breaks are created. Typically this is done in a left-to-right
 fashion, resulting in a format that lends itself nicely to subtotaling values at multiple levels.

In the example below, the table on the left has a break on the year column. The same table
is shown on the right with a break on the Year then the Quarter Column.

To adjust the appearance of the breaks in a table, you can use the break properties to show or hide the
break headers and footers. You can also define how to show duplicate values: Display all, Display first,
Merge, or Repeat value on new page.

In the example below, the breaks on the Year and Quarter columns have the duplicate values set to
“Merge”.

To adjust the appearance of the breaks in the document when printed, you use the break property Page
Layout options. Each Break can be individually set to: Start on a new page, Avoid page breaks in block,
or Repeat header on every page. I would suggest changing these properties and viewing your document
in page mode to determine what makes your report look its best.

Sections

Sections are similar to breaks in that when a section is applied to a dimension in a table, each unique
value of the dimension is sub-grouped. However, while a break groups data inside the table, a section
 groups data outside the table, effectively sectioning the entire report. Unlike a break, a section does
not add a blank row after each sub-grouping. Since Sections effect the entire report, they DO effect on
other objects placed within the section such as charts or other blocks of data.

Sections can contain tables, charts sections, and other types of objects. Tables placed within sections
can contain breaks. All objects placed in a section are filtered to the unique value for that section and
are repeated in each Section of the document. Sections are used to easily display related tables and
charts filtered to the same value.

In the example below, the same simple table containing the columns Year, Quarter, and Sales Revenue
is shown with a section applied to the year column. Notice how the report is now divided into sections
representing each instance of the Year value.

In the example below, multiple related objects have been placed into the section on Year. Notice how all
objects are filtered and repeated in each section.

You can easily place a section within a section, first place a table below all other objects in the section, then
apply a section to a column in that table. In the example below a table was added to the section containing
the columns State, Quarter, and Sales Revenue with a section on State.

When you format the appearance of sections in the report, you can define whether to show or hide the
section and the sections background color or image.

To format the appearance of sections in the document when printed, you use the section Layout options.
Each section can be individually set to: Start on a new page, Repeat on every page, or Avoid page breaks.
Again, I would suggest changing these properties and viewing your document in page mode to determine
what makes your report look its best.

When you apply Sections to a WebI document, you also gain a couple of navigational benefits.  First, you
can use the Mapping tab in the Report Panel to navigate to each Section of the document by clicking on the
appropriate value.

Secondly, if the WebI document is exported to PDF, the Sections become bookmarks within the PDF so that
even when the document is offline and in a different format you still have the ability to navigate the report
more easily.

Many WebI developers make use of both design elements in order to achieve specific formatting needs. 
For example, you may wish to create a report sub-grouped with a Section on State that contains multiple
related tables, (Margin, Revenue, etc.). Each table in the report can then be sub-grouped by Year and
Quarter to provide subtotals using Breaks. Finally, to prepare the report for printing, the Section Page
Layout properties are set to start each Section on its own page.

                                                                                                                                                                                   Copied From Deepak Chaubey

Software Testing Knowledge

What is testing?

Testing is the process of evaluating a system or its component(s) with the intent to find that whether it satisfies the specified requirements or not. This activity results in the actual, expected and difference between their results. In simple words testing is executing a system in order to identify any gaps, errors or missing requirements in contrary to the actual desire or requirements.

According to ANSI/IEEE 1059 standard, Testing can be defined as “A process of analyzing a software item to detect the differences between existing and required conditions (that is defects/errors/bugs) and to evaluate the features of the software item”.

Who does  testing? 

It depends on the process and the associated stakeholders of the project(s). In the IT industry, large companies have a team with responsibilities to evaluate the developed software in the context of the given requirements. Moreover, developers also conduct testing which is called Unit Testing. In most cases, following professionals are involved in testing of a system within their respective capacities:

• Software Tester 
• Software Developer 
• Project Lead/Manager 
• End User

Difference between Testing and Debugging:

Testing: It involves the identification of bug/error/defect in the software without correcting it. Normally professionals with a Quality Assurance background are involved in the identification of bugs. Testing is performed in the testing phase.

Debugging: It involves identifying, isolating and fixing the problems/bug. Developers who code the software conduct debugging upon encountering an error in the code. Debugging is the part of White box or Unit Testing. Debugging can be performed in the development phase while conducting Unit Testing or in phases while fixing the reported bugs.

Testing Types:

Manual Testing: This type includes the testing of the Software manually i.e. without using any automated tool or any script. In this type the tester takes over the role of an end user and test the Software to identify any un-expected behavior or bug. There are different stages for manual testing like unit testing, Integration testing, System testing and User Acceptance testing.

Testers use test plan, test cases or test scenarios to test the Software to ensure the completeness of testing. Manual testing also includes exploratory testing as testers explore the software to identify errors in it.

Automation Testing: Automation testing which is also known as “Test Automation”, is when the tester writes scripts and uses another software to test the software. This process involves automation of a manual process. Automation Testing is used to re-run the test scenarios that were performed manually, quickly and repeatedly.
Apart from regression testing, Automation testing is also used to test the application from load, performance and stress point of view. It increases the test coverage; improve accuracy, saves time and money in comparison to manual testing.

How to Automate: Automation is done by using a supportive computer language like VB scripting and an automated software application. There are a lot of tools available which can be used to write automation scripts. Before mentioning the tools lets identify the process which can be used to automate the testing:

1. Identifying areas within a software for automation.
2. Selection of appropriate tool for Test automation.
3. Writing Test scripts.
4. Development of Test suits.
5. Execution of scripts
6. Create result reports.
7. Identify any potential bug or performance issue.

Following are the tools which can be used for Automation testing:

• HP Quick Test Professional
• Selenium
• IBM Rational Functional Tester
• SilkTest
• TestComplete
• Testing Anywhere
• WinRunner
• LaodRunner
• Visual Studio Test Professional
• WATIR

Testing Methods:

Black Box Testing:

The technique of testing without having any knowledge of the interior workings of the application is Black Box testing. The tester is oblivious to the system architecture and does not have access to the source code. Typically, when performing a black box test, a tester will interact with the system’s user interface by providing inputs and examining outputs without knowing how and where the inputs are worked upon.

Advantages: 
 Well suited and efficient for large code segments.
 Code Access not required.
 Clearly separates user’s perspective from the developer’s perspective through visibly defined roles.  Large numbers of moderately skilled testers can test the application with no knowledge of        
    implementation, programming language or operating systems.

Disadvantages:
 Limited Coverage since only a selected number of test scenarios are actually performed.
 Inefficient testing, due to the fact that the tester only has limited knowledge about an application.  Blind Coverage, since the tester cannot target specific code segments or error prone areas.
 The test cases are difficult to design.



White Box Testing:

White box testing is the detailed investigation of internal logic and structure of the code. White box testing is also called glass testing or open box testing. In order to perform white box testing on an application, the tester needs to possess knowledge of the internal working of the code. The tester needs to have a look inside the source code and find out which unit/chunk of the code is behaving inappropriately.

Advantages: As the tester has knowledge of the source code, it becomes very easy to find out which type of data can help in testing the application effectively.
 It helps in optimizing the code.
 Extra lines of code can be removed which can bring in hidden defects.
 Due to the tester's knowledge about the code, maximum coverage is attained during test scenario         writing.

Disadvantages:
 Due to the fact that a skilled tester is needed to perform white box testing, the costs are increased.  Sometimes it is impossible to look into every nook and corner to find out hidden errors that may          create problems as many paths will go untested.
 It is difficult to maintain white box testing as the use of specialized tools like code analyzers and         debugging tools are required.

Grey Box Testing


Grey Box testing is a technique to test the application with limited knowledge of the internal workings of an application. In software testing, the term “the more you know the better” carries a lot of weight when testing an application.

Mastering the domain of a system always gives the tester an edge over someone with limited domain knowledge. Unlike black box testing, where the tester only tests the application’s user interface, in grey box testing, the tester has access to design documents and the database. Having this knowledge, the tester is able to better prepare test data and test scenarios when making the test plan.

Advantages:
 Offers combined benefits of black box and white box testing wherever possible.
 Grey box testers don’t rely on the source code; instead they rely on interface definition and                 functional specifications.
 Based on the limited information available, a grey box tester can design excellent test scenarios            especially around communication protocols and data type handling.
 The test is done from the point of view of the user and not the designer.

Disadvantages:

 Since the access to source code is not available, the ability to go over the code and test coverage is      limited.
 The tests can be redundant if the software designer has already run a test case.
 Testing every possible input stream is unrealistic because it would take an unreasonable amount of      time; therefore, many program paths will go untested.

Levels Of Testing:

Levels of testing include the different methodologies that can be used while conducting Software Testing. Following are the main levels of Software Testing: 

• Functional Testing. 
• Non- functional Testing.

Functional Testing:

This is a type of black box testing that is based on the specifications of the software that is to be tested. The application is tested by providing input and then the results are examined that need to conform to the functionality it was intended for. Functional Testing of the software is conducted on a complete, integrated system to evaluate the system's compliance with its specified requirements. There are five steps that are involved when testing an application for functionality. 

 Step I - The determination of the functionality that the intended application is meant to perform. 

 Step II - The creation of test data based on the specifications of the application. 

 Step III - The output based on the test data and the specifications of the application. 

 Step IV - The writing of Test Scenarios and the execution of test cases. 

 Steps V - The comparison of actual and expected results based on the executed test cases. 

An effective testing practice will see the above steps applied to the testing policies of every organization and hence it will make sure that the organization maintains the strictest of standards when it comes to software quality.

Unit Testing: 
This type of testing is performed by the developers before the setup is handed over to the testing team to formally execute the test cases. Unit testing is performed by the respective developers on the individual units of source code assigned areas. The developers use test data that is separate from the test data of the quality assurance team.

The goal of unit testing is to isolate each part of the program and show that individual parts are correct in terms of requirements and functionality.

Limitations of Unit Testing
 Testing cannot catch each and every bug in an application. It is impossible to evaluate every execution path in every software application. The same is the case with unit testing. There is a limit to the number of scenarios and test data that the developer can use to verify the source code. So after he has exhausted all options there is no choice but to stop unit testing and merge the code segment with other units.

Integration Testing:

 The testing of combined parts of an application to determine if they function correctly together is Integration testing. There are two methods of doing Integration Testing
Bottom-up Integration testing and Top Down Integration testing.

 Bottom-up integration testing begins with unit testing, followed by tests of progressively higher-level combinations of units called modules or builds.
 Top-Down integration testing, the highest-level modules are tested first and progressively lower-level modules are tested after that. In a comprehensive software development environment, bottom-up testing is usually done first, followed by top-down testing.

System Testing:
This is the next level in the testing and tests the system as a whole. Once all the components are integrated, the application as a whole is tested rigorously to see that it meets Quality Standards. This type of testing is performed by a specialized testing team.

Why is System Testing so Important

 System Testing is the first step in the Software Development Life Cycle, where the application is tested as a whole.
 The application is tested thoroughly to verify that it meets the functional and technical specifications.
 The application is tested in an environment which is very close to the production environment where the application will be deployed.
 System Testing enables us to test, verify and validate both the business requirements as well as the Applications Architecture.

Regression Testing:

Whenever a change in a software application is made it is quite possible that other areas within the application have been affected by this change. To verify that a fixed bug hasn’t resulted in another functionality or business rule violation is Regression testing. The intent of Regression testing is to ensure that a change, such as a bug fix did not result in another fault being uncovered in the application.

Why is System Testing so Important

 Minimize the gaps in testing when an application with changes made has to be tested.
 Testing the new changes to verify that the change made did not affect any other area of the                  application.
 Mitigates Risks when regression testing is performed on the application.
 Test coverage is increased without compromising timelines.
 Increase speed to market the product.

Acceptance Testing

This is arguably the most importance type of testing as it is conducted by the Quality Assurance Team who will gauge whether the application meets the intended specifications and satisfies the client’s requirements. The QA team will have a set of pre written scenarios and Test Cases that will be used to test the application.

More ideas will be shared about the application and more tests can be performed on it to gauge its accuracy and the reasons why the project was initiated. Acceptance tests are not only intended to point out simple spelling mistakes, cosmetic errors or Interface gaps, but also to point out any bugs in the application that will result in system crashers or major errors in the application.

By performing acceptance tests on an application the testing team will deduce how the application will perform in production. There are also legal and contractual requirements for acceptance of the system.

Alpha Testing

This test is the first stage of testing and will be performed amongst the teams (developer and QA teams). Unit testing, integration testing and system testing when combined are known as alpha testing. During this phase, the following will be tested in the application:

 Spelling Mistakes
 Broken Links
 Cloudy Directions
 The Application will be tested on machines with the lowest specification to test loading times and any latency problems.

Beta Testing

This test is performed after Alpha testing has been successfully performed. In beta testing a sample of the intended audience tests the application. Beta testing is also known as pre-release testing. Beta test versions of software are ideally distributed to a wide audience on the Web, partly to give the program a "real-world" test and partly to provide a preview of the next release. In this phase the audience will be testing the following:

 Users will install, run the application and send their feedback to the project team.
 Typographical errors, confusing application flow, and even crashes.
 Getting the feedback, the project team can fix the problems before releasing the software to the            actual users.
 The more issues you fix that solve real user problems, the higher the quality of your application            will be.
 Having a higher-quality application when you release to the general public will increase customer       satisfaction.


Non-Functional Testing

This section is based upon the testing of the application from its non-functional attributes. Non-functional testing of Software involves testing the Software from the requirements which are non-functional in nature related but important a well such as performance, security, user interface etc. Some of the important and commonly used non-functional testing types are mentioned as follows.

Performance Testing:
It is mostly used to identify any bottlenecks or performance issues rather than finding the bugs in software. There are different causes which contribute in lowering the performance of software:

 Network delay.
 Client side processing.
 Database transaction processing.
 Load balancing between servers.
 Data rendering.

Performance testing is considered as one of the important and mandatory testing type in terms of following aspects:

 Speed (i.e. Response Time, data rendering and accessing)
 Capacity
 Stability
 Scalability

It can be either qualitative or quantitative testing activity and can be divided into different sub types such as Load testing and Stress testing.


Load Testing:

A process of testing the behavior of the Software by applying maximum load in terms of Software accessing and manipulating large input data. It can be done at both normal and peak load conditions. This type of testing identifies the maximum capacity of Software and its behavior at peak time.

Most of the time, Load testing is performed with the help of automated tools such as Load Runner, AppLoader, IBM Rational Performance Tester, Apache JMeter, Silk Performer, Visual Studio Load Test etc.

Virtual users (VUsers) are defined in the automated testing tool and the script is executed to verify the Load testing for the Software. The quantity of users can be increased or decreased concurrently or incrementally based upon the requirements.

Stress Testing

This testing type includes the testing of Software behavior under abnormal conditions. Taking away the resources, applying load beyond the actual load limit is Stress testing.

The main intent is to test the Software by applying the load to the system and taking over the resources used by the Software to identify the breaking point. This testing can be performed by testing different scenarios such as:

 Shutdown or restart of Network ports randomly.
 Turning the database on or off.
 Running different processes that consume resources such as CPU, Memory, server etc.

Usability Testing

It is a black box technique and is used to identify any error(s) and improvements in the Software by observing the users through their usage and operation.

Security Testing

Security testing involves the testing of Software in order to identify any flaws ad gaps from security and vulnerability point of view. Following are the main aspects which Security testing should ensure:

 Confidentiality.
 Integrity.
 Authentication.

 Availability.
 Authorization.
 Non-repudiation.
 Software is secure against known and unknown vulnerabilities.
 Software data is secure.
 Software is according to all security regulations.
 Input checking and validation.
 SQL insertion attacks.
 Injection flaws.
 Session management issues.
 Cross-site scripting attacks.
 Buffer overflows vulnerabilities.

 Directory traversal attacks.

Portability Testing

Portability testing includes the testing of Software with intend that it should be re-useable and can be moved from another Software as well. Following are the strategies that can be used for Portability testing.

 Transferred installed Software from one computer to another.

 Building executable (.exe) to run the Software on different platforms.

Portability testing can be considered as one of the sub parts of System testing, as this testing type includes the overall testing of Software with respect to its usage over different environments. Computer Hardware, Operating Systems and Browsers are the major focus of Portability testing. Following are some pre-conditions for Portability testing:

 Software should be designed and coded, keeping in mind Portability Requirements.
 Unit testing has been performed on the associated components.
 Integration testing has been performed.
 Test environment has been established.

Testing documentation

Testing documentation involves the documentation of artifacts which should be developed before or during the testing of Software. Documentation for Software testing helps in estimating the testing effort required, test coverage, requirement tracking/tracing etc. This section includes the description of some commonly used documented artifacts related to Software testing such as:

• Test Plan
• Test Scenario
• Test Case
• Requirement Tracability Matrix (RTM)

Differences Between DeskI and WebI

What are all the differences between Desktop Intelligence (Deski) and Web Intelligence (Webi)? The question has been asked so many times, including from clients, that I decided to attempt to put together a comprehensive list of the differences. Why is such a list important? Well, with the release of SAP BusinessObjects 4.0 in September 2011, Desktop Intelligence is no longer available, and is now completely replaced by Web Intelligence. So, for organizations that use Desktop Intelligence, it’s important to know what those differences are, as they transition from Deski to Webi.

The differences can be divided into two categories:

• Deski features that are missing from Webi
• Webi features that never existed in Deski

Fortunately, that first category is getting shorter with each new release of Webi. However, since that category is most important to those migrating from Deski to Webi, we’ll cover that category first.

Deski Features that are Missing from Webi

• Display a table in multiple Columns: In Deski, you can set a table so that, when it reaches the bottom of a page, it “wraps” to the next column on the same page, similar to a how a newspaper column continues on the same page.

• Grouping button: Deski has the ability to create grouped variables with the click of a button. In Webi, we have to write the If – Then – Else logic manually to create such functionality. This can be quite tedious. I have submitted an idea to SAP to have this functionality added to Webi. You can vote for this idea here.

• Freehand SQL queries: I’ve never been a big fan of this, as it defeats the whole purpose of having universes, and also creates maintenance nightmares. But there is some value for it in a test environment. This idea has been submitted to SAP here, and it is currently under review.

• Save as RTF, TXT, HTML: Currently, Webi can save as Excel, PDF, or Webi only.

• Edit List of values: In Deski, users could edit a List of Values for a universe object, so that it would only display the values that are valuable to them.

• List of values in formula editor: In Deski, when creating a formula, you can select values for a particular object, from a list, provided in the formula editor. This idea has been accepted by SAP, and should appear in a future version of Webi.

• Center on page: Deski has a Page Layout option to center a block, or cell, on the page, between the margins.

• VBA Macros: Deski support Visual Basic macros. I don’t know if Webi ever will support this, but I suspect it will support some kind of programming language in the future.

• Delete multiple tabs simultaneously: This seems like such a simple thing, but Webi still doesn’t have this capability.

• Export multiple documents simultaneously: That’s right. In Webi, we can only export documents to the repository one at a time.

• Create a variable in it’s current context: In Deski, we could create a variable from a formula, and lock in the current context of the formula, even if it was not explicitly included in the formula.

• Support for all installed fonts: Webi uses it’s own font library, not the Windows fonts. So, unlike Deski, we can’t add additional fonts to Webi. An idea has been submitted to this here.

• Schedule to a printer: I realize that printing is becoming more and more obsolete, but it isn’t there yet. Deski reports could be scheduled to print automatically. Webi reports cannot.

• Automatic custom sort for month names and weekday names: Deski has built in custom sorts for Months and Weekdays.

So there you have it. As far as I know, that’s a complete list of features that Webi is still missing from Deski. If you know of more, please mention them in the comments below.

Webi Features that never existed in Deski

• Track Data Changes: Allows you to see the difference between two different refreshes of a document, and compare the results.

• Hyperlinking Wizard: You can create an OpenDocument link in about 5 minutes, as opposed to the days or weeks spent doing this in Deski.

• Most built in formulas: Webi has more built-in formulas than Deski. These formulas come in the way of standalone cells that you can simply drag onto your report.

• Save as Excel with 65K+ rows: You can save a Webi document to the Excel 2007 format, which allows for over 1 million rows in a single worksheet.

• Add temporary values to custom sort: When creating a custom sort, you can add values that are missing, to the sort, so that, when they eventually show up on the report, they will sort in the correct order.

• Box chart: A new chart in Webi that allows you to see a measure at five different data points across a dimension.

• Tag cloud chart: A new chart in Webi that allows you to easily compare two measures against a single dimension.

• Heat map chart: A new chart in Webi. It’s the equivalent of a crosstab table, but in chart format.

• Bubble chart: A new chart in Webi that compares two measures against a single dimension, in an easy to read format.

• Optional Prompts: Prompts created in the query can be marked as optional, allowing users to ignore the prompts, if they wish.

• Display query summary on report: Webi has a QuerySummary function that allows you to easily display information about the query on the report.

• Inner join between queries: Webi’s Merge Dimension feature allows you to choose how the data is displayed on the report. It doesn’t have to be a full outer join, like in Deski,

• Query Drill: Allows you to keep your data set small, while drilling through large amounts of data.

• Drill in InfoView: Webi document, of course, can enter drill mode in InfoView (BI Launchpad). Deski documents cannot.

• Edit in InfoView: Webi documents can be easily edited in InfoView (BI Launchpad), by anyone who has permission.

• Alternate row shading: We used to have to do some slight-of-hand with variables to get this to work in Deski. But Webi has it built in.

• Input Controls: This allows for user interaction in the reports, making What-if analysis quite simple. It also make it easy to build powerful dashboards in Webi.

• Advanced formatting rules: That’s the new name for Alerters. Webi’s Formatting Rules editor allows for more powerful options, including using formulas in the rule.

• Publish as a BI Service: Individual blocks in Webi can be published as a Web Service that can be consumed by other BI applications.

• Compatible with Live Office: Report parts form Webi documents can be consumed by Live Office, allowing integration with MS Office.

• Compatible with BI Widgets: Webi report parts can be consumed by BI Widgets, allowing them to be dropped onto the desktop as standalone objects.

• BEx queries: Webi can run queries directly against BEx queries.

• Query stripping: Allows for the automatic removal of objects from a query against an OLAP cube, that do not directly contribute to the report(s).

• Synchronize drill on report blocks: When drilling on one report block, other blocks on the same report can drill at the same time, staying in sync.
• the ability to edit the sql script in Webi 4.0. It is in the Query Editor of both the Rich Internet Application and the Rich Client – the View Script button

As far as I know, that’s everything that Webi offers over and above what Deski had. If you know of any others, feel free to mention them in the comments, below.

Copied from Michael.

INVENTORY MANAGEMENT (in BW)

Symptom

A query with non-cumulative key figures does not display the expected values. You want to find out whether the data in the cube is already wrong by using the transaction LISTCUBE.

Since non-cumulative key figures are calculated during query runtime (they are not physically stored in the fact tables) you don't know how to derive the query result from the data in the cube.

Other terms

inventory cubes, ncum, 0IC_C03, 2LIS_03_BX, 2LIS_03_BF, 2LIS_03_UM

Reason and Prerequisites

In order to be able to interpret query results correctly, it is very important to understand the basic concept of cubes with non-cumulative key figures. This note should help you to aquire this knowledge by providing sufficient technical background information.


CONTENTS

[I]   Definitions and Metadata
[II]  Process Flow
      [II.a] Example
      [II.b] Loading
      [II.c] Compression & Updating Reference Points
      [II.d] Calculation of Non-Cumulatives in a Query (Reporting)
      [II.e] Validity Table
[III] Stock Initialization & DTP
[IV]  Data Mart
[V]   Performance & Memory Consumption
[VI]  Analysis of Problems

• [I] Definitions and Metadata (sample key figure 0TOTALSTCK)

(A)Values of non-cumulative(ncum) key figures cannot be cumulated (summed up) in a meaningful way over time. The 'result' is caculated during query runtime, in the majority of cases the exception aggregation LAST is used.

(B)Key Figure Definition
In RSD1 you can see that 'normal' cumulative key figures are assigned to such a non-cumulative key figure which describe the inflow and outflow (or the value change (delta)). In the query the ncum key figure is then calculated (for the requested days) by taking the data given by the key figures 'Inflow' and 'Outflow'.
Example:
non-cumulative key figure 0TOTALSTCK:
outflow 0ISSTOTSTCK (cumulative key figure)
inflow  0RECTOTSTCK (cumulative key figure)

(C)Time Reference Characteristic
It's the most detailed time characteristic of the cube (often 0calday) and is used to determine the values of all other time characteristics.

(D)Fact Tables
If you use the key figure 0TOTALSTCK in an infocube, the actual fact tables will only contain colums for the cumulative inflow and outflow key figures 0ISSTOTSTCK and 0RECTOTSTCK. You will have no column for the ncum key figure 0TOTALSTCK.
The values are only calculated during query runtime and not stored in the fact tables! Thats why you can't find this key figure in the transaction LISTCUBE either.

(E)Reference Points
Technically, only non-cumulative changes (movements) and the stock refering to a certain point in time (so called reference points or markers) are stored in the fact tables. In order to distinguish between this two kind of values the technical infoobject 0RECORDTP (part of the package dimension) was introduced.
For movements we have 0RECORDTP=0, for the reference points 0RECORDTP is always equal to 1 and the time reference characteristic is set to 'infinity' (e.g 0CALDAY = 31.12.9999).
In case the cube is totally compressed and you are only interested in the current stock values of a certain material, the query can only read the marker and hasn't to carry out calculations using the inflow and outflow.
By default the transaction LISTCUBE only reads data records where 0RECORDTP=0! Hence, if you want to get the reference points displayed you need to restrict this infoobject explicitly to 1 (or interval [0,1]).

(F)Validity
As the values of non-cumulatives are computed by reading the reference points and the non-cumulative changes, it is in principle possible to compute a value for every point in time. To be able to do this calculation you need to know for which time interval you can calculate the non-cumulative. The minimum and maximum loaded values of the time reference characteristic are used as standard as the boundaries for the validity area.


(G)Online Help/ How to Paper
Please search for the term 'Non-Cumulatives' in the online help or directly execute the URL
http://help.sap.com/saphelp_nw70/helpdata/en/8f/da1640dc88e769e10000000a155106/frameset.htm

There is a "How to..." Paper for Inventory Management Scenarios in BI which can be found in SAP Developer Network (SDN), under https://www.sdn.sap.com/irj/sdn/howtoguides choose SAP NetWeaver 2004 -> Business Intelligence -> How to Handle Inventory Management Scenarios

• [II]Process Flow

In this chapter we always refer to a very simple example which should help to understand the process steps and definitions faster.

• [II.a] Example

STEP 1:
A stock initialization is performed on 06.05.2010 for the material A. The current stock value is 100 PC.
STEP 2:
For this material there are also movements for calday < 06.05.2010. Only the movements from the last 2 days before the initialization are extracted.
STEP 3:
A day after the stock initialization a new movement is posted and loaded into the cube. The current stock is then 90.
STEP 4: Compression of stock initialization
STEP 5: Compression of historical data
STEP 6: Compression of new movements
STEP 7: Running queries

relevant stock values in ERP(transaction MB5B):
03.05.2010:  40 PC
04.05.2010:  60 PC
05.05.2010: 100 PC
06.05.2010: 100 PC
07.05.2010:  90 PC
Please note that the stock for a certain day always includes already all changes of this day. Hence, by looking at the table above, we know that e.g. on the 05.05. 40 PC must have been added and that 'nothing' happened on the 06.05..

• [II.b] Loading

For inventory cubes there is a special loading process called stock initialization. It transfers the current non-cumulatives once from the source system to the BW system. With the initialization you are only able to post one initial value for each combination of characteristics of the data source. Afterwards you can start loading 'historical data' and new movements (changes) using a different data source:

With 2LIS_03_BX the initialization of the current stock is carried out.
With 2LIS_03_BF you can load the 'historical data' and the new data (movements)

For all data records of the stock initialization we have 0RECORDTP=1. They are called reference points (marker) and play an important role when the non-cumulative values are calculated during query runtime.
Since the stock initialization refers to a certain point in time we must distinguish between movements which happened before and after this moment. The former are called 'historical data', the latter are new movements. When the delta (not stock!) initialization is carried out, the historical data (back to a required date) can be loaded into the cube. (Its not allowed to have historical data and movements in one request..)

Its not mandatory to carry out a stock initialization (but it is e.g. necessary in case not all movements exist any more in the source system or if you don't want to load all movements because of the amount of data). If you skip this and load only movements the system assumes that the inital stock is zero for all materials. New markers are then created whenever requests are compressed with 'new' materials.
Please review also point [III]Stock Initialization & DTP.
The standard extractor for 2LIS_03_BX can also deliver records from the source system with stockvalues = 0. In order to activate this option you have to mark the flag "Zero stock to be transferred" in the transaction MCNB. Please be aware that this may lead to a considerable runtime increase.


                            EXAMPLE

STEP 1: Loading of stock initialization
RequestID 1000
initialization: using 2LIS_03_BX
record:
material A -> 100 PC (on 06.05.2010)

STEP 2: Loading of historical data
RequestID 2000
historical data: using 2LIS_03_BF
records:
material A -> 40 PC added on 05.05.2010
material A -> 20 PC added on 04.05.2010

STEP 3: Loading of new movements(deltas)
RequestID 3000
delta (new movement): using 2LIS_03_BF
record:
material A -> 10 PC removed on 07.05.2010


F-table:

requestid recordtp  calday      0ISSTOTSTCK   0RECTOTSTCK
------------------------------------------------------------
2000        0      04.05.2010                  20 PC
2000        0      05.05.2010                  40 PC
3000        0      07.05.2010     10 PC
1000        1      31.12.9999                  100

Please note that the marker refers to the calender day 31.12.9999 and that recordtp is equal to 1 !

E-table: still empty (we have not compressed yet)

• [II.c] Compression & Updating Reference Points

After the initialization the reference point represents the stock value for a certain material at the time of the initialization. If movements (not historical data) are loaded, the reference points get updated during compression of the requests if the flag 'No Marker Update' (RSA1 -> Manage->tab Collapse) is NOT set (this is the default setting). Hence, for a fully compressed cube, the reference point tells you the current stock value for a certain material.
*******************      Important      ****************
There is one very important exception regarding the update of the marker: if you compress historical data the reference points must not be adjusted !! Otherwise you get wrong data in the cube (since these movements are already included in the marker of the stock initialization)! Such inconsistencies cannot be repaired automatically, once the marker is wrong a reload of the data into the cube is necessary.
Please also note that the request with the historical data must be compressed since the query 'can't know' that these records shouldn't get added to the marker (see Example: STEP 7 point(A))!
*********************************************************

                            EXAMPLE

STEP 4: Compression of request 1000 (with marker update)

F-table after compression:

requestid recordtp  calday      0ISSTOTSTCK   0RECTOTSTCK
------------------------------------------------------------
2000        0      04.05.2010                  20 PC
2000        0      05.05.2010                  40 PC
3000        0      07.05.2010     10 PC


E-table after compression:

requestid recordtp  calday      0ISSTOTSTCK   0RECTOTSTCK
------------------------------------------------------------
   0        1      31.12.9999                  100


STEP 5: Compression of request 2000 Historical Data (WITHOUT! marker update)

F-table after compression:

requestid recordtp  calday      0ISSTOTSTCK   0RECTOTSTCK
------------------------------------------------------------
3000        0      07.05.2010     10 PC


E-table after compression:

requestid recordtp  calday      0ISSTOTSTCK   0RECTOTSTCK
------------------------------------------------------------
   0        0      04.05.2010                  20 PC
   0        0      05.05.2010                  40 PC
   0        1      31.12.9999                  100


STEP 6: Compression of request 3000 new deltas (with marker update)

F-table after compression: empty

E-table after compression:

requestid recordtp  calday      0ISSTOTSTCK   0RECTOTSTCK
------------------------------------------------------------
   0        0      04.05.2010                  20 PC
   0        0      05.05.2010                  40 PC
  0        0       07.05.2010     10 PC
   0        1      31.12.9999                  90


As we already know, the marker is defined in such a way that it refers to the date 31.12.9999. For inventory cubes this date is exclusively reserved for the marker, its not allowed to use this date for movements!

Even if you use 'No Marker Update = X' it may happen that the number of records with recordtp = 1 increase during compression. The reason is that the compression is adding marker records (its assumed that the stock value was zero before the change) for movements where no corresponding marker records exist (see also note 1795551)

• [II.d]Calculation of Non-Cumulatives in a Query (Reporting)

The query calculates the stock for the requested points in time(t) by using the movements(inflow and outflow) and the marker.
It starts with the calculation of the current stock value (CSV): [Marker + (total delta (change,movement) of all uncompressed requests)]:
CSV = [M + SUM(deltas with requestid>0)]
Then it calculates 'backwards' the stock value for the requested day(t):
Value(t) = CSV - SUM(deltas with time>t)


                              EXAMPLE

STEP 7: Running Queries

(A)query executed after STEP 4 was carried out:

0TOTALSTCK(on 04.05)  = [100 (marker in E-table) + 50(uncompressed requests)] + 10(movement 07.05) - 40(movement 05.05) = 120 PC

120 is wrong! The calculation of the marker is by 60 PCs too large since the query took into account the historical movements. Thats why you must always compress the request with the historical data records!

(B)query executed after STEP 5 was carried out:

0TOTALSTCK(on 04.05) = [100 (marker in E-table) - 10(uncompressed request)] + 10(movement 07.05) - 40(movement 05.05) = 60 PC

(C)query executed after STEP 6 was carried out:

0TOTALSTCK(on 07.05) = [90 (marker in E-table)]  = 90 PC
Please note that in this case (fully compressed, current stock requested) the calculation was very simple ( and fast). The query had only to read the marker.

• [II.e] Validity Table

By default the so called validity table contains only the time reference characteristic and the interval for which non-cumulative values are defined (and can be displayed in a query). The validity interval is automatically adjusted when new data is loaded into the cube (by default the minimal and the maximal value of the time reference characteristic of all entries which have been loaded into the infocube).
Please also see note 1501907 and the online help (see above).
It might be necessary to add an additional characteristic to the validity table, e.g if the validity interval should depend on the plant(or source system). Please only add such validity-determining characteristics if its really necessary since too many of them ( or a validity-determining characteristic with a lot of characteristic values) decreases the performance considerably.

The validity table has the following form /BIC/L"ICUBE". The query only takes into account data records where SID_0REQUID is equal to -1 (or -2). These records contain the relevant validity interval for the entire cube (which gets calculated by taking into account all request). You can change the validity independently of the data loaded into the cube, this can be done by using the transaction RSDV. In such a case the corresponding record in the validity table has then SID_0REQUID=-2.

If the cube is already filled then adaptation(remove/add validity objects) of the validity table is only possible with the report RSDG_CUBE_VALT_MODIFY.

If the validity table is inconsistent, then rebuilding is possible using the function module RSDV_VALID_RECREATE. For the parameter I_REBUILD_FROM_FACT you can choose between two values:

= space : recomputation of the values for request -1 by aggregation of all reporting relevant requests (by only reading of validity table itself)
= X  : recomputation, complete facttables must be read


                          EXAMPLE

In our example we would have the following entry in the L-table

SID_0REQUID  SID_0CALDAY_F SID_0CALDAY_T MODEFROM MODETO
--------------------------------------------------------
    -1        20100504      20100507

The record with SID_0REQUID=-1 is the relvant one for the OLAP engine. If we change this interval manually in the transaction RSDV to [04.05.2010 - 10.05.2010] the L-table gets an additional entry (with SID_0REQUID=-2):

SID_0REQUID  SID_0CALDAY_F SID_0CALDAY_T MODEFROM MODETO
--------------------------------------------------------
    -1        20100504      20100507
    -2           0          20100510              F

A query (without any restrictions) where the infoobject 0calday is drilled down displays now the following result:

calday          0TOTALSTCK
04.05.2010      60 PC
05.05.2010     100 PC
06.05.2010     100 PC
07.05.2010      90 PC
08.05.2010      90 PC  <-
09.05.2010      90 PC  <-
10.05.2010      90 PC  <-

Please note that for the days 08-10 (extended part) the value from the day 07 was taken.
In case you have in addition to 0calday e.g. the infoobject 0plant as an validity object, the processing of the query gets more complicated. Lets assume we have the following validities:

SID_0REQUID SID_0PLANT SID_0CALDAY_F SID_0CALDAY_T MODEFROM MODETO
-------------------------------------------------------------------
      -1        1         20100504    20100507
      -1        2         20100504    20100512

Please note that the validity intervals for the 2 plants are not identical. If non-cumulative key figures are aggregated across several validity characteristics it may be necessary to determine non-cumulative values outside the corresponding validity periods. For this the system determines a kind of superset of all involved validity periods, please see note 1501907 for more details to this. In case the validity object is drilled down, the system highlights the values which are outside the validity by setting of square brackets.
Lets discuss this again with the help of our example. We assume that the total stock is 75 PC for the plant 2 on the 10.05.2010. If we restrict a query to this day we get the following overall result:

0TOTALSTCK
165 PC

In case 0plant gets drilled down, the query displays the following

0PLANT  0TOTALSTCK
   1      [90]
   2       75
Result    165

Please note that the value 90 for plant 1 is actually outside of the validity and therefore displayed in brackets. If 0PLANT were restricted to the value 1 (hard filter under 'Characteristic Restrictions' which can't be changed during query navigation) the query wouldn't display the value in brackets but issue the message 'no applicable data found' (since an aggregation over different plants won't take place anyway).

Important Remark: The non-cumulative logic works only if no posted non-cumulative movements exist outside the validity interval! Please review note 1501907 for more details.

• [III] Stock Initialization & DTP

If you use a BW70 data source and a DTP in order to carry out the stock initialization the following points have to be fulfilled:

- for the data source the setting 'Opening Balance' has to be active (field name STOCKUPD of table ROOSOURCE(source system) or RSOLTPSOURCE(BW3x)/RSDS(BW7x), respectivelly)
- the data target has to be a cube (with non-cumulative key figures; NCUMTIM in table RSDCUBE filled)
- only then the DTP offers the Extraction Mode 'Initial Non-Cumulative for Non-Cumulative values' which must be chosen in order to get the time reference characteristic and 0recordtp filled correctly

• [IV] Data Mart in BW7x

If you want to use the data mart interface to update data from an inventory cube into another one (see review note 375098 for further details, procedure depends on the BW release!) please note the following:

-if both, the target and the source cube contain non-cumulative key figures (system checks whether the field NCUMTIM in table RSDCUBE is filled) the DTP offers the extraction mode 'Initial Non-Cumulative for Non-Cumulative values'. This mode has to be taken for the stock initialization. Then the system reads automatically all records with 0RECORDTP=1 from the source and updates the data records correctly into the target.

-the source cube must be completely compressed, otherwise data consistency cannot be guaranteed!

• [V] Performance & Memory Consumption

• As for all basis cubes it must be observed that as many requests as possible are compressed (see note 590370) For inventory cubes the system has to take into account all uncompressed request when the marker is calculated. This additionally contributes to a long runtime in case of uncompressed cubes.

• Please only use validity-determining characteristics if its really necessary since too many of them ( or a validity-determining characteristic with a lot of characteristic values) decreases the performance considerably. See also chapter [II.e].

• Its important to restrict the query as far as possible regarding time. This obviously improves the runtime by simply lowering the number of points in time for which ncum values have to be calculated. Keep in mind that less calculations during the query runtime also decrease memory requirements.

• [VI] Analysis of Problems

In case its necessary to look at a non_cumulative value in a query more closely, you could follow the step-by-step description below:

1) Simplify query: this helps a lot to be fast and keep track of things

• identify a material and set a corresponding filter

• add 0CALDAY (or the time reference characteristic) to the free characteristics

• add time filters and focus on one certain point in time

• if necessary add validity characteristics to the drill down

• take all 3 relevant key figures (0TOTALSTCK, 0RECTOTSTCK, 0ISSTOTSTCK) in the structure (no restriced/calculated key figures)

2) Compare query result with LISTCUBE

• under 'field selections for output' choose material, the time reference characteristic, validity char., requestID, recordtp, 0isstotstck, 0rectotstck

• restrict material to the chosen value

• display the marker (0RECORDTP = 1) and remember the value

• display the deltas 0RECORDTP = 0)and sort by time

• compare if cumulative key figures in query match the values in LISTCUBE (deltas have been posted ok)

• check how many records aren't already compressed (not added to the marker) and hence need to be used in the calculation of the current stock

• calculate the marker and check for correctness

• calculate the stock for requested point in time (see point [IV])

3a) if query and LISTCUBE results don't match, please search for relevant notes under the component BW-BEX-OT-NC. In case no note can be found, please contact SAP support with the technical name of the simplified query and the corresponding LISTCUBE report(see note 1479893).

3b) if query and LISTCUBE results match, it means that the data is already wrong in the cube - continue with 4)

4) check PSA tables for the data sources 2LIS_03_BX, 2LIS_03_BF and 2LIS_03_UM: do the values correspond with the LISTCUBE output?

5a) if no, the data extraction from the source system has to be analyzed in detail. The corresponding component is BW-BCT-MM-IM (if the delivered data sources 2LIS_03_BF and 2LIS_03_BX are used), see also note 586163 in order to get more background information.

5b) if yes, continue with 6a and 6b.

6) typical issues:

• a) Did you compress the requests correctly with or without marker update ? In case a request was compressed with 'no marker update' you should find the following message in the SLG1 log: Loading of historical data switched on

• b) Did you carry out the stock initialization in a dtp with the extraction mode 'Initialize Non-cumulatives'?

• c) In case the query diplays no values at all please check the validity table

• d) Do you have characteristics in the drilldown that were not loaded by the data source 2LIS_03_BX (e.g. storage location)? Please see note 589024 for further details.

• e) Please pay attention to the order of the loaded requests. At first the initialization needs to be done (data source 2LIS_03_BX), then the movements can be loaded. Please see note 1795551 for further details.