BW is Dead – Long Live BW.

Learnings on SAP BI Analysis Process Designer APD to extract a query result to a file

While there are several limitations on the extraction process from a query to a file, the benefit is that you get a result extracted quite fast as compared with some other methods. Once valid concern is that if the query results in a large dataset you can face issues, but there are some workarounds to overcome that limitation, such a splitting into several APDs or setting the query element to divide the data collection into Packages, in the Extended Settings for the element. Here are some of my learnings:

• Unless you use a logical path / filename, you cannot specify a file termination, the APD check fails explaining that file names may only contain the characters A-Z, 0-9 and _.
• If you have a column in the report that is set as hidden, it will be extracted regardless of its show/hide status.
• The order of the fields on the file for a report I used with characteristics in rows and key figures in columns: it listed first all key figures in the file and then all characteristics: it did not follow the order of the key figures in the BEX report, it sorted the key figures by description and that was the order. For the characteristics it did respect the order as specified in the BEX report.
• The format of the key figures is ignored: for restricted key figures it used 3 decimals and for formulas / calculated key figures it used scientific notation. Also, for the restricted key figures with unit it added a column for the unit and for the calculated key figures / formulas it did not add a unit column.
• If you need to debug the extraction to understand it a little bit better, use the context menu on the query node and select the option display data after setting a breakpoint at method CL_RSCRMBW_BAPI->GENERATE_MDX.
• A way to overcome all these limitations regarding formats, column order, etc., would be to insert a routine between the query source element and the file target element. Then you can select what columns to pass as a source, removing for example the unit fields, you could also set the order of the fields and for fields the APD could not determine an infoObject for reference (hence the floating point type), you could use standard KF InfoObjects such as 0AMOUNT and 0QUANTITY.
• The BEx report cannot have display hierarchies neither as filters nor as elements in the report (rows or columns).
• If you have key figures showing inside key figures (expand / collapse), you will also get the warning “RSCRM 252 does not support display hierarchies” like in the image below.

• As an example of an MDX generated query, see below

SELECT   {
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[7ZYK7HLNG4UC6JD3PY2AH9IQK],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8AM6Y5EMB1JOWE9WS8LWOVQJG],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8CQX3HD7OFAD1KGGZWBFJ7DP8],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8EVN8TBT1T116QN17K0YDJ0V0],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[823ACTK8TIL0BPJNXLRTBL5WC],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[86CQNHHFKA2CM1WSCX6V08G7W],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8H0DE5AEF6RPBWTLF7QH7UO0S],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8J53JH8ZSKIDH305MVG026B6K],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8L9TOT7L5Y91M96PUJ5IWHYCC],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8NEJU566JBZPRFDA26V1QTLI4],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8480I5IU6WBOGVQ859HC5WT24],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8PJ9ZH4RWPQDWLJU9UKKL58NW],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[88HGSTG0XNT0R83CKKWDUK3DO],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8RO04T3DA3H21RQEHIA3FGVTO],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8TSQA51YNH7Q6XWYP5ZM9SIZG],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8VXGFH0K0UYEC43IWTP544658],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8Y26KSZ5E8P2HAA34HENYFTB0],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[92BMVGWC506ERMN7JSTPN33MK],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[94GD0SUXIDX2WSTRRGJ8HEQSC],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[96L364TIVRNR1Z0BZ48RBQDY4],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[98PTBGS495EF756W6RYA6213W]}  ON COLUMNS,
NON EMPTY CROSSJOIN(CROSSJOIN({
[0MATERIAL].LEVELS(01).MEMBERS},
{[CRMSET_0PLANT]}),
{[0MATERIAL__0PRODH2].LEVELS(01).MEMBERS})
ON ROWS FROM [-yourProvider-/-yourQuery-]
SAP VARIABLES [-versionDate-] INCLUDING
[-calDayVar-].[20141112]
[-UOMVar-] INCLUDING  [0UNIT].[CS]
[-PlantVar-] INCLUDING [0PLANT].[plantLow]:[0PLANT].[plantHigh]

How to set up the Process Chain Execution User

Even when the start process job scheduling in a process chain shows the name of the user that scheduled it, the user that executes the process chain is generally ALEREMOTE or whatever name is given to the batch job processing user.

