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. Open the Table Properties screen for a table. Right-click the table and click Properties. The Table Properties screen appears.

   

8. Go to the Extended Properties page for the table.

   

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

   There are conceptual connections 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. Browse to a column in the table.
12. View the extended properties for the column. It contains the mapping. For example, the MS_Description for APPEALSTARTDATE contains the value dbo.[APPEAL].[STARTDATE].

    

13. View the extended properties for the column. It contains the mapping. For example, the MS_Description for APPEALSTARTDATE contains the value dbo.[APPEAL].[STARTDATE].

    

14. Click OK.