BI Blogs

I’m constantly amazed at how no one calls SugarCRM on their deceptive open source license. Cliffs Notes: They call it open source, but it by no means qualifies as such and OSI will never certify it because doesn’t meet the definition of open source! Apparently, because they’ve got a bunch of money no one made a stink when they switched their license, and went from a legitimate “open source” company to well, shareware type license.

Matt Asay (smart, sharp, open source advocate) thinks it’s copacetic (check out the thread here). Is SugarCRM allowed to change the definition of open source to suit their own business model? Am I the only one willing to say without any standard (OSI) the term open source will become dilluted and viewed with skepticism?

PS - I think I’m just being awnry now… I should lay off it I suppose… Or invite John Roberts to have a beer and see if he can convince this skeptic it’s not shareware.

I’m constantly amazed at how no one calls SugarCRM on their deceptive open source license. Cliffs Notes: They call it open source, but it by no means qualifies as such and OSI will never certify it because IT IS NOT OPEN SOURCE! Apparently, because they’ve got a bunch of money no one made a stink when they switched their license, and went from a legitimate “open source” company to a deceptive “we call ourself open source but we’re really not” company.

Matt Asay (smart open source advocate) won’t even comment on their deceptive use of the words “open source.” Is SugarCRM allowed to change the definition of open source to suit their own business model? Am I the only one willing to call this BS?

In part two, I mentioned that large files could take tens of seconds to process and the processing of a file is wrapped into a single transaction that either fails or succeeds. I also said this problem seemed to occur with outlets that typically polled larger files. I did not mention though that stock transactions appear towards the beginning of the file. Putting this information together leads us

I never like finding why a deadlock happens, all too often the solution is embedded in the application code where some error in logic has caused multiple sessions to do things in different orders. The text of the error message provided by Oracle implies “heap all blame at the developers’ door, they are the guilty ones and the solution lies with them”. And often, but not always that is so.

Each night, after the customer’s outlets close for the day they each transmit a single binary file to the central system. The central system unpicks this file into the many record types within the file and sends them off to various destinations in the business. However things are not quite that simple. The overarching rule in this process is that the file must contain validated data or it is rejected as a whole. So the processing of each file is wrapped in a single transaction. No rocket science here: write to tables (lots of them) check some business rules and then commit or rollback. The time to process a single file basically depends on its length; big files could take tens of seconds but small ones just fractions of a second. To speed processing the application processes the incoming data four files at a time using four separate threads.

The deadlocks always occurred during an update on a single table, the one that stored stock item, store and a couple of transaction dates.
DESC STKTAB
OUTLET_ID                                 NOT NULL NUMBER
STOCKITEM_ID                              NOT NULL NUMBER
DATELASTSOLD                                       DATE
DATELASTVALIDATED                                  DATE

The primary key is on outlet_id & stockitem_id. There is also an index on stockitem_id. Both outlet_id and stockitem_id have foreign keys.

Strangely, the deadlocks always seemed to come from outlets belonging to one of two brands. These brands usually made large daily data files and had rigorously maintained stock policies; nothing can be stocked unless it is set up at the centre first.

Trace file from the deadlock showed this:

Deadlock graph:
                       ———Blocker(s)——–  ———Waiter(s)———
Resource Name          process session holds waits  process session holds waits
TX-00050021-00014589        11      31     X             13      16           S
TX-00060017-00014503        13      16     X             11      31           S
session 31: DID 0001-000B-00000002      session 16: DID 0001-000D-00000002
session 16: DID 0001-000D-00000002      session 31: DID 0001-000B-00000002
Rows waited on:
Session 16: no row
Session 31: no row

Now the interesting things here are the ‘S’ in the last column and the no row values in the ‘Rows waited on’ bit. To me this actually indicates that Oracle can not mark a transaction in the data block header. This is either because the number of transactions has exceeded MAXTRANS (unlikely as we have only four process threads) or the block is full and there is no space the mark any additional transactions. What the deadlock graph does not show is a problem with the application code.

