"Your job is to dig this ditch ... nope, you can't have the shovel" ... I cannot count how many times I've come across this same foolishness!

Welcome to dimensional modeling ... facts (measures) are what is aggregated BY dimensions (attributes) Sales (aggregated fact) by employee (dimensional attribute) by month (dimensional attribute) You set the aggregation rule (default is usually SUM) ... but in the case of an inventory, where you can not add across time,…

capacity is an 'inventory measure' and currently is an attribute in his dimension ... he's got to get it to a fact table first ...

make a logical fact out of dim_sections you care trying to make 'capacity' a measure and you've got it in your dimension (it's both so you need both structures) you have 2 facts and one dimension.

https://www.rittmanmead.com/blog/2016/07/obiee-12-2-1-1-0-new-feature-guide/ "Also included (assuming you have installed and configured Oracle's R distribution) is the ability to add clusters, outliers, reference lines, trend lines and forecast."

sum(last("SSA BI Warehouse"."BI Dim Course Sections View"."Class Section Capacity" by "SSA BI Warehouse"."BI Dim Course Sections View"."Class Number")) This might help explain it a bit: http://www.wegobeyond.co.uk/blog/entry/aggregation-based-on-dimensions-in-obiee.html

SUM of LAST() grouped by Column B

SampleApp uses HTML and linking to create a menu page - drawback is that you'll have to manage it with releases/updates

"I was given a very useful formula in Excel that I am trying to replicate in OBIEE." Replicating formulas is always a goose chase ... replicate intent ... the intent is to have distinct count - then you have basis for which to inspect the developer guide to find as @Gianni Ceresa has indicated

+1 to @Robert Angel Use of information drives design ... you are attempting to back into a deficient design. Robert has given you the 'best, better, worst'. I advise you to pursue #2

Are you wanting to trigger an ETL refresh from an OBIEE page?

Here's an example of using a simple java-script to achieve the same: https://www.jamescoyle.net/how-to/1907-javascript-to-refresh-all-obiee-dashboard-analysis

+1 to @Gianni Ceresa in essence you have the option (or both): Alias the dimension ... customer.ID = Loans.custID groupCustomer.ID=Loans.ParentID ParentCustomer.ID=Loans.GroupID Alias the fact ... CustomerLoans.custID = Customer.ID ParentLoans.ParentID = Customer.ID GroupLoans.GroupID = Customer.ID - with aliased dimension…

When the logical doesn't seem to work .. fall back to the physical -- it's where it all started (and, yes I age myself!)

the most consistent approach to the 53rd week in your date dimension and accounting for it in TY vs LY type analysis is to have the dimension properly formed such that each row has its corresponding LY keys set in ETL logic. this removes the functionality from the BI logic layer and places it in the database layer - not…

This is why I suggested a physical approach and not the logical ... the hierarchy is a purely logical construct

"I know it'll hit the performance but there are cases when you need to listen to business and get things done quickly" Respectfully - this is a case of inmates running asylum. Your business people get to tell you WHAT - never get to tell you HOW. When we are willing to cut quality for the sake of speed -- we set a…

Period is a logical aggregation of week, right? so add the corresponding keys to your dimension ... you need a well formed time/calendar dimension. "This is because 2012 has 53 week and the first week of 2012 should be shifted out in LY metric and the entire week/month relationship in year 2012 has to be adjusted." ^ do…

You have major layers: Database - BI Logic - Presentation "* Make any changes in repository or ETL" ^ so you remove the possibility of the Database & BI Logic layers leaving you with Presentation "* Also, I do not want to put CASE statements with 52 statements for 52 weeks as it will affect the performance." ^ the last one…

Canonical approach has ONE date dimension and you alias the fact multiple times (you join the date key in the fact once to the current date key in calendar and one to the previous period key on the same row in dimension); logically you've conformed the date dimension across two facts - even though you only have one (each…