Datawarehouse Architect

Week_Of_Year Returns a number (between 1 and 53) corresponding to the week of the year for the specified date.   Syntax Week_Of_Year(dateExpr) Where: dateExpr Any expression that evaluates to a date.

  Bit_Length Returns the length, in bits, of a specified string. Each Unicode character is 2 bytes in length, which is equal to 16 bits.   Syntax Bit_Length(strExpr) Where: strExpr Any expression that evaluates to a character string.   Practical Usage in Answers Using the Paint Business Area SELECT Markets.Region saw_0, bit_length(Markets.Region) saw_1, bit_length('C') saw_2, bit_length('a') saw_3 FROM Paint ORDER BY saw_0, saw_1, saw_2, saw_3  

Percentile Calculates a percentile rank for each value satisfying the numeric expression argument. The percentile rank ranges are between 0 (0th percentile) to 1 (100th percentile). The percentile is calculated based on the values in the result set.   Syntax Percentile(Expr) Where: Expr Any expression that evaluates to a numerical value.

Upper Converts a character string to uppercase.   Syntax Upper(strExpr) Where: strExpr Any expression that evaluates to a character string.

Rank Calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,...). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...). The rank is calculated based on the values in the result set.   Syntax Rank(Expr) Where: Expr Any expression that evaluates to a numerical value.

Count Calculates the number of rows having a nonnull value for the expression. The expression is typically a column name, in which case the number of rows with nonnull values for that column is returned. Syntax Count(numExpr) Where: numExpr Any expression that evaluates to a numerical value.

Current_Time Returns the current time. The time is determined by the system in which the Oracle BI Server is running.   Note:  The Analytics Server does not cache queries that contain this function. Syntax Current_Time(integer) Where: integer Any integer that represents the number of digits of precision with which to display the fractional second.

Sum Calculates the sum obtained by adding up all values satisfying the numeric expression argument.   Syntax Sum(numExpr) Where: numExpr Any expression that evaluates to a numerical value.

  TopN Ranks the highest n values of the expression argument from 1 to n, 1 corresponding to the highest numerical value. The TOPN function operates on the values returned in the result set. A request can contain only one TOPN expression.   Syntax TopN(Expr, integer) Where: Expr Any expression that evaluates to a numerical value. integer Any positive integer. Represents the bottom number of rankings displayed in the result set, 1 being the highest rank.

  Using Nonsystem Session Variables The procedure for defining nonsystem session variables is the same as for system session variables. A common use for nonsystem session variables is setting user filters. For example, you could define a nonsystem variable called SalesRegion that would be initialized to the name of the user's sales region. You could then set a security filter for all members of a group that would allow them to view only data pertinent to their region. NOTE:   When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable SalesRegion set the filter to the Variable NQ_SESSION.Sales Region .

  Using System Session Variables   System session variables are session variables that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes. System session variables have reserved names, that cannot be used for other kinds of variables (such as static or dynamic repository variables and nonsystem session variables).   NOTE:   When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable LOGLEVEL set the filter to the Variable NQ_SESSION.LOG LEVEL. Table 34  describes the available system session variables.

To create a repository variable From the Administration Tool menu bar, choose Manage > Variables. In the Variable Manager dialog box, from the menu bar, choose Action > New > Repository > Variable. In the Variable dialog box, type a Variable name. Names for all variables should be unique. The names of system session variables are reserved and cannot be used for other types of variables. In the Variables dialog box, select the type of variable: Static or Dynamic. The name of the dialog box changes to reflect the type of variable that you select. (Dynamic repository variables) Use the Initialization Block drop-down list to select an existing initialization block that will be used to refresh the value on a continuing basis. (Dynamic or static variables) To add a Default initializer value, perform one of the following steps: To use the Expression Builder, click the ellipsis button to the right of the Default initializer work space. Type the value in...

  Char Converts a numerical value between 0 and 255 to the character value corresponding to the ASCII code.   Syntax Char(numExpr) Where: numExpr Any expression that evaluates to a numerical value between 0 and 255.   Practical Usage in Answers Using the Paint Business Area SELECT Char(40) saw_0, Char(45) saw_1, Char(55) saw_2, Char(62) saw_3, Char(64) saw_4, Char(65) saw_5, Char(66) saw_6, Char(67) saw_7, Markets.Region saw_8 FROM Paint WHERE Markets.Region = 'CENTRAL REGION'

 Formatting Columns and Changing Column Order 1. Additional formatting can be done column by column, by selecting the format icon (looks like a hand) within any column. Doing so opens the column format dialogue box. 2. The column order can be changed by dragging and dropping the column, or the parent heading (if they are being displayed). When dragging, a dark blue bar indicates where the column or group can be dropped. 3. Investigate the options available if you select the “Column Properties” icon (looks like a hand). Don’t make any changes yet! 4. Practice dragging and dropping the columns to re-order the column order. Notice that you can re-order columns on both the “Criteria” tab and the “Results” tab (as long as you are in the table editor, not the compound layout). 5. Make sure the column order is Region, Brand, Units, and Dollars before continuing.   Filtering the Request   1.Requests can easily be filtered by selecting the filter icon (looks like a funnel) on ...

Step #1: Accessing a Subject Area 1.Subject Areas are collections of metrics and attributes that can be reported together in a meaningful way.   2.Completed requests can be accessed either through a directory structure..   3 ... or through any Dashboard page, in which that request has been “dropped.” (Requests can be placed in an unlimited number of Dashboard pages.)   1. Select the link “Answers” at the top of your web page. 2. Select the subject area “Paint” on the right side of the Answers page. When you need to find an existing request but you don’t where the request has been stored in the directory structure, you can track it down through the Dashboard tab in Answers.   Step #2: Selecting Columns   1.Once a subject area is selected, a “Pick List” appears on the left of the screen, organizing metrics and attributes into folders. 2.When clicked, the selected metric or attribute appears in the column list... 3...and any selected filte...