Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 216 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
How to create report on custom logical SQL in OBIEE 11g
Hi,
I want to create a report based on custom logical SQL. I came up with below logical SQL but when I execute this, OBIEE is throwing error: Invalid SQL identifier.
Logical SQL:
SELECT
a."p1" saw_0,
a."p2" saw_1,
a."p3" saw_2,
a."p4" saw_3,
a."p5" saw_4,
b."q2" saw_5,
b."q3" saw_6,
c."r2" saw_7,
c."r3" saw_8
FROM
(SELECT
"- Attribute"."Access Id" "p1",
SUM(CASE WHEN MONTH("- Create Time"."Day Date") = MONTH(DATE '2016-11-11') THEN 1 ELSE 0 END) "p2",
SUM(CASE WHEN MONTH("- Create Time"."Day Date") = (MONTH(DATE '2016-11-11')-1) THEN 1 ELSE 0 END) "p3",
SUM(CASE WHEN MONTH("- Create Time"."Day Date") = MONTH(DATE '2016-11-11') THEN 1 ELSE 0 END) + SUM(CASE WHEN MONTH("- Create Time"."Day Date") = (MONTH(DATE '2016-11-11')-1) THEN 1 ELSE 0 END) "p4",
SUM(CASE WHEN CAST("- Create Time"."Day Date" AS DATE) = DATE '2016-11-11' THEN 1 ELSE 0 END) "p5"
FROM "OAD"
WHERE SUM(CASE WHEN MONTH("- Create Time"."Day Date") = MONTH(DATE '2016-11-11') THEN 1 ELSE 0 END) + SUM(CASE WHEN MONTH("- Create Time"."Day Date") = (MONTH(DATE '2016-11-11')-1) THEN 1 ELSE 0 END) >= 5
AND "Client"."Client Name" = 'Ford Motor Company') a,
(SELECT
"- Attribute"."Access Id" "q1",
"- Attribute"."Create Time" "q2",
"- Transaction Type"."Transaction_type" "q3",
RANK(EVALUATE('F_GET_CREATE_TIMESTAMP(%1)' AS CHAR, "- Attribute"."Create Time") BY "- Attribute"."Access Id") "q4"
FROM "OAD"
WHERE RANK(EVALUATE('F_GET_CREATE_TIMESTAMP(%1)' AS CHAR, "- Attribute"."Create Time") BY "- Attribute"."Access Id") = 1
AND "Client"."Client Name" = 'Ford Motor Company') b,
(SELECT
"- Attribute"."Access Id" "r1",
"- Attribute"."Create Time" "r2",
"- Transaction Type"."Transaction_type" "r3",
RANK(EVALUATE('F_GET_CREATE_TIMESTAMP(%1)' AS CHAR, "- Attribute"."Create Time") BY "- Attribute"."Access Id") "r4"
FROM "OAD"
WHERE RANK(EVALUATE('F_GET_CREATE_TIMESTAMP(%1)' AS CHAR, "- Attribute"."Create Time") BY "- Attribute"."Access Id") = 2
AND "Client"."Client Name" = 'Ford Motor Company') c
WHERE a."p1" = b."q1"
AND a."p1" = c."r1"
Appreciate your help.
Thank You.
Answers
-
- How are you trying to execute it?
- What's your use case? Why can't you build an analysis using the RPD as normal?
0 -
Hi,
My table has data something like below -
CUST_ID DOMAIN CALLTIME REASON
C001 X 2017-01-02 12:30 PM A
C001 Y 2017-01-02 2:46 PM B
C001 X 2016-12-29 11:20 AM B
C002 Z 2017-01-10 1:20 PM C
C002 X 2016-12-28 11:10 AM B
C003 Y 2017-01-05 10:24 AM C
C003 Y 2016-12-30 2:40 PM D
C003 Z 2017-01-05 10:40 AM E
C003 X 2017-01-05 3:20 PM A
In My report, I would like to display something as below -
CUST_ID CallsPrevMonth# CallsCurMonth# Total PrevCallTime PrevCallReason RecentCallTime RecentCallReason
C001 1 2 3 2017-01-02 12:30 PM A 2017-01-02 2:46 PM B
C003 1 3 4 2017-01-05 3:20 PM A 2017-01-05 10:40 AM E
Since Total calls made by CUST_ID C002 is less than 3, hence it'll not be displayed in report.
I tried with RANK() function but was not able to pull previous call Time and Reason. Hence, opting to use 3 SQL blocks joining them and showing results.
Let me know if that is possible or is there any workaround?
Thank You.
0 -
You didn't answer my question (1) above. To your answer to my second question, the 'workaround' would be to use OBIEE as it's generally intended and build your data model such that analyses can be built automatically through the Answers interface. If you're having to resort to hacking together logical SQL you're either misunderstanding how OBIEE should be used, or haven't built your RPD correctly.
0 -
Hi,
To answer your 1st question, I'm plugging this logical SQL in Analysis Simple SQL Statement box. The error then appeared.
I'm not sure how to fetch the previous call time from the data available. RANK() function didn't help in OBIEE.
Is there any way I can fetch previous call time?
Thanks...
0 -
Build your fact table to be able to answer the question the business is asking ... so you have your base table ... just add a routine on your post-load-processing to pivot the data (databases handle data better than the logical smarts of OBIEE) then model this fact into OBIEE.
Even a materialized view in your database will perform much better and the SQL to get what you want is pretty strait forward ...
0 -
Hi Thomas,
Can you please explain how pivoting the data would help me? The user wants to have interactive dashboard with Domain and Reason acting as dashboard prompts.
So, business would like to see data for a particular domain and/or reason and/or no filters applied.
Thanks,
Rituraj
0 -
You can still have domain and reason as prompted values ... the value in the physical structure is PERFORMANCE, reuse and ease of maintenance.
So you end up with two physical structures that can satisfy the requirements ... the original one gives the complete history, the new one with the most recent items on the same row as the 'current' item ... you are still going to use the dimensions etc
0 -
Can you please demonstrate how my new physical table would look like that can answer these questions?
Thanks,
Rituraj
0 -
You have already showed it:
CUST_ID CallsPrevMonth# CallsCurMonth# Total PrevCallTime PrevCallReason RecentCallTime RecentCallReason
C001 1 2 3 2017-01-02 12:30 PM A 2017-01-02 2:46 PM B
C003 1 3 4 2017-01-05 3:20 PM A 2017-01-05 10:40 AM E
Set this up as a snapshot of the baas table at a regular interval (seems monthly; or weekly with rolling month snapshot)
0 -
Hi Thomas,
If my new table contains only the records that I need to show on report, then how will the interaction with Domain dashboard prompt would work. Say, If i select Domain 'X', the there would be no output. I'm not sure with new table structure, as you've mentioned above, how report will work.
Thanks,
Rituraj
0