SPE-167887-MS

Getting More Value of Your Production Data by Using Datamart

Lars Petter Pedersen, Statoil; Tram T. N. Tran - Schlumberger

Copyright 2014, Society of Petroleum Engineers

This paper was prepared for presentation at the SPE Intelligent Energy Conference and Exhibition held in Utrecht, The Netherlands, 1–3 April 2014.

This paper was selected for presentation by an SPE program committee following review of information contained in an abstract submitted by the author(s). Contents of the paper have not been
reviewed by the Society of Petroleum Engineers and are subject to correction by the author(s). The material does not necessarily reflect any position of the Society of Petroleum Engineers, its
officers, or members. Electronic reproduction, distribution, or storage of any part of this paper without the written consent of the Society of Petroleum Engineers is prohibited. Permission to
reproduce in print is restricted to an abstract of not more than 300 words; illustrations may not be copied. The abstract must contain conspicuous acknowledgment of SPE copyright.

Abstract

Great   diversity   and   number   of   data   sources   represents   real   challenges   to   every   organization,   when   trying   to  
combine  these  sources  into  one  common,  optimized  and  streamlined  source  supporting  specific  business  needs.    

In  Statoil  our  challenge  was  to  manage  data  from  a  multitude  of  production  data    sources  from  operated  fields  
on  the  Norwegian  Continental  Shelf  effectively;  retrieving  and  structuring  these  data  in  a  format  that  not  only  
makes  sense  to  the  different    production/exploration  teams  and   departments,  but  also  for  other  usage  within  
and  outside    the  organization.    

A  project  was  initiated  to  create  one  common  and  shared  data  source,  which  would  provide  all  users  with  the  
same   type   of   data   for   day-­‐to-­‐day   production   and   exploration   activities.   The   goal   of   this   project   is   to   improve  
data   availability,   efficient   management   of   data,   security   and   access   to   data,   present   in  a   streamlined  manner  to  
both   end   users   and   other   clients   with   similar   data   needs,   thus   protecting   end   users   from   any   underlying   IT  
systems  changes  in  the  future.    

This  was  accomplished  by  harmonizing  the  hundreds  of  views  from  multiple  data  sources;  developed  partitioned  
tables  and  procedures  shared  through  a  common  interface.      

The  result  is  a  solution  that  provides   enhanced  security  for  the  various  data  sources;  improved  efficiency;  a  best  
practice   case   as   it   extracts   intrinsic   value   to   the   organization   as   a   whole   and   a   seamless   access   to   data   when  
required.  
2 SPE-167887-MS

Introduction

Data  management  can  be  a  costly  affair.  One  reason  why  data  could  be  a  challenge  in  any  E&P  operation  is  the  
volume  of  data  which  is  increasing  exponentially  at  a    daily  basis  due  to  more  instruments  and  measurements  in  
the  field.  Data  accumulates  as  exploration  and  production  activities  continue;  a  result  of  new  techniques  in  data  
acquisition  from  the  field  right  to  the  engineers  desktop.    
 
In  addition  to  the  Exploration  &  Development  (E&D)  activities  within  Statoil,  several  studies  are  also  required  to  
generate  the  production  development  plans  for  fields  and  reservoir.  In  order  to  carry  out  the  required  studies,  
engineers  and  geoscientists  require  the  availability  of  reliable  quality  data.    
 
In  Statoil  our  challenge  was  to  manage  data  from  a  multitude  of  production  data    sources  from  operated  fields  
on  the  Norewegian  Continental  Shelf  effectively;  retrieving  and  structuring  these  data  in  a  format  that  not  only  
makes  sense  to  the  different    production  teams  and  departments,  but  also  for  other  usage  within  the  
organization.  Statoil  decided  to  combine  these  sources  into  one  common,  optimized  and  streamlined  source  
supporting  specific  business  needs.    

A  project  was  initiated  to  create  one  common  and  shared  source,  which  would  provide  all  users  with  the  same  
type  of  data  for  production  surveillance,  analysis  and  forecast.  The  goal  of  this  project  is  to  improve  data  quality,  
efficient  management  of  data,  security  and  access  to  data  and  present  a  streamlined  interface  to  both  end  users  
and  other  clients  with  similar  data  needs.    It  adds  value  of  having  standardisation  across  the  corporation  with  
regard  of  time  saving    while    rotating;  basically  engineers  will  see  the  same  interface,  but  different  data.  To  
accomplish  this  we  developed  a  Data  Mart  with  partitioned  tables,  Extract  Transform  &  Load  (ETL)  procedures  
and  a  set  of  shared  included  common  views  as  an  interface.      

