reyemsaibot

SAP BI Blog about SAP BW/4HANA, Analysis for Office and SAP HANA - by Tobias Meyer

staging.tobiasmeyer.net

Autor: reyemsaibot

  • Analysis Office 2.6 SP1 is available

    Since today Analysis Office 2.6 SP1 is available. You need as always a S-User to download the latest version. Here is a short overview what it might fix.

    • AO 2.6 Table design rule: Error with Formula VLOOKUP to external excel (s-note 2590137)
    • AO: Exceptions occur when loading AO after opening an AO workbook (s-note 2595959)
    • Analyis Office: Text and Key are concatenated in new line attributre cells (s-note 2586756)
    • Analysis Office: Saving a workbook destroys the layout of the workbook (s-note 2594627)
    • Prompts dialog: Hierarchy variable shows wrong value (s-note 2587530)
    • Scheduling A.O. Workbook Generates Corrupted Workbooks (s-note 2589444)
    • Sheets option are lost when Excel document is opened from server repos (s-note 2593473)
    • Table Design formats are not applied (s-note 2594716)

     A overview what’s new will be available next week. If you find something interesting, please feel free to post a comment.

    These posts might also be interesting:

    author.

    I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

  • SAP BW Create own reversal entry

    Lately, all my posts started with „In my current project“, so now something else, even if it was developed in the current project. The problem we are facing with is that we get a data extraction
    which deliver us only the new data records, not the reverse data record.

    Old booked value
    Old booked value
    New booked value
    New booked value


    So we need to build your „own reversal entry“ to set the values of a record to 0. For this we write our ADSO in itself with a formula which delivers the negative value.

    SAP BW Negative formula
    SAP BW Negative formula

    I build a transformation which the formula above and the magic will be done in the DTP filter. First, let me briefly explain our data model. We use the following layers:

    • Acquisition Layer
    • Propagation Layer
    • Transformation Layer

    But back to the magic. The routine is writen in the DTP Transformation Layer in Transformation Layer.

    Write an Advanced DSO in itself
    Write an Advanced DSO in itself

    First I determine all requests from the Acquistion Layer DSO.  

    1
2
    		 
    "Determine all requests from source DSO
SELECT DISTINCT ( reqtsn ) FROM /bic/avf0aperia1 INTO TABLE lt_requests.

     

    After I have all requests in my internal table, I sort it descending to get the right order. 

    1
2
    		 
    "Sort Table descending
SORT lt_requests DESCENDING BY request.

     

    Now I select the first request. It is the newst. 

    1
2
3
4
    		 
    LOOP AT lt_requests INTO ls_requests FROM 1 TO 1.
  "Puffer last request
  lv_last_request = ls_requests-request.
ENDLOOP.

     

    Now we know the right request and select all data from the advanced DSO (Aquisition Layer). 

    1
    		 
    SELECT DISTINCT field1 field2 calyear field3 FROM DSO_AL INTO TABLE lt_new_data WHERE reqtsn = lv_last_request.

     

    After we now have all necessary information in our internal table, we have to fill the DTP filter. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
    		 
    DATA: l_idx LIKE sy-tabix.

READ TABLE l_t_range WITH KEY fieldname = 'field2'.
l_idx = sy-tabix.
LOOP AT lt_new_data ASSIGNING <fs_new_data>.
  l_t_range-fieldname = 'field2'.
  l_t_range-iobjnm    = 'field2'.
  l_t_range-sign      = 'I'.
  l_t_range-option    = 'EQ'.
  l_t_range-low       = <fs_new_data>-field2.
  IF l_idx <> 0.
    MODIFY l_t_range INDEX l_idx.
  ELSE.
    APPEND l_t_range.
  ENDIF.
