We recently hit a bug with OBIEE 11.1.1.5.0 (installed on Windows 7), BI Publisher. To our dev teams surprise, we ran into a blank blue screen when we launched BI Publisher. We had never experienced this before with the other installs on Linux and XP.

A search on the Oracle support site, let us to a solution (ID 1359064.1).

The issue occurs because some roles are missing for SOAP. The roles can be updated in the “SOAP Section” under Manage Privileges.

1. Log into OBIEE 11g with Administrator privileges -> Click on Administration -> Manage Privileges.
2. Go to the “SOAP Section” and assign following roles:

Assign BI Consumer and BI System roles to the Access to SOAP privilege

Assign BI Administrator and BI System roles to Impersonate as System User privilege

After making the changes, refresh the BI Publisher page. The page will show up correctly. I didn’t have to restart any services after assigning the privileges.

BI Publisher Home Page works after assigning privileges

Oracle Spatial is an option for Oracle database that provides advanced spatial features to support high-end GIS and LBS solutions. Here are the steps for manual installation of Oracle Spatial 11g and verification of a successful installation.

For detailed instructions or new features in 11g release 2, refer to the Oracle Spatial User’s Guide and Reference. The scripts executed in the following instructions are listed in the oracle database home directory:

C:\app\product\11.2.0\dbhome_2\md\doc\readme.txt

Step 1: Connect to the database where you want to install Oracle Spatial using SYSDBA privileges (use the SYS or SYSTEM accounts). For my install I used SQL*Plus to connect to the database.

Step 2: Verify prerequisites. To install Oracle Spatial, we need to have JAVA virtual machine, Oracle intermedia and Oracle XML database products already installed. Verify if the products are installed by executing the following SQL:

SQL> SELECT comp_id, version, status FROM dba_registry
2  WHERE comp_id IN ('JAVAVM','ORDIM','XDB')
3  /

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ -----------
ORDIM                          11.2.0.1.0                     VALID
XDB                            11.2.0.1.0                     VALID
JAVAVM                         11.2.0.1.0                     VALID

Step 3: The install scripts also require a MDSYS user account. Ensure MDSYS user exists or create one before installing Oracle Spatial:

SQL> select username from dba_users
2  where username like 'MD%'
3  /

USERNAME
------------------------------
MDSYS
MDDATA

-- If the user does not exist then create the user

SQL> CREATE USER mdsys IDENTIFIED BY <password> DEFAULT TABLESPACE sysaux ACCOUNT LOCK
2  /

Step 4: Execute scripts to install Oracle Spatial:

SQL> spool c:\spatial_install.txt
SQL> @C:\app\product\11.2.0\dbhome_2\md\admin\mdprivs.sql
SQL> @C:\app\product\11.2.0\dbhome_2\md\admin\mdinst.sql
SQL> spool off

Step 5: Verify Oracle Spatial is installed correctly. There should not be any invalid objects in MDSYS account:

SQL> SELECT comp_id, control, schema, version, status, comp_name
2  FROM   dba_registry
3  WHERE  comp_id = 'SDO'
4  /

COMP_ID                        CONTROL
------------------------------ ------------------------------
SCHEMA                         VERSION                        STATUS
------------------------------ ------------------------------ -----------
COMP_NAME
--------------------------------------------------------------------------
SDO                            SYS
MDSYS                          11.2.0.1.0                     VALID
Spatial

1 row selected.

SQL> SELECT object_name, object_type, status FROM dba_objects
2  WHERE  owner = 'MDSYS'
3  AND    status <> 'VALID'
4  ORDER BY 1
5  /

no rows selected

Resolve “Data issues” as soon as possible – another good tool to have in your tool bag.

The definition of an issue is that it is a problem that will be detrimental to the success of the BI project and cannot be totally resolved by the project team. That definition leads you to understand that data issues must be addressed quickly. If a problem is indeed being classified as an issue, the project manager must take responsibility for getting it resolved. The project manager should have an activity in the schedule every week to follow-up on open issues to ensure they are being diligently resolved.

By the same reasoning, if there is no urgency to resolve the issue or if the issue has been active for some time, you should look again to see if it really is an issue. It may be a potential problem (risk) or it may be an action item that needs to be resolved at some later point. Issues by their nature must be resolved with a sense of urgency.

Try to Solve the Root Cause, Not Just Various Symptoms!

When issues arise, they should be solved as quickly as possible. However, try to resolve the root cause of the issue, not just the symptom.

By solving the root cause you will ensure that the problem does not resurface later in your project. The root cause can usually be found by asking a series of ‘why’ questions. Why did the issue arise? When the question is answered, ask yourself ‘why’ again, and again. When you cannot answer the ‘why’ question again, you are probably close to the root cause.

Sometimes You have to Make Decisions Among Many Bad Alternatives

After reviewing the process and the techniques for managing issues, you may think that you should be able to successfully resolve every one if you only knew the right technique. In fact, you may find some issues that do not have good, clean solutions. It may be difficult in some cases to determine any good options for resolution. Other times, issues arise that are hard to resolve not because of a lack of options, but because of the difficulty gaining approval and resolution among a number of alternatives. In other cases, you may have a number of options that are less than optimal, and the ultimate resolution may be one that is the least offensive.

An example of this dilemma is an issue that involves internal politics. Usually when a problem starts to get mixed up with internal politics, you will find that the resolution is difficult because there is more to the decision-making process than a cool examination of the facts. When a problem becomes political, in fact, a resolution may be approved that is actually far less than optimum for the project team. However, a less-than-perfect solution may be preferable to deadlock or the prospect of an even worse alternative approved.

