BI Blogs

Writer’s block and a glut of spam dims the appeal of blogging - maybe next week will be better

This week Jon and I took a few days out from client work, and went down to Oracle’s offices in Bristol to work on some OBIEE and SOA prototypes. Regular readers of this blog will know that I’m particularly excited about the convergence of BI and Service Orientated Architectures, as it gives us the possibility of broadening the reach of BI into business processes and business applications. Jon and I had an invite from Mike Durran and the SOA integration development team to go to their offices and build out some examples, so we decamped down to Bristol, got our laptops out and started integrating some services.

The main driver for this has been the next round of masterclasses I’m running for Oracle University. The theme for this year’s masterclasses is OBIEE, going beyond the basics and integrating it with the wider world of Oracle Fusion Middleware. In response to some requests from readers earlier this year, I’m having a common thread going through the entire masterclass, where we take the data provided in the SOA Suite “Order Bookings” demo, extend it a bit to create a customer data warehouse, then modify the Order Bookings BPEL process to call out to OBIEE reports, conditions and iBots to add “intelligence” to the business process.

its where the request for customer information, which previously was obtained through a database adapter against an Oracle database, is instead retrieved from an Oracle BI Answers report. The credit check, which in the original Order Bookings demo was performed by checking that the credit card checksum was correct, is instead obtained by running an Answers report that either returns rows (customer is valid) or doesn’t (customer is invalid). At the end of the process, an iBot is triggered which checks inventory and emails a “low stock” report to the inventory manager if stock needs replenishing.

All of this is accomplished through the Web Services interface provided by OBIEE, and is intended to demonstrate to the developer audience how this integration process works. In terms of customer benefits, it allows you to “externalize” decision about customer validity, get customer data, say, from an integrated BI Server data store, all of which can be modified by a report builder and enhanced to add intelligence to the business process.

Now that the basic invokation of OBIEE Web Services is built out, the next thing we’re doing is integrating an Oracle Real-Time Decisions decision engine in to the BPEL process, removing a set of static rules and replacing them with a self-learning decision process that assesses customer feedback, prices offered and past order preferences and picks a supplier based on which is likely to be most profitable for the company. I’ve got all the code together and the decision process, so next week I’ll be folding this into the enhanced Oracle Bookings process and integrating the whole lot.

Apart from all the SOA and RTD goodness, I’m also working on some data modeling issues (see previous postings a week or so ago around ragged hierarchies, fragmentation and aggregate persistence), some dashboard and alerts tips (including calling a BPEL process from a guided analytics link), and a whole section on OBIEE and Identity Management. If you’re interested in attending, here’s the current set of dates:

One of the Oracle team and I are also working on an article for OTN on integrating OBIEE and SOA, which we’ll probably submit after the first seminar in July and hopefully see up on OTN around August or September.

So, what, I hear you ask, about the escaped parrot? Well, just before I went down to Bristol, we’d heard this rustling sound in our chimney but put it down to bits of our chimney falling off (we’ve got an old Victorian house in Brighton).

Well after I got back, I was lying in bed on Friday morning just happening to look at the fireplace, when a bird’s head popped out of the chimney flue, looked round at me, and then fell from the flue into the fireplace. Bizarrely, rather than being a pigeon (or a seagull, as it’s Brighton) it was a rather puzzled-looking parrot; whilst it was a bit flustered at first, it then took up residence on our bed, let us feed and water us, and then just sat there for around four or five hours whilst we tried to think what to do with it.

Looking in the phone book, there is an actual “Sussex Parrot Rescue” service, but in the end we bundled it up in the cat transporter box, took it to the vet and left it with them to rehome. The parrot itself took it all in it’s stride - it just sat there looking philosophical, shuffled about a bit and so on, unlike my wife who was rather understandably rather freaked out by a parrot falling down our chimney. Our cats however knew nothing of what was going on, although given the size and un-flappability of the parrot, I don’t know who would have come off worse.

UPDATE: Unbelievably, as I just finished writing this blog posting, another parrot fell down our chimney - not the same one (a homing parrot?) but presumably it’s mate, who presumably also was nesting in our chimney. This one looked a much tougher proposition, flapped around the bedroom a bit and then flew through the (rapidly opened) window. As if things couldn’t get any stranger.

It’s Friday. I enjoyed this video. You might enjoy it too. Particularly after about a minute.

