Popular SQL Functions

 < Day Day Up > 



Take a deep breath. If the BusinessObjects internal functions have overwhelmed you, get a latte, a glass of wine (at home, of course!) or do some yoga: SQL functions are ever more powerful and limited only by your creativity, but potentially, they are more overwhelming (especially if you have a nasty habit like me of putting commas and parentheses in all the wrong places). In the universe design process, this is when the power users, universe Designers, and DBAs must partner together to build a technically correct but business-robust universe.

There are entire books dedicated to SQL commands alone (refer to the Bibliography), so it is impossible to cover all of the functions here. What follows are just a few examples of some popular objects that use SQL functions to deliver business functionality.

Concatenated

Concatenated objects combine information from multiple fields. A common usage is to combine a customer or employee's First Name and Last Name into a new object, Name. Depending on your database, you have two ways to concatenate fields:

  • The CONCAT function, which is database specific and allows you to combine two columns of data. One can nest CONCAT statements to combine multiple columns, but personally, I find the nested functions harder to read than an operator.

  • An operator, which allows you to combine several columns into one. Microsoft databases use +, and Oracle uses ||.

The following object uses the CONCAT function and uses BusinessObjects @Select to reference existing objects in the universe:

CONCAT(@Select(Hr Employees\First Name),@Select(Hr Employees\Last Name))

The SQL statement will appear as follows:

CONCAT(HR.EMPLOYEES.FIRST_NAME,HR.EMPLOYEES.LAST_NAME)

Using an operator has the following syntax and allows you to combine more than two fields plus spaces between each column:

( HR.EMPLOYEES.EMPLOYEE_ID ) ||' '|| ( HR.EMPLOYEES.FIRST_NAME )||' ' || ( HR.EMPLOYEES.LAST_NAME ) 

Time Objects

If you look at any quarterly or annual report, it contains current period and Year-To-Date information as shown from the most current Business Objects 10Q filing, as in the following earnings statement:

 

Three Months Ended
December 31

YTD (Ended December 31)

 

2002

2001

2002

2001

License fees

66,129

72,688

243,955

249,594

Services

60,054

44,123

210,844

166,200

     

Total revenues

$126,183

$116,811

$454,799

$415,794

To create this functionality in BusinessObjects requires two steps. First, you must determine what is the definition of to-date: Is it whichever accounting month the books have closed? Is it the calendar day of today? The answer to this may depend on whether you are viewing accounting information or sales order information. Accountants may want closed accounting months, whereas salespeople will want the latest date possible. The second step involves grouping the information into columns of data as shown in the preceding table. Grouping information into columns of data is described in the section 'If-Then-Else Logic with Decode and Case.'

If users want to run rolling reports, you can provide them with condition objects that let them select current time periods or a rolling period. If you are looking for a closed accounting month, the best practice is to store the closed accounting month as a flag in the time dimension table. If this is not available, create an interactive object that prompts for the closed accounting month.

click to expand

The prompt Closed Accounting Month can now be used as an @Variable in any Year- To-Date objects. For example, YTD Sales would include in the WHERE clause the following:

@Select(Time\Fiscal Month Number)<=@Variable('Closed Accounting Month')

If the interactive object Closed Accounting Month exists in the query or has been used during the report, then BusinessObjects will prompt the user and provide the list of values. If Closed Accounting Month is not in the current report, then when a user accesses YTD Sales, BusinessObjects will prompt the user for the Closed Accounting Month variable but does not know to link back to the interactive object to get the list of values. You can best resolve this quirk with user training and instructions in the Object Description.

So far, you have only asked the user to specify the month. You could include the accounting year in the prompt, or you could get the current year from the RDBMS's system date. Either users can include a Current Year condition in their query or you may have a compound WHERE clause in Current YTD Sales:

@Select(Time\Fiscal Month Number)<=@Variable('Closed Accounting Month') AND @Select(Time\Fiscal Year)=TO_NUMBER(TO_CHAR(SYSDATE,'YYYY') )

