The start of SQL Server2005 Analysis Service Practice

Server one, defining a data source view in an Analysis Services project
1. Create an Analysis Services project based on the template
BIDS (Business Intelligence Development Studio) uses templates to create different types of projects, and the Analysis Services project is one of the templates, and the templates are customizable.

2. Define the data source
Connect SQL Server using the native OLE db\microsoft OLE DB Provider for SQL Server driver.

3. Define the data source view
Data source view, which is the metadata view of the table or view of the data source selected in the project, stores the metadata in the data source view, allowing the user to detach from the data source during the development process using meta data. You can select multiple heterogeneous queries as data sources, but at least one data source must be a SQL Server data source.

Commodity dimension: Pub_ Commodity main file
Date Dimension: Sys_ Sales Calendar
Store Dimension: Pub_ Warehouse Store
Vendor Dimension: pub_ Suppliers
Fact: E1 Day sales details
4, modify the default table name
Bids defines dimensions (dimensions), attributes (attributes), and measure groups (measure groups) using metadata in the data source view. Bids use the FriendlyName property of a cube object instead of the Name property, this step is primarily to modify the FriendlyName properties of the object created in the previous step to improve user-friendliness. (Not only can you change the friendly name of the table in the data source view, you can also change the name of the column, define computed columns, and even connect between tables or views to improve user friendliness).

Several concepts:

Dimensions (Dimension): The basic component of multidimensional cubes (a fundamental component), which organizes data (in a logical hierarchical format to organize business data) from the perspective of user interest.
Attribute: A component of a dimension (building block) used to organize measures in a multidimensional cube. A dimension is a collection of attributes that correspond to one or more columns in a dimension table. In a dimension, attributes are organized in a hierarchical manner, providing the path of aggregation drill-down for the measure in the cube. property to provide a grouped, constrained information to the client application.
Measure (Measure): A column in a fact table (fact table) that contains numeric data that can be calculated, aggregated, and so on. In a cube, measures are grouped by fact tables into measure groups.
Measure group (measure groups): Used to group measures contained in a fact table and to communicate between dimensions and measures.

Pub_ Commodity main file-->product
Sys_ Sales Calendar-->date
Pub_ Warehouse Store-->depository
Pub_ Suppliers-->supplier
E1 Daily Sales Detail-->sales

Ii. Define and configure cubes
1. Definition cube
The Cube Wizard can assist in defining measures and dimensions.

A cube can also be defined without a data source. In this case, the bids automatically generates the underlying related objects, with SQL Server intergration Services loading data from the related database objects into the Analysis Services dimension and cubes. This top-down approach is generally used in prototyping and hypothesis analysis (what-if analyses).

The steps to define a cube based on the data source above are as follows:

A. Select the method of building the cube, select "Build the cube using a DataSource" and select "Auto builds".

B. Select the data source view defined above.

The C. Cube Wizard automatically detects the fact table and dimension table.

D. Development of fact tables and dimension tables. Specifies that the E1 day sales detail is the fact table, the other is the dimension table (here is name), and the Time dimension table is set to date (here is FriendlyName).

E. Select the Sales Date field in the time period date is.

F. Select measure. The selection of the measure contains a numeric field from all the fact tables, and the last "Sales Count" is the count segment of the fact table that is automatically generated by the wizard.

G. Check the attributes in the dimension and change their structure when needed, such as removing unnecessary fields such as notes, pictures, and so on to save space.

2. Check the properties of cubes and dimensions
Examine the results generated by the Cube Wizard in the Cube Designer. The Cube Designer consists of nine tabs: Cube structure (cube Structure), dimension Purpose (Dimension Usage), calculation (calculations), Key performance indicator (KPIS, key performance indicators), behavior (actions), partitions (partitions), Perspectives (Perspectives), Transformations (translations), browsers (Browser).

A. " The Cube Structure tab is used to view the structure of the cube, which is turned to this page when the Cube Wizard finishes.

1 in the measure panel, you can use the mouse to drag and drop the order of the measures.

2 in the dimension panel, you can add, delete, and modify dimension hierarchies, levels, and attributes when you select a dimension and edit the dimension.

B. " The dimension purpose panel is used to browse the dimensions of each measure group in a cube that has more than one measure group. Click the ellipsis next to the selected measure to eject the Define relationship (Define Relationship) dialog box, which defines the relationship between dimensions and degrees. If the dimension table is directly connected to the fact table, the regular should be selected, at which point the granularity of different levels (granularity) can be defined.

Click the Advanced (Advanced) button on the Define Relationship dialog box to eject the measure group bindings (Measure Group Bindings) dialog box, which allows the user to edit the bound field for each property and allows the user to specify the processing of a null value.

The C. Cube Wizard defines a separate partition using a MOLAP storage pattern that does not have aggregations. In MOLAP, all leaf-level (including aggregate) data is stored in cubes for maximum performance. Aggregation is a summary of the data that is calculated beforehand to reduce the response time of the query. In addition, in the Partitions tab, you can define additional partitions for storage settings and write-back settings.

D. At this point, the cube cannot be browsed under the Browser tab because the cube needs to be deployed to an Analysis Services instance.

3. Deploying the Analysis Services Project

A. In Solution Explorer, right-click Analysis Services Project Select Properties, eject the save path for the XMLA script for the script specified on the property page output path, "Server" Specifies the target Analysis Services instance for this project deployment.

B. If the Analysis Services service is not started, start \microsoft SQL Server Ctp\configuration tools\sql Server Surface area Configuration Start the Analysis Services service with the name MSSQLServerOLAPService. Right-click the Analysis Services project in Solution Explorer to select Deployment (Deploy).

The cube was successfully deployed.

4, browse the deployment of the cube
Dimension browsers are used to browse the members of each level of the dimension.

You can browse cubes in the browser (Browser) tab of the Cube browser.

At first it was strange why the data rendering area in the English version of the development environment is displayed in Chinese? With a closer look, the original Cube browser's data rendering area uses Microsoft Office PivotTable.