Ms SQL Server 2000 administrator manual series-29. Use Microsoft SQL Server Analysis Service

29. Use Microsoft SQL Server Analysis Service
Analysis Services Overview
Install Analysis Services
Use Analysis Services
Summary
Microsoft SQL Server 2000 Analysis Services (formerly called OLAP services) is a component designed in SQL Server 2000 to assist you in Online Analytical Processing (OLAP, this component allows you to access and collect data in your data warehouse and data supermarket. In this chapter, you can learn what analysis services components are, how to install them, and how to use them. In addition, you can learn new functions of Analysis Service in SQL Server 2000. Since this book is intended for SQL server administrators rather than application developers, we will only discuss topics such as installation, setup, and management of analysis services, the Application Development Section is not under discussion.
________________________________________
Description
In this chapter, we may constantly mention the two exclusive terms data warehouse and data mart. Data warehousing can be defined in several different directions. One of them is to regard it as a warehouse of commercial data, which stores online transaction processing (OLTP) from the company) the historical data and current data obtained by the system. Data supermarkets are similar to data warehousing, but the information they contain is only related to one aspect of the company. For example, a company may have a data warehouse containing accounts payable, accounts receivable, human resources, and other data, as well as a data supermarket containing accounts payable information. Data that makes up data warehouse and data supermarket is usually planned into a star-like structure description or Snowflake-like structure description, which will be described later in this chapter. Most of the concepts discussed in this chapter can be used in data warehousing and data supermarkets. Unless otherwise stated, we use data warehousing to represent both databases.
________________________________________
Analysis Services Overview
　
Analysis Services is a set of tools that can help you develop and manage data for online analysis and processing. Analysis Services is composed of the Analysis Service server, English query, and other supporting components. The Analysis Service Server constructs a cube of data to assist you in multi-dimensional analysis. The word cube is used to describe a collection of summary or summary data to process complex analysis queries, such as monthly sales results and sales plans. (Cube will be described in detail in the "OLAP cubes" section later in this chapter .)
In multi-dimensional analysis, multiple queries search for databases from different perspectives or dimensions. For example, assume that a bicycle dealer database still maintains sales data for the previous year. One query in a multi-dimensional analysis job may be searching for customer purchasing habits, while the other query searches for monthly sales, at the same time, you can query the sales performance of a bicycle or component of a specific style. Although data is shared to all queries, each query views data from different perspectives (dimensions.
Analysis Service component
　
Analysis Services provides tools and genie that allow you to access multidimensional data. Analysis Service is composed of the following components:
• Analysis administrator: provides a GUI to use analysis services, such as creating cubes, managing security, and browsing data sources.
　
• Data Warehousing architecture: a group of components and APIs that implement the SQL Server 2000 data warehousing function.
　
• Data Conversion Service (DTS): assists in loading and transforming data to a data supermarket or data warehouse. DTS is composed of the import wizard and the export wizard, which can be used to migrate and convert data. For details about DTS, see Chapter 24th.
　
• Storage Mechanism (repository): contains some interfaces, database structure description models, and pre-defined data conversion methods to conform to the data warehouse architecture. Because data conversion is based on rules, their definitions can be stored for future reuse.
　
• Data Mining: provides an algorithm for defining and implementing multi-dimensional cubes.
　
• English query: converts an English-language problem to an SQL statement that can be executed on a database.
　
• Extended Markup Language (XML): a language that provides standard formatting and data presentation. XML is an important component of applications for data conversion between applications and can be used to publish data to the Internet.
　
In this chapter, we will combine these components to provide an integrated tool, just like combining all the parts of a puzzle.
OLAP cube
　
OLAP cube is the main form of data in analysis services. Cube is a multi-dimensional representation of detailed data and summary data. Detailed data is the data of a specific data column, and summary data is the summary data. Cube is designed based on data analysis needs. Each cube represents a different business entity, such as sales and inventory. Each plane of the cube represents the sentiment of different dimensions in the data. In other words, cube is composed of many different data orientations. Therefore, it is called a "data cube 」.
Analysis Services cube can be created through two types of database structure descriptions: Star Schema description and snowflake schema description. Schema description (Schemas) is actually a developing topic, but to properly describe analysis services, we will give a brief look at the structure description here. Both types of structure descriptions are composed of fact data tables and Dimension Data Tables. Analysis Services aggregates the data in the data table to create a cube. Let's take a closer look at this process.
Fact table
　
Fact tables are data tables that store historical data in data warehousing. These historical data are the core information of data warehousing. In our bicycle dealer example, this information is a transaction record (including database transactions and sales transactions) that occurs in the bicycle dealer ). The data in this record covers the transaction date, transaction type, sales item, total transaction amount, customer name, and salesperson name. This record can be used as the basis for multi-dimensional analysis.
As you can see, fact data tables are data centered on commercial transactions. These transactions can be the sale of an item, credit card transaction, profit, and so on. Basically, fact data tables record some types of business events.
Fact data tables in data warehouse are the largest data tables in the database and the most frequently operated data tables. As you may imagine, fact tables can contain millions of records and occupy more than 1 Tb (or 1024 GB) of space.
Dimension Table
　
A dimension table is used to define fields in a fact data table, such as the salesperson name, transaction type, or project. This process is similar to the normalization process. Both of them extract useful data to accelerate processing efficiency. Fact data tables contain historical transaction information, while dimension data tables contain information that shows how to obtain useful materials from fact data tables. In other words, a dimension table is used to indicate the meaning of the data contained in a fact table.
For example, for a fact data table that contains a sales record, a dimension data table may contain information related to the sales representative, it can be used to create abstract data such as the monthly sales of each salesperson, while the other dimension data table contains region information, which can be used to create summary data such as the monthly sales of each region.
Dimension Data Tables are not as large as fact tables. On the contrary, Dimension Data Tables are usually small and often contain only a few data columns. Data warehousing generally only has one or two fact tables, but there are several dimension tables.
Structure Description
　
Star Schema is a common data warehousing architecture. It consists of a fact table and dimension table. The star structure description indicates that a fact data table is surrounded by Dimension Data Tables and is in the shape of a star. Each dimension data table is equivalent to a data row in a fact data table. These Dimension Data Tables are the basis for Architecture Analysis, so that we can analyze data in fact data tables.
In the snowflake schema, several Dimension Data Tables are connected to each other before being joined to a fact data table. In other words, the layers of Dimension Data Tables are created first, each layer is equivalent to a data row in a fact table. Figure 29-1 shows the star structure description and snowflake structure description.

　
 
