BI Publisher ... OBIEE is for on-screen interaction with data.

If a data value does not exist for a unique combination of members, Essbase gives the combination a value of #MISSING. How can you calculate where nothing exists? examples: X + #MISSING = X #MISSING - X = -X X * #MISSING = #MISSING X / #MISSING = #MISSING Not sure what your end game is on this one...

Your issue is because you've logically snowflaked ... take your physical snowflake and reduce it so you don't have the logical snowflake (fold it into the D1 logical dimension - as an LTS)

did you add this new role as a member of BIConsumer Role so it gets the basics?

Perhaps ask in the ODI forum .. no doubt there are ETL people who've transition between the tools.

Use Usage Tracking ... don't start handing out Admin level anything to users!

Use NQ_SESSION.LOGLEVEL variable https://gerardnico.com/wiki/dat/obiee/obis/loglevel

This is not the way to do Multi-User Development ... you can do branch development with OBIEE and everything works - no stepping on toes. Having separate physical, logical and presentation layers for each developer is a nightmare and you lose the benefits of OBIEE. 

Hence the main issue with build facts logically at report run time ...

Every fact has a dimensional context Every dimensional attribute is related through a fact (conformed dimensions & implicit facts) Use of information drives fact star design (typically a related set of business questions are answered by a single fact star) + to @Christian Berg ^ the basic rules

BLOB/CLOB not a BI datatype ... XMLPublisher does support it ... but assuming 11g... Let's OBIEE: Handling Large Data Objects In OBIEE11G

Properly formed calendar and date dimensions is what you need ... then you can do Gregorian, fiscal, holiday (by nation/culture), marketing, etc ... When doing analytics and BI it's the FIRST thing built ... EVERYTHING after built after that relies on properly formed calendars and dates. We live in TIME and SPACE ...

Why not use a ZERO?

Do you have a hierarchy of Taxi level -> Taxi? It's unclear ... if you do, then counting at the higher level I don't expect would ever give you a null ... I'd restructure the data to answer the question - doing the logic at fetch time (or after) may not give you the desired results.

You are much better off 'architecting' a physical data structure that records the PRODUCT, DAY, QUANTITY, PREVIOUS DAY QUANTITY, LAST NON-ZERO DAY Have a row for EVERY Product and Day ... now you have no nulls only zeros for appropriate day. PREVIOUS DAY QUANTITY = LAG(QUANTITY) OVER(PARTITION BY PRODUCT ORDER BY DAY) LAST…

LAST() where value <= prompted value

Why aren't the codes in the dimension table along with the department names?

Perhaps something in here might help you... Expanded view of hierarchy

logical column with a 1 or 0 based on the criteria of quantity by day ... then sum up the new logical column over time ...

If you truly want a hierarchy of those three then based on the transactional relationships it's pretty straight forward to build a flattened hierarchy table then import into OBIEE and model.