Feed aggregator

Agent blocked....

Bas Klaassen - Fri, 2010-08-06 04:29
In our 11g Grid Control I noticed an agent that was not uploading any data to the oms anymore.When checking the status of the agent I noticed the following:Last successful upload : (none)Last attempted upload : (none)Total Megabytes of XML files uploaded so far : 0.00Number of XML files pending upload : 199Size of XML files pending Bas Klaassenhttp://www.blogger.com/profile/04080547141637579116noreply@blogger.com4
Categories: APPS Blogs

APEX 4.0 Enhancements: Validating Form Data

Anthony Rayner - Thu, 2010-08-05 07:48
Oracle Application Express 4.0 introduces lots of big new features; websheets, dynamic actions, plug-ins, RESTful web services, team development, updated charts, the list goes on. But there are also many enhancements to existing functionality that we hope will help to simplify the overall process of developing applications in APEX. One such area, and the focus of this post is how data is validated. This post will give you an overview of what's changed with validations and how these changes will make your daily development life a little easier.


Item-Centric Validation
Historically in APEX if you want to validate data input on a page, you create a validation. The validation is a separate component that you need to define and maintain. Now in APEX 4.0, the actual item can handle some simple validation of the data it receives. For example, all item's (both native to APEX and plug-ins) now have a 'Value Required' attribute. By setting this to 'Yes', APEX will automatically validate a value has been entered and raise an error if not, no separate validation required.

Additional to this 'Value Required' validation, some item types also validate their data, based on how the item is defined. For example, the new 'Number' item type, which you can use for handling numeric data contains settings for 'Minimum Value' and 'Maximum Value'. When these are defined, APEX will automatically validate the data received based on these settings and raise appropriate errors.

Settings for the new 'Number' item, settings highlighting are automatically validated

The new datepicker item in APEX 4.0 also supports this type of automatic validation. Additional to the 'Value Required' setting (available for all items), the datepicker also allows setting 'Format Mask', 'Minimum Date', 'Maximum Date' and 'Year Range'. In doing so, APEX will again automatically validate the data received based on these settings and raise appropriate errors.

Settings for the new 'Date' item, settings highlighting are automatically validated

Plug-in items may also contain automatic validations, depending on whether the plug-in author has coded in this support.

You also have a couple of ways of customising the error message that is displayed by APEX when these validations fail, in terms of content and position. To override the default error messages, please refer to this section of the user guide. This details all of the 'Text Messages' that you would need to define in your applications to override the defaults. If you want to change where the error is displayed on screen, please see the 'Default Error Display Location' attribute available via 'Edit Application Properties' on the application homepage.

Finally, debug mode has also been enhanced to show when these validations are executed and whether they passed or failed.

Item based validations offer a more logical approach to validating data and requires fewer moving parts, which means less to define and less to maintain. Of course, for other more complex situations a separate validation will still be required, but this greatly simplifies some of the more common, simple scenarios.


Button-Centric Validation Exclusion
Again, historically in APEX if you don't want a validation to fire when certain buttons are pressed, you would define that logic in the validation. Let's take an example. If you have a typical 'Form' page used for inserting, updating and deleting data, you may want your validations to fire for insert and update, but not for delete. This would involve going through each validation and setting some condition such as where REQUEST != 'DELETE' or similar, to prevent the validation from firing. Now, in APEX 4.0, the actual button can be defined to either 'Execute Validations' or not.

Specify that pressing the button should not cause any validations to fire.

This is much easier, all you need to do is set this at button level and that's it, no item, plug-in or custom validations will fire. Wizard created forms will set this up for you automatically, so when creating these types of forms, the 'Create' and 'Save' buttons execute validations and the 'Delete' and 'Cancel' buttons do not.

You can also override this at validation level by setting the 'Always Execute' validation attribute to 'Yes' (defaults to 'No'). This could be useful for example if you want to always execute a security check, regardless of any button exclusions.

Debug mode has again been enhanced here to show if validations are prevented from firing because of the button setting.


Tabular Form Validations
APEX 4.0 now also supports declarative validation of tabular form data. Before APEX 4.0, there was no declarative support for validating this type of form and you would have to do a lot of manual PL/SQL to validate your data. Currently, tabular form validations only support a subset of what's available with page item validation, but do cater for some of the more common scenarios (required values, type checks and string comparisons). We are looking to extend this in a future release of APEX.