p://vimeo.com/173714″>Lip Dub - Flagpole Sitta by Harvey Danger from amandalynferri and Vimeo.

I was going to write about Powerset, a search startup / hype machine that keeps generating buzz without delivering anything, but Donna Bogatin delivered far better than I could with this post on their supposed coming out of stealth mode. I keep tabs on search because it’s one piece of the information management puzzle, but in enterprise situations it’s highly overrated and far more difficult to implement, despite all the “BI & search” hype.

Powerset hyped NL search queries, and now they seem to be hyping web 2.0 search. I’m one of those people who doesn’t really believe NL search is that great a thing, for a lot of reasons I won’t go into. But the mutating Powerset pitches and buzz marketing leave me clenching my teeth. I’m glad Insider Chatter was there to do the work for me. It’s moved up on my weekly reading list. Here’s her take on Google.

“The Google $160 billion plus market cap business plan is to CONTROL all the world’s information AND all the personal data of ALL the world’s citizens with the goals of:
1) selling high-priced advertisements against the content owned by others, and
2) data mining the personal information of others for its own corporate advantage.”

Good take on Google’s constant drumming of the “we’re not so evil” mantra while they continue to slide into evil. Curmudgeonly. I like it.

I came across a situation the other day where a client was accessing rows from a table via one of several different indexes and getting poor performance on all of them.

The table in question had about five million rows and about four indexes. For the process in question, the table was hitting each of the indexes on this single table at one point or another.

I noticed that the clustering factor for each of the indexes was quite poor - a value closer to the number of rows in the table rather than the number of blocks in the table. This was leading to a high number of logical IOs to get the rows needed on each query in the process, because the rows pointed to by the index were all in different data blocks, scattered far and wide. As I recalle, Jonathan gives a great explanation of how the clustering factor can affect the performance of a query in Chapter five of his CBO Fundamentals book and basically this was relevant to this scenario.

A fix for a poor clustering factor, is to sort the data in the table, in the order which matches that of the index in question. So, if INDEX_A is on columns A then B, you should order the data in the table by column A, then B…perhaps by removing it and then reinserting it all using an ORDER BY on the insert.

This of course, only works for one order - you can’t order data in multiple different ways within the same table, to suit all the indexes, so for one index you might be able to order the data perfectly but that might be terrible for another index which wants to order by say, column C, then D….or does it?

Well, in this particular case, the data required by each query hitting different indexes on this single table was all of a different type - by this I mean, the table itself was modelled from a Supertype Entity, implemented physically as a single table with a Supertype differentiator column (PRODUCT_TYPE in this case). Query one in the process wanted data for PRODUCT_TYPE “X”, and wanted to get this via Index IDX1 on COL1. Query two would then want data of PRODUCT_TYPE “Y” and get it via index IDX2 on COL2 etc…

I figured that if we split the table into partitions based on the PRODUCT_TYPE then we’d be able to order the data within each partition by the appropriate order for the index which would commonly be used to access data of that PRODUCT_TYPE.

It will take them a while to test and implement the solution and gain some empirical results, however a simple test script I knocked up seemed to indicate that the solution could work:

The script…

CLEAR BREAKSCLEAR COLUMNSCOLUMN index_name HEADING “Index|Name” FORMAT A10COLUMN partition_name HEADING “Partition|Name” FORMAT A10COLUMN leaf_blocks HEADING “Leaf|Blocks” FORMAT 999COLUMN distinct_keys HEADING “Distinct|Keys” FORMAT 999,999COLUMN num_rows HEADING “Num|Rows” FORMAT 999,999COLUMN clustering_factor HEADING “Clustering|Factor” FORMAT 999,999COLUMN clfpct HEADING “Clu Fact|Percent” FORMAT 999

DROP TABLE jeff_unpartitioned PURGE/CREATE TABLE jeff_unpartitionedASSELECT l pk_col,      MOD(l,4) partitioning_key_col,      (dbms_random.value * 1000) index_col1,      (dbms_random.value * 1000) index_col2,      (dbms_random.value * 1000) index_col3,      (dbms_random.value * 1000) index_col4FROM   (SELECT level l FROM dual CONNECT BY LEVEL < 100001)ORDER BY l/CREATE UNIQUE INDEX ju_pk ON jeff_unpartitioned(pk_col)/CREATE INDEX ju_idx1 ON jeff_unpartitioned(index_col1)/CREATE INDEX ju_idx2 ON jeff_unpartitioned(index_col2)/CREATE INDEX ju_idx3 ON jeff_unpartitioned(index_col3)/CREATE INDEX ju_idx4 ON jeff_unpartitioned(index_col4)/exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>USER,tabname=>’JEFF_UNPARTITIONED’);

