Sqlserver bi-data warehouse design (2)

In our system, our data granularity is day. Well, since it is a simplified version, we don't need to be so arrogant. What needs analysis and design are all saved, and we will go directly to the database design below. Our database consists of four dimension tables (department, game, item, and time) and one fact table (game transaction data fact table ).

This is because the project has not really started, but it has been in contact with Bi for more than half a year. In the past few days, I have prepared a simplified Bi project. On the one hand, I will give a reference to my friends who are just getting started with Bi, and on the other hand, I will make preparations for my project to be started, so that I can take into account the details of some projects. I also hope to provide some suggestions and experience for Bi researchers. Because our case was developed on the Bi platform of Microsoft sqlserver2005, we will first post a screenshot in webcast, which mainly reflects the use

The architecture of the sqlserver2005 Bi project.

Okay. Let's get started. The requirement for this project is very simple, because it is a simplified version. This is a sales analysis software for the items used in the game. It includes several concepts, such as game, sales department, item, transaction amount, number of transactions, and publication singular. What we need to do is to use games, departments, and items to achieve cross-analysis of transaction amounts, transactions, and other data.

In our system, our data granularity is day. Well, since it is a simplified version, we don't need to be so arrogant. What needs analysis and design are all saved, and we will go directly to the database design below.

Our database consists of four dimension tables (department, game, item, and time) and one fact table (game transaction data fact table ).
Department dimension table

Game dimension table

Item dimension table

Time Dimension Table

Transaction data fact table

Because our case is relatively simple, the relationship between dimensions and fact tables is also relatively simple.

Single, is a simple star architecture.

This section is written here first. In the next section, I will write the ETL section of this project in detail.