The  Data  Mart  is  currently  being  implemented  in  Statoil,  and  is  used  by  a  number  of  assets  with  great  success;  
its  reliable,  secure,  contains  all  necessary  information,  cost  effective  and  flexible  to  changes  in  underlying  IT  
sources.  

Background

In  Statoil  there  were    few  standardised  and  streamlined  ways  for  distributing  production  data  to  applications  
used  for  production  and  reservoir  monitoring  and  surveilance.  Many  of  the  applications  in  use  had  their  own  
solution  and  approach,  which  are  both  ineffective  regarding  cost  and  resources,  but  also  offered  a  less  stable  
and  consisted  solution  to  end  users.  To  improve  this  situation,  one-­‐    standardised  and  centralised  Data  Mart  was  
selected  and  created  as  a  basis  for  future  tools  for  production  surveillance  and  monitoring.    The  first  client  and  
application  for  this  Data  Mart  is  OilField  Manager  (OFM)  from  Schlumberger,  which  will  also  be  standardised  for  
Statoil.    
 
The  standardised  version  of  OFM  will  be  based  on  an  approach  or  a  model  already  used  and  tested  within  
Statoil;  a  central  database  with  a  set  of  views,  a  common  setup  and  a  common  area  with  directories  for  each  
asset  (each  asset  will  have  their  own  directory  based  on  a  standardised  setup).    

Organizing flow and management of data: Situation, considerations and decisions

An  application  like  OFM  requires  several  types  of  data  as  a  foundation  for  adequate  production  surveillance  and  
forecasting.  Retrieving  the  right  information,  with  sufficient  quality,  reliability  and  integrity  is  important.  A  
centralized  solution  will  have  a  lot  of  benefits  for  the  organization;  reduced  costs,  better  use  of  resources  and  
control,  but  centralization  also  have  disadvantages;  Less  user  influence,  huge  amount  of  data  and  challenges  in  
SPE-167887-MS 3

finding  a  standard.  
 
In  Statoil  the  sources  for  OFM  differ  in  many  ways.  There  are  sources  which  contains  well  header  data,  
information  about  daily  drilling  operations  and  off  course  production  reporting.  One  ‘type’  of  data  sources  are  
also  different  depending  on  asset,  since  an  asset  might  use  different  applications  for  i.e.  production  reporting.  A  
solution  with  a  shared  and  customized  database  will  therefore  be  vital  in  order  to  provide  a  streamlined  and  
standardised  solution.  The  database  supports  the  necessary  process  of  collecting  and  structuring  data  from  
multiple  sources,  and  provides  a  set  of  standardised  and  shared  views  to  end  users  regardless  of  the  sources  
data  are  retrieved  from.    

Key  challenges  and  considerations:  

• Multiple  sources  and  different  extraction  techniques  
o Interfaces;  standard  or  customize?  
o Technology;  Features  or  limitations?  
o Integration  technology;  Services?  Database  connections?  Special  API’s?  
• Differences  in  terms  and  data  and  implications  for  conversion  and  standardisation  
o Defining  a  Standard  
o How,  when  and  where  to  convert  data?  
• Data  integrity  and  security  
o Integrity:  How  to  maintain  and  assure  the  accuracy  and  consistency  of  data  over  its  
entire  life-­‐cycle?  
o Security:  Providing  access  and  protecting  data  
• Flexibility    
o Flexible  regarding  time  and  technology  
o Flexible  regarding  ever  changing  environment.  Sources  that  exist  today  may  be  gone  
tomorrow.  
• Costs  and  available  resources  

All  of  these  considerations  are  important  and  have  impact  on  the  chosen  solution;  Data  Mart.  We  need  a  
solution  that  best  would  fit  a  given  area  and  group  of  users,  relatively  easy  and  fast  to  implement.  Cost  effective  
to  maintain,  flexible  and  which  also  would  provide  sufficient  security  and  data  integrity.  

Data warehouse vs. Data Mart

Within  the  IT  industry  a  data  warehouse  is  often  coupled  to  large  and  costly  projects,  which  span  over  several  
years  and  which  becomes  a  ‘heavy  burden’  on  the  entire  organization.  There  are  many  approaches  on  data  
warehouse,  many  ideas,  facts  and  myths.  Our  requirements  and  need  for  data  storage,  and  management,  fell  
against  a  Data  Mart  and  use  of  warehousing  techniques.  
 
The  terms  Data  Mart  and  data  warehouse  each  tend  to  imply  the  presence  of  the  other  in  some  form.  However,  
most  writers  using  the  term  seem  to  agree  that  the  design  of  a  Data  Mart  tends  to  start  from  an  analysis  of  user  
needs  and  that  a  data  warehouse  tends  to  start  from  an  analysis  of  what  data  already  exists  and  how  it  can  be  
collected  in  such  a  way  that  the  data  can  later  be  used.  A  data  warehouse  is  a  central  aggregation  of  data  (which  
can  be  distributed  physically);  a  Data  Mart  is  a  data  repository  that  may  derive  from  a  data  warehouse  and  that  
emphasizes  ease  of  access  and  usability  for  a  particular  designed  purpose.  In  general,  a  data  warehouse  tends  to  
4 SPE-167887-MS

be  a  strategic  but  somewhat  unfinished  concept;  a  Data  Mart  tends  to  be  tactical  and  aimed  at  meeting  an  
immediate  need.  

A  Data  Mart  is  a  repository  of  data  gathered  from  operational  data  and  other  sources  that  is  designed  to  serve  a  
particular  community  of  knowledge  workers.  In  scope,  the  data  may  derive  from  an  enterprise-­‐wide  database  
or  data  warehouse  or  be  more  specialized.  The  emphasis  of  a  Data  Mart  is  on  meeting  the  specific  demands  of  a  
particular  group  of  knowledge  users  in  terms  of  analysis,  content,  presentation,  and  ease-­‐of-­‐use.  Users  of  a  Data  
Mart  can  expect  to  have  data  presented  in  terms  that  are  familiar.  Data  Marts  are  typically  smaller  and  less  
complex  than  data  warehouses;  hence,  they  are  typically  easier  to  build  and  maintain.  Data  Mart  represents  
the  programs,  data,  software  and  hardware  of  a  specific  department.  For  example,  there  is  separate  Data  Mart  
for  finance,  production,  marketing  and  sales  department.  Data  Mart  is  therefore  useful  for  small  organizations  
with  very  few  departments  or  at  places  that  requires  data  management  for  a  specific  business  requirement  
whereas  data  warehousing  is  suitable  to  support  an  entire  corporate  environment.  
 

Data  warehouse  is  built  iteratively  as  it  is  not  possible  to  create  the  complete  data  warehouse  and  then  
implement  it.  Data  warehouse  is  a  vast  concept  that  covers  various  aspects  of  a  corporate  world.  In  contrast,  
Data  Mart  can  be  easily  and  quickly  designed  and  implemented  for  end  users  use.  A  data  warehouse,  unlike  a  
Data  Mart,  deals  with  multiple  subject  areas  and  is  typically  implemented  and  controlled  by  a  central  
organizational  unit  such  as  the  corporate  Information  Technology  (IT)  group.  Often,  it  is  called  a  central  or  
enterprise  data  warehouse.  Typically,  a  data  warehouse  assembles  data  from  multiple  source  systems.  

“Independent Data Mart”

There  are  two  kinds  of  Data  Mart;  dependent  and  independent  (there  also  exist  a  third  option  called  a  “hybrid  
Data  Mart”,  which  is  a  combination  of  independent  and  dependent  Data  Mart).      A  dependent  Data  Mart  is  one  
whose  source  is  a  data  warehouse.  An  independent  Data  Mart  is  one  whose  source  is  the  legacy  applications  
environment.  All  dependent  Data  Marts  are  fed  by  the  same  source;  the  data  warehouse.  Each  independent  
Data  Mart  is  fed  uniquely  and  separately  by  the  legacy  applications  environment.  Dependent  Data  Marts  are  
architecturally  and  structurally  sound.  Independent  Data  Marts  are  unstable  and  architecturally  unsound,  at  
least  for  the  long  haul.  The  problem  with  independent  Data  Marts  is  that  their  deficiencies  do  not  make  
themselves  manifest  until  the  organization  has  built  multiple  independent  Data  Marts.  

 
SPE-167887-MS 5