So can we come up with a reasonable model to support the case that the deadlocks are caused by contention for datablocks? That’s in the next part!

Occasionally, just occasionally, I am asked to leave my sane, safe data warehouse world (OK, not many DBAs out there would agree with that) and take a look at some other systems. For the past couple of days I have been looking at ‘deadlock’ problems on a legacy application and to suggest solutions that did not involve the application code.



Probably the best description of the application is a data switch interfacing a company’s 2000 or so retail outlets with their central accounting and DW systems. The application does a few other things in the working day but for the batch is just moves data around. The application is about 8 years old and was written in a 4GL object-orientated language that is no longer on this planet. Coupled with the excessive zeal of the designers to implement every last aspect of OO dogma they could come up with such as 5 levels of abstraction and a single-table-to-contain-everything database design. Tempered with the developers sense of practicality to introduce more tables to allow the application to actually work and you get a dog’s breakfast of an application that is almost impossible to find contract programming skills to fix or enhance (or even understand). Can any one tell me why the developers insisted that all tables had six-character table names (padded with Xs if need be) and how that boosts performance?



Deadlocks have always been an intermittent feature of the application. The developers could never come up with a reason why as only one session was updating a record at time and all accesses were by primary key. Recently the number of deadlocks has increased and as the application has not been changed it was thought to be a problem with the data or a business process change.



Next part – deadlock investigation and possible solutions.

My last post generated some great responses, ranging from Master Data Management as MDM philosophy to MDM Data Marts as “better than gospel” or better than system of record on the source system. In this entry I will take a look at MDM in a little more detail, and try to answer my view points on some of the issues raised in the responses. First, thank-you to all those who are responding, I enjoy reading your thoughts.

Data warehouses and business intelligence applications are designed to answer exactly the types of questions that users would like to pose against real-time data. They are able to analyze vast quantities of data over time, to determine what is the best offer to make to a customer, or to identify potentially fraudulent, illegal, or suspicious activity. Ad-hoc reporting is made easy using today’s advanced OLAP tools. All that needs to be done is to make these existing systems and applications work off real-time data. This white paper examines the challenges of adding real-time data to these system, and presents several approaches to making real-time warehousing a reality today.

Two prevailing trends in the Enterprise Resource Planning (ERP) market have created a significant challenge for companies looking to use operational reporting to improve their business decision making. SAP, the market leader in ERP, drives both trends. Interestingly, because of SAP’s tight partnership with Business Objects, SAP is also a part of the solution. SAP customers that find their operational reporting capabilities lacking despite deploying SAP BW to meet business intelligence requirements should not despair. By leveraging the bundled Crystal Enterprise SAP Edition from Business Objects - in conjunction with a proven methodology - companies can be up and running with operational reporting in less than a month.

Data quality is one of the largest challenges facing data warehousing projects today. Poor data quality can undermine the success of what would otherwise be well-developed applications by stripping the business value from the information. Historically, data quality has typically been addressed in a tactical way and mostly only around “customer” data (i.e. using specific tools and external databases to improve name, phone number, and address data fields.) Today, however, new technologies make a more strategic approach to data quality both possible and cost-effective. This paper outlines a set of specific strategies for improving data quality through the use of XML-based electronic forms at the information capture stage.

In early 2004, Claraview released a whitepaper to help MicroStrategy customers develop an upgrade plan for moving to MicroStrategy 7.5. This release of the MicroStrategy platform introduced significant new functionality and a major architectural enhancement to the MicroStrategy suite of business intelligence products. Now, in early 2005, MicroStrategy has made available a beta version of MicroStrategy 8.0. As with any new software release, existing MicroStrategy customers will want to weigh the benefits of these new releases against the costs and complexity of an upgrade project. Customers will also want to determine whether to upgrade inline or skip certain versions.

