Build a proper calendar dimension ...

Perhaps the following is what you might need: XDO_SKIPROW_?Company_Name_ID12? <xsl:if test="string-length(Company_Name_ID12) = 0">TRUE</xsl:if>

What version of OBIEE (11g has a ton of releases) and did you check the certification matrix for your version?

https://www.rittmanmead.com/blog/2016/12/enhanced-usage-tracking-for-obiee-now-available-as-open-source/ ^ perhaps there's something here that you can use ...

They are contained in the SampleApp RPD and Catalog .. it's a snap to extract them from there and enhance. I've never seen anything OOTB from Oracle on it.

This is looking more and more like a star modeling issue than a simple formula with a fetch/aggr ordering issue.

Perhaps in addition to the search functionality have additional Prompts to reduce the employee ID count (region, company, department, function, etc)... Millions of potential values in a selector for an analytics system is missing the point of the design and intent of the system.

Did you try calculating the days in a physically mapped column then performing the aggregate? I wonder if you are getting fetch/Aggr order issues.

along those lines ... NO DW dimension should ever have a NULL and NO fact should ever not have a corresponding key join ... So if Gender dim is key | code | descr 1 | M | Male 2 | F | Female 3 | U | Unspecified then always add -1 | <UNASSIGNED> | Missing <-- used in ETL when you get a null value -2 | <FUTURE> | Future <--…

The short of it is: FACT -> A -> B is physical snowflake OBIEE requires a logical star lFACT -> lDim (A + B)

Sigh ... I tried Joel, I tried ...

@Joel Acha has given you the only correct answer unless you are looking at a totally different vendor ... give him all the laud (and points)

Forcing the query engine to write bad/inefficient SQL to boot ...

Section 4-7 in the documentation covers your bullet of: - Install the ODI based ETL Sub section 4-21 describes the RCU involved with ODI ...

Where are you storing the data? OBIEE displays information based on logical models imposed on physical data sources ...

Message me if you want a few contacts - states side ... but Mr. Berg is a fantastic resource of OBIEE knowledge.

Gianni has answered, but - injecting SQL into this mix is not the way to go. You need to build a logical column that evaluates the LAST() based on the chronological dimension you've already got set up.

Since you are using OBIA, be absolutely sure you follow the guide lines for extensions and customizations ...

Good point ... track usage of the information (that's where the value is) ...

LAST() gives you the most recent data point at the intersection of your selected dimensions (time being mandatory). If you have a NULL at an intersection and it's the most recent then that's what's going to be returned - hence Gianni asking his question. He's managing your expectations