Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 77 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Achieving Highly Customized Layout For Financial Reports in OBIEE/OBIA
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
Answers
-
Having had to do this at multiple clients ... I would suggest you build a custom physical structure to hold the data in that format - then just present it via OBIEE. You will spin your wheels a lot more trying to rig the presentation/logical layers to do it ...
0 -
Dear Thomas,
Thanks for quick response.
I have tried building custom dimension where I tried to include calculated members as a row but couldn't get the success. My group account table is joined with fact table and not sure how I can join calculated dimensions rows in the group account dimension with Fact.
Can you please explain little more how to achieve this?
Regards
Ali Mostafa
0 -
Custom FACT table (it's really a snapshot) ... and/or custom dimension (if your GL Account dim isn't cutting it) to achieve this
0 -
-
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.
0 -
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
0