Designing a Meta Data Repository

If the decision is made to custom build a meta data repository in-house, you have to choose between an E-R design and an OO design.

Entity-Relationship Design

Because an E-R design represents the meta data objects and their relationships explicitly, and because E-R designs are intuitive and easy to understand, many organizations choose this type of database design for their meta data repositories. To illustrate the intuitiveness of an E-R design, assume that the physical meta model contains four objects (Database, Table, Column, and Attribute) and that these objects are related in a one-to-many cardinality, as shown in Figure 10.5.

Figure 10.5. Example of Entity-Relationship Design. (Short vertical lines indicate "one," and the crow's feet indicate "many.")

This type of database structure is easy enough to understand that technology-savvy business people could write their own ad hoc Structured Query Language (SQL) queries against it. However, if these ad hoc queries are executed against a large centralized meta data repository, performance could be a problem. It is relatively easy to write poorly performing SQL queries. In addition, since each meta data object is implemented as a separate table, there will be dozens of tables, and some queries will contain very complicated JOINs across many of these tables. This could also affect performance.

Table 10.5 lists the advantages and disadvantages of E-R designs.

Table 10.5. Advantages and Disadvantages of Entity-Relationship Designs

Advantages	Disadvantages
E-R designs are easy to read and easy to understand.	Changes and enhancements may require a database redesign, as well as unload-ing and reloading the meta data repository.
Because of the intuitive and explicit nature of the design, queries can be written with relatively simple SQL statements.	The physical meta model is fairly large, with many objects and many relation-ships, which makes the architecture somewhat complex.
E-R designs are easy to implement as relational database structures.	Meta data objects and their relationships must be very well defined and under-stood for the physical meta model to be accurate.

Object-Oriented Design

As popular as E-R designs may be for meta data repository databases, OO designs are more efficient. Since they are more abstract, they result in fewer tables, run queries more efficiently , and are much easier to expand. Using the same example as above, the OO model would contain only three objects, but these objects would be more generic, as shown in Figure 10.6.

Figure 10.6. Example of Object-Oriented Design. (Short vertical lines indicate "one," and the crow's feet indicate "many.")

This type of database structure is not easy to understand, and business people will probably not be able to write their own ad hoc SQL queries against it. It is not intuitively obvious that the object named Object contains the instances (rows) for all meta data objects, such as database instances, table instances, column instances, attribute instances, and so on. It is also not obvious that the object named Object Type differentiates the various meta data object instances by assigning the appropriate label of Database, Table, Column, Attribute, and so on. And the untrained eye would have an even more difficult time discerning that all relationships between these object instances are reflected in the third object named Object Relationship. However, it is easy to see that expanding this type of generic design is as simple as adding new instances (rows) to these three objects (tables).

Table 10.6 lists the advantages and disadvantages of OO designs.

Table 10.6. Advantages and Disadvantages of Object-Oriented Designs

Advantages	Disadvantages
OO designs are extremely flexible; they will not need any database redesigns when changes are necessary.	Since the object named Object contains all instances (rows) of meta data, this table will become very large. This may affect access performance.
OO designs are simplistic and therefore easy to maintain and to enhance.	Queries are much more difficult to write and will require many recursive JOINs. Advanced SQL knowledge is required.
OO designs are easy to implement as object-oriented database structures.	OO designs require a high learning curve. The very abstract physical data model is difficult to comprehend, and the extensive rules take time to understand.

Designing and building your own meta data repository may not be within the scope of your BI project. There may be no budget and no staff for a separate meta data repository project. Maybe your organization would prefer to license (buy) a meta data repository product. As with all off-the-shelf products, a licensed meta data repository product will probably not be the perfect solution, but it may be the most cost-effective one. It would certainly be better than ignoring meta data altogether.