ODI and analytic functions (again???)

My friend and colleague Maciej Kocon has come up with the following trick. Rather than use a user defined function to implement an analytic function such as MAX() OVER or SUM() OVER as suggested by note 807527.1 there is another way to work around the getGrpBy bug.

We will take advantage of the ODI multi pass parser and hide the analytic function from getGrpBy in our mappings:

<?out.print(”SUM”);?>(SALES.AMOUNT_SOLD) OVER (PARTITION BY PRODUCTS.PROD_CATEGORY)

The above SUM gets only generated once getGrpBy has been parsed and as a result no GROUP BY clause is generated, which is the correct behaviour.

A brilliant yet simple trick.

Cheers Maciej.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

Downloading, Setting Up & Running Oracle Data Miner

As the awareness of Oracle Data Miner increases so does the interest in trying out and playing with Oracle Data Miner (ODM). The following set of instructions steps you through what is required to download and install the database, configure a data mining user in the database, populate the data mining user with some data. The instructions finish with details of how to download and configure the Oracle Data Mining tool. The following instructions cover 11g R1 of the database and tool.

Download and install the 11g R1 Database

1. The first step is to download the Oracle 11g Enterprise Edition database software. The data mining functionality is only available in the enterprise edition. You need to download Oracle 11g (Release1 – 11.1.0.6.0) Enterprise Database software
2. Create a working directory and save download to your PC
3. UnZip the software into the working directory
4. Before you run the install on your PC you need to log in as Administrator of the machine
5. Double click on the Setup.exe file
6. Follow the on screen instructions
7. Install the full Enterprise edition
8. Click here for a tutorial on installing Oracle 11g
9. When you get to step 3 in the installation tutorial, make sure you write down the Database Password. This is very important as you will need this later.

NB. Make sure that you complete all the installation steps correctly and that you did not receive any errors. If you did you may need to start again.

HINT : You may need to do the installation as Administrator on your PC.

Set up the Sample Data for the Data Mining User

1. You will need to download the Examples files as these will contain the sample data sets need by ODM
2. Download the Oracle 11g Examples file to you working directory
3. UnZip the Examples into you working directory
4.  See the installation guide for installing the Examples

Create and Configure the Oracle Data Mining User

SYSTEM user (and the password you gave during the installation process) and type a command like the following:

ALTER USER SH IDENTIFIED BY SH;

Download the files DMSHGRANTS.SQL  and DMSH.SQL

Logon into the database as SYSTEM and run the DMSHGRANTS.SQL file

 @<File Location>dmshgrants sh dmuser

Logon into the database as your DMUSER (password = DMUSER) and run the DMSH.SQL file

@<File Location>dmsh.sql

If everything has worked (no errors) then you should have Oracle Data Mining setup in the Database

Download, Setup and Login to Oracle Data Miner

1. Download Oracle Data Miner software
2. UnZip the software into C:\Oracle\ODM
3. To start ODM run   C:\Oracle\ODM\odminerw.exe
4. Click on the New button to create a new connect.
5. Enter a connection name, the username and password of the ODM user created above plus the name of the pc/machine/server, port and SID/Service Name
6. When a successful connect is made the ODM tool will be opened.
7. To view the sample data created above, expand the Data Sources branch in the Navigator panel, select your ODM user. You can now see the Views and Tables with the sample data. 

Two useful books in the documentation are, ODM Administrators Guide 11g and ODM Concepts Guide 11g

If you don’t want to go to the trouble of downloading and installing the enterprise edition of the database then you can use it on the Amazon cloud. The ODM team have provided a set of instructions for you to follow on how to setup yourself up on the Amazo cloud and how you can connect the Oracle Data Mining tool to the database on th Amazon cloud.  Link to instructions.

10 Reasons you really need predictive analytics

SPSS have recently posted and article called “10 Reasons you really need predictive analytics“. I thought it would be interesting to post the main points from this article to illustrate that not all predictive analytic projects involve Data Mining, but involve a number of different techniques and looking the the business data in a different way. Yes data mining can be a very important element in some of the following

1. Get a higher return on your data investment
Your organization has a significant investment in data – data that contains critical information about every aspect of your business. Today more than ever, you need to get the best return on the data you have collected–and predictive analytics is the most effective way to do this. Predictive analytics combines information on what has happened in the past, what is happening now, and what’s likely to happen in the future to give you a complete picture of your business.

2. Find hidden meaning in your data
Predictive analytics helps you maximize the understanding gained from your data. It enables you to uncover hidden patterns, trends, and relationships and transform this information into action.

3. Look forward, not backward
Unlike reporting and business intelligence solutions that are only valuable for understanding past and current conditions, predictive analytics helps organizations look forward. By leveraging sophisticated statistical and modeling techniques, you can use the data you already have to help you anticipate future events and be proactive, rather than reactive.

