I have a column A, that I want to sum, but since the rows repeat, I only want to sum the values for the distinct values of column B. Said another way - In the table, there may be many rows for a given value of B, but I only want to sum the value in A for distinct values of B. I am certain that the value of A will not be different for any given value of B. Example table: Column AColumn BColumn C 20abcother interesting stuff20abcmore interesting stuff30defhere30defis30defsome30deftoo So, I want a logical column that will total "50" for this data.... the 20 plus the 30. I cannot find a function that will allow me to specify this in a logical column in the repository. Any ideas? Thanks, Scott

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, the aggregation rules needs to be set at LAST (most recent count).

Asim,by That is exactly the formula I want - but OBIEE won't let me put that syntax in the expression for a logical column. I get the following error: "[nQSError: 27002] Near : Syntax error [nQSError: 26012] ." Thomas, I created a new logical table called "fact_sections" and added in the logical columns for the key and the "capacity", my measure. Initial reports are looking like it is aggregating correctly. I will finish up my testing and post back. So.... it seems like measures will naturally just aggregate at the granularity of the fact table they are in - is that true?

Oracle Communities

Oracle Analytics and AI

Child Item

Create a Logical Column to Sum one column for distinct values in another column

user11079646

I have a column A, that I want to sum, but since the rows repeat, I only want to sum the values for the distinct values of column B.

Said another way - In the table, there may be many rows for a given value of B, but I only want to sum the value in A for distinct values of B.

I am certain that the value of A will not be different for any given value of B.

Example table:

Column A	Column B	Column C
20	abc	other interesting stuff
20	abc	more interesting stuff
30	def	here
30	def	is
30	def	some
30	def	too

So, I want a logical column that will total "50" for this data.... the 20 plus the 30.

I cannot find a function that will allow me to specify this in a logical column in the repository. Any ideas?

Thanks,

Scott

Find more posts tagged with

Accepted answers

All comments

Thomas Dodds

SUM of LAST() grouped by Column B

user11079646

I can't find any syntax that supports those functions when building a logical column in the Business Model.

Closest I can get is this:

sum(last("SSA BI Warehouse"."BI Dim Course Sections View"."Class Section Capacity" ))

I'm not sure how to add in this part:

group by "SSA BI Warehouse"."BI Dim Course Sections View"."Class Number"

Thomas Dodds

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

user11079646

Thomas,

I think that article is referring to a way to provide different aggregation for different hierarchies. I don't need more than one method.

I've tried to place that code into the Expression Builder for a logical column, but it fails with the following message:

"[nQSError: 27002] Near <by>: Syntax error [nQSError: 26012] ."

So, it does not like the use of "by" here. I've tried in the column source for a logical column as well as in the aggregation formula for the regular column.

Version 12.2.1 of the BI Admin tool, if that helps.

Robert Angel

Hi,

I might be misunderstanding your requirement, if so please post some brief illustrative data, but have you thought of pinning a copy of a base measure to the dimension hierarchy represented by B - "A Aggregated by B". You could create an alternative drill path for this purpose if one does not exist.

Hope this helps?

Robert.

user11079646

Here are more specifics of the problem.

I have a table with one row per "class". In it, I have a column for capacity of that class. So, it is often something between 20 and 200 depending.

I have another table with one row per enrollment in classes (so, one person might have many rows in this table... likewise, any one class will be represented with multiple rows in this table).

So, I am counting the enrollments and want to compare to capacity. When joining the two tables, I don't want to sum the capacity from every row since that would be adding duplicates.

Not sure if that scenario helps or muddles it.

For any set of rows, I want to do exactly what the first responder wrote.... sum the last of capacity by section. That would do it, but I cannot figure out how to tell that to obiee. in either a logical column, or in any of the other features with dimensions

Thanks for any help you can provide!

Scott

Robert Angel

I stand by my original answer, provided you have decent dimension hierarchies then you can use them to say that another fact only relates to it at total level, or by a specific key level. You do this by going into the LTS and making sure that the dimension and fact have the correct settings.

If you have ever seen OBIA then Oracle solved the same problem by joining pretty much everything to everything, but with total being the level that the facts are set to where the join to the dimension in question is spurious.

Martin van Donselaar

So, the result you are looking for would be something like this?

Capacity | Enrollments | Available

Class A 20 13 7

Class B 30 25 5

---------------------------------------

50 38 12

I still think Thomas' solution should work and it is the best solution IMO.

Alternatively, you could model this having a 'Capacity' Fact and an 'Enrollment' Fact and put the measures together over the conformed dimension 'Class'

user11079646

I am still struggling with this.

I am trying to establish this in the setup of the Business Model and maybe thats the wrong place... but thats where I am.

I am trying to provide a measure in the repository that SUMS as its normal aggregate function across any of the dimensions, BUT, it must only sum for rows with distinct values of another column (the ID column).

Examples: Show the sum total of a class capacity measure when joined with enrollments in that class. The resulting table of data would contain one row per enrollment, which would mean the class capacity is repeated many times.

I have a hierarchy setup for class sections. I'm still not clear on how to tie this capacity measure to that hierarchy (at what level, the leaf, or grand total) and which aggregation measure to specify (last, or sum).

I will continue to try variations of those. Thank you for all of your ideas, I wish I understood this a little more deeply.

Scott

Robert Angel

You are in the right place in the business model.

What I am trying to tell you is that you can take your base measure, create a copy, and drag it to associate it with a specific hierarchical level in the dimension hierarchy.

So, for your value that has duplicate IDs, create a dimension hierarchy that includes that in one of its levels, with that value as the key.

Associating the cloned measure with that level will then tell OBIEE that the measure is invariant at that level.

The keys are the key in OBIEE, by a country mile they are the most important, fundamental concept which will bite you if you do not get your head around it, but which can also be greatly used to your advantage once you do.

Hope I have clarified your waters rather than muddying them. See the below if not...

https://docs.oracle.com/middleware/12212/biee/BIEMG/GUID-3383503F-FBDA-4EB4-99B6-C2E7C3B5F24B.htm#hpp_l_level