In these situations, try to get the approvers to understand that a delay in the resolution decision usually does not make the result any more palatable. The project manager should strive to gain a resolution as quickly as possible so that the project can move forward. If the issue is political, the project manager will usually need to rely heavily on the sponsor and other management stakeholders to help in the resolution.

Ask Team  Members to Identify Problems and Solutions

Issues can come from team members, clients or any project stakeholder. It is a good practice to encourage people to help identify solutions along with the issues. When a team member identifies a potential issue, ask them to bring one or more possible solutions. This process will help build accountability among the team members, but it will also help determine possible courses of action. In fact, if a team member proposes one or more viable solutions, the problem may be able to be resolved with the help of the project manager and never reach the level of an issue at all.

More will be written on this at a later time.

Keep in mind that you are not the ultimate solution to all problems, getting others involved, early will help you out drastically.

Communicating up and down the chain….

As I sit here I am thinking about the projects that I have been working on for the past couple of months.  I need to let everyone know what is going on, but there are at least 3 different levels of personnel that I need to keep in the loop and each has their own needs.  This can make things complex to report and could end up taking me several hours a week to complete (not good when I don’t get paid for the time I spend on status reports).

My first thought is to ask why?  Why do these people need to know what I am doing and what the heck do they need to know about it?   Should I be putting everything I do down (as a consultant – yes!) or should I be giving summaries?  The answer is simple – it depends on the person I am reporting to.

So you understand I have 6 people that are subordinates or equals, then I have a direct manager at the company.  Then I have several independent managers that are at different levels in the organization that I consult with and am responsible to.  As well there are other independent consultants in the company that need to know what I am doing on their projects.  Also I have to report my time to someone.  So I really have 5 different types of people to keep informed as to what I am doing for them, but there are only 3 levels of detail that I need to report.

5 Types of People but only 3 Levels?

Yes, that is what I said, some are up the chain, others are equal and some are down the chain.   Lets start with down the chain and lateral on the chain.

I have 6 people that I work with directly on a daily basis that need to know where things stand at, each of the individuals have their own projects and some of them cross with mine, some I manage, some I don’t.  So this is a type of person (a subordinate, 1 of 5) and represents only 1 level of 3.

They need the details of what I am doing, what I am concerned with in their work, the Project Plan, the status of their tasks and any known issues that have to be worked on by them.

Then there is a direct manager at the company that I have to report to.  In my case she handles my time card and is involved to some level with every project I am as well as others.  Her time is valuable, but she needs to know everything she can about all of the different things that I am involved with.   She is another type of person (2 of 5) and at a different level (2 of 3 levels) than subordinates.

One other point here is that problems need to be brought up with her, this is an opportunity to get issues out there and document them so that I have a record of it, this keeps us from pointing fingers later.

Direct managers need the details of each project, the time spent, the status, new project issues, existing/open issues, legal issues, closed items and concerns.  Sometimes you might want to report financial issues here as well, that will depend on your situation.

I stated earlier that I work with other consulting houses as well at the same company.  My role with them is very similar as it is with the direct manager in that I help supervise their staff, but also work with their project managers.  These are completely different types of people (they can be an equal, subordinates and sometimes managers) yet they deserve the same level of detail that I provide subordinates and the same issues I present to the manager, but only for their direct projects, so they are at the same level as both a subordinate and direct manager, but they are project specific.  This represents 2 types of people (3 and 4 of 5) but they have the same level as the subordinates and manager above (1 and 2 of 3 levels as above).

Status reports here, ensuring that they are project specific.

Finally information that is pushed up the chain needs to be summarized and filtered.  These are the corporate executives that are either project champions or owners, sometimes they are the president of the company, others they are VP’s or CxO levels.  Given this, they need to know individual project statuses, in summary form, and anything in the unresolved problems category that are not capable of being resolved between the managers and staff (including other contract houses).  I also include anything in the legal area here (they should be informed of these details) and anything in the financial aspect of the projects that I work on.  This is the last type of person (5 of 5) and the final level (3 of 3).

So what do they need?

For each person it is a bit different.  Keeping it simple can be hard to do, but it can be done and it can be simplified from the entry point of view.

Here is a simple format that covers a lot of ground quickly it leaves the time card outstanding:

I use Excel and create PDF’s for each group, I used roll-ups to keep it simple.  The time card is an addition to this and it is the only thing that I keep separated.  In the above, the roll-ups are:

The Use Case tool, rolls up for execs.

The Legal Concerns, rolls up for subordinates.

Simple and effective, updating takes only a few minutes a week.  Formulas are used to cover ground, I leave that to you to figure out (I cannot give away everything).

For some, Labor Day is a time to get together with family and friends or relax on the last holiday of the summer. Hopefully this weekend, you can take a break from all of the hard work this past year.  Whether your work involves saving databases from performance grief or ensuring the  database(s) are running with SLA’s of 99.9%  or making sure the correct data exists to render the dashboard graphs correctly…its amounts to the hard work that we put in. Putting in long days does not mean we are working hard; hard work means we are truly working hard.

Labor Day signifies a creation of the labor movement and honors American workers for their contributions. The Department of Labor acknowledges the national holiday by stating,

“The vital force of labor added materially to the highest standard of living and the greatest production the world has ever known and has brought us closer to the realization of our traditional ideals of economic and political democracy. It is appropriate, therefore, that the nation pay tribute on Labor Day to the creator of so much of the nation’s strength, freedom, and leadership — the American worker.”

At BraveSoft our staff clearly works hard and at the same time, enjoys doing what they do.  Its not about punching the clock.

I would have made this Labor Day post longer, but after I read Seth Godins post, I could not have said it any better myself.  Seth states: Hard work is where our job security, our financial profit, and our future joy lie.

please read the full blog at: Seth’s Blog about Labor Day

Happy holiday and succeed by doing hard work!

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