OBIEE: Physical Development Environment

I had contemplated about using source control before.

Related:
OBIEE Software Configuration Management by Mark Rittman
- Part 1 : Initial Deployment from DEV to PROD
- Part 2 : Subsequent Deployments from DEV to PROD
- Part 3 : Version Controlling the Project
OBIEE Enterprise Methodology Group Discussion
- OBIEE development, test and deployment
- Multiple RPDs/WebCats

Those deal with more of the software side of things. I want to explore the physical set up a little. I don't have the definitive answer, just thinking out loud perhaps.

The client is considering a ramp-up of development efforts (good thing). We were asked to think about an environment to allow parallel development. This is no easy task in OBIEE as the RPD can be a bottleneck.

While talking about it though, it sounded a lot like classic software development and source control. You pull down the code locally, fix/create/update it, and then merge that up into your trunk (subversion lingo anyway). You merge it up into a common repository.

What if you physically separated your environment? Here's my thinking:

1. Find the common code (init blocks, connection pools, etc) shared by all subject areas. Especially relevant if setting some variables or something for security.
2. Create virtual (or physical) machines with the full OBIEE environment (services, rpd, web cat) set up for each subject area and only that subject area. Of course this would include all components identified in step 1.

Something like this:



Pros

1. Allows for parallel development
2. Easier to identify changes
3. Easier to import than merge?
4. ????

Cons

1. Separating RPD into sections, scary
2. How do you handle physical tables shared across subject areas?

I need some good contrarians out there to discuss this with.

I don't know if this has merit or not, but I wanted to put it out there. Good or bad, I'm OK with. The goal is to give the client the most reasoned, researched, supportable analysis that I can.

ORA-01820: format code cannot appear in date input format

I learned something new today...you can't use week date format qualifiers in TO_DATE.

I was trying to transform a week to a month like this:

CJUSTICE@TESTING>SELECT TO_DATE( 200803, 'YYYYWW' ) FROM DUAL;
SELECT TO_DATE( 200803, 'YYYYWW' ) FROM DUAL
                        *
ERROR at line 1:
ORA-01820: format code cannot appear in date input format

Then tried one "W"

  1* SELECT TO_DATE( 200803, 'YYYYW' ) FROM DUAL
CJUSTICE@TESTING>/
SELECT TO_DATE( 200803, 'YYYYW' ) FROM DUAL
                        *
ERROR at line 1:
ORA-01820: format code cannot appear in date input format

Then "IW"

  1* SELECT TO_DATE( 200803, 'YYYYIW' ) FROM DUAL
CJUSTICE@TESTING>/
SELECT TO_DATE( 200803, 'YYYYIW' ) FROM DUAL
                        *
ERROR at line 1:
ORA-01820: format code cannot appear in date input format

Datetime Format models in 10gR2

Lo and behold the Error messages manual didn't have it (at least where I would expect it).



Put it up on twitter and Gary Myers responded:



That's good to know.

Now I just need to figure out how to transform that week into a month.

I'm trying to generate some test data for a quick...test.

I switched to using SYSDATE as that would allow more more flexibility to transform the other way.

SELECT DISTINCT
  TO_NUMBER( TO_CHAR( r, 'YYYYWW' ) ) fiscal_year_week,
  TO_NUMBER( TO_CHAR( r, 'YYYYMM' ) ) fiscal_year_month
FROM 
  (
    SELECT SYSDATE - rownum r
    FROM dual
      CONNECT BY LEVEL <= SYSDATE - TRUNC( SYSDATE, 'YEAR' )
  )

Which gave me exactly what I needed

FISCAL_YEAR_WEEK FISCAL_YEAR_MONTH
---------------- -----------------
          201015            201004
          201014            201004
          201012            201003
          201011            201003
          201007            201002
          201006            201002
          201002            201001
          201016            201004
          201010            201003
          201003            201001
          201017            201004
          201013            201003
          201009            201003
          201009            201002
          201005            201002
          201004            201001
          201013            201004
          201008            201002
          201005            201001
          201001            201001

