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...

  Creating Session Variables Use this task to create a session variable. To create a session variable From the Administration Tool menu bar, choose Manage > Variables. In the Variable Manager dialog box, from the menu bar, choose Action > New > Session > Variable. In the Session 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. For session variables, you can select the following check boxes: Check box that allows you to set the session variables after the initialization block has populated the value (at user login) by calling the ODBC store procedure NQSSetSessionValue(). For example, this allows non-Oracle BI Administrators to set this variable for sampling. Check box that identifies the variable as sensitive to security for virtual physical databases (VPD). When filtering cache table matches, the Oracle BI...

Oracle Business Intelligence Enterprise Edition (OBIEE) and Oracle Analytics Cloud (OAC) are both business intelligence (BI) platforms developed by Oracle. However, there are some key differences between the two platforms. Deployment OBIEE is an on-premises BI platform, while OAC is a cloud-based BI platform. This means that OBIEE must be installed and maintained on your organization's own servers, while OAC is hosted and managed by Oracle in the cloud. User experience OAC has a more modern and user-friendly interface than OBIEE. It also has a wider range of self-service features, making it easier for business users to create and share insights without relying on IT. Data visualization OAC has a wider range of built-in visualizations than OBIEE, as well as the ability to create custom visualizations. OAC also has more advanced data visualization capabilities, such as interactive charts and graphs. Data preparation OAC has a more powerful data preparation tool than OBIEE. This tool ...

Migrating to Oracle Analytics Cloud (OAC) offers a range of benefits for organizations seeking to enhance their data analytics capabilities and decision-making processes. These benefits include: 1. Reduced IT Costs and Infrastructure Burden: OAC's cloud-based infrastructure eliminates the need for organizations to maintain and manage on-premises hardware and software, reducing IT costs and freeing up resources for other strategic initiatives. 2. Enhanced Scalability and Agility: OAC's elastic cloud architecture enables organizations to scale their analytics capabilities seamlessly as their data volumes and user demands grow, ensuring they always have the resources they need. 3. Simplified Deployment and Maintenance: OAC's cloud-based deployment eliminates the complexities associated with installing, configuring, and maintaining on-premises BI solutions, reducing time to value and simplifying ongoing maintenance. 4. Improved Data Security and Governance: OAC ...

Power BI and Oracle Analytics Cloud (OAC) are both powerful business intelligence (BI) platforms that can help you visualize and analyze your data. However, there are some key differences between the two platforms that may make one a better fit for your organization than the other. Ease of use Power BI is generally considered to be easier to use than OAC. It has a more intuitive interface and a wider range of self-service features, making it a good choice for users who are not familiar with BI tools. OAC is more powerful and offers more customization options, but it can also be more complex to learn and use. Data visualization Power BI excels at data visualization. It has a wide range of built-in charts and graphs, as well as the ability to create custom visualizations. OAC also has a good selection of visualizations, but they are not as customizable as those in Power BI. Scalability Power BI is a cloud-based platform, so it can easily scale to meet the needs of your organization. OAC ...

Operators Operators are used to combine expression elements to make certain types of comparisons in an expression. Operator Function in an Expression         +  Plus sign for addition.  -  Minus sign for subtraction.  *  Multiply sign for multiplication.  /  Divide by sign for division.  ||  Character string concatenation.  (  Open parenthesis.  )  Closed parenthesis.  >  Greater than sign, indicating values higher than the comparison.  <  Less than sign, indicating values lower than the comparison.  =  Equal sign, indicating the same value.  <=  Less than or equal to sign, indicating values the same or lower than the comparison.  >=  Greater than or equal to sign, indicating values the same or higher than the comparison.  <>  Not equal to, indicating values higher or lower, but not the same. AND AND connective, indicatin...

  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 ...