Help protect and work with business intelligence data in Excel

Power User Corner

By Colin Wilcox

The last column explained how to use Microsoft Data Analyzer and Microsoft Excel to find and analyze business intelligence data. This week's column, the second in a two-part series, explains how to help protect and share your reports, how to answer the "why" questions that result from any business intelligence effort, and how to browse data cubes directly with Excel.

Applies to
Microsoft Excel 2002

See all Power User columns
See all columns

The previous Power User column, Analyze Business Intelligence Data with Data Analyzer and Excel, describes the process that a group of financial analysts followed to find problem areas in their business intelligence data. Those analysts also mentioned another problem: version control. Their coworkers and managers duplicated their reports, and, as a result, the analysts often found a dozen versions of a report circulating around the company. Some of them contained inaccurate data.

This column explains one way to work around versioning problems. It briefly touches on how you can answer the "why" questions posed by business intelligence data. Finally, it shows how to use Excel to connect directly to data cubes, a useful technique for those times when Data Analyzer isn't available. As part of that exercise, we'll compare a PivotTable® report with a Data Analyzer view.

 Note    To complete all of the procedures in this column, you must first read the previous column, Analyze Business Intelligence Data with Data Analyzer and Excel.

Help maintain version control with passwords

One of the fastest ways to overcome versioning problems is to place your reports in a central location and help protect them with a password.

 Note   The following steps assume you know how to copy a file to a network share. If you don't, contact your IT department.

These steps explain how to password-protect an entire Excel workbook. Excel also provides other security options. For example, you can help protect individual worksheets in a workbook, and you can also allow certain users to edit some of the cells in a worksheet. For more information about using those options, see Help in Excel.

To help protect an entire workbook with a password

  1. Open a workbook in Excel that you want to help protect.
  2. On the Tools menu, point to Protection, and then click Protect Workbook.
  3. Ensure that the Structure check box is selected, type a password in the Password box, and then click OK.
  4. Type the password again and then click OK.
  5. Copy or move the file to a shared folder on your computer or elsewhere on your network.

Answering "why" questions

The previous column explained how to use Data Analyzer to identify unprofitable routes run by airlines. If you're working through both Power User columns in this series, so far you've found a business problem, created a report, and shared your findings with the right people. However, you haven't answered a key question: Why are those routes in trouble? Answering "why" questions is a key part of the business intelligence cycle. It's the reason you acquire business intelligence systems and data in the first place.

At times, you can use PivotTable reports to answer "why" questions. Because PivotTable reports contain all the exported data, they can show you relationships that a Data Analyzer view can't. For example, say that a Data Analyzer view shows decreased sales for one department. You then export the view to a PivotTable report, and as you explore the data you find that the department employs only one salesperson. Bingo! You've found one reason why sales are down.

However, software and reports cannot always help. In some cases, you have to rely on your knowledge about the data. To use the previous example, the department may use only one salesperson because the company reorganized itself.

Let's return to the analysts mentioned earlier. They attend numerous sales and marketing meetings, and they stay in constant contact with key people in those organizations. For them, that's the best way to answer their "why" questions. You may need to develop relationships with accountants, cashiers, or line chefs. The key point to remember is that you must know enough about your business to answer the "why" questions. The software and data cannot always do that for you.

Connecting to data cubes directly from Excel

You may not always have access to a computer with Data Analyzer, or you may not want to take the time to create a view. In those cases, you can connect to a data cube directly from Excel and create a PivotTable report. The following steps explain how to create a data source—a connection to the sample cube supplied with Data Analyzer—and see the data that you worked with in the previous column.

First, create the connection to the data source:

  1. Open a new, blank worksheet in Excel.
  2. On the Data menu, click PivotTable and PivotChart Report.
  3. Select External data source, and then click Next.
  4. Click Get Data.
  5. Click the OLAP Cubes tab, click New Data Source, and then click OK.
  6. In the first box, type a name for the data source.
  7. In the second box, select Microsoft OLE DB Provider for OLAP Services 8.0, and then click Connect.
  8. Click Cube file and then click the button with the ellipsis (...) to navigate to the sample data cube.

 Note    If you installed Data Analyzer in its default location, navigate to C:\Program Files\Microsoft Data Analyzer\Data Analyzer 3.5, and double-click the file Airline.cub. If you installed Data Analyzer in another location, search for the \Data Analyzer 3.5 folder. You'll find the cube in that location.

  1. Click Finish, and then click OK to close the Create New Data Source dialog box.
  2. In the Choose Data Source dialog box, ensure that your new connection is selected, and then click OK.

All those steps return you to step 2 of the PivotTable and PivotChart Wizard. Take a deep breath, and follow these steps to complete the wizard and build the PivotTable report:

  1. Click Next, and then click Finish to complete the wizard.
  2. Move the Destinations item from the PivotTable Field List pane to the Drop Row Fields Here area of the blank PivotTable report.
  3. Move the Report Date item to the Drop Page Fields Here area of the report.
  4. Move the Types of Aircraft item to the Drop Column Fields Here area of the report.
  5. Move the remaining items to the Drop Data Items Here area of the report.

That's it! Now, let's compare the PivotTable report with the Data Analyzer view:

  1. In the report, click the arrow in the Region Name column label.
  2. Click each of the check boxes in the list until a double check mark appears. The list should look like this.

    A typical list of available members.
  3. Click OK.
  4. Scan the report and try to find the relationships that you see when you work with the Data Analyzer view. (You can see the view in the section titled "Analyzing the data for problems" in the previous column .)

On the one hand, you can't see the relationships among the members in the different dimensions as easily or as quickly as you can in Data Analyzer. On the other hand, the PivotTable report gives you all the data right away. In addition, PivotTable reports enable you to find details that Data Analyzer cannot provide. The point is, the two applications (and the OLAP technology behind Data Analyzer) complement each other. Use Data Analyzer to quickly locate the data you need, and then analyze that data thoroughly with Excel.

More information

About the author

Colin Wilcox writes for the Office Help team. In addition to contributing to the Office Power User Corner column, he writes articles and tutorials for Microsoft Data Analyzer.

See all Power User columns
See all columns