In this post I cover (briefly) connecting Discoverer Plus relational to the EUL. I used the connect directly option in the Discoverer connections page to connect to the 'AA' EUL hosted on the XE database. After Plus launched, I verified that I was indeed connected as user 'AA' against an XE database to the 'AA' EUL. The reason I verified this every time connected is because I have three databases running on my laptop: a 10.1.0.4 infrastructure database, another 10.1.0.4 database for OLAP (I don't want to use the infrastructure database for OLAP, even though it is an enterprise edition version with OLAP, Data Mining, and Partitioning options available), and now the XE database (10.2.0.1). It would do me no good if I made a mistake when entering the connection details and ended up connecting to one of the other two databases (my tnsnames.ora file has entries for all three databases).
Having verified that Plus was connected to an XE database ("Oracle Database 10g Express Edition" to be propah), I now wanted to see if I could create a simple report, then proceed to create some analytic calculations, share the workbook, schedule it - basically do some basic sanity testing. Oh, and yes, open the workbook in Viewer, and go through the typical steps that a user would go through.
A simple tabular report with Region, City, Profit and Sales turned up nicely. A multi-pie chart added to the appeal!
On to creating an analytic calculation. Here is the first analytic calc being created - a rank of cities by Profit. The calculation itself is:RANK() OVER (ORDER BY Video Analysis Information.Profit SUM DESC)
Discoverer itself would not perform this calculation: it would simply include the sql in the worksheet SQL and pass it to the database. Therefore, if this worked, it would mean that XE was also able to compute SQL analytic functions.
Added a second calculation for share of Sales by city for all regions, added a stoplight format each to the two analytic calculations, edited the sheet title, renamed the sheet, saved it, re-opened it, and everything worked like clockwork!
Also opened the worksheete in Viewer, made some layout changes, saved the changes back, and re-opened the worksheet in Plus.Here is the actual SQL that Discoverer generated:
For readability, I copied this, removed a couple of items, and ran this on my XE database using SQL*Plus:SELECT o100027.CITY as E100123,o100027.REGION as E100155,RANK() OVER(PARTITION BY ( GROUPING_ID(o100027.CITY,o100027.REGION) ) ORDER BY ( SUM(o100026.PROFIT) ) DESC ) as C_2,SUM(o100026.PROFIT) as E100153_SUM,RATIO_TO_REPORT(( SUM(o100026.SALES) )) OVER(PARTITION BY ( GROUPING_ID(o100027.CITY,o100027.REGION) ) ) as C_1,SUM(o100026.SALES) as E100161_SUM,GROUPING_ID(o100027.CITY,o100027.REGION) as GID
FROM VIDEO5.SALES_FACT o100026, VIDEO5.STORE o100027
WHERE ( (o100027.STORE_KEY = o100026.STORE_KEY))
GROUP BY GROUPING SETS(( o100027.CITY,o100027.REGION ),( o100027.CITY ))
HAVING (GROUP_ID()=0)
ORDER BY GID DESC;
SQL*Plus: Release 10.2.0.1.0 - Beta on Mon Nov 14 12:41:42 2005So far, I have not found anything that hasn't worked on XE; but then again, I have not tried out a whole lot of things that depend on functionality that may not be available on XE. Partitioning for one is not available on XE, nor is Data Mining and OLAP functionality. More in the next post.
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta
SQL> SELECT
2 o100027.REGION as Region,
3 o100027.CITY as City,
4 RANK() OVER(ORDER BY ( SUM(o100026.PROFIT) ) DESC ) as Profit_Rank,
5 SUM(o100026.SALES) as Sales FROM VIDEO5.SALES_FACT o100026,
6 VIDEO5.STORE o100027
7 WHERE
8 ( (o100027.STORE_KEY = o100026.STORE_KEY))
9 GROUP BY o100027.CITY,
10 o100027.REGION
11 ORDER BY
12 o100027.REGION ASC
13 ;
REGION CITY PROFIT_RANK SALES
-------------------- ------------------------------ ----------- ----------
Central Cincinnati 4 204164.95
Central St. Louis 8 105522.13
Central Chicago 14 50347.5
Central Minneapolis 15 46624.8
Central Louisville 5 174112.57
Central Nashville 16 40403.89
Central Dallas 17 39707.8
East Atlanta 13 93476.72
East Boston 9 92032.79
East Philadelphia 7 108908.1
East Washington 6 175790.69
REGION CITY PROFIT_RANK SALES
-------------------- ------------------------------ ----------- ----------
East New Orleans 12 48117.43
East Pittsburgh 11 87643.18
East Miami 20 41603.33
East New York 1 396408.26
West Los Angeles 19 34782.61
West Phoenix 18 42363.74
West Denver 10 90674.14
West Seattle 3 179786.16
West San Francisco 2 182809.33
20 rows selected.
SQL>
Related posts: