Working with Classes and Objects

 < Day Day Up > 



We have already seen how objects can be re-sorted within a class in the Calendar Year Lookup example. Classes and objects can also be renamed, deleted, hidden, and modified. In manipulating an object, you can access these actions in a number of ways:

  • Use the Edit pull-down menu to clear, hide, or modify properties and formats.

  • Select the item and then right-click to choose from various actions on the pop-up menu.

  • Double-click the item to bring up the Edit Properties dialog box.

The modifications described in the remainder of this section will generally use the right-click approach.

Rename

Using the Test Fashion universe created in Chapter 6, you want to rename the class Calendar Year Lookup to Time.

  1. Select the class Calendar Year Lookup.

  2. Right-click and then choose Class Properties from the pop-up menu. The following screen shows the initial class definition:

    click to expand

  3. Once in the Edit Properties dialog box, replace the current name in the Name: box with Time.

  4. Click OK.

In the following screen, the Calendar Year Lookup class has been renamed Time, and class description information has been added. Classes as well as objects can contain meaningful help text in the Description box. Help text is an underutilized feature in most universes, even though it is fairly easy to extract with an ETL tool. Power users or subject matter experts can also provide description information via spreadsheet files and object strategies (see Chapter 6) that can be easily imported into the universe.

click to expand

Delete

To delete a class or object, you can use the same three selection methods described earlier or two shortcut keys:

Key

Menu Equivalent

Explanation

DEL

Clear

This deletes the class or object. The only way to retrieve it again is to use the undo button.

CTRL-X

Cut

This cuts the class or object and puts it in the MS Windows clipboard. To retrieve the object again, use paste or CTRL-V.

Let's imagine the worst-case scenario: you accidentally deleted a class. When you delete a class, all the objects in that class will also be deleted. Designer warns you of this with the following error message:

click to expand

With Clear or DEL, the contents are not placed in the MS Windows clipboard. Undo allows you to undo only the last action. As the Clear was several steps ago, the undo button no longer can help you. All is not lost. Recall from Chapter 5 that you are working on only a copy of the universe. There is still a universe, with all the old object definitions, stored in the repository. When you make a catastrophic mistake like this, you can reimport a copy of the universe from the repository, although you will lose all other changes you made during this Designer session.

Given that DEL doesn't add items to the Windows clipboard, I suggest you use Cut as often as possible so that you have a better recovery process in case you accidentally delete something important. Keep in mind, though, that if you delete an object and recover it with the Paste command, the object will be assigned a new object ID and will generate errors in any existing user reports that reference the deleted object.

Hide

Hidden classes and objects are items that appear to you as the designer in italics but that users do not see when creating queries against this universe. Because the items still exist in the universe, they do take up space and will increase the size of the universe. Some designers will hide items so that all columns within the data mart appear to the designer but not to the user. I don't like this use of Hide because of the impact on universe size. However, Hide is useful when you want to hide 'work in progress' or if you wish to remove an object but want a transition period to ensure that the removed object will not create problems for the users.

In the following example, I've added the table ARTICLE_COLOR_LOOKUP; Designer automatically added the corresponding class and objects. I don't want the users to see these items yet, as they are not in business terms, nor have I added my joins. To hide a class or object, select the item and then click the Hide/Show button. The Hide/Show button acts as a toggle. When I am ready for users to see this new class and its objects, I unhide or Show the items with the same button.

click to expand

Modify a Dimension

If you have used the Quick Design Wizard to create your universe, or if you have made the setting to automatically create new classes and objects when you add a table to the universe (Tools | Options | Database), then much of your work now will be to modify those objects. In the next example, you will modify the dimension object Month, in the renamed Time class.

