Wednesday, 4 April 2018

Bursting in OBIEE

What is Bursting?

Bursting is a process of splitting data into blocks, generating documents for each block, and delivering the documents to one or more destinations. The data for the report is generated by executing a query once and then splitting the data based on a "Key" value. For each block of the data, a separate document is generated and delivered.

Using BI Publisher's bursting feature you can split a single report based on an element in the data model and deliver the report based on a second element in the data model. Driven by the delivery element, you can apply a different template, output format, delivery method, and locale to each split segment of your report. Example implementations include:
  • Invoice generation and delivery based on customer-specific layouts and delivery preference
  • Financial reporting to generate a master report of all cost centers, splitting out individual cost center reports to the appropriate manager
  • Generation of pay slips to all employees based on one extract and delivered via e-mail
------

As we are told by Oracle team, OBIEE is not a tool for bursting of reports, use BIP instead. Agree.!! We all had a problem with bursting in OBIEE. The only tool providing this feature is BI Publisher but it requires a lot of extra efforts to complete a bursting defination using a separate data model since it dosen't leverage the RPD data model for bursting. 

There are already three ways through which we can do the FTP of bursted report sets to a shared physical location using Java Script or VB Script or installing EJB methods. Every one of this comes along with some limitations like we cannot change the directory for export or we cannot export it to remote/shared location, and even using this methods it is really confusing to end-users to set parameters as expected in the respected scripts. You can find my posts for exporting (bursting) reports using Java Script / VB Script.

Copy Results to the File System - Using Java Scipt (Windows Only)
Copy Results to the File System - Using VB Scipt (Windows Only)

To create bursting Using Session Variable, follow below steps.


1. Here's a data set


Take a note that JCRUZ is allocated to 2 regions.

2. Create an Initialization Block & Row-wise Session variable which will act as a filter parameter to the report.



2. Create a single report that could be sent to different people with an appropriate filter to give them only the content they needed:  i.e. bursting.


3. Configure Agents
  • Set an agent as an administrator to Configure an Agent:
  • Set "Run As" to "Recipent"
  • In "Delivery Content" set the report that you created earlier
  • In Recipent tab, include all users/Application Roles as per your requirement
  • Save the Agent



4. Run or Schedule Agent as an Administrator.

5. Try log-in with JCRUZ to open the Agent Alert.

6. Try log-in with JCRUZ

7. Here I've set the default "Home Page and Dashboard" as the destination. You can set the respective default profile to send it as an email, etc.



Monday, 22 January 2018

WriteBack in Pivot Table - OBIEE 12c / OBIEE 11g

Is it really not possible to have WriteBack functionality in OBIEE Pivot table? Oracle says a strict, "NO".

So let me tell you how we can get over this biggest limitations. Do you know what's the difference between Table View and Pivot View? At least in terms of OBIEE whereas everything it deals with is XML.!! If you ever see what XML it writes in Advanced Tab for both these views, you will get to know that there's no difference as such. So, I tried to do some analysis, and come up with the below solution. Please let me know whether this works for you to or not.

If you are at OBIEE 12c version, you no longer need to add <LightWriteBack>true</LightWriteBack> tag to instanceconfig file. There is a straight forword and simple way to implement WriteBack functionality.

Some basics of WriteBack feature:

Users of a dashboard page or an analysis have the ability to modify the data that they see in a table view. This ability is often referred to as "write back." To enable this functionality, below are some steps that we need to follow, (These are for Table view by the way..)

Setting up WriteBack in the repository

(Note: It is advisable that you do all the rpd changes in Online mode to get list of all the Users / Applciaion Roles in LDAP)

Physical Layer

  • In Physical layer, Go to Physical Table that has Writable Field.
  • In General table, make sure tab ‘Cacheable’ is un-checked.

BMM Layer

  • In BMM layer, Go to Logical Table.
  • Double click on the Writable column, check the flag for Writable

Presentation Layer

  • In Presentation layer, Go to Presentation Table.
  • Double click on the Writable column > Click on Permission > Assign Read/Write Access to desired Application Roles. (In general, it would be BIContentAuthor)


Direct Database Request

  • Make sure to assign "Direct Database Request" to the desired Application Roles
  • Go to Manage > Identity > Application Roles
  • Double Click on BIContentAuthor (in my case)
  • Permission > Query Limits > Set "Execute Direct Database Request" to "Allow"


