SQL script problem under Oracle Fusion MiddleWare - Item Aggregation

Question

I am brand spanking new to SQL and Stack Overflow. I am originally an accountant but have recently moved on to financial analytics. I am having trouble understanding how to write SQL to aggregate manually created items under a single column:

Report Draft

From the report draft attached, you'll see there is multiple lines for both Fringe and Other Direct Expenses, I want to make these single lines, but have been trying for weeks with no other help.

Here is my code:

SELECT 
   subjectarea1.ProjectName saw_0,
   subjectarea1.PurchaseOrderAmount saw_1,
   subjectarea1.ProjectID saw_2,
   subjectarea1.BeginDate saw_3,
   subjectarea1.EndDate saw_4,
   subjectarea1.Indirect saw_5,
   subjectarea2.Location saw_6,
   subjectarea2.BudgetID saw_7,
   subjectarea2.budgetamount saw_8,
   subjectarea2.BudgetAcount saw_9,
   subjectarea1.Credit saw_10,
   subjectarea1.Debit saw_11,
   subjectarea1.GLaccount saw_12,
   subjectarea1.Debit - subjectarea1.Credit saw_13,
   subjectarea2.budgetamount - (subjectarea1.Debit - subjectarea1.Credit) saw_14,
   subjectarea1.Account_Category saw_15,
   subjectarea2.Account_Category2 saw_16,

   REPORT_AGGREGATE("subjectarea1"."Debit"-"subjectarea1"."Credit" BY "subjectarea1"."Account_Category"),
   REPORT_AGGREGATE("subjectarea2"."budgetamount"-("subjectarea1"."Debit"-"subjectarea1"."Credit") BY "subjectarea1"."Account_Category")
 FROM (SELECT "gl:gl detail"."project"."SIHB:PROJECT_INDIRECT_RATE" Indirect,
"gl:GL Detail"."General ledger detail Attributes"."ACCOUNTNO" GLaccount,

CASE WHEN "gl:GL Detail"."General ledger detail Attributes"."ACCOUNTNO" IN ('45010','50010') THEN 'Direct Revenue' WHEN "gl:GL Detail"."General ledger detail Attributes"."ACCOUNTNO" IN ('45020','50020') THEN 'Indirect Revenue' WHEN "gl:GL Detail"."General ledger detail Attributes"."ACCOUNTNO" ='70010' THEN 'Salaries' WHEN "gl:GL Detail"."General ledger detail Attributes"."ACCOUNTNO" IN ('70020','70030','70040','70045','70050','70055','70060','70070','70080','70090','70100','70110') THEN 'Fringe' WHEN "gl:GL Detail"."General ledger detail Attributes"."ACCOUNTNO" BETWEEN '70120' AND '89999' THEN 'Other Direct (AP Expenses)' WHEN "gl:GL Detail"."General ledger detail Attributes"."ACCOUNTNO" ='90000' THEN 'Indirect Expense' WHEN "gl:GL Detail"."General ledger detail Attributes"."ACCOUNTNO" IN ('40010','40020','40030','40040','40050','41010','41020','41030','41040','41050','41060','43000','43010','44010','45030','45070','50030','51010','51015','51020','51030','51033','51035-001','51035-002','51035-003','51035-004','51035-005','51035-006','51035-007','51035-008','51035-009','51035-010','51035-011','51035-012','51035-013','51035-014','51035-015','51035-016','51035-017','51035-018','51035-019','51035-020','51035-021','51035-022','51035-023','51035-024','51035-025','51035-026','51035-060','51035-062','51040','51050','51060','51070','51075','52010','52020','53010','53020','53030','54010','54020','54030','58010','58020','59999') THEN 'Other Revenue Accounts' WHEN "gl:GL Detail"."General ledger detail Attributes"."ACCOUNTNO" ='98010' THEN 'Capital Asset (Grant)' ELSE "gl:GL Detail"."General ledger detail Attributes"."ACCOUNTNO" END Account_Category,

"gl:gl detail"."Project"."name" ProjectName,
"gl:gl detail"."project"."poamount" PurchaseOrderAmount,
"gl:gl detail"."Project"."projectid" ProjectID,
"gl:gl detail"."project"."BEGINDATE " BeginDate,
"gl:gl detail"."General ledger detail Measures"."CREDITAMOUNT" Credit,
"gl:gl detail"."General ledger detail Measures"."DEBITAMOUNT" Debit,
"gl:gl detail"."project"."ENDDATE" EndDate
FROM "gl:GL detail") subjectarea1 left outer join (SELECT  "gl:GL Budget"."GL budget Attributes"."BUDGETID" BudgetID,
"gl:GL Budget"."Project"."Locationname" Location,
"gl:GL Budget"."GL budget Measures"."AMOUNT" budgetamount,
"gl:GL Budget"."GL budget Attributes"."ACCT_NO" BudgetAcount,

CASE WHEN "gl:GL Budget"."GL budget Attributes"."ACCT_NO" IN ('45010','50010') THEN 'Direct Revenue' WHEN "gl:GL Budget"."GL budget Attributes"."ACCT_NO" IN ('45020','50020') THEN 'Indirect Revenue' WHEN "gl:GL Budget"."GL budget Attributes"."ACCT_NO" ='70010' THEN 'Salaries' WHEN "gl:GL Budget"."GL budget Attributes"."ACCT_NO" IN ('70020','70030','70040','70045','70050','70055','70060','70070','70080','70090','70100','70110') THEN 'Fringe' WHEN "gl:GL Budget"."GL budget Attributes"."ACCT_NO" BETWEEN '70120' AND '89999' THEN 'Other Direct (AP Expenses)' WHEN "gl:GL Budget"."GL budget Attributes"."ACCT_NO" ='90000' THEN 'Indirect Expense' WHEN "gl:GL Budget"."GL budget Attributes"."ACCT_NO" IN ('40010','40020','40030','40040','40050','41010','41020','41030','41040','41050','41060','43000','43010','44010','45030','45070','50030','51010','51015','51020','51030','51033','51035-001','51035-002','51035-003','51035-004','51035-005','51035-006','51035-007','51035-008','51035-009','51035-010','51035-011','51035-012','51035-013','51035-014','51035-015','51035-016','51035-017','51035-018','51035-019','51035-020','51035-021','51035-022','51035-023','51035-024','51035-025','51035-026','51035-060','51035-062','51040','51050','51060','51070','51075','52010','52020','53010','53020','53030','54010','54020','54030','58010','58020','59999') THEN 'Other Revenue Accounts' WHEN "gl:GL Budget"."GL budget Attributes"."ACCT_NO" ='98010' THEN 'Capital Asset (Grant)' ELSE "gl:GL Budget"."GL budget Attributes"."ACCT_NO" END Account_Category2,

"gl:GL Budget"."Project"."PROJECTID" ProjectIDD FROM "gl:GL Budget") subjectarea2 ON (subjectarea1.ProjectID = subjectarea2.ProjectIDD) AND (subjectarea1.GLaccount = subjectarea2.BudgetAcount)

 WHERE 