Figure  1:  Independent  Data  Mart  

 
Designing the Data Mart

Business  requirements,  scope,  sources  and  other  consideration  determine  which  type  of  mart  (or  warehouse)  
you  will  start  to  design  and  deliver,  and  the  conceptual  design  of  the  end  product.  A  Data  Mart  design  should  be  
oriented  towards  the  needs  of  the  end  users.  A  well-­‐planned  design  allows  for  growth  and  changes  as  the  needs  
of  users  change  and  evolve.  There  are  different  steps  involved  in  the  design  stage:  

-­‐ Create  the  logical  and  physical  design  for  the  Data  Mart  
-­‐ Creating  a  wish  list  of  data  
-­‐ Identifying  all  sources  
-­‐ Classifying  data  for  the  Data  Mart  schema  
-­‐ Designing  the  Data  Mart  schema;  Star,  snow  flake,  relational  or  result  set  
-­‐ Moving  from  logical  to  physical  design:  Size  estimates  and  meta  data  considerations  

The  next  steps  include  constructing  the  Data  Mart,  populating  data  from  external  sources  (ETL)  and  accessing  
data  in  the  Data  Mart.  

 
ETL techniques and tools for maintaining the Data Mart

In  computing,  Extract,  Transform  and  Load  (ETL)  refers  to  a  process  in  database  usage  and  especially  in  data  
warehousing  that  involves:  

• Extracting  data  from  outside  sources    

• Transforming  it  to  fit  operational  needs,  which  can  include  quality  levels  
• Loading  it  into  the  end  target  (database,  more  specifically,  operational  data  store,  Data  Mart  or  data  
warehouse)  
6 SPE-167887-MS

ETL Tools

An  ETL  processes  can  be  created  using  almost  any  programming  language,  which  facilitates  methods  for  
connecting  to  a  variety  data  source,  but  building  such  processes  from  scratch  can  become  complex.    There  exist  
a  number  of  different  ETL  tools  to  help  in  the  creation  of  ETL  processes.  By  using  an  established  ETL  framework,  
one  may  increase  one's  chances  of  ending  up  with  better  connectivity  and  scalability.  A  good  ETL  tool  must  be  
able  to  communicate  with  the  many  different  relational  databases  and  read  the  various  file  formats  used  as  a  
source.  ETL  tools  in  most  cases  contain  a  GUI  that  helps  users  conveniently  transform  data  as  opposed  to  writing  
large  programs  to  parse  files  and  modify  data  types  -­‐  which  ETL  tools  facilitate  as  much  as  possible.  

Oracle databases and ETL tools

The  majority  of  the  data  sources  for  the  production  Data  Mart  are  stored  using  Oracle  databases.  Oracle  as  
many  of  the  leading  vendors  within  data  storage  and  management,  delivers  tools  which  can  be  used  to  both  
build  the  Data  Mart  but  also  create  necessary  ETL  procedures.  Oracle  data  integrator  (ODI)  and  Oracle  
Warehouse  Builder  (OWB)  are  both  such  tools.  OWB  is  included  in  ODI,  but  do  not  require  extra  licenses  from  
Oracle,  only  basic  license  and  are  therefore  a  cheap  but  fairly  robust  and  flexible  alternative.    

The production Data Mart

The  Data  Mart  for  production  data  consists  of  the  following  primary  components:    

• A  Data  model  and  data  warehouse  architecture.    

• ETL  (Extract,  Transform  and  Load)-­‐procedures.    
• Operational  procedures  for  executing  ETL.    
• Access  control  solution  for  limiting  /  filtering  access  to  the  data  in  the  Data  Mart.    
• Monitoring  solution  for  monitoring  results  of  ETL.    

Most  of  the  solution  has  been  developed  using  Oracle  Warehousebuilder  (OWB)  and  should  therefore  be  
maintained  from  there.  The  rest  of  the  solution  can  be  maintained  from  any  PL/SQL-­‐tool  (TOAD,  SQL  Developer,  
SQL*Plus  etc).    

