BI & Warehousing

Today’s the first full day of the UKOUG Technology & EBS Conference in Birmingham, UK, and I’m currently getting my presentations ready for later on today. This year we’ve again got a stand in the main exhibition hall, where we’ll be running demos of OBIEE, Warehouse Builder and the rest of the Oracle BI technology stack. Yesterday though was all about getting up to Birmingham, getting the stand set up and then off in the evening for the second annual ACE Director meal.

The evening was organized by Debra Lilly and myself, with Todd from the OTN team kindly bringing along the credit card and passing along messages from Vikki, Lillian, Justin and the team back from HQ. We had an excellent turnout with 25 ACEs and ACE Directors from around the world, including such names as Steven Feuerstein, Bryn Llewellyn, and Mogens Norgaard, and it was a great chance to catch up with people such as Marco, Dimitri, Dan Morgan, Tim and others that I tend to see only at other conferences around the world.

Today’s a busy day for me, with the following two sessions running later today:

• “Oracle BI EE and Essbase Integration Step-by-Step”, Monday 14:45 – 15:45, presented by myself and Venkat Janakiraman
• “Oracle Warehouse Builder 11gR2 New Features”, Monday 16:15 – 17:00, presented by myself

We’ve also got a fun demo running on the stand (Stand 50) where we’re using OBIEE as part of a football management simulation, where you can pretend to be an investor looking to put money into any of the Premier League teams and seeing, through the use of analytics, writeback and the dashboard where this money would be put to best use. I’m trying to persuade Chris to rig the numbers so that Arsenal always get relegated and Spurs win the league, but I think at least the latter is a bit beyond the powers of Oracle BI – perhaps we need to deploy Essbase in the background. Anyway, if you’re at the conference then be sure to pop by stand 50 if you get a moment, otherwise hopefully I’ll see a few people at the various conference sessions.

I’m currently sitting in speaker ready room at the UKOUG Technology & EBS Conference up in Birmingham, and the team are on the stand now ready to meet people at the exhibition. I’ve got an hour or so free now this morning, and so I thought it’d be a good opportunity to blog about one of the sessions we’re delivering later this week at the conference.

The session I’m thinking about is our OBIEE Masterclass that’s running for two hours on the last day of the conference. In previous years we’ve covered the basics of OBIEE development together with performance tuning tips for the BI Server and the underlying Oracle database, and as we’re still waiting for the 11g release to come out, we thought we’d devote this year’s session to some of the perennial questions that come up on the forums, on our blog and when we work with customers. We’re therefore spending half of the masterclass on OBIEE data modeling questions, specifically how do we model normalized data sources, single-table sources and un-conformed star schemas, whilst the second half of the session is being devoted to software configuration management for OBIEE projects.

Software Configuration Management is a topic that seems to come up on most projects, with customers at least wanting to version control their project and have a means to promote code between development, test and production environments. Now there’s not really a defined, standard way to do this with OBIEE projects, and certainly on this blog we’ve talked about various ways to do this, and others have added to the debate with alternatives and suggestions on how to do things better. We’ve also had a utility released, the “Content Accelerator Framework”, that some people have talked about as being a good tool for promoting changes between environments, and now that OBIEE projects are getting more mature and more “enterprise” we’ve also had requests for tips on code branching, code merging and other common software configuration management (SCM) techniques.

So this seemed to me a good area to cover in our OBIEE masterclass. As we’ve got a fair few experts on OBIEE within the company, including people like Venkat J, Adrian Ward, Borkur Steingrimsson and Christian Berg, we had a fair bit of internal debate on what works, what doesn’t work and what’s practical within a project. If you’re interested, I’ve uploaded the resulting slides here and over the next few blog posts, I’ll go through what we came up with, starting in this post with tips on the initial deployment of a project.

To get back to basics though, if you’re working with OBIEE on a project, there are a number of SCM tasks that you’ll want to carry out:

• We’ll want to set up separate development (DEV), production (PROD), and perhaps test (TST) and user acceptance testing (UAT) environments
• We’ll then want to be able to promote OBIEE metadata and configuration files between these environments
• You’ll want to be able to promote individual reports between environments, and create them for users to use
• You’ll typically want to version your project, so that you can identify releases and refer back to previous versions of the project
• You may want to branch the code, or work concurrently on separate but linked projects, and then merge the code back into a single code stream

Within an OBIEE system (disregarding the BI Apps or the underlying data warehouse for the time being), there are a number of project elements that you’ll want to include in this process, including:

• The BI Server repository file (the “RPD” file)
• The BI Presentation Server web catalog (the “webcat”)
• various configuration files (the BI server .INI files, instanceconfig.xml etc)
• various other artifact files (used for setting up writeback, for example), and
• various web files (CSS etc) if you’ve customized the dashboard UI

For the purposes of this posting, we’ll assume you’ve just got DEV and PROD environments (if you have TEST, UAT etc as well, the process is very similar with additional steps in between when you promote code from one environment to the other). For now though we’ll assume you have just a DEV environment, which is where the developers edit and develop the RPD together with a set of shared reports and dashboards; and PROD environment, where the RPD is “read-only” but users create their own reports in their own web catalog folders.

