Oz Analytics

Korean Business Intelligence

Next Generation Data Warehousing Conference

IDG, Seoul, South Korea, February 16th 2011

I'm in Korea having just given the keynote address at this conference. I was asked to speak about the future of intelligence (and I will talk about some of my ideas in another post soon) but first a little about the rest of the conference. 

This was my first visit to Seoul and it has been interesting to see business intelligence from a new perspective. The presentations were evenly split between those by vendors with a technology platform emphasis, and client case studies. The 3 case studies were:

• Korea Credit Bureau - using a DWH Appliance (Neteeza & Greenplum) to improve efficiency and reduce costs at this industry owned credit checking business. Future directions were discussed and these included the use of Neteeza Spatial to analyse their credit data using maps.
• LG U+ - enhancing the EDW to support improved customer analytics at a large mobile telco.
• Hanwha General Insurance - integrating data after the acquisition of the Jeil Fire Insurance company.

What impressed me most was the detail given in the presentations and the methodical approach taken to solve the problems. It was nice to see people talk in some detail about their solutions. All too often I find that case studies are short generic stories of little interest. These presentations went into considerable detail and I got real insight into the strategies employed. Very impressive.

I could be generalising, but the Koreans seem to have a no-nonsense, 'just do it' approach to BI. Is this one of the reasons why I now find my life in Australia surrounded by the products of Samsung, LG and Hyundai?

 

Defining Analytics: Data Warehousing

 Following-up to my blog 'Just Tell Me What I'm Doing', this is the second in a series of posts that define the key concepts and terms that make up my analytic world. Everything I do is coloured by my experience actually doing analytics in commercial organisations.

So while I believe these posts will present practical definitions that will be actionable in the business world, I know that there are other worlds in academia and science where they are less relevant. At the very least, people in these areas will gain a better understanding of how business regards analytics. Data Warehouse puritans should perhaps stop reading, take a pill and lie down instead.

---

Bennett's Analytica  
   A Practitioner's Guide To Analytics

---

Data Warehousing

---

The overall process of providing Information to support business decision making is referred to as either data warehousing or business intelligence. The terms are used interchangeably and often as acronyms like DW/BI, DWH, EDW, ODW and BI.

EDW stands for enterprise data warehouse. This is a warehouse that stores data about activities in many areas of an organisation. An EDW or DWH is typically used to support the strategic business requirements of an organisation.

ODW stands for operational data warehouse. This is a warehouse whose primary purpose is to analyse and report on operational (day-to-day) activities.

Data warehousing refers to not only the data warehouse itself (see below) but also the technologies and activities to put data into it, extract data from it, analyse the data, and inform users through reports, dashboards, alerts and data extracts.

(image copyright unknown)

ETL

Is the acronym of Extract, Transform and Load data into and out of a data warehouse. ETL, also referred to as data integration is an important area of analytics and deserves it's own section in any Analytica. Stay tuned for a fuller discussion in another post.

Briefly, ETL is the activity of:

• Getting data into a data warehouse:
• • Extract data from source system files. Source systems are usually the transactional systems of the organisation or are files that contain metadata required to assist in the analysis of the transactional data.
  • Transform the data ready to place it into a warehouse. Often multiple sources of data are involved and similar data from different sources must be modified (i.e. transformed) so that they have the same format and/or meaning. When this is completed the data is said to be conformed.
  • Load the conformed data into the tables of the data warehouse
• Getting data out of the data warehouse:
• • Extract data from the tables of the data warehouse.
  • Transform the data so that it is better organised to support the analytic purpose that the business requires to be performed.
  • Load the transformed data into another database (called a data mart) or file ready for analytic or reporting use. It's called a data mart because it conjures-up comparisons with a supermarket - a place where you can easily pick what you want to consume.

Just to confuse you, ETL is now a popular term for loading and extracting data from any data store or application - especially in IT middleware buses where data is moved between applications.

Data Warehouse

A data warehouse on the other hand is generally defined as a database where the data is stored in such a way that it facilitates reporting and analysis. Other databases in business tend to be places to store data for transactional use. This difference in purpose has a surprisingly fundamental impact on the way that data is stored and the tools used to get data into and out of the database.

Another way to think about the difference is to think of transactional use to be operational where the speed of recording a transaction is important. Data warehouses on the other hand are used to support decisions where speed of recording is less important than our ability to analyse. This is useful but just to confuse you, data warehouses can also be used for operational reporting and analysis.

Most data warehouses store data in rows within tables but a growing number use columns (see Columnar Data Warehouses below).

Data Mart

Data marts are data stores whose data covers specific business functions for a specific community within an organisation. For example a finance or sales data mart. Data marts are often subsets of data in a data warehouse. Data marts simplify analytics as they:

• Remove data that is not relevant to the intended type of analytic insight required by the business.
• Pre-calculate measures that are frequently needed. This means that the measures can be standardised, reports are simpler to design, and run more quickly as they don't have to do as much calculation.

Data Warehouse Appliances

A DWH Appliance is an integrated set of servers, storage, operating system, database and software that is-optimised for data warehousing. Most appliances today use massively parallel processing (MPP) architectures to provide high query performance and platform scalability. In this respect appliances use the same architecture of the traditional very large scale data warehouses from Teradata. Appliance vendors include Netezza and Greenplum.

Normalised versus dimensional Storage Models

A storage model is the way the data is stored in a database.

A normalised model is one where the tables are grouped together by subject area. Each area containing a data category. Examples are data on customers, products, and the like. The advantages of normalisation is that it is easy to load information into the database. The disadvantages are that the database can end up with a large and confusing number of tables. This often makes it difficult for users understand the data and it also slows down the retrieval of data.

A dimensional model is one where transaction data are separated into 'facts' and 'dimensions'. Facts are typically numeric and transaction data. Dimensions are the reference information that gives context to the facts.

An example (from Wikipedia) helps understand the difference:

"A sales transaction can be broken up into facts such as the number of products ordered and the price paid for the products, and into dimensions such as order date, customer name, product number, order ship-to and bill-to locations, and salesperson responsible for receiving the order."

The advantages of the dimensional approach are very big advantages. The first is that the data in the warehouse is easier to understand and use. Secondly, the retrieval of data from the warehouse is fast.

The main disadvantages of the dimensional approach are that making the data easy to understand comes at the cost of a more complex ETL load into the warehouse. This is because maintaining the integrity of the facts and dimensions is complex. The second disadvantage it that it can be difficult to change the data warehouse structure if the organisation changes the way it does business.

