Shulou(Shulou.com)06/01 Report--

Fact table: to record a normal business data of the company, you need to provide 4W, when (When), where (Where), who (Who) did what (What). In order to facilitate the follow-up analysis, when designing the fact table, we should provide detailed dimensional information and measurement information as much as possible.

For example, when we are talking about the data warehouse of a logistics company, how to design a fact table, we follow the following process to land.

1: the purpose of the user: first of all, we need to know the purpose of the user, value-oriented, solve the user's problem, then the user will propose that the current operating cost of the company is too high, how much money needs to be saved this year.

2: define indicators: for the above goal, we need to work with users to analyze this goal, which indicators can be used to measure the expected direction of this goal, that is, what we call the split goal, this process will communicate many times, repeatedly split and merge the previously defined indicators, for example, we finally agree that there are loading rate, vehicle mileage, and cost per kilogram. The four indicators of cost per kilometer.

3: index subdivision: after the index is confirmed, we need to subdivide the index data (multi-level index), such as the loading rate, whether we need to distinguish the loading situation in the early, middle and late periods according to the business scenario, the loading rate of different models (5KGGG10kg), and whether we need to know clearly the cost items of the cost per kilogram and per kilometer. For example, driver cost, fuel cost, vehicle depreciation cost and so on.

4: define relevant fields and logic: with the determination of the above detailed metrics, we will only confirm the logic and numerical caliber of each metric with the user, and design the fields of the good table to verify whether the data application meets the user's purpose. if there is the following system, we confirm the relationship between the source system and the backstage table field for each fact table field.

A: vehicle dispatching system: when which car ran which line.

B: settlement system: how much is the cost of each car?

C: loading system: traffic information of each car and each line.

5: with these indicators, we quickly achieve and land on the ground, provide some analysis reports, make some improvements to the front-end business, and then verify whether our indicators are changing in a good direction, while the cost is also reducing. so our whole scheme and indicators are well defined. If the indicators are changing in a good direction, but the cost is not reduced, then we have to redefine whether some indicators are correct. Just keep iterating until we reach the user's goal.

Dimension table: it is an extension of more detailed information to the dimensions in the fact table. How do we design it when we define a dimension table? For example, there is a dimension such as customer in the fact table. We communicate with users. When they look at the customer, what information they are mainly concerned with and what purpose they want to achieve. Users generally want to achieve user differentiated management and sales. So we need to grasp how to distinguish the customer's differentiated information:

1: basic attributes: for example, customer size (1000, 10,000), region (Shenzhen, Guangzhou, etc.), industry (manufacturing, Internet, etc.).

2: by customer behavior: level (key customers, retail customers, etc.), sales volume (0-100,101-500,501-1000, etc.).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.