Figure 29-1 Structure of star and snowflake
Data Summary
　
Analysis Services is based on data in a dimension data table to create a Summary of data rows in a fact data table. For example, employee-related dimension data tables can be used to create employee-based sales data summary information. Project-related dimension data tables can be used to create project-based Data summary information. Because the summary is based on the dimension data table to create different data sections (that is, dimensions), you can construct a virtual cube of data, as described above.
The OLAP cube created by Analysis Services is essentially a summary function for calculation based on the star or snowflake structure description. You use the analysis services Wizard to create these summaries, and then use the summary to create a business model and make business decisions.
Metadata)
　
"Relay data" is used to describe data-related data. Therefore, the summary and detailed data used to describe database data are called relay data. In our example, the sales data table is the dimension data table that represents the data status. The abstract information we set up (sales data of sales personnel and sales data of various items) is the relay information. The main benefit of Analysis Services is to build the capabilities of relay data (summary data tables or summaries). With analysis services, you can simply create relay data and then apply it to various tasks, without having to manually maintain the data.
SQL Server 2000 data analysis Enhancement
　
SQL Server 2000 provides many new functions in data analysis and data warehousing. These new or enhanced functions include some tools and information that can be used to improve data analysis processing. In this section, you will learn the most important parts of these new features.
Enhanced Data Collection Functions
　
Analysis Services has integrated new data collection techniques to discover data associations between relational databases and OLAP cubes. These associations can be added to the existing OLAP cube and additional data analysis has been provided. One of the unique new features of data collection is Microsoft demo-tree. Microsoft demo-tree uses precision classification techniques and algorithms to analyze data. Then, it constructs one or more decision trees for Prediction and Analysis of new data. For example, our bicycle dealers can use this technology to construct a decision tree to analyze the historical credit data and transaction data of potential customers, so as to predict the customer's credit risk.
Another new feature of data collection is cluster usage. The cluster technology used for data collection is different from the cluster type described in Chapter 12th. When Analysis Services executes a clustering, it uses an algorithm called the Nearest Neighbor Method to record the data into a cluster with similar features. In many cases, these associations are hidden or imperceptible. Therefore, the cluster technology can be said to open another door to data analysis.
In addition, the SQL server data collection component also contains some new genie and dialog boxes, making data collection easier to use. These new features allow DBA to perform most of the relevant work more quickly when establishing and maintaining a data supermarket or data warehouse.
Dimension Enhancement
　
SQL Seever contains several new dimension tables. SQL Server now supports the parent-child dimension, Relational OLAP (ROLAP) dimension, and writable dimension.
The parent-child dimension allows the parent-child hierarchy between Members in the source data table. An example of parent-child relationship is a component combination structure from each part. A single part of a parent can have many child components, that is, children. When performing data analysis, you can use the parent-child dimension to enhance the connection between child components and this part.
The ROLAP dimension can be used to address the capacity limit of the standard multidimensional OLAP (molap) mode used by analysis services. In molap mode, a dimension can contain almost 5 million members. Once the growth of members exceeds this limit, the ROLAP dimension is required. The ROLAP dimension can be very large, but the molap mode is much better than ROLAP in querying member sets. Therefore, the ROLAP mode should be defined only when the dimensions are very large.
When you use Write-enabled dimension, dimension members can be updated by the analysis administrator and client applications that support write-back. You can use the SQL Server role to control the write access to dimensions by client applications. SQL Server roles are described in Chapter 34th.
Security Enhancement
　
SQL Server 2000 also includes enhanced security capabilities to provide better protection for your data for business analysis. After all, these data may be quite sensitive. These new features include changing the security of Dimension Data Tables, data security features, and support for additional verification technologies.
Dimension Data Tables are now operated in the role-based security mode of SQL Server. According to the definition of each role, you can restrict its access to individual dimensions, levels, and members. In addition, you can set the read and read/write permissions for these resources. SQL Server 2000 supports the role security of both fat and NTFS systems.
SQL Server 2000 allows you to implement roles at the Cube data level. The analysis administrator contains a dialog box that defines the security of a Data Grid. You can control the role's access to any cube data grid combination. In addition, the read and write permissions of each role can be different.
Because SQL Server 2000 includes Windows 2000 security mode, when a user or application needs to access the cube and its data, SQL Server 2000 supports Kerberos communication protocol, NT License Manager security support provider, or any other provider that uses the Security Support Provider Interface (sspi) provided by security users to perform verification actions. This allows you to have consistent overall security at all levels of SQL Server installation.
English query Enhancement
　
In SQL Server 2000, the English query function has been enhanced, allowing you to integrate Microsoft Visual Studio 6.0 and other integrated products. English query allows program developers to integrate English statements into applications, rather than T-SQL statements. In addition, the new graphical user interface tool also provides great help for the development of English query statements. SQL Server also contains the SQL Project Wizard, which can automatically establish the basic database structure to support English query, making the English query environment easier to set and use. This wizard scans database data tables and creates related SQL server components.
Install Analysis Services
　
Analysis Services is a component of SQL Server 2000. To install analysis services, follow these steps:
1. From the installation menu, click SQL Server 2000 components, and then click Install analysis services. A welcome screen is displayed.
2. Click Next to enter the software authorization contract dialog box. After you read and agree to the authorization, press Yes.
3. The select component dialog box is displayed, ranging from 29 to 2. In this dialog box, you can select the analysis services component you want to install. Click the check box before each component name to select all components. If the component has been installed, you cannot change the status of the check box. To select a new location to install analysis services, Click Browse. After you select the destination data folder, click Next.
　
 