SELECT i.index_name,      i.leaf_blocks,      i.distinct_keys,      i.clustering_factor,      (DECODE((t.num_rows - t.blocks),0,0,(i.clustering_factor) / (t.num_rows - t.blocks))) * 100 clfpctFROM   all_indexes i,      all_tables tWHERE  i.table_name = t.table_nameAND    i.table_owner = t.ownerAND    t.table_name = ‘JEFF_UNPARTITIONED’AND    t.owner = USER/

DROP TABLE jeff_partitioned PURGE/CREATE TABLE jeff_partitioned(pk_col number,partitioning_key_col number,index_col1 number,index_col2 number,index_col3 number,index_col4 number)PARTITION BY LIST(partitioning_key_col)(PARTITION p1 VALUES (1),PARTITION p2 VALUES (2),PARTITION p3 VALUES (3),PARTITION p4 VALUES (4))/INSERT /*+ APPEND */INTO jeff_partitioned(pk_col,partitioning_key_col,index_col1,index_col2,index_col3,index_col4)SELECT l,      1,      ROUND(l,100),      (dbms_random.value * 1000),      (dbms_random.value * 1000),      (dbms_random.value * 1000)FROM   (SELECT level l FROM dual CONNECT BY LEVEL < 25001)ORDER BY 3/COMMIT/INSERT /*+ APPEND */INTO jeff_partitioned(pk_col,partitioning_key_col,index_col1,index_col2,index_col3,index_col4)SELECT l,      2,      (dbms_random.value * 1000),      ROUND(l,100),      (dbms_random.value * 1000),      (dbms_random.value * 1000)FROM   (SELECT level l FROM dual CONNECT BY LEVEL < 25001)ORDER BY 4/COMMIT/INSERT /*+ APPEND */INTO jeff_partitioned(pk_col,partitioning_key_col,index_col1,index_col2,index_col3,index_col4)SELECT l,      3,      (dbms_random.value * 1000),      (dbms_random.value * 1000),      ROUND(l,100),      (dbms_random.value * 1000)FROM   (SELECT level l FROM dual CONNECT BY LEVEL < 25001)ORDER BY 5/COMMIT/INSERT /*+ APPEND */INTO jeff_partitioned(pk_col,partitioning_key_col,index_col1,index_col2,index_col3,index_col4)SELECT l,      4,      (dbms_random.value * 1000),      (dbms_random.value * 1000),      (dbms_random.value * 1000),      ROUND(l,100)FROM   (SELECT level l FROM dual CONNECT BY LEVEL < 25001)ORDER BY 6/COMMIT/CREATE UNIQUE INDEX jp_pk ON jeff_partitioned(pk_col,partitioning_key_col) LOCAL/CREATE INDEX jp_idx1 ON jeff_partitioned(index_col1,partitioning_key_col) LOCAL/CREATE INDEX jp_idx2 ON jeff_partitioned(index_col2,partitioning_key_col) LOCAL/CREATE INDEX jp_idx3 ON jeff_partitioned(index_col3,partitioning_key_col) LOCAL/CREATE INDEX jp_idx4 ON jeff_partitioned(index_col4,partitioning_key_col) LOCAL/exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>USER,tabname=>’JEFF_PARTITIONED’);

SELECT i.index_name,      i.leaf_blocks,      i.distinct_keys,      i.clustering_factor,      (DECODE((t.num_rows - t.blocks),0,0,(i.clustering_factor) / (t.num_rows - t.blocks))) * 100 clfpctFROM   all_indexes i,      all_tables tWHERE  i.table_name = t.table_nameAND    i.table_owner = t.ownerAND    t.table_name = ‘JEFF_PARTITIONED’AND    t.owner = USERORDER BY i.index_name/

