Rolap cubes and writeback data from Board cube to SAP/4 HANA database

Options
Arnaud Villemain
Arnaud Villemain Active Partner
First Anniversary First Comment Name Dropper Photogenic
edited March 2023 in Platform

Hello,

i will discuss several points around Rolap cubes here

  1. Documentation
  2. example for SAP/4 HANA relational table including writeback
  3. my questions regarding writeback to relational database

Documentation (Board manual)

I found only partial information in the board manual regarding RDB cubes and writeback topics in the board manual (https://www.boardmanual.com/2021/summer/data-modeling/2_Database_design_functions/3_Cubes/creating-cube.htm). The topic “writeback” is only documented in relation with Nexel formulas, but not in relation with RDB cubes, unfortunately.

Therefore i had a look in the old board manual, where the section “rolap cubes” has more information (https://www.boardmanual.com/prev-man/Rolap_Cubes.htm)

My point of interest is: where is explained a) how to configure a RDB cube reading data from my relational (SAP/4 HANA) database and b) how i can use this cube to both:

  • let the Board user enter data manually which is then directly stored in the relational (SAP/4 HANA) database
  • set a dataflow which copies data from a MDB cube into the RDB Cube so that at the end the data from MDB cube is inserted into the relational (SAP/4 HANA) database.

The old manual tells well how to set up the RDB Cube to read data from the relational database, but i miss one point regarding the writeback ability: the relational table must have a primary key set on all dimension columns. Otherwise the user can write something in a dataview in the rolap cube, but when saving the entry, the old value comes again and has not been updated.

@ all who update the manual, i would suggest to make the hint visible. I got this “idea” of primary key only through the workaround of creating the relational table in the underlying relational database direct from the RDB cube interface (Tabelle erstellen on the picture below)

grafik.png

I suggest to indicate directly in the manual something like:

“⚠️ if the relational table already exist, make sure it has a primary key defined by the combination of all relational columns which are later in Board the dimensions of the RDB cube, otherwise the writeback Board → relational database won't work”

example for SAP/4 HANA relational table including writeback

i have one table in the source database (SAP4/HANA) having only 1 record. the column VALUE is the one later shown by the RDB cube in Board:

grafik.png

I can:

  1. see this value in Board.
  2. If the user updates the value in the dataview (the RDB cube is "Rueck_T_FLOW_PNL_MOVEMENTS", it is updated back in the SAP/4 HANA table ✅
    grafik.png
  3. But i am not able to insert any new data while drilling down to the lowest level. I write the value 123465 in a call, click on save.
    grafik.png

but after refreshing, the cell is empty again and no value is inserted in the relational database.

I tried another way for inserting data, namely by using an easy dataflow RDB cube (Target) = MDB cube (source)

grafik.png

This doesn't work either.

if it helps your analysis, here the Writeback option of the RDB cube

image.png

Questions

how is it possible to enter data

  1. manually in Board as a user in a screen
  2. per procedure via Board dataflow (RDB cube receives data from MDB cube)

in a RDB cube which is therefore directly inserted in the relational table behind the RDB cube?

—

My current Board version is 12.2.0.0.67608

Many thanks for your help

Accepted Answer

  • Arnaud Villemain
    Arnaud Villemain Active Partner
    First Anniversary First Comment Name Dropper Photogenic
    edited March 2023 Answer ✓
    Options

    Hello @Daniele Santandrea

    Many thanks for your answer.

    The error message from SQLError_202303.log is:

    ERROR [S1000] [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;383 invalid identifier: T_FLOW_PNL_MOVEMENTS: line 1 col 202 (at pos 201) SELECT t.MONTH, t.LEGAL_ENTITY, t.GL_ACCOUNT, t.SCENARIO, t.COST_CENTRE, t.VERSION, t.REPORTING_LAYER, t.BOARD_MODEL, t.VALUE  FROM BOARD_FIN_PROTOTYP.T_FLOW_PNL_MOVEMENTS t   WHERE BOARD_FIN_PROTOTYP.T_FLOW_PNL_MOVEMENTS.MONTH = '201803'   AND BOARD_FIN_PROTOTYP.T_FLOW_PNL_MOVEMENTS.LEGAL_ENTITY = '150-RB01'   AND BOARD_FIN_PROTOTYP.T_FLOW_PNL_MOVEMENTS.GL_ACCOUNT = '19150000'   AND BOARD_FIN_PROTOTYP.T_FLOW_PNL_MOVEMENTS.SCENARIO = 'BGT'   AND BOARD_FIN_PROTOTYP.T_FLOW_PNL_MOVEMENTS.COST_CENTRE = 'PG01617'   AND BOARD_FIN_PROTOTYP.T_FLOW_PNL_MOVEMENTS.VERSION = '1'   AND BOARD_FIN_PROTOTYP.T_FLOW_PNL_MOVEMENTS.REPORTING_LAYER = '10'   AND BOARD_FIN_PROTOTYP.T_FLOW_PNL_MOVEMENTS.BOARD_MODEL = '60'
DSN=SAPE57;UID=BOARD_FIN_PROTOTYP;

    it looks like that the identifiyer T_FLOW_PNL_MOVEMENTS ist not known, although my RDB cube SQL query definition (see screenshot first message above

    Untitled Image
    ).

    I don't really understand this error message, since the RDB definition is working properly regarding Update of a cell cube using screen data-entry.

    I suspected, the INSERT action might have issues while using SQL manual mode. The manual mode makes SQL queries easier to read, because it allows aliases (here the letter “t”):

    SELECT t.MONTH, t.LEGAL_ENTITY, t.GL_ACCOUNT, t.SCENARIO, t.COST_CENTRE, t.VERSION, t.REPORTING_LAYER, t.BOARD_MODEL, t.VALUE FROM BOARD_FIN_PROTOTYP.T_FLOW_PNL_MOVEMENTS t

    I have then tested using SQL automatic mode

    grafik.png

    After this the data-entry worked.

    1. Does that mean i cannot use SQL manual mode for the RDB datareader if i want the user to enter new figures in empty cells in a Board screen?
    2. detail question: the log entries in SQLError_202303.log do not have any timestamps. Is this normal? See picture with four entries:
      grafik.png

Answers

Categories