Thursday, October 27, 2005
Article: Oracle to pitch open-source database against Microsoft and Sybase
And, on another note, Oracle now has a consolidated page where you can read about its recent acquisitions, all on one page - http://www.oracle.com/corporate/acquisition.html
In case you had lost count, the companies listed are PeopleSoft, Retek, TimesTen, ContextMedia, Siebel, Innobase, Oblix, TripleHop, ProfitLogic, i-flex, and G-Log.
I am off on vacation next week. It's holiday season in India; three days of holidays in Banglore, and then a couple of week after that are the Thanksgiving weekend in the US. And then there's Christmas and New Year - I like this part of the year :)
Update: corrected a typo in the blog title.
Monday, October 24, 2005
More with the Spreadsheet Add-In - 10.1.2.1
Taking the example below, I have a page-item in my query, Year, that has five values. I can use the context menu and click the option 'Expand Pages to Worksheets'.

The Add-In creates a new workbook, and creates one sheet for each page-item value. To help users even more, it adds as the first sheet a table of contents, with links to all the sheets in the workbook.

This, the TOC, can be very useful if you have a lot of page-item combinations. See below: Year has five values, and the Region dimension at the given hierarchy has six members. Using simple arithmatic, 5x6 equals thirty (30).


And 30 can be a lot of sheets.


A TOC therefore is much more appreciated in this example. As you can see below, every combination of page-item values generated is linked, and the value combination listed for the user's benefit. Furthermore (and there really seems to be no end to the number of small but very useful things here), the members of the geography dimension are indented for readability.

The Discoverer OLAP screenshot with the option to export all or selected combinations of page-items.

Friday, October 21, 2005
Using the Spreadsheet Add-In - 10.1.2.1
Once you have started the add-in, you have to connect to an Oracle OLAP data source. You have to define a connection first - which is the host name, port number, and the SID.

You can define multiple data sources in this manner, and using the 'OLAP Connection' dialog you select from one of those defined connections. Enter the user name, password, click 'Connect' and the familiar query builder is displayed upon a successful authentication.

While the Add-In attempts to establish a JDBC connection to the Oracle OLAP server you even get to see a nice little animation.

The familiar Query Builder is displayed - the same as what you see when using Plus OLAP. Because it is the same, that's why! Cool, isn't it? :-)

Anyway, I won't go into the intricacies or mechanics of query creation. Once the query displays in the Excel sheet, you can right-click any cell in the query to be displayed a context-menu, like the one shown below.

Now, there is no practical restriction on the number of OLAP queries you can have in a single sheet in Excel, or within an Excel workbook. This means that you may not know how many queries you have, or where they are. This is where the 'Properties' option from the Add-In context menu (or even from the menu) comes in handy. It will list how many OLAP queries in how many sheets you have in the current workbook. And in the details tab you get to see the connection details, cell location, and last-refresh date-time for each query.


While recognizing that people who refuse to evolve out of Excel need therapy (I am quoting an analyst here), I also recognize that habits die hard, especially those formed as a result of certain market anomalies... So if you have an OLAP query, you can add totals to it, Excel charts, etc...


Oracle's executive blogs
For now you have blogs by John Wookey, Cliff Godwin, and Jesper Andersen. These don't appear to be in the traditional blog format, and don't seem to have been updated in almost a month, but I still hope that blogging will be recognized and organized as a legitimate and important channel of reaching out to our customers and user community. I believer blogging, by being more immediate and less formal, plays an important part in reaching out to a segment of users who may not be adequately covered by the existing forms of communication. Product management and development have an important part to play here, so here's to a long and popular OracleBI blog!
Thursday, October 20, 2005
Installing the Spreadsheet Add-In - 10.1.2.1
Once you click on the installable .exe file (OBISpAddinInst_10.1.2.1.47.exe), the first screen displayed is fairly simple - with two buttons, one to start the install, the other to cancel.

A default path is displayed, and you have the option of changing it.


A log file of the install process is written to "<install>\OracleBIAdd-InInstall.log" - which can be useful in the event of an unsuccessful installation.




