Feed aggregator

Conditional Formatting of Calculated Items in OBIEE 11g

Chet Justice - Tue, 2013-08-13 15:24
By Victor Fagundo

Calculated items in OBIEE Pivot tables can be very useful in certain reporting circumstances, either for ease of development, or to meet specific report requirements. While calculated items in OBIEE are easy, and flexible, they do have one important drawback: they take on the data and display formatting of the fact column they are calculated against.

The most common case is the calculation of a % change across a time dimension in financial reporting ( Year over Year, Quarter over Quarter, etc.). 1   This type of calculation usually takes the form of a percent change calculation similar to below:

 (( $2 - $1 ) / $1) *100 

By default, if you perform this calculation against a numerical fact ( sales, customers) you will run into the problem of how to display the % change in the correct format, since the calculated % will want to take the form of the fact it is calculated against, as can be seen in the example 2 below:

Figure 1 - Pivot Table

Figure 2 - Calculated item

Figure 3 - Results

Not very pretty at all.

As people searched for a work around to this problem 3 common solutions have arisen:
  1. Use HTML formatting tricks to “hide” trigger text in the results, then conditionally format off those triggers. While inventive, as the comments note, this solution falls flat if the report is ever printed, as the PDF engine will pick up and display all of the hidden characters.

  2. Convert the pivot table to a regular table with some complex column formulas. Very time consuming and cumbersome, would also not solve the requirement of showing the dimension values noted as noted in Footnote1.

  3.  Convert the calculated result to text and manually add your formatting characters. I don’t think this actually works since the calculated fields won’t accept logical SQL functions, and this would be very cumbersome.
Now with 11g providing conditional formatting that allows you to override the default data format, this is possible via the following steps:
  1. Add a column that is a COUNT DISTINCT on the dimension that you are calculating across ( in the displayed example, “Time T05 Per Name Year”. This column will serve as your “trigger” to apply your conditional formatting.

    Figure 4 - Column Formula

  2. For each of your facts, apply a conditional format that is triggered when the above column value is zero. In the formatting, apply whatever visual and data formats you desire. In this example we will format the data as a percent, with one decimal place.

    Figure 5 - Condition

    Figure 6 - Format when condition is met
  3. Exclude the “trigger” column from your pivot view. View your results and be satisfied:

    Figure 7 - Correct formatting of calculated item.

* Note that this would also allow you to apply visual formatting if you wanted to distinguish this row/column as a total.

Why it worksThe use of conditional formatting that applies a data type as part of the format is a straightforward leap of logic, but what to use as the trigger? Most people will try to use the dimension they have setup the calculation in. However, if you try to use the text description given to the calculated item you will find that the condition is never applied:

Figure 8 - Condition on dimension
Figure 9 - Condition never met, format never applied
If you try to setup a filter that is true when the dimension is not in reasonable range of values ( in this example we try to format off all years not in the 2000s ) you will find that your calculated item is skipped as well (this has the added vulnerability of being very explicit):

Figure 10 - Condition on dimension values

Figure 11- Condition never met, format never applied

The reason for all this is that the calculated item “borrows” EACH of the dimension values it operates against. Hence, no matter how inventive your filter is, as long as you are trying to somehow separate the calculated member away from the members it is operating on, you will never succeed. This member “borrowing” is apparent if you add the dimension it operates against to the query a 2nd time, and look at the table view.

Figure 12 - Calculated item "borrows" members
But since the “member value” given to the calculated item does not actually exist in the dimension, if you try to perform a count distinct against it, you will always get zero.

Figure 13 - Count distinct against dimension
There is your difference; there is your “trigger.” The rest is basic formatting.

1: You might suggest that this requirement is better served using column(s) with time series calculations, and you might be right. However, more often than not the user will want to SEE the time periods being compared ( 2012 vs 2011, or 08/07/2012 vs 08/07/2011). When using facts with time series calculations you will only be able to show “this year” vs “last year” since the column heading of the time series calculated fact will always be static. In these cases you will need to use the base fact and a time dimension, along with the solution provided here.

2: All screen shots, and examples used in this post are performed in Sample App V305. An XML of the final correctly formatted report can be downloaded here.
Categories: BI & Warehousing

WebLogic Admin Cookbook Review

Antony Reynolds - Tue, 2013-08-13 14:42
Review of Oracle WebLogic Server 12c Advanced Administration Cookbook

Like all of Packts cookbook titles, the book follows a standard format of a recipe followed by an explanation of how it works and then a discussion of additional recipe related features and extensions.

When reading this book I tried out some of the recipes on an internal beta of 12.1.2 and they seemed to work fine on that future release.

The book starts with basic installation instructions that belie its title.  The author is keen to use console mode, which is often needed for servers that have no X11 client libraries, however for all but the most simple of domains I find console mode very slow and difficult to use and would suggest that where possible you persuade the OS admin to make X11 client libraries available, at least for the duration of the domain configuration.

Another pet peeve of mine is using nohup to start servers/services and not redirecting output, with the result that you are left with nohup.out files scattered all over your disk.  The book falls into this trap.

However we soon sweep into some features of WebLogic that I believe are less understood such as using the pack/unpack commands and customizing the console screen.  The “Protecting changes in the Administration Console” recipe is particularly useful.

The next chapter covers HA configuration.  One of the nice things about this book is that most recipes are illustrated not only using the console but also using WLST.  The coverage of multiple NICs and dedicated network channels is very useful in the Exalogic world as well as regular WLS clusters.  One point I would quibble with is the setting up of HA for the AdminServer.  I would always do this with a shared file system rather than copying files around, I would also prefer a floating IP address to avoid having to update the DNS.

Other chapters cover JDBC & JMS, Monitoring, Stability, Performance and Security.

Overall the recipes are useful, I certainly learned some new ways of doing things.  The WLST example code is a real plus.  Well worth being added to your WebLogic Admin library.

The book is available on the Packt website.

RMAN 12c – More SQL without ‘sql’

alt.oracle - Mon, 2013-08-12 19:03
Up until version 12c, the RMAN command line parser was capable of parsing certain DML and DDL statements.  To do this, we use the “sql” command from the RMAN command line, like this.

11gHost> rman

Recovery Manager: Release - Production on Wed Aug 7 15:29:19 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target
connected to target database: TEST2 (DBID=1111111111)

RMAN> sql 'create table altdotoracle (col1 number)';
sql statement: create table altdotoracle (col1 number)

RMAN> sql 'insert into altdotoracle values (1)';
sql statement: insert into altdotoracle values (1)

However, one operation we could not do was SELECT.  The RMAN parser lacked the facility to return arrays of data to the screen.  This shouldn’t be surprising, considering that RMAN is intended to be used to run backup and restore operations.

RMAN> sql 'select * from altdotoracle';
sql statement: select * from altdotoracle

The statement does not error, but no data is returned.  Now, in version 12c, we get two new benefits in this area.  First, we no longer need to use the ‘sql’ command.  For example…

12cHost> rman

Recovery Manager: Release - Production on Wed Aug 7 15:35:27 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target
connected to target database: TEST1 (DBID=1212121212)

RMAN> create table altdotoracle (col1 number);
Statement processed

RMAN> insert into altdotoracle values (1);
Statement processed

Additionally, the RMAN parser in 12c allows us to run SELECT commands, as well as DESCRIBE commands.

RMAN> select * from altdotoracle;


RMAN> describe altdotoracle

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

The ability to use the ‘sql’ RMAN command is still supported for backward compatibility.  A simple thing, but useful!
Categories: DBA Blogs

I'm available

Catherine Devlin - Sun, 2013-08-11 21:11

I'm available for hire! If you need a database expert with lots of programming skill, or a Python programmer with deep database experience, please check out:

Resume / portfolio

But: you must be telecommute-friendly, or in the Dayton area. I'm sorry, but I'm not available to relocate.

Coherence 12.1.2 Rest application build with OEPE

Edwin Biemond - Sun, 2013-08-11 16:20
With WebLogic 12.1.2 Oracle also released a new version of Coherence and OEPE. The 12.1.2 release contains many new Coherence features like WebLogic Managed Coherence Servers and Coherence Grid Archive ( GAR ) which can be included in an normal EAR. Coherence also has some nice new REST features like direct & named queries,  Custom Query engines and new Security options. Plus with OEPE you can

Oracle BI Publisher Desktop Microsoft Visual Basic System Error &H8000FFFF -2147418113 Catastrophic failure

Gareth Roberts - Thu, 2013-08-08 19:50

Not too sure what I changed on my laptop, possibly just installation of another product or perhaps Microsoft or similar updates, but when I went to edit a BI Publisher RTF template I got the following error message on clicking ANY button in the BI Publisher plugin for Microsoft Word.

Microsoft Visual Basic
System Error &H8000FFFF (-2147418113). Catastrophic failure 
OK   Help   

After quite a bit of researching, I found the solution, for me it was the second of the following fixes:

1. Delete the EXD files. I attempted this in the root of my C:\ and D:\ drives, but it didn't fix the error.

DEL /S /A:H /A:-H *.EXD

2. Change security setting, and then reboot Windows.

2a. For Windows 32bit:

Regsvr32 "C:\Windows\System32\MSCOMCTL.OCX"

2b. For Windows 64bit:

Regsvr32 "C:\Windows\SysWOW64\MSCOMCTL.OCX"

Problem solved!

Catch ya!


This is a post from Gareth's blog at http://garethroberts.blogspot.com

ReferencesRelated Posts

Measuring the time left

Robert Baillie - Thu, 2013-08-08 10:27
Burn-down (and burn-up, for that matter) charts are great for those that are inclined to read them, but some people don't want to have to interpret a pretty graph, they just want a simple answer to the question "How much will it cost?" That is if, like me, you work in what might be termed a semi-agile*1 arena then you also need some hard and fast numbers. What I am going to talk about is a method for working out the development time left on a project that I find to be pretty accurate. I'm sure that there are areas that can be finessed, but this is a simple calculation that we perform every few days that gives us a good idea of where we are. The basis.It starts with certain assumptions: You are using stories.OK, so they don't actually have to be called stories, but you need to have split the planned functionality into small chunks of manageable and reasonably like sized work. Having done that you need to have a practice of working on each chunk until its finished before moving on to...

Pleasing line

Robert Baillie - Thu, 2013-08-08 10:27
Gotta admit, I'm quite pleased with this line from my new ORM object based database connection library... $oFilter = Filter::attribute('player_id')->isEqualTo('1')->andAttribute('fixture_id')->isEqualTo('2');

The Happiness Meter

Robert Baillie - Thu, 2013-08-08 10:27
As part of any iteration review / planning meeting there should be a section where everybody involved talks about how they felt the last iteration went, what they thought stood in the way, what they though went particularly well and suchlike. We find that as the project goes on, and the team gets more and more used to each other, this tends to pretty much always dissolve into everyone going "alright I suppose", "yeah fine". Obviously, this isn't ideal and will tend to mean that you only uncover problems in the project when they've got pretty serious and nerves are pretty frayed. This is where "The Happiness Meter" comes in. Instead of asking the team if they think things are going OK and having most people respond non-committally, ask people to put a value against how happy they are with the last iteration's progress. Any range of values is fine, just as long as it has enough levels in it to track subtle movements. I'd go with 1-10. You don't need strict definitions for each...

Ideas for improving innovation and creativity in an IS department

Robert Baillie - Thu, 2013-08-08 10:27
At our work we've set up a few 'action teams' to try to improve particular aspects of our working environment. The team that I'm a member of is responsible for 'Innovation and Creativity'. We're tasked with answering the question "How do we improve innovation and creativity in IS?" - How we can foster an environment that encourages innovation rather than stifles it. As a bit of a background, the company is a a medium sized (2,500 plus employees) based mainly in the UK, but recently spreading through the world, the vast majority of whom are not IS based. The IS department is about 100 strong and includes a development team of 25 people. It's an SME at the point where it's starting to break into the big-time and recognises that it needs to refine its working practices a little in order to keep up with the pace of expansion. We met early last week and have put together a proposal to be taken to the senior management tier. I get a feeling it will be implemented since our team...

A reading list for our developers

Robert Baillie - Thu, 2013-08-08 10:27
An idea I'm thinking of trying to get implemented at our place is a required reading list for all our developers. A collection of books that will improve the way that developers think about their code, and they ways in which they solve problems. The company would buy the books as gifts to the employees, maybe one or two every three months. Some questions though: Is it fair for a company to expect its employees to read educational material out of hours? Conversely: Is it fair for an employee to expect to be moved forward in their career without a little bit of personal development outside the office? If anyone has any books out there that they'd recommend - please let me know. Otherwise, here's my initial ideas - the first three would be in your welcome pack: Update:Gary Myers came up with a good point, being that any book should really be readable on public transport. That probably rules out Code Complete (although I read it on the tube, I can see that it's a little tricky),...

