Microstrategy BI – Physical schema components


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.

Advertisements

Comment please...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s