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

Arnaud Villemain · 2023-03-02T11:25:31+00:00

There was an error rendering this rich post.

Hello,

i will discuss several points around Rolap cubes here

Documentation
example for SAP/4 HANA relational table including writeback
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)

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:

I can:

see this value in Board.
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 ✅
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.

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)

This doesn't work either.

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

Questions

how is it possible to enter data

manually in Board as a user in a screen
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

Find more posts tagged with

Accepted answers

Arnaud Villemain

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

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

After this the data-entry worked.

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?
detail question: the log entries in SQLError_202303.log do not have any timestamps. Is this normal? See picture with four entries:

All comments

Daniele Santandrea

Hello Arnaud Villemain,

i try to answer your questions:

the ROLAP must have all the dimensions dense
the relational table must have a primary key set on all dimension
you can write into a relational table with Data Entry (at max level) or Dataflow (b=a from MDB to RDB with the same dimensions).
If you can't write into relational table, you can see the error in the “SQL Error” log

So, can you send me the SQL Error after Save Data Entry or Dataflow?

In addition, if you want there are other 2 new ways:

BULK INSERT (from Winter 2021) —>
https://www.boardmanual.com/2021/summer/whats-new-release-notes/winter-2021-release/main-features/New_Extract_procedures.htm?rhsearch=BULK&rhhlterm=bulk
BOARD API (from Summer 2022) —>
https://www.boardmanual.com/2021/summer/whats-new-release-notes/2022-spring-release/main-features/rest-apis.htm?rhsearch=api%20&rhhlterm=apis%20api

Thanks,
Regards

Daniele

Leone Scaburri

Hello @Arnaud Villemain,

be aware selecting “Delete Insert” method:

when data is saved to a cell of the InfoCube corresponding to an existing record then the record is updated through an UPDATE.
when data is saved to a cell of the InfoCube which does not correspond to any existing record then a new record is added to the table through an INSERT command.

You can try to reduce the buffer size so Board runs multiple SQL commands each with subset of conditions and SQL queries are then merged and aggregated to the required view.

Arnaud Villemain

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

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

After this the data-entry worked.

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?
detail question: the log entries in SQLError_202303.log do not have any timestamps. Is this normal? See picture with four entries:

Daniele Santandrea

Hello Arnaud Villemain,

I answer your questions:

In general you can use SQL manual mode for the RDB Cube.
Probably you cannot use the alias in these cases.
Yes, i confirm that the SQL Error doesn't have the timestamp.
You can use the Diagnostic Log to integrate this information.

Regards
Daniele

Daniele Santandrea

Hello Arnaud Villemain,

i try to answer your questions:

the ROLAP cube must have all the dimensions dense
The relational table must have a primary key set on all dimension
you can write in four ways:
1. Data entry directly in the RDB Cube
2. Dataflow from MDB Cube to RDB Cube (b=a) with the same dimensions
3. Bulk insert (new functionality from Winter 2021) —> https://www.boardmanual.com/2021/summer/whats-new-release-notes/winter-2021-release/main-features/New_Extract_procedures.htm?rhsearch=BULK%20INSERT&rhhlterm=bulk%20insert%20inserted
4. BOARD API (new functionality from Spring 2022) —> https://www.boardmanual.com/2021/summer/whats-new-release-notes/2022-spring-release/main-features/rest-apis.htm?rhsearch=API&rhhlterm=apis%20api

If you can't write into relational table, you can see the error in the SQL Error.
Can you send me the SQL Error after SAVE Data Entry or Dataflow?

Thanks,
Regards

Daniele

Daniele Santandrea

Hello Arnaud Villemain,

i try to answer your question:

the ROLAP must have all the dimensions dense
the relational table must have a primary key set on all dimension
you can write into a relational table with Data Entry (at max level) or Dataflow (b=a from MDB to RDB with the same dimensions).
If you can't write into relational table, you can see the error in the “SQL Error” log

So, can you send me the SQL Error after Save Data Entry or Dataflow?

In addition, if you want there are other 2 new ways:

BULK INSERT (from Winter 2021) —> https://www.boardmanual.com/2021/summer/whats-new-release-notes/winter-2021-release/main-features/New_Extract_procedures.htm?rhsearch=BULK&rhhlterm=bulk
BOARD API (from Summer 2022) —> https://www.boardmanual.com/2021/summer/whats-new-release-notes/2022-spring-release/main-features/rest-apis.htm?rhsearch=API&rhhlterm=apis%20api

Thanks,
Regards

Daniele

Daniele Santandrea

Hello Arnaud Villemain,

i try to answer your question:

the ROLAP must have all the dimensions dense
the relational table must have a primary key set on all dimension
you can write into a relational table with Data Entry (at max level) or Dataflow (b=a from MDB to RDB with the same dimensions).
If you can't write into relational table, you can see the error in the “SQL Error” log

So, can you send me the SQL Error after Save Data Entry or Dataflow?

In addition, if you want there are other 2 new ways:

BULK INSERT (from Winter 2021) —> https://www.boardmanual.com/2021/summer/whats-new-release-notes/winter-2021-release/main-features/New_Extract_procedures.htm?rhsearch=BULK&rhhlterm=bulk
BOARD API (from Summer 2022) —> https://www.boardmanual.com/2021/summer/whats-new-release-notes/2022-spring-release/main-features/rest-apis.htm?rhsearch=api%20&rhhlterm=apis%20api

Thanks,
Regards

Daniele