Definition

  1. Select the object Month and double-click to bring up the Edit Properties dialog box. This dialog has three tabs: Definition, Properties, and Advanced. The Definition tab contains the object name, type, SQL, and description or help text.

    click to expand

  2. Change the object name from Month to Month Number.

  3. The object type relates to the field format in the source database as well as to how users will choose to manipulate the object's data in a report. Leave the Type as Number.

  4. In the Description box, enter help text that is meaningful for users. If users will use a field as part of a condition, I like to give them an indication of the format. Enter the following help text: Month Number is a numeric field that shows the month in which the sale or promotion occurred. For example, January is equal to 1.

  5. The Select statement is the SQL used to dynamically build the user's query. If you are creating a new object, it is best to use the arrow button to launch the SQL Editor to select tables and columns to ensure the SQL is accurate.

  6. The Where box is used to generate the WHERE clause within a SQL statement. It's important to remember, though, that BusinessObjects appends the WHERE clause to the entire query (assuming the particular object is used in the query). For example, if you have an object Current Year Sales with a WHERE clause of Year=2003 and a Last Year Sales where Year=2002 and a user selects both objects for one report, then no data will be returned; the two WHERE clauses are mutually exclusive. Therefore, this box is most often left blank or used for variable prompts discussed in Chapter 10.

Object Types vs. Database Field Format

As described in the preceding step 3, the object type and the data source field format do not necessarily have to be the same. In most cases, they are the same and should be. Table 8-3 summarizes the different types and their settings. There is only one instance in which I recommend changing the object type, and that is when your source system is a number field that you will never use as a measure. For example, product ID and customer number may be numeric fields in the source system, but you will rarely want to treat them as numbers (unless the coding has some meaningful sort or logic that you wish to manipulate). In this case, consider setting the object type to character.

Table 8-3: Object Types Correspond to Physical Database Field Formats

Object Type

Explanation

Number

Any measure objects must have the object type of Number. Key fields and date fields may be numeric fields physically but should not be number objects in the universe. When they are number objects in the universe, BusinessObjects allows you to perform calculations such as sum, multiply, and divide that would not make sense from a business perspective.

Date

Fields that are in date format in the data source should be set to date type in the universe.

Character

Fields that are character in the data source should be set to character as the object type. Numeric fields that are not measures and that will not benefit from calculations may also be set to character.

Long Text

Long text fields are generally used for comments and note fields. The length of the long text object returned in a query can be controlled and is specific to each universe; it is set under File | Parameters | Controls.

When you specify an object as a number (whether or not you specify it as a measure), certain functionality within BusinessObjects is available to end users. Users will be able to do sums, divisions, averages, and so forth, on these numbers. From a business point of view, these calculations make no sense against most ID fields. The only calculation that makes sense is a count: How many new products sold this month? How many customers do I have? The count calculation is available to all objects, regardless of their types (number, date, character). Personally, I'd like to see BusinessObjects automatically disable nonsensical functionality for dimension objects, but they are more likely to consider an enhancement request from you than from me!

Except for changing from numeric to character types, be very careful about changing your field types. For example, in the Test Fashion universe, from a business point of view, I would much prefer that Month Number be a date type object. This would allow me to use all the wonderful date functions within BusinessObjects formulas. However, it is a numeric field in the database, and if I specify it as a date type in the universe, I will receive an #ERROR in my report if I try to use any of the date functions. So I need to use multiple functions to get my Month Number to generate a month name:

=Month(ToDate(FormatNumber(<Month Number> ,"00") ,"mm"))
Caution 

If you change the object type to be different than the source data field type, formula functions within BusinessObjects will generate an error.

Clearly, having users do extra conversions for what should be a simple task is a poor universe design. To prevent users from having to do extra conversions, a business-oriented universe designer may try to help users by converting the number field to a date field (or number to character, and so on) in the objects' SQL select statement. This is a good practice in theory, but only if it does not affect the use of indexes. With most RDBMSs, if you change the appearance of a field, the index will not be used. Therefore, work closely with the DBA or analyze an explain plan anytime you use SQL functionality to convert field types. (See Chapter 10 for more on creating advanced objects.)

Caution 

If you specify a character database field as a number and attempt to use it as a measure, BusinessObjects will drop any rows that contain characters from your report.

Properties

The Properties tab of the edit Properties dialog box sets the object qualification, the aggregates if it's a measure, and a list of values.

click to expand

  1. The Month Number is a dimension, so leave this box checked.

  2. Change the list name of the associated list of values to MonthNN. List of values is explained in more detail in Chapter 9.

Advanced