Another tricky aspect of the dimensional approach are Slowly Changing Dimensions (SCD). These are dimensions that have data that slowly changes (in case you didn't guess). An example best illustrates the problem:

A common dimension in a data warehouse is one that tracks the sales records of your company's salesforce. Reporting and analysing sales performance should be straightforward but what do you do when a sales person is transferred from one sales team to another. How do you record such a change in your sales dimension so that you remember the past and the present composition of your salesforce? This is important when you do all sorts of performance analytics.

Solving these (and a range of similar) issues involves methodologies referred to as Type 0, 1, 2, 3, 4, and 6 SCDs. The solutions to handling these types are outside of the scope of this post. Stay tuned for a fuller discussion in another post.

Alternative and Emerging Ideas

Columnar Data Warehouses

Wikipedia says it best:

"A column-oriented DBMS is a database management system (DBMS) which stores its content by column rather than by row. This has advantages for databases such as data warehouses and library catalogues, where aggregates are computed over large numbers of similar data items."

The Bus Architecture

A bus in computing is a subsystem that transfers data between computer components or between computers. It has a more specific meaning in data warehousing. Ralph Kimball, one of the founders of data warehousing explains it best. Here is my mashup of his ideas:

You can think of the conformed dimensions and facts of an enterprise as a standard set of connection points for applications — in other words, as a data warehouse bus architecture.

The corresponding bus matrix identifies the key business processes of an organisation, along with their associated dimensions. Business processes (typically corresponding to major source systems) are listed as matrix rows, while dimensions appear as matrix columns. The cells of the matrix are then marked to indicate which dimensions apply to which processes.

In a single document, the data warehouse team has a tool for planning the overall data warehouse, identifying the shared dimensions across the enterprise, coordinating the efforts of separate implementation teams, and communicating the importance of shared dimensions throughout the organisation.

Data warehouse bus architecture. The bus architecture is based on standardised dimensions and facts that let separate data marts, fact tables or OLAP cubes coexist and integrate. In this context, the term bus is not a large motor vehicle. Instead, it refers to the term's early meaning in the electrical power industry (a conductor for collecting electrical currents and distributing them) and commonly used in the computer industry to describe the standard interface specification that lets peripheral devices usefully coexist.

Ralph Kimball is credited with associating these fundamental bus concepts to the data warehouse delivery and presentation environment.

Related Terms and Concepts

Refer also to ODS, Operational Data Store

Refer also to Metadata

---

Comments? Via form below or send feedback to [email protected]         version 0.1 201002

---

Defining Analytics: Data, Information and Knowledge

Following-up to my blog 'Just Tell Me What I'm Doing', I'm starting a series of posts that define the key concepts and terms that make up my analytic world. Everything I do is coloured by my experience actually doing analytics in commercial organisations. So while I believe these posts will present practical definitions that will be actionable in the business world, I know that there are other worlds in academia and science where they are less relevant. At the very least, people in these areas will gain a better understanding of how business regards analytics.

---

Bennett's Analytica  
   A Practitioner's Guide To Analytics

---

Data, Information and Knowledge

---

Information is a collection of related data – often transformed and aggregated – about a topic. In business, that topic is often insight about an operational area or a performance question. In analytics, information is often used interchangeably to mean ‘data’ but data is actually best thought of as something that on its own carries no meaning. The main differences are in the degree of meaning and the level of abstraction being considered. To explain:

Degree Of Meaning

Data, information and knowledge all have some degree of meaning. Even data has meaning at some level. For example:

• data: 99.9 is a number (you know it is probably not text). There is still a possibility that 99.9 is code for a text string or value. 
• information: 99.9 is the percent of transactions successfully processed by an application.
• knowledge: 99.9 is 0.05 below the acceptable level for failed transactions with our customers.

Level Of Abstraction

Data is the lowest level of abstraction, information is the next level, and finally, knowledge is the highest level among all three.

Be careful: abstraction is not the same as summarisation. Summaries may only be the sum of individual pieces of data. This doesn't change the data into information in and of itself. An example:

A list of amounts 5, 8, 5, 2 can be summed to 20. Is 20 information?

Sources

In the business intelligence world data is extracted from fixed sources (batch or in real time, it doesn't matter). Sources are usually either transactional applications or reference data. All sources have meaning. Transactional data has meaning because:

• each transaction is stored in one or more records and this gives context to the individual data items of the record.
• the source application is known and that is information that gives additional meaning to the data.

Reference data also has meaning as the table(s) within which it is stored has an internal meaning due to the relationship between the table rows. Typically this meaning is either hierarchical (for example an organisational structure or products grouped into categories) or group (for example a list of product codes or currencies).

In order for data to become information, it must be interpreted and take on a meaning.

Analytica Illustration

An example (care of Wikipedia):

"The height of Mt. Everest is generally considered as "data", a book on Mt. Everest geological characteristics may be considered as "information", and a report containing practical information on the best way to reach Mt. Everest's peak may be considered as "knowledge"."

Related Terms and Concepts

Refer also to Data

Refer also to Metadata

---

Comments? Via form below or send feedback to [email protected]         version 0.1 201002

---

Just Tell Me What I'm Doing

In my last post I took us all in the local analytic community to task for a shared lack of ambition. Having burnt some bridges there, I thought that I'd finish the job and have a go at putting a torch to the global community at the same time. Forget about moving back to France - I've picked out a nice remote location where the 'net can't find me¹.

So enough about my future. What's my beef this time?

It's our inability to agree on a common language to hold our collective body of knowledge. What do I mean? Well let's start at the top. How do you describe your profession to others? Are you a business intelligence expert? An analytics expert? Data Warehousing? Database Marketing? Performance Manager?

I bet that if I asked 10 of us in similar functions, that we would not agree on a single description. I'm not even sure that we would all recognise that we share the same profession.

You get the picture.

Why does this matter?

This inability to agree on the most basic common language presents a serious barrier to gaining a wider appreciation of what we do. How can we expect the business community to understand us when we confuse then with a mish-mash of words seemingly designed to confuse and obfuscate?

And this problem is exacerbated as we look deeper into the language we use to describe the key concepts of our profession. Have you ever tried to create a set of standard definitions for the foundation concepts of our field of knowledge? Well good luck. I have over the last couple of years but it is not something you can do easily. There is no single source of truth for the [insert here whatever term you like to describe what you do] body of knowledge. I'm going to call this analytics.

There is no single place on the web. No professional group to codify most of the key concepts we use. Wikipedia is a good start - take a look at business intelligence:

"Business Intelligence (BI) refers to skills, processes, technologies, applications and practices used to support decision making."

I can live with that. But what about plain English definitions and examples of:

• Business Analysis Models
• Business Analytics
• Business Data Model
• Conformed dimensions
• Customer Data Integration
• Data Integration
• Data Lineage
• Data Management
• Data Mining
• Data Profiling
• Data Stewards

and the list goes on. Now I'm not advocating that we need to agree on a single definition for everything, but a definition that we can all easily access, edit and make use of would be a great start.

I don't claim to have all the answers, but I am going to try and create a solution. I will start by making a modest effort to publish definitions on this blog by assembling the best information I can glean from the web, research papers, books and presentations. I will try to acknowledge the original sources wherever I can. I will naturally be drawing deeply on the work of many past and present practitioners in the Analytic field – not least Ralph Kimball, Bill Inmon, Howard Dresner, Thomas Davenport and the rest of the BI Community. I will stand on the shoulders of giants and I'm hoping that they won't complain too much.

Into this mix I will blend my own experiences and learnings. My hope is that we will begin to have a practical set of definitions that over time will contribute to the core of our body of knowledge.

In addition to blogging individual definitions, I will collect them together on the CORTEX.

Let the arguments and the plagiarism accusations begin!

.

¹ Campbell Island - a lovely place I visited in 2008. Population 0.

Taser Your Data Warehouse

 Earlier this week I was having yet-another-end-of-year-lunch¹, this time with a bunch of friends who are all data warehouse developers. Now we could have spent the time enjoying the food and wine and talking about what we are all going to do over the holidays.

I say could because of course we didn't. Instead we talked about when a data warehouse is not a data warehouse. Go figure.

If you are still reading this, then you too must be a data warehouse developer so I will dispense with any attempt to include non-experts in the conversation. If this isn't you, just skip to the end of the article and then go to have lunch with friends who are happy to talk about the real world!

We all know that data warehousing is a term that is widely used to describe a database - any database - that somebody (dare I say a vendor?) wants you to think is somehow endowed with mystical powers that normal databases don't have.

To me, a data warehouse exists if the following three conditions are met:

1. A data warehouse is a repository of an organisation's electronically stored data.
2. It is designed to facilitate reporting and analysis.
3. Is optimised for fast data retrieval (as opposed to update) across large data sets.

There is often confusion between a data warehouse and databases used in operational systems. Here is how I think of the differences.

Operational databases are optimised to preserve data integrity while also handling the insertion of transactions into normalised databases. Operational system designers generally follow the Codd rules²of database normalisation in order to ensure data integrity.

Fully normalized database designs (that is, those satisfying the five (of the 12 in total) Codd rules related to normalisation, result in transaction data being stored in many different tables. Relational databases are efficient at managing the relationships between these tables and, in order to improve performance, older data are usually regularly purged from operational systems.

Data warehouses are optimised for data retrieval - by placing all relevant information together in a single table - and analysis - by holding historical information that shows you how the data has changed over time.

So how does this work in practise? Even more importantly, how does this relate to my recent lunch?

Well, somebody mentioned that they had read about a new cloud-based data warehouse built by Taser (yes, that fun-loving bunch that zap criminals for a living). The data warehouse captures tamper-proof video recordings of police 'incidents' and stores them for use as evidence in subsequent criminal trials. 

I argued that this isn't a data warehouse because it wasn't designed to facilitate analysis, reporting or even fast retrieval. Others argued that it supported analysis by providing the raw video footage of incidents in the same way that other warehouses provide data to business intelligence applications. Their logic was that the warehouse doesn't do the analysis but it is the analytic software that delivers the insight.

I still don't buy it and we (amicably) agreed to disagree.

On reflection, perhaps it comes down to our different worlds. I'm only interested in data warehouses because of the opportunities they give me to drive greater insight and better decisions in the business.

I think my data warehouse friends have the more modest aim of giving the business the data that makes insight possible - but it's up to the business to make smart use of this data. Isn't this just like a scientist inventing a cure and the drug company making it available to people? Sounds like a big enough challenge to me.

But hold on, doesn't that make me a drug pusher?

---

Since the lunch I have tracked-down an article about this and you can read how Taser Builds Cisco-Based Data Warehouse. In case the article disappears, you can also access it here.

¹ That's what happens to you when you stop doing actual business intelligence work and become a manager. It's a tough life.

² Ted Codd was the IBM scientist who invented the relational model for database management. Interestingly, Codd also invented OLAP back in 1993 when he published an article in Computerworld. The article was then retracted by the publisher when they found out that Codd wrote it while working as a consultant for Arbor Software, the inventors of Essbase (which later merged with Hyperion and is now Oracle).

Outsourcing Your Data Warehouse

I was intrigued by a recent post on the CORTEX about Optus signing a three year deal with HP Enterprise Services (formerly EDS) to outsource several core applications including the data warehouse. I don't have much more detail than that but my first thought was to wonder about the wisdom of an organisation outsourcing core capabilities like analytics.

Later on I thought a little more about this and I wondered if my first reaction was wrong - and hence this post.

I believe that an ability to compete on information (i.e. analytics) is a core capability that many firms have to possess to succeed. Does that mean that they also have to keep in-house all their information management activities? Is the entire information chain from source to decision a core activity? Or can you outsource the data warehouse/data stores and still be good at 'doing analytics'?

I don't have any hard evidence either way so I will make a judgement call and say no. What's my justification for this? Here's my logic:

• A warehouse is a lot more than a place to store data. Building and operating one means that you learn a lot about your data, how it is used operationally, what it is - and frequently what it is not. I'm talking here about the quality of your data and the insight that this knowledge gives you about the soundness of the data you use to make decisions.
• The people that make ETL happen can also tell you a lot about what really goes on in your transactional applications and business processes. The best ETL and data warehouse experts I have worked with have always had a fantastic ability to support business people and ensure that they become better information users.

Sure, you still gain insight from your analytic activities that make use of the data warehouse but I would worry about the impact of outsourcing on my business decision makers ability to access the knowledge locked in the (HP) ETL and data warehousing team.

This may not be as important in organisations with well developed MDM infrastructure that robustly captures and makes available this information to the business. I'm pretty sure that few such organisations exist today.

So Optus will (presumably) save some dollars in the short term but as the months go by the hidden costs associated with less insight will greatly outnumber these. Ultimately, this may all be a moot point as based on discussions I have had with Optus BI staff over the years, as an organisation, they do not seem to be very good at analytics anyway. A lot of good analytics people have left Optus over the years and they all seemed to be frustrated at the inability to make an impact on this.

I'm also aware of the irony of my opinion being based on 'experience' when I spend my career advocating fact-based decision making. So let's just keep this post between you and me. OK?

Quick Guide To Choosing An MPP Database

I have just come across an interesting blog by Charlie Wardell in the States. If you are looking for a short discussion on the relative merits of MPP (Massively Parallel Processing) database products from Teradata, Netezza and Vertica then read "...And This Database Is Just Right".

He has also recently looked into the popularity of Google searches on Teradata, Netezza, Vertica, ParAccel, and Greenplum here. If you're about to place a bet on which vendor will increase their market share then this is a good place to start.

A Brief Analysis Of 100 Australian BI Projects

Yesterday I gave a talk at the Mastering Business Intelligence with SAP 2009 conference. The objective was to talk about the core reasons for 'doing' BI/IM/DWH and to identify best practises. I did this by looking at a bunch of projects that I have been involved with over the last several years. The key features of the projects are:

• Lessons from approximately 100 BI projects
• Projects were active in the period 2003 - 2009
• Tools used:
• • Analytic Tools:
  • • 50% in environments with SAP
    • 30% SAS
    • 20% with Cognos
    • 20% Microsoft
    • 20% Other
  • Underlying databases:
  • • 60% Oracle DB
    • 20% DB2
    • 20% SQLServer/MySQL
    • 10% Teradata
• 70% Australian, 30% Asian
• Mainly in Banking, Insurance and Leisure industries.

The most common benefits of the projects were the development of the following capabilities:

• Better understand current performance (issues)
• Identify efficiency opportunities (insight)
• Reliably predict future performance
• Make better decisions because they are based on correct data and insight (trusted)
• Reduce risks (less surprises)
• Enhance agility (in handling new requirements)
• Promote a shared understanding (same language)
• Reduce internal conflicts (SSOT)

Best practises observed were:

• Data Quality champions were active in both IT and BI Departments.
• Domain experts were drawn from across the enterprise
• Data owners existed for every data item and measure in the BI platform
• Data Council members are empowered to make decisions: 1. All business data and their definitions - including reference data; 2. Enterprise performance measures and their calculation basis; 3. solutions (business processes and IT); 4. Monthly meetings to solve DQ issues

Finally, some of the lessons to be drawn are that to develop these capabilities you need to keep in mind:

• BI investments often spread over several years. It is not usual for stakeholders to fund projects for this period without seeing significant returns on their investments in the first 12 months.
• The actual solution(s) is usually only known when well into the BI project (once detailed business data requirements are agreed).
• 30 – 50% of your investment in BI will be spent on meeting additional requirements that were not included in your original business case.

And perhaps most importantly: The more successful you are, the more new requirements you will have to meet - with the same original budget.

I think that the talk went OK as I had a few questions from the floor but only the audience can give you honest feedback. If you are interested, here is a slideshow of the presentation:

Download 20091020Presentation

DW 2.0 Asia Pacific Summit

I am happy to announce that the Business Intelligence Group is supporting the DW 2.0 Asia Pacific Summit to be held in Sydney at the end of October.

We are not only supporting the event but I am going to talk about 'Decision Mashups - Strategies for Combining Structured and Unstructured Data for Better Decision Making'.

Once again I make the offer for any readers of this blog, or members of the CORTEX to make contact so that we can meet up at the event.

Here is the brochure for the summit:

Download DW 2.0 Asia Pacific Summit Brochure

I also see that Eugene Dubossarsky from Presciient and moderator of our Forecasting Special Interest Group on the CORTEX is to speak on 'The Future Role of Analytics: Arms Races, Success Stories and the Changing Role of the Analys'. Eugene always has an interesting perspective and I'm looking forward to his talk.

Local Intelligence Beta Testers Wanted

As most of my readers know by now, I and a small group of like-minded people are building an online community for business intelligence, information management, data warehousing, analytics and performance management enthusiasts in Australia and New Zealand. Called the CORTEX for short.

We launched officially this year and membership is approaching 700 people. It's great to know that so many other people like the concept. Everyone is welcome and membership is free. OK, that's the end of the PR bull.

We are trying to regularly add new features and in the spirit of Google Labs we have just launched a one-click search function that scours the CORTEX for news and views on specific leading ANZ organisations. Currently we have the following industries covered:

• Construction and Property
• Financial Services
• Government, Health and Education
• Manufacturing and Distribution
• Publishing and Media
• Retailers
• Rural, Minining, Oil and Gas
• Telecommunications
• Tourism and Hospitality
• Transportation
• Utilities

I know that we have missed several organisations and I ask anyone who is active in these (or other) industries to:

• Beta-test the search function
• Suggest other organisations we should include
• Add news to the CORTEX local industry channels if you know of something that we are missing.

All comments and criticisms are welcome!