Database Build Script "Greatest Hits"

Robert Baillie - Thu, 2013-08-08 10:27
I know its been a quiet time on this blog for a while now, but I've noticed that I'm still getting visitors looking up old blog posts. It's especially true of the posts that relate to "The Patch Runner". Many of them come through a link from Wilfred van der Deijl, mainly his great post of "Version control of Database Objects". The patch runner is my grand idea for a version controlled database build script that you can use to give your developers sandbox databases to play with as well as ensuring that your live database upgrades work first time, every time. It's all still working perfectly here, and people still seem to be interested, so with that in mind I've decided to collate them a little bit. basically provide an index of all the posts I've made over the years that directly relate to database build scripts, sandboxes and version control. So, Rob's database build script 'Greatest Hits': On the basic idea of building a database in an agile mannerOn how you might start...

Can a change in execution plan change the results?

Robert Baillie - Thu, 2013-08-08 10:27
We've been using Oracle Domain indexes for a while now in order to search documents to get back a ranked order of things that meet certain criteria. The documents are releated to people, and we augment the basic text search with other filters and score metrics based on the 'people' side of things to get an overall 'suitability' score for the results in a search. Without giving too much away about the business I work with I can't really tell you much more about the product than that, but it's probably enough of a background for this little gem. We've known for a while that the domain index 'score' returned from a 'contains' clause is based not only on the document to which that score relates, but also on the rest of the set that is searched. An individual document score does not live in isolation, rather in lives in the context of the whole result set. No problem. As I say, we've known this for a while and so have our customers. Quite a while ago they stopped asking what the...

Handy "Alert Debugging" tool

Robert Baillie - Thu, 2013-08-08 10:27
One of the coolest things about OO Javascript is that methods can be written to as if they are variables. This means that you can re-write functions on the fly. Bad for writing maintainable code if you're not structured; Fantastic for things like MVC controllers (rather use the controller to forward calls on to the model, you use it to rewire the view so that it calls it directly, and all without the view even realising it!). What I didn't realise was that the standard window object (and probably so many others out there) can have its methods overwritten like any other. Probably the simplest example of that proves to be incredibly useful... changing the alert function so that the dialog becomes a confirm window. Clicking cancel means that no further alerts are shown to the user. Great for when you're writin Javascript without a debugger and have to resort to 'alert debugging'. window.alert = function(s) { if( !confirm(s) ) window.alert = null; } In case you're wondering... I...

Remote workforces and the 12 golden questions

Robert Baillie - Thu, 2013-08-08 10:26
I had an interesting conversation with a friend the other day about the difficulties in managing a remote team. That is a team who aren't all located in the same office. Some may be home workers, some may work in different offices.  The main crux of the discussion was around how you turn a group of people into team, garner some emotional connection between them, and to you and your company, and then get the best out of them. After a few days of gestation and rumination it came to me. The rules are the same as with a local team - you may do different things and the problems may be more difficult to overcome, but the techniques you use are fundamentally the same. That thinking led me back to Marcus Buckingham's fantastic book "First Break all the Rules". If you manage people and haven't read this book - shame on you. It is a must read. One of the main arguments in the book revolves around a set of questions you should ask of your staff defined by years of research by Gallup based...

