Building Cubes Part-II

Building Cubes with SQL Server 2005 Analysis Services

Configure Data Source View

The next step is to configure your data source view. In the Solution Explorer, right-click on the ‘Data Source Views’ Folder, then select ‘New Data Source View’ to launch the wizard. Click ‘Next’, and you will be prompted to select a data source. Select the one we just created and you will see the following (Figure 6):

Figure 6

At this point, you should already have an idea of what data you are trying to analyze, for example perhaps someone is asking to analyze the internet sales data. So, let’s continue with our demo by selecting the DimCustomer, DimGeography, DimProduct, DimTime, and FactInternetSales tables, then click ‘Next’. We will name our view ‘Adventure Works DSV’ and click ‘Finish’. BIDS will then display the design view of the data source view we just created.

The Cube

The next step is to build the actual cube. In Solution Explorer, right-click on the ‘Cubes’ folder and select ‘New Cube’ to launch the wizard, then click ‘Next’ and you will see the following screen (Figure 7):

Figure 7

For our demo we will accept the defaults and just click ‘Next’ However, you may want to experiment with different cubes by selecting different options, such as creating attributes only or not using the auto build feature. You can even build the cube without a data source and select a template to use. Some experimentation here would give you a greater sense of what actions the wizard will perform for you if you compare the finished products side-by-side.

You should now see the following (Figure 8):

Figure 8

Select the data source view we have just created and click ‘Next’. The wizard will now detect the dimension and fact tables and analyze the relationships between them to offer some suggestions. Click ‘Next’ to review the suggestions (Figure 9):

Figure 9

There is one extra piece of information not readily apparent to the wizard, and that is the identity of the Time dimension table. The Time dimension is quite important, as it will be used to represent time periods that are useful for analyzing and reporting on data.

We will manually select the DimTime table as our Time dimension, and select ‘Next’. You should now see the following (Figure 10):

Figure 10

Previous Next