ENDLOOP.

    After I execute the DTP and activate the request in the transformation layer advanced DSO you see there are only 18 entries which are corrected.

    Number of entries which are false
    Number of entries which are false

    When you now look into the active table of the advanced DSO, you see all values which are shown above are now 0.

    Active table values
    Active table values

    The changelog also show the new, the before and the after image for the entries. So you can understand what was booked.

    Changelog View
    Changelog View

    And when we now execute all DTPs to write the data from the Acquistion Layer to the Tranformation Layer, we see that there are only 18 entries which are new.

    Requests of the advanced DSO
    Requests of the advanced DSO

    And also the new booked values are the same we have above in our second extract.

    New values in advanced DSO
    New values in advanced DSO

    As you can see it is very easy to create your own reversal entry if the source system don’t deliver one. I hope someone can need it.

    author.

    I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

  • Analysis Office Filter by Member Attribute

    Crosstab before Filter by Member Attribute
    Crosstab before Filter by Member Attribute

    A feature which I didn’t know even it is available since Analysis Office 2.2 is to search and filter by attribute. First
    select a dimension you want to filter.

    Filter by Member via Context Menu
    Filter by Member via Context Menu

    Now you see the Filter by Member dialog and you can display the necessary attribute you want to filter.

    Filter by Member Dialog
    Filter by Member Dialog

    Now select in the dropdown menu the attribute you want to filter.

    Filter by Member Select Attribute
    Filter by Member Select Attribute

    After you select the attribute, you can use the search bar to filter your result.

    Filter by Member via Attribute
    Filter by Member via Attribute

    The result of the crosstab should look like the following screenshot.

    Crosstab after Filter by Member
    Crosstab after Filter by Member

    So as you can see, you cannot only Filter by Member for a dimension, you can also Filter By Member for an attribute. This might be interesting for you, when you use a lot of attributes.

    author.

    I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

  • SAP BW: Keyfigure to Account Model

    In my current project we have a lots of source systems which delivers our data in a key figure model. In a normal case is this not a problem. But we need the values of the key figures in a
    hierarchy. So there are two ways to realize it. First way would be we build a structure with restricted key figures and get our hierarchy. Here is the problem, the customer will need different
    queries to realize his needs. If there is a change, we need to adjust different queries (each has a light different hierarchy) and the maintainace is immense.

    Query Hierarchy Structure
    Query Hierarchy Structure

    Another way is to use an account model. Here is your first Problem, we only get a key figure model and it is not so easy to use rule groups or a simply routine, because we get around 100 key
    figures. So it isn’t easy as Denis wrote in his post (this post is in German). But I found, really I can’t belive I found an article on the former SCN. This post decribes how to transform
    a key figure based model to an account based model with some ABAP coding and an expert routine. So I think that must be the answer to my problem, but it isn’t so well writen that I could use it.
    But the start was done. I need a Z-Table and some ABAP. First I created my Z-Table with four fields:

    • MANDT
    • AREA
    • SOURCE_KF
    • ACCOUNTPOSITION

    AREA is necessary to use this table in different deparments, SOURCE_KF represents the source key figure for example /BIC/ZGROSSVAL and the
    ACCOUNTPOSITION is the technical key of the account model, for example 15.

    Z-Table for Mapping Keyfigure to Account
    Z-Table for Mapping Keyfigure to Account

    This was the easy part. Now I need some ABAP and here I had some struggle. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
    		 
    Data: lt_account        type HASHED TABLE OF z_account WITH UNIQUE KEY source_kf,
      ls_account        like LINE OF lt_account.

FIELD-SYMBOLS: <fv_source> type any.

Select * from z_account into table lt_account WHERE area = 'SOMETHING'.

LOOP AT SOURCE_PACKAGE ASSIGNING <source_fields>.
  MOVE-CORRESPONDING <source_fields> to RESULT_FIELDS.
  Loop at lt_account into ls_account.
    RESULT_FIELDS-/BIC/VF0CBPOS = ls_account-berichtsposition.
    ASSIGN COMPONENT ls_account-source_kf of STRUCTURE <source_fields> to <fv_source>.
    RESULT_FIELDS-/bic/vf0kbehw = <fv_source>.
    Append RESULT_FIELDS TO RESULT_PACKAGE.
  Endloop.
