Posts Tagged ‘SSAS’

SSAS basics – building my first cube

Posted: June 30, 2012 in MSSQL
Tags:
Task: to give a basic look at the building of my first data cube by means of SSDT by Microsoft Visual Studio



To build a data cube we will need to follow these few steps:

1. Creating a data source
To connect to this data source the service account is being used. Using the service account (the account that runs the SQL Server Analysis Server service) is common even in production. Make sure that the service account has privileges to read this data source.

Figure 1: Creating a data source – Configuration

Figure 1: Creating a data source – Configuration



2. Creating a data source view
Select FactInternetSales and click on the Add Related Tables button.

Figure 2a: Creating a data source view – Configuration

Figure 2a: Creating a data source view – Configuration


Figure 2b: Creating a data source view – Diagram of facts and dimensions

Figure 2b: Creating a data source view – Diagram of facts and dimensions



3. Building my first cube
Select the data source from the first step, then click on the Suggest button and at the end select measures and dimensions.

Figure 3a: Building my first cube

Figure 3a: Building my first cube


Figure 3b: Tweaking dimensions for user-friendly appearance

Figure 3b: Tweaking dimensions for user-friendly appearance


Any warnings appear after tweaking. We will ignore warnings for this basic example.

4. Deploying and querying the cube
At the last step, click on the Deploy Solution item on the Build menu and publish the cube.

Figure 4: Querying the cube

Figure 4: Querying the cube


Double click on the cube and navigate to the Browser tab. For example, drag and drop measures such as ExtendedAmount and dimensions and hierarchies such as Fiscal Quarter – Month hierarchy under the Due Date dimension into the query pane. We will retrieve requested data.

Source code: SSAS Package (SQL Server 2008 R2 and BIDS; Adventure Works DW2008R2)
Note: do not forget to customize connection strings etc

Additional references:
Advertisements