We know that we can use the evaluate function to call any function stored on the database and pass arguments to it. What if we have a requirement to use different database functions for the same column based on different selections made by the user. This article talks about a method to handle this requirement.
We had a requirement of tracking a query fired from OBIEE. This was to help us find out the Analysis that consume the most database resources. It is possible that some user might be selecting a huge date range resulting in the problem at the database. So the trouble in this case might be the filters put by the user and not the Analysis itself.
We had a strange requirement. The user did not want the dashboard to be published and he hated the fact that he had to expand the dashboard to see the pages in it. Since the requirement is strange so will be the solution.
While we all know about using conditions on sections in a dashboard, and while we all have used presentation variables to show or hide a report using these conditions, this blog talks about a process to use conditions on sections to implement a 3 way decision rather than just a boolean (true/false) decision.
We had a requirement in which the a column had either pure numbers such as 123 or pure alphabets such as QWED. The requirement was to display 'Y' in a column if the value was a number and display 'N' if it was a character string.
SQL query is often used as a data source in BIP and often we have a requirement in which we have to use one table when the user selects a certain value in a parameter and select a totally different table when the user selects another value in the parameter. While we have discussed a method to do this using pipelined functions in http://obiee-oracledb.blogspot.com.au/2011/07/performance-enhancing-drug-called-table.html, this blog post talks about a BIP 11g method of achieving the same result.
I was recently involved in a project implementation on OBIEE 10g. The dashboard had a multiselect and the values in the prompt were huge. The standard width of the multiselect was making the user uncomfortable since a large part of the value was hidden due to the small size of the multiselect. This blog talks about a method to fix this problem.
My client wanted to hide some of the reports from the users and release them at a later date. The however wanted a set of users to have the privilege to see hidden objects. Let us see the process to achieve this objective.
This article introduces the concept of Smart Tables in OBIEE. I call them 'Smart' because these tables have the ability to produce different data on the fly. These can morph themselves according to the user requirement. Smart Tables also make the data model dynamic in nature as a Smart Table can change characteristics on the fly. Let me now explain the procedure of implementing Smart Tables.
This is an awesome feature in BI Publisher 11g but I do not hear a lot of noise about it so let me create some. I'm talking about Event triggers in BI Publisher 11g. Using event triggers, we can conditionally stop the delivery of a report. We will see the process of skipping schedules in this article.
In the BI world, we talk about measures and aggregation but we don't talk a lot about string aggregation. String aggregation is about concatenating strings for a certain dimensional value. Let me give an example. Let us say that we have a purchase order (Lets call it PO1) raised for a set of items (Lets say I1,I2 and I3). Lets say that the supplier does not have I1 in stock and wants a larger order quantity for I2 to make the shipping viable and the supplier feels that I3 can be shipped. The store manager might want to see "I1 out of stock, Order quantity of I2 is too less" against PO1. Question is, how do we do it? This blog talks about the process.
This is another one of those articles where I pick a topic from the sample chapter of my book and share with you. Some of the other discussions in this blog from the sample chapter of my book are on dynamic breadcrumbs, data densification and ReForms. In this post, we will see 3 different ways of creating a Matrix report a.k.a Crosstab report a.k.a Pivoted report.
Let me begin by defining a ReForm. A ReForm is an amalgum of Reports and Forms and has the DNA of both. This discussion not only presents a ReForm but also demonstrates the enormous benefits of using JQuery in APEX report development.
Data is often stored in sparse form and business often wants to see data in a more organized and explicit fashion. Report developers working across reporting technologies face a requirement to show a record with a '0' in the measures columns if no data exists for a particular combination of dimension values. For such a requirement, we will have to densify the data and this article presents a way to densify and aggregate on the fly.
I wish to state that this article is borrowed from my book on Oracle APEX 4.2 Reporting. You can check the table of contents and a brief description along with screenshots.
Let me explain the use case now
I faced a rather strange but relatively simple requirement a few days back. The client had employed a resource to refresh the sessions page and capture the running queries from it and mail it to a team that dealt with performance tuning. This is certainly not the most efficient resource management. It is strange that OBIEE has an elaborate mechanism of Usage Tracking but this system does not capture the running analysis. It captures the info only after the queries have fetched the desired results and not while they are still running. This post talks about devising a programmatic way of capturing the info about running analysis/reports in OBIEE 11g.
This article is in continuation of my previous work
on Oracle Map Viewer/Map Builder and Oracle 12c database.
This article demonstrates the conversion of ESRI maps to
datatype (SDO_GEOMETRY) that can then be used in Map Viewer. The article ends with an HTML code that can be put in APEX's HTML region to display the map with ESRI info on it.
Oracle APEX 4.2 reporting is designed to bring the best of
the reporting world for intermediate –advanced readers. The book is available at http://www.packtpub.com/oracle-apex-4-2-reporting/book. It is designed to
expose APEX developers to other reporting solutions, inform them about the
strengths of each of these and to enable them to use these technologies in
APEX. The book also assists designers who wish to use APEX with their existing
heterogeneous enterprise so that their existing code is reused and effort in
building better business intelligence systems is minimized. The table of contents of the book can be found at http://obiee-oracledb.blogspot.in/2013/07/my-upcoming-book-oracle-apex-42.html. Let me briefly walk
you through the contents of the book.
Let me start this article with an apology for not posting an article in such a long time. I have been involved in writing a book and it is eating most of my time so articles will be sparse in the next few months as well. Let’s however cut the negativity and enjoy the fun of creating a download utility using PLSQL
The requirement was to generate an output in
statement page size or in legal page size, depending on a parameter value.
Since the parameter was passed from Siebel and since I did not know the method
to conditionally call different rtf files from Siebel so I had to code
everything in 1 single rtf file
I spent almost a day to implement a grouping and sorting requirement and after all that brain storming I found that the requirement can be implemented by an elementary piece of code. I wish to share this with the rest of the world
My 1st article in this blog was about OBIEE repository tuning and this article is an extension to it.
This article is intended to be a glossary of the things that
I feel are important to tune OBIEE output. This article is only intended to give a
pointer and an introduction to the things mentioned in it
your comments if you feel that I have missed something. I will update
this article if I discover some new tricks
Out of all my posts on BI publisher, this one is my personal
favorite. The article describes a mechanism to use any custom function in
BI publisher reports. This opens a whole box of opportunities. The custom
functions have to be written in java which means that you can code anything
between the sun and the earth and then use it in your BI Publisher reports.
This article describes a mechanism to create reports in excel using the relations established in Oracle BI Server. We can leverage BI server and the relations established in it to create quick reports in excel. The last paragraph of the article talks about using the iqy file to get data with OBIEE's formatting.
I haven’t extensively worked on data modeling but I saw this kool feature of sql developer data modeler. It gives you a relational picture of your warehouse and lists all attributes, enforced constraints, joins, keys etc. It became a lot easier for me to work with the physical layer after using this
OBIEE is a great business intelligence tool but it is a one way highway. Users can use OBIEE to see the intelligence in the data but what if users want to put a few things of their own. I am presenting a mechanism to make OBIEE a 2 way highway
This is the 2nd part of the series in which I am using webservies to writeback. In my previous article, I showed a mechanism to do writeback using a utility of OC4J. That mechanism works well with OC4J and OAS (Oracle Application Server) but for other webservers we need an independent webservice. In this article, I'm creating an independent webservice and deploying it on weblogic
This article presents an alternate way to do write back in OBIEE. I call it silenced because this doecs not involve the creation of text boxes to writeback something into the database. The mechanism is helpful if we intend to writeback some info, depending on some action taken by the user and we do not want the user to explicitly write the info in text boxes
Below is a review of Oracle BI Publisher 11g-A Practical Guide to Enterprise Reporting, written by Daniela Bozdoc and published by Packt Publishing. More information about the book can be found at here
BPEL is a tool used to define web services. We can use it to invoke web service of a certain application or tool and then transform the output of this service into something that can be used by the web service of another tool. The web service of the 2nd tool can then be invoked to get the desired results.
I wouldn’t take the credit of this but I found a very cool way of converting an array to a string using hierarchical queries. I found this mechanism in one of my projects and I think it is good enough to be shared
Finally a post on APEX :) I have seen a number of situations in development of both Oracle APEX (Interactive reports) and in Oracle BI Publisher where the developer has to implement complex functionality but he only has the freedom to write SQL statements and not PL SQL logic. Due to this he ends up writing the complex logic using ‘and clause’ and ‘or clause’.
Building on the concept of changing files in the war (web archive) and hosting multiple applications, we will now try to host multiple BI Publisher applications on the same server. This process of changing files in war package and deploying it has been discussed in the following post