Figure 29-2 select component dialog box
4. The data folder location dialog box is displayed, ranging from 29 to 3. This dialog box is similar to the Select destination folder dialog box. However, you need to select the location where the data is stored. You can specify a location different from the default value by browsing. After you select the data folder location, click Next.
　
 
Figure 29-3 "storage data folder location" dialog box
5. The Select Program data folder dialog box is displayed, ranging from 29 to 4. You can select the program data folder to be placed in analysis services (that is, the position where analysis services appears on the Start Menu ). The default value is generally acceptable. Follow the next step to complete the installation.
　
 
Figure 29-4 Select Program data folder dialog box
After you have installed analysis services, you can install english query. Although English query can be said to be part of the overall analysis services service, their installation is separated. You do not have to install english query to use analysis services. To install english query, follow these steps:
1. From the SQL Server 2000 installation menu, click the SQL Server 2000 component, and then click Install English query. The installer first installs Microsoft Data Access Components (MDAC) and Microsoft Visual Studio components. After these components are installed, a welcome screen is displayed. Press continue to continue the installation.
2. The software authorization contract dialog box for Microsoft English query 2000 appears. After you read and agree, press I agree.
3. The Microsoft English query 2000 setup dialog box is displayed, ranging from 29 to 5. You can select the required installation type-complete or run-time only. Complete will install all the components, and run-time only will allow you to specify which components to install. You can also specify the installed data folder, but the preset data folder is generally acceptable. Unless you are an expert in English query, press complete. The English query components component is installed. Press OK to complete the installation.
　
 
