How to WEBI reporting on BFC data
Hi!
This issue can give you the tool for building Web Intelligence reports (WEBI) on SAP BusinessObjects Financial Consolidation (BFC) package and consolidated data. If BFC standard reporting doesn’t satisfy you by any reason and you want to benefit of using secure repository for your reports, authorized access to data through SAP BO BI Platform and so on you can follow this guide.
I’ve found only one way to access BFC data by OLAP driver in Information Design Tool for only Microsoft Platform (MSAS).
Anyway there is a suggestion or workaround to access BFC data using relational driver of your database which Universe Designer or Information Design Tool supports. In this example it is Oracle database.
All of the steps in this guide related to the manipulation with database objects. Your database has to provide something like triggers and jobs as in Oracle DB.
The tables of BFC DB schema containing package or consolidated data is quite dynamical. It means that tables are created and droped during the diverse activities in BFC. Package data resides in CT_PKNNNN tables, where NNNN is a serial number left padded with zeros. In addition CT_PKREF table contains identification data where the value of ID column is equal to NNNN serial numbers of СT_PKNNNN tables. During the activities in BFC the tables CT_PKNNNN are created or droped and correspond rows in CT_PKREF table are inserted or deleted. The same is for consolidated data that is in CT_CONNNN and CT_COREF tables. For more information about BFC DB schema refer to SAP BusinessObjects Financial Consolidation Physical Database Schema Guide.
Thus do the following steps:
- Create database view that combine package or consolidated data from all of the corresponding tables CT_PKNNNN & CT_PKREF or CT_CONNNN & CT_COREF
- Create procedures and triggers to respond the required events of a new table emergence or disappearing one.
For instance CT_CONNNN tables consist of the columns listed in the next table.
Make an assumption that the structure of these tables won’t change from table to table in a sequential series of tables. That is you will keep the Dimensions in BFC unchanged.
| Column name | Data type |
|---|---|
| CT_0000_AN | NUMBER (10) |
| PERIOD | NUMBER (10) |
| ENTITY | NUMBER (10) |
| ENTORIG | NUMBER (10) |
| ACCNT | NUMBER (10) |
| FLOW | NUMBER (10) |
| NATURE | NUMBER (10) |
|
PARTNER |
NUMBER (10) |
| CURNCY | NUMBER (10) |
| TECHORIG | NUMBER (10) |
| GLOBORIG | NUMBER (10) |
| JOURNAL | NUMBER (10) |
| ENUMBER | NUMBER (10) |
| ROLLUP_PARTNER | NUMBER (10) |
| ROLLUP | NUMBER (10) |
| CT_0000_CO | NUMBER (10) |
| MU | NUMBER (10) |
| PMU | NUMBER (10) |
| ROLLUP2 | NUMBER (10) |
| ROLLUP_PARTNER2 | NUMBER (10) |
| CT_0100_MV | NUMBER (10) |
| CT_0100_AI | NUMBER (10) |
| CT_0100_A4 | NUMBER (10) |
| CT_0100_CF | NUMBER (10) |
| CT_0100_SL | NUMBER (10) |
| CT_0100_CN | NUMBER (10) |
| CT_0100_CTR | NUMBER (10) |
| CT_0100_CDV | NUMBER (10) |
| CT_0100_CFA | NUMBER (10) |
| CT_0100_CRR | NUMBER (10) |
| CT_0100_OB | NUMBER (10) |
| CT_0100_DS | NUMBER (10) |
| CT_0100_DC | NUMBER (10) |
| CT_0100_CDE | NUMBER (10) |
| AMOUNT | FLOAT (126) |
| CONVAMOUNT | FLOAT (126) |
| CONSAMOUNT | FLOAT (126) |
| DATA_COMMENT | NVARCHAR2 (2000) |
And there are 2 tables СT_CO0001, CT_CO0002.
So the database view script looks like the following.
CREATE OR REPLACE FORCE VIEW DWH_FC_CO
(
ID,
PHASE,
UPDPER,
SCOPE,
VARIANT,
CONS_CURNCY,
CT_0000_AN,
PERIOD,
ENTITY,
ENTORIG,
ACCNT,
FLOW,
NATURE,
PARTNER,
CTSHARE,
TRANS_CURNCY,
TECHORIG,
GLOBORIG,
JOURNAL,
ENUMBER,
ROLLUP_PARTNER,
ROLLUP,
CT_0000_CO,
MU,
PMU,
ROLLUP2,
ROLLUP_PARTNER2,
CT_0100_MV,
CT_0100_AI,
CT_0100_A4,
CT_0100_CF,
CT_0100_SL,
CT_0100_CN,
CT_0100_CTR,
CT_0100_CDV,
CT_0100_CFA,
CT_0100_CRR,
CT_0100_OB,
CT_0100_DS,
CT_0100_DC,
AMOUNT,
CONVAMOUNT,
CONSAMOUNT,
DATA_COMMENT
)
AS
( SELECT REF.ID,
REF.PHASE,
REF.UPDPER,
REF.SCOPE,
REF.VARIANT,
REF.CURNCY,
co.CT_0000_AN,
co.PERIOD,
co.ENTITY,
co.ENTORIG,
co.ACCNT,
co.FLOW,
co.NATURE,
co.PARTNER,
co.CTSHARE,
co.CURNCY,
co.TECHORIG,
co.GLOBORIG,
co.JOURNAL,
co.ENUMBER,
co.ROLLUP_PARTNER,
co.ROLLUP,
co.CT_0000_CO,
co.MU,
co.PMU,
co.ROLLUP2,
co.ROLLUP_PARTNER2,
co.CT_0100_MV,
co.CT_0100_AI,
co.CT_0100_A4,
co.CT_0100_CF,
co.CT_0100_SL,
co.CT_0100_CN,
co.CT_0100_CTR,
co.CT_0100_CDV,
co.CT_0100_CFA,
co.CT_0100_CRR,
co.CT_0100_OB,
co.CT_0100_DS,
co.CT_0100_DC,
SUM (co.AMOUNT),
SUM (co.CONVAMOUNT),
SUM (co.CONSAMOUNT),
co.DATA_COMMENT
FROM BOFC.CT_COREF REF, BOFC.CT_CO0001 co
WHERE REF.id = 1
GROUP BY REF.ID,
REF.PHASE,
REF.UPDPER,
REF.SCOPE,
REF.VARIANT,
REF.CURNCY,
co.CT_0000_AN,
co.PERIOD,
co.ENTITY,
co.ENTORIG,
co.ACCNT,
co.FLOW,
co.NATURE,
co.PARTNER,
co.CTSHARE,
co.CURNCY,
co.TECHORIG,
co.GLOBORIG,
co.JOURNAL,
co.ENUMBER,
co.ROLLUP_PARTNER,
co.ROLLUP,
co.CT_0000_CO,
co.MU,
co.PMU,
co.ROLLUP2,
co.ROLLUP_PARTNER2,
co.CT_0100_MV,
co.CT_0100_AI,
co.CT_0100_A4,
co.CT_0100_CF,
co.CT_0100_SL,
co.CT_0100_CN,
co.CT_0100_CTR,
co.CT_0100_CDV,
co.CT_0100_CFA,
co.CT_0100_CRR,
co.CT_0100_OB,
co.CT_0100_DS,
co.CT_0100_DC,
co.DATA_COMMENT
UNION
SELECT REF.ID,
REF.PHASE,
REF.UPDPER,
REF.SCOPE,
REF.VARIANT,
REF.CURNCY,
co.CT_0000_AN,
co.PERIOD,
co.ENTITY,
co.ENTORIG,
co.ACCNT,
co.FLOW,
co.NATURE,
co.PARTNER,
co.CTSHARE,
co.CURNCY,
co.TECHORIG,
co.GLOBORIG,
co.JOURNAL,
co.ENUMBER,
co.ROLLUP_PARTNER,
co.ROLLUP,
co.CT_0000_CO,
co.MU,
co.PMU,
co.ROLLUP2,
co.ROLLUP_PARTNER2,
co.CT_0100_MV,
co.CT_0100_AI,
co.CT_0100_A4,
co.CT_0100_CF,
co.CT_0100_SL,
co.CT_0100_CN,
co.CT_0100_CTR,
co.CT_0100_CDV,
co.CT_0100_CFA,
co.CT_0100_CRR,
co.CT_0100_OB,
co.CT_0100_DS,
co.CT_0100_DC,
SUM (co.AMOUNT),
SUM (co.CONVAMOUNT),
SUM (co.CONSAMOUNT),
co.DATA_COMMENT
FROM BOFC.CT_COREF REF, BOFC.CT_CO0002 co
WHERE REF.id = 2
GROUP BY REF.ID,
REF.PHASE,
REF.UPDPER,
REF.SCOPE,
REF.VARIANT,
REF.CURNCY,
co.CT_0000_AN,
co.PERIOD,
co.ENTITY,
co.ENTORIG,
co.ACCNT,
co.FLOW,
co.NATURE,
co.PARTNER,
co.CTSHARE,
co.CURNCY,
co.TECHORIG,
co.GLOBORIG,
co.JOURNAL,
co.ENUMBER,
co.ROLLUP_PARTNER,
co.ROLLUP,
co.CT_0000_CO,
co.MU,
co.PMU,
co.ROLLUP2,
co.ROLLUP_PARTNER2,
co.CT_0100_MV,
co.CT_0100_AI,
co.CT_0100_A4,
co.CT_0100_CF,
co.CT_0100_SL,
co.CT_0100_CN,
co.CT_0100_CTR,
co.CT_0100_CDV,
co.CT_0100_CFA,
co.CT_0100_CRR,
co.CT_0100_OB,
co.CT_0100_DS,
co.CT_0100_DC,
co.DATA_COMMENT
);The number of involved tables into script will grow and sooner or later you faced with the Oracle string length restriction that is 32K. Thus we divide script. One script creates views DWH_FC_CO_NNNN for each table CT_CONNNN and the second creates the view that unifies all of the views with a magic SQL operator “SELECT * …”. The following PL\SQL script produces such views. First of all run this script before proceed to the trigger implementation.
CREATE OR REPLACE procedure BOFC.DWH_FC_UPDATE_CO_VIEW_DEV is
pragma autonomous_transaction;
-- cursor running on the list of existed tables
cursor co_id is select id from BOFC.CT_COREF;
-- cursor for the list of "old" views
cursor old_view_name is select view_name from all_views where view_name like 'DWH_FC_CO%' and owner = 'BOFC';
l_co co_id%rowtype;
l_old_view_name old_view_name%rowtype;
l_upper_view_txt varchar2(32767);
l_base_view_txt varchar2(32767);
begin
-- drop all of "old" views
open old_view_name;
loop
fetch old_view_name into l_old_view_name;
exit when old_view_name%notfound;
execute immediate 'DROP VIEW BOFC.'||l_old_view_name.view_name;
end loop;
close old_view_name;
-- header of the "upper" view
l_upper_view_txt := 'CREATE OR REPLACE VIEW BOFC.DWH_FC_CO (ID, PHASE, UPDPER, SCOPE, VARIANT, CONS_CURNCY, CT_0000_AN, PERIOD, ENTITY, ENTORIG, ACCNT, FLOW, NATURE, PARTNER, CTSHARE, TRANS_CURNCY, TECHORIG, GLOBORIG, JOURNAL, ENUMBER, ROLLUP_PARTNER, ROLLUP, CT_0000_CO, MU, PMU, ROLLUP2, ROLLUP_PARTNER2, CT_0100_MV, CT_0100_AI, CT_0100_A4, CT_0100_CF, CT_0100_SL, CT_0100_CN, CT_0100_CTR, CT_0100_CDV, CT_0100_CFA, CT_0100_CRR, CT_0100_OB, CT_0100_DS, CT_0100_DC, AMOUNT, CONVAMOUNT, CONSAMOUNT, DATA_COMMENT) AS (';
-- in the loop for each row in ct_coref create "base" view and gather script for the "upper" view
open co_id;
loop
fetch co_id into l_co;
exit when co_id%notfound;
l_base_view_txt := 'CREATE OR REPLACE VIEW BOFC.DWH_FC_CO_'||lpad(l_co.id, 4, '0')||' (ID, PHASE, UPDPER, SCOPE, VARIANT, CONS_CURNCY, CT_0000_AN, PERIOD, ENTITY, ENTORIG, ACCNT, FLOW, NATURE, PARTNER, CTSHARE, TRANS_CURNCY, TECHORIG, GLOBORIG, JOURNAL, ENUMBER, ROLLUP_PARTNER, ROLLUP, CT_0000_CO, MU, PMU, ROLLUP2, ROLLUP_PARTNER2, CT_0100_MV, CT_0100_AI, CT_0100_A4, CT_0100_CF, CT_0100_SL, CT_0100_CN, CT_0100_CTR, CT_0100_CDV, CT_0100_CFA, CT_0100_CRR, CT_0100_OB, CT_0100_DS, CT_0100_DC, AMOUNT, CONVAMOUNT, CONSAMOUNT, DATA_COMMENT)
AS (SELECT REF.ID, REF.PHASE, REF.UPDPER, REF.SCOPE, REF.VARIANT, REF.CURNCY, co.CT_0000_AN, co.PERIOD, co.ENTITY, co.ENTORIG, co.ACCNT, co.FLOW, co.NATURE, co.PARTNER, co.CTSHARE, co.CURNCY, co.TECHORIG, co.GLOBORIG, co.JOURNAL, co.ENUMBER, co.ROLLUP_PARTNER, co.ROLLUP, co.CT_0000_CO, co.MU, co.PMU, co.ROLLUP2, co.ROLLUP_PARTNER2, co.CT_0100_MV, co.CT_0100_AI, co.CT_0100_A4, co.CT_0100_CF, co.CT_0100_SL, co.CT_0100_CN, co.CT_0100_CTR, co.CT_0100_CDV, co.CT_0100_CFA, co.CT_0100_CRR, co.CT_0100_OB, co.CT_0100_DS, co.CT_0100_DC, SUM (co.AMOUNT), SUM (co.CONVAMOUNT), SUM (co.CONSAMOUNT), co.DATA_COMMENT
FROM BOFC.CT_COREF REF, BOFC.CT_CO'||lpad(l_co.id, 4, '0')||' co WHERE REF.id ='||l_co.id||
' GROUP BY REF.ID, REF.PHASE, REF.UPDPER, REF.SCOPE, REF.VARIANT, REF.CURNCY, co.CT_0000_AN, co.PERIOD, co.ENTITY, co.ENTORIG, co.ACCNT, co.FLOW, co.NATURE, co.PARTNER, co.CTSHARE, co.CURNCY, co.TECHORIG, co.GLOBORIG, co.JOURNAL, co.ENUMBER, co.ROLLUP_PARTNER, co.ROLLUP, co.CT_0000_CO, co.MU, co.PMU, co.ROLLUP2, co.ROLLUP_PARTNER2, co.CT_0100_MV, co.CT_0100_AI, co.CT_0100_A4, co.CT_0100_CF, co.CT_0100_SL, co.CT_0100_CN, co.CT_0100_CTR, co.CT_0100_CDV, co.CT_0100_CFA, co.CT_0100_CRR, co.CT_0100_OB, co.CT_0100_DS, co.CT_0100_DC, co.DATA_COMMENT)';
execute immediate l_base_view_txt;
l_upper_view_txt := l_upper_view_txt||' select * from BOFC.DWH_FC_CO_'||lpad(l_co.id, 4, '0')||' UNION ALL';
end loop;
close co_id;
l_upper_view_txt := rtrim(l_upper_view_txt, ' UNION ALL') || ')';
execute immediate l_upper_view_txt;
exception
when others then
if co_id%isopen
then
close co_id;
end if;
raise;
end DWH_FC_UPDATE_CO_VIEW_DEV;
/Now let’s comprehend appropriate events and triggers to maintain the views in actual condition. Event AFTER DELETE on the table CT_COREF is appropriate for the trigger that will drop the corresponding DWH_FC_CO_NNNN view and recreate the “upper” view DWH_FC_CO. Event AFTER INSERT on the table CT_COREF is inappropriate for the trigger because the row may be inserted into the table CT_COREF and thereafter the table CT_CONNNN is created. Therefore the event AFTER CREATE ON SCHEMA fully satisfies our requirements. But there is another restriction on usage such events to fire the triggers. PL\SQL script cannot contain DDL operators for instance “CREATE OR REPLACE VIEW…” since when it contain DDL operators and the trigger fires you’ll see a message “ORA-00604: error occurred at recursive SQL level string”. To avoid recursive call of the procedures you need to use a mechanism known in Oracle as Jobs. The job will schedule the DDL execution. In our example the procedure is called with 10 seconds delay. Well you can see the PL\SQL scripts for trigger definition below.
Trigger definition script for AFTER DELETE event.
CREATE OR REPLACE TRIGGER DWH_FC_UPDATE_CO_VIEW_DELETE
after delete ON CT_COREF
for each row
begin
if deleting then
dwh_fc_update_co_view_new(:old.id, 'DELETE');
end if;
end;
/Trigger definition script for AFTER CREATE event with a condition for CT_CONNNN tables only.
CREATE OR REPLACE TRIGGER DWH_FC_UPDATE_CO_VIEW_INSERT
after create on schema
WHEN (
ora_dict_obj_type = 'TABLE' and
ora_dict_obj_name like 'CT_CO%' and
length(trim(translate(substr(ora_dict_obj_name,6,4),'0123456789',' '))) is null
)
declare
l_id number;
l_jobno PLS_INTEGER;
begin
-- sequencial number of created table
l_id := to_number(substr(ora_dict_obj_name,6,4),'9999');
-- schedule the job to update view definitions
dbms_job.submit( l_jobno,
'BEGIN dwh_fc_update_co_view_new('||l_id||', ''INSERT''); END;',
sysdate + interval '10' second );
end;
/The procedure “dwh_fc_update_co_view_new” is called in both scripts with two patameters. The first parameter is the CT_CONNNN table number and the second one is the table operation where “DELETE” is for dropping table processing and “INSERT” is for created table processing. Look at the script that defines this procedure.
CREATE OR REPLACE procedure BOFC.DWH_FC_UPDATE_CO_VIEW_NEW (table_id number, operation varchar2) is
pragma autonomous_transaction;
-- running cursor at the list of CT_CONNNN tables
cursor co_id is select id from BOFC.CT_COREF;
l_co co_id%rowtype;
l_upper_view_txt varchar2(32767);
l_base_view_txt varchar2(32767);
begin
IF (operation = 'INSERT') THEN
l_base_view_txt := 'CREATE OR REPLACE VIEW BOFC.DWH_FC_CO_'||lpad(table_id, 4, '0')||
' (ID, PHASE, UPDPER, SCOPE, VARIANT, CONS_CURNCY, CT_0000_AN, PERIOD, ENTITY, ENTORIG, ACCNT, FLOW, NATURE, PARTNER, CTSHARE, TRANS_CURNCY, TECHORIG, GLOBORIG, JOURNAL, ENUMBER, ROLLUP_PARTNER, ROLLUP, CT_0000_CO, MU, PMU, ROLLUP2, ROLLUP_PARTNER2, CT_0100_MV, CT_0100_AI, CT_0100_A4, CT_0100_CF, CT_0100_SL, CT_0100_CN, CT_0100_CTR, CT_0100_CDV, CT_0100_CFA, CT_0100_CRR, CT_0100_OB, CT_0100_DS, CT_0100_DC, AMOUNT, CONVAMOUNT, CONSAMOUNT, DATA_COMMENT)'||
' AS (SELECT REF.ID, REF.PHASE, REF.UPDPER, REF.SCOPE, REF.VARIANT, REF.CURNCY, co.CT_0000_AN, co.PERIOD, co.ENTITY, co.ENTORIG, co.ACCNT, co.FLOW, co.NATURE, co.PARTNER, co.CTSHARE, co.CURNCY, co.TECHORIG, co.GLOBORIG, co.JOURNAL, co.ENUMBER, co.ROLLUP_PARTNER, co.ROLLUP, co.CT_0000_CO, co.MU, co.PMU, co.ROLLUP2, co.ROLLUP_PARTNER2, co.CT_0100_MV, co.CT_0100_AI, co.CT_0100_A4, co.CT_0100_CF, co.CT_0100_SL, co.CT_0100_CN, co.CT_0100_CTR, co.CT_0100_CDV, co.CT_0100_CFA, co.CT_0100_CRR, co.CT_0100_OB, co.CT_0100_DS, co.CT_0100_DC, SUM (co.AMOUNT), SUM (co.CONVAMOUNT), SUM (co.CONSAMOUNT), co.DATA_COMMENT'||
' FROM BOFC.CT_COREF REF, BOFC.CT_CO'||lpad(table_id, 4, '0')||' co WHERE REF.id ='||table_id||
' GROUP BY REF.ID, REF.PHASE, REF.UPDPER, REF.SCOPE, REF.VARIANT, REF.CURNCY, co.CT_0000_AN, co.PERIOD, co.ENTITY, co.ENTORIG, co.ACCNT, co.FLOW, co.NATURE, co.PARTNER, co.CTSHARE, co.CURNCY, co.TECHORIG, co.GLOBORIG, co.JOURNAL, co.ENUMBER, co.ROLLUP_PARTNER, co.ROLLUP, co.CT_0000_CO, co.MU, co.PMU, co.ROLLUP2, co.ROLLUP_PARTNER2, co.CT_0100_MV, co.CT_0100_AI, co.CT_0100_A4, co.CT_0100_CF, co.CT_0100_SL, co.CT_0100_CN, co.CT_0100_CTR, co.CT_0100_CDV, co.CT_0100_CFA, co.CT_0100_CRR, co.CT_0100_OB, co.CT_0100_DS, co.CT_0100_DC, co.DATA_COMMENT)';
execute immediate l_base_view_txt;
ELSE
execute immediate 'DROP VIEW BOFC.DWH_FC_CO_'||lpad(table_id, 4, '0');
END IF;
-- header of the "upper" view
l_upper_view_txt := 'CREATE OR REPLACE VIEW BOFC.DWH_FC_CO (ID, PHASE, UPDPER, SCOPE, VARIANT, CONS_CURNCY, CT_0000_AN, PERIOD, ENTITY, ENTORIG, ACCNT, FLOW, NATURE, PARTNER, CTSHARE, TRANS_CURNCY, TECHORIG, GLOBORIG, JOURNAL, ENUMBER, ROLLUP_PARTNER, ROLLUP, CT_0000_CO, MU, PMU, ROLLUP2, ROLLUP_PARTNER2, CT_0100_MV, CT_0100_AI, CT_0100_A4, CT_0100_CF, CT_0100_SL, CT_0100_CN, CT_0100_CTR, CT_0100_CDV, CT_0100_CFA, CT_0100_CRR, CT_0100_OB, CT_0100_DS, CT_0100_DC, AMOUNT, CONVAMOUNT, CONSAMOUNT, DATA_COMMENT) AS (';
-- gather the script for definition of the "upper" view
open co_id;
loop
fetch co_id into l_co;
exit when co_id%notfound;
IF (l_co.id != table_id) THEN
l_upper_view_txt := l_upper_view_txt||' select * from BOFC.DWH_FC_CO_'||lpad(l_co.id, 4, '0')||' UNION ALL';
END IF;
end loop;
close co_id;
-- addition for NEW table
IF (operation = 'INSERT') THEN
l_upper_view_txt := l_upper_view_txt||' select * from BOFC.DWH_FC_CO_'||lpad(table_id, 4, '0')||' UNION ALL';
END IF;
l_upper_view_txt := rtrim(l_upper_view_txt, ' UNION ALL') || ')';
execute immediate l_upper_view_txt;
exception
when others then
if co_id%isopen
then
close co_id;
end if;
raise;
end DWH_FC_UPDATE_CO_VIEW_NEW;
/When you realize this solution you’ll get DWH_FC_CO view that you can include in your Universe with other diverse master data tables from BFC database schema and get something like in this picture. You’ll get a view resides in the BFC schema named BOFC. You can substitute it to what you need.
Well you may encounter with some hidden pitfalls with work of this mechanism because only developers know how BFC works =) Use it on your own risk.
Kind Regards!
Hello Igor
Interesting way of accessing BFC - but the recommended way is to deploy cubes (MSAS or BW) that can be accessed with BI (WebI) or EPM Excel add-in.
This also adds the BFC authorization (data-access groups)
Marc
Hi Marc!
Thanks for advice! Could you tell there is need extra Microsoft licences for using MSAS cubes?
Kind regards
Igor
Hi Igor,
The following is the requirement for Analysis Server:
Marc