Sunday, January 31, 2016

Configuring OBIEE 12c Analytics to use Port 80

I installed OBIEE 12c on Windows 2008 server. During the installation, there is no option to specify which ports to use (remember the customports.ini file in 11g? No such luck in 12c as of Feb 28, 2016.)

The default analytics port is 9502, such as http://myserver:9702/analytics. I want to change that port to 80, so that we can just access http://myserver/analytics. Here's what worked for me:
  • Use the search tool of your choice to find all files under your BI home directory (a) have an .xml extension; and, (b) contain the string 9502.  
  • Search each file for the string '9502' to determine if it refers to a port number.  If it does, change it to port 80. 
In my system, I found these relevant files that needed to be changed:

Under c:\oracle\middleware\user_projects\domains\bi\config\
  • Config.xml
  • Backup_config2.xml
  • Backup_config3.xml
  • Backup_config4.xml
 Under C:\oracle\middleware\user_projects\domains\bi\config\fmwconfig\

  • Wsm-ccw-config.xml
    • After making the changes from 9502 to 80, sign in to Weblogic Server
    • Lock & Edit 
    • Environment > Servers > bi_server1
    • Configuration tab
    • Change the Listen Port: 80 (formerly 9502)
    • Save
    • Activate Changes
    • Test connecting to the repository in online mode with the Administration tool
    • Test connecting to OBIEE Analytics without the :9502 port
    All servers, services, and processes should be up and running.  I previously had included instructions to restart the bi_server1 server, but the most recent time I made this change (on a different system) I found that it was not necessary.

    Monday, May 26, 2014

    OBIEE Repository Sequence Numbers

    Blog Index

    We know that every time we activate a different repository in Fusion Middleware a new sequence number is appended to the repository (.rpd) file name.  Mark Rittman wrote an excellent blog entry that takes us about 95% of the way down the path to understanding the process.  This blog entry completes the journey.

    The Rittman blog is located at:

    http://www.rittmanmead.com/2011/03/so-how-does-enterprise-manager-work-within-obiee-11g/

    In his blog, Mark introduces us to the System MBean Browser, accessible via Fusion Middleware as shown here:

    Mark takes us through the why’s and wherefore’s of the entire path to the repository sequencer, and I won’t cover it here.  Instead, I’ll provide the Readers Digest version and skip to the answer in the back of the book to answer the question “Where is the sequence number stored?”

       1.        After accessing the System MBean Browser as shown above, navigate to this path:

    Application Defined MBeans + oracle.biee.local + Server: bi_server1 + DomainConfigProxy + DomainConfigProxy

     

    The notation in the Description column points us toward a file called biee-data-zip.  The file is actually biee-data.zip, located at

    {mw home}\user_projects\domains\bifoundation_domain\config\fmwconfig \biee-data.zip

       2.        Open the biee-data.zip archive file.

       3.        Drill into the folder that is traveling incognito.

       4.        Open the info.properties file with a text editor.

       5.        Here we see the next repository version number.

       6.        For a final surprise, drill into the coreapplication folder.

       7.        In this folder are two files. 

       8.        The RPD file is a backup copy of the currently active repository as it existed at the time you clicked Activate Changes.

       9.        The info.properties file contains the history of all of the repositories that have been activated.

    #Mon May 26 09:47:48 CDT 2014

    file.added.time[Retro_Prod_BI0002.rpd]=1367545948718

    file.added.time[MavProd1_BI0003.rpd]=1401115199014

    file.added.time[MavProd2_BI0004.rpd]=1401115668675

    file.added.time[SampleAppLite_BI0001.rpd]=1366982612863

     

    The large numbers at the end of each line are the number of milliseconds elapsed since the beginning of January 1, 1970.

     

    Sunday, January 5, 2014

    Pretty Radio Buttons with CSS in APEX



    Blog Index



    I occasionally work a bit in Oracle's Application Express (APEX).  I recently wanted to modify the display of a set of standard APEX radio buttons.

    By default, APEX Radio Buttons look like this when arranged horizontally:

     


    How can we modify the Radio Group object to look like this?

     

    The answer rests in the use of CSS classes in APEX.

    To begin, we will create a bit of CSS on the page.  If desired, we could then move it to the page template, or even upload it as a cascading style sheet into the APEX shared components for repeated use with any application.


    1.
    Create a Radio Group object with these characteristics:

    Display Orientation
    Horizontal
    Number of Radio Columns
    99
    List of values definition
    Select style as display_value, style as return_value from
    (Select 'Car' as style, 1 as mysort from dual union
    Select 'Pickup' as style, 2 as mysort from dual union
    Select 'MiniVan' as style, 3 as mysort from dual union
    Select 'SUV' as style, 4 as mysort from dual)
    order by mysort
    Default value
    Car



    2.
    Run the page to view the very mundane results.


     

    3.
    Edit the page, and click on any of the links in the Page zone (such as HTML Header).


    4.
    From the toolbar at the top of the screen click the CSS tab.



    5.
    Add this code into the Inline area and click Apply Changes.

    .rg {
      background: #FFFF00;
    }

    .rg label {
      background: #cccccc;
      color: green;
      font-weight: bold
    }

    When the rg class is assigned to an object, this will place a yellow background field behind all radio buttons.  The button labels will be displayed with green text on a grey background.


    6.
    Edit the radio group object created earlier.


    7.
    In the Element section, for the HTML Form Element CSS Classes field, type rg (without the leading period), and click Apply Changes.


    8.
    Run the page to see the results:


    Of course, this is a rather obnoxious combination of colors that we wouldn’t really use, but we're just experimenting with object definitions and positioning right now.

    What happens if we create and use other CSS classes with conflicting characteristics? 


    9.
    Return to the CSS for the page and add this new rg-gray class in the Inline area, after the rg class.

    .rg-gray {
      background: #0000FF;
    }

    .rg-gray label {
      color: red;
    }


    10.
    Return to the radio group and add rg-gray to the HTML Form Element CSS Classes field, with a space between the two CSS class names.



    11.
    Run the page and view the results:


    Notice that the background color behind the labels is still a light gray. Characteristcs specified in multiple CSS classes are cumulative.  A style feature will be retained unless it is specifically changed by a later CSS class reference. 

    Important Note: The order of the CSS classes in the HTML Form Element CSS Classes field (i.e. rg-gray  rg instead of rg  rg-gray) is irrelevant.  It is the order of the CSS classes in the CSS code that controls which features take precedence over others.  If the rg class was listed after the rg-gray class in the CSS code, the features specified in the rg class would override any of the same features specified in the rg-gray class.  "Last one in wins."

    This feature allows us to set up a default CSS class stored in the Shared Components, a second CSS class in the Page Template, and yet a third CSS class in the page's inline CSS as we did here, and go from more general to more specific in the implementation of CSS classes for our objects.  The CSS in the shared components is evaluated first, followed by the CSS in the page template, followed by the CSS on the page itself.

    Now that we've learned a bit about CSS classes, and have seen a couple of the basic styles that can be included, let's remove these garish colors and create something pretty.


    12.
    Return to the CSS and remove all of the class information added earlier.


    13.
    Add this new CSS class family named rgbtn in the CSS code:

    .rgbtn {
    }

    .rgbtn label {
      font-family:Arial;
      font-weight: bold;
      font-size:12px;
      background: #CCCCCC;   /* Light gray background */
      color: #000000;   /* Black font color */
    }

    .rgbtn input[type="radio"]:checked + label {
      background: #222222; /* Dark gray background */
      color:#F9F9F9;  /* Nearly white font */
    }


    14.
    Edit the radio group, remove any values in the HTML Form Element CSS Classes field, and instead refer only to the rgbtn CSS class.


    15.
    Run the page and view the results.



    16.
    Let's make the field names look like buttons by rounding the corners, and also provide a bit of padding around the text.  Add these two CSS style caracteristics to the CSS code in the .rgbtn label class, then view the results:

    padding:3px 10px;   /* 3 pixels top and bottom, 10 pixels left and right */
    border-radius:3px;


    In the sample shown at the beginning of this chapter, we saw that the circles had been removed, leaving just buttons.  Well guess what?  It is very difficult to remove those circles from a radio button.  But luckily, we don't have to remove them at all.  Instead, we can simply slide the labels a few pixels to the left and hide those circles behind their labels!

    17.
    Add this CSS code in the .rgbtn label class, then view the results:

    position: relative
    left: -21px;

     

    There is a bit too much empty space (25px) between the buttons.  Let's slide them a bit closer together.


    18.
    Add this CSS code in the .rgbtn label class, then view the results:

    margin-right: -18px;  /* Remove 18 pixels of space between each button */

     

    This concludes our "generic" version of a radio group turned into buttons.  Any radio group that refers to the rgbtn CSS class will be displayed with these characteristics.

    Now let's create a subsequent CSS class that will display these "buttons" with a specific gradient background.


    19.
    Create this new CSS class family (rgbtn-gray) in the CSS code:

    .rgbtn-gray {
    }

    .rgbtn-gray label {
      background: linear-gradient(#DDDDDD, #888888);
      color: #111111;
    }

    .rgbtn-gray input[type="radio"]:checked + label {
        background: linear-gradient(#888888, #333333);
        color:#F9F9F9;
    }


    20.
    Add the rgbtn-gray class to the HTML Form Element CSS Classes fieldfor the radio group.


    21.
    View the results:


    And now we have created a very nice looking set of gradient, clickable buttons in place of the standard radio buttons with circles.


    Tuesday, December 3, 2013

    YMD_BETWEEN and MOS_BETWEEN

    Not a Meta Tag, but required anyway

    Custom function: YMD_BETWEEN


    YMD_BETWEEN returns an 8-character string that represents the number of years, months, and days between two dates.  The output format is:

    01.02.03

    ·         The 1st and 2nd characters (01) represent the number of years between the two dates.
    ·         The 4th and 5th characters (02) represent the number of months between the two dates.
    ·         The 7th and 8th characters (03) represent the number of additional days between the two dates, after allowing for the years and months.

    YMD_BETWEEN takes two required arguments, and one optional argument that will be discussed later. 

    ·         Argument #1: Any date
    ·         Argument #2: Any date
    ·         Argument #3: Calculation type (default = 0)

    The order of the dates in the arguments (earliest, latest) is irrelevant.  YMD_BETWEEN will always return a positive number of time periods, starting with the earlier date and ending with the later date, irrespective of the order in which the dates are provided in the arguments.  By default, the difference is computed as of the end of the day for both days. 

    For example, the difference between 30Dec2008 and 31Dec2008 is only 1 day, because by default YMD_BETWEEN assumes that the time is approximately 23:59:59 on each date.

    The code for the YMD_BETWEN function is included at the end of this blog post.



    In its default mode, YMD_BETWEEN evaluates the dates using the following logic rules, in order:  Once a pair of dates meets a logical condition, the rule for that condition is applied, and no future logical conditions are tested.

    Apply the rule for the first TRUE condition:

       1.        If both of the dates represent the ending date of their respective months, then zero days are reflected in the result.    Notice that the order of the date arguments doesn't matter.

    Example: YMD_BETWEEN('29FEB2008','31AUG2009') = 01.06.00 (both dates = month end)
    Example: YMD_BETWEEN('28FEB2009','30APR2008') = 00.10.00 (both dates = month end)

       2.        If the later date represents the ending date of its month, YMD_BETWEEN counts full months working backwards from that ending date, and the number of days returned is the number of days remaining in the month represented by the earlier date.

    Example: YMD_BETWEEN('27FEB2008','31AUG2009') = 01.06.02

    This represents the six full months of Mar 1 through Aug 31, plus the last 2 days of February 2008 (28th and 29th).   In the example, remember that 27FEB2008 is interpreted as representing the end of that date, so there are only 2 days remaining in the month of February 2008.

    Example: YMD_BETWEEN('31AUG2009', '27MAR2008') = 01.05.04

    This represents the five full months of Apr through Aug, plus the last 4 days of March (28th through 31st).

       3.        If the earlier date represents the ending date of its month, YMD_BETWEEN counts full months working forwards from that ending date, and the number of days returned is the day number of the later date.

    Example: YMD_BETWEEN('31AUG2007', '24MAR2008') = 00.06.24

    This represents the six full months of Sep through Feb, plus 24 days in March.

       4.        When both dates have the same day number (e.g. August 25th and December 25th), the number of days returned is 0.

    Example: YMD_BETWEEN('25AUG2007', '25DEC2008') = 01.04.00

       5.        When the day number of the later date is greater than the day number of the earlier date, full months are counted starting with the day number of the earlier date, and the number of days is the result of subtracting the day number of the earlier day from the day number of the later date.

    Example: YMD_BETWEEN('15AUG2007', '27DEC2008') = 01.04.12

    In this example, the day number of the later date (27th) is greater than the day number of the earlier date (15th).  Therefore, Rule 5 counts Sep 15 as 1 month, Oct 15 as 2 months, Nov 15 as 3 months, and Dec 15 as 4 months, then counts 12 days (Dec 16th through 27th).


       6.        The only other possibility is that the day number of the earlier date is greater than the day number of the later date.  When that is the case, full months are counted starting with the day number of the earlier date, and the number of days is (A) the day number of the later date plus (B) the number of remaining days in the month immediately preceeding the month of the later date.

    Example: YMD_BETWEEN('27AUG2007', '20DEC2008') = 01.03.23

    Rule 6 counts Sep 27 as 1 month, Oct 27 as 2 months, and Nov 27 as 3 months.  There are 3 days left over at the end of November (28th through 30th), plus 20 days at the beginning of December for a total of 23 days.

    Note: The decision to use this method for Rule 6 was a conscious one.  An alternative, which was explored, would be to calculate the months as the number of full calendar months in between the two dates, then calculate the days as the number of stub days from each of the two dates.  The results of this alternate method would be slightly different from the selected method under certain conditions.

    Example: 04Jun2010 to 02Aug2010.

    Method 1 (in use): Begin with 04Jun2010, and count forward to 04Jul2010 as 1 month.  There are 27 days remaining in July (5th through 31st) plus 2 days in August, for a total of 29 days.

    Method 2 (considered): Count the intervening month of July 2010 as 1 full month.  There are 26 days remaining in June (5th through 30th) plus 2 days in August for a total of 28 days.

    Method 1 was chosen for two reasons.  First, by counting months starting with the earlier date, it is consistent with Rule 5.  Second, Method 2 is inappropriate for Rule 5, because it could result in silly results such as zero months plus 54 days for Mar05 through Apr28.

    Alternate 3rd argument:

    Including the number 1 as the 3rd argument will calculate differences from the beginning of the earlier date to the end of the later date.  All rules are adjusted accordingly, as shown in the examples below.

    Example: YMD_BETWEEN('29FEB2008','31AUG2009') = 01.06.00
    Example: YMD_BETWEEN('01MAR2008','31AUG2009',1) = 01.06.00
    Example: YMD_BETWEEN('29FEB2008','31AUG2009',1) = 01.06.01

    Example: YMD_BETWEEN('01MAY2008','28FEB2009',1) = 00.10.00
    Example: YMD_BETWEEN('30APR2008','28FEB2009',1) = 00.10.01

    Example: YMD_BETWEEN('27FEB2008','31AUG2009',1) = 01.06.03
    Example: YMD_BETWEEN('28FEB2008','31AUG2009',1) = 01.06.02

    Example: YMD_BETWEEN('27MAR2008','31AUG2009',1) = 01.05.05
    Example: YMD_BETWEEN('27MAR2008','01SEP2009',1) = 01.05.06


    Custom Function: MOS_BETWEEN


    The MOS_BETWEEN function is intended as an improvement to Oracle's MONTHS_BETWEEN function.  Building on the YMD_BETWEEN custom function described above, MOS_BETWEEN will calculate the number of months and portions of a month based on the value of YMD_BETWEEN.  Just like MONTHS_BETWEEN, MOS_BETWEEN will return a negative or positive number, based on the position of the earlier and later dates in the list of arguments.  However, MOS_BETWEEN corrects some shortcomings present in Oracle's MONTHS_BETWEEN function.

    Consider these two calls to Oracle's own Months_Between function:

    Select months_between('28FEB2009','31JAN2009') from dual;
    Value returned: 1

    This makes sense.  There is exactly one month between the end of January and the end of February.

    Now let's ADD one day to the range.

    Select months_between('28FEB2009','30JAN2009') from dual;

    Instead of starting with January 31, we'll start with January 30.  Logically, the result should add one day, or 1/31st of a month, so we should see something like 1.03225.

    Value actually returned: .9354

    This, of course, makes no sense whatsoever, unless you are looking to win a bar bet by proving that 30 is greater than 31.

    Using the Mos_Between function produces reasonable results:

    Select mos_between('28FEB2009','31JAN2009') from dual;
    Result: 1

    Select mos_between('28FEB2009','30JAN2009') from dual;
    Result: 1.032258065



    Function Code

    create or replace function YMD_BETWEEN
    (date1_in IN date, date2_in IN date, type_in in number default 0 )
    return varchar
    IS cvalue varchar(20);
    cursor tb is
    select *
    from
    (select YY || '.' ||
           MM || '.' ||
           DD  as YMD
    from
    (select substr(to_char(100 + trunc(MOS/12,0)),2,2) as YY,
            substr(to_char(100 + mod(MOS,12)),2,2) as MM,
            substr(to_char(100 + DD),2,2) as DD
    from
    (select Two.*,
            End_Mo - Begin_Mo
          - case when to_char(Begin_Date+1,'DD') = '01'
                  and to_char(End_Date+1,'DD') = '01' THEN 0
                 when to_char(End_Date+1,'DD') = '01' THEN 0
                 when to_char(Begin_Date+1,'DD') = '01' THEN 1
                 when to_char(End_Date,'DD') = to_char(Begin_Date,'DD') then 0
                 when to_char(End_Date,'DD') < to_char(Begin_Date,'DD') then 1
                 else 0 end as MOS,
            case when to_char(Begin_Date+1,'DD') = '01'
                  and to_char(End_Date+1,'DD') = '01' THEN 0
                 when to_char(End_Date+1,'DD') = '01' THEN M1Days
                 when to_char(Begin_Date+1,'DD') = '01'
                 then to_number(to_char(End_Date,'DD'))
                 when to_char(End_Date,'DD') = to_char(Begin_Date,'DD') then 0
                 when to_char(End_Date,'DD') > to_char(Begin_Date,'DD')
                 then to_char(End_Date,'DD') - to_char(Begin_Date,'DD')
                 else to_char(add_months('01' || to_char(End_Date,'MONYYYY'),0)-1,'DD')
                    - to_char(Begin_Date,'DD') + to_char(End_Date,'DD')
                 end as DD
    from
    (select One.*,
           to_char(Begin_Date,'YYYY')*12 + to_char(Begin_Date,'MM') as Begin_Mo,
           to_char(End_Date,'YYYY')*12 + to_char(End_Date,'MM') as End_Mo,
           to_char(add_months('01' || to_char(Begin_Date,'MONYYYY'),1)-1,'DD')
         - to_char(Begin_Date,'DD') as M1Days
    from
    --One
    (select Begin_Date - case when type_in = 1 then 1 else 0 end as Begin_Date,
           End_Date
    from
    (select case when date1_in < date2_in then date1_in else date2_in end as Begin_Date,
            case when date1_in < date2_in then date2_in else date1_in end as End_Date
     from dual)) One) Two)));

    begin
    open tb;
    fetch tb into cvalue;
    close tb;
    return cvalue;

    END;



    create or replace
    function MOS_BETWEEN
    (date1_in IN date, date2_in IN date, type_in in number default 0 )
    return number
    IS cvalue number;
    cursor tb is
    select (to_number(substr(YMD,1,2))*12
         + to_number(substr(YMD,4,2))
         + to_number(substr(YMD,7,2))/31)
         * case when date1_in < date2_in then -1 else 1 end AS MOS
    from
    (select YMD_BETWEEN(date1_in, date2_in, type_in) as YMD from dual);

    begin
    open tb;
    fetch tb into cvalue;
    close tb;
    return cvalue;

    END;