Creating an OLAP Cube (SSAS)

Introduction

To create a new OLAP cube using Business Intelligence Development Studio (BIDS),

you need to perform these steps- 

• Create a new Analysis Services project
• Define a data source
• Define a data source view
• Invoke the Cube Wizard

How to create a New Analysis Services Project?

Follow these steps

1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click SQL Server Business Intelligence Development Studio.

   The Microsoft Visual Studio development environment opens.

2. Select File -> New -> Project.

3. In the New Project dialog box, select Business Intelligence Projects in the Project types pane, then choose Analysis Services Project in the Templates pane.

4. Name the new project, select a convenient location to save it, and click OK to create it.

Defining a Data Source

Follow these steps

1. In the Solution Explorer, right-click Data Sources and click New Data Source.

2. On the Welcome to the Data Source dialog box, click Next to open the "Data Source Wizard," which has the sub-title "Select how to define the connection."

3. On the "Select how to define the connection" dialog box, you can define a new connection or use an existing one. Verify that "Create a data source based on an existing or new connection" is selected and then click New.

4. In the Connection Manager Dialog box, you define connection properties for the data source. Select the Native OLE DB\SQL Native Client provider (this is the default provider). Analysis Services supports other providers also, which are displayed in the Provider list.

5. Select the server containing your database from the Server Name combo box. Fill in your authentication information.

6. Select or enter a database name.

7. Click Test Connection to test the connection to the database.

8.  

   Click OK, and then click next.

    

9. Select Default impersonation information to use the credentials you supplied for the connection and click Next.

10. Accept the default data source name and click Finish.

Defining a Data Source View

Follow these steps

1. In Solution Explorer, right-click Data Source Views, and then click New Data Source View.

2. On the Welcome to the Data Source View Wizard page, click next.

3. Under Relational data sources, the Adventure Works DW data source is selected. The Select a Data Source dialog box appears. Click Next.

   You could also launch the Data Source Wizard from here by clicking New Data Source.

4. On the Select Tables and Views page, you select tables and views from the list of objects available from the selected data source. You can filter this list to help you in selecting tables and views.

5. Click Next.

6. Click Finish. The schema will automatically display the new data source view.

Invoking the Cube Wizard

Follow these steps

1. In Solution Explorer, right-click Cubes.

2. On the Welcome to the Cube Wizard dialog box, click next.

3. Select the appropriate creation method on the Select Creation Method dialog and click next.

4. Check the appropriate measure group table on the next Dialog box and click next.

5. Click next and next and finish to complete the cube creation.

Summary

In this article, you will see how to create an OLAP Cube (SSAS) step by step.