When you run a race, the start is paramount to your success. However, even if you have a good start, you need a strong middle and end of the race to compete successfully. This is a good analogy for your business intelligence (BI) project as well. You need to be cognizant of the entire project life cycle, or you may end up with a solution that is poorly architect-ed, populated with questionable data, or isn’t well-adopted by the business users.

Listed below are some items to consider before you begin a BI project.

1) Before the project begins:

a) Rally the business users on the concepts of BI and the benefits  – what to expect.

b) Identify key stakeholders (business decision-makers) and get them in-sync for the duration of the project.

c) Identify the source systems and base the data marts on key business processes. (Find the first win-win for the phase I).

d) Capture the holistic view of the project, but deliver in small phases, then expand to new  phases.  (think holistic; don’t have a narrow view )

2) During the project:

a) Maintain communication between functional (business) and IT; also remind IT this is the “business users” system, not IT’s system…..Create a plan to keep the project visible to all and share the goals and milestones that have been hit.

b) Concentrate on the data quality. Raise flags on data issues or limitations.

c)  Identify analytical views that the business users will understand and benefit from. Get the rendered data into their hands sooner than later. Plan to demo a prototype within weeks, not months. Receiving feedback as soon as possible is critical, therefore by providing the business users with a visual to review, benefits the project.

3) After the project ends:

a) Ensure that end-users are properly trained and are using the system appropriately. Provide a collab tool for feedback.

b) Encourage ideas and feedback from the business community

c) Understand that BI projects evolve and never really ends;  ensure the stake owners understand that. Hit milestones, but keep the momentum going.

(There is a lot more to add, but we can only touch on some key points).

Note the common theme of involving the business – whether it is during design, development, test and/or future enhancements to the system (to name a few) – getting the business users excited about the end-product will make the time spent educating, while also learning from them well worth it. Yes, it’s a threat to some that can’t let go of excel and silo data, but BI success will help put an end to that.

In the coming months I will go into more detail for many of the items listed above. In the meantime, checkout this YouTube clip that represents the aforementioned point:

And when you are planning your upcoming BI initiatives, make sure you think about the start, middle, and end of the project to ensure a successful implementation.

This is a follow up on an earlier blog that we did on Date Parameters in BI Publisher.

Oracle BI Publisher offers more Date Expressions such as:
{$SYSDATE()$} – current date (the system date of the server on which BI Publisher is running)
{$FIRST_DAY_OF_MONTH()$} – first day of the current month
{$LAST_DAY_OF_MONTH()$} – last day of the current month
{$FIRST_DAY_OF_YEAR)$} – first day of the current year
{$LAST_DAY_OF_YEAR)$} – last day of the current year

This has been documented in Oracle’s Business Intelligence Publisher New Features Guide.

The expression is entered in the “Default Value” field for the Date Parameter in “General Settings”.

Here is an example of implementing Data Triggers (Event Triggers) using Data Templates in Oracle BI Publisher (XML Publisher).

As per Oracle documentation, “Data triggers execute PL/SQL functions at specific times during the execution and generation of XML output. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as perform initialization tasks and access the database.”

Data trigger is implemented using the <dataTrigger> element in a data template. This element has two attributes: a name and a source. The source attribute points to a function where our PL/SQL code resides.

Note: It is important that the PL/SQL code called by data triggers should reside in a function and the function should be wrapped in a package. The function should return a boolean value (true for success and false for failure). I tried implementing data triggers using procedure and code that was not wrapped in a package – it did not work for me. So I am only using functions returning a boolean value for data triggers.

Also, location of the trigger indicate at what point the trigger fires. Placing the data trigger element before the <dataStructure> element will fire the code before the data query is executed – this will act like a “Before Report Trigger”. Placing the data trigger element after the <dataStructure> element will execute the code after the data query is executed – acting like a “After Report Trigger”.

In the following example I use the data template to populate a staging table using a “before report” trigger. Then execute a SQL on the staging table to fetch the report data and in the end execute an “after report” trigger to log an entry into a Log table.

For this example, I am using the customers table in the oracle demo database “oe” schema.

Step 1: Create a staging table and a log table. The staging table will be populated by the before trigger and I will make an entry in the log table using the after trigger.

DROP TABLE dataTemplateLog
/
DROP TABLE stageCustomers
/
CREATE TABLE dataTemplateLog (actionReport VARCHAR2(100), dtReport DATE)
/
CREATE TABLE stageCustomers AS SELECT * FROM customers WHERE 1 = 2
/

Step 2: Create a package containing PL/SQL code for the before and after data triggers.

CREATE OR REPLACE PACKAGE dataTemplateTriggers
AS
  FUNCTION beforeTriggerPopulateStage RETURN BOOLEAN; -- Function for Before Report Trigger
  FUNCTION afterTriggerLogRun RETURN BOOLEAN; -- Function for After Report Trigger END;
/
CREATE OR REPLACE PACKAGE BODY dataTemplateTriggers AS
 FUNCTION beforeTriggerPopulateStage RETURN BOOLEAN AS
 BEGIN
  DELETE FROM stageCustomers; -- Clear the staging table
  INSERT INTO stageCustomers
  SELECT * FROM customers; -- Insert data into the staging table for the report
  COMMIT;
  RETURN true;
 EXCEPTION
  WHEN OTHERS THEN
   RETURN false;
 END;
 FUNCTION afterTriggerLogRun RETURN BOOLEAN AS
 BEGIN  -- Create a Log entry after the report runs
  INSERT INTO dataTemplateLog (actionReport, dtReport) VALUES ('Data trigger executed.', sysdate);
  COMMIT;
  RETURN true;
 EXCEPTION
  WHEN OTHERS THEN
   RETURN false;
 END;
END;
/

Step 3: Create a data template for the Customer report

Here is the data template code:

<dataTemplate name="CustomerReport" dataSourceRef="oe" defaultPackage="dataTemplateTriggers">
 <dataQuery>
  <sqlStatement name="CUSTOMER1">
   SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_EMAIL, CREDIT_LIMIT FROM stageCustomers
  </sqlStatement>
 </dataQuery>
 <dataTrigger name="beforeReport" source="dataTemplateTriggers.beforeTriggerPopulateStage()"/>
 <dataStructure>
  <group name="G_CUSTOMER" source="CUSTOMER1">
   <element name="CUST_FIRST_NAME" value="CUST_FIRST_NAME"/>
   <element name="CUST_LAST_NAME" value="CUST_LAST_NAME"/>
   <element name="CUST_EMAIL" value="CUST_EMAIL"/>
   <element name="CREDIT_LIMIT" value="CREDIT_LIMIT"/>
  </group>
 </dataStructure>
 <dataTrigger name="afterReport" source="dataTemplateTriggers.afterTriggerLogRun()"/>
</dataTemplate>

Step 4: Execute the report. The data in the report is from the staging table populated by the Before Report Data Trigger.

Step 5: Select information from the Log table. The latest entry was made by the After Report Data Trigger.

SQL> SELECT substr(actionreport, 1, 30), dtreport FROM datatemplatelog;
SUBSTR(ACTIONREPORT,1,30)      DTREPORT
------------------------------ ---------
Data trigger executed.         30-OCT-09

You can use this example and enhance it to match your requirements for Before and After report triggers in Oracle BI Publisher.

Here are the steps to implement a “Date” parameter type in BIP. I have also included some additional Java Date formats that can be used to format the dates.

1. Open (Edit) the report using date parameter in BIP. 
2. Click on the “Parameters” node. Click the New button in the toolbar to create parameter.
3. Use the Parameter Type of “Date” and set the remaining values for the Parameter (as displayed):

 

Following is a description of the important fields used with the “Date” parameter type:

