Microsoft Data Warehouse Architecture
Abstract: This paper briefly introduces a data warehouse using the Microsoft Data Warehouse architecture, discusses the functions that the data warehouse can implement, use the appropriate timing of the data warehouse, and how to synthesize the data warehouse with system architecture. Directory Introduction Data Warehouse As a Cube, a Cube, a cube, a data warehouse, performs decision to view cube fragments and programming interface Microsoft data warehouse architecture data warehouse Other applications Implement data warehouse Error summary Introduction 1998 released version of Microsoft® SQL ServerTM Data warehouse software is already included. If you are more unfamiliar with the data warehouse, you may ask: "What can it do? When do you use the data warehouse? How can I integrate the data warehouse and system architecture?" This article will briefly introduce the use of Microsoft data warehouse architecture. database. Data warehouses have a relatively short history of the software industry, and the database has become the basis of the system that collects and distributes information. These databases are deeply hidden with statistical or measurement methods, and strategies can study them to improve the overall efficiency of the system. Data acquisition is an important part of such information, and the purpose is to make correct decisions based on the content of the database. Until recently, this effort also requires expensive and cumbersome data acquisition packages, or can accurately translate information requests into database experts that can be used, more efficient queries. In addition to the large-scale institution, the cost of these two programs is extremely expensive for other all of the agencies. For the purposes of data acquisition, it is not the best design of the normal relationship database that can be effectively used for product classification, inventory control, and order entry systems. Cross-table and cross-database (sometimes even cross-server) summarize outputs may be very complicated, and this complex is avoidable. When data acquisition is required, create a information center reserve library for data analysis queries will be more meaningful. This is the meaning of the data warehouse. Information from different parts of the system is integrated into the data warehouse for easy access. As a cube, a cube, a cube, as a data warehouse reserve, is not perfect, but it is more satisfactory. How to distinguish between data warehouse cubes and geometric cubes? There are several important differences between these two cubes. Data warehouse cubes are defined by any number of dimensions (not limited to three dimensions, sometimes data warehouse cubes may be less than 3D). Describe the dimensions of the data warehouse cube and the length, wide and high of the description geometric cubes. If desired, the dimension can be combined into any number of levels. The two-dimensional relationship can be used with grid. The dimension is similar to the marker on the grid coordinate axis. The cell is the content. The content corresponds to the cross result of each dimension of the cube. The data in the cell is a unit of measure. The unit of measure is the basis for judging the cube. If the cube is about the number of sold items, the measurement unit is a count of the number of sold items. To repeat the grid sample, the unit is the number you found in the grid cell. Figure 1: The above figure shows the organizational structure of two-dimensional cubes. In this example, "product" and "region" are dimensions; "Cofje", "soap", "Oregon", "Washington", "Seattle" and "Spokane" are level of each dimension. The cells containing different charts are content. Single data in the content cell is the metering value. In this example, three metering units are used in the cubes. Dimensions and levels for data warehouse cubes designed for grocery stores, their dimensions may include products, preferential, time (operating days), and regions. It may also include employee dimensions and customer dimensions (some grocery stores may have member accounts). The level is used to organize dimensions as smaller units as needed. They may also contain other levels depending on the configuration of the level in cubes. For example, it is assumed to have a regional dimension. Perhaps this grocery is open in three states and uses the state community as the boundary line. Assuming the area weighs three levels: California, Oregon and Washington.
If the store also includes other sub-areas (such as Seattle, Olympia, Yakima and Scotan) in Washington, even if the California and Oregon areas, these levels can be added as a sub-level to Washington area. Level is only a convenient way to organize dimension content. The contents and measurement units are combined by each dimension. Positioning content is similar to the use of coordinate system. As the origin in mathematical cubes can be expressed as (x = 0, y = 0, z = 0), the content will be represented by a specific dimension (eg, ",", " Contents of Callow Collodulat in Seattle on Wednesday. According to the use of cubes, the content may show a unit similar to "selling 580 unit items" or "sales of $ 860.00". The meaning of the measurement unit depends on the definition of cubes. In this example, there may be a variety of broccoli or multiple stores in Seattle. This value will represent the summary of the definition group. Meter units in cubes may be numbers. For a grocery store, the measurement unit may be product price, net sales, sales quantity, product cost, etc. Summary counting and total mathematical operation is one of the useful crosses of data warehouse, belonging to summary function. The dimension organization has been calculated after the cubes have been processed, and the summary will begin. Typically, summarization will be made after the cubic is initially filled or after the content of the cube is made. Use a data warehouse to make a decision to assume a grocery store. Assume that some kind of promotion has been carried out for a few days, the owner needs to decide whether to make promotion again. The owner may have the following questions: "There are more products sold during the promotion than the promotion." In the use of ordinary structural affairs databases, the grocery store inventory system can record prices, products, sales and promotions. The inventory system is optimized in insertion and updating records, and it may also be optimized in a simple styled selection (such as retrieval project cost). This situation is unlikely: the organization of the system enables the generated report to describe the effectiveness of some sales in the day or according to the product. In fact, there is always contradiction between systems and systems designed for transaction effectiveness and systems for the effectiveness of query. In this case, a data warehouse should be used. The data warehouse is a separate repository that uses data related to existing resources in an optimized structure. In this case, the use of data warehouses will easily answer the owner's problem. By using the product, promotion and time cube dimensions, the content record of the measurement project sales can generate the desired results. Compared to this technology, information in other systems may not even be in the same database. Inventory data source may be different from customer data sources or employee data sources. Even if the system is located in the same database, it is also very cumbersome to establish a query system (the system merges and summarizes the way to generate the correct answer). In fact, merging data sources and summary results are the most good at data warehouse software. The Foodmart sample cube is included in Microsoft SQL Server Analysis Services software, namely Microsoft's data warehouse software. This is a very good resource for what kind of information that can be used when you understand the use of a data warehouse. Foodmart example uses grocery store as a model. View Cube Fragment and Programming Interface Although the data warehouse is constructed into multi-dimensional may be simple design selection, and for the processor, the query that performs the result of the resulting result across multiple dimensions is not particularly complex, but the structure of the multi-dimensional output is displayed very difficult. Charts, graphics and tables are usually displayed using two-dimensional display. Although there are some good three-dimensional chart tools, the chart will become difficult to understand. Common technology for viewing multi-dimensional output is a two-dimensional output "fragment" for viewing cubes. This is also the way Microsoft SQL Server Analysis Tool displays the output. Using DSO is fortunately, the output is not limited to two dimensions.
Microsoft SQL Server Analysis Services provides programming interfaces for multi-dimensional data warehouse output: DSO, Decision Support Object. DSO can be used to program access to multiple dimensions. For more information on the DSO containing the object model and programmer reference, read "Decision Support Object" on MSDN Online. This link is located at http://msdn.microsoft.com/library/psdk/sql/prabout_84a4.htm. Using MDX MDX (multidimensional expansion) is a syntax designed for querying multidimensional objects and data. For such systems, using MDX is more effective than using SQL (designed for completely different object sets). The syntax of the MDX query is similar to the syntax of the SQL query. Please pay attention to observe the following MDX query, which will use the sales number in the previous example (Cofara sold in Seattle on Wednesday): SELECT [MEASURES]. [Sales] on columns
[Time]. [Wednesday] on rows
From mysalescube
WHERE [Region]. [Washington]. [Seattle]
