29. Use Microsoft SQL Server Analysis Services
Analysis Services
Install Analysis Services
Use Analysis Services
Summary of this chapter
Microsoft SQL Server 2000 Analysis Services is designed in SQL Server 2000 to help you engage in an element of online analysis processing (OLAP), using this component to make you stay in your information Access and pick data with data supermarkets. In this chapter, you can learn what is an Analysis Services component, how to install and use these components; in addition, you will learn about the added features of Analysis Service in SQL Server 2000. Since this book is mainly written to the SQL Server administrator rather than application developers, our discussion will only end in the installation, setting and management of Analysis Services, etc., and the part of the application is not within the scope of the discussion.
Description
In this chapter, we may constantly mention the Data Warehouse and the Data Mart (Data Mart) These two proprietary nouns. The storage warehousing can be defined from a few different directions, one is to treat it as a commercial information, which stores historical data obtained from the online transaction processing, OLTP system and current information. . The data supermarket is similar to the data storage, but the information they contain is only related to one aspect of the company. For example, the company may have a data storage, including account payment, accounts receivable, and human resources, as well as a data supermarket, including account payment information. The information that makes up the storage and data supermarket is usually planned as a star structure description or snowproof structure, which will be explained later in this chapter. Most of the concepts discussed in this chapter can be used in the data warehousing and data supermarket, so we use the information warehousing to simultaneously represent these two databases.
Analysis Services
Analysis Services is a tool that can be used to assist you with the development and management of information for online analysis. Analysis Services consists of an Analysis Service Server, English Query, and other support elements. The Analysis Service Server Constructing Cube Assist you multi-dimensional analysis, and the word is used to describe a summary of a summary or summary of information to handle complex analysis, such as monthly sales results and sales plan. (Cube will explain in this chapter "OLAP CUBES> section later.)
In multi-dimensional analysis, multiple queries searches for database libraries from different perspectives or dimensions. For example, it is assumed that there is a bicycle library, which remains last year's sales information. A query in the multi-dimensional analysis may be searching for customers to buy habits, and another query search is a monthly sales, and there is a query to find a certain type of bicycle or component sales. Although the information is shared given to all queries, each query is viewed in different perspectives (dimensions).
Analysis service components
Analysis Services offers some tools and elves to allow you to access multi-dimensional information. Analysis Service consists of the following components:
Analysis Administrators: Provide a GUI to use Analysis Service, such as establishing Cube, managing security, and browsing information. Data Warehousing Architecture: Components with SQL Server 2000 Information Warehousing Functions and APIs. Data Conversion Service (DTS): Assist in loading and conversion materials to data supermarkets or information warehousing. DTS is composed of anhui into the elf and the remittual elf, which can be used to migrate data and conversion materials. See Chapter 24 for details on DTS. Repository: Contains some interfaces, database structural description models, and pre-defined data conversion methods to comply with data storage architecture. Because data conversion is based on rules, they can be stored for future reuse. Data Mining: Provides the algorithm for definition and actual multi-dimensional CUBE. English Query: The problem of English language is converted to the SQL statement that can be performed on the database.
Extension Markup Language (XML): Provides a language that formats and information presented. XML is an important component of the application conversion between the app to the application and can be used to publish the data to the Internet. In this chapter, we will combine these components together, as part of the combination of puzzles, to provide a integrated tool. The main manifestation of the data in OLAP CUBE in Analysis Services is OLAP CUBE. CUBE is a detailed information and a multi-dimensional expression of the summary information. Details are information on specific information columns, and the summary information is summarized. CUBE is designed based on the information of the data itself. Each Cube represents a different business entity, such as sales, inventory, etc. Each of the CUBE represents the love of different dimensions in the data. In other words, CUBE is composed of many different domains, so it is called a "data CUBE". Analogs Services Cube can be established through two database structure descriptions: Star shaped structural descriptions and snowflake structure descriptions. Structural description (Schemas) is actually a development topic, but for the appropriate description of Analysis Services, we have a simple understanding of structural descriptions here. These two structural descriptions are composed of factual data sheets and dimensional data sheets. Analog SERVICES summarizes these information in the data sheet to establish CUBE. Let us look at this process in more detail. Fact Table Fact Table is a data sheet for storage historical data in the data warehousing, these historical data is the core information of information storage. In our bicycle vendor, this information is a transaction record that happened in bicycle business (including database transactions and sales transactions). The information within this record covers the total amount of trading dates, trading types, sales projects, transactions, customers name, salesperson name, etc. This record can be used as a basis for multi-dimensional analysis. As you can see, the information sheet information is based on commercial transactions. These transactions can be sales, credit card transactions and profits of items. Basically, the fact sheet records some of the types of business events. The fact sheet in the data warehousing is the largest data sheet in the database, and is also the most frequent data sheet. You may imagine getting, the fact table can include millions of records, and can occupy a space exceeding 1TB (or 1024 GB). Dimensional Data Table Dimensions Data Sheet (Dimension Table) is used to define the fields in the fact table, such as salesperson names, trading types, or projects. This process is similar to the normalization process, both of which are picked up to speed up the efficiency of processing. The fact sheet contains historical information of the transaction, and the information contained in the dimensional data sheet, indicates how to obtain useful materials in real information tables. In other words, the dimensional data sheet is the meaning of the information contained in the fact sheet. For example, for a fact sheet containing sales records, there may be a dimensional information table containing information related to the sales representative, which can be used to build a summary information such as the monthly sales of each salesman; Another dimensional data sheet is included in regional information, which can be used to establish a summary information of monthly sales per country. The dimensional data sheet is not like the fact sheet, the opposite is that dimension data sheets are usually small and often contain only several information columns. Data warehousing generally said that there will only be one to two fact sheets, but there are several dimensional information sheets. Structure Description Star Schema is a quite common storage architecture, which consists of a fact sheet and some dimensional data sheets. Star shaped structure description means that a fact sheet is surrounded by dimensional data sheets, showing a star shape. Each dimensional data sheet is equivalent to a data line in the fact table.
These dimensional data sheets are the basis for architecture analysis so that we analyze the information in the fact table. Snowflake Schema, several dimensional data sheets are connected to each other before the fact sheet, in other words, first establish some dimensional data sheets, each layer is equivalent to one of the fact tables. Figure 29-1 shows a description of the star-shaped structure and a snowflake structure. Figure 29-1 Structure of Stars and Snowflakes
The data aggregation Analysis Services is based on the data in dimensional data, thus establishing a summary of the information line in the factual data. For example, the dimensional data sheet related to employee can be used to establish a summary information based on employee-based sales information. Dimensional data sheets related to the project can be used to establish a summary information based on the project. Since the summary is based on dimensional data sheets to establish different sides (that is, dimensions), a virtual Cube can be constructed, as mentioned earlier. The OLAP CUBE established by Analysis Services is essentially a summary function of calculating in accordance with star-shaped or snowflake structure. You use the Analysis Services elf to build these summary, and then use aggregation to establish a business model to make business decisions. MetadATA "Relay Information" is used to describe information-related information, so the abstracts used to describe the database data are called relay data. In the case of our example, the sales information sheet is the status of the dimensions of dimensions. Our summary information (sales information of salesperson, sales information of various items) is relay information. The main benefits of Analysis Services are the ability to establish relay information (summary information list or summary), using Analysis Services, you can simply establish relay data, then apply to a variety of work without having to safeguard these information. SQL Server 2000 Data Analysis Enhancements SQL Server 2000 provides a lot of new features in data analysis and data storage, including some tools and information, can be used to enhance data analysis. In this section, you will learn the most important part of these new features. Data Picking Enhancements Analysis Services has integrated new information picking technology, which can be used to find data association between the associated database and OLAP CUBE. These associations can be added to the existing OLAP Cube has provided additional data analysis. One of the unique information picked up new features is Microsoft Decision Tree. Microsoft Decision Tree uses a precise classification technology with the algorithm to analyze the information, then it will construct one or more decision trees, which can be used to predict the new information. For example, our bicycle company can analyze the credit history information and trading history of potential customers through this technology to analyze the credit history data of potential customers, thereby predicting the customer's credit risk. Another new feature of information pick is the use of clusters. Clustering techniques for data picking and the cluster type described in Chapter 12 are not the same. When Analysis Services performs a cluster, it uses a cluster that makes the data recording in a cluster of similar characteristics using an algorithm called the nearest approach. Many times, these associations will be hidden or not aware. Therefore, clustering technology can say another door of the data analysis. In addition, SQL Server data picking components also contain some new wizards and dialogues, making information picks easier to use. These new features allow DBA to perform most of the relevant work faster when establishing and maintaining data supermarkets or information warehousing. Dimensional Enhancements SQL Seever contains several new dimensional data sheets. SQL Server now supports parent-based dimensions, associated OLAP (ROLAP) dimensions, and writable dimensions. The father and son dimension allow the classroom-based class architecture definition between the members of the source data sheet. An example of a parent-based association is a component combination structure from each part. The parent is a single part, and there can be many sub-components, namely. When data analysis is performed, the parent-based dimension can be used to enhance the connection between the sub-element and the part. ROLAP dimensions can be used to resolve the capacity restriction problem of the standard multi-dimensional OLAP (MOLAP) mode used by Analysis Services.
MOLAP mode allows dimensions to contain nearly 5 million members. Once the growth of the member exceeds this limit, the ROLAP dimension is required. ROLAP dimensions can grow extremely large, but in query member sets, the performance of MOLAP mode is much better than ROLAP. Therefore, it is only defined as ROLAP mode if the dimension is very large. When you use Write-enabled Dimension, the dimension member can be updated through analyzing administrators and user-ended client applications that support back. You can use the SQL Server role to control the write access to the dimension of the user. The SQL Server role will be introduced in Chapter 34. Safety Enhancements The SQL Server 2000 also includes security enhancements, which can provide better protection for information you are used for business analysis. After all, these materials may be quite sensitive. These new features include the security, data security functions of change dimensional data sheets, and support for additional verification techniques. The dimensional data sheet is now working in the role of SQL Server (Role-based) security mode. In accordance with the definition of each role, you can limit its access to individual dimensions, hierarchies, and members. In addition, you can also set the read and read / write permissions of these resources. SQL Server 2000 supports role infrastructure of both FAT and NTFS systems. SQL Server 2000 allows you to implement roles in Cube's data level level. The analysis administrator contains a dialogue that defines the security security, and you can control the access to any Cube data combination. In addition, the read / write permissions of each role can be different. Since SQL Server 2000 includes Windows 2000 security mode, when the user or application needs to access Cube and its information, SQL Server 2000 supports the Kerberos Communication Agreement, NT License Manager Security Support Provider, or any other security use. Provide the provider of the SECURITY Support Provider Interface (SSPI) to perform the verification action. This allows you to have an overall security on all levels installed in SQL Server. ENGLISH QUERY Enhancements The functionality of English Query in SQL Server 2000 has been enhanced, and more complete integration of NOSOFT Visual Studio 6.0 and other suites. ENGLISH Query allows program developers to integrate English in the application, rather than T-SQL statements. In addition, the new graphical user interface tool also provides considerable assistance to the development of ENGLISH Query statements. SQL Server also includes SQL project wizards, which can automatically establish basic database structures to support English Query, allowing the ENGLISH Query environment to make it easier to set up. This wizard scans the data sheet for the database and establishes the associated SQL Server component. Installing Analysis Services Analysis Services is a component of SQL Server 2000. To install Analysis Services, follow these steps: From the installation list, press SQL Server 2000 components, then install Analysis Services, the welcome screen appears. Press Next to enter the software license contract dialog. After you read and agree to this authorization, press yes.
The selection component dialog appears, as shown in Figure 29-2. In this dialogue, you can select the Analysis Services component you want to install. Press the verified block before each component name to select all components. If the component has been installed before, you will not be able to change the status of its verification. To select a new location to install Analysis Services, press Browse. After you choose the destination data clip, press the next step. Figure 29-2 "Select Components" Dialogue
The data clamp position dialogue of the stored materials appears, as shown in Figure 29-3. This dialogue is very similar to the selection destination folder dialogue, however, here you have to select the storage data clamp position. You can specify a location that is different from the preset value by browse. After you choose the data clamp position of your information, press it.
Figure 29-3 Diagram of "storage information" dialogue
The selection program folder dialog is displayed, as shown in Figure 29-4. Here you can select the program folder to place Analysis Services (that is, the location of Analysis Services appearing on the start function table). Preset values are generally accepted. Press Next to complete the installation.
Figure 29-4 "Select Program Correspondence" dialogue
After you have installed Analysis Services, you can install English Query. Although ENGLISH Query can be said to be part of the Analog Services overall service, how they are installed separately. You don't have to install English Query to use Analysis Services. To install English Query, follow these steps:
From SQL Server 2000 installation menu, press SQL Server 2000 components, then press Install English Query. The installer will install Microsoft Data Access Components (MDAC) and Microsoft Visual Studio components. Welcome screens will appear after these components are completed. Continue to install by Continue. The software license contract dialog of Microsoft English Query 2000 appears. Press i agree after you read it.
The Microsoft ENGLISH Query 2000 Setup dialog appears, as shown in Figure 29-5. Here you can choose the installation type you need - Complete or Run-Time Only. Complete will install all components, run-time only allows you to specify which components you want to install. You can also specify the installed folder, but the preset data clip is usually acceptable. Unless you are an expert in ENGLISH Query, press COMPLETE. Then will then install the ENGLISH Query Components component. Press OK to complete the installation.
Figure 29-5 "Microsoft English Query 2000 Setup" dialog
After you complete the installation, use Analysis Services and English Query components, press Start / Program Set / Microsoft SQL Server / Analysis Services. In the subdirectory of Analysis Services, you have the following three options: Analysis Administrator: Enable the main components of Analysis Services. This component includes some elves and public programs that allow you to start the Analysis Services service. Online Series: Enable the analogs Services online file. MDX Sample Application: An application example provided by Analysis Services is enabled. Using Analysis Services Now we have already introduced Analysis Services and its installed programs, then let's take a look at how to use the services it use to create and manage your information warehousing. In this section, we must first set a source of information, then build an OLAP database on the source, and finally create a Cube on the database. Setting the Source To connect Analysis Services to the SQL Server Database, the primary step is to set an ODBC system source for the server. You can use the ODBD data source utility in the system management tool to complete this work. To set the source source, follow these steps: Press Start / Program / System Management Tools / Data Source (ODBC), appear ODBC data source administrator dialogue, as shown in Figure 29-6.
Figure 29-6 "ODBC Data Source Administrator" dialogue
Press the Source Name tag of the System Information, as shown in Figure 29-7, you will find some existing sources of information in the system data source. Some of these data sources have been defined as connecting to SQL Server. According to the use of the database, sometimes we will need multiple ODBC data sources to refer to the same database, which is of course allowed. In this example, we have to build an ODBC data source with reference to the Northwind database.
Figure 29-7 "Source Name" tab of "ODBC Data Source Administrator"
Press a new increase, there is a new source of data, as shown in Figure 29-8. In the optional unilateral block, select SQL Server and press Completion.
Figure 29-8 "Establishing a new source of information" dialogue
There is a new source of information to the SQL Server dialog, as shown in Figure 29-9. Here you must give the name of the source, and give a description, and you must specify SQL Server to connect. Press Next.
Figure 29-9 "Establishing a new source to SQL Server" dialogue
The next dialogue is as shown in Figure 29-10, you can specify the verification mode to be used when the user is connected to SQL Server. You can choose to use the network login identification code for Windows NT authentication or to log in to identify code and enter the password by the user. (The user verification mode is introduced in Chapter 34.) At the point of the dialog, you will see a preset to pick-up. If you don't need to connect to SQL Server at this time to get the preset setting for other options, the verification block is changed to the state of not selecting. Press Next.
Figure 29-10 Specifying a verification mode
The next dialogue is as shown in Figure 29-11, you can specify the database, database name, and ANSI mode you want to use. Analysis Services will allow you to choose the database you want to connect, so you don't need to provide a preset database name. However, it is not an issue where it is specified for a preset repository because other applications may also be used in this Data Source Name (DSN). When you are finished, press the next step. Figure 29-11 Designation Preset Database
The next dialogue is as shown in Figure 29-12, you can change the language of the SQL Server system message to other languages, turn on the translation function, specify the area setting, specify the record file of the query and driver statistics for long-term execution. When you complete the setting, press.
Figure 29-12 Specifying language and other settings
The ODBC Microsoft SQL Server setting dialog is displayed, as shown in Figure 29-13. This dialogue illustrates a new ODBC source that will be created and lists all settings you selected for this source.
Figure 29-13 "ODBC Microsoft SQL Server Settings" summary dialogue
You should test your settings as a source of test. When you press it, you will start testing the connection to the database. Once you have successfully completed the connection test, press OK, this DSN can start using it.
Note SQL Server must be in executing the status to set and test the source.
Establishing an OLAP Database Now you have set and tested an ODBC source, then you can prepare to create an OLAP database. Establishing an OLAP database also includes setting an existing database to an OLAP database. You must prepare which information tables are used as factories to be used as a dimensional data sheet.
Description In this section, we will set the Northwind library as an OLAP database. This database does not have all the property supermarkets or all the properties of the data warehouse, but we will use it to make demonstrations, because it is built in SQL Server, and this demonstration process can simply make it in practical applications.
During the establishment of an OLAP database, you will use the analysis administrator, Cube to establish wizards, dimension establishing the wizard, and storage design wizards. To establish the database, follow these steps:
Press Start / Program / Microsoft SQL Server / Analysis Services / Analysis Administrator, an Analysis Administrator Window, as shown in Figure 29-14.
Figure 29-14 "Analysis Administrator" Window
Expand the Analysis Servers folder in the left pane, then expand your server name folder. Press the mouse button on the server name and select the new repository in the quick function table, and the database dialog box appears, as shown in Figure 29-15. Please enter the database name and give it an introduction. In this example, we name the database northwind_olap.
Figure 29-15 "Database" dialogue block
Note When you expand your server name data clip, you will find an example repository installed in the analysis administrator. If you have the process of installing the Analysis Services, you will automatically install this Database named Foodmart 2000 when you select an example application in the installation element dialog.
Press OK to return to the analysis administrator window. If you expand the Analysis Servers folder and expand your server name folder, you will see a new database has been added. (This database has been named, but it is not yet connected to SQL Server source, but don't be nervous, we will start connecting.) Expand the database data clip (in this example for Northwind_olap folder) can display the source, Cube, shared dimensions, picked models, and database roles, as shown in Figure 29-16. Figure 29-16 Expanding OLAP Database
Press the mouse button on the Cube folder and move the cursor to the new Cube in the quick-changing function table and select the wizard in the child menu. The Cube created the elf Welcome screen, as shown in Figure 29-17. This wizard is used to select the source of information to be specified at the Cube level.
Figure 29-17 Welcome screen of "Cube Establishing Elf"
Press the next step to select a fact sheet screen from the data source, as shown in Figure 29-18. To select the SQL Server Database, press New Source.
Figure 29-18 "Select a fact sheet from the Source Source" screen
Data connection content window appears, as shown in Figure 29-19. You can specify a source for this Cube in the provider tag; however, in this case we want to use the connection label to select the source of information we have just established.
Figure 29-19 "Data Link Content" Window "Provider" tag
Select the data source name (this example is DataSourceExample) in the connection tag (Fig. 29-20) of the data link content window (this example is DataSourceexample), type the connection user name and password, and enter the initial directory you want to use. If you don't have an administrator password (if you should be on the network), select the blank password to check the block.
Figure 29-20 "Connection" tag of the "Data Link" window
At this point you should press the test connection to test the connection. If the test is successful, there will be a message that is successful. If the test fails, you may have some input errors. After the connection is successful, press OK Back to Cube to establish the wizard from the data source to select a fact sheet screen, as shown in Figure 29-21.
Figure 29-21 "Cube Establishing Elf" in the Source and Information Sheet "Select a Factory Data Sheet" screen
In this picture source and data sheet list, press two under the data sheet you want to use as a source source. In this example, we press two in the ORDERS data sheet, even if the ORDERS table is actually not a fact sheet, but it is already close. (Select this data here is mainly to practice the general user through this example.) Press the next step to select the digital data line screen of the selected defined value, as shown in Figure 29-22. Here you can choose one or more of the data lines to define the digital value of CUBE; these data will be used in the summary. In this example, select ORDERID and FREIGHT, you can press two or the right arrow to select these two data on both data lines.
Figure 29-22 Select Digital Data Line of Defining Value
Press the next step to select the dimensional picture of the Cube, as shown in Figure 29-23. Here you can choose the dimensional data table you want to use for Cube. In this example, we have to establish a dimensional data sheet.
Figure 29-23 "Select the dimension of CUBE" screen
Eliminating the wizards in the new dimension, as shown in Figure 29-24.
Figure 29-24 "Dimension Establishing Elf" Welcome Screen
Press Next. There is a choice to create a dimensional screen, as shown in Figure 29-25. In this picture, you can specify how to build dimensions. You can choose a star-shaped structure description, a snowflake structure description, a parental relationship, a virtual dimension, or a picked model. In this example, the star shaped structure is selected. Figure 29-25 "How to establish a dimension" screen
Press the next step to select the dimensional data sheet screen, as shown in Figure 29-26. In this example, we select the Employees tab as a dimensional data sheet.
Figure 29-26 "Select Dimension Data Sheet" screen
Select the dimension type screen as follows, as shown in Figure 29-27. Here you can choose to use standard dimensions or time dimensions. In this case we choose a standard dimension.
Figure 29-27 "Select Dimension Type" screen
Press Next, the hierarchy screen of the selection dimension, as shown in Figure 29-28. In this picture, you can choose several summary levels, but we only select a level-EmployeE ID in this simple example. To select a level, press two down on the data to be selected, or first select the data line and press the right arrow. Press the next step to appear the specified member index key data line screen, as shown in Figure 29-29. If you create CUBE from multiple data tables, you can specify a data sheet index key information.
Figure 29-28 Select the level of dimensions "screen
Figure 29-29 "Specify member index key information line" screen
Press the next step to select the advancement option screen, as shown in Figure 29-30. Here you can change the dimension, specify the sorting mode of the member, and define the storage mode. If you are building Cube very huge, you should specify ROLAP storage mode, just as discussed in this chapter. If you choose any of these options, the elf will display the appropriate screen to help you make a decision. Here we don't discuss these pictures.
Figure 29-30 "Select Advanced Options" screen
Press the next step to complete the dimension to establish the wizard screen, as shown in Figures 29-31. Press to be completed for dimension.
Figure 29-31 "Completing the Dimension Establishing Elf"
Once you have completed the dimension, you will return to CUBE to establish the wizard of the Cube's dimension screen (as shown in Figure 29-23), the new dimension will appear in the Cube dimension block. Here, you can choose the dimensional data table to be used to create summary data on the fact sheet, or perform dimensional establishment of the dimension in the new dimension to continue to establish more dimensional data sheets. Press Next. If a message will ask if you want to calculate the information line, press yes. Then there will be a completion of the Cube to establish a wizard screen, as shown in Figures 29-32. After name the Cube, press all the settings we do in the elves to all.
Figure 29-32 "Completing Cube Establishing Elf"
Take you to the Cube editor window as shown in Figure 29-33. Edit the Cube in accordance with the requirements, or press the Close button to leave the Cube Editor window. Generally speaking, editing the action is not required.
Figure 29-33 "Cube Editor" window
When you leave the Cube editor window, you will have a message asking you if you want to build a reservoir option for this Cube, please press Yes, the storage body design wizard welcome screen, as shown in Figure 29-34.
Figure 29-34 "Welcome" Welcome Screen
Press the next step to select the data storage type screen, as shown in Figure 29-35. Here you can specify how the data storage is multi-dimensional, related or use two data-type merge, in this example we select MOLAP to store the data in the data structure of Analysis Services. If you choose an associated OLAP (ROLAP), the new information table will be stored in the database you work (this example is the Northwind Database). The last option is HOLAP (hybrid OLAP). If this option is selected, the information will remain in the associated data table, and the summary is stored in the multi-dimensional structure. Figure 29-35 "Select Data Storage Type" screen
Press the next step to set the summary option screen, as shown in Figure 29-36. Here you can specify how much effort is summarized. In this example, the preset value is 100MB, and the summary is established.
Figure 29-36 "Setting Summary Options" screen
Since we used the information table used in this example quite small, the calculation summary takes only a few seconds. The summary results will be drawn into a chart, and the setup summary option screen will appear again (as shown in Figure 29-37). Note that we have not made more drawings in this example, so this graph is just a vertical line on the left on the chart.
Figure 29-37 Picture of "Setting Summary Options" with a summary chart
Press the next step to complete the design body wizard screen, as shown in Figures 29-38. Here you can specify immediate completion of the storage body design wizard or save the settings and wait for some time. If you want to wait until working hours, the system is in low load, which is useful. In this case, we choose to handle it immediately.
Figure 29-38 "Completing Design Elf"
Press. The handling dialogue appears, as shown in Figure 29-39. After establishing the job of Cube storage, a message appears in the bottom of the screen, telling you that the handler has completed successfully. Press to close this program.
Figure 29-39 "Processing" dialogue block
Modifying an existing OLAP Database You can use the above similar way to modify an OLAP database through analysis administrators. In this section, we will modify the Foodmart 2000 database. The Foodmart 2000 repository is part of the Analysis Services installation (if you have a selected sample application in the installation process). To edit Cube in the Foodmart 2000 database, follow these steps:
In the analysis administrator window, expand the Analysis Servers folder, expand your server, expand the Foodmart 2000 folder, then expand the Cubes folder, as shown in Figure 29-4.
Figure 29-40 "Analysis Administrator" Window
Press the mouse button on the Sales folder and select Edit from the fast display function. This will open the Cube editor window, as shown in Figure 29-41. This window shows the correlation between the Cube China-dimensional data sheet and the fact sheet. In the Cube Editor window, you can use the column option to edit Cube:
New dimensions: You can press the mouse button on any dimension name in the dimensional data clip or the left pane, and select an existing dimension in the flash function table to open the dimension administrator. The dimension administrator is similar to the dimension you see before this chapter. It can be used to add dimensions or remove existing dimensions. Remove the dimension: You can right click on the dimension name to be removed and select Remove, so you can remove the dimension from the database. New, delete, or rename the value: Press the slider on the volume name to right click to select the new value, delete, or rename it. Increased members: Press the mouse button on the export member folder or any export member name to select the newly exported member. Edit, delete, rename the export member: Press the mouse button on the export member name and select Edit, delete, or rename it. In the Structure Description Tag on the right pane, you can press the slider on the title of the dimensional data sheet or the fact sheet, select the following options:
Inserting the information sheet allows you to add the data sheet to the database. Change alias allows you to rename existing Cube properties. You can define a Cube property based on other Cube properties without changing the base properties. The browsing information allows you to take information on the data sheet to review. Replacing Allows you to choose different data sheets to replace data tables in the database existing. Remove (only dimensional data sheet) Allows you to remove dimensional data sheets from the database.
Figure 29-41 "Cube Editor" window
Select the information in the view function, or press the data tab in the right pane, you can see the true use of the OLAP system. As shown in Figures 29-42, on the Data label of the Cube editor window, there are several pull-down menus, and the summary information obtained by this standard is observed through the selection of these menu. These menu are established based on the dimensions in CUBE. The data tag is similar to the Cube browser dialogue that will be later mentioned later. In this tag, you can select different variable combinations to achieve different analysis angles of the information. Since the summary information has been calculated, it can be obtained immediately. If the summary information cannot be used, you will have to execute individual queries. In a large-scale data supermarket or information storage, calculation summary may require a considerable amount of time.
Figure 29-42 Data tab of the "Cube Editor" window
Processing Once you have established CUBE, you can have several options to give you to review and process information. Among these services, many of them can be reached by pressing the mouse button on the Cube name in the left pane of the analysis administrator. These options include the following: Processing for updating summary. When the basic information is changed, the summary does not automatically update, so you must update them regularly. This process may take time, so it should be scheduled to be scheduled for the periodic table (night, weekend, etc.). Designing the storage body to open the storage body design wizard. Allows you to correct the basic storage of OLAP CUBES. In this chapter, you have learned how to use the storage body design wizard. Use status optimization Operation Optimization Elf, help this to improve summary based on query history that has already been executed, and then adjust CUBE. You can use the query that is already executed on the database, and optimize those queries to complete this work. Use status optimization wizards provide some suggestion methods to modify those queries or summarize themselves. Browsing the information allows you to review the summary. The browsing information option will open the Cube browser dialog, as shown in the Foodmart 2000 database example shown in Figures 29-34. You can see that it is very similar to the information label of the Cube editor window. In the Cube browser dialogue, you can easily use the Cube stored summary to establish a self-order result set. Use status analysis Opens Use Status Analysis Elf, allowing you to analyze those queries sent to CUBE. Use the status analysis of the query information used by the elves is based on your standard in the query executed on CUBE. This wizard and usage conditions are optimized, allowing you to choose a reference to determine which query takes up the longest time; however, the use status analysis is only used to view the information. Analysis Services does not automatically update OLAP CUBE, and basic information will be changed, so you must decide the updated frequency in accordance with your needs, regularly update these CUBEs. If the information changes are very frequent and the user needs the latest information, you may continue to update the Cube. If yesterday's information is acceptable, then update per night may be enough. You can right click on the Cube folder of the OLAP database and select All CUBEs, so you can update all CUBE. As mentioned before, if you want to update the Cube, you can press the mouse button on the Cube name and select Processing in the Great Function. SQL Server OLAP CUBES can not only access through an OLE DB application, but also through analysis administrators, or set one to the OLAP database. Analysis Administrator's Cube browser dialogue is a very useful tool that allows you to view information based on the established Cube. Figure 29-43 "Cube Browser" dialogue
