OLEDB

1
OLE-DB

• Nir Feldman
• Oded Zilinsky

2
Overview

• Motivation
• What is OLE-DB
• How does it work
• Usage example OLE-DB for Data-Mining
• OLE DB compared to ODBC

3
Motivation

• Personal computing - Desktop data integration
• Use different type of data sources within an
  application
• Commercial sector - Uniform access to
  heterogeneous data
• Lower the cost of maintaining gateways
• Lowers the cost of building data warehouses
• Efficient access to COM objects
• Every significant Windows product generates and
  consumes COM objects
• Allow future extensions

4
Data Integration

• Traditional databases
• ISAM, relational, hierarchical, network
• File system, file formats
• Personal databases
• Spreadsheet, documents, desktop DBMS
• E-mail, project management
• Stock data feeds, WWW data sources
• Computed Data
• Data derived in multi-tier environments
• Business rule enforcement in workflows

5
Overview

• Motivation
• What is OLE-DB
• How does it work
• Usage examples - OLE-DB for Data-Mining
• OLE DB compared to ODBC.

6
COM brief description

• COM - Component Object Model
• The object model for all Windows software
• Today - the industry-standard
• Becoming available on non-Windows platforms
• UNIX

7
OLE Brief overview

• Object Linking and Embedding
• Used to integrate programs (mainly MS)
• Can use one application from within another,
  without leaving the originals interface
• e.g. using a excel spreadsheet within a word
  document

8
What is OLE DB?

• The way to access data in a COM environment
• Access all corporate data regardless of storage
  format or location
• Components consume, expose and share data through
  interface, and events
• Simplified programming model for corporate
  developers (ADO)

9
What is OLE DB?

• Flexibility leads to
• better performance
• better error handling
• interface for non-relational data sources
• An open technology - available free

10
Interoperable Components
CONSUMERS
PROVIDERS
Docs, Files
Text Query Engine
Spread Sheet
Relational Query Engine
COM / DCOM
DBMS
SQL
Desktop Application
Stock Prices
Cursor Engine
Geo-Data
Development Tool
Mail
11
Example - Access Diverse Data Sources

• Find e-mail messages received from Seattle
  customers, including their addresses, within the
  last two days to which a reply has not been sent.
• SELECT m1., c.Address
• FROM MakeTable( Mail, c\mail\smith.mmf) m1
• MakeTable( Access,
  c\access\dbs\Enterprise.mdb, Customer) c
• WHERE m1.Date gt Date( today(),-2 )
• AND m1.From c.EmailAddr
• AND c.City Seattle
• AND NOT EXISTS
• (SELECT FROM MakeTable( Mail,
  c\mail\smith.mmf ) m2
• WHERE m1.MsgId m2.InReplyTo )

12
Overview

• Motivation
• What is OLE-DB
• How does it work
• Usage example - OLE-DB for Data-Mining
• OLE DB compared to ODBC.

13
Approach

• Define standard interfaces for DBMSs
• Define common abstractions to access data
• Rowsets
• Make all data sources database-enabled
• Make it really easy for data sources to expose
  and share their data

14
(No Transcript)
15
(No Transcript)
16
Object Instantiation
Data Source
IDBCreateSession
ITransaction
CreateSession ()
DBSession
IDBCreateCommand
CreateCommand()
Command
ICommand
Execute()
Rowset
IRowset
17
Command Interfaces

• Encapsulate query processing functions
• Formulate commands
• Text (SQL)
• Stored Procedures

RowSet Interfaces

• Universal abstraction for tabular data
• Base data, query results, computed data, schema
  information

18
Notification
Synchronizing consumers of a shared rowset
IRowsetNotify
IRowset
IConnectionPoint
IRowsetNotify
ROWSET CACHE
IRowsetNotify
19
Watches

• Data source notifications
• Alert clients that relevant data in the data
  sources have changed
• Upon notification, clients refresh their data
• Various refreshing mechanisms available

20
Transaction Interfaces

• Keeping resources on commit or abort
• row handles, bookmarks, pending changes
• Nested transactions

21
Remote Data Access

• Efficient, transparent data access across thread,
  process, and machine boundaries (network)
• based on DCOM

22
Security

• Interfaces support Authentication, Authorization
  and Administration
• Philosophy
• Expose relevant security functionality that is in
  the security models of the providers or operating
  systems

23
Overview

• Motivation
• What is OLE-DB
• How does it work
• Usages examples - OLE-DB for Data-Mining
• OLE DB compared to ODBC.

24
What is Data Mining?

• A process of data exploration and analysis using
  automatic or semi-automatic means
• Exploring data scanning samples of known
  facts about cases.
• knowledge Clusters, Rules, Decision trees,
  Equations, Association rules
• Once the knowledge is extracted it
• Can be browsed
• Can be used to predict values of other cases

25
Why OLE DB for DM?

• Make DM a mass market technology by
• Use existing technologies and knowledge
• SQL and OLE DB
• Changing DM market perception from private to
  open
• Increasing the number of players
• one tool works with multiple providers
• Dramatically increase the number of DM developers

26
Integration With RDBMS

• Customers would like to
• Feel that DM is a native part of their database.
• Perform predictions as relational queries
  (tables in, tables out)
• Therefore
• Data mining models are relational objects
• All operations on the models are relational
• The language used is SQL (w/Extensions)
• The effect every DBA and VB developer can become
  a DM developer

27
Overview

• Motivation
• What is OLE-DB
• How does it work
• Usages examples - OLE-DB for Data-Mining
• OLE DB compared to ODBC

28
OLE-DB Vs. ODBC

• Provides access to non-relational data sources
• provides an object model that lends itself to OO
  programming based upon COM
• New technologies uses OLE-DB and not ODBC
• SQL Server 2000
• .Net framework
• ODBC will not likely be updated
• Backwards compatibility - if ODBC driver but no
  OLE DB provider, can still interface
• ODBC relied upon conforming with different
  standard levels. There were so many that each
  application had to practically configure itself
  to each RDBMS

29
A bonusPractical example!
30
.NET Usage example

• public DataSet execute_select_query(string
  sql_query)
• //Prepare command for execution.
• OleDbCommand command new OleDbCommand() comm
  and.CommandText sql_query
• command.Connection this.connection
• OleDbDataAdapter adaptor new
  OleDbDataAdapter(command)
• DataSet return_data_set new DataSet()
• //Fill the DataSet using the adaptor object.
• adaptor.Fill(return_data_set)
• return return_data_set

31
THE

• END