The  production  Data  Mart  data  model  consists  of  database  links,  tables,  views  and  materialized  views.  Due  to  
end  user  needs  and  usage  for  OFM,  neither  Star  nor  Snow  flake  data  mart  design  where  applicable.  Our  model  is  
based  on  a  relational  approach.  The  model  for  our  Data  Mart  has  been  created  and  deployed  from  OWB,  and  is  
maintained  from  there  for  documentation  and  consistency  reasons.  The  database  links  are  used  by  ETL-­‐
procedures  and  operational  procedures  to  link  from  the  Data  Mart  database  to  the  source  databases.  Figure  2  
describes  concepts  of  our  Data  Mart  and  use  of  ETL.
SPE-167887-MS 7

Figure  2:  Data  Mart  and  ETL  

The  Data  Mart  contains  tables  for  storing  data  and  views  for  displaying  data.  The  tables  belong  to  the  following  
categories:  
• Data  tables.  They  contain  data  loaded  from  sources  and  constitute  the  “kernel”  of  the  Data  Mart.    
• Staging  tables.  For  some  of  the  ETL-­‐procedures,  the  process  is  so  complex  or  the  number  of  rows  so  
large  that  we  first  load  into  the  staging  table,  and  then  load  from  Staging  to  Data  table.    
• Temporary  staging  tables.  These  are  used  the  same  way  as  the  permanent  ones,  but  they  are  
implicitly  created  and  controlled  by  OWB,  not  explicitly  created  and  controlled  by  a  developer.  They  
are  prefixed  with  OWB$.    
• Tables  for  materialized  views.  The  MVs  are  used  for  the  access  control  solution.    
• Tables  for  status,  monitoring  and  access  control.    
 
 
 
The  views  belong  to  2  categories:    
• Access-­‐controlled  views  for  use  in  any  application,  i.e  OFM.  These  are  named  according  to  the  
agreement  between  production  Data  Mart  and  OFM  application.      
• Unrestricted  views.  These  are  named  the  same  way  but  have  an  added  suffix  _ALL.    
8 SPE-167887-MS

Partioning and PEL

Due  to  large  amounts  of  data  (millions  of  rows)  and  the  need  for  efficient  nightly  ETL,  the  data  tables  that  
contain  daily  production  data  are  partitioned,  and  the  ETL-­‐procedures  for  this  data  use  Partition  Exchange  
Loading  (PEL).    PEL  is  a  technique  for  loading  data  into  the  Data  Mart,  and  by  using  PEL  we  can  instantly  add  or  
delete  data  in  a  relatively  large  target  table.  When  a  table  is  exchanged  with  an  empty  partition  in  the  target  
table,  new  data  is  added.  PEL  can  also  be  used  to  load  new  data  by  exchanging  it  into  a  target  table  as  a  
partition.  For  example,  a  table  that  holds  the  new  data  assumes  the  identity  of  a  partition  from  the  target  table  
and  this  partition  assumes  the  identity  of  the  source  table.  This  exchange  process  is  a  DDL  (data  definition  
language)  operation  with  no  actual  data  movement.  
 
The  partitioning  scheme  used  for  our  Data  Mart  has  been  implemented  in  OWB.  Each  partition  contains  data  for  
one  year,  and  the  partitions  are  named  accordingly  (ex.  Y2011).  New  partitions  must  be  added  every  year.  Apart  
from  this,  partitioning  is  controlled  by  the  RDBMS  (currently  Oracle  11g)  and  needs  no  special  consideration.    
 
PEL  is  implemented  in  the  operational  procedures,  because  each  ETL-­‐procedure  execution  does  not  fill  a  
complete  partition  (it  only  fetches  data  from  one  source),  so  the  scheme  can’t  be  implemented  in  the  ETL-­‐
procedures.    PEL  is  only  used  when  loading  data  for  a  year  at  a  time;  when  loading  all  data,  this  is  done  directly  
into  the  partitioned  table.    
 
General  structure  for  an  operational  procedure  that  loads  data  for  a  single  year  using  PEL:    
1. Truncate  staging  table.    
2. Load  data  for  the  year  from  all  relevant  sources  into  the  staging  table.    
3. Exchange  the  partition  for  that  year  with  the  staging  table.    