Oracle 12c - Out With the Grid, In With the Cloud

alt.oracle - Wed, 2013-08-07 15:52
So Oracle database 12c is out now.  The cloud (and hence the “c”) is hanging over us.  At least we don’t have to hear about “the grid” anymore.  New versions of Oracle are funny things – as DBAs, we’re kinda excited about the possibilities, but then reality sets in and we realize that our company isn’t going to upgrade for a long time.  That’s not necessarily a bad thing – some of us have experienced the pain of the “release 1’s” that have come out in recent years.  There are generally early adopters and then everyone else.  Over the course of my time as a DBA, I’ve swung from wanting to be on “the bleeding edge” to saying “we’re not upgrading until the terminal release!”  Reality is somewhere in the middle – every release probably has something useful to us.  Inevitably, what I see is that new versions come out and DBAs don’t get familiar with the new features.  Let’s be honest – so many features seem oriented towards selling something that we miss the good ones.  But understanding a new feature set can be extremely useful.  I’m not promising anything, but 12c could have that one new thing that could make your life so much easier that you eventually can’t live without it.  Who could really deny the importance of stuff like ASM and the AWR that came with version 10g?  Of course, reading through the actual new features documentation can be about as pleasant as giving yourself a root canal.  So, since I’ve combed through some of the docs, I wanted to spare you the pain and give you some of the good stuff.  Over the next few posts, we’ll take a look at some new features in 12c – I think they could be useful, so maybe you will too.  All of the meat and none of the fat!
Categories: DBA Blogs

Personal Edition for Linux - 11gR2 and 12c

Hans Forbrich - Tue, 2013-08-06 12:13
See MOS Note "How to Install Oracle Database Personal Edition on Linux?" (Doc ID 1574396.1)
Thank you Oracle.
Categories: DBA Blogs

My book: Oracle Database 12c Backup and Recovery Survival Guide

Oracle NZ - Mon, 2013-08-05 18:15

It was a long journey of hard work and team work that now has finished, as a result you will find my book Oracle Database 12c Backup and Recovery Survival Guide that I wrote with the help of my good friend Aman Sharma.

I would also take this opportunity to recognize the great work done by many good friends involved in the technical review of the book:

  • Tom Kyte
  • Arup Nanda
  • Robert Freeman
  • Laurent Schneider
  • Gokhan Atil
  • Wissem El Khlifi
  • Bjorn Naessens
  • Alessandro Parisi

Thank you so much my friends for all comments, suggestions, corrections and support during this long journey!

You can pre-order the book here: http://www.packtpub.com/oracle-database-12c-backup-recovery-survival-guide/book


Here is some information about the book:

The three main responsibilities of a successful DBA are to ensure the availability, recoverability, and performance of any database. To ensure the recoverability of any database, a DBA needs to have a strong backup and recovery skills set. Every DBA is always looking for a reference book that will help them to solve any possible backup and recovery situation that they can come across in their professional life.

