Achieving Highly Customized Layout For Financial Reports in OBIEE/OBIA

Question

Dear Team ,

We have requirements to design P&L Report on top of OBIA with customized layout by business team. I am attaching the layout for your reference.

We are using P&L group accounts dimension and GL Balance/Budget Facts for the same. As per the layout we need to have some custom calculations on top of group accounts to satisfy the layout, For example we need to calculate Product Margin which is Sales + COGS.

Further Group Account names are sorted by a custom key column in repository.

We have tried using selection stesps (creating calculated fields/Groups) in pivot table layout which return correct data as per the expectations. However we are not able to sort calculated fields in selection steps to form desired layout. Further ordering in selection steps seems to be a limitation in OBIEE as per MOS note Doc ID 2040416.1

Please suggest how we can achieve this layout. We are okay to made changes in repository or warehouse for the same.

Regards

Ali Mostafa

Naeem Akhtar · Answer

Below steps may help.

* Duplicate group account dimension in warehouse.
* Add a new column named Custom Group Account Name.
* Add calculated row name in custom group account. in you case product margin & commercial margin etc rows will be inserted. 
* Calculated rows WIDs will be inserted repeatedly at row level for as many times as number of group accounts in calculated row. For example Sales ROW_WID is 1000 and COGS ROW_WID is 1010 then Product Margin will have two additional rows with ROW_WID as 1000 and 1010.
* Copy description of all PL accounts to new column created in step 2
* Join this dimension with facts in physical layer, import this as logical dimension and set hierarchy and levels on it.
* Add group account content levels in Facts LTS so that aggregation work for newly created group account dimension.
* Expose this to presentation layer.
* Use custom group account name filed in your report.
* Build ETL for new dimention population.

It should work as "many to many" relation with your facts.

cheers

Naeem

Thomas Dodds · Answer

Too many fact tables?  I doubt it ... a fact star (singe fact table with dimensions) answers a specific question or set of related questions about the business.  Given that foundation - what I hear is "the business asks too many questions" when I hear "too many fact tables".

Having solved this exact issue more than once for many different sized businesses - the way to go is a custom fact table AND POSSIBLY an additional dimension to solve it.

Ali M Mostafa · Answer

Dear Tomas,

I have too many fact tables , it's difficult to make replicate theses fact tables . do you any suggestion to make only custom dimension  without change in fact tables?

Regards,

Ali