Setting up Permission from BI Presentation Services

Login with Admin privileges

  • Go to Administration > Manage Privileges under Security section > Sroll down to the WriteBack privillage section at te bottom of the page
  • Default setting would be this, Change it to below configuration,



Create analysis with WriteBack field

  • Go to Column Properties of a Writable field > WriteBack tab
  • Check the box for "Enable Write Back"

Here onwards, the additional steps to configure WriteBack mechanism for Pivot view.
  • Convert the data type of column from Criteria > Edit Formula. Please Note: Data type of column in database can be of any type, we are not going to change it.

  • Change the data format as HTML to make it editable.
    @[html]<input class="WBInput" type="text" novalue="false" size="10" onchange="obipswb.Grid.Change(event)" onfocus="obipswb.Grid.Focus(event)" value=@H origvalue=@H>
      Note: This is the default formatting for all writable columns in OBIEE

      • Add a Pivot view to the analysis.
      • Change the XML code of the report from Advanced tab. This will allow us to use the standard WriteBack functionality in Table view that OBIEE supports.
      Find the start of XML code for Pivot table, i.e.
        <saw:view xsi:type="saw:pivotTableView" name="pivotTableView!1" scrollingEnabled="false" rowsPerPage="25">

        Add the below code to the next line,

        <saw:displayFormat>            
        <saw:formatSpec>               
        <sawwb:writeBack xmlns:sawwb="com.siebel.analytics.web/writeback/v1" xsi:type="sawwb:tableProperties" enabled="true" toggleTableMode="false" templateName="SetWriteBack_Pivot" useTemplate="true" buttonPos="right"/>
        </saw:formatSpec>
        </saw:displayFormat>

        Write-Back Template

        The write-back template is an XML-formatted file that contains SQL statements that are needed to insert and update records in the write-back table and columns that you have created. For Table view, we specify the name of the write-back template to use in Properties of that Table View, whereas we are setting it directly in XML of that report.

        Some standard consideration while defining columns in WriteBack template:

        Values can be referenced by position such as @1, @2, @3, ETC.
        If a parameter’s data type is not an integer or real number, add single quotation marks around it.

        But don't you think so this will not be applicable to our (special..!!) Pivot table?? Yes, it doesn't. We cannot specify columns by their position. If you observe, OBIEE generates unique column IDs for each of the column, we can consider that while writing SQL statement in WriteBack template.

        <?xml version="1.0" encoding="utf-8" ?>
        <WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
        <WebMessageTable lang="en-us" system="WriteBack" table="Messages">
        <WebMessage name="Forecast">
        <XML>
        <writeBack connectionPool="FedEX_CP">
        <insert> </insert>
        <update>
        MERGE INTO D1_ORDERS2 F
        USING (
        SELECT C.NEWKEY, T.YYYYMMDD, P.PRODUCTKEY, F.ACTLEXTND 
        FROM D1_ORDERS2 F, D1_CALENDAR2 T, D1_PRODUCTS P, D1_CUSTOMER2 C
        WHERE F.CUSTKEY = C.NEWKEY 
        AND F.PERIODKEY = T.YYYYMMDD
        AND F.PRODKEY = P.PRODUCTKEY
        AND C.NAME = '@{ca28e047766770c2c}'
        AND T.YYYYMMDD = '@{cdcaf965d4e3a312d}'
        AND P.SPECIFICDESCRIPTIN = '@{c543b919b7bb3b49b}') S
        ON (F.CUSTKEY = S.NEWKEY 
        AND F.PERIODKEY = S.YYYYMMDD
        AND F.PRODKEY = S.PRODUCTKEY)
        WHEN MATCHED THEN UPDATE SET F.ACTLEXTND  = '@{ce0127765131535c9}'
        </update>
        </writeBack>
        </XML>
        </WebMessage>
        </WebMessageTable>
        </WebMessageTables>

        Modify wbpivotview.js file

        Now we will let Pivot view to do some work like his younger brother.. Let's change a JS source code file for Pivot View then. i.e. wbpivotview.js from location

        C:\Oracle\Middleware\Oracle_Home\user_projects\domains\bi\servers\bi_server1\tmp\_WL_user\analytics\za01ic\war\res\b_mozilla\views\pivot

        C:\Oracle\Middleware\Oracle_Home\user_projects\domains\bi\servers\bi_server1\tmp\_WL_user\analytics\eiguw6\war\res\b_mozilla\views\pivot

        Replace the function definition for UpdateWriteBackActionForEdge

        obipswb.Grid.UpdateWriteBackActionForEdge = function(c, f, h) {
        var g = false;
        var b = c.getWriteBackRows(f);
        if (f == obips.JSDataLayout.DATA_EDGE) {
             for (var d = 0; d < b.length; d++) {
                 var a = b[d];
                 if (!a || a.length == 0) {
                     continue
                 }
           for (var dd = 0; dd < a.length; dd++) {
                     var aa = a[dd];
               if (!aa) {
                    continue
               }
               var e = obipswb.Grid.getRecordValues(c, a, f, aa.getCoordinate().getLayer(), aa.getCoordinate().getSlice());
          
               if (obipswb.Grid.shouldDeleteRow(aa)) {
                   h.deleteRecord(e)
               } else {
                   h.updateRecord(e)
               }
               g = true
           }
             }
             return g   
            }
        for(var d = 0; d < b.length; d++) {
        var a = b[d];
        if(!a || a.length == 0) {
        continue
        }
        var e = obipswb.Grid.getRecordValues(c, a, f, a[0].getCoordinate().getLayer(), a[0].getCoordinate().getSlice());
        if(obipswb.Grid.shouldDeleteRow(a)) {
        h.deleteRecord(e)
        } else {
        h.updateRecord(e)
        }
        g = true
        }
        return g
        };

        Restart services & Test the results.

        • Restart the BI stack to reflect the changes in configuration files. 
        • Open a report in a new tab that you created earlier.
        • Try updating values from a Pivot view
        Original Report:


        Updating Values:

        Updated Results:

        I hope you guys find this helpful. Please comment if you face any issues..

        Happy Blogging.. :)

        Thursday, 18 January 2018

        WriteBack Configuration In OBIEE 12c

        WriteBack in OBIEE 11g and 12c is somewhat different.. I think it is more simplified as there is no need to add any XML tag to instanceconfig.xml file. (Changing of any config files manyally is not recommended in any case, we can use  MBean Browser instead.)

        If you are at OBIEE 12c version, you no longer need to add <LightWriteBack>true</LightWriteBack> tag to instanceconfig file. There is a straight forword and simple way to implement WriteBack functionality.

        Some basics of WriteBack feature:

        Users of a dashboard page or an analysis have the ability to modify the data that they see in a table view. This ability is often referred to as "write back." To enable this functionality, below are some steps that we need to follow, (These are for Table view by the way..)

        Setting up WriteBack in the repository

        (Note: It is advisable that you do all the rpd changes in Online mode to get list of all the Users / Applciaion Roles in LDAP)
        • In Physical layer, Go to Physical Table that has Writable Field.
        • In General table, make sure tab ‘Cacheable’ is un-checked.
        • In BMM layer, Go to Logical Table.
        • Double click on the Writable column, check the flag for Writable
        • In Presentation layer, Go to Presentation Table.
        • Double click on the Writable column > Click on Permsssion > Assign Read/Write Access to desired Application Roles. (In general, it would be BIContentAuthor)

        Make sure to assign "Direct Database Request" to the desired Application Roles

        • Go to Manage > Identity > Application Roles
        • Double Click on BIContentAuthor (in my case)
        • Permission > Query Limits > Set "Execute Direct Database Request" to "Allow"

        Setting up Permission from BI Presentation Services

        • Login with Admin privileges.
        • Go to Administration > Manage Privileges under Security section > Scroll down to the WriteBack privilege section at the bottom of the page
        • Default setting would be this, Change it to below configuration,


        Create analysis with WriteBack field

        • Go to Column Properties of a Writable field > WriteBack tab
        • Check the box for "Enable Write Back"
        • Select the Text Field Width as 10. (This box is not very much clear enough but you can just click over and type the value, this worked for me)
        • Click on the Results tab.
        • Go to properties of Table View.
        • Enable WriteBack > Set Template Name


        Configuring WriteBack.xml template

        To place this template, there's a slight change in location than that was in 11g version. There is no default directory named customMessages where we are supposed to place WriteBack templates.

        Create customMessages directory under C:\Oracle\Middleware\Oracle_Home\user_projects\domains\bi\bidata\service_instances\ssi\metadata\content\msgdb\l_en

        Note: 

        You must also include SQL commands to insert and update records. These SQL commands reference the values passed in the write back schema to generate the SQL statements to modify the database table. Values can be referenced either by position (such as @1, @3) or by column ID (@{c0}, @{c2}). Column positions start numbering with 1, whereas column IDs start with c0. If a parameter’s data type is not an integer or real number, add single quotation marks around it.

        You must include both an <insert> and an <update> element in the template. If you do not want to include SQL commands within the elements, then you must insert a blank space between the opening and closing tags. The insert tag is only necessary if there
        are null values in the write back physical column. This example uses both elements. Oracle BI Server will choose between update and insert, depending on whether the column is null.

        Create a WriteBack templete as below, (Change WebMessage name, connectionPool name, SQL code as per your requirement)

        <?xml version="1.0" encoding="utf-8" ?>
        <WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
        <WebMessageTable lang="en-us" system="WriteBack" table="Messages">
        <WebMessage name="Forecast">
        <XML>
        <writeBack connectionPool="FedEX_CP">
        <insert> </insert>
        <update>
        MERGE INTO D1_ORDERS2 F
        USING (
        SELECT C.NEWKEY, T.YYYYMMDD, P.PRODUCTKEY, F.ACTLEXTND 
        FROM D1_ORDERS2 F, D1_CALENDAR2 T, D1_PRODUCTS P, D1_CUSTOMER2 C
        WHERE F.CUSTKEY = C.NEWKEY 
        AND F.PERIODKEY = T.YYYYMMDD
        AND F.PRODKEY = P.PRODUCTKEY
        AND C.NAME = '@1'
        AND T.YYYYMMDD = '@3'
        AND P.SPECIFICDESCRIPTIN = '@2') S
        ON (F.CUSTKEY = S.NEWKEY 
        AND F.PERIODKEY = S.YYYYMMDD
        AND F.PRODKEY = S.PRODUCTKEY)
        WHEN MATCHED THEN UPDATE SET F.ACTLEXTND  = '@4'
        </update>
        </writeBack>
        </XML>
        </WebMessage>
        </WebMessageTable>
        </WebMessageTables>

        Test the result

        Open a saved Analysis in another browser window and test whether the WriteBack is implemented correctly or not. If it is correctly configured, you will see a message saying "Update Successful" when you modify some values and click on Apply.

        Happy Blogging..!!

        Wednesday, 30 August 2017

        Not able to change Presentation Table names

        Welcome to OBIEE 12c.. With the default BI Admin Tool configuration, you cannot change any object's name from presentation layer..

        If you try to change the Table Name from a presentation layer, you would see Name field is not editable at all as in below screenshot.


        To enable this, Go to Tools > Options
        Select check-box for "Edit Presentation Names"

        You can now see the option for Name is enabled to edit into it..

        Happy Blogging..!!

        Monday, 21 August 2017

        Start - Stop OBIEE Services - 12c

        Welcome to OBIEE 12c.. :)

        As we already know, a lot of OBIEE developers life became an easy from the time we are experiencing OBIEE 12c or onwords version of the same.. The basic and day to day life tasks for OBIEE Admin / Developers is to upload a repository or restart of servers. This was really a time consuming tasks with 11g version of OBIEE. Here with 12c, we can just run a single command to upload a new repository and everything else will be taken care by this utiliy.. This is a link to the detailed article to upload a new rpd. Upload Repository Commands - downloadrpd & uploadrpd

        Same is with the restart of services. There is no need to Google "Restart sequence of OBIEE 11g services". Oracle has provided a simple and single command line utility to start or stop all the OBIEE stack, this includes Admin Server + BI Servers + BI Components. This utility is available for both the OS types i.e Unix and Windows too.. Below is the step (yeah, singular.!!) to stop the services,

        Go to the mentioned location and run stop.cmd

        C:\Oracle\Middleware\Oracle_Home\user_projects\domains\bi\bitools\bin


        To start the services, Run start.cmd


        Happy Blogging..!!

        Sunday, 23 July 2017

        Installing OBIEE 12c on Windows 7 - 64 Bit

        1. Verify your system environment:

        Before beginning the installation, verify that the minimum system and network requirements are met.


        Obtain the following Oracle distributions:
        Download the Oracle Fusion Middleware 12c Infrastructure and Oracle BI (12.2.1) distributions from any of the following locations:
        Oracle Technology Network or Oracle Software Delivery Cloud.

        Here's the link for OTN

        Install prerequisite software:

        Install the Java Development Toolkit (JDK) and the Oracle Fusion Middleware Infrastructure to create Oracle home, and to install the Oracle Fusion Middleware software directory for further installations.

        Set below environmental variable,
        JAVA_HOME=C:\Java\jdk1.8.0_101

        2. Install the Oracle Fusion Middleware software:


        Run the Oracle BI installer to install Oracle BI software in the same Oracle home where you installed the Oracle Fusion Middleware Infrastructure infrastructure.

        • Open command prompt in an Administrator mode and run the below command to start the installation,

        • Wait for a moment to see the Welcome screen of Fusion Middleware.

        •  Click next to continue just to skip Automate updates.
         

        • Click Next to select home location of Fusion Middleware.


        • Click Next.

        • The installer will then perform prerequisite checks, specially the supported OS and JDK version before starting the installation.


        • You can un-check the box and go ahead if you are not interested in security/update mails from Oracle Support.



        • Click Install and wait to finish.


        • Click Finish and see the summary of the installed product.


        3. Install the Oracle Business Intelligence software:


        The OBIEE 12c software comprises just two of the files. 

        For Windows 7-64 bit, 
        fmw_12.2.1.2.0_bi_windows64_Disk1_1of2.zip 
        fmw_12.2.1.2.0_bi_windows64_Disk1_2of2.zip

        Unzip these files into 1 directory. Please be informed that, fmw_12.2.1.2.0_bi_windows64_Disk1_2of2.zip wll get expand into another zip file, DO NOT unzip it again.

        • Simply double click the setup file, and follow the below screenshots



        • Specify the home for this installation. This should be the same home as the Fusion Middleware installed earlier. If confused, select it from drop down option. ;-) 


        • Specify whether you want SampleAppLite installed with this installation or not. As this is first time I'm installing 12c, I need to play around the new things in this so called latest version. 





        • We get installation summary. Notice that, unlike OBIEE 11g, we are not asked which components of OBIEE to install – BI Enterprise Edition, BI Publisher or Essbase. The software install always gives you all three, however later during the configuration we can decide which components we actually want setup. 



        4. Create RCU

        You can create the database schema either using the Repository Creation Utility (RCU) or using the Oracle Business Intelligence 12c Configuration Assistant.

        Follow the below screenshots to complete this straight forward approach,













        Configuring the Domain


        In this final part I will perform the configuration of OBIEE 12c. This will setup the domain with Fusion Middleware, configure the BI components and most importantly setup the URLs so you can connect!

        This is performed with the configuration utility which can be found under <obiee_home>/bi/bin

        Be informed that, there are two config.sh (config.cmd for windows) files, but in different directories.

        <obiee_home>/bi/bin contains the one for standard single server installations

        ORACLE_HOME/oracle_common/common/bin contains the one for enterprise deployments. This is far more comprensive than the single server installation and allows you to configure multiple instances on multiple servers.

        Run the Oracle Business Intelligence 12c Configuration Assistant tool to configure the BI domain.


        • Select the choices as per your requirement. 


        • Then the standard prerequisite checks are performed to ensure the server is configured correctly. 


        • Let the default values be there and select the weblogic password to login to EM, Console and Analytics. 


        • Enter the details of schema that you created earlier. 






        • The summary screen then displays showing the settings you’ve selected and main URLS for accessing the weblogic console, enterprise manager, OBIEE and BI Publisher. Save this file for future references.
        • Click on Configure to start the configure process. This can take considerable time to configure and start all the components installed so far.
        • Click Finish.


        Once you go successfully through all this, you are all set to use your brand new BI system. ;-)

        Enter below URL to open Analytics, http://obieenow.localdomain:9502/analytics