Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Creating a Simple OLAP Cube

Installing Analysis Services is a straightforward procedure. The installation program creates a new program group called Analysis Services under the Microsoft SQL Server program group. To create an OLAP cube, start the Analysis Manager console. It will automatically connect to the Analysis Server. Right-clicking the Analysis Server folder and selecting Register Server can register additional Analysis servers.

TIP

It's recommended that you install the latest SQL Server 2000 service pack available before installing Microsoft Analysis Services, and install the latest Analysis Services service pack after the Analysis Services installation.

Figure 6 shows the initial Analysis Manager screen.

Figure 6Figure 6 The Analysis Manager console.

To create a sample OLAP cube, follow these steps:

  1. Right-click the Analysis Server you want to use and then select New Database.

  2. Enter the name and description of the database and click OK. A new cube is created under the selected server.

  3. Double-click the cube to expand its contents.

  4. Right-click Data Sources and select New Data Source. The Data Link Properties dialog box appears.

  5. Select Microsoft OLE DB Provider for ODBC Drivers (see Figure 7) and click Next to continue.

  6. Figure 7Figure 7 The Data Link Properties dialog box.

  7. On the Connection tab, click Use Data Source Name and select the appropriate data source (see Figure 8). The names that appear on this list are the data sources configured on the Data Sources (ODBC) utility in the Windows Control Panel; each data source points to a specific database.

  8. Figure 8/Figure 8 Data source configuration.

  9. Click Test Connection and then click OK.

  10. Right-click Cubes and select New Cube Wizard.

  11. Read the welcome screen and then click Next.

  12. From the left pane, select the fact table you want to use (see Figure 9). Click Next.

  13. Figure 9Figure 9 The Cube Wizard.

  14. From the left pane, select the measures you need for your cube (see Figure 10). Click Next.

  15. Figure 10Figure 10 Selecting measures.

  16. In the left pane, click the New Dimension button. The Dimension Wizard appears.

  17. Read the welcome screen and then click Next.

  18. Select Star Schema: Single Dimension Table (see Figure 11). Click Next.

  19. Figure 11Figure 11 The Dimension Wizard.

  20. In the left pane, select the table to use for the dimension. Click Next.

  21. Select Standard Dimension. Click Next.

  22. Select the levels you need for the dimension (see Figure 12). Start with the more general and finish with the less general level.

  23. Figure 12Figure 12 Selecting dimension levels.

  24. Click Next until the Dimension Name dialog box appears. Enter a name for the dimension and click Finish.

  25. Repeat steps 12 through 18 to create all the dimensions that you need.

  26. Click Next in the Cube Wizard. Enter the name of the cube and click Finish. The Cube Editor window appears (see Figure 13).

  27. Figure 13Figure 13 The Cube Editor.

  28. From the menu, select Tools, Design Storage. The Design Storage Wizard appears.

  29. Read the welcome screen and click Next.

  30. Select MOLAP and click Next.

  31. Click the Start button to calculate the size of the cube and the amount of aggregations needed. Click Next.

  32. Select Process Now and click Finish to calculate the aggregations. The time to process the cube may vary depending on the number of dimensions, the number of levels in each dimension, and the amount of data stored in the database.

  33. When the process completes successfully, click Close to finish.

  34. The cube is finished and ready to process queries from client applications.

  • + Share This
  • 🔖 Save To Your Account