Error Message Label Placeholders
This is small but one of my favourites. When defining an error message that displays when a validation fails, if the validation is associated with a specific page item, you can now use the #LABEL# placeholder to dynamically reference the associated item's label.

Use #LABEL# instead of hard-coding the associated item label text.

So instead of having to duplicate the label text in the error message (and have to remember to change it if you change the item's label) as was historically the case, just use the #LABEL# placeholder and that's it. Again, less to define and less to maintain. An equivalent placeholder is also available for the new tabular form validations, #COLUMN_HEADER#.


Upgrading Applications
So finally, what about your existing APEX applications that have been long since built, where you want to take advantage of some of these new features. Well, take a look at the 'Upgrade Application' feature available via the 'Utilities' menu from the application homepage. This assists you in upgrading your application to use some of the new features in APEX 4.0.

Of particular relevance to validations are the following upgrade types:
  • Update Text Field Item to Number Field Item, where appropriate - Locates where you have an unconditional 'Is Numeric' validations on 'Text Field' items and upgrades them to use the 'Number' item type with in-built numeric checking. Also removes the now redundant separate validation.
  • Update Value Required item attribute to Yes, where appropriate - Locates where you have unconditional 'Not Null' validations on items and sets those item's 'Value Required' attribute to 'Yes'. Also removes the now redundant separate validation.
  • Numeric, Required and Date Picker Item updates based upon conditional validations - Just locates where you have conditional validations for 'Is Numeric', 'Not Null' or 'Is Valid Date' on 'Text Field' items, for your manual review. So you can determine if the validation can be replaced with some item settings and button exclusions.


So quite a few little enhancements that hopefully add up to easier and more intuitive data validation. Good luck with your new APEX 4.0 style validations and let us know what you think!

Many thanks to Patrick Wolf for reviewing this post and filling in the gaps.
Categories: Development

One Million

Robert Vollman - Wed, 2010-08-04 16:51
Today, August 4th, shortly after lunch, ThinkOracle had it's one millionth visitor. Care for a stroll down memory lane?I started this site May 16, 2005, shortly after starting a new position with a company that made financial software. The idea was to make my own contribution to the growing Oracle community, expand my knowledge, improve my technical writing, and it never hurts to establish a Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com4

Note to Newbies: Know when it's time to call Oracle Support

Lisa Dobson - Wed, 2010-08-04 07:50
This thread on the OTN forums caught my eye today.Whilst I love the forums and think they are a great place to go for help and advice, there are times when they are not the best medium for support and this was one of them.Forum questions are answered by volunteers, people who have day jobs to do themselves, that are happy to share their experience and help out others with queries. That’s why you Lisahttp://www.blogger.com/profile/16434297444320005874noreply@blogger.com7

What will Oracle buy next?

Lisa Dobson - Tue, 2010-08-03 14:26
Stephen Jannise of ERP Software Advice has written an interesting article around likely targets for Oracle's next acquisition.The article is well written and well thought out, being based on research into the past 5 years of Oracle acquisitions alongside a study into the current market. There's also a diagram to show all of the companies that Oracle has acquired since the PeopleSoft acquisition Lisahttp://www.blogger.com/profile/16434297444320005874noreply@blogger.com2

APEX 4.0 - Do you want to know more?

Anthony Rayner - Tue, 2010-08-03 07:32
Do you want to ask the Vice President of Database Tools at Oracle and original developer of Application Express a question about Oracle Application Express 4.0?

Mike Hichwa is going to be interviewed by Oracle Profit Magazine on APEX, so if you have something you want to ask, questions are being collected for consideration via Twitter. Tweet your questions to @OracleProfit, with the hash tag #askprofit. Selected submissions will receive a 1GB flash drive — and be printed in the November issue of Profit Magazine.
Categories: Development

Changing User's Default Schema

Robert Vollman - Mon, 2010-08-02 20:30
Last week I got a question about changing a user's default schema.My colleague is supporting a typical database application which is configured to use the user/schema that was created for its database. Many queries were written for this application that use that schema owner, but my colleague would like to run those queries with his own account instead - either because he doesn't want to log in Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2

We have moved!

Oracle Optimizer Team - Sat, 2010-07-31 12:57
You might have been wondering why things had gone so quiet on the Optimizer development team's blog Optimizer Magic over the last few months. Well the blog has moved to blogs.oracle.com/optimizer. All of the old articles have moved too and we plan to be a lot more active at our new home, with at least one new post every month.


We have moved!

Inside the Oracle Optimizer - Sat, 2010-07-31 12:57
You might have been wondering why things had gone so quiet on the Optimizer development team's blog Optimizer Magic over the last few months. Well the blog has moved to blogs.oracle.com/optimizer. All of the old articles have moved too and we plan to be a lot more active at our new home, with at least one new post every month.


Categories: DBA Blogs, Development

Dell and HP to Certify and Resell Oracle VM, Oracle Enterprise Linux, and Oracle Solaris

Sergio's Blog - Fri, 2010-07-30 02:42
Those of you that follow us on twitter/ORCL_Linux probably already saw this.   HP and Dell yesterday announced that they'll be certifying and reselling Oracle VM, Oracle Enterprise Linux and Oracle Solaris on their x86 servers.
Categories: DBA Blogs

Quick script to maintain a diary

Vattekkat Babu - Fri, 2010-07-30 01:30

I like to keep my daily notes in a folder in the filesystem with filenames yyyymmdd.otl, using VIM Outliner. Here is a small DOS script to make a file for a day if it doesn't exist and then open it. Name it as diary.cmd and keep in your path.

RDBMS events

Fairlie Rego - Thu, 2010-07-29 23:36
RDBMS events are often used to do additional tracing and for debug purposes.
Most of them are listed in $ORACLE_HOME/rdbms/mesg/oraus.msg
One such event I use quite often to determine which locks/enqueues a session is requesting is the following.
For example the below trace indicates that an innocuous looking query on v$flash_recovery_area_usage takes a controlfile lock in mode 4 which might not be the best thing to happen on a high throughput multi node RAC environment with a huge number of flashback logs.
SQL> alter session set events '10704 trace name context forever, level 10';

Session altered.

SQL> oradebug setmypid
Statement processed.

SQL> oradebug
tracefile_name /u01/app/oracle/diag/rdbms/TEST/TEST1/trace/TEST1_ora_600.trc
SQL> select * from v$flash_recovery_area_usage;


*** 2010-07-30 10:07:33.978
ksqgtl *** CF-00000000-00000000 mode=4 flags=0x1a011 timeout=900 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
ksqlkdid: 0001-0036-00000169

*** 2010-07-30 10:07:33.978
*** ksudidTrace: ksqgtl
ksusesdi: 0001-0036-00000168
ksusetxn: 0001-0036-00000169

*** 2010-07-30 10:07:33.978
ksqcmi: CF,0,0 mode=4 timeout=900
ksqcmi: returns 0
ksqgtl: RETURNS 0

*** 2010-07-30 10:07:33.978
ksqgtl *** CF-00000000-00000004 mode=4 flags=0x10010 timeout=0 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
ksqlkdid: 0001-0036-00000169



Another event I have used in the past is related to parallel query to determine why PQ slaves do not get spawned
But to my surprise this event does not work anymore in 11.2

SQL> alter session set events '10392 trace name context forever, level 1';
Session altered.

SQL> oradebug setmypid
Statement processed.

SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/TEST/TEST1/trace/TEST1_ora_14748.trc

SQL> select /*+ parallel(a,8) */ count(*) from sys.obj$ a;

COUNT(*)
----------
231692

SQL> !cat /u01/app/oracle/diag/rdbms/TEST/TEST1/trace/TEST1_ora_14748.trc

*** 2010-07-30 14:41:02.547
*** SESSION ID:(316.58074) 2010-07-30 14:41:02.547
*** CLIENT ID:() 2010-07-30 14:41:02.547
*** SERVICE NAME:(SYS$USERS) 2010-07-30 14:41:02.547
*** MODULE NAME:(sqlplus@bart.au (TNS V1-V3)) 2010-07-30 14:41:02.547
*** ACTION NAME:() 2010-07-30 14:41:02.547

Processing Oradebug command 'setmypid'

*** 2010-07-30 14:41:02.547
Oradebug command 'setmypid' console output:

*** 2010-07-30 14:41:08.598
Processing Oradebug command 'tracefile_name'

*** 2010-07-30 14:41:08.598
Oradebug command 'tracefile_name' console output:

The trace does not contain any information

Feedback from Oracle was that “not many people use the px numeric ones and so they removed the code.”…
You can still use the _px_trace underscore parameter to determine why queries are not running in parallel

Oracle scene editor

Neil Jarvis - Mon, 2010-07-26 05:46
Just to show that I do keep this blog up to date I thought I'd announce that the voting for the next Oracle Scene editor will shortly be opening for all UKOUG members. I am standing for this position being the technical editor for the last 3 yrs

CP7 for 10.1.2.3 released

Michael Armstrong-Smith - Mon, 2010-07-26 00:34
Just wanted to let you know that on June 4, 2010, Oracle has released CP7 for 10.1.2.3. You will find it on MetaLink as patch number 9112482. When compared to CP6, 9 bugs have been fixed.

So far this cumulative patch has been released for the following platforms:
  • HP-UX PA-RISC (64-bit)
  • Microsoft Windows 32-bit
  • Linux x86 (works for both 32 bit and 64 bit)
  • Oracle Solaris on SPARC (64-bit)
If you are upgrading to CP6 from any patch level prior to CP4 then JDBC patch patch p4398431_10105_GENERIC.zip for bug 4398431(release 10.1.0.5) needs to be installed before you apply CP5.

This patch needs to be applied to all Oracle Homes, i.e. Infrastructure home as well as all related midtier homes.

Bug 4398431 - HANG WHEN RETRIEVING A CONNECTION FROM THE IMPLICIT CONNECTION CACHE

The following posting has been updated:

CP6 for 10.1.2.3 released

Michael Armstrong-Smith - Mon, 2010-07-26 00:31
Just wanted to let you know that on November 18, 2009, Oracle has released CP6 for 10.1.2.3. You will find it on MetaLink as patch number 8746296:. When compared to CP5, 19 enhancements or bugs have been fixed.

So far this cumulative patch has been released for the following platforms:
  • HP-UX Itanium
  • HP-UX PA-RISC (64-bit)
  • IBM AIX on POWER Systems (64-bit)
  • Microsoft Windows 32-bit
  • Linux x86 (works for both 32 bit and 64 bit)
  • Sun Solaris SPARC (32-bit)
If you are upgrading to CP6 from any patch level prior to CP4 then JDBC patch patch p4398431_10105_GENERIC.zip for bug 4398431(release 10.1.0.5) needs to be installed before you apply CP5.

This patch needs to be applied to all Oracle Homes, i.e. Infrastructure home as well as all related midtier homes.

Bug 4398431 - HANG WHEN RETRIEVING A CONNECTION FROM THE IMPLICIT CONNECTION CACHE

The following posting has been updated:

Report filtering with APEX 4.0, dynamic action style

Anthony Rayner - Wed, 2010-07-21 02:45
I've just got back from the APEX SIG event in Birmingham. As ever, the event was well organised and attended and it's always good to catch up with the extremely knowledgable and enthusiastic UK APEX community. If you haven't yet attended one of these events then I'd definitely recommend them, you will undoubtedly learn something new, make some great contacts and enjoy the day! The next event is planned for 3rd November at the Oracle City Office in London, so if you're interested in coming along, please see here.

Down to business. During the event I was asked whether dynamic actions in APEX 4.0 can handle Ajax based filtering of report data. The answer is yes, so thought I'd make the most of the train journey home to blog about how. The approach is slightly different depending on whether you're dealing with classic or interactive report regions. In this post, I will detail the steps to achieve this with Interactive Report Regions (IRRs), fully declaratively without coding 1 line of JavaScript.


You can also see this working in my sample application here. This example shows a report on the EMP table, with quick filters available to filter by department or job via Ajax, partial page refresh.

The following steps assume that you have the EMP and DEPT tables in your current schema.


With IRRs, the basic approach involves the following 4 steps:
  • Create a page with a report region, ensuring the page item filters are referenced in the SQL.
  • Create the page items for filtering.
  • Create the dynamic action to fire whenever any of the page item filters change value, to refresh the IRR.
  • Define the IRR to save these items values in session state after it is refreshed.

Now let's walk through these steps in detail.
  • Create the page
    1. Within an application, click the 'Create Page' button.
    2. Select 'Report', click 'Next'.
    3. Select 'Interactive Report', click 'Next'.
    4. For 'Page Attributes', enter 'Employee Report' for 'Page Name' and 'Region Name'. Importantly, you must also ensure that the 'Region Template' is set to some template that contains the #REGION_STATIC_ID# substitution string, otherwise this example will not work (dynamic actions need this ID to be able to perform the refresh). Most of the new themes now default to an appropriate template when creating new IRRs (as defined by the new Theme 'Region Default' for 'Interactive Reports'). However if you're using an old theme then you may need to select an appropriate template (try 'Region without Buttons and Title'). Also make a note of the page number (I'll assume page 1 for these steps) and click 'Next'.
    5. For 'Tab Attributes', leave as default and click 'Next'.
    6. For 'Enter a SQL SELECT Statement', enter the following query (referencing the page number noted in step 4 when defining the item names) and click 'Next':


      select e.empno,
      e.ename,
      d.dname,
      e.job,
      e.hiredate,
      e.sal,
      e.comm
      from emp e,
      dept d
      where e.deptno = d.deptno
      and e.deptno = nvl(:P1_DEPTNO, e.deptno)
      and e.job = nvl(:P1_JOB, e.job)
    7. Click 'Finish'.
    8. For purposes of this example, we're going to switch off the standard 'Search' bar to keep things clean. You can do this by right clicking on the 'Employee Report' in tree view and selecting 'Edit Report Attributes'. Then in the 'Search Bar' region, select 'No' for 'Include Search Bar' and 'Apply Changes'.
    If you now click 'Run Page', you'll see the basic report page as been created. We now need to add the page item filters.
  • Create the page items
    1. Go to edit the page. We'll create these items in a separate 'Quick Filters' region above the IRR. In 'Tree View', right click on the region position where the IRR has been created (by default this is 'Body (3)' and select 'Create'.
    2. Select 'HTML' and click 'Next'.
    3. Select 'HTML' and click 'Next'.
    4. For 'Title', specify 'Quick Filters', for 'Region Template' select 'Report Filter - Single Row' and amend the 'Sequence' to be say 5, so that this appears before the IRR on the page, click 'Next'.
    5. For 'Region Source', leave blank and click 'Create Region'. This has created a blank region, that will be the container for our filter items. We'll create 2 filters, to filter the employee report by the job and department columns respectively.
    6. In 'Tree View', right click on the 'Quick Filters' region and select 'Create Page Item'.
    7. Select 'Select List', click 'Next'.
    8. For 'Item Name', enter 'P1_DEPTNO', click 'Next'.
    9. For 'Item Attributes', accept defaults and click 'Next'.
    10. For 'Settings', accept defaults and click 'Next'.
    11. Ensure 'Display Null Value' is 'Yes'.
    12. For 'Null Display Value' enter '- Show All -'.
    13. Leave 'Null Return Value' leave as blank, this will be treated as a true null by APEX.
    14. For 'List of Values Query', enter the following query and click 'Next':


      select dname d,
      deptno r
      from dept
      order by 1
    15. For 'Source' accept defaults and click 'Create Item'.
    16. Now for the job select list. In 'Tree View', right click on the 'Quick Filters' region and select 'Create Page Item'.
    17. Select 'Select List', click 'Next'.
    18. For 'Item Name', enter 'P1_JOB', click 'Next'.
    19. For 'Item Attributes', accept defaults except for the 'Begin on New Line' attribute. We want to set this to 'No', so that the filters appear on the same line in the 'Quick Filters' region. Click 'Next'.
    20. For 'Settings', accept defaults and click 'Next'.
    21. Ensure 'Display Null Value' is 'Yes'.
    22. For 'Null Display Value' enter '- Show All -'.
    23. Leave 'Null Return Value' leave as blank, this will be treated as a true null by APEX.
    24. For 'List of Values Query', enter the following query and click 'Next':


      select distinct job d,
      job r
      from emp
      order by 1
    25. For 'Source' accept defaults and click 'Create Item'.
    If you run the page now, you'll see all the basic page components are created, the regions and the items, but selecting different values from the filters has no effect. We now need to bring these to life.
  • Create the dynamic action
    1. Go to edit the page. Right click on the 'Dynamic Actions' tree node and select 'Create'.
    2. Select 'Advanced' for the 'Implementation'. We need 'Advanced' here because 'Standard' only supports the 'Show', 'Hide', 'Disable' and 'Enable' actions.
    3. For 'Name', enter 'QUICK FILTER REFRESH', click 'Next'.
    4. For 'When', leave the event as 'Change' and for 'Item(s)', enter 'P1_DEPTNO,P1_JOB'. Leave 'Condition' as 'No Condition'. These selections define the dynamic action will fire whenever either the department or job select list's value changes. Click 'Next'.
    5. For 'Action', select 'Refresh' under the 'Component' category in the select list. The 'Refresh' action currently supports IRRs, classic reports, all item types with cascading LOV support and may also support item or region plug-ins, depending on whether the plug-in author has coded the plug-in to support this (the plug-in documentation should state if this is supported). Charts in APEX 4.0 are not yet supported.
    6. Ensure the 'Fire on Page Load' checkbox is unchecked, there is no need to refresh the report when the page loads, it's already fresh. Click 'Next'.
    7. On the 'Affected Elements' page, we'll define what will be refreshed. Select 'Region' from the 'Selection Type' select list and 'Employee Report' from the 'Region' select list. Click 'Create'.
    If you now run the page, you'll notice that this still isn't working as expected. Actually, the dynamic action is firing, the report is being refreshed, but it is not being scoped by the filter selection. The problem is the values for the filter page items are not being saved to session state and are therefore not set when the report's SQL is executed. This is easy to fix.
  • Define the IRR to save these items values in session state after it is refreshed
    1. Right click on the 'Employee Report' and select 'Report Attributes'.
    2. Go the 'Advanced' and for 'Page Items to Submit', enter 'P1_DEPTNO,P1_JOB'. Click 'Apply Changes'.


So that's it, run the page and you will now see the filters are fully functional. Select different jobs and departments and see the report refresh, showing employees scoped by your selections. This example could be easily extended to have different item types used as the filters, not just select lists. A common example would be a slider component where you select a value or range of values, upon which the report is refreshed with rows specific to your selection. I will try and add that to my sample application when I can.

For classic reports, this is slightly more complicated than as outlined above, as there is no 'Page Items to Submit' attribute (yet!!), so you need to save the values in session state in a slightly different fashion. I will try and add that too when I can. (Hint: Instead of defining the IRR 'Page Items to Submit' as detailed in the last bullet, use the 'Execute PL/SQL Code' action within the dynamic action, to fire before the 'Refresh' action. Just leave the code as 'null;' and set the 'Page Items to Submit' attribute to your page items. This will work, but unfortunately issues 2 Ajax calls to do so, which is not optimal.)

Also, if you're interested in seeing more examples of refreshing reports with dynamic actions, I have a couple of others in the sample application:
  1. Refresh - Showing an alternative approach for user's to select their 'Saved Reports' in IRRs. Instead of using the default select list in the search region, this has been switched off in favour of a separate report on the left of the page containing the saved reports available to the current user. This report also shows a tooltip for the report description (if defined), when hovering over the report name. The dynamic action is used to refresh this saved report list, so if a user saves a new report or deletes a report, the report list is updated. Again this is all via Ajax and does not require any manual JavaScript coding.
  2. Refresh 2 - Showing how report row deletion can be handled via Ajax with dynamic actions. This example does require a couple of places within dynamic actions where a line of JavaScript is required.

To learn more about these examples, please download the application, install in your workspace and have a closer look.
Categories: Development

A first look at OBIEE 11g with Oracle OLAP

Keith Laker - Tue, 2010-07-20 16:53
For those who missed it, the global launch for the 11g release of the Oracle Business Intelligence Enterprise Edition suite (OBIEE) took place in London on July 7th.

And the fantastic news for Oracle OLAP customers is that OBIEE 11g will work out-of-the-box with Oracle OLAP in almost exactly the same way as OBIEE 10g does - with just one additional configuration step required to enable the new OLAP-style front-end functionality.

Of course, there are other features that are relevant such as the WebLogic application server, and the new security model, but these have already been well blogged elsewhere so the focus of this posting will be Oracle OLAP integration.

To illustrate how easy it is, I will use a trusted old friend as a starting point - the 11g Global sample schema. I have installed this in an Oracle 11.2 database instance, created an Oracle OLAP Analytic Workspace, and then refreshed this AW so that the dimensions and cubes are built.

With an AW in place, the next step is to use the OBIEE plug-in for AWM to generate the metadata required for the OBIEE Server. For those who have not used the plug-in before, check out this excellent demonstration of how it works. While this particular version of the plug-in was originally released to work with OBIEE 10g, and presumably an updated version will be released in due course, it can be used in exactly the same way in OBIEE 11g to import metadata into the Administration tool.



And at first glance, aside from a few updated icons, this version of the Administration tool looks very similar, but the biggest change related to the administration of OLAP data sources (relational or MOLAP) is the ability to map hierarchy objects right through into the presentation layer.

Here is the Metadata generated by the plug-in for the Channel Dimension in both the Business Model and Presentation layers


The new 11g OLAP-style front-end functionality is enabled by adding these hierarchies into the Presentation layer too. This can be achieved by a simple click-and-drag for each hierarchy like the following which is again for the Channel Dimension



Hopefully, the next release of the plug-in will handle this additional step automatically (and also provide support for value-based hierarchies which were not supported by the front-end in OBIEE 10g) but in the meantime it really is just a simple click-and-drag for each dimension.

Once all the hierarchies are mapped through into the Presentation Layer, the cube is ready to query. I can log into the OBIEE 11g home page and create a new analysis based upon my Oracle OLAP subject area. The new hierarchies are available for selection when I construct a query




I can then select all of the 'columns' I need for my query and view the results as a pivot table. Here is a really simple example showing Sales by Time. I have also added some calculated measures which have been created inside the AW and derive really useful analytics from the Sales measure. This is a classic reason for using the OLAP Option in the first place - it facilitates the easy creation of calculations that are difficult (or often impossible) to express in SQL. And by having them embedded in the cube, the only thing that the SQL tool (in this case OBIEE) needs to do is select the calculation as a field in a view. How easy is that?!



Once a pivot table with Hierarchy-based columns has been created, this is where the new front-end features really come into play. Some highlights include Calculated Items (derived Dimension members) and a new Selector (which allows dimension selections to be built up as a series of steps based upon add/keep/remove logic):



For those familiar with Discoverer OLAP, or Sales Analyzer, Financial Analyzer and Express Objects/Analyzer, these aren't exactly revolutionary features, but combined with all the other great features of the OBIEE suite, this is now a very compelling platform for your Oracle OLAP data.

Finally, I would guess that there are probably thousands of old Oracle Express/OLAP systems that have been waiting for a BI platform like this. If you work on one, what are you waiting for?

***OBIEE 11g can now be downloaded from OTN***
Categories: BI & Warehousing

RCU:6107 DB Init Param error

Habib Gohar - Tue, 2010-07-20 08:09
While Installing Repository Creation Utility (RCU) Installation the following error occurs: RCU:6107 DB Init Param Error This can be removed simply by the following: 1. Login on your database with system user. 2. Write > show parameters processes (which will show the current value of processes). 3. If its value is less than 500 then write […]

Micropayments opening gate to the fortune at the bottom of the pyramid

Khanderao Kand - Mon, 2010-07-19 16:29
Late C. K. Prahalad wrote a path breaking book showing to the world that there are great opportunities in the emerging as well as poor nations. Those potential markets would need innovation to realize.

When he wrote The Fortune at the Bottom of the Pyramid, he may not have thought about VoiceSMS and voice blogging.


But he does mentioned about the innovation. And such innovation is in action in the form of bubbleMotion http://www.bubblemotion.com/ which has more than 1.2 million paid users who pay 0.65 USD per month for blogging / accessing voice.

Loosely typed interfaces - The Normalizer Pattern and Oracle SOA Suite

Peter O'Brien - Mon, 2010-07-19 15:56
When getting two or more systems working together, the making a connection part is generally the easiest, identity propagation is a bit trickier, but many times, the syntax and semantics of one system is at odds with another one. Over the decades this has been manifest in many ways and thankfully there are tools and techniques to work through them. A collation of such techniques is Enterprise Integration Patterns. The book, along with the associated website, is worthy of a dedicated article or two, and is not the real subject of this article. One message transformation pattern the book describes is the Normalizer Pattern where different formats for essentially the same object are catered for.
An example where this form of message translation becomes necessary is when using a service with a loosely typed interface. This can often happen when an existing system or utility (such as a batch / command processor) gets exposed as a web service. The request / response payload is little more than a collection of key / value pairs. There are a couple of ways to cater for this using Oracle SOA Suite 11g which I will outline in this article. One has full tool support. The other, a solution using XSLT, is not fully supported, but has an advantage in that the XSLT can be used both with the BPEL and Mediator components.

Troublesome Payloads
The challenge is to get something that is strongly typed into something that is loosely typed
Strongly TypedLoosely Typed
<department>
<name>Furniture</name>
<manager>Pam Beech</manager>
<budget>50000</budget>
<spent>20000</spent>
<committed>47000</committed>
</department>

<property>
<key>NAME</key>
<value>Furniture</value>
</property>
<property>
<key>MANAGER</key>
<value>Pam Beech</value>
</property>
<property>
<key>BUDGET</key>
<value>50000</value>
</property>
<property>
<key>SPENT</key>
<value>20000</value>
</property>
<property>
<key>COMMITTED</key>
<value>47000</value>
</property>
Now this is quite a simple example and the real world has a lot more complicated cases out there. Ones that would involve number formatting, character encoding, attribute concatenation, and so on. For the purposes of this exercise however, it is ideal to illustrate the point. All the files in this example are free and available for you to use, modify or incorporate into your own solution as you see fit. The example was produced and tested using Oracle Fusion Middleware 11.1.1.2.0. That is JDeveloper Studio 11.1.1.2.0 and WebLogic Server 11gR1. To reduce the number of files and dependencies involved, the example is a simple service (BPELProcessExample.wsdl) that takes a strongly typed request payload and returns a loosely typed response payload. The SOA composite project contains two BPEL processes that provide slightly different implementations for this same WSDL.

BPEL assign with copy and insertAfter
You can cater for the key / value pair structure by repeatedly appending a new 'property' element and then, using an XPATH predicate to specify which 'property' element is the target, copying the desired values. In the BPEL process this can all be done in a single 'assign' command, using the insertAfter instruction from the Oracle BPEL extension library. The BPEL designer supports this approach, and it is used extensively in production systems around the globe. It can get difficult to maintain and hard to read if the payload is large though. If dealing with a large payload see if you can split the copy / insertAfter instructions into two or more 'assign' commands. Ideally, you would give these 'assign' commands logical names such as 'assign_part_1_admin_details' and 'assign_part_2_finance_details'. What is a large payload in this case then? Well, I would say that if you have more than 10 key value pairs you should be seriously thinking about splitting them up and if you have more than 20, you should be definitely splitting them up. Remember, you might be familiar with the structure and understand how it is constructed, but what about the college graduate brought in by the consulting firm in a months time, or even you having to come back to this in 6 months time? Go on, make the maintenance a bit easier for everyone and break that assign behemoth into manageable chunks!

BPEL transform with XSLT
If experienced in dealing with XML documents then this approach will probably be very familiar to you. Extensible Stylesheet Language Transformations is used to convert XML data into some other format, which may also be XML data. In this case the stylesheet explicitly describes the target XML structure, making it somewhat easier to read than the BPEL assign approach described earlier. For example...
<client:property>
<client:key>
<xsl:text disable-output-escaping="no">MANAGER</xsl:text>
</client:key>
<client:value>
<xsl:value-of select="/client:process/client:department/client:manager">
</xsl:value-of>
</client:value>
</client:property>
The 'client' is a namespace prefix for the schema and in this SOA composite example the request and response happen to be defined in the same schema. This is easier to read, even if one is not all that familiar with XSL. For large payloads it is not necessary to split up the transformation as one might consider for the BPEL assign, but it could be done. The single transformation file could have a whole set of templates. However, the XSL Mapper does not support this approach yet, so if using this approach you will have to work in the Source editor, not the Design mode. Since this is using XSLT, the same XSL file can be used by the Mediator too, so it is not only a BPEL solution.

Which one is right for me?
As one can see from the diagrams there is not much difference in the BPEL designer between the two examples and the decision on which technique to use would be based on factors which are different from company to company, but clearly use of the XSL mapper is an important factor.
The complete example application with SOA composite is available at http://sites.google.com/site/soastation/soastation_looselytyped_normalizer.zip

Pages

Subscribe to Oracle FAQ aggregator