You are better utilized determining why users want to use OBIEE as a data pump. There are much better tools suited for that.

Tech Siksha: How to Remove Recent and Most Popular dashboard links on the OBIEE home page? ^ if you want to go to the effort to remove the sections off the Home page entirely....

I'm sure @rmoff would love to impart his knowledge -- it's how he makes his living.

+Infinity to @Andrew Fomin. and @rmoff IF there's no analytical, informational or decision-support value in the requirement ... how did it get passed? The request is adding additional steps therefore prolonging the 'trip' to information ... it seems counter to the intent of Business Intelligence.

Build #? Patch#? It's what you'll get asked ... and how to determine if you have a bug (Oracle Support).

I've posted a starter SQL here: Group Account Number Configuration Then you take the ranges of accounts (with the help of a finance person) and you map them in the CSV file. It's a domain file because you aren't sourcing the group mappings from your financial system -- you are 'making them up' to work with OBIA. Those…

This is where you need a dimensional hierarchy to do the aggregation logically and then you'd have the logical column on which to base the advice that you've been given. After having done copious amounts of Financial rollouts with OBIEE, it's imperative to have the account dimension built with the appropriate hierarchies…

Suppression works on dimensional attribute columns, not on aggregate-able fact/measure columns.

That's why I recommended a PHYSICAL structure FIRST ....

Now that you have your snapshot and the date on which it was taken, you can build a logical column which is pinned at the LAST aggregation level (based on snapshot date) and additionally you can logically filter that by your current year minus one.

Your logical model diagram looking like your physical diagram is the problem ... You should one logical fact table with both physical facts as LTS and set levels appropriately. "You cannot make single big ideal star schema in a big project where different facts and dimensions don't relate to each other. " .... Oracle BI…

You need to build the snapshot fact first ... to get you started ... http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techn… Valuable Data: Periodic Snapshot Fact Tables Fact Table - Definition, Examples and Four Steps Design by Kimball

Performance Rule of Thumb is to push as much work back to the database as possible ... yes, you can do it logically, but I can assure you your approach only works for small data sets and it only works for the one tool (OBI) you've implemented it in -- in other words it's a limited approach. I'll never advise you on a…

LISTAGG function in Oracle has the restriction of 4000 characters too ... Given you are blowing through a DB limit, might be the time to redesign a physical structure so you aren't trying to function-ship something like this back and forth over the network. Seems very inefficient, given you could process the data locally…

Use of information drives model design ... You need a snapshot fact table that stores the balances monthly (by whatever other dimensional keys in the grain) ... then you just are looking at the (selected month/year) balance and (month12/year-1) balance from the snapshot table. The logical variance and percent change…

All this documentation is available on Oracle support ... https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=a2m5h4t82_9&_afrLoop=299784868188617 ^ if that's not what you want, you can search for the product guides by version of the apps.

What does TNSPing tell you when you try from the VM across the wire to your Oracle DB? If it time-out, then it might be a firewall issue ... I've run into similar when using a cloud BI tool against a remote Oracle DB.

"Actually I've already implemented this by forcing logical stars. Now i was trying to learn best practices and thought there might be a better approach." the only practice is to conform to logical stars ... there is no other way.

"I've tables with first level facts joined to second level facts and then joined to third level facts" -- not sure I follow you ... you should never have a fact joined to a fact ... 2 basic rules: 1. every measure has a dimensional context 2. all dimensional attributes are related through a fact (for dimensional browsing…

If this diagram is your logical diagram -- then @Christian Berg is correct ... it won't work. Go back through the link you are trying to follow and note that your diagram looks like the physical diagram ... the logical one needs to look like a star, else OBI server is going to choke and give you results like you are…