SYSDATE returns the system date on the RDBMS. It is a date field from which you want to extract just the year. TO_CHAR allows you to extract the four-digit year. However, your comparison object is numeric, so you must convert the character year to a numeric field using TO_NUMBER.

Caution 

Be aware that with this type of object, the WHERE clause gets appended to the entire query and users will not be able to have Last Year objects in the same report. Refer to the next section for an alternative.

To allow users to create a rolling three months, use the ADD_MONTHS SQL command to subtract three months from the SYSDATE. Note that in this example, you only need the month or MM from the TO_CHAR function.

click to expand

If-Then-Else Logic with Decode and Case

Oracle provides a SQL function DECODE that works like an If-Then-Else statement. The syntax is DECODE(@Select(Class\Object),'if_a','then_b'), where

  • @Select(Class\Object) can either be the object whose value you want to change or the RDBMS TABLE.COLUMN.

  • if_a is the value in the data column you wish to compare and replace.

  • then_b is the replacement value.

In the sample Oracle SH.CUSTOMER table, the CUST_GENDER column contains only two values: F for Female and M for Male. The gender description is not stored in a column. DECODE can help you create a description:

decode(SH.CUSTOMERS.CUST_GENDER, 'F', 'Female', 'M', 'Male')

Current Period

DECODE is also quite powerful for creating Current Period and Year-To-Date (YTD) objects without the problem of WHERE clauses becoming appended to the entire query. To understand how to build this kind of object, look at the sample data in Figure 10-6. This is taken from the sample Oracle Sales History (SH) tables. Unfortunately, the data only goes through the year 2000, so for your purposes, you will have to imagine it is September 30, 2000 or Quarter 3 2000. You want to create two objects, one that retrieves the current period sales = 66,772,321 and one that retrieves the year-to-date sales = 217,702,751.

click to expand
Figure 10-6: Quarterly sales report

To determine which quarter you are in, you again use the SYSDATE and TO_CHAR commands. This time you want the date to be in YYYYQ format. The sample SH.TIMES table has the Year and Quarter in two separate columns, so you will concatenate the two together to get the same comparison from your SYSDATE. You want to create a Current Quarter Sales object that says: If the current calendar quarter matches the accounting quarter, then, for the same period in the database, show the sales; else, return null.

The corresponding SQL is

sum(decode(to_char(sysdate,'YYYYQ'), '20003',DECODE(SH.TIMES.CALENDAR_YEAR||SH.TIMES.CALENDAR_QUARTER_NUMBER,'20003',SH .SALES.AMOUNT_SOLD))) 

When using DECODE in this way, it is important that the aggregate SUM function goes around the entire statement and is not nested between the DECODEs. The following table decomposes the DECODE portion of the SQL statement:

Part

Purpose

SQL Syntax

1

If the current calendar quarter