Job: DBA in Philadelphia

Oracle's latest acquisition has created a need for an Oracle DBA in Philadelphia, PA.

Contact information is below.

Job Summary: Oracle Database Administrator (DBA)

The Oracle DBA will be supporting multiple environments from Development, Quality Assurance (QA) and Production databases running Oracle 10g in a clustered environment. Experience working in a production UNIX/Linux environment is required for this position. Experience in performance tuning, change implementation, backup and recovery, monitoring and installation are key requirements. Candidate will also be expected to provide application-level support for our in house J2EE applications running on Tomcat and OC4J. Ideal candidate will be a self-starter and a team player with a strong organization, process and communication skills. This is a permanent position, not a contract. No 3rd parties please. No H1's or L1's.

Job Description

• Installation and patching of Oracle RDBMS on Linux – adhering to standard operating procedures
• Performance tuning and administrative functions on Oracle production databases
• Enforce best practices in all areas of database administration and architecture including
• Review and implement database changes
• Participate in 24x7 on-call rotation for production databases
• Ensure backup and recovery of all Oracle data using RMAN, Standby Database and import/export
• Develop scripts for reporting, data manipulation and automation of daily tasks
• Provide support to Development and QA teams on applications/database issues
• Engage and support Software Developers during the SDLC
• Administration and support of Oracle’s Business Intelligence, Informatica, Tomcat and OC4J applications
• Develop and document formal processes and procedures

Job Requirements

• Computer Science, or related science/Engineering degree (B.S. or equivalent).
• At least 3 to 5 years production Oracle DBA experience in a Linux/UNIX environment
• At least 2 years of experience with Oracle Real Time Application Clusters 10g or 11g with ASM or NFS.
• UNIX/Linux system knowledge sufficient to configure, maintain and monitor Oracle instance(s)
• Experience with OLTP, OLAP, DW, and ETL concepts
• Experience with indexing strategies and reading explain plans
• Experience with parallel processing, and partitioning
• Experience with Oracle Data Guard
• Experience with Oracle Grid Control, especially monitoring and alerting and managing multiple databases
• Database performance tuning ability is required
• Familiarity with the Full Software Development Lifecycle (SDLC)
• Demonstrated knowledge of Oracle and Oracle tools to support database environments
• Demonstrated knowledge of shell environments (C, Korn, KSH, Bourne)
• Demonstrated proficiency of SQL and PL/SQL
• Familiarity with HTML/ JAVA /Java scripts / JSP / PERL is a plus
• Familiarity with Oracle’s Business Intelligence suite, Informatica and J2EE administration is a plus.
• Will require after-hours or occasional weekend work to make changes during outage windows.
• Excellent oral and written skills

Resumes should be sent to careers@fiberlink.com

Subject line must contain the following: Oracle DBA Position (Job ID: careers119)

Compensation is negotiable and based on experience.

Relocation package is not available.

OBIEE: Gotcha #1

When generating SQL for testing, make sure you set the database to the database you will be running against.

I found this out the fun way, of course, by going through, grabbing the SQL from the logs, and then modifying said SQL to run against Oracle. I should have paid attention to that "Check Consistency" warning.



I spent an hour or so "fixing" the SQL, removing stuff like the following:

fn ifnull
{ curly brackets }
d '2004-04-10'
ts '2004-04-10 13:44:39'

mostly small stuff, but annoying none-the-less. Strangely (to me anyway), this was the SQL submitted to an Oracle database...without errors. I'm guessing that somewhere, either in OBI or in the driver itself, the SQL is rewritten.

After I realized what had happened, I went back into the RPD and updated the database properties.



It was ODBC basic



I changed it to Oracle 10R2/11g



I tried the Consistency Check again and still received the warning. Opened up the database properties again and selected the Features tab



Then selected "Revert to Defaults"



Run the Consistency Checker again and voila!



So don't waste your time like I did. Make sure you use the Consistency Check Manager and make sure your database defaults match.