Chapter 4. Modeling Relational Data

After importing the metadata from your relational data source, you must ensure that it meets the reporting requirements of your users. This chapter will cover the modeling of the relationships between the data tables.

The topics covered in this chapter include:

• Dimension and fact tables

• Cardinality

• Determinants

• Role playing dimensions

• Loop joins

• Reflexive and recursive joins

By the end of this chapter, you will have modeled the relationships between the tables in an imported relational data source.

During the import process Framework Manager can automatically create the relationships between tables for you. In this situation it is important to review the created relationships to ensure that they give the correct results for use by the report authors.

Most modelers will choose not to allow the import process to create the relationships between tables. As a result, the modeler must create the relationships themselves.

In creating relationships, Framework Manager uses...

Cardinality

Framework manager uses the cardinality of the joins between tables to identify dimension and fact tables.

Cardinality affects how queries are written, and thus affects the results of the query. Framework Manager allows cardinality to be specified by the modeler or generated based on the criteria while importing from the database.

The general rules that apply to cardinality are as follows:

• Cardinality is applied in the context of a query, and only the cardinalities of items explicitly included in the query are evaluated

• Query subjects defined with one-to-many or zero-to-many cardinalities are always identified as facts

• Query subjects defined with one-to-one or zero-to-one cardinalities are always identified as dimensions

• It is possible to have a query subject that behaves as a dimension in one query and as a fact in another

A query subject that has only maximum (n) cardinality for all of its relationships to other query subjects can be regarded as a fact. This implies that there are...

Determinants

Determinants are one of the most confusing aspects of Framework Manager and are therefore the least understood. As a result, many modelers will never use or create them. However, determinants can play an important role in the performance of your Framework Manager model.

Determinants allow a table with one level of detail (or granularity) to behave as if it has a completely different level of detail. Generally determinants will be used with dimension tables, where fact tables join the dimension table at more than one level in the dimension.

A common example of where determinants would be used is with a date dimension table, with a granularity at the day level. If all the fact tables are joined to this table at the day level, there would be no need for determinants.

A join between a monthly forecast table aggregated at the month level with one row per month, and with this date dimension table would return between 28 and 31 records, depending upon the month. This could cause problems...

Special purpose joins

There are some special purpose joins which cannot be automatically generated during the import of the data source from your relational database. These special purpose joins can only by created by the modeler.

Role playing dimensions

A table that has multiple relationships between itself and another table is known as a role playing dimension. A good example is an Orders Fact, which may have multiple relationships to the Customer Dimension on keys such as Sold To, Ship To, and Bill To:

This situation can create problems with joins in Framework Manager since there are now multiple joins paths between two tables. If you run the Model Advisor (see Chapter 2, Proven Practice), this is one of the of the errors which will be diagnosed and must be fixed in the model.

The solution in Framework Manager is to create a model query subject for each role. The model query subjects query items that can then be renamed to have an appropriate name for their use. A single relationship can...

Where to model the relationships

It is important to model the relationships and set determinants on the objects in the Framework Manager model, but an important consideration is where this modeling should take place.

If you allow Framework Manager to create the relationships for you during the import process then the relationships will be created in the Import (or Database) layer of the model. If you create the relationships or add determinants manually, then you are free to create them in any layer of the model. However, best practice dictates that they should be in the Import or Database layer of the model.

Tip

Wherever you create your relationships and add determinants, do this only in one layer of the model.

Summary

In this chapter, we have learned how to model relational data in Framework Manager. We have learned how to create relationships, and add determinants to the model.

We now have an understanding of the effects of cardinality on determining whether a table is a fact or a dimension table. We have looked at how determinants can affect the way data is aggregated.

We have learned how to model some special relationships which cannot be created by the data import process.

In the next chapter, we will look at modeling dimensionally modeled relational data in Framework Manager.