4. Deliver intelligence in real time
Your business is dynamic. With predictive analytics, you can automatically deploy analytical results to both individuals and operational systems as changes occur, helping to guide customer interactions and strategic nd tactical decision making.

5. See your assumptions in action
Advanced analytical methods give you the tools to develop hypotheses about your organization’s toughest challenges and test them by creating predictive models. You can then choose the scenario that is likely to result in the best outcome for your organization.

6. Empower data-driven decision making
Better processes help people throughout your organization make better decisions every day. Predictive analytics enables your organization to automate the flow of information to match your business practices and deliver the insights gained through this technology to people who can apply them in their daily work.

7. Build customer intimacy
When you know each of your customers or constituents intimately—including what they think, say, and do—you can build stronger relationships with them. Predictive analytics gives you a complete view of your customers, and enables you to capture and maximize the value of each and every interaction.

8. Mitigate risk and fraud
Predictive analytics helps you evaluate risk using a combination of business rules, predictive models, and information gathered from customer interactions. You can then take the appropriate actions to minimize your organization’s exposure to fraudulent activities or highrisk customers or transactions.

9. Discover unexpected opportunities
Your organization can use predictive analytics to respond with greater speed and certainty to emerging challenges and opportunities, helping you to keep pace in a constantly changing business environment.

10. Guarantee your organization’s competitive advantage
Predictive analytics can drive improved performance in every operational area, including customer relations, supply chain, financial performance and cost management, research and product development, and strategic planning. When your organization runs more efficiently and profitably, you have what it takes to out-think and out-perform your competitors

So what is Predictive Analytics. Check out the description on Wikipedia

Let me know you views and comments on the above.

Brendan Tierney

Comparative Window Functions: Proposed Extensions to Analytic Functions

I promise that this will be the last post on analytic functions this month; but I came across this proposal to extend analytic functions with a comparative window function on Tom Kyte’s blog yesterday (also make sure to review the actual paper). Basically this new type of analytic function would allow you to compare the current row in a window to any other row in the window. I recently had a requirement where this would have come in handy. Imagine a dataset made up of product category, product id, product referential id, e.g.:

Computers,1,2
Computers,1,3
Computers,1,4
Computers,4,9

The requirement was to find those products whose reference products were also in the dataset. In the example above this would have been the record Computers,1,4. Something that can only be easily solved with a self-join. With this new proposal you could write this as

SELECT * FROM (
	SELECT
		prod_cat.
		prod_id.
		prod_ref_id,
		MAX(CASE WHEN prod_ref_id = INDEX(prod_id,anchor_row) THEN 1 ELSE 0) OVER (PARTITION BY prod_cat ROWS BETWEEN UNBOUNDED PRECEDING UNBOUNDED FOLLOWING) ref_exists_ind
	FROM
		prod_ref
)
WHERE ref_exists_ind = 1

Pretty cool…

SQL quiz revisited: ROW_NUMBER, LAST_VALUE, FIRST_VALUE and the SQL knights value problem.

Thank you all for your entries to the recent quiz. The guys from Packt should be shortly sending out the e-books to the winners.

Today I will walk you through some of the possible solutions and also briefly touch upon performance of these.

Last week’s contest was based on a classic SQL problem. Anthony Molinaro, the author of the SQL Cookbook has named this the Knight Values problem because it is analogous to a knight’s path in the game of chess: “You determine the result the way a knight determines a new location: by jumping to a row then turning and jumping to a different column”. In our example we first have to find the highest amount sold for a customer and from there then get the corresponding time_id. Another complicating factor is that we want the query to be deterministic, i.e. we just want to get back one record per customer. So what happens if there is a tie between the amount_sold, e.g. the same amount_sold exists on 5/11/2009 and 7/11/2009. If we come across such a tie we would like to select the amount_sold with the highest time_id.

Note: You can make this query only deterministic if there is a unique key on the table. Otherwise you may end up with a tie. There is a way around this though by randomly selecting one of the values or performing a SELECT DISTINCT. I will give two examples further down.

Of course you can do this in classic SQL using a subquery:

SELECT
   cust_id,
   amount_sold,
   MAX(time_id)
FROM (
   SELECT
      a.cust_id,
      a.time_id,
      a.amount_sold
   FROM
      customer a JOIN (
         SELECT
            cust_id,
            --amount_sold,
            MAX(amount_sold) amount_sold
         FROM
            customer
         GROUP BY
            cust_id
      ) b ON (a.cust_id = b.cust_id AND
              a.amount_sold = b.amount_sold)
)
GROUP BY
   cust_id,
   amount_sold