Figure 29-5 Microsoft English query 2000 setup dialog box
After installation, if you want to use the analysis services and English query Meta, please start/set/Microsoft SQL Server/analysis services. In the analysis services subdirectory, you have the following three options:
• Analysis administrator: enables the main components of analysis services. This component includes some genie and utilities to help you start the Analysis Services Service.
　
• Online books: enables online analysis services files.
　
• MDX sample application: enables an application example provided by analysis services.
　
Use Analysis Services
　
Now we have introduced Analysis Services and their installed programs. Let's take a look at how to use the services it provides to establish and manage your data warehousing. In this section, we need to first set a data source, then create an OLAP database on the data source, and finally create a cube on the database.
Set Data Source
　
To connect Analysis Services to the SQL Server database, the first step is to set an ODBC system data source for the server. You can use the odbd data source utility in the system management tool to do this. To set the system data source, follow these steps:
1. Press Start/ASSEMBLY/system management tool/Data Source (ODBC). The ODBC data source administrator dialog box is displayed, ranging from 29 to 6.
　
 
Figure 29-6 "ODBC data source administrator" dialog box
2. Click the system data source name, which is 29-7. You will find that some existing data source lists are listed in the system data source box. Some of these data sources have been defined as online to SQL Server. Depending on the database usage, sometimes we need multiple ODBC data sources to refer to the same database, which of course permits. In this example, we will create an ODBC data source that references the northwind database.
　
 
Figure 29-7 "System Data Source Name" of "ODBC data source administrator"
3. Click Add. The new data source dialog box is displayed, 29-8. In the menu bar, select SQL Server and click Finish.
　
 
Figure 29-8 create a data source dialog box
4. The new data source to SQL Server dialog box is displayed, 29-9. You must specify the name and description of the data source, and specify the SQL Server to be online. Click Next to continue.
　
 
