Creating an OLAP Cube (SSAS)



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 and invoke the Cube Wizard.

Creating a New Analysis Services Project

Follow these steps:

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, 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, and then select Analysis Services Project in the Templates pane.

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

    OlapCube2.gif

Defining a Data Source

Follow these steps:
  1. In the Solution Explorer, right-click Data Sources, and then 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 you can use an existing one. Verify that "Create a data source based on an existing or new connection" is selected and then click New.

    OlapCube3.gif
     
  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.

    OlapCube4.gif
     
  9. Select Default impersonation information to use the credentials you just 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. The Select a Data Source dialog box appears. Under Relational data sources, the Adventure Works DW data source is selected. Click Next.

    Note that 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 that are available from the selected data source. You can filter this list to help you in selecting tables and views.

    OlapCube4.5.gif
     
  5. Click Next.
  6. Click Finish. The schema will automatically display for the new data source view.

    OlapCube5.gif

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. On Select Creation Method dialog select appropriate creation method and click next.
  4. On the next Dialog box check appropriate measure group table and click next.

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

    OlapCube7.gif