Oracle Database 12c Backup and Recovery Survival Guide has the unique advantage to be a reference to all Oracle backup and recovery options available, making it essential for any DBA in the world. If you are new to Oracle Database, this book will introduce you to the fantastic world of backup and recovery that is vital to your success. If you are an experienced DBA, this book will become a reference guide and will also help you to learn some possible new skills, or give you some new ideas you were never aware about. It will also help you to easily find the solution to some of the most well known problems you could find during your career as a DBA. This book contains useful screenshots, scripts, and examples that you will find more than useful.

Most of the books currently available in the market concentrate only on the RMAN utility to backup and recovery. This book will be an exception to the rule and will become a must-have reference, allowing you to design a real and complete backup and recovery strategy. It covers the most important topics on Oracle database such as backup strategies, Nologging operations, new features in 12c, user managed backups and recoveries, RMAN (including reporting, catalog management, troubleshooting, and performance tuning), advanced data pump, Oracle Enterprise Manager 12c and SQL Developer.

“Oracle Database 12c Backup and Recovery Survival Guide” contains everything a DBA needs to know to keep data safe and recoverable, using real-life scenarios.



Copyright © OracleNZ by Francisco Munoz Alvarez [My book: Oracle Database 12c Backup and Recovery Survival Guide], All Right Reserved. 2016.
Categories: DBA Blogs

Oracle's Internet of Things platform

Anshu Sharma - Mon, 2013-08-05 17:35

Internet of Things is a new market opportunityfor many Enterprise Software vendors. We just launched our web site around Oracle's Internet of Things platform which is acollection of Oracle's products that makes developing & managing IoTApplications easier. This is an evolving field and developments will happen ata fast pace. I will spend time discussing IoT in my future blog posts. Pleaseavail below opportunities to get the dialogue going with us -

OPN Webcasts

1) Internet of Things Platform for Smart City, HomeAutomation, Industrial Automation, and Telecommunication  (Sep 5) http://event.on24.com/r.htm?e=663587&s=1&k=313ED7C22DF039171FF48196322185E8

2) Fast Data:Connecting High Velocity Data to Value (Sep 12) http://event.on24.com/r.htm?e=663633&s=1&k=6EE08D56CAC07AA7551478FE344DC39

If you are attending Oracle OpenWorld SF 2013 (Sep 22-26), these sessions would be useful -

  • Internet of Things Platform: AnIn-Depth Look at the Role of Oracle Fusion Middleware [CON9867]
  • Internet of Things Platform forSmart City, Home and Industrial Automation, and Telcos [CON9848]
  • Unlocking the Potential of M2M: TheOracle Device-to-Data-Center Platform [CON8573] (Canon)
  • How Big Data and M2M CreateCompetitive Advantage in the Communications Industry [CON9616]
  • Big Data for Remote Services andMaintenance [CON9821]
  • Innovations in Oracle’sConcept-to-Cash Solutions [CON10061] (Telematics)
  • Fast Data for a Safer City[CON2072] (NEC)

If you are attending JavaOne then these sessionswould be useful –

  • Internet of Things with Java [CON4877] ,JES & OEP for Robust, Data-Intensive Applications [CON8405]
  • Optimized Architecture for M2M Solutions[CON5763] (Gemalto)
  • People Counting: A Case Study of Javaand OSGi in Connected M2M Systems [CON5811] (Eurotech, Hitachi)
  • Home Automation in Action [CON7773] (Prosyst)
  • JMS, WebSocket, and the Internet ofThings: Controlling Physical Devices on the Real-Time Web [CON3914] (Kaazing)

Android Update: 4.3

Dietrich Schroff - Sat, 2013-08-03 14:38
This week my Nexus 7 came up with this notification:
I read about that Google launched 4.3, so i tapped on this notification and:
And this time Google provides some information about this release.
One really nice thing is, that with 4.3 it is possible to create restricted user accounts and you can grant these accounts access to specific apps, which you already installed. This is exact the thing you need, if you want to create accounts for your children without app store access and without creating them e-mail accounts....


Subscribe to Oracle FAQ aggregator