Analyze geographic data with the BW Business Explorer Map functionality.
SAP BW has a great feature that allows you to analyze geographic data. For example, you can evaluate customer data starting at a regional view and then drill down to a state, county, or city view if you wish. The engine in BW that allows you to do this is the BEx Map within Business Explorer. It is part of the BEx Analyzer, and it aids users in the decision-making process by showing patterns and trends from a geographic perspective. Considering that 80 percent of all corporate and business data contains geographic components, this functionality can add significant value to the decision-making process.
The BEx Map requires geographic data to know what to plot. If this geographic data is not properly loaded, you will experience error messages when attaching maps to queries. To help you to avoid encountering error messages, I offer you the following steps1 that can be used as a checklist to ensure that your BEx Map is fully set up. I've also included a few tips that I've learned from working with the BEx Map that can save you from some common mistakes.
Step 1. Apply for the ESRI Software
Although the BEx Map is a standard BW component, you can't use it until you obtain the ArcView GIS software from ESRI. (ArcView 3.3 is used for versions up to BW 2.0B, and ArcView 8.2 is used with versions later than BW 3.0A.) Please see the Service Marketplace (https://service.sap.com/bw) folder Partner-OEM/Resell Partners-ESRI for more information.
ArcView is necessary for using the BEx Map. Your SAP BW installation packet should contain a voucher that you can redeem to get the software from ESRI. The installation packet also contains a CD developed by SAP and ESRI that contains a range of maps covering the world in various levels of detail. If these maps are not sufficient for your needs, you can apply directly to ESRI for additional detailed maps in ESRI shapefile format (https://gis.esri.com/intldist/contactint.cfm).
Step 2. Identify the InfoObjects (Characteristics) to Use for Mapping
You will be loading the geographic data from ESRI into the BW system. It is important to identify which InfoObjects will be used in your queries so that the geo-specific data is attached to the correct InfoObjects (i.e., the same InfoObjects that are used in your InfoCubes). For example, if you want to query by city, and the InfoObject in your InfoCube for city is 0CITY, then make sure you load your geographic data for city into the InfoObject 0CITY.
Step 3. Determine Whether the InfoObjects Will Be Static or Dynamic
A static geo-characteristic describes an area (polygon) for which the geographical coordinates do not often change. Countries or regions are examples of static geo-characteristics. A dynamic geo-characteristic describes a specific geographical location or point on a map for which the geographical information changes more often. Customers or plants are examples of dynamic geo-characteristics, because they are associated with a geographical point on a map as described by an address. This information is likely to change eventually.
It is important to identify the type of InfoObject so that the correct type of geo-specific data can be loaded (i.e., dynamic geo-characteristics will need specific latitude/longitude coordinates whereas static geo-characteristics would not). To tag static and dynamic InfoObjects, go to the InfoObject maintenance screen using transaction code RSD1. Click on the Business Explorer tab (Figure 1).
Figure 1
Identify InfoObjects as static or dynamic at this screen
If the InfoObject is a static geo-characteristic, you must download the shapefiles from the ESRI CD to a hard-disk drive that your BW system can access. These “shapefiles” define the geometry and the attributes of the static geo-characteristics and will be loaded into the BW system in a later step of this process.
If your InfoObject is a dynamic geo-characteristic, then you will need to assign latitude and longitude coordinates to the master data values. This process is referred to as geocoding.2
Step 4. Maintain the SAPBWKEY
You must match your master data values to the values in the delivered shapefiles (which are stored in a .dbs format). The shapefiles you receive from ESRI do not take into account your company's master data coding scheme. For example, for some companies the customer ACME would have a key value of ACME, whereas other companies would have a key value of customer 98844. This matching process is called maintaining the SAPBWKEY.3 To maintain the SAPBWKEY, you will use either ArcView GIS or other software that can edit dBase files (i.e., Microsoft Excel).
Step 5. Upload the Prepared Shapefiles to the BW System
You load these shapefiles from the same transaction that you used to mark the InfoObject as a geo-characteristic (RSD1) in step 3. Notice the button Upload shape files in Figure 1. When you press this button, BW prompts you to load three shapefiles for the InfoObject that is currently being edited, assuming that you have already made it a geo-characteristic. These files have the following extensions:
- .dbs – dBase file that saves the attributes or values of the characteristic
- .shp – saves the current geometry of the characteristic
- .shx – saves an index for the geometry
Tip! If you have loaded multiple versions of your shapefiles (i.e., you loaded the shapefiles before maintaining the SAPBWKEY, and then maintained the key and renamed and loaded the files), then be sure to delete the old files.
Now that the geo-specific data has been loaded, you are ready to create a Query and attach a map. In Figure 2, the static geo-characteristics are country and state (notice the distinct boundary lines). Color shading represents the total sales for each state (states in grey did not have any sales). Pink dots represent specific distributor locations; the geo-characteristic for distributor is dynamic as the distributor's location is subject to change.
Figure 2
Static and dynamic geo-characteristics
Tip!
Make sure your query displays only one geo-characteristic in the row at a time. (This is a system requirement and will help you to avoid error messages.) It is possible to have multiple geo-characteristics in the query, but drill down so that only one is in the row at any given drill-down step. One way to do this is with the Save Filter function.
Street-Level Geocoding
For many applications, geocoding to zip code or other administrative boundary files may be all that's needed. Typically, X-Y coordinates obtained from this level of granularity are based on the center of the zip code or other boundary. This may be adequate for some marketing or advertising applications, but may not be granular enough for other applications such as routing. For these applications, street-level geocoding may be needed. Here, X-Y coordinates are derived from an interpolation along street segments with known address ranges. The geocoding process uses the street-level addresses in its algorithms, not just the zip or other boundary code.
When downloading master data from BW, you can select which attributes of 0Customer you want to download, usually the Postcode attribute. You can also download street addresses if they are available. However, you will also need street-level spatial data with address ranges for both the left and the right sides of the street for use as a reference layer for the geocoding process. This data is available from a number of sources including ESRI Inc., Geographic Data Technology (GDT), and Tele Atlas North America.1
Once you have your street reference layer, the procedure for street-level geocoding is similar to that for postal-code geocoding except that you have more preferences and properties to set. For example, you can set spelling sensitivity and address match scores, side-of-street offsets for locating points in ArcMap, and output fields from the geocoding process such as the standardized address, which can be propagated back into BW for other uses such as mailing campaigns. Follow the instructions in the ArcCatalog and ArcMap user guides for ArcView version 8.x.
An important consideration is what coverage is available for the area you are interested in. For example, street-level data for the U.S. and Western Europe is readily available and relatively inexpensive. Street-level data for other parts of the world might be patchy or nonexistant. Furthermore, the address format and address scheme vary significantly throughout the world, which can greatly alter the geocoding algorithms from country to country. If your organization operates in many countries, street-level geocoding can become complicated and expensive.
Fortunately, things have improved recently. The major data vendors have targeted the largest and most active markets worldwide, greatly expanding coverage. To lower costs they have begun to offer worldwide geocoding as Web services, eliminating the need for an organization to buy, integrate, and maintain multiple street data sets and geocodes. Check out ESRI's Geography Network (https://www.geographynetwork.com/) for links to key sources of street-level data and geocoding services.
-Steve Benner, ESRI
1 The Web addresses for these companies are, respectively, https://www.esri.com, https://www.geographic.com, and https://www.na.teleatlas.com.
Again, I suggest that you download the “BEx Map 2.0b (Including Geocoding)” whitepaper, as it contains more detail than I can show here. The process for attaching maps to queries is also detailed in the whitepaper. I will go into more depth on this topic in the next issue of BW Expert. I will also show you how to set up a Web template for the BEx Map, and I will explain the relationship between maps and tables.
Figure 2
Static and dynamic geo-characteristics
Tip!
The system will only graph dimensional characteristics. Make the InfoObject that contains the geo-characteristic a main characteristic (not a navigational attribute).
1 These steps apply to all versions of SAP BW.
Lori Vanourek
Lori Vanourek started with SAP in 1996, after receiving her master’s degree in international business from the University of South Carolina, as a financial applications consultant focusing on the reporting and information analysis requirements of SAP’s customer base. She moved into the BW practice with its first release and has since specialized in the BW product, currently as a member of the BW product management team. Prior to joining SAP, Lori spent five years in the private sector as a financial analyst.
You may contact the author at lori.vanourek@sap.com.
If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.
