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.

Featured

  • senior center (vuqarali/Shutterstock.com)

    Bmore Responsive: Home-grown emergency response coordination 

    Working with the local Code for America brigade, Baltimore’s Health Department built a new contact management system that saves hundreds of hours when checking in on senior care centers during emergencies.

  • man checking phone in the dark (Maridav/Shutterstock.com)

    AI-based ‘listening’ helps VA monitor vets’ mental health

    To better monitor veterans’ mental health, especially during the pandemic, the Department of Veterans Affairs is relying on data and artificial intelligence-based analytics.

Stay Connected