SELECT i.index_name,      ip.partition_name,      ip.leaf_blocks,      ip.distinct_keys,      ip.clustering_factor,      (DECODE((t.num_rows - t.blocks),0,0,(ip.clustering_factor) / (t.num_rows - t.blocks))) * 100 clfpctFROM   all_indexes i,      all_ind_partitions ip,      all_tables tWHERE  i.table_name = t.table_nameAND    i.table_owner = t.ownerAND    i.index_name = ip.index_nameAND    i.owner = ip.index_ownerAND    t.table_name = ‘JEFF_PARTITIONED’AND    t.owner = USERORDER BY i.index_name,        ip.partition_name/

And now the results…

Table dropped.

Table created.

Index created.

Index created.

Index created.

Index created.

Index created.

PL/SQL procedure successfully completed.

Index        Leaf Distinct Clustering Clu FactName       Blocks     Keys     Factor  Percent———- —— ——– ———- ——–JU_IDX4       226  100,000     99,830      101JU_IDX3       226  100,000     99,880      101JU_IDX2       226  100,000     99,872      101JU_IDX1       226  100,000     99,833      101JU_PK         103  100,000        685        1

5 rows selected.

Table dropped.

Table created.

25000 rows created.

Commit complete.

25000 rows created.

Commit complete.

25000 rows created.

Commit complete.

25000 rows created.

Commit complete.

Index created.

Index created.

Index created.

Index created.

Index created.

PL/SQL procedure successfully completed.

Index        Leaf Distinct Clustering Clu FactName       Blocks     Keys     Factor  Percent———- —— ——– ———- ——–JP_IDX1       218  100,000     74,593       75JP_IDX2       218  100,000     74,624       75JP_IDX3       218  100,000     74,609       75JP_IDX4       218  100,000     74,627       75JP_PK         124  100,000        568        1

5 rows selected.

Index      Partition    Leaf Distinct Clustering Clu FactName       Name       Blocks     Keys     Factor  Percent———- ———- —— ——– ———- ——–JP_IDX1    P1             32   25,000        142        0JP_IDX1    P2             62   25,000     24,826       25JP_IDX1    P3             62   25,000     24,811       25JP_IDX1    P4             62   25,000     24,814       25JP_IDX2    P1             62   25,000     24,812       25JP_IDX2    P2             32   25,000        142        0JP_IDX2    P3             62   25,000     24,835       25JP_IDX2    P4             62   25,000     24,835       25JP_IDX3    P1             62   25,000     24,802       25JP_IDX3    P2             62   25,000     24,826       25JP_IDX3    P3             32   25,000        142        0JP_IDX3    P4             62   25,000     24,839       25JP_IDX4    P1             62   25,000     24,830       25JP_IDX4    P2             62   25,000     24,832       25JP_IDX4    P3             62   25,000     24,823       25JP_IDX4    P4             32   25,000        142        0JP_PK      P1             31   25,000        142        0JP_PK      P2             31   25,000        142        0JP_PK      P3             31   25,000        142        0JP_PK      P4             31   25,000        142        0

20 rows selected.

From the results we can see that when the table is unpartitioned and, in this instance, ordered by the primary key, all four indexes have high clustering factors.

We could order the data by one of the columns in one of the indexes and make that index get a good (low) clustering factor, but the other indexes would still remain with high values - you can’t make them all have low clustering factors whilst the table is one big amorphous mass of data.

In the partitioned table we are able to order the data in the most efficient way for each partition, insofar as each partition maps to an access path via a specific index.

So, partition P1, where users would commonly access the data via index JP_IDX1, has a low clustering factor because we ordered the data by INDEX_COL1 on insert.

Partition P2 has the lowest clustering factor for index JP_IDX2 and P3 for JP_IDX3 etc…

Some caveats to bear in mind for this approach:

1. You should ensure that your queries do use the partitioning key column when accessing the table, otherwise you’ll end up scanning extra index partitions unnecessarily. You will also make life extremely difficult, if not impossible, for the optimizer because if we don’t identify that the query will only use one partition then the CBO will be forced to use the global stats on the index/table rather than the specific ones for a single partition…the global ones as you can see from the results still show a high clustering factor because it is an aggregate which is hiding the detail that one specific partition - the one we really want - has a low clustering factor.

2. If you want LOCAL indexes, you will need to add the partitioning key column to that index - if it is not already present of course. As Jonathan points out in the first comment on this post…this only applies if the index in question is unique and supports a PK/unique constraint. If the index does fall into this category then it can still be local without the need to have the partitioning key column(s) in it.

