How to insert two queries into one BEx Analyzer workbook.

I frequently need to use data form different InfoProviders. Sometimes, instead creating MultiProvider, it is faster to put two or more queries into one workbook and create separate tab to display joined data. Here are 7 steps to create such a solution:

1. Create queries you would like to join.
2. Open one of the queries in BEx Analyzer and save it as a workbook.
3. Create two additional tabs in the workbook, and give them names (e.g., query2, results)
4. Edit the query2 tab by adding design items: click BEx Analyzer > Design Toolbar > Insert Analysis Grid
5. Click on the Properties dialog box, change Data Provider's name and click create button.
6. Choose the second query and confirm your choice.
7. Create a table, on the result tab, that merge data form both queries. Save the workbook.

What about selection screen? The variables related to the queries will be displayed on one selection screen, if you use the same variable in the queries - there will be only one field for the shared variables.

To learn more about analysis grid, read Analysis Grid section at sap.help.com.

Search sites about SAP BW using customer search

I created Search sites on SAP BW and Reporting, my search engine, focused on searching sites about SAP BW, using Google Customer Search. It shows results mainly form the sites: help.sap.com, sdn.sap.com, bwarea.com, sap.ittoolbox.com (BW group), sapfans.com (BW forum). If you think it is helpful, interesting or ... worthless - give me your feedback.

SAP BW Process Chains - automation and job scheduling

Would you like to automate repetitive BW tasks like loading transaction data? Create Process Chains!

Let's begin with basic definitions:

• Process chain (PC) is a sequence of processes linked together.
• Each Process have a type (BW activity, e.g., activate ODS) and a variant (contains parameters).
• Start process describes when the process will start (immediately, scheduled job, metaprocess, API).
• Connector is linking processes; you can choose 1 of 3 options of starting next process: when previous finishes with success (green arrow), failure (red) or always start the next process (black).
• Variant is a set of parameters passed to the process, such as the name of the InfoPackage to use for loading, the name of the InfoObject to perform a change run for.

Selected icon bar buttons:

• Planning view enables to create and modify processes.
• Checking view checks consistency of a process chain selected in plan view.
• Log view shows log of the execution of a process chain selected in plan view.

Selected left panel buttons:

• Process Chains displays process chains.
• Process Types displays process types, enable to drag and drop process into main panel.

The picture below shows simple PC (loading transaction data into ODS and than into InfoCube).

Loading Hierarchy using Process Chain

Now, we will describe how to load a hierarchy into an InfoObject. The process will start every day at 1PM. The process chain will have the following processes:

Start >  Load Hierarchy > Save Hierarchy > Attr. Change Run

1. Start transaction RSPC and choose Process Chain > Create. Enter a name of the chain and description. You will be asked to insert a name of start process - choose New, enter a variant name and description.

2. Select Direct Scheduling option and click Change Selection. Click Date/Time, enter schedule start date and hour (current date, 01:00:00). Click Period values > Daily > Save > Save > Save > Back. 

3. Click Process Types button on the left panel. Expand Load process and click twice Execute InfoPackage. Choose the InfoPackage for the Hierarchy you would like to load and confirm the choice. To connect the Start process with the load process: right click on the Start, choose process > Connect with > Load Data > created process. 

4. Add processes to save hierarchy and attributes change run (commits changes in the InfoObject).

5. Save the process. Click Checking View button. If the chain is OK, activate and schedule the chain by clicking Activate and then Schedule buttons.

Additional information

• To work with PC, you need authorization for authorization object S_RS_PC.
• To monitor selected processes create a list of PCs using TCode RSPCM. This tool will enable you to see statuses of selected PCs and provide a link to process chains' log.
  
• To have a PC that can be scheduled and maintained only in specified client - press Process Chain > Attributes > Editing Client, and enter the name of the selected client.
  
• To transport client dependant PC with complete starting options, enter required background user data in the target system using TCode RSTPRFC.
• If you transport PC with scheduling option: immediately the PC will start immediately after the transport.
• To "stop" scheduled PC click Execution > Remove from Schedule.
• To see overall PCs status - start BWCCMS tool.
• PC can send e-mail message when process failed. To create e-mail alert right click on the process and choose Maintain Message option.
• To see technical names and additional information about processes: click View > Detail View.

Examples of BW PC
Example of processes sequence when deleting overlapping requests form a InfoCube:
Start > Del. indexes > Load InfoCube > Delete Overlapping request > Gen. Indexes

Loading hierarchy, attributes and texts into InfoObjects:
Start > Load Attr. > Load Hierarchy > Save Hierarchy > Attr. Change Run > Load Texts

Additional resources:
Creating process chains (SAP Help)
Decision Makers in PC (SDN Article)
Create process chain - step by step tutorial (SDN Blog)
Presentation on Process Chains (SAP Library)

Transporting SAP BI queries

In this post I present basic steps to transport SAP BI queries from development to quality server. The steps are performed in source and target system, so you need authorizations to release and import objects.

Source system
Start with transaction RSOR (Transport Connection), insert initial and target source system names using Conversion button (2) and choose grouping type (3). Select queries you would like to transport to target system and press Execute and than Transport objects (truck) button.

Release the change request to transport using SE10 transaction. Press Display, choose tasks and requests you would like to release and press the button with single truck (or F9). When both task and request have been released successfully, start transport in target system.

Target system
To import queries to quality system start STMS transaction > Import Overview (F5) > Display Import Queue. On the Import Queue screen select the request and press Import (truck with a small loading). Choose target client's number and press enter. The queries will be written to the target system.

