SQL Server Data Warehouse Construction and Analysis

Instance construction process and analysis

1Now we will analyze and discuss it with a simple example.Ms SQL ServerData warehouse construction process. In fact, the construction of the Data Warehouse is quite complex. It combines the front-end technology of the data warehouse with strong business requirements. Here is just a simple example to illustrate his general construction process.

2A data warehouse model consists of two parts: one is to consider the useful data that the original data source can provide, that is, the data can be used for the data warehouse after the data is filtered. Second, it depends on the analysis results required by the Company's business layer. This should work closely with the company's senior decision-making layer to fully understand its business needs, because data warehouse users are mainly senior decision makers of the company.[U1]

At this stage, we need to do a lot of preliminary work, because the data in your original database may be very different from the data warehouse you are about to establish, the structure is totally two things. How can you extract your original data as a useful data warehouse? Your original database stores fragmented transaction data, what you need in your data warehouse is converted and extracted statistical data. For example, your original database stores all the deposit and withdrawal records on a daily basis, your Data Warehouse does not care about the data of each record, but wants to calculate the total number of deposits and withdrawals for this month at the fastest speed in the shortest time, if this type of query is placed on the original database, it will lose the meaning of the Data Warehouse. The ultra-large amount of data makes it impossible to query, at this time, you need to convert meaningful data for this query to the Data Warehouse. This is data cleansing, that isETL. There are many methods to clean data, and there are also many details, such as data type matching, data format conversion, and duplicate primary keys when data in a remote data table is centralized, and how you process data to a data warehouse on a regular basis. In my example, I did not strictly go through this step because I do not have a standardized original database or standardized business requirements. I just used the STAR MODEL AND snowflake model to create several typical data warehouse tables. The table relationships are as follows:

 

 

WindowFactFor fact tables,Time,Address,DetailTime Dimension, address dimension, detailed address dimension,DetailAgainAddress. They form a snowflake model. All of them have part of the data.

3Now, the data warehouse has been established successfully, and the next step isOLAPCreate a metadata database on the server. This database is different from the database we previously mentioned. It is a database that stores metadata, for example, the multi-dimensional data set to be created in the next step.Roles, data sources, shared dimensions, and mining models. Then we needODBCConnect the data source established in the data source Manager to the data warehouse.

I created a database.MmmAnd Data sourcesTest, As follows:

 

 

After these tasks are completed, you can use the dimension tables in the data warehouse to create a shared dimension. Now, we use time dimension and address dimension as an example. The creation process is the same.

 

 

 

Click Next to create a time dimension (Time).AddressAndDetailCreate a snowflake model sharing dimension

 

 

 

 

 

Click Next to createDetailDimension. After the creation is complete, it must be processed to take effect.

 

 

 

After creating a dimension, you should create a multi-dimensional dataset. A multi-dimensional dataset is a dataset Based on dimension tables and fact tables. It allows you to quickly access a data warehouse. Our Multidimensional Dataset structure is as follows:

 

Detail(Sreet)

 

Detail(Mark)

 

Address(Province,City)

 

 

Time(Year,Day)

 

 

Multi-dimensional datasetStudyThe creation process is as follows:

 

 

 

Click Next (Study). The process is as follows:

 

 

Next, we should create a mining model.

 

 

 

 

 

 

The post-processing process is as follows:

 

Now a simple data warehouse architecture has been established successfully. We use front-end analysis tools to query the created data warehouse to see if it can meet our simple business requirements.ExcelAs a query tool:

 

 

 

 

 

 

 

 

In additionExcel,English QueryYou can also useMdxFunction DirectlyOLAPQuery

 

 

 

So far, a simple data warehouse has been created successfully, and some simple business queries can be implemented. This instance mainly analyzes the creation process of the data warehouse, further deepens the understanding and understanding of the data warehouse, and further understands the basic concepts.

[U1]Qianti