dimension table with "like" condition

Question

In an effort to scale some existing code, we are removing logic from CASE statements and putting it in dimension/lookup tables. Thus instead of CASE WHEN 'F' THEN 'Female' WHEN 'M' THEN 'Male' is now handled by a gender dimension. (A very simplistic example)

But some of our CASE statements use LIKE operators, including comparison strings like 'ABC%DEF' and '%ABC%'.

I am looking for suggestions on the best way to handle this. Our fact tables have over a billion rows, and growing, so efficiency is key. We are Oracle 11gr2, if that is applicable.

Answer 1

Here are four approaches that each have advantages and disadvantages depending on what the current bottleneck is.

Normalize by adding a surrogate key column

--Sql Server syntax (My Oracle knowledge seems to have 
--                   rusted due to lack of use)
CREATE TABLE orders (
  order_id int not null primary key, 
  order_date DateTime not null,
  source_code char(10) not null, 
  amount decimal(10,2) not null
)

SELECT 
    order_id, 
    CASE 
        WHEN source_code LIKE '%A11' THEN 'Acme 2011'
        WHEN source_code like '%A12' then 'Acme 2012'
    END advertising_campaign
FROM orders

Transforms into:

CREATE TABLE orders (
  order_id int not null primary key, 
  order_date DateTime not null,
  source_code_key int not null,
--  source_code char(10) not null, 
  amount decimal(10,2) not null
)


CREATE TABLE source_codes (
  source_code_key int not null primary key, 
  Advertising_campaign varchar(20) not null )

INSERT INTO source_codes ( advertising_campaign)
SELECT DISTINCT 
   CASE 
        WHEN source_code LIKE '%A11' THEN 'Acme 2011'
        WHEN source_code like '%A12' then 'Acme 2012'
    END advertising_campaign
FROM orders

SELECT 
    order_id, 
    source_codes.advertising_campaign
FROM orders INNER JOIN source_codes 
            ON orders.source_code_key = source_codes.key

This approach is the cleanest from a pure dimensional modeling perspective. The logic in your CASE statements would be applied in an ETL process and the size on disk is reduced. In Sql Server the CPU overhead is increased because a join is typically more cpu intensive then a CASE statement (I assume this is also true for Oracle). If I/O is the bottleneck then this is a trade-off worth making and is the preferred approach.

Join on Business Key to Lookup table

CREATE TABLE orders (
  order_id int not null primary key, 
  order_date DateTime not null,
  source_code char(10) not null, 
  amount decimal(10,2) not null
)


CREATE TABLE source_codes (
  source_code char(10) not null primary key, 
  Advertising_campaign varchar(20) not null )

INSERT INTO source_codes (source_code, advertising_campaign)
SELECT DISTINCT 
    source_code, 
    CASE 
        WHEN source_code LIKE '%A11' THEN 'Acme 2011'
        WHEN source_code like '%A12' then 'Acme 2012'
    END advertising_campaign
FROM orders

SELECT 
    order_id, 
    source_codes.advertising_campaign
FROM orders INNER JOIN source_codes 
            ON orders.source_code = source_codes.source_code

This approach would require one row in source_codes for each distinct source_code. Size on disk increases due to source_codes table. Disk IO remains the same or increases depending on the ability of the source_codes table to fit in memory. This approach makes sense only in the case where the queries are CPU bound due to logic in the CASE statement that is horrendously complex.

Keep Existing Schema

If the bottleneck is the CPU and the cost of computing the CASE statements is less than the cost of joining to a another table then keeping your existing queries may be the best bet.

Keep Existing Schema + Move CASE logic into client application/reporting tool

This approach makes sense if the bottleneck is network bandwidth to the client or the CPU load is too high with either the JOIN or the CASE statements. By moving the complex CASE logic off the server you can distribute CPU load. This would be the most intrusive solution and should be considered last resort.