View Information about Fact and Dimension Relationships in a BBDW Database

You can view table and column properties maintained as MS_Description extended properties. These are essentially comments about tables and columns. There are no foreign keys on tables in a BBDW database. So at the table and column level of the database, comments provide guidance about the relationships between facts and dimensions.

1. Open SQL Server Management Studio.

2. Connect to the database engine. Click File > Connect Object Explorer. The Connect to Server screen appears.

3. Fill in the necessary information and click Connect.

4. Open Object Explorer. Click View > Object Explorer.

5. Browse to a table in a BBDW database.

   

6. Browse to a table in a BBDW database.

   

7. To open the Table Properties screen for a table, right-click it and click Properties. The Table Properties screen appears.

   

8. Select the Extended Properties page for the table.

   

9. An MS_Description property has a value that describes the table.

   Note: Conceptual connections exist between dimensions and fact tables. But when you look at a fact table in a BBDW database, you'll notice there are no foreign keys. Relationships between fact tables and dimensions are catalogued through comments on the tables and columns in a BBDW database. These are stored in the extended properties of the tables and columns as MS_Description values. Further, BBDW database tables are not arranged in a pure schema. Views that abstract BBDW database tables create the star schema. For more information, see Star Schema.

   

10. Click OK.

11. In Object Explorer, browse to a column in the table.

12. To open the Column Properties screen for the column, right-click it and click Properties. The Column Properties screen appears.

13. Select the Extended Properties page for the table. It contains the mapping. For example, the MS_Description for APPEALSTARTDATE contains the value dbo.[APPEAL].[STARTDATE].

    

14. Click OK.