SQL Server 2005/2008 comes along with the Business Intelligence Development Studio (also known as BIDS) which is used for creating projects related to analysis services, integration services, report server and report models (used to design reports on client side). In order to get started with BIDS to build your first analysis services project, you need to have the following SQL Server components installed: SQL Server Database Engine SQL Server Analysis Services (SSAS) Business Intelligence Development Studio A good working data warehouse Below are the steps to be followed to create the cube
Create a new Analysis Services project
For creating a new project in BIDS, you’ll find the Business Intelligence Development Studio (BIDS) in Start > Microsoft SQL Server 2005/2008 folder. Once you’re in the business intelligence development studio, click File > New > Project from where you could create a new Analysis Services Project. Give it any name you like and click on ok.
Define a data source
To define a data source, you'll use the Data Source Wizard. You can launch this wizard by right-clicking on the Data Sources folder in your new Analysis Services project. The wizard will walk you through the process of defining a data source for your cube, including choosing a connection and specifying security credentials to be used to connect to the data source.
Defining a Data Source View
A data source view is a persistent set of tables from a data source that supply the data for a particular cube. BIDS also includes a wizard for creating data source views, which you can invoke by right-clicking on the Data Source Views folder in Solution Explorer. You can select from existing tables and set relations between them or enter in your own statements using “Edit Named Query”.
Now you can move further either creating Cube using Cube Wizard which automatically creates Dimensions to be used in the cube or by creating a Dimensions and using it in the Cube.
BIDS also provides wizard for creating dimensions. It helps in defining the key columns and attributes to be in used within a cube.
Cube wizard appears within BIDS for creating cubes where in measurement group tables are to be defined. Here option to either select from existing dimensions or create new dimensions is provided.
Deploying and Processing a Cube
To deploy the cube you just created, select Build > Deploy. This will deploy the cube to your local Analysis Server, and also process the cube, building the aggregates for you. BIDS will open the Deployment Progress window which will keep you informed during deployment and processing.