Standard BEx Transport Request
When the request, you have released, was set as Standard BEx Transport Request, you need created a new standard request. If there is no standard request, nobody is able to process queries or workbooks on the system. When you try to do so, you will receive the error: The query could not be saved due to a problem in transport. BEx transport request is not available or not suitable.

To create a new request you need to press BEx and than Assign / Delete button, add the request and save the choice.

Now all new objects and modifications will be written to the chosen BEx transport request. For more information on the standard transport request see this note:194051.

Additional resource:
Transporting: role and objects.
Authorizations for change and transport: S_TRANSPRT and S_CTS_ADMI.

How to learn SAP Business Intelligence (SAP BW). Tips and ideas for beginners.

I was always looking for effective ways of finding information and learning SAP Business Intelligence. Here are my seven tips how to learn SAP BI faster and have more fun with it.

1. Decide what you are going to learn.
Create your personal list of topics that you are going to learn. The list below may help you as a starting point.

- architecture of SAP NetWeaver BW 7.0
- functions of SAP BW analysis tools
- creating query: key figures, characteristics, hierarchies, variables, properties, exceptions
- using BEx Analyzer and BEx Information Broadcaster
- BW objects: InfoObjects, InfoProviders, MultiProviders, InfoSources, ...
- SAP BW data flow and ETL
- using predefined objects from the SAP Business Content

Expand the points. Discuss the list with experienced colleagues and find the most important subjects for you. Create a mind map to organize and visualize the list. Decide what the next topic to learn is and when you will start to learn it.

2. Gather all important links and learning materials two clicks away
Create bookmarks to all most often used resources in your favorite browser. Create links to websites, folders and local files like .pdf, .doc and .ppt documents. Keep the list short enough to easily find the appropriate link.

3. Use search engines in your address bar
Define access key to two or three search engines you use most extensively. To add a keyword to Firefox browser enter the site with the search engine, right click on the search form, choose Add a Keyword for this Search... choose a name, keyword and confirm the choice. Now you can use the search engine by entering the key and then your search terms in the address bar. I use keywords to SAP Forums, SAP Help and to this blog

To search data loading in this blog, I enter: bwa data loading in my Firefox address bar.

4. Learn by using multiple channels
Learning in multiple ways create more interconnection in your brain, is more efficient and effective. So listen to podcasts, read articles and blogs, discuss topics on-line and with your co-workers. Take notes, draw pictures and mind maps. All of this engage different senses and improve your memory. Taking tests will also help you to improve your learning. Find on-line tests and certification books to organize your knowledge.

5. Practice
You may gain practical experience by performing step-by-step scenarios and how to guides. The more exercise you do the more practical solutions you will have. If you do not have an access to a training system, you can find some commercial access.

6. Teach what you have learned to another person
Teaching is one of the best ways of learning. You remember and understand the subjects you have presented or explained to someone else much better then other topics. Start by describing what you have learned on SAP BW to your colleagues, creating a presentation and answering on-line questions.

7. Start a blog
Share your knowledge by writing a blog. Not like this one, but professional one. Start small, grow and have fun with sharing your experience! My favorite sites about writing for the web are A List Apart and SitePoint. Popular publishing platform that can help you to start blogging: Blogger, WordPress, TypePad.

Loading plan and actual data into the same InfoCube

Today, I had a discussion with my colleagues, about loading plan and actual financial data into the same InfoCube. The process seems to be easy, but update mode and aggregation issues make me consider different way of loading such a data.

The actual data need to be loaded with delta update mode, plan data (e.g., 0VTYPE 20) can only use full update and the previous load need to be deleted to avoid duplicated data. I consider 3 loading options:

1. Automatic removal of the previous load after data loading using InfoPackage.

To use this option we need to modify the InfoPakage that is used to load data. In the DataTarget tab we choose Automatic Loading of Similar/Identical Requests from the InfoCube and then Delete Existing Requests, Full or Initial Requests, Same or More Comprehensive.

Now we can schedule the job or add the load to a process chain. After the update, Same or More Comprehensive requests will be deleted.

2. Delete previous load in the process chain.

The second option is to build a process chain (TCode: RSPC) to load data and add deletion of the previous load of plan data using Delete Overlapping Requests from InfoCube option.

3. Remove previous data via ABAP program using DELETE_FACTS.

This option requires including in a process chain an ABAP program that performs selective deletion of the data (TCode to generate the program: DELETE_FACTS).

I must also take into consideration data aggregation. If I have both actual and plan data in one InfoCube I can not aggregate the Cube. One of the option is to create two InfoCubes, the first for actual data (delta update) and the second for plan data (full update), and aggregate the first one. The second option is to remove plan data from our InfoCube before aggregating the InfoCube. I think I will choose the option with two InfoProviders because it appears to be more flexible to maintain.

What is your experience with loading plan and actual data? Which loading option do you prefer and how do you solve the aggregation issue? Feel free to comment the post and share your opinion with us.

Note: The overall issue is related not only with plan values but also with down payments, overall plan values, variances and accrual calculations. For these values only a full update is possible.

SAP BI Library - January 2009

I've just found the complete SAP BI 7.0 Library - one of the largest document on SAP BI I've ever seen. It is PDF file, 3656 pages, 20MB. Most of the information contained in the file is available in the business intelligence section on the help.sap.com. The file was updated in January 2009. Enjoy reading!