The first thing to bear in mind here, is that you should generally try and have a single BI Server per physical/virtual server, which you may end up clustering with other single BI Servers on other single physical/virtual servers. There are a few workarounds where you can set up a single BI Server with multiple RPDs attached, or indeed install multiple BI Server instances on the same physical/virtual servers, but this is not recommended as OBIEE 10g isn’t really designed for this and you hit issues around files, configuration settings and so on being inadvertently shared amongst all environments. If you want to set up multiple BI Servers on the same box, use VMWare or another such virtualization product to create OS containers and work from there. I have heard that support for multiple BI Servers on the same OS environment is coming in the 11g release, but for now it’s not recommended.

So once you’ve got DEV and TEST environments set up, and you’ve developed your initial system in the DEV environment, how do you go about making use of the PROD environment? For us, you can generally divide up code promotion into two stages:

1. The initial deployment of the project from DEV to PROD
2. Subsequent updates of PROD from DEV, for example new releases of the project

This initial release typically involves copying the whole project, including the RPD, the web catalog, the supporting web, configuration and artifact files, from the DEV environment to the PROD one, and we don’t have to worry about merging, overwriting or preserving what’s currently in PROD (as there won’t be anything there yet). The second and subsequent migration require a bit more thought though as you’ll typically want to preserve what’s already in production.

Doing the initial DEV to PROD code promotion is therefore a fairly straightforward process, as there aren’t really any decision points, more a series of steps to remember, as shown in the flowchart below:

Starting from the beginning, the typical steps you’d want to perform in this initial migration are:

1. Create a temporary directory somewhere on the DEV server, into which we’ll put all the files to migrate. Shut down the BI Server if the RPD you want to migrate is running online,

and then copy the development RPD to this temporary folder.

2. Next, start the Catalog Manager utility and connect to the web catalog either online or offline. Select (click on) the Shared folder, and then from the application menu select File > Archive. Archive the shared web catalog folder to a file in the temporary directory you created earlier, and then repeat this for the Users directory if there are reports in there that you want to migrate. Don’t archive the whole web catalog, or the System folder, as the production web catalog will get corrupted if you try and copy the system folder from another catalog into it (you’ll not be able to connect to any dashboards or to Answers).

3. Now gather up the various configuration, artifact and other files that you need to copy from this environment to the production one. This list isn’t exhaustive, but you’d typically want to gather up the following files and copy them into the temporary directory on the development server.

