Creating the SSAS Project and Connecting to the Data Source

To begin, open Visual Studio and select Analysis Services Multidimensional and Data Mining project.

Right click the Data Source in the Solution Explorer and click New Data Source. Complete each step in the Wizard to connect to the Database AdventureWorksDW2012.

Creating a Data Source View

Right click Data Source Views in the Solution Explorer and select New Data Source View to launch the Wizard. This cube includes only the Product and Customer Dimensions tables and the Fact Internet Sales table.

Click through the Next buttons to Finish and complete the Data Source View.

Creating the Cube in SSAS

Right click on Cubes in the Solution Explorer and select New Cube to start the Cube Wizard. Select the FactInternetSales table as the Measure Group table.

Select the New Dimensions, if not already selected for you.

Click Finish to Complete the Cube.

The Cube Structure window should now appear.

Double click on each of the Dimensions in the Solution Explorer to add attributes to them. Only the Key will be in the Attributes pane when it is first created. The fields from the Data Source View must be selected and Dragged over to the Attributes pane to complete the Dimension.

Some of the Dimensions may not have been automatically created when creating the Cube. To Manually create the Dimensions in this project, right click on Dimensions in the Solution Explorer and click on New Dimension.

Select the Dimension to add from the drop down and click next.

Select the attributes to include in the Dimension and click Next.

Click Finish to complete the addition of the Subcategory Dimension. Dimensions will need to be added for Product Category and Geography by repeating these same steps.

Adding Dimension Usage to Measures in the Cube

The newly added Dimensions will not appear in the Dimension Usage tab of the Cube as they need to be added. The cube may be deployed and processed without them and they will appear in SSMS using a connection to the Analysis Server in the Object Explorer, but they will not appear in the Metadata tab of an MDX query as there is no relationship established with the Measure group. So they are unusable until this step is completed.

In the Cube, click on the Dimension Usage tab and click on the button to Add Cube Dimension.

in the Select Relationship Type drop down pick Referenced. Select the Intermediate Dimension Relationship (if not already selected) as Product and select the Reference and Intermediate dimension Attributes as the Product Subcategory Key and click OK.

The Dimension and Measure groups are now related for Product and Product Subcategory. The same steps may be used to add Cube Dimensions for Product Category (to Product Subcategory) and Geography (to Customer).

Deploy and Process the Cube

In the Solution Explorer, right click the Project Name and click on Deploy.

A window will pop up and provide the details for any errors encountered or it will let you know that the Deployment was Successful.

Right click the Project name again and select Process to finish the Cube.

A pop up window will tell you how the Process is progressing and let you know once it has completed.

To Confirm that Deployment and Processing was successful, a connection to Analysis Services may be opened in SSMS and then select an MDX query to test the new Cube. This query provides the Sales data for the Product Categories in the cube.

To change the data format in the cube, go back to the Cube Structure and click on the Measure (Sales Amount in this case) and select Format String from the Properties section and change to Currency.

After Deploying and Processing the cube again, we can rerun the query to see that the sales amounts are now formatted as Currency.