If you keep the 'View Supplemental Information' box checked, this page below is displayed upon clicking the 'Finish' button. The 'Quick Tour Demo' link launches a short Flash based demo of the Spreadsheet Add-In.

New versions of AWM and Spreadsheet Add-In available
AWM
- Analytic Workspace Manager 10.2.0.1.0A is now available. You can find it on MetaLink as PS# 4656244 or OTN at http://www.oracle.com/technology/products/bi/olap/index.html.
- What is AWM 10.2.0.1.0A? It is a patched, stand-alone client that supercedes the version found on the Oracle Database Client CD for 10gR2.
- What's the background on AWM 10.2.0.1.0A? It was initially created to fix issues while connecting to Oracle OLAP 10.1.0.4.0 but over time picked up some other fixes.
- Which Oracle OLAP versions does AWM 10.2.0.1.0A work with? It works with Oracle OLAP 10.1.0.4.0 and above on the Oracle Database 10gR1 code stream and Oracle Database 10.2.0.1.0.
- Does AWM 10.2.0.1.0A require a corresponding Oracle OLAP 'A' patch to the database? No
- Is there scheduled an AWM 10.1.0.5 or any other future 10gR1 version? No, AWM 10.1.0.4 users can move to AWM 10.2.0.1.0A.
- You can download the file here. The readme is available here.
A new version (10.1.2.1) of the OracleBI Spreadsheet Add-in is available for download from the product page:
http://www.oracle.com/technology/products/bi/spreadsheet_addin/index.html
It contains mainly bugfixes and performance enhancements. Please read the release notes for more information.
The readme is available here.
Monday, October 17, 2005
Oracle Data Miner 10gR2 beta now available, with a Discoverer bridge
This also has a Discoverer bridge, so you can now directly push results to Discoverer.
Oracle Data Miner Release 2 introduces the popular Decision Tree algorithm for classification problems that can provide human readable “IF…, THEN… rules” that communicate the relationships discovered by ODM. The new Anomaly Detection algorithm flags rare events and supports fraud and compliance monitoring. Oracle Data Miner improves support for assembling the data including unstructured “text” data for mining. Oracle Data Miner also supports PREDICT and EXPLAIN “one-click data mining” predictive analytics. Release 10gR2 adds Receiver Operating Characteristics support for model evaluation and tuning. Oracle Data Miner can automatically generate the Java and SQL components needed to transform the data mining steps into an integrated data mining/BI enterprise application. Lastly, a new Gateway to Oracle Discoverer enables data analysts to publish their results for viewing through Oracle Discoverer.You can download the software from here - Oracle Data Miner 10.2.0.1 (37MB). The release notes and installations instructions are available here.
With Oracle Data Miner and Oracle Data Mining, the data never leaves the database: all data movement is eliminated. In addition, Oracle Data Miner and Oracle Data Mining provide the security of the Oracle database.