Endloop.

    The Problem was the ASSIGN COMPONENT part, which I didn’t know how to work, but a colleague helped me here. So that’s it. The Z-Table can be modified to fulfill your needs, but the basic code
    should help you to realize a solution.

    author.

    I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

  • Analysis Office 2.6 is available

    As you might know, Analysis Office 2.6 is available now. To download it, you need a S-User with download credentials. First SAP
    BusinessObjects Analysis for Microsoft Office is now called, SAP Analysis for Microsoft Office. Damn I have to rewrite my book, just kidding.
    Here is a short overview of What’s new in Analysis Office 2.6:

    • You can now copy a Table Design formula by using Microsoft Excel Fill Handling
    • The formula SAPSetFilterComponent has a new selection type MEMBERSSELECTOR.
    • The API Command NumberOfNewLines can now set the number of new lines for a crosstab
    • According to Matthew Shaw you can now connect to/report on SAP Analytics Cloud.

    So I just downloaded Analysis Office 2.6 and maybe I can write a little bit more in the next days.

    These posts might also be interesting:

    author.

    I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

  • Analysis Office 2.5 SP2

    In the last month I was very busy with my thesis and my project at work, so I had no time to write a lot of blog posts. So here is a short overview what Analysis Office 2.5 SP2 fix. But keep in mind, Analysis Office 2.6 is up coming.

    •  An execption is thrown when grouping crosstabs (s-note 2530837)
    • Formula SAPGetVariable does not return value for BASEINFOOBJT if variables are merged (s-note 2529886)
    • Group Crosstabs / Compact Display does not work properly (s-note 2527113)
    • Native date, date time and time span rendering (s-note
      2482546      )
    • Not able to change presentation of attribute (s-note 2525435)
    • Refresh in Open dialog does not work (s-note 2536320)
    • Launching a second workbook starts an empty Excel (s-note 2551513)

    There are a lot more bugs which are now patched, so install the latest version.

    These posts might also be interesting:

    author.

    I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

  • What’s new in Analysis Office 2.6

    Today Alexander Peter showed in the ASUG Webcast Analysis Office 2.6. The expected GA is in three weeks. New in Analysis Office 2.6 are the following things:

    • Table Design Improvements (auto complete enhancements)
    • Improved handling of new lines in planning applications (SAP BW 7.5 SP11)
    • Open Analysis Office workooks using variables (BI Platform 4.2 SP5)
    • Editing schedule jobs (BI Platform 4.2 SP5)
    • Scheduling based on events (BI Platform 4.2 SP5)
    • Publications (BI Platform 4.2 SP5)
    • More features with SAP Analytics Cloud such as exception aggregation
    • Access repository of all (native) cloud models
    • Exception aggregation on the fly
    • Use Dynamic Calculations Rank and Rank Olympic (planned for SP1)
    • Launch Workbooks directly from the BI Platform with variables (BI Platform 4.2 SP5)
    • Rescheduling Jobs on the BI Platform 4.2 SP5
    • Scheduling workbooks with events (e.g. successful/failed data load to BW)
    • Use VLOOKUP with Table Design
    • Filter by Range is now possible
    • New Parameter such as EnableNativeFormatting or UseNewLinesLegacyMode

    These posts might also be interesting:

    author.


    I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

  • SAP BW Use Pattern in Variable with Customer Exit

    In my current project I created with a collegue a really cool function to analyze a string with 1333 characters. We are using a BW 7.4 SP 17 on HANA. First we have to build an Advanced DataStoreObject with a Field which has a length of 1333. For further understanding, we call the
    field Field_1333. As data type I used SSTRING.

    Create a field with a length of 1333 in Eclipse
    Create a field with a length of 1333 in Eclipse

    The Advanced DataStoreObject will be included into a CompositeProvider. The Problem is the CompositeProvider cannot work with a field which has the length of 1333.

    Advanced DSO cannot work with a field which is longer than 255
    Advanced DSO cannot work with a field which is longer than 255

    So my colleague decided to build an Open ODS View on top of the Advanced DSO. Therefor you have to activate the checkbox External SAP HANA View. My Open ODS View contains only
    the field „Field_1333“, Number of Records and one InfoObject I need for my query. As you can see, the Open ODS View shows a length of 250 for the field „Field_1333“. This is only a
    display problem.

    Open ODS View Display Problem
    Open ODS View Display Problem

    When you look into the data, the Information is complete.

    Datafield in ADSO
    Datafield in ADSO

    Now I use the Open ODS View in my CompositeProvider and here we go, the field is now available for my query.

    Mapping Field with Open ODS View
    Mapping Field with Open ODS View

    Now we build a query on the CompositeProvider and add the necessary InfoObject into the rows and a restricted key figure into the columns with a variable for the field „Field_1333“. This variable
    is used to fill the customer exit variable of the field „Field_1333“.

    Query Variable Customer Exit
    Query Variable Customer Exit

    Now you have the opportunity to fill a lot of values separated by comma. The customer exit sorts all values by ascending and fill the variable with ‚*‘ & value & ‚*‘

    ls_e_range-low = ls_e_range-low && ‚*‘ && ls_code-ccode && ‚*‘.

    Now we can combine different combinations and get a result. This result is used as a PreQuery in my main development. So I am able to build a complex analytics application and give the user the
    opportunity to analyze his data deeply.

    Replacement Variable with PreQuery
    Replacement Variable with PreQuery

    author.

    I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

  • Analysis Office 2.5 – Technical Configuration

    A really cool feature of Analysis Office 2.5 can you see in the „backend“ under File >> Analysis. The function
    Customize Analysis offers now new options. Besides the Customize User Interface from Analysis Office 2.4
    which allows you to change the ribbon menu of Analysis Office, you now can directly modify the technical settings of Analysis Office.

    Customize User Interface
    Customize User Interface

    With the function Technical Configuration you can modify all setting parameters from the file system settings.

    Technical Configuration
    Technical Configuration

    This is nice, because several properties could be edit while Excel with Analysis Office Addin is open and you don’t have to search for the parameter files in the file system. You have a little
    search bar on the top. The only thing what I am missing is that there is a direct link to a help file oder something similar to see what the property does. Maybe someone from SAP reads this and
    add this feature for a future release.

    These posts might also be interesting:

    author.

    I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

  • Analysis Office 2.5 DataSource Information

    This week is very short in case of the day of German unity and we have sprint change next week, I haven’t time to write a lot so here is just one goodie. When you insert a DataSource in Analysis Office 2.5 you see the recent inserted DataSource. Now in Analysis Office 2.5 you also see the technical name of the query and the system. I think this is nice if you are testing a query on different systems.

    Recent DataSource
    Recent DataSource
    Recent DataSource Detail Information
    Recent DataSource Detail Information


    Sorry for the German text on the screenshot. At the moment I have only a German Excel. Is there any cool feature which Analysis Office now
    provide you do not want to do without? Just tell me in the comments.

    These posts might also be interesting:

    author.

    I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

reyemsaibot.com auf WordPress-Staging