Business intelligence and data warehousing systems continue to blossom in both public and private sector environments. Organizations are harnessing the power of business intelligence products that are in the their second decade of existence, offering features and functions more and more honed to measuring corporate performance, providing multi-tiered dashboard views of their businesses and even providing suppliers and business partners views of their data directly through extranets.

I don’t know if anyone else has come across it yet, but
Jonathan Lewis has recorded a
podcast for DBAZine.
It talks about Jonathan’s new book, the type of work he does as a consultant,
how he got into the industry and how he got to the position he is now -
basically making sure he wrote down techniques he came across, and then taking
time out afterwards to work out the full picture. There’s also a good section on
tips he’d give anyone looking to move into independent consultancy, and some
comments on misconceptions and myths that he comes across on his travels. All
good stuff, about 45 minutes in length and a

30MB MP3 download.

At BMC Software, we use our Business Service Management (BSM) solutions to achieve compliance, increase security, and ensure business continuity across the organization.<br><br>We have achieved and maintained IT compliance for Sarbanes-Oxley, and consistently maintain controls to secure both databases and user access. And we’ve extended these management processes beyond compliance to ensure we keep business processes at the top of our priority list. Find out how we do it in this complimentary white paper. Download now!

I’ll echo others in saying that giving the virtual presentation is much different than real life presentations. It was fun though, and I hope people found it useful. Thanks to ODTUG for letting me share some information about OWB 10gR2.

Here are the slides… Check out the blog series on Paris as well!

PS - I think I prefer “real” conferences!

I have a Mac, and, as all Mac users, I want to do everything from my Mac (it does not hurt that it has a great looking and easy to use OS). When I found out that I could run Oracle Data Miner from my Mac I had to try it. Small caveat, there are no instructions on how…

[ This is a content summary only. Visit my website for full links, other content, and more! ]

Keith Laker from the Oracle
Business Intelligence product team dropped me a line this evening, to tell me
about a survey the Data Warehouse Development team at Oracle are carrying out.
It sounds like a useful and interesting survey and I’ll certainly be taking
part. Here’s Keith’s message:

"At the start of most data warehouse projects, or even during a project, I
am sure you as customers try to find answers to the following questions to help
you plan and manage your environments:

• Where can I find trend and comparison information to help me plan for
  future growth of my data warehouse?
• How many cpu’s do other customers use per terabyte?
• How many partitions are typically used in large tables? How many
  indexes?
• How much should I allocate for memory for buffer cache?
• How does my warehouse compare to others of similar and larger scale?
  

The data warehouse development team, here at Oracle would like to help
provide answers to these questions. However, to do this we need your help. If
you have an existing data warehouse environment, we would like to obtain more
technical information about your environment(s) by running a simple measurement
script and returning the output files to us, here at Oracle. This will allow our
developers to provide comprehensive documents that explain best practices and
get a better understanding of which features our customers use the most. This
will also allow you as Customers, to benchmark your environments compared to
other customers’ environments.

From a Company perspective we are also interested to get feedback on features we
have added to the database, are these features used, how are they used etc. For
example we are keen to understand:

• Which initialization parameters are most frequently used at what
  values?
• How many Oracle data warehouses run on RAC? on single nodes?
• Is there a trend one-way or the other, especially as data volumes
  increase?
• Does this change with newer releases of the database?

All results from these scripts will be held confidential. No customers will
be mentioned by name; only summaries and trends will be reported (e.g., “X
percent of tables are partitioned and Y percent are indexed in data warehouses
that are Z terabytes and larger in size.” or “X percent of Oracle9i and Y
percent of Oracle10g data warehouses surveyed run RAC”). Results will be written
up as a summarized report. Every participating customer will receive a copy of
the report.

Terabyte and larger DW are the primary interest, but information on any data
warehouse environment is useful. We would like to have as many customers as
possible submit results, ideally by the end of this week. However, this will be
an on going process so regular feedback after this week is extremely useful.