This is how you would use the Discoverer bridge (this is reproduced verbatim from the installation instructions page):
Publish to OracleBI Discoverer Gateway
Tools | Publish to Discoverer Gateway uses OracleBI Discoverer Gateway to publish data mining result in OracleBI Discoverer. You can publish the following results:
- Attribute Importance
- Association Rules
- Apply Results
- Decision Tree Rules
- Clustering Rules
- Classification Test Metrics
Follow these steps to publish data mining results to OracleBI Discoverer:
- Install required BI and OracleBI Discoverer components.
- Create a new EUL using OracleBI Discoverer.
- Register the Oracle Data Miner Gateway with the EUL.
- Use Oracle Data Miner to publish results to OracleBI Discoverer.
- Add Oracle Data Miner gateway objects as folders in a business area using OracleBI Discoverer Administration.
If you just want to publish data mining results to the Oracle Data Miner Discoverer Gateway, no software is required in addition to Oracle Data Miner. If you intend to use published mining results in OracleBI Discoverer, the following software is required; in each case click the link to download the software:
- Oracle BI 10.1.2.0.0 or higher
- Oracle Developer Suite 10.1.2.0.0 or Higher: OracleBI Discoverer Administrator is required; other components of the suite are optional.
- Patch 7595805 for OracleBI Discoverer Administrator, available from Patch Request 7595805; if this patch is not installed, the gateway will not work.
1. If an EUL does not exist, create a new EUL.
Click these links for information about creating an a EUL in two different cases:
* Create an EUL for an existing database user
* Create an EUL in a new database user
2. Register the Oracle Data Miner Discoverer Gateway with the EUL:
Execute the following SQL script in the EUL user to register the Oracle Data Miner Discoverer Gateway with the EUL:
-- registration script
insert into EUL5_GATEWAYS(
gw_id, -- Gateway ID
gw_type, -- Type of Gateway
gw_gateway_name, -- Name of Gateway
gw_product_name, -- Name of the product
gw_description, -- Description of the gateway
egw_version, -- version of the gateway
egw_database_link, -- For remote DB provide dblink
egw_schema, -- Gateway owner
egw_sql_paradigm, -- SQL paradigm
gw_element_state, -- element state
gw_created_by, -- who created this gateway
gw_created_date, -- when it was created
gw_updated_by, -- who updated this gateway
gw_updated_date, -- when it was updated
notm
)
values
(
EUL5_ID_SEQ.NEXTVAL,
'EGW',
'ODMr 10.2 Discoverer Gateway',
'Oracle Data Mining',
'This gateway provides data mining results accessible to OracleBI',
'1.1',
NULL, -- dblink if dmuser is in remote
'DMUSER', -- Change to the schema as needed
'OBJECT',
0,
USER,
SYSDATE,
USER,
SYSDATE,
0
)
Update:
Added links to Oracle Data Mining:
Oracle Technology Network (OTN) Discussion Forums:
Out of town, with a busy two weeks coming up...
Thursday, October 06, 2005
Queries you can easily understand.....
- Discoverer 10g
- BI Spreadsheet Addin for Excel
- Business Intelligence Beans
- Reports OLAP pluggable data source
From an Oracle perspective - Been there, seen it, done that and got the t-shirt. Just in case you have not seen this feature within BI10g, lets try some simple English questions to explain how this all works:
- How do I create a query?
- How do I create a multi-step query?
- Can I check my query?
- Can I save my query and reuse it?
How Do I Create a Simple Query?
Discoverer 10g make the query building process so easy. We allow you to manually select dimension members using your mouse. You can drill down through your hierarchies and select any member or group of members to include in your presentation. But where is the power in that?
The real power of Discoverer comes when you create conditions by clicking on the 'Conditions' tab. To make this as simple as possible we provide templates that guide you through the process of creating just about any query you could think of. Want to create a query to select the top 3 products based on a 6 month moving average for sales revenue? No problem – we have just the template for you….
The really nice thing is that each template is driven by hyperlinks, an idea that we have patented. So you find the template that sounds similar to the query you want to create and then just use the hyperlink to modify the template to meet your exact requirements. There is a large library of templates so you can build any query you could ever want.
How do I create a multi-step query?
Many business queries need to contain multiple steps, so how do you make this process as easy as possible. Very simple, we translated the whole join process as well. Now you can use terms like Add, Keep or Remove to further describe how steps interact with each other.
How do I check my query?
After creating a query that contains multiple steps this is an obvious question, and one that definitely needs to be answered. Fortunately, Discoverer can again answer this question with one click. We have provided a Members tab that will allow you to check your query before running the complete query.
Can I save my query and reuse it?
Absolutely, we even provide a Save button so you can reuse your queries. This is actually a huge feature as it allows you to create a query and share it across multiple presentations. Need to change the query and update all those presentations? No problem, just change the saved query and every presentation will automatically adopt the new saved query. Now that really is powerful.
Discoverer 10g allows you to just drag and drop any of those saved queries right into your presentation directly from your BI catalog. What could be simpler?
So remember folks, despite what you might read to the contrary elsewhere, you heard it here first - ' Create your queries using English with Oracle BI10g'.
Open World 2005 Attendance Graphs - with Discoverer
The session/track attendance numbers from the San Francisco Oracle Open World came out late last week, and a couple of PMs from the BI group took those numbers, built a Discoverer EUL on top of it, and cranked out a number of reports, and all in less time than it would take to say 'Wow! That was quick, and good!' (btw - the final numbers were that more than 35,000 people attended Open World! And if the Siebel acquisition goes through, then expect that number to go up even more!)
This post is not really about the data per-se, but more of a followup on the last couple of posts from Keith and me on the value of graphs and the huge number of very useful graphs already available in BI 10g - Discoverer, Beans, etc... (specifically, see 'Making more of BI Graphs' and 'More on the bubble graph').
Bubble graph - here the X-axis represents the number of sessions , Y-axis the average attendance per session, while the size of the bubble is the percentage of sessions in a track that were more than the conference average.