A query such as the following would not be deterministic as it does not resolve the potential tie outlined above.

SELECT
   *
FROM
   customer
WHERE
   (cust_id, amount_sold) in (
      SELECT
         cust_id,
         max(amount_sold)
      FROM
         customer
      GROUP BY
         cust_id
);

You can clearly see this if you insert the following record into our customer table

INSERT INTO customer VALUES (2,TO_DATE('05-FEB-2001','DD-MON-YYYY'),1545.78);

An alternative to the above is the use of analytic functions. You can either use ROW_NUMBER(),LAST_VALUE/FIRST_VALUE or KEEP FIRST/LAST with DENSE_RANK.

Let’s first have a look at LAST_VALUE.

SELECT cust_id, time_id, MAX(amount_sold) FROM (
   SELECT
      cust_id,
      time_id,
      LAST_VALUE(time_id) OVER (PARTITION BY cust_id ORDER BY amount_sold, time_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) time_max_amount,
      amount_sold
   FROM
      customer
)
WHERE
   time_max_amount = time_id
GROUP BY
   cust_id, time_id

So what are we doing here? We first break up the customer table by cust_id and order the resultset by amount_sold. We then grab the last value in this ordered resultset, which will give us the time_id for the maximum amount_sold for each customer. If you want to avoid a tie between the same amount_sold to a customer on two different days you need to include a unique value in the ORDER BY clause. In our case we are including the time_id to avoid a tie. If you are using LAST_VALUE you need to include the windowing clause ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING. As per documentation: “If you omit the windowing_clause of the analytic_clause, it defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This default sometimes returns an unexpected value, because the last value in the window is at the bottom of the window, which is not fixed. It keeps changing as the current row changes. For expected results, specify the windowing_clause as RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Alternatively, you can specify the windowing_clause as RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.”

We could of course use the FIRST_VALUE function where we can use the default windowing behaviour to our advantage and omit the clause altogether. Thus making it easier to read. We have to slightly change our query though:

SELECT cust_id, time_id, MAX(amount_sold) FROM (
   SELECT
      cust_id,
      time_id,
      FIRST_VALUE(time_id) OVER (PARTITION BY cust_id ORDER BY amount_sold DESC, time_id DESC) time_max_amount,
      amount_sold
   FROM
      customer
)
WHERE
   time_max_amount = time_id
GROUP BY
   cust_id, time_id

One reader sent in a query to avoid the subquery altogether. Well, in Teradata you could have done this using analytic functions in the predicate clause.

SELECT DISTINCT
   cust_id,
   FIRST_VALUE(amount_sold) OVER (PARTITION BY cust_id ORDER BY amount_sold DESC, time_id) amount_sold,
   FIRST_VALUE(time_id) OVER (PARTITION BY cust_id ORDER BY amount_sold DESC, time_id) time_id
FROM
   customer

Another way of solving this with analytic functions is to use KEEP FIRST/LAST with dense_rank

SELECT cust_id, time_id, MAX(amount_sold) FROM (
   SELECT
      cust_id,
      time_id,
      MAX(time_id) KEEP(DENSE_RANK LAST ORDER BY amount_sold)  OVER ( PARTITION BY cust_id) time_max_amount,
      amount_sold
   FROM
      customer
)
WHERE
   time_max_amount = time_id
GROUP BY
   cust_id, time_id

If you don’t have a unique key in your dataset you can make the result deterministic by randomly selecting one of the tied records. This is achieved via row_number(). The solution below is my favourite as it is the most readable of the lot and always deterministic.

SELECT
   cust_id,
   time_id
FROM (
   SELECT
      cust_id,
      time_id,
      row_number() OVER (PARTITION BY cust_id ORDER BY amount_sold DESC, time_id DESC) rn FROM customer
)
WHERE
   rn = 1

A couple of words on performance.

The pure SQL approach will perform a full scan of the customer table twice. This means that it will generate twice the amount of logical I/O. However, the analytic functions will use up a lot more memory or even worse temp disk space for sorting based on the PARTITION and the ORDER BY clause. By creating an index on the columns used in the partition clause and the order by clause (such as the one below) you can generate a WINDOW NO SORT explain plain. As a result the query will have to do no sorting at all and should generally speaking perform better than the pure SQL. However, like everything in life it all comes down to your specific situation.

CREATE INDEX idx ON customer
(cust_id,AMOUNT_SOLD DESC,time_id desc)

If you want to read up on analytic functions I recommend the following books:
SQL Cookbook. This is my number one.
The Art of SQL.
SQL Hacks
SQL Tuning
Advanced SQL Functions in Oracle 10g. This one has more examples on analytic functions in Oracle.