Figure 29-9 create a new data source to SQL Server dialog box
5. In the following dialog box 29-10, you can specify the Authentication mode to be used when the user is online to SQL Server. You can use the network login identification code for Windows NT authentication or the login identification code and the user's input password for SQL Server Authentication. (The User Authentication mode is described in Chapter 34th .) At the bottom of the dialog box, you will see a preset check box. If you do not need to connect to SQL Server at this time to obtain preset settings for other options, change the check box to unselected status. Click Next to continue.
　
 
Figure 29-10 verify Mode
6. In the following dialog box, you can specify the database, database name, and ANSI mode to be used. Analysis Services allows you to select the database to be connected. Therefore, you do not need to provide a default database name. However, it does not matter if you specify a default database, because other applications may also use this data source name (DSN ). After you finish, click Next.
　
 
Figure 29-11 specify the default database
7. in the following dialog box 29-12, you can change the language of the SQL Server System message to another language, enable the translation function, and specify the region settings, specifies the location of record files for long-running queries and driver statistics. After you complete the settings, click Finish.
　
 
Figure 29-12 specify the language and other settings
8. The ODBC Microsoft SQL Server Settings dialog box is displayed, ranging from 29 to 13. This dialog box shows that a new ODBC data source will be created and all the settings you have selected for the data source will be listed.
　
 
Figure 29-13 Summary dialog box of "ODBC Microsoft SQL Server Settings"
9. You should test your settings according to the test data source. After you press the button, it starts to test the connection to the database. Once you have successfully completed the online test, press OK to start using this DSN.
________________________________________
Description
SQL Server must be in the executed status to set and test the data source.
________________________________________
Create an OLAP database
　
Now that you have set and tested the ODBC data source, you can prepare to create an OLAP database. Creating an OLAP database also includes setting an existing database as an OLAP database. You must specify the data tables to be used as fact tables and dimension tables.
________________________________________
Description
In this section, we will set the northwind database as an OLAP database. This database does not have all the attributes of the Data supermarket or data warehouse, but we will use it for demonstration, because it is built in SQL Server, in addition, this demonstration process allows you to easily apply it to practical applications.
________________________________________
When creating an OLAP database, you will use the analysis administrator, cube creation wizard, dimension creation wizard, and storage design wizard. To create a database, follow these steps:
1. Press Start/ASSEMBLY/Microsoft SQL Server/Analysis Services/analysis administrator. The analysis administrator window is displayed, ranging from 29 to 14.
　
 
Figure 29-14 analysis administrator window
2. Expand the analysis servers data folder in the left pane, and then expand your server name data folder. Right-click the server name and select Add database from the shortcut menu. The database dialog box is displayed, ranging from 29 to 15. Enter the database name and give it an introduction. In this example, we name the database northwind_olap.
　
 
