We had a requirement where the user wanted to be able to get into the tool and change his formula and also wanted the change to be reflected across all his reports. Changing the formula in each report was a lot of effort and hence not practical.
This blog talks about a 6 possible ways to handle this requirement and introduces the concept of Private Variable Columns.
I am writing this blog after a loooooong gap and I can tell you that it is worth the wait. In this blog, we discuss about the process of setting multiple values to a presentation variable through goURL. This blog also introduces the concept of CONDITIONAL FILTERING in OBIEE.
Ok call me a geek for writing this blog on new year's eve, I dont care :-P. I can tell you that this blog is totally worth it. We know that EM can be used for deployment but we had a requirement to maintain an automated log every time a deployment is done with mandatory comments from the developer and link the deployment to a backed up RPD so that we can keep a track of the changes been done to the RPD and revert back if necessary. I developed the Multi User DEPLOYMENT (not Development) tunnel a.k.a neo-MUD tunnel utility to meet this requirement. We will talk about the process to implement the neo-MUD tunnel in this blog.
We know that we can perform outer joins in OBIEE in the Logical Layer. The problem with this mechanism is that it is not possible for one logical table sources (LTS) to join to a fact by an inner join and the other to join to the fact by an outer join. Let us try to solve this problem here.
My last article talked about a maintenance and hassle free mechanism to implement security. The article moved the security architecture to roles created in EM. The next logical step is to move the roles from one environment to another because lets face it, manually creating roles is a lot of effort.
This blog talks about a hassle free mechanism of OBIEE security implementation with almost zero overhead.
We will first look at the data level security implementation and then extend it to object level security.
We have a replication and the primary database server. Replicating the data takes some time so the replication server is usually a few mins behind the primary server. The requirement for one of the report was to get the data from the primary server if the replication is lagging by over 1 min. This blog talks about a method to implement this business use case.
We recently had a requirement that the button sizes in OBIEE had to be increased. The reasoning was that the users of some of these reports are old and they hate smaller sized buttons. Now we know the properties of buttons such as the size are set in the css files which together form the skin. While it is possible to copy the skin and then edit the desired property, it is a long process. This blog talks about a shortcut to achieve the objective.
The most skilled task in the world of Oracle Business Intelligence is to design and code the RPD. While we are all aware of the general concepts of Business models, logical tables, joins and hierarchies, the RPD is essentially a query generating tool. The query generated by the tool depends on a sound understanding of the business model and data warehousing concepts. The most trickiest part of RPD development is to ensure that OBIEE generates a tuned physical query. I wish to write a series of articles that show the impact of coding something in the RPD on the Physical query and would hence enable the developers to find out the best things to do from a performance stand point. Please note that this article does not recommend one query over the other as the efficiency of the query will depend on the data structures themselves. The intention of this blog is to study the impact of changing the RPD on the Physical SQL. This is the first article in this series and we will be talking about the scenario where we pull columns from 2 different physical sources and 1 common dimension in the same analysis.
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