decode(to_char(sysdate,
'YYYYQ')

2

Matches the accounting quarter

'20003'

3

Then for the same period in the database

DECODE(SH.TIMES.CALENDAR_YEAR||SH.TIMES.CALENDAR_
QUARTER_NUMBER,'20003'

4

Show the sales

SH.SALES.AMOUNT_SOLD

5

Else null

 

Cumulative To-Date

There are a number of ways to create a Current Year-To-Date Sales object. While the comparison part of your SQL (parts 1-2) would remain the same, retrieving the values for a cumulative period in part 3 will be a bit more of a challenge. You can have three sums such as Q1+Q2+Q3, but if you ever try to create a Current Week-To-Date or Current Month-To-Date object, your SQL gets long and messy fast. What you really want is a way to retrieve a range of values or to test if the RDBMS accounting quarter is less than or equal to the SYSDATE quarter. With DECODE, one can do this in a complicated way using the SIGN function, or in Oracle 8i and 9i, the CASE command is an improved version of DECODE that allows comparisons.

The syntax for a simple CASE expression in Oracle 9i is CASE @Select(Class\ Object) when 'a' then 'b' else 'c' end. The else is optional but recommended. To take the gender example from earlier, the CASE equivalent is

CASE SH.CUSTOMERS.CUST_GENDER        when 'F' then 'Female'       when 'M' then 'Male'       else 'Not Listed'       End

The syntax for CASE in Oracle 8i is slightly different in that the comparison object or column must be repeated after each WHEN:

CASE       When @Select(Class\Object)='A' then 'a'       When @Select(Class\Object)='B' then 'b'       When @Select(Class\Object)='C' then 'c'       Else 'X'       END

The gender example would be as follows:

CASE when SH.CUSTOMERS.CUST_GENDER= 'F' then 'Female' when SH.CUSTOMERS.CUST_GENDER= 'M' then 'Male' Else 'Not Listed' END

Oracle 9i supports both syntaxes. The main difference is that the first simple CASE may be more efficient for simple translations (like DECODE), but the latter syntax provides more flexibility.

The Current Quarter Sales object using CASE instead of DECODE is as follows:

sum(       CASE       When SH.TIMES.CALENDAR_YEAR||SH.TIMES.CALENDAR_QUARTER_NUMBER       =       to_char(sysdate,'YYYYQ' )       Then SH.SALES.AMOUNT_SOLD Else 0       End ) 

To create the Year-To-Date object, replace = with <= to get a cumulative total. The following SQL also contains a nested CASE statement to ensure that you add quarters from the same year:

sum(       CASE       When SH.TIMES.CALENDAR_YEAR=to_char(sysdate,'YYYY')       Then (CASE When             SH.TIMES.CALENDAR_YEAR||SH.TIME.CALENDAR_QUARTER_NUMBER             <=             to_char(sysdate,'YYYYQ' )             Then SH.SALES.AMOUNT_SOLD             End)       Else 0       End ) 

You can combine these automatic time period objects to create variances that compare sales trends between the two years or two quarters. The following report uses several time period objects to determine whether sales were 21 percent higher for the first three quarters of the year:

click to expand

One thing to be aware of with the Current Year and YTD objects is that users do not have to enter a Year as a condition. Everything is automatic. This can be great for users and for standard report maintenance, but it can be bad for the RDBMS if the queries result in a full table scan; the database will not use an index from any of the TIMES columns. If users will always select some other condition criteria such as Product or Region, then these indexes may be used to process the query. As a workaround, you may want to include a WHERE clause in the automatic objects that includes enough years for the results to be accurate, but also, for an index to be used. For example, if you have automatic objects for Current Year, Last Year, and 2 Years Ago, then include the following as a WHERE clause for each object:

@Select(Time\Year) IN ('2002','2001','2000')

To avoid having to update this each year, in theory one may be able to use <= SYSDATE-(365*3); however, this assumes that the base comparison year is also a date field. In the preceding example, it is not, in which case, one would need to convert the SYSDATE calculation using TO_CHAR. Once you add this kind of function to the SQL statement, the index is not used (unless the DBA creates a special function index).

As of this writing, CASE does not exist in the ora7EN.prm file. You can manually enter CASE statements in the SQL Editor, or to include some help, you may want to add the following entry:

NAME= If Then Else With Case TRAD= String HELP= Compares and replaces strings TYPE=A IN_MACRO=N GROUP=N SQL=CASE table.column when 'a' then 'b' else 'c' end 

Count

In Chapter 8, you looked at using the SQL COUNT function in measure objects to count the number of products or the number of customers. COUNT can get a little more complex than this, as 1) what you want to count is not always obvious, and 2) COUNT may give unexpected results.

What to Count?

The business user says, 'How many products do we have?' Easy, just count the unique product_ids in the dimension table! The following screen shows why this may not always be what the business user expects:

click to expand

A unique product_id is created for each combination of a product description, supplier, price, packaging, and availability. Note that the only difference between product_ids 195 and 200 is the packaging: brown envelope versus heavy-duty box. The business definition for number of products may in fact be according to the product ID, or it may be by product name. The SQL is different for each:

COUNT(PRODUCT_ID) COUNT(DISTINCT PRODUCT_NAME) 

Distinct Count

The COUNT function actually counts the number of rows returned; it does not count individual occurrences. In the preceding example, if you used COUNT(PRODUCT_ NAME), you would get the same result as with COUNT(PRODUCT_ID). If you want "Potpourri Skirt" to count as one product regardless of the number of times it occurs in the database, you must use COUNT(DISTINCT TABLE.COLUMN).

