I have used the analysis service of Microsoft SQL Server 200 in the past few days, and I will post it to share with you.
Please correct me. Thanks.
1. Requirements:
Establish a book order statistics system
1. Count the number of orders from each library.
2. Count the number of each status of each library's order as a percentage of the library's order quantity.
3. Simultaneously count the original quantity and storage and transportation quantity 2. The main field of the data table, order ID
Unit number (library number)
Unit (library name)
Original quantity, latest status of storage and transportation quantity (current status of the order)
3. Implementation:
1. Open a new project in Visual Studio 2005 and select the Analysis Services project.
Select the data source folder and create a new data source:
The New Data Source Wizard starts:
Click on the above image to create a new connection configuration dialog box.
The configuration method is the same as that of the linked database.
Configure the account to connect to the data source, and then it’s the next step :)
Select the data source view folder and create a new data source view
Select the table containing statistical data, and then it’s the next step:)
Select the cube folder and create a new cube, which is a Cube
Set up fact and dimension tables
Select the measurement value, that is, if it is SQL, you need count, sum and other statistical data.
Cancel all options, and then select the dimension, which is the GROUP by field in SQL (here I selected "Latest Status" and "Unit")
The selection is as above. This is not the final result and will need to be edited manually later. , it will be the next step along the way:)
Open the edit dimension by browsing.
Create a hierarchy with units at the top and states at the bottom.
Rename "Book Distribution Order Count" to "Order Quantity"
As shown above after modification
Switch to the browser, drag and drop the unit to the row position.
Then drag and drop the latest status to the row, the final effect is as follows.
Drag and drop the displayed data, that is, the details
Drag and drop completed effect
Settings are displayed as percentages
final effect
Open SQL Server Management Studio of Microsoft SQL Server 2005 and connect to Analysis Services
Create a new query. The query results are as shown in the figure. The query statement is as follows:
with
set [AllCount] as '[Book Distribution Order].[Hierarchy].[Unit].[Anhui University Library].Children'
Member [Book Distribution Order].[Hierarchy].[Unit].[Anhui University Library].[Total] as 'aggregate([AllCount])'
Member [Percentage of Orders] as '[Order Quantity]/([Order Quantity],[Book Distribution Order].[Hierarchy].[Unit].[Anhui University Library].[Total])',format_string ='#.00%'
select {[Measures].[Order quantity],[Measures].[Storage and transportation quantity],[Measures].[Original quantity],[Percentage of orders]} on columns,
{[Book Distribution Order].[Hierarchy].[Unit].[Anhui University Library].Children} on rows
from [library statistics]
Actually it’s not that difficult
Next time I have time, I will write about how to display query results in ASP.NET 2.0.