e=”http://www.oracle.com/technology/products/bi/db/10g/dw_survey_0206.html” href=”http://www.oracle.com/technology/products/bi/db/10g/dw_survey_0206.html”>
http://www.oracle.com/technology/products/bi/db/10g/dw_survey_0206.html

Please return the script outputs using the link shown on the above web page,
see the FAQ section, or alternatively mail them directly to me:

keith.laker@oracle.com.

Thank you and we look forward to your responses."

And relax…

Just finished the second of my two
Desktop Conference 2006
presentations, this time on Oracle OLAP Best Practices. There were more
technical problems as the start again, just as I was about to start I was asked
to move to a different conference ID (i.e. virtual "room") and only about half
of the attendees on the old room managed to move across to the new one. We
didn’t get to start until about 20 minutes past the official start time, which
meant I had to leave out the last 20 minutes or so of my talk. A bit of a shame
but if you were listening in, you can download the
powerpoint
slides and
paper and read what you missed.

It was interesting delivering the paper actually. I’ve delivered it once
internally within the company, and now once externally, and I’m due to present
it again at the Collaborate’06 event in Nashville. Next time around, I’ll
probably cut out about half of the "best practices" and focus more on sparsity
handling, dimension ordering, memory management and partitioning, as they’re the
factors that have by far the biggest impact on cube performance. When I do the
presentation next time, I’ll go into more detail on scenarios where, for
example, time is the least or the most varying dimension, the actual impact that
compression can have on cube size and rollup time, and what effect logical and
physical partitioning has. Sort of less, more focussed and with examples.

If you sat through the presentation and you’ve got feedback, or comments on
the best practices put forward, drop me a line or add a comment.

At the start of most data warehouse projects, or even during a project, I am sure you as customers try to find answers to the following questions to help you plan and manage your environments:

• Where can I find trend and comparison information to help me plan for future growth of my data warehouse?
• How many cpu’s do other customers use per terabyte?
• How many partitions are typically used in large tables? How many indexes?
• How much should I allocate for memory for buffer cache?
• How does my warehouse compare to others of similar and larger scale?

The data warehouse development team, here at Oracle would like to help provide answers to these questions. However, to do this we need your help. If you have an existing data warehouse environment, we would like to obtain more technical information about your environment(s) by running a simple measurement script and returning the output files to us, here at Oracle. This will allow our developers to provide comprehensive documents that explain best practices and get a better understanding of which features our customers use the most. This will also allow you as Customers, to benchmark your environments compared to other customers’ environments.

From a Company perspective we are also interested to get feedback on features we have added to the database, are these features used, how are they used etc. For example we are keen to understand:

• Which initialization parameters are most frequently used at what values?
• How many Oracle data warehouses run on RAC? on single nodes?
• Is there a trend one-way or the other, especially as data volumes increase?
• Does this change with newer releases of the database?

All results from these scripts will be held confidential. No customers will be mentioned by name; only summaries and trends will be reported (e.g., “X percent of tables are partitioned and Y percent are indexed in data warehouses that are Z terabytes and larger in size.” or “X percent of Oracle9i and Y percent of Oracle10g data warehouses surveyed run RAC”). Results will be written up as a summarized report. Every participating customer will receive a copy of the report.

Terabyte and larger DW are the primary interest, but information on any data warehouse environment is useful. We would like to have as many customers as possible submit results, ideally by the end of this week. However, this will be an on going process so regular feedback after this week is extremely useful.

To help our developers and product management team please download and run the DW measurement script kit from OTN which is available from the following link:

http://www.oracle.com/technology/products/bi/db/10g/dw_survey_0206.html

Please return the script outputs using the link shown on the above web page, see the FAQ section, or alternatively mail them directly to me: keith.laker@oracle.com.

Thank you and we look forward to your responses.