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.

APEX 4.0 EA version is now out. Everyone who has been waiting to get their hands on the new release can sign up at: http://tryapexnow.com/. Click on the Sign Up button.

tryapexnow.com Early Adopter Sign Up Page

I first launched the application in IE8 (browser) but realized that some of the buttons were not showing up. Oracle also posts a warning, “Internet Explorer is not currently supported with the hosted APEX 4.0 Early Adopter. The preferred browser for this evaluation is Mozilla Firefox. Internet Explorer will be fully supported at a later date (before Production).”

Application Express 4.0 EA Home Page

I first wanted to try the new application type – “websheet”. But as I clicked on the option, I got an error: “Websheets are not enabled at this time”. Looking at some of the messages, it looks like Oracle will have an EA Phase II.

The interface does look good. I will be reviewing the new features and posting my feedback/thoughts here in future posts.

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

Create a report with Data Template code for the Data Triggers

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.

View Report. Data is displayed from the staging table populated by 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.

Oracle unveils Exadata Version 2

One of the key ingredients of this Exadata Version 2 is the combination of Sun and Oracle storage technologies (Flash fire memory cards and ASM, advance compression, etc.) and of course Oracle 11g.  Exadata Smart Flash Cache addresses the disk random I/O bottlenecks moving hot data to Sun FlashFire cards.

Sun FlashFire Technology

Exadata Version 2 is available in four models: full rack (8 database servers and 14 storage servers), half-rack (4 database servers and 7 storage servers), quarter-rack (2 database servers and 3 storage servers) and a basic system (1 database server and 1 storage server)…. all available now.   The  “quarter rack,” is $110,000, while a “full rack” system carries a price of $1.1 million.

We do a lot of work with data warehouse demands so have already identified several clients/projects that would benefit just from the basic system.  We have concluded that when addressing data warehouse projects with performance needs, data warehouse users should give serious consideration to the Oracle Exadata Version 2 solution.

Quick recap of the highlights:

• 1 million I/O operations per second
• uses Linux, rather than Sun Solaris
• Intel-type processors, rather than Sun’s Ultrasparc T2 chips
• uses Sun Fire X4275 servers with Intel’s quad-core Nehalem processors
• new flash-based memory system from Sun that is used in the storage servers; called FlashFire

We ran into an issue where a third party application running on SQL-Server 2005 could not interface with Active Directory because the application manages its own setup of user security accounts.  We needed the existing users in Active Directory to be copied to this third party applications security tables and kept insync with Active directory. There were  about a thousand user accounts and hand typing was not an attractive option as you can imagine. Rather than have Human Resources input each employee by hand, we came up with a way to export employee names from active directory and import them into the applications tables.

Our method was to connect to Active Directory using LDAP and a SQL Server database link.  We created a stored procedure to perform the extract and scheduled it to run periodically to delete or add new employees, since the applications needed to be insync with the latest account changes in Active Directory.

To extract information from Windows Active Directory into SQL Server 2005 database first you need to create a linked server connection to Active Directory via LDAP.

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'

Next enable OpenRowSet and OpenDataSource support (Also known as AD Hoc Queries) if it is not already enabled.

EXEC sp_configure 'show advanced options',1;

GO;

reconfigure;

GO;

EXEC sp_configure 'Ad Hoc Distributed Queries',1;

GO;

reconfigure;

GO;

Once you have enabled Ad Hoc Queries you can query Active Directory via LDAP, but first you will need to determine the proper ldap string. This is usually your windows active directory domain name and extension. If your domain was mycompany.int then the ldap string would be:

LDAP://DC=mycompany, DC=int

Once you have your ldap string you can use the query below to extract employeeID, displayName and distinguishedName from Active Directory. The query we’re using is commonly available on the internet and can be customized to extract the information you need. It extracts the information from Active Directory and inserts into a temporary table called #ADUsers.

CREATE TABLE #ADUsers
( email varchar(255) NULL,
employeeID varchar(10) NULL,
displayName varchar(255) NULL,
distinguishedName varchar(255) NULL);

GO;

/* AD is limited to send 1000 records in one batch. to work around this limit we loop through the alphabet. */

DECLARE @cmdstr varchar(255)

DECLARE @nAsciiValue smallint

DECLARE @sChar char(1)

SELECT @nAsciiValue = 65

WHILE @nAsciiValue < 91

BEGIN

SELECT @sChar= CHAR(@nAsciiValue)

EXEC master..xp_sprintf @cmdstr OUTPUT, 'SELECT mail, employeeID, displayname, distinguishedName

FROM OPENQUERY( ADSI, ''SELECT distinguishedName, displayname, employeeID, mail

FROM ''''LDAP://DC=mycompany, DC=int''''WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''%s*'''''' )', @sChar

INSERT #ADUsers EXEC( @cmdstr )

SELECT @nAsciiValue = @nAsciiValue + 1

END

SELECT * from #ADUsers

DROP table #ADUsers

If you plan on running the extract as a stored procedure from a scheduled job it makes more sense to use a permanent table rather than temporary table.

CREATE TABLE ADUsers
( email varchar(255) NULL,
employeeID varchar(10) NULL,
displayName varchar(255) NULL,
distinguishedName varchar(255) NULL);

GO;

This query imports the data into the ADUsers table we created above. Note that the first step of our extract is to truncate the ADUsers table.

CREATE PROCEDURE [dbo].[sp_extract_AD]

AS

TRUNCATE TABLE ADUsers;

/* AD is limited to send 1000 records in one batch. to work around this limit we loop through the alphabet. */

DECLARE @cmdstr varchar(255)

DECLARE @nAsciiValue smallint

DECLARE @sChar char(1)

SELECT @nAsciiValue = 65

WHILE @nAsciiValue < 91

BEGIN

SELECT @sChar= CHAR(@nAsciiValue)

EXEC master..xp_sprintf @cmdstr OUTPUT, 'SELECT mail, employeeID, displayname, distinguishedName

FROM OPENQUERY( ADSI, ''SELECT distinguishedName, displayname, employeeID, mail

FROM ''''LDAP://DC=mycompany, DC=int''''WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''%s*'''''' )', @sChar

INSERT ADUsers EXEC( @cmdstr )

SELECT @nAsciiValue = @nAsciiValue + 1

END

Now we can create a scheduled job to execute the sp_extract_AD stored procedure periodically to pickup any changes in Active Directory and export them to our time tracking application database.

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):

Define a Date Parameter (Calendar Widget) in Oracle BI Publisher

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

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:

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

Here is a run time image of the calendar widget for the report date parameter

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;

For one of our clients, we developed an APEX application (APEX 3.2.1) and during a screen review they were particularly concerned with the look of the entry fields that had disproportionate lengths. As a best practice, its important for an application to have a uniform look to be aesthetically pleasing.  The application required a form with multiple select drop down lists and by default “select lists”  in APEX display different widths based on the existing data value lengths. During QA the users were not pleased with the look of the select lists and requested to see uniformed columns. We decided to make the width of the “select lists” to match the width of the longest select list value in the form.

Image of Form before the Select Lists are sized to match

Image of Form after the Select Lists are sized to match

Apply Style in HTML Form Element Attributes

Select Lists in the Form have the same Width

The key here for such a simple task, was using CSS styles.