The first idea is star schema. Figure 1 below is the conceptual schema of the business. We can see there are several primary entities, including members, memberships, salesinvoices, merchandises, etc. An interesting thing we noticed is that there is only merchandises sales has quantity attribute. The reason of this is based on the assumption that sales quantities of other products, such as memberships and oneday pass, are defined as one for every record.
To create a star schema, we need to thoroughly understand how business works and define dimensions and fact first. The purpose to analyze this data is to figure out how to boost sales and profits, depending the past record to target most valuable customers. The information that can help us to understand customer's behavior is purchase pattern and trend, which can be extracted from transaction records. Therefore we define fact as transactions, including event, membership, and any purchases. Attributes in the fact table include surrogate keys from dimension tables, including times, products, members, etc. The common shape of dimension table would be short and fat, consisting of a lot attributes but not tons of data. On the other side, fact table is usually skinny and tall with few attributes and uncountable records. To achieve the objective here, we will need to execute ETL process ( Extracting tables, Transferring into different tables, Loading new tables which are different from the original extracted tables).
In addition, followings are a few terms that are frequently used in dimensional modeling.
- Factless Fact table: a table contains nothing but surrogate keys. Frequently used in promotion to associate relationship among dimensions.
- Transaction ID (degenerate key): an attribute in fact table just to order and count records.
- Market Basket Analysis: using a model to figure out what products are more likely to be purchased together.
No comments:
Post a Comment