RSS Feeds

Article Topics

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

SQL Azure

Developer

General DBA

ASP.NET / ADO.NET

SQL Azure

USEFUL SITES :

ASP.NET Tutorials
Windows and SQL Azure Tutorials
Cloud Hosting Magazine
SharePoint Tutorials
Windows Server Help

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...

Accessing Cubes from Excel 2007

By : Dinesh Asanka
Jan 27, 2009

Introduction
Cubes are built over an OLAP database to enhance the performance of data gathering. The architecture of a cube enables users to generate reports from them. However, if using a report tool like SQL Server Reporting Services (SSRS), there will be times where a user will require a new report or modifications made to an existing report. This task is not only time consuming but also not necessarily time efficient.

If reporting access to cubes can be provided from Microsoft Excel, then report building can be performed by an end user. Majority of the time, using this method, users can construct reports the way they wish. Improvements in Excel 2007 have provided a number of new fancy features that can be used with cubes.

Please note that this article does not cover how to build SQL Server Analysis Services (SSAS) cubes.

Cube
A sample cube has been created to facilitate the examples in this article; using the AdventureWorksDW database which comes with the SQL Server installation. Figure 1 shows the structure of the cube that will be accessed from Excel.

Figure 1: Cube Structure

After creating this SSAS project, the SSAS database needs to be processed and deployed to the SSAS server.

Connecting to SSAS
Launch Microsoft Excel first. Select the option, Select the Data table, then select the From Other Services button in the Data ribbon, then select From Analysis Services. As shown in Figure 2, a list of available data sources is provided.

Figure 2: Other Sources Option

In the Analysis Services option is the screen shown in Figure 3. To connect to the SSAS servers, login credentials need to be provided. However, SSAS does not support SQL Server authentication, hence the Windows Authentication option must be selected.

Figure3: Login Credentials to SSAS Server.

After providing the login credentials, the SSAS database and the Cube need to be selected as shown in Figure 4.

Figure 4: SSAS database and Cube

All cubes for the selected SSAS database will be listed. Only one cube can be specified at this point. The next step is to set the configurations to the Data connection file. There are options to specify the file name and the path for the Data connection file.

Figure 5: Data Connection File

Next select (from the Figure 6) whether a Report or chart is wanted.

Figure 6: How to view data.

Viewing Data
Now are the most fascinating steps, viewing the SSAS data from an Excel file. In the right side of the excel sheet, notice a PivotTable Field List as seen in Figure 7. Within this list the KPI, measures and dimension attributes can be seen; the user is able to select the attributes they want.

Figure 7: Pivot Table Field List

To see the Sales amount for each product color and class.; simply select, Class and Color from the Product dimension and Sales Amount.

Figure 8: Pivot Table

A pivot table can be designed with columns and row headers as shown in Figure 9.

Figure 9: Pivot table with Conditional Formatting

Figure 9 showing a pivot table with the Conditional formatting available in Excel 2007. As this is a Excel sheet, all the additional features available with Excel are provided.

KPIs are another important feature in SSAS. As this is not the place to describe how to create KPIs using SSAS, the discussion will only include how to display KPI values in a Excel sheet.

The available KPI's will be listed in the Pivot Table Filed List. For each KPI there will be three attributes, Value, Status and Trend.

Figure 10 : KPIs in Pivot Table Field List

After selecting the appropriate KPIs and it’s attributes, users can be given information akin to what is shown below in Figure 11.

Figure 11: KPI Values

Using these techniques, users will have a more aesthetically pleasing interface while DBA's save time instead to focus on other parts of the system.