Select Dimensions in Microsoft Analysis Services

In this section will choose  create few Dimension Tables. We will first create Time dimension then we will create Customer, Store and Product Dimensions.

Click on Next button on the Create Cube Wizard from the previous section

In the following dialog box, click on  New Dimension

Select Dimensions for Cube

 

  • You will see the Dimension Wizard Main Screen

Dimension Wizard

  • Click On Next and you will see ‘Choose How you want to create the Dimension’ screen
  • You will create the Product  Dimension Table in the next section

Select Fact Table and Choose Measures in Analysis Services

In this section will choose  a Fact Table and add measures to it.

Click on Next button on the Create Cube Wizard from the previous section

In the following dialog box, choose sales_fact_1998 as your Fact Table

Cube Wizard

 

  • Click on Next and you will see all the numeric columns present in the Fact table
  • Choose Store Sales, Store Cost and Unit Sales and click next

Cube Wizard

  • You will create a Dimension Table in the next section

Create Data Source for Analysis Services

  • In this section will create a new Microsoft Analysis Services Data Source from the ODBC DSN that we created in the last section.
  • Right click on the Data Sources (under your computer name/Tutorial ) in the left panel and select New Data Source

OLAP Datasource

  • In the next box, choose ‘Microsoft OLE DB Provider for ODBC Drivers

Data Link properties

  • Click on Next  and choose MSTUTORIAL DSN that we created in the previous step from the drop down list.

Data Link properties

  • Click on OK and you have successfully created a Microsoft Analysis Services data Source.
  • You will create Cube based on this Data Source in the next section

Create ODBC DSN for Analysis Services

  • We will be using the data from the Sample Access database provided by Microsoft Analysis Services in our tutorial.
  • Analysis Services will connect to this Access Database thru ODBC DSN
  • Go to Start menu. Click on Settings/Control Panel/Administrative Tools/Data Sources (ODBC)
  • Go to System DSN Tab and click on Add

ODBC Data Source Administrator

  • Choose Microsoft Access Driver and Click on Finish

Create new ODBC data source

  • In the following dialog box, choose name (MSTUTORIAL) and description.
  • Next click on the Select button and choose the file  ‘C:\Program Files\Microsoft Analysis Services\Samples\foodmart 200.mdb’ and click OK.

ODBC Setup

  • In the next section we will point to this MSTUTORAIL DSN to create a Data Source for the Analysis Services.

Create Database for Analysis Services

In the Analysis Manager, Right click on your computer name and choose ‘New Database’ . A dialog box will appear asking for the Database name and description.

In this input screen, type name and description of the database as shown below.

Create Database

Click OK and you will see a new node under your computer name in the left panel called “Tutorial”

Expand the Tutorial node and you will see Data Sources, Cubes, Shared Dimensions, Mining Tools and Database Roles.

Analysis manager tree

In the next section, we will learn how to create a Data source.

Starting the Analysis Manager in Analysis Services

Analysis Manager is the tool that you will use to access the Microsoft Analysis Services.  This tool runs within Microsoft management console. All Microsoft Analysis Services design will be done thru  Analysis Manager.

  • Go to the Start menu
  • Click Programs/MS SQL Server/Analysis Services/Analysis Manager

Analysis manager

  • Expand the ‘Analysis Servers’ node and you will see your computer name listed.
  • Expand your computer and you will see no children under that yet.
  • In the next step, you will create a database under this node.

Microsoft Analysis Services Tutorial

Introduction

The Microsoft Analysis Services is a feature of SQL Server which enables one to design and build powerful data warehouses and OLAP cubes on top of SQL Server based relational databases. Microsoft Analysis Services comes with powerful reporting and cube browsing features and supports dimensional model.

The Microsoft Analysis Services (also known as SQL Server 2000 Analysis Services) Tutorial is made by KnowledgeHills.com.

This is Analysis services tutorial is unofficial and is neither endorsed nor approved by Microsoft.

For the most recent and authentic tutorial, please refer to the Analysis Services Product Documentation.

This Tutorial is for educational purposes only.

Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .