The primary components for physical schema are tables and columns.
A column can store fact or attribute data. Attribute columns are of two types, ID columns and Descriptive columns. If an attribute has more than one descriptive column, then we can create attribute forms for such.
Every table has a primary key which will enable us to uniquely identify any data or record. A primary key can be a single column or multiple column combination key.
Lookup table stores information about attributes.
Relationship tables are crosswalk table which is used to store ID’s from two tables which needs to relate. Ideally , a relationship table is used if we have a many-many relationship between two attributes.
For Employee table, email attribute has a one-one relationship with employee, So we can keep it as an attribute form of employee in Microstrategy. If we specifically needs email as a separate attribute, then we may want to keep it as a separate lookup table.
Fact table stores facts and attribute ID columns. Base fact tables are the ones which stores the fact data at a very detailed level. Aggregate fact tables stores data in a higher aggregate level. A table with item_ID and Sales_Amout can be a base fact and a table with Item_Category_ID and Sales_Amount can be an aggregate fact table. Number of rows in an aggregate fact table will be less than base fact table. Keeping aggregate fact table may increase query performance and reduce query time.