Analysis Services enables you to analyze large quantities of data. With it, you can design, create, and manage multidimensional structures that contain detail and aggregated data from multiple data sources.

The Data Tools is the primary environment for data warehouse designer that will use to develop business solutions that include Analysis Services, Integration Services, and Reporting Services projects. Each project type supplies templates for creating the objects required for business intelligence solutions, and provides a variety of designers, tools, and wizards to work with the objects.

To create new OLAP cubes, designers use Business Intelligence Data Tools. The Data Tools are the environment that you use to develop OLAP cubes and data mining models. It is the Microsoft Visual Studio 2008 environment with enhancements that are specific to business intelligence solutions, such as dimensional modelling, adding currency conversions or handling time dependencies.

You create Analysis Services projects in Visual Studio by using the Analysis Services Projects template. The Analysis Services project helps you organize and manage the items that are required to build, debug and deploy an Analysis Services database.

• Open Visual Studio from your desktop or search in the program groups
• Click File | New | Project

• In the New Project dialog box, in the Project Types pane, click the Business Intelligence Projects folder.
• In the Templates pane, click the Analysis Services Multidimensional and Data Mining Project icon.

• Name it: NO SID SalesAnalysis
• Change the location to c:\temp

A data source is an object that provides the information needed for Analysis Services to connect to a source of information for the business intelligence solution. Analysis Services can access data from one or more sources of data, provided that Analysis Services is able to construct the OLAP or data mining queries required by the business intelligence solution. The list of providers that you can utilize in an Analysis Services project is increasing over time as Microsoft and third-party vendors provide support for SQL Server Analysis Services.

At a minimum, a data source includes an identifier, a name, and a connection string.

• Server name: dwh.hdm-server.eu
• Use SQL Server identification
• User name: DM_NO GID
• Password: DM_NO GID
• Save your password
• Database: select the database from the drop-down menu: pick AdventureBikes_DM_NO GID
• Click Next

In the Impersonation Information set the credentials to inherit. This tells Analysis Services to use the username and password you have just defined.

Click Next.

Rename the Data Source to DATAMART.ds

Click Finish.

Next, we have to identify the related tables, views, and relationships between the underlying database from one or more data sources.

• Get F_MonthlySales and select related tables.

For all dimensions, use the New Dimension wizard to create new dimensions.

• In the creation method window, create the dimenions by using an exiting table.
• select D_Product as the main table. The key column is displayed because it is the primary key in the datastore database.
• In the next window, select all available attributes from the source table to become dimension attributes.
• Type Product as a name for the new dimension.

Save the dimension and close the design window.

Repeat the steps for

• Use the wizard to create a new data cube.
• In the Select the creation method, use an existing table from the data store.
• In the Select Measure Group Tables window, select F_Monthly_Sales. This table will be used to select the group of measures from.
• Then, select all measures to be included into the cube.
• Select the existing dimensions to include in the cube.
• In the Completing the Wizard Window, rename the Cube SalesCube.

Before you deploy our data cube on the Analysis Services Database, you have to add the server name to the project properties.

In the main menu → Project → … properties.

• in the property page window, set the target deployment server to NO MDB
• Click ok.

In the main menu → Build → Deploy the cube.

The password is required for the impersonation account of data source

After the deployment was successful, open the Browse… window in the Cube Designer and create a query to check, if everything is okay. Just drag-and-drop a keyfigure into the browser panel and click on execute query

• Go to Data Source View.
• Right-click on a free space and click on Add/Remove Tables.

• Add F_Monthly_Costs and select Add Related Tables.
• Your data source view should look like this.

• Use the New Dimension wizard to create the new dimension D_Cost_Record.

Open the Data Cube structure and add a new measure group. The measure group F_Monthly_Costs should contain the cost measure.

• Add the dimension to the cube.

Build and deploy your cube.

In the cube designer, great a new calculation. Open the formula editor with Menu → Cube → New calculated member.

• Name it: [StoreProfit].
• Expression: [Measures].[Gross Profit] - [Measures].[Cost Value]

• Select the format string “#.##0;-#.##0” from the drop down box.
• Set the Associated Measure Group to F_Monthly_Sales.

Deploy your cube.

Now, use Excel to analyse your cube.

In Data → Get Data → From Analysis services.

Server: NO MDB Use windows authentication.

Select your cube NO SID SalesAnalysis.

Create a Pivottable and create query to find the top selling stores. Order it by StoreProfit.

In the data source view, add

• F_Monthly_Stock
• F_Monthly_Marketing

Add the related tables.

In the cube designer, add new measure groups.

• F_Monthly_Stock
• F_Monthly_Marketing

Rename

• F_Monthly_Sales → Sales
• F_Monthly_Stock → Stock
• F_Monthly_Marketing → Marketing
• F_Monthly_Costs → Costs

Build and deploy your cube.

A perspective is a definition that allows users to see a cube in a simpler way. A perspective is a subset of the features of a cube. A perspective enables administrators to create views of a cube, helping users to focus on the most relevant data for them. A perspective contains subsets of all objects from a cube. A perspective cannot include elements that are not defined in the parent cube.

Build and deploy your cube.