This Tool puts Excel to work in SQL Server
Featured eBooks
Connecting state and local government leaders
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.
By Larry StevensSpecial to GCNLike 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.OLAP@Work, a third-party Excel add-in from OLAP@Work Inc., provides the missing link between Microsoft's OLAP server and its spreadsheet. OLAP@Work also provides a Web client and embedded OLAP client capability on other components in Microsoft Office.Once installed, OLAP@Work 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.To explore the data further, you can drag and drop dimension buttons from one axis to another, or rearrange dimensions within the axes. With OLAP@Work 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 OLAP@Work 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.
OLAP@Work is fairly easy to learn and adds analytical power to the spreadsheet program
Looking closer
Looking closer
NEXT STORY: Lawmakers take e-government hearing on the road