With the release of OBIEE 12c we got a number of interesting new features on the front-end. We’re all talking about the cleaner look-and-feel, Visual Analyzer, and the ability to create data-mashups, etc.
While all this is incredibly useful, it’s one of the small changes you don’t hear about that’s truly got me excited. I can’t tell you how thrilled I am that we can finally save a column back to the web catalog as an independent object (to be absolutely fair, this actually first shipped with 220.127.116.11).
For the most part, calculations should be pushed back to the RPD. This reduces the complexity of the reports on the front-end, simplifies maintenance of these calculations, and ultimately assures that the same logic is used across the board in all dashboards and reports… all the logic should be in the RPD. I agree with that 110%… at least in theory. In reality, this isn’t always practical. When it comes down to it, there's always some insane deadline or there’s that pushy team (ahem, accounting...) riding you to get their dashboard updated and migrated in time for year end, or whatever. It’s quite simply just easier sometimes to just code the calculation in the analysis. So, rather than take the time to modify the RPD, you fat finger the calculation in the column formula. We've all done it. But, if you spend enough time developing OBIEE reports and dashboards, sooner or later you’ll find that this is gonna come back to bite you.
Six months, a year from now, you’ll have completely forgotten about that calculation. But there will be a an org change, or a hierarchy was updated… something, to change the logic of that calculation and you'll need make a change. Only now, you know longer remember the specifics of the logic you coded, and even worse you don’t remember if you included that same calculation in any of the other analyses you were working on at the time. Sound familiar? Now, a change that should have been rather straightforward and could have been completed in an afternoon takes two to three times longer as you dig through all your old reports trying to make sense of things. (If only you’d documented your development notes somewhere…)
Saving columns to the web catalog is that middle ground that gives us the best of both worlds… the convenience of quickly coding the logic on the front-end but the piece of mind knowing that the logic is all in one place to ensure consistency and ease maintenance.
After you update your column formula, click OK.
From the column dropdown, select the Save Column As option.
Save the column to the web catalog. Also, be sure to use the description field. The description is a super convenient place to store a few lines of text that your future self or others can use to understand the purpose of this column.
As an added bonus, this description field is also used when searching the web catalog. So, if you don’t happen to remember what name you gave a column but included a little blurb about the calculation, all is not lost.
Saved columns can be added from the web catalog.
Add will continue to reference the original saved column, so that changes to made to the saved column will be reflected in your report. Add Copy will add the column to your report, but future changes to the saved column will not be reflected.
One thing to note, when you add a saved column to a report it can no longer be edited from within the report. When you click on Edit Formula you will still be able to see the logic, but you will need to open and edit that saved column directly to make any changes to the formula.
Try out the saved columns, you’ll find it’s invaluable and will greatly reduce the time it takes to update reports. And with all that free time, maybe you’ll finally get to play around with the Visual Analyzer!