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

[Deleted User]

Robert Angel wrote: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, 

@Robert Angel really? More important then the concept of logical table sources and content levels?

asim cholas

what is the result for below formula in answers

sum(ColumnA by ColumnB)

Column B Column C   ColumnA  Sum(ColumnA by ColumnB)

Dont apply any aggregation rule for third column and hide it. Let us know the results.

Robert Angel

Okay - touché - maybe it is a triad of most important - but I would see keys / content levels as part of the same fundamental issue...

[Deleted User]

Agreed. The reason I asked is, that most people can kind of comprehend "key" whereas the concept of a single LTS which is made up of 20 different phys layer objects which themselves point to different source technologies is what most just do not grasp.

Robert Angel

The number of times I have seen content level unset on production systems I am not sure that there are even that many that understand the simple concept of LTS, much less the underlying complexities that it allows.

asim cholas

Can somebody help me to understand why cant  a simple group by statement for the issue? The one which i posted above?

user11079646

ok, still not working out, let me try to simplify the example:

dim_sections

- *term_id

- *class_id

- department

- subject

- capacity

fact_enrollment

- *term_id

- *student_id

- *class_id

- enrolled (1/0)

(the fact/dim are joined by class_id and term_id, multiple rows in fact_enrollment will exist for one row in dim_sections)

Now, I want to see the total capacity and the total enrolled.

Problem is, SUM will sum up capacity for every row in fact_enrollment.

Right now, in my business model, I have a "section" hierarchy and I have made a logical column using "last(capacity)" and placed it at the leaf level of the section hierarchy.  Still repeats when I put it in a table with enrollment info.

Banging my head against a wall looking for a way to tell OBIEE that this number SUMs only for unique values of term_id+class_id

asim cholas

can you please provide create and insert script for some sample data, so that we can replicate it here.

user11079646

This will create the two tables I describe and put a few rows in each.

In this example date, I would want to see, in OBIEE, the sum total of the capacity of the unique dim_sections (which adds up to 130 in my example) along with the number of enrollments (sum of enrolled column) (which adds up to 4 in my example).

I will also want this to be broken down by term_id, dept, and subject (I have a hierarchy set up for these).

You will see, the problem is that the "capacity", when joined with the more detailed fact rows, gets double-counted.

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

  CREATE TABLE "BI_WH_DEV"."TEST_DIM_SECTIONS"

   ( "TERM_ID" VARCHAR2(20 BYTE),

"CLASS_ID" VARCHAR2(20 BYTE),

"DEPT" VARCHAR2(20 BYTE),

"SUBJECT" VARCHAR2(20 BYTE),

"CAPACITY" NUMBER

   )

INSERT INTO "BI_WH_DEV"."TEST_DIM_SECTIONS" (TERM_ID, CLASS_ID, DEPT, SUBJECT, CAPACITY) VALUES ('1179', '1', 'ART', 'ARTH', '30')

INSERT INTO "BI_WH_DEV"."TEST_DIM_SECTIONS" (TERM_ID, CLASS_ID, DEPT, SUBJECT, CAPACITY) VALUES ('1176', '2', 'ART', 'ARTH', '30')

INSERT INTO "BI_WH_DEV"."TEST_DIM_SECTIONS" (TERM_ID, CLASS_ID, DEPT, SUBJECT, CAPACITY) VALUES ('1179', '3', 'ART', 'ARTH', '30')

INSERT INTO "BI_WH_DEV"."TEST_DIM_SECTIONS" (TERM_ID, CLASS_ID, DEPT, SUBJECT, CAPACITY) VALUES ('1179', '4', 'BIOL', 'BIOL', '40')

  CREATE TABLE "BI_WH_DEV"."TEST_FACT_ENROLLMENT"

   ( "TERM_ID" VARCHAR2(20 BYTE),

"CLASS_ID" VARCHAR2(20 BYTE),

"STUDENT_ID" VARCHAR2(20 BYTE),

"ENROLLED" NUMBER

   ) ;

INSERT INTO "BI_WH_DEV"."TEST_FACT_ENROLLMENT" (TERM_ID, CLASS_ID, STUDENT_ID, ENROLLED) VALUES ('1179', '1', '231', '1')

INSERT INTO "BI_WH_DEV"."TEST_FACT_ENROLLMENT" (TERM_ID, CLASS_ID, STUDENT_ID, ENROLLED) VALUES ('1179', '1', '123', '1')

INSERT INTO "BI_WH_DEV"."TEST_FACT_ENROLLMENT" (TERM_ID, CLASS_ID, STUDENT_ID, ENROLLED) VALUES ('1179', '1', '111', '1')

INSERT INTO "BI_WH_DEV"."TEST_FACT_ENROLLMENT" (TERM_ID, CLASS_ID, STUDENT_ID, ENROLLED) VALUES ('1179', '1', '222', '1')

asim cholas

Are you looking for the below result?