ETL-procedures

The  purpose  of  these  procedures  is  to  extract  data  from  views  and  tables  in  the  sources,  transform  these  data  
into  the  format  and  quality  necessary  for  the  Data  Mart,  and  load  the  data  into  the  Data  Mart  tables.    
 
The  procedures  have  been  produced  in  OWB  and  deployed  from  there.  They  should  be  maintained  in  OWB  for  
documentation  and  consistency.  However,  when  deployed,  the  procedures  end  up  as  packages  in  the  OFM  
schema  on  the  server.  It  has  proven  quite  useful  to  extract  SQL-­‐statements  directly  from  the  code  on  the  server  
when  analyzing  issues  with  sources  or  ETL-­‐procedures.    
 
The  OWB  repository  is  stored  in  the  development  database,  and  is  therefore  backed  up  along  with  the  other  
data  in  this  database.    
 
The  tool  used  to  develop  objects  is  called  Design  Center.  It  is  a  client  tool,  so  it  is  locally  installed  on  each  
developer’s  computer.    

Operational procedures

The  primary  purpose  of  these  procedures  is  to  execute  the  ETL-­‐procedures  and  record  status  information  from  
the  execution.  A  few  “housekeeping”  operations  are  also  performed,  as  described  below.    
 
We  load  data  from  several  production  reporting  databases  using  an  agreed  common  source  view  interface.  In  
order  to  be  able  to  reuse  the  same  ETL-­‐procedure  for  loading  data  from  a  particular  view  in  all  the  sources,  we  
use  synonyms  for  accessing  the  views.  Example:    
 
SPE-167887-MS 9

We  load  daily  deferment  data  from  the  view  ZV_OFM_DAILY_DEFERMENT  in  the  production  allocation  
databases.  This  view  exists  in  for  instance  the  databases  for  Assets  within  Statoil.    
 
We  have  a  fundamental  copy  of  the  view,  implemented  as  a  table,  in  the  schema  SRC_EC_COMMON  on  the  
development  server.  The  ETL-­‐procedure  has  been  developed  in  OWB  using  this  table  as  source.    
 
For  each  source,  the  operational  procedure  loading  daily  deferment  data  does  the  following:    
 
1. Drop  the  synonym  ZV_OFM_DAILY_DEFERMENT.    
2. Create  the  synonym  ZV_OFM_DAILY_DEFERMENT  pointing  to  the  view  in  the  source.    
3. Execute  the  ETL-­‐procedure  for  loading  daily  deferment  data.    
 
After  the  last  ETL-­‐procedure  has  completed,  the  operational  procedure  resets  the  synonym  to  point  to  the  table  
in  the  SRC_EC_COMMON  schema.    

Error handling in operational procedures

Errors  in  calling  an  ETL-­‐procedure  will  be  caught  by  exception  handling  in  the  operational  procedure.  The  
operational  procedure  will  attempt  to  insert  a  status  record  in  T_STATUS_DWH_LOAD  for  every  error  (we  expect  
this  to  succeed  for  most  errors).  However,  while  regular  status  records  fetch  their  status  code  from  the  
mapping,  when  an  error  is  raised  we  set  the  status  code  to  -­‐9  in  T_STATUS_DWH_LOAD.  The  -­‐9  error  is  reported  
in  code  in  PKG-­‐packages,  and  may  be  investigated  with  that  as  a  starting  point.  Common  error:  6508:  If  a  
synonym  points  to  a  nonexistent  view,  the  package  will  not  compile  and  the  call  to  the  package  will  fail.    

DB Jobs

We  use  database  jobs  to  implement  the  nightly  load  of  data  to  the  Data  Mart.  This  description  is  for  TOAD,  but  
the  same  can  be  done  in  other  tools  with  PL/SQL.    TOAD  has  a  separate  tab  in  the  Schema  Browser  called  Jobs,  
where  database  jobs  are  created  and  maintained.    

Access control solution

Originally,  the  access  control  solution  consisted  of  only  the  set  of  access-­‐restricted  views  and  surrounding  tables  
and  materialized  views.    However,  the  super  users  determined  that  they  sometimes  needed  a  complete  view  of  
all  data  in  the  data  warehouse,  so  the  unrestricted  view  interface  was  added  late  in  the  development  project.    
 