To check which user is set up for a specific process chain, display it / or modify it in transactions RSPC or RSPC1, and execute the menu path Process Chain →  Attributes →  Execution User.

You can then select between using the BW Background User, the current user or a specific Special User.

This information is then stored in table RSPCCHAINATTR field BATCHUSER: if empty, it will use the BW Background User, if the value is %%actual%%, it means it will run under the current user, if it set to any other value, it will run under that specific user.

Missing Element ID when transporting query elements

When transporting a BEX query, a transport fails with an error but not self-explanatory.

Fortunately there is an SAP Note that helps in this issue, 1593665 – Missing Element Id when transporting query elements.

Running report CHECK_MISSING_ELEMENTS, copy the element ID from the transport log and enter the version M

It will then tell you if there is a missing element for the specif query noted in the transport. You can also check it without query parameter to see all missing elements in the target system.

If you then get a result, check this element in the source system in table RSZCOMPDIR and / or RSZGLOBV as it is very likely to be a global report variable. This happens frequently when you share the same path in the landscape for production support and projects as some objects belonging to the project may be in development but not yet migrated to production. All this to diagnose the root cause as the problem gets solved if you collect the BEX query in the transport connection with all objects.

Remember to re-schedule your modified process chain after you modify it and activate it

This may sound evident, but whenever a process chain is changed and activated it is removed from the scheduling. You see if it scheduled graphically when you enter in the process chain via transaction RSPC or RSPC1 and the different process variants show as green, if it is not scheduled it shows as grey or the default color.
So, whenever you change and modify a process change, remember to re-schedule it to avoid headaches the “day after”.

The button to schedule is in the toolbar right to the activate button.

Process Chain Activate Button

You can use Function Module RSPC_CHAIN_START to start the process chain without changing its current scheduling. In transaction SE37, enter RSPC_CHAIN_START in the Function Module field and click on the Test/Execute Button (F8)

Function Module RSPC_CHAIN_START, initial screen

Enter the name of the Process Chain on the field I_CHAIN and click on the “Execute” button (F8).

VBA Example for RSDRI_INFOPROV_READ_RFC

I found during testing that FM RSDRI_INFOPROV_READ_RFC cannot be used directly in VBA as it sends an error when trying to do the command

Set oR3F = Module1.oFunction.Add(“RSDRI_INFOPROV_READ_RFC”)  à Message box: wdtfuncs SAP data type not supported

This is due to an Export Parameter that is String type (E_RFCDATA_UC) as found in SCN thread issue using the COM components supplied with SAP GUI 6.2 or 6.4

To overcome this issue, I copied RSDRI_INFOPROV_READ_RFC into ZBW_RSDRI_INFOPROV_READ_RFC removing that parameter and it now works. The code below contains a test for the function module, by running testFM you get data from a cube. Be aware this is a test only and you would need to refine it to have a loop calling the FM multiple times based on I_MAXROWS provided and the size of the specific result set.

For more information refer to the SAP Online Help Data Mart Interface

Below is all the code needed to wrap the function module and to test it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169

Public oConnection As Object     ' SAP/BW Connection
Public oFunction As Object       ' Function object
Public sDetMsg As String
Public sSysID As String
Public vBWConnStatus As Boolean  ' Status for BW connection
Public sEndOfData As String
 
Public Function RSDRI_INFOPROV_READ_RFC( _
ByVal i_infoprov As String, _
ByVal i_reference_date As String, _
ByRef i_t_sfc() As String, _
ByRef i_t_sfk() As String, _
ByRef i_t_range() As String) As String()
 
    Dim tableData() As String
    Dim oR3F As Object
'
    Dim Data As Object
    Dim sfc As Object
    Dim sfk As Object
    Dim myRange As Object
    Dim Line As Long
    Dim FuncResult As Integer
    Dim iRowCount As Integer