(subjectarea2.BudgetID = 'Budget') AND (subjectarea1.ProjectID = '131-20') AND (subjectarea1.GLaccount BETWEEN '70010' AND '90000') AND (subjectarea2.BudgetAcount BETWEEN '70010' AND '90000')
 ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4, saw_5, saw_6, saw_7, saw_9, saw_12

Anyone know what im doing wrong here?

I tried to create an aggregate within an aggregate, but found that is not possible with oracle fusion middleware. Also, I understand that this is probably a simple problem for many, but I've gotten desperate. Any help is much appreciated!

Answer 1

This is not really an answer, but I recognize SQL generated by what used to be called Siebel Analytics Warehouse, then Oracle Business Intelligence, and just Oracle Fusion, I guess. Great tool until you have to really understand what's going on, and then it's hell on earth to try to fix what seem like minor problems.

I have a lot of sympathy for your problem, so let me first ask if you generated this SQL yourself through the OBIEE front end tool (Create Analysis) or did you inherit this SQL from someone else generating it in the tool? Because if you generated it yourself, the best thing you can do is go back to the tool you generated it in and look for something called "Aggregation Rules" and go add some for this query. (It's been a lot of years since I've used OBIEE and supported this tool, so forgive me if I'm not too specific here. I just don't remember the details of writing queries in it.)

Your problem here is that no part of your query is aggregating anything together. This means you are getting one report line for every record in your data base. When you turn on aggregation correctly, it will start summing up your measures across your attributes. But remember, the more attributes in your query the finer the level of detail in the number of rows. If you want one number for each category, then category needs to be your sole attribute in your query. There are ways to turn your other report attributes into measures, but you'll want to think about what do I want to show when, for example, I'm aggregating multiple budget line item names within a single category.

Since you're a complete SQL newbie, let me leave you with this simple primer and a really high level analysis of what OBIEE thinks it's doing to give you your results:

SQL queries are all keyword-driven, and the keywords must appear in a fixed order. They ALL start with SELECT followed by a list of table columns and calculations to be included in your results. An example of such a calculation in your query issubjectarea1.Debit - subjectarea1.Credit. The next keyword is FROM followed by a list of tables connected by their join relationship. Your OBIEE administrator created these table relations when he/she defined the subject area. One table your query references is "gl:GL Budget". The next keyword is WHERE followed by a set of filter expressions to only include the appropriate raw data. Your WHERE filter contained the clause subjectarea1.ProjectID = '131-20' indicating your report was for only one project, 131-20. The next keyword is GROUP BY followed by a list of the columns you want to aggregate by. Since there was no Group By in your query I could tell that there was no aggregation so you're getting all the detail that's available. Lastly you have ORDER BY followed by a list of columns to order the record display by.

At a really high level your query is doing something like this:

SELECT <some stuff separated by commas>
FROM (
       SELECT <other stuff>
       FROM "gl:GL Budget"
     ) Left Outer Join (   <1st half of relationship spec>
       SELECT <more stuff>
       FROM gl:GL Budget"
     ) On (subjectarea1.ProjectID = subjectarea2.ProjectIDD) AND (subjectarea1.GLaccount = subjectarea2.BudgetAcount)
WHERE (BudgetID = 'Budget') AND (ProjectID = '131-20')
ORDER BY <your first seven columns & ninth & twelfth>

Once you get the aggregation rules sorted out, I would expect to see a Group By section get added in.

Hope this helps. Like the tape recorder said in Mission Impossible, "Good Luck, Jim".