The Advanced tab controls who can access the object, where the object can be used in a query, and date formats.

For the Month Number, leave all the settings at their defaults. Click OK to save the changes on the Definition and Properties tabs.

click to expand

On the Advanced tab, security access level settings interact with Supervisor settings discussed in Chapter 12. The Public setting allows all users to access an object and is the default.

For each object, you can control whether the object can be used as a result, a condition, or a sort as described in Table 8-4.

Table 8-4: Objects Can Be Used in Three Different Ways in a Query

Can Be Used As A

Explanation

Result

A result is a column in a query or a report. Most often, all objects are results.

Condition

Conditions relate to the WHERE clause of a SQL statement. If your universe accesses an OLTP, you may want to disallow nonindexed fields as conditions, as they may result in slow queries and bog down the source system. If you are in a data warehouse environment, I recommend allowing all objects as conditions. It's true that you may want users to search on the indexed CUSTOMER_CODE, for example, but what if there are several related customers that all start with the same first few letters, such as Deloitte & Touche? They could have the forms Deloitte Consulting, Deloitte& Touche Management Solutions, Deloitte Parsipanny office, And soon. By allowing Customer Name as a condition, a user could select everything starting with Deloitte.

Sort

Sort allows users to sort results on the server rather than on the client. As with conditions, I suggest allowing this on all objects. In most cases, users will sort their results within the report once they see the data. However, if users want to select Top10 product sales, or Top100 customers, the sort must be processed on the server.