'A copy of the original function module removing export parameter
'E_RFCDATA_UC as it is String and it is not supported
    Set oR3F = Module1.oFunction.Add("ZBW_RSDRI_INFOPROV_READ_RFC")
    oR3F.Exports("I_INFOPROV") = i_infoprov
    oR3F.Exports("I_REFERENCE_DATE") = i_reference_date
    oR3F.Exports("I_RESULTTYPE") = "V"
 
    Set sfc = Nothing
    Set sfc = oR3F.Tables.Item("I_T_SFC")
    For i = 1 To UBound(i_t_sfc, 2)
        sfc.Rows.Add
        sfc(i, "CHANM") = Trim(i_t_sfc(1, i))
        sfc(i, "CHAALIAS") = Trim(i_t_sfc(2, i))
        sfc(i, "ORDERBY") = Trim(i_t_sfc(3, i))
    Next i
 
    Set sfk = Nothing
    Set sfk = oR3F.Tables.Item("I_T_SFK")
    For i = 1 To UBound(i_t_sfk, 2)
        sfk.Rows.Add
        sfk(i, "KYFNM") = Trim(i_t_sfk(1, i))
        sfk(i, "KYFALIAS") = Trim(i_t_sfk(2, i))
        sfk(i, "AGGR") = Trim(i_t_sfk(3, i))
    Next i
 
    Set myRange = Nothing
    Set myRange = oR3F.Tables.Item("I_T_RANGE")
    For i = 1 To UBound(i_t_range, 2)
        myRange.Rows.Add
        myRange(i, "CHANM") = Trim(i_t_range(1, i))
        myRange(i, "SIGN") = Trim(i_t_range(2, i))
        myRange(i, "COMPOP") = Trim(i_t_range(3, i))
        myRange(i, "LOW") = Trim(i_t_range(4, i))
        myRange(i, "HIGH") = Trim(i_t_range(5, i))
    Next i
 
  Set Data = Nothing
  Set Data = oR3F.Tables("E_T_RFCDATAV")
 
  FuncResult = oR3F.Call
 
    sEndOfData = oR3F.Imports("E_END_OF_DATA")
    If FuncResult = True And Data.RowCount > 0 Then
    'Fields: ID IOBJNM VALUE UNIT
        ReDim tableData(4, Data.RowCount)
        For Line = 1 To Data.RowCount
          tableData(1, Line) = Data(Line, "ID")
          tableData(2, Line) = Data(Line, "IOBJNM")
          tableData(3, Line) = Data(Line, "VALUE")
          tableData(4, Line) = Data(Line, "UNIT")
        Next
        RSDRI_INFOPROV_READ_RFC = tableData
    End If
 
  Set Data = Nothing
  Set sfc = Nothing
  Set sfk = Nothing
  Set myRange = Nothing
  Set oR3F = Nothing
 
    For i = 1 To oFunction.Count
        oFunction.Remove (1)
    Next i
End Function
Public Sub Logoff()
    If vBWConnStatus = False Then
        MsgBox "Not logged in"
    Else
      oFunction.Connection.Logoff
      vBWConnStatus = False
   End If
   Set oConnection = Nothing
   Set oFunction = Nothing
End Sub
Sub clearData()
    ActiveCell.SpecialCells(xlLastCell).Select
    iLastRow = Selection.Row
    If iLastRow >= 2 Then
        sRowRange = 2 & ":" & iLastRow
        Rows(sRowRange).Select
        Selection.Delete
        Range("A1").Select
    End If
End Sub
 
Sub testFM()
Dim myData() As String
Dim i_t_sfc() As String
Dim i_t_sfk() As String
Dim i_t_range() As String
 
Logon 'YOUR LOGON Sub
 
sEndOfData = ""
'In a real case scenario, it should do a while...loop until sEndOfData = "X"
 
If Month(Date) < 10 Then
    sMonth = "0" & Month(Date)
Else
    sMonth = Month(Date)
