Data Warehouse Application (III): Data Warehouse application for SQL Server 2005-Online analytic OLAP

Related articles:
Data warehouse application (1): data warehouse model design
Data warehouse application (2): data extraction, conversion, loading (ETL)

Foreword: Research on data warehouses is not just theoretical. At present, several major RDBMS products, such as Oracle, SQL Server, Informix, and Sybase, can provide users with development tools for data warehouse projects; and some general application development platforms, such as Delphi, can also be used for data warehouses. Provide effective support for project development. We will use the data warehouse tool SSAS provided by Microsoft SQL Server 2005 to perform OLAP (online analysis) on the data in the data warehouse; create an Analysis Services project, define the data source, define the data source view in the SSAS project, and modify the default name of the table; Define multi-dimensional data sets, check multi-dimensional data sets and dimension attributes, and browse the deployed multi-dimensional data sets; use SSAS tools to perform OLAP online analysis on the data in the data warehouse created previously.
1. Background

An e-commerce website mainly sells electronic products, and the products are divided into several major categories, including: computer, mobile phone, keyboard, etc., each category is subdivided into several small categories, there are many Each product has a unique product number. Users can make purchases of goods by registering as members. After the user places an order, the system will automatically generate a unique order number.
On the basis of the data warehouse established above, create AS projects, define data sources, data source views, multi-dimensional data sets, and browse multi-dimensional data sets for OLAP online analysis.

Second, the operation process

1. Analyze the sales volume and sales of all kinds of commodities within [2013-2014 nationwide]

Analysis results:

    Notebook computers have the highest sales volume and notebook memory modules have the lowest sales volume

    Notebook computers have the highest sales and notebook memory modules have the lowest sales
Decision suggestion:

    Nationwide, the purchase of notebook computer products should be increased and the purchase of notebook memory modules should be reduced

    You can moderately reduce the selling price of notebook computers to achieve small profits but quick sales of such products, and further increase the sales of such products to obtain greater profits.
2. Analyze the sales volume and sales of various commodities within the scope of [2013-2014 Fuzhou]

After rotation:

Analysis results:

    Notebook computers have the highest sales volume, while Android phones have the lowest sales volume

    Notebook computers have the highest sales, while Android phones have the lowest sales
Decision suggestion:

    Within the scope of Fuzhou City, the purchase of laptop products should be increased, and the purchase of Android phones should be reduced

    You can moderately reduce the selling price of notebook computers to achieve small profits but quick sales of such products, and further increase the sales of such products to obtain greater profits.
3. Analyze the sales volume and sales of various commodities in [2013-2014 Fuzhou City-all educational backgrounds]

analysis:

Junior high school education:

   Notebook computers have the highest sales volume and the highest sales.

   There is no sales volume for wired keyboards.

High school diploma:

    Laptops have the highest sales and the highest sales.

    There is no sales volume for Android phones.
Decision suggestion:

    Within Fuzhou City,

    For consumers with a junior high school degree, they should increase the purchase of laptop products and reduce the purchase of Android phones.

    You can moderately reduce the selling price of notebook computers to achieve small profits but quick sales of such products, and further increase the sales of such products to obtain greater profits. For Android mobile phone products, you should cooperate with some Unicom, mobile, and telecommunications operators of the school, for example: buy cards to send mobile phones to promote students' consumption of Android mobile phones.

    For consumers with high school education, the purchase of laptop products should be increased, and the purchase of wired keyboards should be reduced.

    You can moderately reduce the selling price of notebook computers to achieve small profits but quick sales of such products, and further increase the sales of such products to obtain greater profits.
4. Analyze the sales volume and sales of various commodities in [2013-2014 Fuzhou City-Junior High School Education]

analysis:

In the Android mobile phone category, Xiaomi 2S mobile phone has the highest sales volume and the highest sales volume.

Among notebook computers, ASUS SS51X Super Notebook has the highest sales volume and the highest sales volume.

            --- Acer V3-772G and Sony SVF15A18S notebook computers have the lowest sales.

            --- Sony SVF15A18S notebook computer has the lowest sales.
Decision suggestion:

    Within the scope of Fuzhou, for consumers with junior high school education,

    In Android phones: we should provide more purchases of Xiaomi 2S, and cooperate with some operators appropriately to promote the sales of Xiaomi phones.

    In notebook computers: the purchase volume of the ASUS SS51X super notebook computer should be increased, and the purchase volume of the Acer V3-772G and Sony SVF15A18S notebook computers should be reduced.

    For the ASUS SS51X super laptop, you can moderately reduce the price of the product, or do more activities to make the sales of the product more efficient.
Data warehouse application (3): SQL Server 2005 data warehouse application-online analysis OLAP