Microsoft Office Online
Sign in to My Office Online (What's this?) | Sign in

 
 
Help and How-to
Search
Search
 
Try or buy
Get Office 2007
Icon: Get help: (c) Microsoft
Help & support
Updates: (c) Microsoft
Office updates
 
 
 
Help by Product
How-to Resources
Support and Feedback
Technical Resources
Additional Resources
Get Office 2007
Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.
Printer-Friendly VersionPrinter-Friendly Version
Connecting to data cubes and creating views
 
Applies to
Microsoft Data Analyzer 2002

"Can I connect Data Analyzer directly to my database?"

We hear that question a lot, and the answer is no. In fact, even if you could connect Data Analyzer to your online transaction processing (OLTP) or online analytical processing (OLAP) database, chances are you would slow down the database, and you probably would not get the answers you want. Data Analyzer is designed to connect to data cubes. Why? Cubes contain the aggregated numbers that you need to answer business intelligence questions such as "how has buying behavior changed over the past five years for customers aged 35 to 50?"

Note  For a gentle introduction to databases and cubes, read the Welcome to the Data Analyzer tutorial, on Office Online.

While you can't connect Data Analyzer directly to your databases, you can analyze cubes made from almost any data source. For instance, you can analyze cubes created with Microsoft Excel (see the article Creating data cubes with Excel and a database, on Office Online), or with Analysis Services, the OLAP server included with Microsoft SQL Server™. In addition, those cubes can be almost anywhere: on your computer, on your network, or on the Web.

ShowWhat will I learn in this article?

This article explains how to:

  • Create connections to data cubes and select the dimensions, measures, and colors for a view.
  • Change existing views.
  • Change existing connections.
  • Remove connections.

ShowConnecting to a cube and selecting dimensions, measures, and colors

The sets of steps in this section explain how to connect to a data cube and select the dimensions, measures, and colors for the resulting view. You follow this process whenever you want to connect to a cube, regardless of that cube's location.

Note   You can always find a cube by searching for its filename extension: .cub.

ShowTo connect to a cube

  1. Start Data Analyzer.
  2. In the Microsoft Data Analyzer Startup dialog box, click Create a new view, and then click OK.
  3. In the View — Introduction dialog box, click Next.
  4. In the View — Connections dialog box, click Add. The Connection Properties dialog box appears.

    The following illustration depicts the dialog box:
    Connection Properties dialog box

  5. In the Name box, type Practice Connection.

    At this point, you use the Connection Properties dialog box to connect to a local cube (a cube on your hard disk), a cube on your network, or a cube on the Web. The following table explains how to perform each task. Some of the steps require the name of an online analytical processing (OLAP) server and cube or the address of a cube on the Web. If you do not have that information, contact your database administrator or IT department before you proceed.

    To connect to a local cube To connect to a cube on a network To connect to a cube on the Web
    1. Click Local Cube, and then click the browse button to the right of the adjacent text box.
    2. Double-click the local cube.
    3. Click Connect.
    4. Select the desired catalog in the Catalog list, select the desired cube in the Cube list, and click OK.

      Note   A catalog is a collection of cubes. All catalogs contain at least one cube, and an OLAP database can contain multiple catalogs. If you don't know which catalog or cube to select, contact your IT department.

    5. In the View - Connections screen, click Next, and then follow the next set of steps to select the dimensions and measures for your view.
    1. Click Server and type the name of the server that contains the cube.
    2. Click Connect.
    3. In the Catalog list, select a catalog.
    4. In the Cube list, select a cube.

      Note   A catalog is a collection of cubes. All catalogs contain at least one cube, and an OLAP database can contain multiple catalogs. If you don't know which catalog or cube to select, contact your IT department.

    5. Click OK.
    6. In the View - Connections screen, click Next, and then follow the next set of steps to select the dimensions and measures for your view.
    1. Click Http and type the address of the server that contains the cube.
    2. Click Connect.
    3. In the Catalog list, select a catalog.
    4. In the Cube list, select a cube.

      Note   A catalog is a collection of cubes. All catalogs contain at least one cube, and an OLAP database can contain multiple catalogs. If you don't know which catalog or cube to select, contact your IT department.

    5. Click OK.
    6. In the View - Connections screen, click Next, and then follow the next set of steps to select the dimensions and measures for your view.

Note   If you use color in your view, make sure you use it to represent a percentage or ratio. For example, use color to represent trends, such as changes over time, or profitability. If you use color to represent quantities, all you're doing is repeating the information that appears in the dimension panes of your view.

ShowTo select dimensions, measures, and colors

  1. If you're using the local cube supplied with Data Analyzer (Airline.cub), in the View - Dimensions screen select all of the available dimensions, and then click Next. Otherwise, select the dimensions that you want to analyze and click Next.
  2. If you're using the local cube supplied with Data Analyzer, select Total Revenue in the Length list and select Profitability in the Color list. Otherwise, select a measure in the Length list that represents a quantity, and select a color in the Color list that represents a percentage or ratio such as profitability or a change over time.
  3. Click Finish.

Data Analyzer renders the view.

ShowChanging views

You can change the dimensions, measures, and colors in a view at any time.

  1. On the Data Analyzer toolbar, click the Change Views button Button image.
  2. On the Dimensions tab, add or remove any of the available dimensions.
  3. Click the Measures tab.
  4. In the Display Type list, select either Bars to display the default bar charts, or select Grid to display data grids.

    If you select Bars for an existing view, you must use the Display Type toolbar to view the grids.

  5. In the Length list, select an option that represents a quantity.

    Good choices include options that represent total amounts.

  6. In the Color list, select an option that represents a ratio or percentage.

    Good choices include profitability or changes during given time periods.

  7. Click Apply to apply your changes, and then click OK to return to the view.

ShowCreating or editing connections with the Open Using Connections command

You use the Open Using Connections command when you need to create or change a connection to an existing cube that you've associated with at least one view. For example, say your IT department creates a cube for you and places it on a test computer. After you create a view from that cube and verify that it contains the information you need, the IT department then moves the cube to a production computer. When that happens, you use the Open Using Connections command to change the connection the cube.

Keep one fact in mind: When you create connections, reserve each one for use with a single cube. You can point a connection for one cube to a different cube. However, if you do that, Data Analyzer cannot connect to the other cube. For example, say you create a connection called "sales" to a cube containing sales data. If you then point that connection to a cube containing inventory data, the connection breaks because it expects a certain type of data in a certain form, and the new cube cannot provide that data or form.

Important  You must create a view from a given cube before you can follow the steps in this section. For more information about creating views, follow the steps in the Connecting to a cube and selecting dimensions, measures, and colors section, above.

  1. On the File menu, click Open Using Connections.
  2. Double-click the .max file associated with the cube and connection that you want to change. The Connections List dialog box appears:

    Connections List dialog box
  3. At this point, you can add, edit, or remove a connection. The following sets of steps explain how to perform each task.

ShowTo add a connection

  • In the Connections List dialog box, click Add. The Connection Properties dialog box appears. At this point, you can use that dialog box to connect to a cube on your hard drive, a network, or the Web. The steps in this table explain how to perform each task.
    To connect to a local cube To connect to a cube on a network To connect to a cube on the Web
    1. In the Name box, type a name for the connection.
    2. Click Local Cube, and then click the browse button to the right of the adjacent text box.
    3. Double-click the desired .cub file.
    4. Click Connect.
    5. Select the desired catalog in the Catalog list, select the desired cube from the Cube list, and click OK.

      Note   A catalog is a collection of cubes. All catalogs contain at least one cube, and an OLAP database can contain multiple catalogs. If you don't know which catalog or cube to select, contact your IT department.
    1. In the Name box, type a name for the connection.
    2. Click Server and type the name of the server that contains the cube.
    3. Click Connect.
    4. In the Catalog list, select a catalog.
    5. In the Cube list, select a cube.

      Note   A catalog is a collection of cubes. All catalogs contain at least one cube, and an OLAP database can contain multiple catalogs. If you don't know which catalog or cube to select, contact your IT department.

    6. Click OK.
    1. In the Name box, type a name for the connection.
    2. Click Http and type the URL of the server that contains the cube.
    3. Click Connect.
    4. In the Catalog list, select a catalog.
    5. In the Cube list, select a cube.

      Note   A catalog is a collection of cubes. All catalogs contain at least one cube, and an OLAP database can contain multiple catalogs. If you don't know which catalog or cube to select, contact your IT department.

    6. Click OK.

ShowTo edit a connection

  • In the Connections List dialog box, click Edit and refer to the table in the previous section for information on changing your connection.

ShowTo remove a connection

  • In the Connections List dialog box, select the connection you want to delete and click Remove.

ShowRemoving connections

You remove connections to cubes for a variety of reasons. For instance, your IT department may move the cube to another location. For this exercise, you'll remove the practice connection you created earlier.

  1. On the File menu, click New, and then click Next.
  2. Click Practice Connection.
  3. Click Remove.
  4. Click Cancel to close the Define View - Connections dialog box.
See Also
advertisement
Printer-Friendly VersionPrinter-Friendly Version