End If
If Day(Date) < 10 Then     sDay = "0" & Day(Date) Else     sDay = Day(Date) End If sDate = Year(Date) & sMonth & sDay ReDim i_t_sfc(3, 4) i_t_sfc(1, 1) = "0DISTR_CHAN" 'CHANM i_t_sfc(2, 1) = "0DISTR_CHAN" 'CHAALIAS i_t_sfc(3, 1) = "0" 'ORDERBY i_t_sfc(1, 2) = "0DIVISION" 'CHANM i_t_sfc(2, 2) = "0DIVISION" 'CHAALIAS i_t_sfc(3, 2) = "0" 'ORDERBY i_t_sfc(1, 3) = "0SALESORG" 'CHANM i_t_sfc(2, 3) = "0SALESORG" 'CHAALIAS i_t_sfc(3, 3) = "0" 'ORDERBY i_t_sfc(1, 4) = "0CALDAY" 'CHANM i_t_sfc(2, 4) = "0CALDAY" 'CHAALIAS i_t_sfc(3, 4) = "0" 'ORDERBY ReDim i_t_sfk(3, 1) i_t_sfk(1, 1) = "NET_VAL_S" 'KYFNM i_t_sfk(2, 1) = "NET_VAL_S" 'KYFALIAS i_t_sfk(3, 1) = "SUM" 'AGGR ReDim i_t_range(5, 1) i_t_range(1, 1) = "0CALDAY" 'CHANM i_t_range(2, 1) = "I" 'SIGN i_t_range(3, 1) = "BT" 'COMPOP i_t_range(4, 1) = "20110401" 'LOW i_t_range(5, 1) = "20110531" 'HIGH sWS = "MAIN" 'Rename to your worksheet name Sheets(sWS).Select Range("A1").Select Columns("A:D").Select Selection.ColumnWidth = 30 Selection.NumberFormat = "@" Range("A1").Select iRow = 1 iCol = 1 ActiveCell.SpecialCells(xlLastCell).Select iLastRow = Selection.Row If iLastRow >= iRow Then
    sRowRange = iRow & ":" & iLastRow
    Rows(sRowRange).Select
    Selection.Delete
End If
 
myData = RSDRI_INFOPROV_READ_RFC("0SD_C03", sDate, i_t_sfc, i_t_sfk, i_t_range)
iCount = UBound(myData, 2)
If iCount > 0 Then
    'First write the headers
 
    For i = 1 To UBound(i_t_sfc, 2)
        Worksheets(sWS).Cells(iRow, iCol + i - 1).Value = i_t_sfc(1, i)
    Next i
    iColNext = iCol + UBound(i_t_sfc, 2)
    For i = 1 To UBound(i_t_sfk, 2)
        Worksheets(sWS).Cells(iRow, iColNext + i - 1).Value = i_t_sfk(1, i)
    Next i
    iColTotal = UBound(i_t_sfc, 2) + UBound(i_t_sfk, 2)
 
    'Now the data
    iCol = 1
    For i = 1 To iCount
        sValue = ""
        sUnit = ""
        sValue = Trim(myData(3, i))
        sUnit = Trim(myData(4, i))
        If sUnit <> "" Then
            sValue = sValue + " " + sUnit
        End If
        Worksheets(sWS).Cells(iRow + myData(1, i), iCol).Value = sValue
        iCol = iCol + 1
        If iCol > iColTotal Then
            iCol = 1
        End If
    Next i
    Columns("A:D").Select
    Columns("A:D").EntireColumn.AutoFit
    Range("A1").Select
End If
 
Logoff 'YOUR LOGOFF SUB
 
End Sub

Transport status in table TMSBUFFER and field MAXRC

In order to find and download the status for the different transports that go into a system, use table TMSBUFFER, the field MAXRC:

Field MAXRC has a logic described below. As an example see the 4 transports below in a target system:
D50964060 RC is 0004 (between 1 and 4, warning, yellow)
D50968806 RC is 0008 (between 5 and 8, single error, red)
D50971456 RC is 0000 Green
D50971642 RC is empty Blitz

TMSBUFFER traffic light field

The logic for field MAXRC is as follows:

Traffic Lights

This logic comes from the code in Function Module MS_UIQ_IMPORT_QUEUE_DISPLAY

SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP Business ByDesign, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries.

2LIS_02_ITM Not Updating Setup Tables In Initialization

Summary

When running the setup for application 02 (TR OLI3BW), the table was not filled: running RSA3 with no restrictions did not retrieve any records.

Diagnostics

By debugging function module MCEX02_PREPARE_INFO it was found that the field UPDACT in table TMCEXACT was not set for MC02M_0ITM. Once set (with a custom program to update), it started writing data in the setup table.

Activating the DataSource in transaction LBWE updates the field TMCEXACT-UPDACT as per SCN thread http://forums.sdn.sap.com/thread.jspa?threadID=115672

Some times the configuration in ECC is done in a client and the data is available in another one. To activate the DataSource in the “data” client, find your customizing transport request that contains the DataSource activation . Log into your “data” client and enter TCODE”SCC1″ enter the transport number and select include request subtasks.
This will bring across the client dependent data.

SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP Business ByDesign, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries.