MSAS : Meta data Repository Page - 2

The Database information model is the basic model that stores database information. It stores metadata about data sources and data destinations. This model is derived form the Unified Modeling Language Information model.

• The SQL Server Information Model, the Oracle Information model and the DB2 Information Model are used to store information specific to the data base system. These are derived from the Database Information model. 
• The Database Transformation Information Model is used to store information about data transformations. The model is derived from the Database Information model. 
• The Data Transformation Services Information model stores data transformation information specific to Microsoft’s Data Transformation Model. This is also derived from the Database Information model. 
• The OLAP Information Model is the basic model for multidimensional data structures. This model is also derived from the Database Transformation Information Model. 
• The Microsoft OLAP Information Model stores multidimensional data information that is specific to Microsoft OLAP services. This model is derived from the OLAP Information Model.

The repository is physically located in a set of tables in the database. The default location of the repository is the msdb database.

The Repository Engine handles meta data storage and retrieval. It uses the information model and stores meta data instances described in the Information model in the SQL tables and columns. Caching is used to optimize access to meta data. Versioning is an important feature introduced by Microsoft. This implies that changes are captured on the run and the repository maintains a history of the updates. This process allows the user query the changes over time.

XML Interchange help users easily import information into the repository from custom sources or other tools. The Extensibility feature of the repository lets users add information to the models to cover topics specific to their organization or tool. The Microsoft Repository SDK enables the extension of information models that the repository uses. Users can even store information about locations that are not part of the OIM, and the model can be extended to add a new object or relationships to track the information. New properties can be added to track information for existing objects.

Integration with the Microsoft Data-Warehousing Framework

The repository is a component of the Microsoft Data warehousing framework and server strategy. It adds value to the warehousing package and can be accessed by clicking the Meta data node under the Data Transformation services. Thereafter, it is possible to explore the relational schema information and package and lineage information. Relationships between columns and packages can be explored and updated.

Data Transformation Services is the Extraction, transformation and loading tool of SQL Server 2000. The flexibility of this tool helps users populate the warehouse and save packages directly into the Repository. The lineage feature of DTS requires the Repository as it tracks how data in the warehouse was calculated and when it entered the warehouse. Data can be saved into the repository by saving the DTS information into the SQL Server Repository. The Advanced tab on the package properties can be used to set the scanning options and thereby call the OLE DB scanner to load all source and target catalogs into the Repository. If the scanning options are not set, DTS creates DTS Local catalogs as reference for all source and target catalogs and this will make the location of the databases inaccessible.

Saving DTS transformations into the repository has its own problems. Choosing a query as a transformation source, results in the source becoming an object that is not part of the OLE DB imported data. Connection to the original source also becomes difficult. Users need to use a script to perform a simple transformation and choose the source columns explicitly. In this case all transformation data is captured. However, the problem of choosing a query as a transformation source can be solved by writing a program to resolve the references in a repository or by using a custom model along with DTS model to store the source and target mappings.

Versioning is used for the package object in DTS. However, it replicates all subordinate objects with each save. This enables the user to go back to any version of a package and see exactly how the data was transformed. This feature is very important from the point of view of tracking down problems related with data months or years after it has entered the warehouse. The versioning works well if appropriate scanning options to import the relational schemas are used.

OLAP Services provides multidimensional analysis for the data warehouse. By using this facility in the Repository SDK, all definitions for the OLAP data can be imported into the repository. SQL Server 2000, OLAP services stores meta data directly in the repository. Connections to the measures and dimensions will have to be made manually or programmatically based on the data available in the repository model.

The English Query lets the user define a semantic model for a database and then translated the English phrases into SQL. The Repository SDK is provided with an utility to import data from the English Query into the repository. English Query also has an inbuilt utility to import and export models from the repository.

OLE DB (relational schemas) are imported into the repository to provide a base set of information to begin the documentation of the warehouse. Database schemas can be imported from any OLE DB or ODBC compliant data source. The import can be run from the Enterprise manager by right clicking the Metadata tab under data transformations services and selecting ‘Import Metadata’. Full versioning is used to load the data and the changes can be tracked over time. The versioning feature enables the preservation of descriptive information, comments and rescanning the catalog does not impact on the information.