Data Mining

Data mining is inbuilt into SQL Server 2000 and helps the user define models containing grouping and predictive rules that can be applied to relational databases or multidimensional OLAP cubes. These models can then be used to automatically perform sophisticated analysis of data and identify trends in data for decision making. Data mining models can be built using the Analysis Services wizard based interface or programmatically using the DSO object model. Visual tools in the data mining Wizard simplify the complex modeling process and pivot table support is an advantage. The PiovtTable Service also provides capabilities for building and training data mining models. Integration with third party data mining tools is also possible

Enterprises seek to maintain copies of data in different systems to improve the overall performance of the systems. SQL Server 2000 replication feature allows enterprises multiple copies of data to be maintained in different systems and also enables the enterprise to keep these copies in synchronization. Disconnected and mobile users find this a great advantage as they would have synchronized data the moment they connect to the server hosting the departmental database. The SQL server replication also supports replication of data in data warehouses. It can also replicate the data from any data source or to any data source that uses OLE DB access.

The replication model used by SQL Server is the publish-subscribe model for distributing data. The server which is the publisher which contains the data source to be replicated. An article is defined for each table or database object to be used as a data source and one or more articles are organized to form a publication. The subscriber is the server that receives the replicated data. The subscriber server defines the subscription to a particular publication and specifies when the subscription will receive the publication. It also maps the articles to the tables and other database objects in the subscriber. A distributor is a server that performs the task of distributing the data from the publisher to the subscriber. SQL Server 2000 allows three kinds of replication:

Snapshot replication copies data or database objects as they exist in the moment of time. These are generally scheduled and time based and is used when the data is relatively static.

Transactional Replication is a process of replicating data by first applying a snapshot and then capturing transactional data and transmitting them to the subscriber. Transactional integrity is maintained in the process and modifications are synchronized between the publisher and the subscriber. This replication is used when dynamic replication is required to capture modifications to data.

Merge replication is a process of replication that is adopted when multiple sets of subscribers work together. The multiple subscriber data generated is then combined and merged back into the publisher. The subscribers and the publisher are then synchronized with a snapshot and all changes are tracked both on the subscriber and the publisher. Later the changes are also merged to define a single version of the data. Conflict ‘resolvers’ are a defined set of rules for resolving conflicts in the merge replication data. This technology is used when the Publisher and the subscribers need to work autonomously or when multiple subscribers must update the same data.

The Administration of replication services is simplified in SQL Server 2000. The Enterprise manager supports configuring and monitoring replication. The SQL DMO interfaces can be used for programmatically configuring and monitoring the process. Microsoft ActiveX controls help in embedding replication technology in custom applications and replication can be scripted using T-SQL systems stored procedures.

English Query

The English Query feature of the SQL Server 2000 allows users to build applications that customize themselves to user queries in plain English. Users can query, using ad hoc queries. The English query Administrator defines all logical relationships between tables and columns of a database or cubes in the data warehouse or data mart and the user can enter a character string to query the data. The engine analyzes the string against the relationships defined and returns to the application an SQL statement or a MDX expression, which in turn will return the reply to the user’s question.

Microsoft has introduced the following features in English Query to make it user friendly: 

1. Greater integration with Microsoft Visual Studio, Analysis services and Full text search. 
2. A graphical user interface for English Query authoring 
3. An XML based language for persisting English Query model information.

Meta Data

Meta Data services, inbuilt in SQL Server 2000, is a facility for storing, viewing and retrieving descriptions of objects in the applications and systems. Meta data defines the structure and meaning of data, applications and processes. It consists of ActiveX interfaces and information models that define database schema and data transformations as defined by Microsoft data warehousing framework. The goal of the framework is to provide meaningful integration of multiple products through shared meta data. Business and technical meta data are combined to provide an industry wide standard for storing the schema of production data sources and destinations.

Meta data services is a popular means of storing DTS packages in data warehousing scenario, because it provides lineage for packages. DTS also uses the meta data services storage to enable transformations, queries and ActiveX scripts to reused by heterogeneous applications.

Tool developers add meta data management support to their products with ease as the service provides a platform for building such management capabilities into dedicated tools. Shared meta data is used to deploy data and application structures across heterogeneous platforms and development environments. Common definitions are so constructed that tools and applications can interpret the same meta data and transform them into application specific structures. It is the point of integration as it is abstract, contains essential details that remain constant whatever the implementation strategy. This flexibility allows the user to separate design from implementation.

Meta data services help the user store meta data constructs, version meta data objects and relationships, track meta data created, allocate workspaces to isolate modifications to a specific set of objects, import and export meta data structures in XML format.

The service supports the MDC Open information Model (OIM) specifications and defines a common format for storing descriptions of entities such as tables, views, cubes or transformations. It specifies the relationships between these entities. The OIM support enables application developers use these facilities for rapid development and interchange with other tools and applications. The other components of the SQL Server 2000 can also be stored in the Meta data services repository.

We will learn how meta data repositories are used in Microsoft SQL Server 2000, in greater detail, a little later in the tutorial.

TrackBack URI for this entry

Comments (0)

Subscribe to this comment's feed

Show/Hide comments

Show/Hide comment form