Chapter 20: Accessing New Data

 < Day Day Up > 



The previous chapters have shown that you can do quite a lot with a report without ever having to requery the data source. If you want to work with more current data, however, you need to refresh a query in which you retrieve a new result set from the data source. Because the size of documents in the BusinessObjects repository can become quite large, some companies use Corporate Documents to store empty standard reports that you must immediately refresh. This chapter reviews different ways to refresh a document and how to handle prompts that may appear when you execute a query.

Refreshing a Document

As discussed in Chapter 16, a document contains multiple components that deliver the formatted reports and analysis. When you want to retrieve new data from a data source, you are sending a query to a database, as shown in Figure 20-1. The data source may be a relational database such as the transaction system, an enterprise resource planning (ERP) system, or a data mart or data warehouse. When it is a relational data source, a SQL statement from your PC gets submitted to the source database. Therefore, you must have all database connectivity files installed on your PC. At this point, you do not go through the repository at all (unless you are using BusinessObjects in three-tier/ ZABO mode). The data source could also be a MOLAP cube such as Essbase or Microsoft Analysis Services. Local spreadsheets and departmental databases also are valid data sources.

click to expand
Figure 20-1: Refreshing a query sends SQL to the data source.

To refresh a query, select the Refresh button from the standard toolbar or select Data | Refresh Data.

One document may have one or more data sources. Depending on how the universe designer established the connection settings for each data source, your BusinessObjects login ID may automatically be used to log you into each data source. Therefore, if you change your password for one system, you may inadvertently cause them to get out of synch.

In Figure 20-1, you may have one password for the data mart and a different ID/ password for the ERP data source. This is clearly not ideal and is very user unfriendly. Preferably, the universe and BusinessObjects login parameters should be designed in such a way that the different systems work seamlessly together. Unfortunately, that isn’t always possible, and when the two become out of synch, you will receive an error message similar to the following:

click to expand

If your company requires a different username for each data source, you also may have multiple BusinessObjects logins. If you have different passwords, you should try to synchronize the passwords. To change your BusinessObjects password to match the data source password, select Tools | Change Password.

Query Phases

When BusinessObjects refreshes a query, it refreshes the entire result set. For example, let’s assume you have a document that shows year-to-date sales. The data source is from a data warehouse updated on a daily basis. A report author originally ran the query last week and sent you the results. Your version of the report is therefore out of date by a week. You refresh the query. This rebuilds the entire microcube (refer back to Figure 16-4); the microcube does not incrementally add one week of data. For smaller queries, this is not important, as the results may be returned in a few seconds. Other queries, however, may take quite a long time to run. Ideally, the report author warns you of this in the File | Properties, Comments box. For exceedingly long queries, the universe designer may also elect to have the database warn you of long-running queries based on estimates from previously run queries. These estimates can be wildly inaccurate and depend on a number of factors; they are useful, though, to tell you whether a query will be instantaneous or long. If the estimated time is acceptable to you, click Yes; if it is too long and you want to submit it to Broadcast Agent to run later, click No. The time estimate will remain displayed during the query refresh.

click to expand

Note 

Although you are able to work with other MS Windows products while a slow query is executing, you cannot continue to work with BusinessObjects.

The status bar in the Report Window displays when a data provider was last refreshed and also displays the query’s progress. For slow-running queries, it’s useful to monitor the different phases.

click to expand

  • Connecting Logs you into the data sources. The universe designer determines if you log in for only the first query refresh and log out when the query is finished, or if you stay logged into the data source for the entire BusinessObjects session. For extremely busy databases, repeatedly connecting and disconnecting for each query can slow down query refreshes and add unnecessary delays.

  • Analyzing When the database reads the SQL statement and performs the sorts and aggregations. If the query is poorly designed or the database is not well-tuned, the Analyze phase can take hours. During the Analyze phase, you generally cannot cancel the query (although the status bar displays a message “Press Esc to cancel”). The only way to interrupt the query at this stage is to contact the DBA. You also can  terminate the BusinessObjects session, by starting the MS Windows Task Manager with CTRL-ALT-DELETE, selecting the BusinessObjects process, and choosing End Task. You will lose any unsaved work and the query will still be executing on the data source database, so this is not something you want to do casually.

  • Fetching When the database is finished processing the query and begins sending the rows of data from the database across the network, as shown in Figure 20-1. The status bar displays the rows and time counters once the Fetch phase begins. At this point, you can press ESC to cancel the query (if the designer has enabled this parameter for this data source connection).

When you choose to cancel a query, you have several choices of what to do with the results sent thus far:

click to expand

  • Continue the execution Continues the Fetch phase of the query. Remember that pressing ESC during the Analyze phase does not interrupt the query. So if you have a slow-running query and you have gotten this far, I recommend continuing the execution. If, however, you suspect you have constructed an incorrect query with the wrong condition statement and the query is returning too many rows of data, stop the execution.

  • Stop the execution and keep the partial results As shown in the earlier status bar, if you interrupt a query and keep the partial results, the status bar warns you the result set is incomplete. If this is the first time you are running the query, seeing the partial results may be useful in diagnosing query problems.

  • Discard the results With this option, you stop the Fetch phase, the microcube is purged, and the report displays only the column headings.

  • Keep the results of the previous execution If you opened a document that was previously refreshed successfully, this option stops the Fetch phase and reverts to the previous result set. Even though you are keeping an old result set, the status bar shows a new Last Execution time.

Time Limit Interruptions

If the universe designer has set time limits for query execution time, you also may receive only partial results of the query. There are two issues to understand with time limits. First, the time limit takes over only during the Fetch phase. So if the universe contains a query execution time limit of 60 minutes, and the Analyze phase takes 90 minutes, the time limit in the universe controls will interrupt only after 90 minutes, after the Analyze phase has completed. If the Analyze phase takes 55 minutes and the Fetch phase takes ten minutes, you will receive approximately half the result set (or five minutes worth of Fetch). Second, when a time limit interrupts a query before any rows have been returned, you may receive an erroneous error message, “No Data To Fetch.” This is somewhat misleading, as you receive the same error message when you construct an incorrect query, with mutually exclusive conditions.

Refreshing and Regenerating SQL

When you refresh a query from the Report window, BusinessObjects uses the last version of the SQL statement. If the universe has been modified since you last refreshed the query, you may need to regenerate the SQL. This particular nuance is important only on rare occasions, such as when an object is incorrectly defined in the universe or the designer has modified objects to point to new table names. For example, let’s assume that the universe contained an incorrect calculation for the selling price. The SQL for this should be SUM(revenue) / SUM(quantity), but the object was initially defined as SUM(revenue/quantity). The universe designer corrects the object definition.

Even though you have the latest version of the universe via your local disk or work group directory, the SQL file on your PC still reads SUM(revenue/quantity). Only when you build a new query or run a query from the Query Panel does BusinessObjects read the new object definition and regenerate the SQL with SUM(revenue) / SUM(quantity).

To regenerate the SQL and run a query from the Query Panel:

  1. Select Data | Edit Data Provider or click the Edit Data Provider button on the standard toolbar to launch the Query Panel.

  2. Click Run.

BusinessObjects generates a new SELECT statement and refreshes the results.



 < Day Day Up > 



Business Objects(c) The Complete Reference
Cisco Field Manual: Catalyst Switch Configuration
ISBN: 72262656
EAN: 2147483647
Year: 2005
Pages: 206

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net