3. If you are using GLOBAL indexes on the partitioned table then they will still have reasonably high clustering factors due to the partitions where the data is not ordered by the columns of that index, but the data itself will be ordered within the partition that we access via the partioning key column so we should get good performance.

4. If you want to use ordered data in a table then you need to factor that into the way you populate the table - if it’s a batch process then you can this as part of that process but if it’s built up over time, in an OLTP fashion then you’ll need periodic “rebuilds” of the table to take the data out and put it back in, reordered. This is something you would need to do whether it was partitioned of course.

I’m sure there are more caveats.

Your mileage may, of course, vary.

Last week I noticed that the long awaited Oracle Business Intelligence Standard Edition One was available on e-delivery download site and on Monday (25 June 2007) Oracle officially announced the general availability of Oracle Business Intelligence Standard Edition One. Oracle Business Intelligence Standard Edition One is based on the Siebel Analytics stack of products […]

There is a lot of buzz in the industry about the emerging class of CDI software that businesses are using to gain a single view of their customer. But let’s separate the hype from the reality.

Certainly, implementing a successful customer data-integration solution is critical to business. But what is necessary to accomplish that? There are three aspects to implementing a CDI solution: people, process and product.

>>>continue to the rest of my post Customer Data Integration (CDI) - Separating the Hype from the Reality

Don’t look now, but Office and Excel are shaping up as PerformancePoint Server’s biggest competition.

What do Microsoft, Oracle, and SAP have going for them, and where do they need to improve if they’re to tackle the best-of-breeds?

Data visualization seems to be getting hotter, as evidenced by TIBCO’s acquisition of data viz specialist Spotfire.

These are the slides from the ETL market overview webcast I did for TDWI in May. Share and enjoy.

s are hard to read you can go to the slideshare site (link on the lower right) where you can see a full-screen version of these presentations.

I’ve had several good conversations with different folks in the industry lately about this term and what it means. Lou Agosta was nice enough to write a piece on DMReview, as well as have a really neat phone call with me. We both agree that there are more layers to this onion that originally thought. What a surprise!! But like anything, there are also steps, types, and classes of DDW to eventually get us there. In this entry I dive into the topic of “classifications of terms”, and levels of DDW, and attempt to put down a rough road-map to get there. Keep in mind the definition is still in flux, and will be for some time to come.

Ya teneis a vuestra disposición las ofertas de empleo existentes en el área del Business Intelligence en España, más algunas ofertas europeas interesantes.
Si quieres enviarnos tu CV, para mandárselo a las empresas y headhunters que nos solicitan trabajadores, mándanos un mail a administrador_arroba_todobi.com junto con tus datos y CV.

Consultar ofertas de Junio 07

Si alguien está interesado en realizar una contratación o busca un perfil concreto, puede dejar sus requerimientos en el apartado de comentarios o enviar un e-mail a: administrador_arroba_todobi.com

My blog has moved! As part of the transition from Hyperion to Oracle, as of now my weblog can be found at:

http://blogs.oracle.com/frankbuytendijk/

The layout has changed, but the topics won’t. I will be commenting on a variety of topics around business intelligence and enterprise performance management, on a weekly basis.

So I hope you will keep reading my weblog, and I look forward to your comments…

frank

In august 2006 I wrote about my new car, and that it was ironic that — as a performance management professional — the first I did was look for the button to switch off all that information on the dashboard on fuel consumption. I just don’t want to know.

I just bought another car, just for fun. It’s a 21 year old Citroen 2CV. Here’s a picture of the car.

Would you like to see the dashboard? Here it is…

I guess today you could call it "minimalistic design". But who cares about the dashboard? I get all the experience I need not by looking at the speed, but by feeling the road going underneath me, the wind coming through the open roof, and from the steering wheel in my hands.

frank

I’m trying something different with the slides for this TDWI ETL webcast, using slideshare instead of the usual PDF and embedding it directly. I will convert and post a PDF later as I’ve done with all the other webcasts later. The downside of Slideshare is that the transcript doesn’t contain the information from the notes pages, which makes presentations like this less useful.

ment if you think this is a good way to post the slides and I’ll upload the other webcasts from earlier in the year.