Transition to SSAs II: Service Release

In the previous chapter, we introduced the establishment of a simple multi-dimensional dataset model, which can be published to the as service on the local machine and connected to the SQL client on the local machine, but cannot be accessed by other machines. This chapter describes how to publish the analysis service to the network through Web Services for online analysis by other clients, including sqlserver, adomd. NET components, and excel.

 

IIS needs to be installed on the Analysis Server. Here is 6.0, and the configuration process is as follows.

 

1. First, find the following directories and files in the SQL Server Installation Directory of the Analysis Server:

 

 

2. Create a web directory for IIS and copy the above files and folders to the web directory.

 

3. Open IIS and configure an application pool

 

4. The name is testssas, and other attributes are default.

 

5. Add a Web Service Extension

 

6. Attribute Configuration:

 

7. Create a virtual directory and configure the properties as follows:

 

8. Don't forget to change the version number. I used sql2005, which corresponds to. NET 2.0.

 

 

9. Do you still remember the role configured after model creation in the previous chapter? At that time, the Administrator was added, so the IIS permission here also applies to the Administrator

 

 

10. Add the DLL file to the application configuration

 

 

11. Restart IIS.

 

So far, the entire Web release is complete. The above steps seem simple, but errors may occur accidentally. If your configuration is not successful, contact me. Check whether the connection is successful. Use the sql2005 client to connect.

 

 

 

If the query fails, an error is returned. If the query succeeds, select the multi-dimensional dataset and create a new MDX query.

 

 

In the MDX query, we can see a certain structure.

 

 

 

 

Next we will briefly introduce several Connection Analysis Server methods.

 

1. Use. Net's adomd. Net to connect. This code is very similar to ado.net. We will discuss its usage in detail in later chapters, and we will not demonstrate it here.

 

2. Use the SQL client to directly connect to the Analysis Service. The above test method is used. After the connection is successful, you can perform the MDX query. We will discuss the usage of the MDX statement in detail in later sections. Here we only show a few simple examples.

 

First, you need to know several keywords, such as select XXX from XXX Where xxx, which has the same meaning as SQL. The new concept is "row" and "column.

 

Query statement where the date is column and the IP address is row

 

Query statement where the date is a row and the IP is a column

 

Query statement with Filter

 

 

 

Mdx is not difficult, but it is interesting and powerful ..

 

3. OpenRowSet of common SQL statements can be used to open the row set of a multi-dimensional dataset.

 

Select * From OpenRowSet (
'Msolap. 3 ',
'Provider = msolap; persist Security info = false; initial catalog = testssas; Data Source = http: // 10.10.88.102/testssas/msmdpump. dll ',
'Select filter ([dim datetime]. [dim datetime]. Children,
[Measures]. [s count]> 0) on rows,
{[Dim IP]. [dim IP]. Children}
* {[Measures]. [s count]} On Columns
From [test SSAs]'
)

 

 

4. We can use Excel to directly connect to the Analysis Server. For example, the connection method in my excel2007 is as follows:

 

 

Input address

 

Then define the query method in Excel

 

 

 

 

In addition to the above methods, there are also many methods to connect to the analysis service. Many professional companies are working on the tools presented on the client. So far, we have a truly usable SSAs cube.

 

 

We will focus on the MDX statement and the client display mode developed by. Net in future chapters. If necessary, it will complicate the analysis model in the future. I hope you will continue to pay attention to it.