- What Is OLAP and Why Do We Need It?
- What Is Analysis Services?
- Creating a Simple OLAP Cube
- References
Like this article? We recommend
Like this article? We recommend
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
6 The Analysis Manager console.
To create a sample OLAP cube, follow these steps:
Right-click the Analysis Server you want to use and then select New Database.
Enter the name and description of the database and click OK. A new cube is created under the selected server.
Double-click the cube to expand its contents.
Right-click Data Sources and select New Data Source. The Data Link Properties dialog box appears.
-
Select Microsoft OLE DB Provider for ODBC Drivers (see Figure 7) and click Next to continue.
-
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.
Click Test Connection and then click OK.
Right-click Cubes and select New Cube Wizard.
Read the welcome screen and then click Next.
-
From the left pane, select the fact table you want to use (see Figure 9). Click Next.
-
From the left pane, select the measures you need for your cube (see Figure 10). Click Next.
In the left pane, click the New Dimension button. The Dimension Wizard appears.
Read the welcome screen and then click Next.
-
Select Star Schema: Single Dimension Table (see Figure 11). Click Next.
In the left pane, select the table to use for the dimension. Click Next.
Select Standard Dimension. Click Next.
-
Select the levels you need for the dimension (see Figure 12). Start with the more general and finish with the less general level.
Click Next until the Dimension Name dialog box appears. Enter a name for the dimension and click Finish.
Repeat steps 12 through 18 to create all the dimensions that you need.
-
Click Next in the Cube Wizard. Enter the name of the cube and click Finish. The Cube Editor window appears (see Figure 13).
From the menu, select Tools, Design Storage. The Design Storage Wizard appears.
Read the welcome screen and click Next.
Select MOLAP and click Next.
Click the Start button to calculate the size of the cube and the amount of aggregations needed. Click Next.
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.
When the process completes successfully, click Close to finish.
)
Figure
7 The Data Link Properties dialog box.
)
Figure
8 Data source configuration.
)
Figure
9 The Cube Wizard.
)
Figure
10 Selecting measures.
)
Figure
11 The Dimension Wizard.
)
Figure
12 Selecting dimension levels.
)
Figure
13 The Cube Editor.
The cube is finished and ready to process queries from client applications.