$ORACLEBI/server/config/*.*
$ORACLEBIDATA/web/config/instanceconfig.xml
$ORACLEBIDATA/web/config/xdo.cfg
$ORACLEBI/web/javahost/config/config.xml
$ORACLEBI/xmlp/XMLP/Users
$ORACLEBI/xmlp/XMLP/Reports

plus any files that you’ve used to customize the dashboard UI, set up writeback and so on. Once you’ve done this, zip up the temporary directory ready for transferring to the production server.

4. Now copy this ZIP file from the DEV to the TEST server, unzip the file, and copy the files to the correct location on the production server, including the RPD that contains the BI Server repository. Watch out for files like NQSCONFIG.INI and instanceconfig.xml that reference machine names in the files, as you’ll need to update these to reflect the naming in the production environment.

5. It’s generally good practice to do any new project development into a fresh, blank RPD and web catalog, and so if the production server is still pointing towards the Sample Sales or Paint web catalog, you’ll want to create a new one into which you’ll copy the development webcat items in a moment. The safest way to create a new web catalog, with all permissions, folders and so on on created correctly, is to create a new folder in the web catalog folder ($ORACLEBIDATA/web/catalog), reference it in the instanceconfig.xml file and then stop and start the BI Presentation Server. When the Presentation Server starts and finds its pointing to a blank directory, it’ll automatically create all the folders and system items we need, ready for the migration will then do from the development environment.

6. Now it’s time to start the Catalog Manager tool and unarchive the Users and Shared webcat directories from the development environment. You can choose to keep timestamps and permissions, the latter becomes relevant if you’ve set up a security system in development that you want to copy across to production.

7. The RPD that you copied across in step 5 will contain database passwords (and connection details) that may not be relevant in the production environment. If your DBA allows it, you can open up the RPD file and edit the connection pool settings to reflect the production settings.

There are various techniques around to do this in a scripted way, one of my colleagues has defined the database password as a variable and then updated this via an init block and a text file, another technique is this one by Venkat where he uses an undocumented command line interface to the BI Administration tool to set the database password.

8. Now that all of the files are in place, and you’ve copied the RPD over, set the database password(s) and migrated the web catalog entries, one step you’ll want to consider is to make the production RPD file read-only. This stops inadvertent changes to the RPD in production, though if this is inevitable (for quick fixes etc) you can always make it read-write and apply subsequent changes using the Merge feature recently introduced into the BI Administration tool. If you can though, make the production RPD read-only.

It’s worth taking a copy of the first production RPD at this stage, which we’ll call the “baseline RPD”, as we’ll need this later on if we apply subsequent RPD changes using the three-way merge feature in the BI Administration tool. Take this copy and place it somewhere safe, and we’ll use it at a later date when we start doing incremental updates to the production environment.

9. Now, in order to pick up all the changes you’ve introduced with the migrated files, stop and then restart the BI Server, BI Presentation Server, Javahost and BI Scheduler services, and the BI Cluster Controller if you’re using clustering.

10. You’re now ready to use your system in production. Users can now create new reports in production (within their own User folders, if you ware maintaining the Shared webcat folder in development and plan to overwrite it with each subsequent release), or developers can create reports in the development environment if they are dependent on updates they are making to the RPD, which will be put into production as part of a co-ordinated release.

So that’s how we promote the initial release of the project into production. What about subsequent releases, where we have updates to the RPD to promote and potentially some more shared reports and dashboards? Well, we’ll cover this in the next instalment of the series.

I "met" Mrs. Pedersen on the oracle-l mailing list. I can't remember what the topic was, but she supplied this detailed message full of stats and opinion. I immediately went out and looked for her blog, but couldn't find it. She didn't have one.

So I dropped her a line and offered her some space here.

I won't take (all) the credit, but she has since started her own blog, DBA Kevlar.

Automatic Undo can be one of those areas that make you go "hmmmm..." I am fully aware that the documentation that came with Oracle 9i new features for DBA's when Automatic Undo was introduced clearly claimed, "You won't receive another ORA-01555 again!" yet Oracle hasn't quite lived up to that promise in either 9i or 10g and I haven't had the chance to prove otherwise in 11g.

I had the opportunity to revisit my old friend, "ORA-01555" when one of our environments began to page a fellow DBA with the issue the other day. I became intrigued, as I often am by any database oddity and when I queried the amount of undo the process was consuming, it didn't add up with the amount of undo that was being consumed. In our environment, it is not uncommon for 800GB or more of undo to be utilized, but it became quickly apparent something was amiss when looking at the query below vs. the amount of undo allocation of 1.2TB that had been recently allocated by the DBA on call:

select distinct
  vs.sid, vs.osuser,
  vs.process,
  vs.sql_id,
  round((vu.undoblks*32768) / (1024*1024*1024)) GB,
  vu.tuned_undoretention
from v$undostat vu, v$session vs
where vu.maxqueryid in vs.sql_id
group by
  vs.sid,
  vs.osuser,
  vs.process,
  vs.sql_id,
  vu.undoblks,
  vu.tuned_undoretention
order by vs.sid

TOTAL GB used from the query above: 45GB

The first aspect I chose to take into consideration is the "tuned undo retention", as many folks forget that the Oracle parameter UNDO_RETENTION can be over-ridden by the SMON if a process requires blocks from the undo to stay unexpired over the set retention period. I have found in my experience, that if there is a rollback issue with an existing process, inspecting the TUNED_UNDO_RETENTION is an important area to start as a DBA.

After verifying that there were over 7000 different maxquerid's in v$undostat, but that only 20% of them were returning when we were looking at active sessions, I queried to find out what queries have undo blocks set with expirations greater than the set time, (currently 43200 seconds) that were also not in active sessions.

select distinct 
  vu.maxqueryid, 
  vu.maxquerylen,
  vu.tuned_undoretention 
from 
  v$undostat vu, 
  v$session vs
where vu.tuned_undoretention > 43200
  and vu.maxqueryid not in  vs.sql_id
group by 
  vu.maxqueryid,
  vu.maxquerylen, 
  vu.tuned_undoretention;

One SQL_ID came up as the offender with the majority of undo blocks retained over the retention period and not in active sessions: 1zuumhmqc93zj

The maxqueryid we are looking for isn't active, but if we searched historical SQL, then we found our statement:

select sql_text
from DBA_HIST_SQLTEXT
where sql_id = '1zuumhmqc93aj';

select col1 
from prod.Tbl1 
where cnt = 1

Why would a select statement create undo blocks and how could it be using undo if it isn't a current running process and yet showing active undo blocks???

If the query is not active - what could it be?

Answer: PREVIOUS

select vs.sid
from v$undostat vu, v$session vs
where  vu.max_queryid=’1zuumhmqc93aj’
  and vu.maxqueryid = vs.prev_sql_id;

SID
---
817

We now have the SID currently running that is the cause, why worry about the SQL anymore? It's not the current SQL, but a previous sql_id in an active undo process in v$undostat, so at this point, we need to focus on the SID.

So what transpired here?

Upon inspecting SID 817, it was found to be a background session for Oracle two weeks back where someone had issued a kill session command on a poorly coded process! Oracle decided that the select, which was a full scan on a 59GB table, used a cursor into a for loop, was going to take just over two weeks to rollback the killed session! The process to rollback was so sublime, that it hadn't come up as a red flag on any monitoring script or tool, including OEM!

Since no actual data block changes had been performed, it was perfectly safe to stop the rollback and execute a session kill. To free the undo vs. allowing the kill command to continue, we selected the OS Session from V$SESSION for SID 817, proceeded to kill the process at the OS level and issued a reset on the undo tablespace to clear the remaining undo blocks that were active and/or unexpired:

begin
  reset_undo_stat();
end;

By resetting the undo, we were able to free up the undo that had been tuned to be retained and were able to disregard the rollback on a select statement that had been killed.

Once we reset the undo, we promptly informed the developer that he/she was never to attempt to use a 59GB non-partitioned table without indexes for a cursor ever, ever again!! :)

Moral of this story
If you have significant undo retained by non-active, non-transactional processes, you should inspect them carefully to verify that they are valid utilization of your undo tablespace. If not, you could receive ORA-01555, snapshot too old errors on transactions that would normally complete successfully, causing incomplete transactions, more rollback occurrence and users asking you why their processes didn't finish.

BIO
Kellyn Pedersen is a Performance Tuning and Operations Database Administration Specialist with 10 years of DBA experience in Oracle, SQL Server and MySQL. She is a Database Administrator and Developer at I-Behavior in Louisville, CO, is busy writing articles, sharing tips on her own blog, DBA Kevlar, and will be presenting at RMOUG 2010 in Denver, CO.

She lives in Westminster, CO with her husband and children - who are wonderfully willing to put up with her strange fascination with Oracle's Cost Based Optimizer.

[ kellyn dot pedersen at gmail | Twitter | LinkedIn ]

Tomorrow I'll be leaving for Dallas, TX for a 4 day teaching assignment on Application Express.

I'm a bit nervous for multiple reasons.
1. I've never had a problem (well, not in my 30's anyway) getting up and speaking in front of people. My style is light on slides and heavy on winging it. I can do that because I don't often give talks on things I don't know intimately. Don't get me wrong, I have a basic outline to follow, but I have never liked lectures and tried to avoid them at all costs.
2. Leaving the family. The last time I was away from my family for more than a day was 3 years ago, when I moved down to Tampa. Before that, it was 2002 when I went to OOW. I was homesick both times. Homesickness...is a strange thing for me. I grew up as an only child and moved 8 times...so I was always the new kid with no friends. I went to college 2000 miles away from home (CO --> FL). It wasn't until I had a family did I first experience the phenomenon.
3. I have to get on a plane. Yes, it's irrational...I know this. You are far more likely to die on the roads (in the US anyway). Again, this has something to do with the family thing. A feeling of mortality? I don't know. I've probably flown 100 times in my life, I used to love flying...the people watching in the terminal, the feeling of takeoff, landing...

In fact, when I was at UF, I rode this plane countless times...without fear. (And I swear there was duct tape on the propellors...)



I've just been out of the habit of flying. No more Peter Pan Invincibility syndrome for me I guess.

I know lots of you travel quite frequently and are non-plussed by it. I will be thinking of all the people that I know that travel on a regular basis. It might require a beer or 3 to get on board this "first" time.

4. Almost forgot, I get to meet OracleNude. That could be scary too. :)

I guess this post should have been titled, A Fear of Flying, would have been more appropriate.

Just a quick remind for anyone who’ll be attending the UKOUG Technology and EBS Conference at the Birmingham ICC next week. We’ll be exhibiting at Stand 50, and will also be delivering the following sessions:

• “Oracle BI EE and Essbase Integration Step-by-Step”, Monday 14:45 – 15:45, presented by myself and Venkat Janakiraman
• “Oracle Warehouse Builder 11gR2 New Features”, Monday 16:15 – 17:00, presented by myself
• “Under the covers of ODI and the Oracle BI Applications”, Tuesday 14.30 – 15.30, presented by myself
• “Oracle BI Enterprise Edition Masterclass”, Wednesday 13.50 – 15.50, presented by myself, Venkat, Adrian Ward and Borkur

This year at the Masterclass, as well as tackling the basics of modeling and reporting, we’ll be tackling four of the questions that most often come up from our customers:

• How do we model non-dimensional (OLTP) sources using the BI Administration tool;
• How do we join together fact tables that do not have the same dimensionality;
• How do we model single-table sources, and
• How do we (safely) migrate OBIEE projects from one environment to the other.

Although OBIEE 10g is a fairly mature technology now, these questions always come up and it seems that no-one is too sure about how to approach them. We’ll deliver a session on this (together with a general update on the product) and try and tackle them for you.

We’re also running a BI “Fringe Event” on the Tuesday night at the Pitcher and Piano from 6pm – 9pm, where we’ll put some money behind the bar, lay on some nibbles and try and get all the BI speakers, delegates and users together for an informal get together. We’ll be inviting all those that came to the BI Forum in May and the Training Days in October as a kind of “reunion”, but we’d be happy to have anyone along with something interesting to say on Oracle BI. If you want to come along, please send me an email or come along to Stand 50 so we can get an idea on numbers. Other than that, see you all in Birmingham in a few weeks time.

Database Administrators, Why not amuse and insult your developers at the same time by setting up the following situation: explain plan for select * from dual;   explain plan succeeded.   select * from table(dbms_xplan.display);   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------- Plan hash value: 272002086 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |     2 |     [...]

Home --> Administrative Services

This is the section where you manage your instance wide application attributes. The only functionality that exists in this section, other than mere reporting, is the ability to change an application from Run and Build Application to Run Application Only.



Application Attributes
In the section you can view the following application attributes:

• Workspace
• Application - the number assigned which will be part of the URL
• Parsing Schema
• Application Name
• Last Updated - The user (workspace admin or developer) who last updated this application.
• Date - Time since the last update
• Pages - Number of pages in the application
• Language

Build Status
From here you can view the Build Status of your applications. It will either be Run and Build Application, which means it can be edited by developers and workspace administrators AND viewed by end users. This is typically the status in a non-production environment; or, it will be Run Application Only which is what the status should be in a production environment. It can not be modified in any way but it can be viewed by the end users.



Edit Build Status
The page where you change the Build Status.



Parsing Schemas
Finally, this tells you what the parsing schema is for the application.

I quick comment of mine on the Oracle Forums prompts me to add a further note of explanation here. Many of us are pretty much wedded to the use of sequences to generate primary key values, either by using a trigger to populate the column or by referencing the sequence next value in the insert statement [...]

If you had attended our training days event or one of our Open World sessions, you will have noticed that we had covered the various aspects of integration between Essbase and relational sources using BI EE. The presentations are available here. One of the scenarios that we had not covered was the ability to display relational attributes along with Essbase data. It is very similar to the Horizontal Fragmentation technique we had covered in the training. But there are significant differences which i thought made sense to cover in a separate blog entry.

For example, lets assume that we have the entire SH schema loaded into Essbase. In many cases, not all the attributes of a dimension are loaded into Essbase for a couple of reasons.

1. Load & Retrieval Performance
2. Cube size

If you look at the Essbase outline below, for the product dimension we have just loaded the primary hierarchy.

As you see this outline does not have all the Product attributes like Product Price, Product Pack Size etc. In many cases, such attributes might be maintained in relational sources but may never be loaded inside an Essbase outline. But from a reporting standpoint, we might still need to display these attributes in some cases as shown below

If you notice, Quantity is an Essbase measure and PROD_LIST_PRICE is a relational attribute of a product. The question is how do we model this in the repository in such a way that there is least impact on the queries generated. There are couple of approaches that we can take to model this. I will cover one such approach today.

This modeling technique leverages the concept of conforming dimensions. We start first by including the relational product source table in the Essbase Product dimension as shown below

Then we create another custom logical table called Product Attributes. In this logical table we we can include all those attributes that do not exist in Essbase. This logical table will be modeled as a separate dimension.

Ensure that the source of this new logical table has the same relational table source as the source that we added in the Product dimension. Now create a dummy Fact logical table called Fact-Products which will enforce the conforming dimension join.

The source for this fact logical table will be the same Products relational source. Now, create complex joins as shown below.

For each fact measure in the Essbase source map it to the total level of the Product – Attributes dimension as shown below. This will ensure that BI Server will combine both the sources together in its own memory.

Now while creating a report we need to ensure that we bring in measures from Essbase as well as our dummy fact. The dummy fact enforces BI Server level join at the product level.

If you look at the SQL queries, you will notice that BI Server will fire 2 separate queries. But the joins across the sources will be through the conforming product dimension.

   T2273.PROD_CATEGORY as c2,
     T2273.PROD_SUBCATEGORY as c3,
     T2273.PROD_NAME as c4,
     T2273.PROD_LIST_PRICE as c5,
     sum(1) as c6
from
     PRODUCTS T2273
where  ( T2273.PROD_SUBCATEGORY = 'Accessories' )
group by T2273.PROD_CATEGORY, T2273.PROD_LIST_PRICE, T2273.PROD_NAME, T2273.PROD_SUBCATEGORY, T2273.PROD_TOTAL
order by c1, c2, c3, c4

With
  set [Product4]  as '{[Product].[Accessories]}'
  set [Product5]  as 'Generate({[Product4]}, Descendants([Product].currentmember, [Product].Generations(5),SELF), ALL)'
select
  { [Measures].[Quantity]
  } on columns,
  NON EMPTY {{[Product5]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [SH.SH]

This shows the capability of BI Server in modeling multiple data source scenarios. If you require other dimensions to be part of the report then the dummy fact will have to assigned to the Total level of all the other dimensions (effectively Total levels need to be created in each dimension). For example, the Promotion dimension is shown below

I'm trying to learn about PARALLEL and how it affects SELECT statements in a DW/DSS (non OLTP) environment. I can't really say I know what I'm doing or what I'm looking at, but I'm trying.

I had a DBA strongly discourage me from using PARALLELism on reporting tables (i.e. facts or dimensions). I think he even tried to explain it to me...but it was even more over my head at that time. I think it had something to do with all the processes kicked off then multiplied by the number of users running queries on those tables. "It will bring the database to its knees" he use to say. I never fought much on that because 1, he explained it to me 2, I trusted him and 3, he showed me once all the processes spawned during a typical data load operation. (I thought that was really cool by the way, all those neat little processes doing little chunks of work).

He did however let me (us) use them on staging tables. This is typically where our transformations took place. INSERTs and/or partition swapping.

Anyway, I was reading the 10gR2 docs, the Data Warehousing Guide to be exact on Using Parallel Execution. Lots of neat pictures...which is good for me.



Then I decided to see if I could see what the run times on a SELECT COUNT(*) would be given different levels of parallelism. If you want to know if a table or index has PARALLEL enabled, you need to look at the DEGREE column in %_TABLES and %_INDEXES. I spent about 20 minutes looking for PARALLEL in those tables before I remembered that.

CREATE TABLE big_table 
AS
SELECT *
FROM dba_objects;

I know have 74,431 records to play with. It's not a lot, I know, I just want to see what happens. I then created an anonymous block to:
1. loop through and change the PARALLELism starting with 1 up to 8
2. get the start time from DBMS_UTILITY.GET_TIME
3. loop doing 20 SELECT COUNT(*)s on table table
4. get the end time
5. get the total time
6. print it out
7. put it in a spreadsheet to compare the results side by side

DECLARE
  l_start NUMBER;
  l_end NUMBER;
  l_total NUMBER;
  l_dummy INTEGER;
BEGIN
  FOR j IN 1..8 LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE big_table PARALLEL ' || j;
    dbms_output.put_line( 'PARALLEL (DEGREE):  ' || j );
    FOR i IN 1..20 LOOP
      l_start := dbms_utility.get_time;

      SELECT COUNT(*)
      INTO l_dummy
      FROM big_table;

      l_end := dbms_utility.get_time;

      l_total := l_end - l_start;

      dbms_output.put_line( 'Run ' || LPAD( i, 2, '0' ) || ':  ' || LPAD( l_total, 3, '0' ) );
    END LOOP;
  END LOOP;
END;
/

And here are my results:



Sorry that it's an image...I can't seem to get decently formatted text into Blogger lately.

For this silly, unscientific test, PARALLEL set to 1 (i.e. NOPARALLEL) was the fastest all the way down. I don't know what it means or if it's even valid, but hey, I'm trying. I figured that if I posted something way above my head all you nice folks out there would happily correct me if I mistated something. I am avoiding any statements, if you couldn't tell.

Today I made it official...I filed my paperwork with the State of Florida to incorporate. It seemed fairly obvious what the name should be.

If you read the previous post, you probably gathered that little fact.

All of this is related to the fact that I have a new job. I'm not quite ready to talk about the details yet but I believe there is quite a bit of opportunity here. For one, I'll be working with OBIEE again. It's been about 9 months since I last worked with and I was just getting started. There may also be some work with APEX, which would be great. Finally, there's a very outside shot that I may get to work with Exadata v2 in the somewhat near future.

Maybe you're wondering why I incorporated? I don't know, just seemed like fun. Actually, I'll be working as a consultant. This is my first real trek down that road. I have to learn about billable hours, successful travel planning...and probably most importantly, budgeting.

I'm very excited about this opportunity. I will have to travel a bit, but I'll also get to work from home quite a bit so I believe in the long run, family time will probably be greater than when I was going to that "9 to 5" type job where I was actually away from home more than 60 hours a week. We'll see.

EIN being an Employer Identification Number.

You can find the start page here at irs.gov.

This is the second time I have done this, well, second time I have had an EIN. I'm pretty sure the lawyer did it the first time around. If I remember correctly, that cost me a whopping $500, 5 years ago. Of course that included incorporation as well.

I remember how daunting it was back then, starting your own business. I felt like I had to hire a lawyer to do it. The truth is, it's very easy to do. Especially if you consider the sheer volume of documentation that exists on the Oracle database...this is a piece of cake.

It took 10 minutes to complete, but most of that time was spent doing screen captures. Twenty steps in all.

Landing page from the link above:



Begin Application:



Choose the type of business:



"Sub" Corporations:



Confirm your selection:



Why do you need an EIN?



Principal Officer?



Individual details:



Location of corporation:



Verify physical location:



"Accept Database Version" is funny. Do most people understand what that would mean? I guessed that's the USPS version of my address...but I tend to work with these types of scenarios.

Corporation details:



More about the corporation:



What does your corporation do?



I selected other because there was no IT type category on there.

What kind of Other?



What kind of Consulting?



How do you want this delivered?



Summary:



EIN assigned!



Additional information:



Thank you!



Pretty easy all in all. I bet is was a bit harder 5 years ago (i.e. not automated).

Amusingly, this form is only available at certain times of the day. I tried this morning and was told the online assistant didn't work until 7 PM on Sundays. WTF?

Since no where I go seems to have heard of Application Express, I have to demo it (locally) and then convince the DBAs to install it. As such, I do tell them that the APEX Administrator (account) has very powerful privileges, including CREATE TABLESPACE.

I don't think it ever truly registered what this means. In talking to my friend and (current) colleague Daniel McGhan (APEX nerd extraordinaire), he reminded me that when you create a new workspace in APEX that it creates a new tablespace as well (if you don't set "Re-user existing schema?" to "Yes") which means a datafile is also created.



Of course I had to see if for myself:

CJUSTICE@TESTING>SELECT file#, name FROM v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------
         1 /u01/app/oracle/oradata/testing/system01.dbf
         2 /u01/app/oracle/oradata/testing/sysaux01.dbf
         3 /u01/app/oracle/oradata/testing/undotbs01.dbf
         4 /u01/app/oracle/oradata/testing/users01.dbf
         5 /u01/app/oracle/oradata/testing/example01.dbf

5 rows selected.

That's what I currently have. I'll create a new workspace now to demonstrate. Starting from the Manage Workspace page I select Create Workspace:



I give it a name, APEX_TEST:



On Step 2, I set "Re-user existing schema?" to "No"



Step 3:



Step 4, confirming details:



Confirmation page:

CJUSTICE@TESTING>SELECT file#, name FROM v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------
         1 /u01/app/oracle/oradata/testing/system01.dbf
         2 /u01/app/oracle/oradata/testing/sysaux01.dbf
         3 /u01/app/oracle/oradata/testing/undotbs01.dbf
         4 /u01/app/oracle/oradata/testing/users01.dbf
         5 /u01/app/oracle/oradata/testing/example01.dbf
         6 /u01/app/oracle/oradata/testing/FLOW_1172420773490155.dbf

6 rows selected.

As you can see, the datafile FLOW_1172420773490155.dbf was created which is mapped to the like named FLOW_1172420773490155 tablespace.

This can obviously be controlled by selecting "Yes" for "Re-user existing schema?", but I just thought it important enough to point out.

James came up with a cunning crosstab report question on the forum this week. Does not look that bad right? Sadly the new crosstab builder can not build what we need. Notice there is no summarization in the grid; just... Tim Dexter

The thing that sets Dodeca apart from all of our competitors is architecture. We spent much time and effort looking at the problems potential customers were seeing with the products they were using. Based on what we learned, we engineered Dodeca to minimize or eliminate those issues. Our primary goal was to create a software product that would be so flexible and functional that we would rarely need to say no to even the most extraordinary user requests. Believe me, some of those requests can be very extraordinary! After man-years of work, I am glad to report that we have met this goal and increased ease of use.

Ease of use means different things to different people. For end users, ease of use means they can explore their data and get the information they need in very obvious ways with only the slightest bit of training. For administrators, it means they can configure the system easily and quickly deliver applications to their users. Finally, for IT departments, the deployment and on-going maintenance of an application must be painless.

One key feature of the Dodeca architecture is the modular design that allows us to very easily plug in new functionality. This modular concept runs deep within Dodeca. In fact, Essbase functionality in Dodeca is a plug-in module. The plug-in concept also allows customers to remove functionality from their distribution. For example, if a customer wants to use only relational databases with Dodeca, they can simply delete the Essbase module files in their deployment.

A second key design aspect of Dodeca is its metadata driven nature. Everything that can be configured within Dodeca is stored as metadata, in a relational database, on our server. When a piece of metadata is required at runtime, it is requested by the Dodeca client via web services and returned, as XML, over the network via HTTP. Metadata transmitted this way includes report definitions, spreadsheet templates, Essbase connection information and toolbars. Similarly, relational data and Essbase data, including outline information, is transmitted via web services.

The use of XML adds significant flexibility to Dodeca. As an added benefit, it makes in-place upgrades easy. We have customers with thousands of users who have upgraded their Dodeca version in about 15 minutes. XML also gives Dodeca flexibility on the Essbase side. A single version of Dodeca works with every version of Essbase from 6.5.3 to the latest 11.1.1.3; we even support multiple retrievals, from any supported version, into the same report.

The modular architecture and the metadata driven aspects of Dodeca also work together. Some customers and partners, such as greenEPM, have leveraged our modular architecture to build extended functionality on the Dodeca Framework. Extensions are built in Visual Studio using C# or VB.Net and packaged using XML descriptors. Custom modules in Dodeca are treated as metadata and are delivered to the client via web-services. We believe this is a unique aspect of our product and is the subject of one of our pending patents.

This week, we received notice from our Intellectual Property attorneys that our patent application has been published; it will be quite a while before it is rewarded. It was a large time investment to complete the patent application, but, hopefully we will be patent-holders in the near future.

The elections are over for the ODTUG (Oracle Development Tools User Group) board of directors. Here are the new board members that will be serving in 2010-2011:

• Jeff Jacobs
• John King
• Monty Latiolais
• Bambi Price
• Mark Rittman

They join the carry-over board members who were serving in 2009-2010:

• John Jeunette
• Barbara Morris
• Marc de Oliveira
• Mike Riley

What the keen eyed among you will immediately notice is that Tim Tow was not re-elected. Apparently, it was a very close election and Tim fell just a couple of votes shy. Oracle EPM fans should not be totally disheartened, though, because Mark Rittman was newly elected. Mark is well known in the EPM/BI community. He will do a fine job representing the EPM/BI/Hyperion needs on the board. I've known Mark for a couple of years and I can vouch that he's a decent human being. (I refuse to be friends with anyone who kicks puppies or kittens. Well, okay, I'll be friends with them on Facebook but not IRL. Point is, Mark doesn't kick any small mammals.)
Tim Tow will serve out the rest of his term through December of this year. I know I speak for not only myself but the entire Hyperion community when I say, thank you, Tim, for all the hard work you've put into ODTUG. Hyperion developers, administrators, and users finally have a place to call home again, and it was your tireless service that helped get us there. Sincerely, we appreciate it.
If you want to say thank you to Tim as well, nothing says you appreciate his service like buying a copy of Dodeca from AppliedOLAP.

Details to follow.

Shout out here for Hussein over on the ADF Dev to Dev blog. Nice article covering some of the BIP APIs and how to use them within an ADF project. http://husaindalal.blogspot.com/2009/11/integrating-bi-publisher-standalone.html Thanks to Jurgen and Klaus for finding Hussein and... Tim Dexter

Just a quick pointer to an answer on the Oracle Forum:  http://forums.oracle.com/forums/thread.jspa?messageID=3912341#3912341 If you are merging into a data warehouse fact table (not so likely) or a large aggregation of a fact table (more likely) then you can improve efficiency by adding a transitive predicate into the USING clause to promote partition pruning on the target table. Unless [...]

Home --> Administrative Services --> Manage Service

As I mentioned previously, this is probably the most important area for administering your APEX instance.


Messages

There are 2 types of messages you can create, one is the Login message and one is the System Message



The Login message appears at the top of the Workspace Login screen:



The System messages appears after you have logged in.



Feature Configuration

• Application Development
  
  
  
  
  1. Allow PL/SQL Program Unit Editing - This pertains to the SQL Workshop. Will you allow end-users (Developers/Administrators), who have access to SQL Workshop, to edit PL/SQL.
  2. Create demonstration objects in new workspaces - By default, when you create a new workspace, the sample application is automatically installed in the schema that it is mapped to. In my case, CJUSTICE. There are database objects included with this object as well. If you would like to see those database objects and need a script to remove them, read here.
     
     I don't like that it gets created automatically so I typically turn it off.


• SQL Workshop
  
  
  
  
  1. SQL Commands Maximum Inactivity in minutes - "Identify the maximum amount of time a transactional command in the SQL Command Processor waits before timing out."
  2. SQL Scripts Maximum Script Output Size in bytes - "Identify the maximum amount of output a single SQL Script can generate. SQL scripts are run from the SQL Workshop."
  3. SQL Scripts Maximum Workspace Output Size in bytes - "Identify the maximum amount of space all scripts within a workspace may consume. SQL Script results are the output generated when running SQL Scripts from the SQL command processor."
  4. SQL Scripts Maximum Script Size in bytes - "Identify the maximum size of a SQL script used within the SQL Workshop."
  5. Enable Transactional SQL Commands - "Enable Transactional SQL Commands for the entire instance. Enabling this feature permits users of SQL Commands to issue multiple SQL commands within the same physical database transaction."


• Monitoring
  
  
  
  1. Enable Database Monitoring - "Enable or disable monitoring within the SQL Workshop."
  
  
  Honestly, I have no idea what kind of monitoring is done here. I haven't been able to find any related reports...which doesn't mean they don't exist, just that I can't find them.

Security

• Security
  
  


1. Set Workspace Cookie
2. Disable Administrator Login - Defaults to No (otherwise, how would you log in?)
3. Disable Workspace Login - Defaults to No
4. Allow Public File Upload
5. Restrict Access by IP Address


• HTTPS
  
  


1. Require HTTPS - Use a comma delimited list of IP Addresses to restrict, can be either 1 or up to 4 values


• Session Timeout
  
  


1. Maximum Session Length in Seconds
2. Maximum Session Idle Time in Seconds


• Excluded Domains
  
  


1. Domain Must Not Contain - "Enter domains (not including the port), separated by colons, that should not be allowed in regions of type URL or accessed as a Web service. "


• Account Login Control
  
  


1. Require User Account Expiration and Locking - "Select Yes to enable Application Express user account expiration and locking features across all workspaces. This selection prevents the same feature from being disabled at the workspace level.
   
   Select No to allow individual workspaces to enable or disable this feature independently.
   
   Application Express user account expiration and locking features apply to end-user accounts created using the Application Express end-user account management interface."
2. Maximum Login Failures Allowed - "This setting applies to accounts used to access the Application Express administration and development environment only. It does not apply to end-user accounts used by applications developed in Application Express.
   
   However, this setting is used as the default workspace-level "Maximum Login Failures Allowed" preference, which workspace administrators can change. The workspace-level preference applies to the end-user accounts within that workspace."
3. Account Password Lifetime (days) - Self Explanatory


• Workspace Password Policy
  
  


1. Minimum Password Length - Self Explanatory
2. Minimum Password Differences - "Enter the number of differences required between old and new passwords. The passwords are compared character by character, and each difference that occurs in any position counts toward the required minimum difference.
   
   This setting applies to accounts for workspace administrators, developers, and end use"
3. Must Contain At Least One Alphabetic Character - Self Explanatory
4. Must Contain At Least One Numeric Character - Self Explanatory
5. Must Contain At Least One Punctuation Character - Self Explanatory
6. Must Contain At Least One Upper Case Character - Self Explanatory
7. Must Contain At Least One Lower Case Character - Self Explanatory
8. Must Not Contain Username - Self Explanatory
9. Must Not Contain Workspace Name - Self Explanatory
10. Must Not Contain - colon seperated list of words that cannot be used in the password, default words are: "oracle:hello:welcome:guest:user:database"
11. Alphabetic Characters - default: "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
12. Punctuation Characters - default: "!"#$%&()``*+,-/:;<=>?_"



• Service Administrator Password Policy
  
  
  
  
  1. Policy - You can either define the Workspace Password Policy (above) or just use the default strong password policy. For my sandbox versions, I tend to make it as lax as possible so I do not have to remember complex passwords when putting things like this together.

Instance Settings

For most organizations, the only 3 areas you need to worry about here are Email, Report Printing and Wallet.

• Self Service
  
  


1. Provisioning Status - 3 options here: Manual, Request and Email Verification. For most organizations Manual will do just fine. That will allow your end-users to follow the normal protocols set forth in your organization. For hosted sites, like apex.oracle.com, Email Verification is the way to go.
2. Development Service URL - Used in conjunction with Request or Email Verification above.



• Email Provisioning
  
  



1. Email Provisioning - By default, is is enabled, but without the other settings it is useless. I turn this off...but it doesn't really matter for me since I use a sandbox.


• Schema
  
  



1. Require New Schema - Do you want to require the user to obtain a new schema or reuse an existing one?


• Email
  
  



1. SMTP Host Address - This is where you set up your internal mail server. All APEX mail calls will use this server by default.
2. SMTP Host Port - Self Explanatory
3. Administration Email Address - Self Explanatory
4. Notification Email Address - Self Explanatory


• Wallet
  
  
  
  
  1. Wallet Path - Path to the wallet on the database server.
  2. Wallet Password - Self Explanatory


• Report Printing
  
  
  
  
  1. Print Server - Standard is the Default. To enable pdf printing you need BI Publisher and this is where you enter the server information.
  2. Print Server Protocol
  3. Print Server Host Address
  4. Print Server Port
  5. Print Server Script
  
  For a How To on setting up APEX and BI Publisher, check out the RittmanMead blog entry Oracle ApEx and BI Publisher.


• New Workspace Request Size
  
  
  
  If using the Self Service module, this is where you'll set the sizes (and the default) of the workspaces to choose from. You can enable them all or just a few.


• Workspace Change Request Size
  
  
  
  Just like the New Workspace Request Size section, you can modify the size of workspaces you are offering.