After you define a data source view in your Microsoft Analysis Services project, you are ready to define an initial Analysis Services cube.
You can define a cube and its dimensions in a single pass using the Cube Wizard. Alternatively, you can define one or more dimensions and then use the Cube Wizard to define a cube that uses those dimensions. If you are designing a complex solution, you generally start by defining the dimensions
Defining a Dimension
In the following task, you will use the Dimension Wizard to build a Date dimension.
Note
This lesson requires that you have either completed all the procedures in Lesson 1 or have opened the completed project for Lesson 1 that is available by downloading and installing the updated samples. For more information, see Obtaining Updated Samples in Considerations for Installing SQL Server Samples and Sample Databases. When it is installed, the default location for this project file is C:\Program Files\Microsoft SQL Server\100\Samples\Analysis Services\Tutorials\Lesson 1 Complete.
To define a dimension
1. In Solution Explorer, right-click Dimensions, and then click New Dimension.
2. On the Welcome to the Dimension Wizard page, click Next.
3. On the Select Creation Method page, verify that the Use an existing table option is selected, and then click Next.
4. On the Specify Source Information page, verify that the Adventure Works DW data source view is selected.
5. In the Main table list, select Date.
6. Click Next.
7. On the Select Dimension Attributes page, select the check boxes next to the following attributes:
• Date Key
• Full Date Alternate Key
• English Month Name
• Calendar Quarter
• Calendar Year
• Calendar Semester
8. Change the setting of the Full Date Alternate Key attribute's Attribute Type column from Regular to Date.To do this, click Regular in the Attribute Type column. Then click the arrow to expand the options. Next, click Date > Calendar > Date. Click OK. Repeat these steps to change the attribute type of the following attributes as follows:
• English Month Name to Month
• Calendar Quarter to Quarter
• Calendar Year to Year
• Calendar Semester to Half Year
9. Click Next.
10. On the Completing the Wizard page, in the Preview pane, you can see the Date dimension and its attributes.
11. Click Finish to complete the wizard.
In Solution Explorer, in the Analysis Services Tutorial project, the Date dimension appears in the Dimensions folder. In the center of the development environment, Dimension Designer displays the Date dimension.
12. On the File menu, click Save All.
Defining a Cube
The Cube Wizard helps you define the measure groups and dimensions for a cube. In the following task, you will use the Cube Wizard to build a cube.
To define a cube and its properties
1. In Solution Explorer, right-click Cubes, and then click New Cube.
2. On the Welcome to the Cube Wizard page, click Next.
3. On the Select Creation Method page, verify that the Use existing tables option is selected, and then click Next.
4. On the Select Measure Group Tables page, verify that the Adventure Works DW data source view is selected.
5. Click Suggest to have the cube wizard suggest tables to use to create measure groups.
The wizard examines the tables and suggests InternetSales as a measure group table. Measure group tables, also named fact tables, contain the measures you are interested in such as the number of units sold.
6. Click Next.
7. On the Select Measures page, review the selected measures in the Internet Sales measure group, and then clear the check boxes for the following measures:
• Promotion Key
• Currency Key
• Sales Territory Key
• Revision Number
By default, the wizard selects as measures all numeric columns in the fact table that are not linked to dimensions. However, these four columns are not actual measures. The first three are key values that link the fact table with dimension tables that are not used in the initial version of this cube.
8. Click Next.
9. On the Select Existing Dimensions page, select the Date dimension that you created earlier and then click Next.
10. On the Select New Dimensions page, select the new dimensions to be created. To do this, verify that the Customer, Geography and Product check boxes are selected and clear the InternetSales check box.
11. Click Next.
12. On the Completing the Wizard page, change the name of the cube to Analysis Services Tutorial. In the Preview pane, you can see the InternetSales measure group and its measures. You can also see the Date, Customer, and Product dimensions.
13. Click Finish to complete the wizard.
In Solution Explorer, in the Analysis Services Tutorial project, the Analysis Services Tutorial cube appears in the Cubes folder, and database dimensions appear in the Dimensions folder. Additionally, in the center of the development environment, Cube Designer displays the Analysis Services Tutorial cube.
14. On the toolbar of Cube Designer, change the Zoom level to 50 percent, so that you can more easily see the dimensions and fact tables in the cube. Notice that the fact table is yellow and the dimension tables are blue.
15. On the File menu, click Save All.
Adding Attributes to Dimensions
In the following tasks, you will use Dimension Designer to add attributes to the Customer and Product dimensions.
Adding Attributes to the Customer Dimension
To add attributes
1. Open Dimension Designer for the Customer dimension. To do this, double-click the Customer dimension in the Dimensions node of Solution Explorer.
2. In the Attributes pane, notice the Customer Key and Geography Key attributes that were created by the Cube Wizard.
3. On the toolbar of the Dimension Structure tab, use the Zoom icon to view the tables in the Data Source View pane at 100 percent.
4. Drag the following columns from the Customer table in the Data Source View pane to the Attributes pane:
• BirthDate
• MaritalStatus
• Gender
• EmailAddress
• YearlyIncome
• TotalChildren
• NumberChildrenAtHome
• EnglishEducation
• EnglishOccupation
• HouseOwnerFlag
• NumberCarsOwned
• Phone
• DateFirstPurchase
• CommuteDistance
5. Drag the following columns from the Geography table in the Data Source View pane to the Attributes pane:
• City
• StateProvinceName
• EnglishCountryRegionName
• PostalCode
6. On the File menu, click Save All.
Adding Attributes to the Product Dimension
To add attributes
1. Open Dimension Designer for the Product dimension.
2. In the Attributes pane, notice the Product Key attribute that was created by the Cube Wizard.
3. On the toolbar of the Dimension Structure tab, use the Zoom icon to view the tables in the Data Source View pane at 100 percent.
4. Drag the following columns from the Products table in the Data Source View pane to the Attributes pane:
• StandardCost
• Color
• SafetyStockLevel
• ReorderPoint
• ListPrice
• Size
• SizeRange
• Weight
• DaysToManufacture
• ProductLine
• DealerPrice
• Class
• Style
• ModelName
• StartDate
• EndDate
• Status
5. On the File menu, click Save All.
Reviewing Cube and Dimension Properties
After you have defined a cube, you can review the results by using Cube Designer. In the following task, you review the structure of the cube in the Analysis Services Tutorial project.
To review cube and dimension properties in Cube Designer
1. To open the Cube Designer, double-click the Analysis Services Tutorial cube in the Cubes node of Solution Explorer.
2. In the Measures pane of the Cube Structure tab in Cube Designer, expand the Internet Sales measure group to reveal the defined measures.
You can change the order by dragging the measures into the order that you want. The order you create will affect how certain client applications order these measures. The measure group and each measure that it contains have properties that you can edit in the Properties window.
3. In the Dimensions pane of the Cube Structure tab in Cube Designer, review the cube dimensions that are in the Analysis Services Tutorial cube.
Notice that although only three dimensions were created at the database level, as displayed in Solution Explorer, there are five cube dimensions in the Analysis Services Tutorial cube. The cube contains more dimensions than the database because the Date database dimension is used as the basis for three separate date-related cube dimensions, based on different date-related facts in the fact table. These date-related dimensions are also called role playing dimensions. The three date-related cube dimensions let users dimension the cube by three separate facts that are related to each product sale: the product order date, the due date for fulfillment of the order, and the ship date for the order. By reusing a single database dimension for multiple cube dimensions, Analysis Services simplifies dimension management, uses less disk space, and reduces overall processing time.
4. In the Dimensions pane of the Cube Structure tab, expand Customer, and then click Edit Customer to open the dimension in Dimension Designer.
Dimension Designer contains these tabs: Dimension Structure, Attribute Relationships, Translations, and Browser. Notice that the Dimension Structure tab includes three panes: Attributes, Hierarchies, and Data Source View. The attributes that the Dimension contains appear in the Attributes pane. For more information, see: Defining Dimension Attributes, Creating User-Defined Hierarchies, Defining Attribute Relationships
5. Switch to Cube Designer by right-clicking the Analysis Services Tutorial cube in the Cubes node in Solution Explorer and then clicking View Designer.
6. In Cube Designer, click the Dimension Usage tab.
In this view of the Analysis Services Tutorial cube, you can see the cube dimensions that are used by the Internet Sales measure group. Also, you can define the type of relationship between each dimension and each measure group in which it is used.
7. Click the Partitions tab.
The Cube Wizard defined a single partition for the cube, by using the multidimensional online analytical processing (MOLAP) storage mode without aggregations. With MOLAP, all leaf-level data and all aggregations are stored within the cube for maximum performance. Aggregations are precalculated summaries of data that improve query response time by having answers ready before questions are asked. You can define additional partitions, storage settings, and writeback settings on the Partitions tab. For more information, see Partitions (Analysis Services - Multidimensional Data), Aggregations and Aggregation Designs, Designing Partition Storage and Aggregations
8. Click the Browser tab.
Notice that the cube cannot be browsed because it has not yet been deployed to an instance of Analysis Services. At this point, the cube in the Analysis Services Tutorial project is just a definition of a cube, which you can deploy to any instance of Analysis Services. When you deploy and process a cube, you create the defined objects in an instance of Analysis Services, and populate the objects with data from the underlying data sources.
9. In Solution Explorer, right-click Analysis Services Tutorial in the Cubes node and then click View Code.
The XML code for the Analysis Services Tutorial cube is displayed on the Analysis Services Tutorial.cube [XML] tab. This is the actual code that is used to create the cube in an instance of Analysis Services during deployment. For more information, see: How to: View the XML for an Analysis Services Project
10. Close the XML code tab.
Deploying an Analysis Services Project
To view the cube and dimension data for the objects in the Analysis Services Tutorial cube in the Analysis Services Tutorial project, you must deploy the project to a specified instance of Analysis Services and then process the cube and its dimensions. Deploying an Analysis Services project creates the defined objects in an instance of Analysis Services. Processing the objects in an instance of Analysis Services copies the data from the underlying data sources into the cube objects. For more information, see Deploying Analysis Services Projects, Configuring Analysis Services Project Properties
At this point in the development process, you generally deploy the cube to an instance of Analysis Services on a development server. Once you have finished developing your business intelligence project, you will generally use the Analysis Services Deployment Wizard to deploy your project from the development server to a production server. For more information, see Planning an Analysis Services Deployment, Using the Analysis Services Deployment Wizard
In the following task, you review the deployment properties of the Analysis Services Tutorial project and then deploy the project to your local instance of Analysis Services.
To deploy the Analysis Services project
1. In Solution Explorer, right-click the Analysis Services Tutorial project, and then click Properties.
The Analysis Services Tutorial Property Pages dialog box appears and displays the properties of the Active(Development) configuration. You can define multiple configurations, each with different properties. For example, a developer might want to configure the same project to deploy to different development computers and with different deployment properties, such as database names or processing properties. Notice the value for the Output Path property. This property specifies the location in which the XMLA deployment scripts for the project are saved when a project is built. These are the scripts that are used to deploy the objects in the project to an instance of Analysis Services.
2. In the Configuration Properties node in the left pane, click Deployment.
Review the deployment properties for the project. By default, the Analysis Services Project template configures an Analysis Services project to incrementally deploy all projects to the default instance of Analysis Services on the local computer, to create an Analysis Services database with the same name as the project, and to process the objects after deployment by using the default processing option. Related topic: Configuring Analysis Services Project Properties
Note
If you want to deploy the project to a named instance of Analysis Services on the local computer, or to an instance on a remote server, change the Server property to the appropriate instance name, such as <ServerName>\<InstanceName>.
3. Click OK.
4. In Solution Explorer, right-click the Analysis Services Tutorial project, and then click Deploy.
Business Intelligence Development Studio builds and then deploys the Analysis Services Tutorial project to the specified instance of Analysis Services by using a deployment script. The progress of the deployment is displayed in two windows: the Output window and the Deployment Progress – Analysis Services Tutorial window. Open the Output window, if necessary, by clicking Output on the View menu. The Output window displays the overall progress of the deployment. The Deployment Progress – Analysis Services Tutorial window displays the detail about each step taken during deployment. Related topics: Building Analysis Services Projects, Deploying Analysis Services Projects
5. Review the contents of the Output window and the Deployment Progress – Analysis Services Tutorial window to verify that the cube was built, deployed, and processed without errors.
6. Hide the Deployment Progress - Analysis Services Tutorial window by clicking the Auto Hide icon on the toolbar of the window.
7. Hide the Output window by clicking the Auto Hide icon on the toolbar of the window.
You have successfully deployed the Analysis Services Tutorial cube to your local instance of Analysis Services, and then processed the deployed cube.
Browsing the Cube
After you deploy a cube, the cube data is viewable on the Browser tab in Cube Designer, and the dimension data is viewable on the Browser tab in Dimension Designer.
To browse the deployed cube
1. Switch to Dimension Designer for the Product dimension in Business Intelligence Development Studio. To do this, double-click the Product dimension in the Dimensions node of Solution Explorer.
2. Click the Browser tab to display the All member of the Product Key attribute hierarchy. In lesson three, you will define a user hierarchy for the Product dimension that will let you browse the dimension.
3. Switch to Cube Designer in Business Intelligence Development Studio. To do this, double-click the Analysis Services Tutorial cube in the Cubes node of Solution Explorer.
4. Select the Browser tab, and then click the reconnect icon on the toolbar of the designer.
The left pane of the designer shows the objects in the Analysis Services Tutorial cube. On the right side of the Browser tab there are two panes: the upper pane is the Filter pane, and the lower pane is the Data pane. In an upcoming lesson, you will use the cube browser to do analysis.
No comments:
Post a Comment