Default Value	This is the default date that will show up in the parameter field at run time. We can set this value to the system date by using “{$SYSDATE()$}”. We can also perform calculations such as {$SYSDATE()-7$} to set default value to last week’s date.
Display Label	This is the label that will display next to the Calendar Widget at run time. This should be a meaningful label for the users.
Date Format String	The format in which the date will be displayed and stored. This should be java date format (see java date format table below for more examples). I am used to Oracle date formats so I had to look up Java date formats in a book. The equivalent of Oracle “DD-Mon-YYYY” in java is “dd-MMM-yyyy”.
Date From	The minimum date a user will be able to select from the calendar widget at run time.
Date To	The maximum date a user will be able to select from the calendar widget at run time.

Note: We can perform calculation on “Default Value”. Example: {$SYSDATE()-7$}

Also, I found the Date From and To fields very helpful. It allowed me to keep users within a pre-defined range of dates. Please note when using these two fields, specify the dates in the format defined in the “Date Format String” field. If you don’t use the exact same format, BI Publisher will not give you any errors. But at run-time, it will not display the date correctly.

Some additional Java Date formats that I got from a Java Book:

Format	Meaning	Result
yy or yyyy	Year	08 or 2008
M or MM	Month Number	7 or 07
MMM or MMMM	Month Text	Dec or December
d or dd	Day in Month	3 or 03
hh or HH	Hour	04 (1-12) or 16 (0-23)
m or mm	Minute	6 or 06
s or ss	Second	7 or 07
EEE or EEEE	Day in Week	Tue or Tuesday
zzz or zzzz	Time Zone	EST or Eastern Standard Time

 

Finally, run your BI Publisher report and click on the calendar icon to invoke the Calendar Widget.

Last week I had installed Oracle XE on our Linux DEV box. I wanted to make some changes to the instance settings and realized I had misplaced the password for Internal workspace account. After doing some research, I found that Oracle provides us a “password change” script with APEX source code. We can run this script using the SYS account and it will reset the password for internal account. Here are the steps and an example:

1) Log into sqlplus using the SYS account.

2) Execute apxxepwd.sql script in the APEX source code directory with the new password.

3) Log back into Application Express INTERNAL workspace using the new password.

Here is an example:

[home@localhost]# cd apex
[home@localhost apex]# sqlplus sys/syspassword
SQL> @apxxepwd.sql password123
Session altered.
...changing password for ADMIN
PL/SQL procedure successfully completed.
Commit complete.
SQL> exit

The code in apxxepwd.sql looks like this:

Rem Copyright (c) Oracle Corporation 1999 - 2006. All Rights Reserved.
Rem
Rem NAME
Rem apxxepwd.sql
Rem
Rem DESCRIPTION
Rem Changes the password for the INTERNAL ADMIN user
Rem
Rem NOTES
Rem Assumes the SYS user is connected.
Rem
Rem REQUIRENTS
Rem - Oracle 10g
Rem
Rem
Rem MODIFIED (MM/DD/YYYY)
Rem jstraub 08/01/2006 - Created
Rem jkallman 09/29/2006 - Adjusted current_schema to FLOWS_030000
Rem jkallman 08/02/2007 - Change FLOWS_030000 references to FLOWS_030100
Rem
set define '&'
set verify off
alter session set current_schema = FLOWS_030100;
prompt ...changing password for ADMIN
begin
wwv_flow_security.g_security_group_id := 10;
wwv_flow_security.g_user := 'ADMIN';
wwv_flow_security.g_import_in_progress := true;
for c1 in (select user_id
from wwv_flow_fnd_user
where security_group_id = wwv_flow_security.g_security_group_id
and user_name = wwv_flow_security.g_user) loop
wwv_flow_fnd_user_api.edit_fnd_user(
p_user_id => c1.user_id,
p_user_name => wwv_flow_security.g_user,
p_web_password => '&1',
p_new_password => '&1');
end loop;
wwv_flow_security.g_import_in_progress := false;
end;
/
commit;