This Tool puts Excel to work in SQL Server

This Tool puts Excel to work in SQL Server

[email protected] is fairly easy to learn and adds analytical power to the spreadsheet program

By Larry Stevens

Special to GCN

Like practically every other online analytical processing product, Microsoft SQL Server 7.0 has a Microsoft Excel add-in client. But its Excel add-in is not sufficiently powerful for large amounts of data.

[email protected], a third-party Excel add-in from [email protected] Inc., provides the missing link between Microsoft's OLAP server and its spreadsheet. [email protected] also provides a Web client and embedded OLAP client capability on other components in Microsoft Office.

Once installed, [email protected] adds its own menu and toolbar to Excel. Each has a 'Create an OLAP report' option. This launches a wizard that guides you through the creation of the report. The first step is to select the cell or range of cells where the report will be added. In the next screen, you indicate preferences such as whether you want the program to generate and display a description of the report based on its contents.

The final window lets you select the OLAP server you want to connect to.

Next, you go to Report Layout dialog box, where you build, and later modify, your report. The top panel contains dimension buttons, each of which names an element you may want to perform analysis on, such as gender, education level, monthly expenses or agency offices.

To create the report, you simply drag and drop the dimension buttons to either the row or column box. Each time you move a dimension button, you see a dialog box that allows you to select specific elements without selecting the overall category. So, for example, if you move the agency button to the column box, the dialog box allows you to select the specific offices you'd like to include in the report.

There is also a measure button that, when dragged to a column or row, allows you to decide on the unit you'll use, such as monthly budget, number of employees or work hours.

Once the dimension buttons are in place, the program generates a report in Excel. For example, if you move 'offices' to the row box and 'May expenses' to the column box, you'll see a report listing each office along with their monthly expenses. Additionally, you can have the program automatically generate a chart based on the data.

Looking closer

To explore the data further, you can drag and drop dimension buttons from one axis to another, or rearrange dimensions within the axes. With [email protected] 2.0, you can place the dimension line right on the spreadsheet. This lets you add, remove or rearrange dimensions by dragging and dropping within Excel.

Once the data is in Excel, it is live. You can click on any item and drill down to additional levels of detail. Or you can display the data as a percentage of a row. If you do, as you drill back up, the percentages are recalculated to represent the sum totals.

To use [email protected] effectively, users will need a day or so of training. But because many people are comfortable with Excel and the product comes with a nice set of wizards, drop-down menus and drag-and-drop bars, the learning curve is fairly easy.


  • business meeting (Monkey Business Images/

    Civic tech volunteers help states with legacy systems

    As COVID-19 exposed vulnerabilities in state and local government IT systems, the newly formed U.S. Digital Response stepped in to help. Its successes offer insight into existing barriers and the future of the civic tech movement.

  • data analytics (

    More visible data helps drive DOD decision-making

    CDOs in the Defense Department are opening up their data to take advantage of artificial intelligence and machine learning tools that help surface insights and improve decision-making.

Stay Connected