SAP AMERICA
Moderator: Lothar Schubert
February 20, 2003/10:00 a.m. CST
Page 1
SAP AMERICA
February 20, 2003
10:00 a.m. CST
(Accents made transcription difficult.)
CoordinatorGood morning, and welcome to the SAP Labs BW Know-How Network conference call. All participants will be able to listen only until the question and answer session of today’s call. The call is being recorded. If you have any objections, you may disconnect at this time.
I would now like to introduce your leader, Mr. Lothar Schubert.
L. SchubertHello, everybody. My name is Lothar Schubert. I am the manager of the BW RIG in the U.S. It’s my pleasure today to introduce the main speaker for today’s BW Know-How Network call.
Some people are new on this call. , BW Know-How Network calls are bi-weekly calls, which are held by SAP on selected topics around BW. They are typically not for the beginner, but for the advanced person working with BW. … calls can be downloaded from the SAP Marketplace, the BW area, under Know-How Network. For two weeks after the call the call transcripts are made available as well on the SAP Marketplace and can be downloaded from there as well.
Today’s topic of … call is going to be Selected Aspects of SAP BW 3.0 OLAP Analysis. It’s going to be about a 30 to 40-minute presentation, and afterwards we will have a Q&A session. Today’s speaker is going to be Gerd Danner. Gerd is with the BW Regional Implementation Group of Europe. And, Gerd, now I want to hand it over to you.
G. DannerThank you, Lothar, for the introduction.
As Lothar said, the topic for today will be Selected Aspects of SAP BW 3.0 OLAP Analysis. The agenda for the call is as follows. We will have a look at pre-calculated value sets in BW. That’s a new feature with 3.0. Very similar to replacement of variable with query result that we already had in 2.0. I want to show you the little differences in this new method of handing over a query result to a variable value. Then we have a quick look at elimination of internal business volume. We will see the difference between BW elimination that the business consolidation system, BCS, provides, and have a look at constant selection with a little example of calculating a market share. Next topic will be the business case, slowly moving articles, especially showing you how we can display things that did not happen, or, for example, which customer did not buy any products in a specific period. And last topic for today will be showing you a slight improvement in currency translation that we have in BW 3.0.
So let’s start with pre-calculated value set. First slide on pre-calculated value set shows you what the preliminary step is. One of the reasons for pre-calculated value sets is you want to filter a characteristic in a query with characteristic values that you determine from another query. For example, you have a pre-query that shows you the top five customers with respect to number of ordered items. And the result, these top five customers you hand over to second query, and filter in the second query the sold-to party characteristic for these five characteristic values that are selected with the first query.
First is to create this pre-query. When you have created the pre-query,
we go to the next slide, You go to the back-end system, enter transaction RSA 1. That is the administrator workbench. You go to the reporting agent. In the reporting agent you say you want to pre-calculate a value set and you create a new setting. In there you have to specify what characteristics you want to create this pre-calculated value set for. In this case, the characteristic is the sold to party, so the customer that we sell our product to. Then we have to tell the system where it gets the values from for this pre-calculation setting. And in this case, this the query that is shown in the middle of the slide. If the query needs some mandatory variables for input, you can specify the variables with a variant, that is the empty space there. And when you save and activate the setting, the system tells you the name of the value set that can be seen in the lower right-hand part of the slide. You have to remember this name since you have to enter this name when you create a variable to access this value set. Then, as you usually do with the reporting agent, you create a scheduling package and you schedule the specific setting and you run it. Then it’s available for being used in reporting.
We go to the next slide, in order to use it in a query you create a variable for restricting characteristic sold-to party in that query. The processing, that’s important here, is set to user entry and default value. Then you go to the “variable represents” drop down box and you choose pre-calculated value set from there. You have to choose that a variable is mandatory and that it is not ready for input. On that screen you will have to enter the name of the value set that the system showed you in the reporting agent. That’s how you connect a characteristic variable within a query to a pre-calculated value set.
The big difference between replacing a variable with a query result that was already available in 2.0/2.1 with this new mechanism of a pre-calculated value set is that in this case the values are actually pre-calculated. In the old version, what we had in 2.0, the two queries were actually run sequentially when you wanted to see the query result, and you had to enter the select criteria for both queries one after the other. So there’s a big performance gain here, especially if you think about many users using this pre-calculated value set. You only pre-calculate it once, but you use it many times.
So let’s go to the next slide, elimination of internal business volume. We support multi-entity consolidation for multiple simultaneous hierarchies. We’ll see in later slides what that exactly means. The BW eliminates the key figure value, and the result is put on a special key figure that you have to define. This is definitely different from legal consolidation, which is part of SEM BCS or EC-CS. The big difference here, or the biggest difference that I personally see, in EC-CS or SEM BCS you actually get postings for the eliminations, postings that can be looked up by your auditor, for example, postings that have to be there for legal requirements. In BW this is all done on the fly, so there are no postings generated.
Pre-requisite for internal business volume elimination is that you need two InfoObjects, a sender and a receiver InfoObject. They have to share the same master data. Examples are sending cost center and the receiving cost center or business area and partner business area. Additionally, you need a special reference key figure, so a key figure that is defined with reference to another key figure. This stores the result of the elimination. You add it to the info cube, but actually the system does not store values for it in the database, but only includes this special key figure in the InfoCube’s structure.
Let’s go to the next slide, it shows you how to define the special key figure and how to define the elimination rules. So when you create a new key figure, on the first pop-up window you tell the system, “I want to create a key figure with a reference.” There, you have to define the source for your elimination. And the key figure you are currently defining is actually a destination for your elimination rule. If you define a key figure with reference you get a fourth tab strip in key figure maintenance called elimination. In there you can specify between which two characteristics you want to eliminate. So in this case we want to eliminate values from profit center to partner profit center. You could also specify more than one characteristic, and in this case specify whether it should be an AND condition, that is elimination of all characteristics pairs, or it should be an OR condition, which is elimination of each characteristic pair.
Let’s go to the next slide, which shows you an example. Let’s have a look at the example data records. There’s one data record marked with that big arrow.. Profit center PC 301, or with country UK. And partner profit center is PC 102 with partner country DE and a revenue value of $50. The following slide will then show you how to eliminate that according to hierarchies, such as you see on the bottom. The first hierarchy is a profit center hierarchy, with profit center 301 being in the PC 03 node. Profit center 102 in the PC 01 node. Note, and the second hierarchy is a country hierarchy with UK and DE both in the Europe node.
So let’s go to the next slide. This shows you some elimination examples. The first elimination is simple elimination by profit center. So the value of 50 that is posted for PC 301 is eliminated against the PC 102. PC 102 is in a completely different hierarchy node. The first common hierarchy node in the profit center hierarchy is the very top node. So you see in the right column that the value of 50 is only eliminated for this very top hierarchy node.
Next example, lower left-hand side, shows you elimination for country. Remember, UK and DE were within the same hierarchy node called Europe, so the value of 50 in this case is eliminated already with this hierarchy node, Europe.
Upper right-hand side shows you elimination for profit center or country. In this case, it’s eliminated—this is difficult now without showing you. On the UK and Europe and country node, it’s eliminated all because of the country rule. This is actually the reason why all these rows here are blank. If you look at the lower right-hand side, profit center and country, the first blank line is blank because of the profit center and because of the country node. The second blank line is also blank of profit center hierarchy node, which applies for that row and the Europe node. You remember the Europe node is already enough for the elimination of the value. If you have a closer look at this example after this call, you can probably very easily follow the example.
Let’s go to the next topic, constant selection. What is constant selection all about? In query design you select data that you want to show for the run time of the report. The problem is when you further navigate within the report, for example, when you filter within the report, the basis for some calculations might change, for example, the basis for percentage calculation, and in this case, the percentage value that you currently show also changes. But in some cases, for example, market share calculation, you always want to see the absolute market share of a specific product in a specific market, no matter whether you’ve currently filtered on some materials, on some products, or whether you don’t do that.
So let’s go to the next slide. It shows you the problem. If you define a market share analysis without constant selection, for example, using the percentage function %CT. If you filter for the materials, in this case Notebook, Speedy 1, Speedy 2 and terminal P400, the second column of the report changes, so the market share seems to change, also your only filter, and you don’t want to have that.
So let’s go to the next slide. How can we avoid that? We can avoid that by defining a selection in the query design as being constant. In this case, the selection, the key figure is sold products total. And in the query designer, within the context menu and properties of that selection, you define it as being constant, and on right-hand side you see how the market share in this case is defined.
Let’s go to the next slide. What happens is when you filter in this case the market share actually remains the same, so dynamic filters that you apply in query runtime don’t change the selections that you have defined to be constant in the query designer.
As another specialty, with constant selection, as shown on the next slide, in this business case we saw that we defined a whole selection as being constant. That is actually just an abbreviation for defining every single characteristic within that selection as being constant. You can also open the definition of that selection that is shown on this slide and choose an individual characteristic, in this case, the characteristic material, and only define this individual characteristic material as being constant selection. Then only filtering for that specific characteristic material wouldn’t change the value that is displayed in the report. All other filterings would change the value for that selection.
Let’s go to the next slide. Next topic is the business case, slowly moving articles. The business scenario on these is you want to display articles that weren’t sold at all or that were sold only in very low quantities. While the second one is easy, only low quantities, but the first one, displaying articles that weren’t sold at all, is quite a difficult task, showing things that did not happen, things that are not contained in your fact table. This can be solved with BW 3.0, using some new functionality. First thing is using the functionality info object as info provider. This functionality is basically that you can define an info object, master data of an info object, directly as an info provider that you can then use in the query designer to build master data queries upon. You can also use that info object as info provider in a multi-provider, and that’s what we’re going to do here, and use new functionality, “selection of constants”, and new functionality to “display values not posted”. We’ll see all that on later slides.
The last solution then is to create a condition to show the slow moving articles, so articles that were sold with a quantity that were less than ten.
Let’s go to the next slide. The next slide shows you the characteristic definition or InfoObject maintenance. In order to define a characteristic to be an InfoProvider you have to go to the master data and text tab strip. And in the lower part of that screen, you have to check that little checkbox, characteristic is InfoProvider, and you have to specify an info area name there. When you do that, save it and activate it, the characteristic is available in the query designer and it’s available in the admin workbench in the info providers tab.
Next slide, this tabular info provider automatically contains a key figure. That is called 1ROWCOUNT. It’s automatically visible. For example, if you open that info provider on the query side, you automatically have that key figure 1ROWCOUNT available, and it basically counts the number of rows in a tabular info provider. Examples, again, for tabular info providers are ODS objects and info objects, and the row counts as a key figure in the tabular info provider.
On the next slide we create a multi-provider. This MultiProvider consists to the multi-dimensional data provider containing our facts and the tabular InfoProvider with the master data. Make sure that on the identification button here we choose the right sources for this characteristic, so we have to actually choose it both from the basis info cube and from the tabular info provider.
Next slide, when you create a query on that multi-provider, while here we create a simple query, sold-to party in the rows, and open orders and value in statistical currency as key figures, and we also have this special key figure called number of records that comes from the tabular info provider, sold-to party. And when you run the query, shown on the next slide, you see what the result is of that query. On the upper right-hand side you more or less see already what you want to see. So, for example, the fourth row of the grid you see Thomas Bush, Inc., and that they haven’t bought anything in the periods. But the problem is as soon as you set a filter, and that is shown in the lower right-hand part, and this filter could also be a fixed filter in the query definition, and that is actually even a bigger problem, as soon as you do that, these rows actually vanish. I’ll now show you how we can avoid this problem. So let’s go to the next slide.
In the query designer for that specific key figure, number of records, you choose from the context menu the edit option. Then you go to the edit selection screen and you add the InfoProvider characteristic to the selections. The InfoProvider characteristic is a characteristic that automatically gets added for multi-providers in 3.0. You restrict that info provider to the name of the tabular info provider, to the sold-to party info provider, and in the context menu for the characteristic info provider, that’s the second right arrow here, you choose selection of constants and display values not posted, these two options.