Figure 29-15 Database dialog box
________________________________________
Description
When you expand your server name data folder, you will find that an example database has been installed in the analysis administrator. If you select the check box for the sample application in the installation component dialog box when installing analysis services, the database named foodmart 2000 is automatically installed.
________________________________________
3. Return to the analysis administrator window as confirmed. If you expand the analysis servers data folder and expand your server name data folder, a new database is added. (This database has been named, but it has not yet been connected to the SQL server data source, but don't worry. We will start online later .) Expand the database data folder (in this example, the northwind_olap data folder) to display the data source, cube, sharing dimension, collection model and database role, 29-16.
　
 
Figure 29-16 show OLAP database
4. Right-click the cube folder, move the cursor to the new cube in the shortcut menu, and select the genie in the submenu. Welcome to create a cube genie, 29-17. This wizard is used to select the data source to be specified at the Cube level.
　
 
Figure 29-17 welcome to "cube build Genie"
5. Select a fact data table screen from the data source as shown in the next step, from 29 to 18. To select an SQL Server database, press add data source.
　
 
Figure 29-18 select a fact data table from a data source
6. The data link content window is displayed, ranging from 29 to 19. You can specify a data source for this cube in the provider label. However, in this example, we will use the online label to select the data source we just created.
　
 
Figure 29-19 "provider" tab in the "Data Link content" Window
7. in the online volume tag (Figure 29-20) in the data link content window, select the data source name (in this example, datasourceexample) and enter the online user name and password, enter the initial directory to use. If you do not have the administrator password (if you have a password on the Network), select the blank password check box.
　
 
Figure 29-20 online volume label in the data link content window
8. Click test online to test the online status. If the test is successful, an online success message is displayed. If the test fails, some input errors may occur. After the online test is successful, press OK to return to the Cube creation wizard and select a fact data table screen from the data source, 29-21.
　
 
Figure 29-21 select a fact data table from a data source in the cube creation Wizard of an existing data source and data table
9. In the data source and data table list on this screen, double-click the data table you want to use as the data source of the cube. In this example, we press twice on the orders data table, even if the orders data table is not actually a fact data table, it is very close. (This table is selected here to allow general users to use this example to practice operations .)
10. Click Next to display the digital data row screen with the defined value, 29-22. You can select one or more data rows to define the numeric value of the cube. These data rows are used in the summary. In this example, select orderid and freight. You can double-click the two data rows or click the right arrow to select the two data rows.
　
 
Figure 29-22 select a numeric data row for a defined value
11. Click Next to display the dimension screen of the selected cube, from 29 to 23. You can select the dimension data table to be used for the cube. In this example, create a dimension data table.
　
 
Figure 29-23 select a cube dimension
12. Create a wizard welcome screen based on the new dimension. The page is displayed from 29 to 24.
　
 
Figure 29-24 "dimension creation wizard" Welcome Screen
13. Click Next to continue. Shows how to create a dimension image, 29-25. In this screen, you can specify how to create a dimension. You can select the star structure description, snowflake structure description, parent-child association, virtual dimension, or acquisition model. In this example, select the star structure description.
　
 
Figure 29-25 Select how to create a dimension
14. Select the Dimension Data Table screen as shown in the next step, from 29 to 26. In this example, the employees data table is selected as the dimension data table.
　
 
Figure 29-26 select Dimension Data Table
15. Select the dimension type screen as shown in the next step, 29-27. You can choose whether to use the standard dimension or the time dimension. In this example, select the standard dimension.
　
 
Figure 29-27 select dimension type
16. Click Next to display the level screen of the selected dimension, 29-28. In this screen, you can select several summary levels, but in this simple example, we only select one level-employee ID. To select a level, you can double-click the data row to be selected, or select the data row first and then press the right arrow.
17. Press next to display the data row screen of the specified member index key, 29-29. If you create a cube from multiple data tables, you can specify a data table index key data row here.
　
 
Figure 29-28 select dimension level

　
 
Figure 29-29 "specify a member index key data row"
18. Select the advanced option screen as shown in the next step, 29-30. You can change the dimension, specify the sorting method of members, and define the storage mode. If the cube you are creating is very large, you should specify the ROLAP storage mode, as discussed earlier in this chapter. If you select any of these options, the genie will display a picture to help you make decisions. We will not discuss these images here.
　
 
Figure 29-30 select advanced options
19. Follow the next step to create a wizard image for the dimension, from 29 to 31. After the dimension is named, press finish.
　
 
Figure 29-31 "complete dimension creation wizard"
20. once you have completed the dimension creation wizard, you will return to the dimension screen of the cube creation wizard (as shown in Figure 29-23 ), the new dimension appears in the cube dimension square. Here, you can select the dimension data table to be used to create summary data on the fact data table, or execute the dimension creation Wizard to create more dimension data tables based on the new dimension.
Click Next to continue. If a message is displayed asking if you want to calculate the data row, press Yes. Then the wizard screen is displayed, 29-32. After naming the cube, click Finish to save all settings we have made in the genie.

　
 
Figure 29-32 "complete cube creation wizard"
21. After you press the button, you will be taken to the Cube editor window, 29-33. Edit the Cube as needed, or press the close button to exit the cube editor window. Generally, you do not need to edit an action.
　
 
Figure 29-33 "cube Editor" Window
22. when you leave the cube editor window, a message is displayed asking if you want to create a bucket option for the cube. Press yes. The welcome screen of the bucket design genie is displayed, 29-34.
　
 
Figure 29-34 "storage design Genie" Welcome Screen
23. Select the data storage type screen as shown in the next step, 29-35. Here, you can specify whether the data storage body is multi-dimensional, relational, or two data types. In this example, we choose molap to store the data in the data structure of analysis services. If you select Relational OLAP (ROLAP), the new data table is stored in the database where you work (in this example, the northwind database ). The last option is holap (Hybrid OLAP). If this option is selected, the data is left in the relational data table and summarized and stored in the multi-dimensional structure.
　
 
Figure 29-35 select data storage type
24. Set the summary option screen as shown in the next step, 29-36. Here, you can specify the method of best effort to summarize. In this example, the default value of 100 mb is accepted, and a summary is created based on the start.
　
 
Figure 29-36 set summary options
Since the data tables we use in this example are quite small, it takes only a few seconds to calculate the summary. The summary result will be drawn into a chart, and the Set summary option screen will appear again (29-37 ). Note that we have not made more plotting in this example, so this graph is just a vertical line on the left side of the graph.

　
 
Figure 29-37 set summary options for a summary chart
25. follow the steps below to display the completed design sprite screen, 29-38. You can specify to finish the bucket design wizard immediately or save the settings and wait for some time. This option is useful if you want to create a bucket when the system is at low load after working hours. In this example, we choose to process it now.
　
 
Figure 29-38 "complete design Genie"
26. Press finish. The processing dialog box is displayed, ranging from 29 to 39. After you create a cube bucket, a message is displayed at the bottom of the screen, indicating that the processing program has been successfully completed. Close the program.
　
 
Figure 29-39 "processing" dialog box
Modify an existing OLAP database
　
You can use a method similar to the above to modify an OLAP database through the analysis administrator. In this section, we will modify the foodmart 2000 database. The foodmart 2000 database is part of the analysis services installation (if you select the sample application check box during installation ). To edit a cube in the foodmart 2000 database, follow these steps:
1. In the analysis administrator window, expand the analysis servers data folder, expand your server, expand the foodmart 2000 data folder, and then expand the cubes folder, as shown in 29-40.
　
 
Figure 29-40 analysis administrator window
2. Right-click the sales folder and select Edit from the shortcut menu. This will open the cube editor window, 29-41. This window shows the associations between Dimension Data Tables and fact data tables in the cube.
In the cube editor window, you can use column options to edit the Cube:
O add dimension: you can right-click a dimension name in the dimension data folder or any dimension name in the left pane, and select an existing dimension from the shortcut menu to enable the dimension administrator. The dimension administrator is similar to the dimension creation wizard you saw before this chapter. It can be used to add or remove existing dimensions.
　
O remove dimension: you can right-click the dimension name you want to remove and select Remove to permanently remove the dimension from the database.
　
O add, delete, or rename a value: Right-click a value and choose add value, delete, or rename.
　
O add and export members: Right-click the export member data folder or any export member name and select Add export member.
　
O edit, delete, and rename an export Member: Right-click the export member name and select edit, delete, or rename.
　
In the structure description volume tab on the right pane, right-click a dimension data table or fact data table and select the following options:
O inserting a data table allows you to add a data table to a database.
　
O changing aliases allows you to rename existing cube attributes. You can define a cube attribute obtained based on other cube attributes without changing the basic attributes.
　
O browsing data allows you to retrieve data from a data table for viewing.
　
O replace allows you to select different data tables to replace existing data tables in the database.
　
O remove (only Dimension Data Tables) allows you to remove dimension data tables from the database.
　

　
 
Figure 29-41 "cube Editor" Window
3. Select data from the View menu or click the volume label in the right pane to see the real use of the OLAP system. As shown in 29-42, there are several drop-down menus on the data volume tag in the cube editor window. By selecting these menus, you can observe the summary data obtained based on this standard. These menus are created based on dimensions in the cube. The data volume label is similar to the Cube browser dialog box mentioned later in this chapter.
In this label, you can select different variable combinations to form different analysis angles for the data. Because the summary has been calculated, you can get the result immediately. If the digest data cannot be used, you must perform a specific query. Computing and summarizing in a large data supermarket or data warehouse may take a considerable amount of time.

　
 
Figure 29-42 volume tag page in the cube editor window
Process Data
　
Once you create a cube, You can have several options for you to view and process data. Many of these services can be achieved by right-clicking the cube name in the left pane of the analysis administrator. These options include:
• Processing is used to update the summary. When the basic data changes, the summary is not automatically updated, so they must be updated on a regular basis. This process may take a long time, so the periodic table (night, weekend, and so on) should be scheduled for execution ).
　
• Enable the storage design wizard. Allows you to modify basic attributes of OLAP cubes. Earlier in this chapter, you have learned how to use the storage design genie.
　
• Condition Optimization enable the Condition Optimization Wizard to help you improve the summary and adjust the cube based on the queried history records that have been executed. You can view the queries that have been executed on the database and optimize those queries to achieve this. The Condition Optimization Wizard provides suggested methods to modify those queries or summarize them.
　
• Browsing data allows you to view the summary. The data browsing option opens the cube browser dialog box, the foodmart 2000 database example shown in 29-34. As you can see, it is similar to the data volume label in the cube editor window. In the cube browser dialog box, you can easily create a custom result set by using the summary of Cube Storage.
　
• Enable the Usage Analysis Wizard to analyze queries sent to cubes. The query data used by the Usage Analysis Wizard is a query performed on the cube based on your standards. This wizard is similar to the Condition Optimization Wizard, which allows you to select a benchmark to determine which query takes the longest time. However, the Condition Analysis Wizard is only used to view data.
　
Analysis Services does not automatically update OLAP cubes and the basic data changes. Therefore, you must determine the update frequency based on your needs and update these cubes regularly for the system. If the data changes frequently and the user needs the latest information, you may have to constantly update the cube. If the data from yesterday is acceptable, an update every night may be enough.
You can right-click the cube folder in the OLAP database and choose to process all the cubes so that you can update all the cubes. As mentioned earlier, if you want to update a cube individually, right-click the cube name and choose "processing" from the shortcut menu.
SQL Server OLAP cubes not only can be accessed through an OLE DB application, but also can be viewed by the analysis administrator, or set a connection to the OLAP database. The analyze administrator's cube browser dialog box is a useful tool that allows you to view data based on the created cube.

　
 
Figure 29-43 "cube Browser" dialog box
However, if you already have a data supermarket or data warehouse in operation, you may find it difficult to merge SQL Server Analysis Services into your existing work, because analysis services must first create a new data cube based on the database you are using to work smoothly, and must also be accessed through an ole db interface. If your current application does not use ole db, these services may not be available.
If you need multi-dimensional analysis, analysis services can be very useful in many different types of data warehousing and data supermarkets. From these summaries, you can use the cube browser dialog box of the analysis administrator to perform multidimensional analysis. You can determine whether to use the Analysis Services Service Based on your business needs.
Summary
　
In this chapter, you learned what analysis services are and how to set it up. You also learned how to create a cube and how to maintain the summary in the SQL Server database. The information discussed in this chapter should help you determine whether analysis services are useful to you. In the next chapter, you will learn the tools and tasks related to SQL Server Management.