Unrestricted view interface

We  have  also  implemented  an  unrestricted  _ALL  view  interface  in  the  data  mart,  to  provide  users  with  an  option  
of  read  access  to  all  data  in  the  data  mart.  The  purpose  of  adding  the  view  layer  between  the  user  and  the  Data  
Mart  tables  (instead  of  giving  users  read  access  directly  on  the  tables)  is  to  allow  for  maintenance  of  the  tables  
without  affecting  the  user  interface.    
 
OFM  accesses  data  in  two  ways:  From  an  external  source  and  from  a  local  Access  database.  Most  of  the  
production  Data  Mart  data  is  accessed  directly  from  production  Data  Mart,  but  for  historical  reasons,  Deviation  
data  is  loaded  from  the  Access  database.    
 
In  order  for  the  users  to  have  access  to  the  correct  version  of  the  Deviation  data,  the  OFM  installation  package  
links  the  Deviation  table  to  the  DEV  view  in  production  Data  Mart,  using  the  same  Kerberos-­‐authentication  as  
for  the  rest  of  the  production  Data  Mart  access.  Thus,  this  represents  an  area  of  improvement  in  the  OFM  
architecture  (a  detour  via  Access  when  accessing  Deviation  data),  but  has  no  practical  consequence  for  
production  Data  Mart.    
10 SPE-167887-MS

 
 
 
Using a Data Mart for Production data in Statoil ASA

The  production  Data  Mart  is  currently  being  implemented  in  Statoil,  and  offers  simpliefied  access  to  production  
related  data  through  a  common  set  of  interfaces,  which  makes  it  useful  for  many  different  user  groups  and  
applications  like  Oilfield  Manager  (OFM),  which  currently  are  used  for  reservoir  and  production  surveillance,  
visualization  and  analysis  in  Statoil.  
 
OFM  consist  of  a  set  of  workflow  templates,  multiple  views  of  data  (plots,  reports,  grid  and  bubble  maps),  
calculated  variables/formulas,  fast  filtering,  public  and  private  workspace  capabilities.  Figure  3  show  an  example  
of  OFM  and  how  data  from  the  production  Data  Mart  can  be  used  within  an  application,  to  monitor  wells,  
production  rates  and  other  issues  realted  to  an  oil  field.

Figure  3:  Oilfield  Manager  

 

Value to Organization
 
The  production  Data  Mart  enables  data  at  the  right  time  to  the  engineer’s  desktop,  which  again  enables  the  
engineer  to  make  analysis  and  take  decisions  much  faster.  The  production  Data  Mart  represents  a  one-­‐stop  shop  
for  multi-­‐discipline  Petro-­‐Technical  Engineers  access  to  various  data  within  the  organization,  and  can  improve  
the  ability  to  efficiently  manage  large  volumes  of  acquired  and  processed  data,  providing  the  basis  for  high  
quality  business  decision  making.    Other  values  to  the  organization  include;  
• Adoption  and  Deployment  of  industry  wide  standards  and  practices  to  enable  the  efficient  management  
of  Statoil  Production  data  
SPE-167887-MS 11

• Cost  and  Time  saving  and  efficient  usage  of  resources  throughout  the  company  
• Implementation  of  processes  and  systems  to  build  an  integrated  database  for  long-­‐term  Production  
related  activities,  which  allows  for  the  seamless  integration  of  application  from  several  vendors.  
 
 
Experiences and opportunities

Statoil  reservoir  and  production  engineers  no  longer  have  to  consolidate  their  own  data  or  wonder  if  they  have  
access  to  all  the  data  they  need.  Through  the  production  Data  Mart,  and  by  using  it  against  tools  like  OFM,  the  
engineer’s  now  have  access  to  all  the  data  they  need,  and  also  one  system  to  learn  and  operate.    
 
This  major  streamlining  and  automation  of  production  data  management  processes  and  procedures  allows  the  
engineers  to  focus  on  their  core  duties,  identify  production  problems  early  before  they  worsen,  and  make  better  
decisions  based  on  higher  quality,  more  accessible  and  reliable  data.  All  of  this  translates  into  increased  
hydrocarbon  production  and  engineering  productivity  (i.e.,  manage  more  wells  in  less  time).  
 