Recall from Chapter 8 that a dimension object should always come from the dimension or lookup table and not the fact table. With COUNT, this becomes even more important to guarantee correct results. There is a significant difference between counting the number of products versus the number of products sold in a particular period. The former must come from the dimension table; the later must come from the fact table. The following report shows how each count yields different results:

click to expand

Here is the SQL used to generate each column of data:

Object

SQL

Number of Products

COUNT(DISTINCT PRODUCTS.PROD_ID)

Number of Product Names

COUNT(DISTINCT PRODUCTS.PROD_NAME)

Number of Products Fact

COUNT(SH.SALES.PROD_ID)

Number of Products Fact Distinct

COUNT(DISTINCT SALES.PROD_ID)

For the Boys' product category, there are 2,428 unique product IDs or 85 different product names. These are both valid numbers. Users may want to see only one or both. If they want to see both, then the object name and the corresponding description must clearly convey what is being counted. Both columns come from the dimension table. The first object, Number of Products, includes the DISTINCT keyword, but only as a precaution; it is not strictly required. If the field you are counting is a unique ID or key field for the entire table, it is not required. Be careful about assuming that ID fields are always unique; some IDs may have an active/inactive flag or timestamp to indicate the latest record.

The next column in the report, Number of Products Fact, is misleading and meaningless. There are 227,102 occurrences of a PRODUCT_ID in the SALES fact table. When DISTINCT is used, there are 1,091 distinct occurrences of a PRODUCT_ID in the SALES fact table. This number has a business meaning in that 1,091 unique Boys' products were sold. Users may want to see one or all three types of counts.

Note 

Always use COUNT (DISTINCT TABLE.COLUMN) when counting items in a fact table. Otherwise, only use COUNT against columns with unique IDs or keys.

Rank

The RANK function is one of the new analytic functions available in Oracle 8.1.6 or later and DB2 version 7.1 or later. At the time of this writing, a corresponding RANK function was not available in SQL Server. The RANK function allows you to rank a dimension (customer, product, salesperson) according to any metric (sales, profit, commission, and so on). The ranked dimension is determined by the user as result objects in the query. You, as the designer, specify the metric for the ranking. The basic syntax of RANK is

RANK() OVER(PARTITION BY DIMENSION_TABLE.COLUMN ORDER BY AGG(FACT_TABLE.COLUMN)  DESC

where PARTITION BY DIMENSION_TABLE.COLUMN is optional and is used to rank items within a subset of data.

AGG(FACT_TABLE.COLUMN) is the aggregated measure that forms the ranking. For example, products with the highest sales appear as SUM(SALES.REVENUE). Customers with the highest average order price would use AVG(SALES.ORDER_ AMOUNT).

DESC is the sort order for the rank, either DESC for descending or ASC for ascending.

The next report shows how an object built with RANK appears to users. The Sales Amount column is not required in the report but is included to show how RANK works.

click to expand

The SQL for the Sales Rank object is

RANK() OVER (ORDER BY sum(SH.SALES.AMOUNT_SOLD) DESC)

Note that this object did not use the PARTITION BY parameter. So in the preceding report, products are ranked regardless of the product category. If you want to see the rankings within a product category, the SQL would be as follows:

RANK() OVER (PARTITION BY SH.PRODUCTS.PROD_CATEGORY ORDER BY sum(SH.SALES.AMOUNT_SOLD) DESC)

This generates the report shown next. Notice that the overall rank without the partition is different than the ranking within the product category. Within Men's products, Ukko Track High is the number one selling product but is the number-three selling product overall.

click to expand

Note 

When you use the PARTITION BY option, if your SQL settings are set to generate multiple SQL statements for each measure (File | Parameters | SQL), you will get two SELECT statements from the preceding report because both a dimension table and a fact table are involved in the SQL statement. Under normal circumstances, BusinessObjects generates multiple SELECT statements only when the measures come from two different fact tables.



 < 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