Database Format applies only to date fields and will be dimmed for nondate fields. If you are an American, you may not realize that European countries write the date differently; if you are a European, I apologize that we are so ethnocentric! How do I know the different formats: I lived in Switzerland for eight years and am married to an English man. An American will refer to April 1 as 04/01. A European will write April 1 as 01/04. (You can imagine the confusion this causes when my husband and I attempt to share date-oriented passwords or book each other's dentist appointments!)

Note 

Concerned with response time issues, some DBAs may disagree with the recommendations in Table 8-4, but it provides the most user flexibility. You need to weigh the risk of users not being able to ask a valid business question with the impact on response time.

By default, BusinessObjects uses the date format defined in the database.sbo file to generate the correct SQL syntax when a date is used in the WHERE clause. You can override this format in the Database Format box. For example, the ODBC.SBO file has the following parameter:

InputDateFormat={\d 'yyyy-mm-dd'}

In the Island Resorts Marketing universe, TIME.RESERVATION_DATE is stored in MS Access as YYYY-MM-DD HH:MM:SS. When a user places the Reservation Date object in the condition panel, BusinessObjects will automatically take the user's input and convert it to this format. Because this format is different from how the physical date values are stored (the ImputDateFormat is missing HH:MM:SS), the users will get no rows returned.

To ensure users get the correct results, specify a date format in the Object properties that corresponds with the date in the physical database. In this example, {\t\s ‘yyyy-mm-dd HH:mm:ss'} is the correct Database Format. Note that ODBC requires the brackets and \t\s parameters; Oracle does not.

Now, if a user enters 1/12/96 as a condition value, BusinessObjects converts the SQL using the format specified in the Object Properties:

SELECT   Reservations.res_date FROM   Reservations WHERE   (   Reservations.res_date  =  {ts '1996-01-12 00:00:00'}   )

Oracle uses the following default settings in the Oracle.sbo file. If any of your date fields in your database do not follow this format, you will need to modify the object properties to override the settings in the Oracle.sbo file.

InputDateFormat='DD-MM-YYYY HH:MM:SS' InitDateFormat='DD-MM-YYYY HH24:MI:SS' AuditTimeStampFormat='DD-MM-YYYY HH:MM:SS'
Tip 

Review the database.sbo file to ensure it corresponds with the date values in your database.

Modify a Detail or Attribute

Detail objects work much the same way that dimension objects work. The only difference is that you must associate a detail object with a dimension object. In the following example, the detail object Street is associated with the dimension object Shop Name. Once you have associated Street with Shop Name, Street and other details or attributes will now appear in a separate folder under Shop Name.

click to expand

Insert a Measure

The previous section discussed modifying an existing object. You can also add new objects by clicking the Insert Object button or using the menu choice Insert | Object. In this section, you will add a measure object called Promotion Cost. This assumes that the table PRODUCT_PROMOTION_FACTS exists in your Universe pane.

  1. Position your mouse in the Test Fashion Measures class. Click the Insert Object button.

  2. Designer will present you with the same dialog boxes as when modifying objects. Enter the name Promotion Cost and set the object type to number.

  3. Enter the object description as: Promotion Cost is the dollar value spent on a given radio, newspaper or other media promotion. Promotion costs are unique for each individual product but are allocated across all stores.

  4. In the Select box, click the >> button to access the full select statement and SQL functions. The functions here relate to your specific database. Some popular functions are discussed further in Chapter 10.

  5. In the Functions column, click the + sign next to Number functions. Scroll to the Sum() function and double-click to add it to the select statement.

  6. Ensure your mouse is positioned in the middle of the Sum parentheses. In the Tables and Columns box, click the + sign next to the table PRODUCT_PROMOTION_FACTS to find the column PROMOTION_COST. Double-click to add this field so that the SQL appears as follows:

    sum(product_promotion_facts.promotion_cost)

    click to expand

  7. Click Parse to check that your SQL is correct.

    Note 

    You can wait to parse all objects when checking the universe integrity, but it is easier to diagnose problems if you parse objects as you build them.

  8. Click OK to save your Select statement and return to the Definition tab.

  9. Select the Properties tab and verify that the object qualification is a measure. The default qualification is Dimension, but this should have changed automatically when you selected the Sum function.

    click to expand

  10. Select the Sum function as the projection aggregate.

  11. There is no reason to have a list of values for measure objects, so leave the option Associate a List of Values unchecked.

  12. Click OK to save your object changes.

  13. Click Save to save the changes made to the universe.

  14. Select File | Export. From the Export Universe dialog box, click OK to export the modified universe to the repository.

About Projection Aggregates

Two forms of aggregates are involved in a BusinessObjects universe: SQL functions and projection aggregates. Users can create a third aggregate called calculations within individual reports.

SQL Aggregates

SQL aggregates such as SUM, MIN, and MAX require a GROUP BY clause that BusinessObjects automatically includes in each SQL statement.

Refer back to Figure 8-2. There are 23 rows of data in the sample fact table. If I select debits and credits without the SQL sum function, then my report will display 23 rows. If I use the SUM function and request only debit and credits by day, SQL will group the debits and credits for each day. For example, there are four detail entries for September 12, 2002. SQL sums these into one entry for September 12, as shown in Figure 8-4. With the sum function, Figure 8-4 shows ten rows of data compared to the physical 23 rows from Figure 8-2. Failure to use SQL aggregates correctly can unnecessarily cause millions of rows of data to be sent across the network.

click to expand
Figure 8-4: SQL groups individual rows into common columns, reducing the number of rows of data sent to the client.

In this example, the number of rows returned to the client workstation or to the WebI midtier has gone from 21 to 10 through use of a SQL aggregate function. In a real-world example, this could be the difference between returning a few rows or millions of rows of data to a client.

Tip 

Always use a SQL aggregate on a measure unless it involves a unit price or something similar; otherwise, you risk overloading your servers, network, and client PCs.

Projection Aggregate

The second form of aggregate is the projection aggregate, used in multidimensional analysis and when users remove dimension columns from a report or chart that still exist in the results set. In the following example, I only have a DATE column and not a MONTH column in the SQL statement; therefore, there are still ten rows in my result set. In Figure 8-5, I have created a month object in the report and removed the individual date. BusinessObjects now does the grouping in the report to yield one row of data for the entire month of September.

click to expand
Figure 8-5: A BusinessObjects projection aggregate groups records within the report.

As a general rule, the SQL function you use will match your projection aggregate used on the individual object properties. As discussed earlier in connection with measures, price is one measure in which designers may not use a SQL aggregate; however, it would be useful to set the projection aggregate to Avg to allow further analysis. With inventory and ending balance, I recommend using the SQL SUM function but then setting the projection aggregate to None.



 < 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