Summary
 
In  Statoil,  like  many  other  oil  comapies,  there  are  several  challenges  in  managing  data  from  a  multitude  of    
Production  data  sources  effectively;  retrieving  and  structuring  these  data  in  a  format  that  not  only  makes  sense  
to  the  different  production/exploration  teams  and  departments,  but  also  for  other  usage  within  and  outside    the  
organization.  The  OFM  application  was  selected  as  our  tool  for  reservoir  and  well  monitoring,  and  the  OFM  
projects  goal  was  to  deliver  one  common  and  shared  solution.  A  key  and  central  part  of  this  solution  is  the  Data  
Mart,  which  represents  a  common  source  for  all  users  of  OFM.        
 
This  major  streamlining  and  automation  of  production  data  management  processes  and  procedures  allows  the  
engineers  to  focus  on  their  core  duties,  identify  production  problems  early  before  they  worsen,  and  make  better  
decisions  based  on  higher  quality,  more  reliable  data.    
 
All  of  this  translates  into  increased  oilfield  production  and  engineering  productivity  (i.e.,  manage  more  wells  in  
less  time).  

Acknowledgements

The  authors  would  like      to  thank  Statoil  and  Schlumberger  for  permission  to  publish  this  paper.  
12 SPE-167887-MS

Glossary and Acronyms

DB = Database
DDL = Data Definition Language

E&D = Exploration & Development

ETL = Extract Transform & Load

GUI = General User Interface

IT = Information Technology

MV = Materialized view

ODI = Oracle Data Integration

OFM = OilField Manager
OWB = Oracle Warehousebuilder

PEL = Partition Exchange Loading

PKG = Package

PL/SQL (Procedural Language/Structured Query Language) is Oracle

Corporation's procedural language extension for SQL and the Oracle relational database

RDBMS = Relational database management system

 
SPE-167887-MS 13

References

1. Authors: Margaret Rouse (November 2005) “What is Data Mart?”

http://searchsqlserver.techtarget.com/definition/data-mart

2. Oracle Business Intelligence Standard Edition One Tutorial Release 10g (10.1.3.2.1) “A Data Mart Concepts”
http://docs.oracle.com/html/E10312_01/dm_concepts.htm

3. Oracle Business Intelligence Standard Edition One Tutorial Release 10g (10.1.3.2.1) “Design the Data Mart”
http://docs.oracle.com/html/E10312_01/dm_design.htm

4. Authors: Ralph Kimball, Bill Inmon (May 1998) “Data Mart Does Not Equal Data Warehouse”:
http://www.information-management.com/infodirect/19991120/1675-1.html?zkPrintable=1&nopagination=1

5. Tech-faq (May 2013) “Data Warehouse”

http://www.tech-faq.com/data-warehouse.html

6. Author: Craig Utley ( July 2008) “Designing the Star Schema Databse”
http://ciobriefings.com/Publications/WhitePapers/DesigningtheStarSchemaDatabase/tabid/101/Default.aspx

7. “Data Warehouse Schema Architecture “

http://datawarehouse4u.info/Data-Warehouse-Schema-Architecture.html

8. Author: Dan Power (Sep 2008) “Data Warehouses, Schemas and Decision Support Basics”
http://www.b-eye-network.com/view/8451

9. Authors: Mark Levene, George Loizou “Why is the Snowflake Schema a Good Data Warehouse Design?”
http://www.dcs.bbk.ac.uk/~mark/download/star.pdf

10. Author: David m Walker, Datamgmt.com (Mar 2012) “Using The Right Data Model In A Data Mart”
http://www.slideshare.net/datamgmt/using-the-right-data-model-in-a-data-mart

11. Author: David M Walker (Mar 2006) “White Paper – Overview Architecture for Enterprise Data Warehouses”
http://www.scribd.com/doc/16652563/White-Paper-Overview-Architecture-for-Enterprise-Data-Warehouses

National Petroleum Resource Data Management, K. R. Knudsen,(Data Management Department, Norwegian Petroleum
Directorate, Norway) 1997. SPE-29128

Data Management Acceleration Project: A Case Study in Doing it Right!, J.N. Perrone, Mark A. Priest, Fahad Ali, RasGas
Company Limited. 2009. SPE-13372-MS