Ray's BI keynote was, unsurprisingly, the most heavily attended session. This was also the only keynote for the BI-DW track. And this session was also the one where the now-fabled Report Center was shown, publicly, for the first time. (Disclaimer: I am not saying that Report Center will be called Report Center when it ships, if it ships. Take it only as a demonstration vehicle of concepts and designs we have been working on internally, nothing more, nothing less.)

Below are two bubble graphs, showing the same data, but the second displays a smaller set than the first one. The X-axis shows the number of sessions, while the Y-axis shows the total number of attendees for the track. The size of the bubble is the average attendance per session. But with so many sessions, this magic quadrant is not very readable, is it?



Wednesday, October 05, 2005
More on the bubble graph
The bubble graph is also my favourite - and have a couple of examples to illustrate its power.
Using the data from the BI Samples (available from OTN), I have used Discoverer to create a workbook that shows me how my product categories are doing - i.e. margins, sales, and change in margin over last year. And I want to see things in perspective - margins and changes in margin will be of greater interest for those products (or product categories) for my volume sellers.


Take a look below:
- On the X-axis I have margin (the first column in my crosstab)
- On the Y-axis I have the change in margin over last year (the second column in my crosstab)
- The size of the bubble is the sales revenue measure (the third column in my crosstab above)

Or, I may want to analyze the 'Software/Others' product category (the top most bubble) further.



Here I want to look at my 'star' - Recordable DVD Drives - a little more.


Monday, October 03, 2005
Making more of BI Graphs....
My favorite graph type is the Bubble Graph.
Most people are familiar with this type of graph as it used extensively by market research companies and is often referred to as a “magic quadrant”. The Discoverer Help system provides the following help text for bubble graphs:
Bubble - graphs to show data in a similar way to a scatter graph, but with an extra dimension that uses the size of the bubbles. Each bubble is a value whose coordinates are specified by three numeric measures. A bubble graph is useful for comparing data that has three measures.
This type of graph is perfect for helping to both identify and quantify business issues all at the same time. The term magic quandrant is often used because the graph can be divided into four sectors and each sector has a specific meaning. Usually the top right is the best place for your data points but it all depends on the message you are trying to convey.
To take a simple example, you could use a bubble graph to plot salaries, years of experience, and productivity for your work force. You expect higher salaries for more experienced employees, and you expect these employees to be more productive.

If we actually plotted some data for this subject area we might generate graphs somthing like this:

I have used this graph quite a lot in recent demos and in some customer implementations since it is the easiest way to view a relationships between different measures. My favorite example is from the retail arena where we used bubble charts to solve a specific business problem. Every Monday, category managers would review sales and production data from the previous week. Typically each manager would be responsible for over 100 different products. They had a very short time to analyze the data and place orders with the various buying groups to either plan to order more or to slow production. The problem was how to present sales and production data in an intelligent way to allow the managers to focus on those items that generate the most margin for the company.
We used bubble charts as the starting point for analyzing their products. On the X-axis we plotted 4-week sales and on the Y-axis we plotted 4-week production data. The size of each bubble was derived from the product margin. The aim was to have products in the top right quadrant as this indicated high sales and high production. Any categories in the lower right or upper left quadrants were either selling well but planned production was not high enough or production was too high for the level of sales and so stock would be increasing. This made it very easy to identify the problem categories.
However, within those categories which ones should you select for further analysis? You could drill down on each and every bubble, but that would take a long time. By focusing on those categories that have the biggest bubble, i.e. those with the highest margin, the category managers could focus on those categories that were likely to have the highest impact on their overall profit. It was simple and extremely effective.