Variable names starting with numbers

Tweet

The long weekend gave me the chance to finally write a blog post. I will try my best to be more frequent with posting new content.

This blog is going to be short one. We had multiple projects running and we had to design a way why which each project can easily identify their variables. We decided to name our session variables after our short project codes.

My project code was "1ADM" and OBIEE didn't like it when I used the varaibles whose names started with numbers.

OBIEE 11G RPD bypass using Private Variable Columns

Tweet

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.

OBIEE 11G: Setting multiple values to Presentation Variables through goURL and conditional filtering

Tweet

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.

The neo-MUD tunnel for OBIEE 11g automated RPD deployment

Tweet

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.

OBIEE 11g: Outer join in the Physical Layer now possible

Tweet

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.

Issue in using EJB Actions in OBIEE 11.1.1.9

Tweet

This blog is less about a solution and more about an information.

If you have written an EJB that uses a obiee report saved in the catalog as an arguement and you are trying to invoke this EJB using Actions in OBIEE 11.1.1.9 then you might get the following error.

Migrating application roles in OBIEE 11g

Tweet

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.

Maintenance free security implementation in OBIEE 11g

Tweet

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.

Toggling the database in OBIEE 11g at runtime

Tweet

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.

Oracle VM file sharing with Windows host

Tweet

This article talks about the process to share files between Oracle VM and windows host.

Using Internet Explorer to customize OBIEE 11g UI

Tweet

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.

OBIEE Hack: Putting prompts in file names

Tweet

Users often have the requirement to have the selected prompt values suffixed to the name of the exported file.
This blog talks about a hack to achieve this in OBIEE. 

Understanding OBIEE RPD - Chapter 1

Tweet

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.

Making OBIEE 11g Evaluate function dynamic

Tweet

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.

OBIEE 11g query tracking mechanism

Tweet

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.

Installing OBIEE 11g Client tool on Windows 8

Tweet

OBIEE 11g client tool is not certified with Windows 8. 
The certification matrix for OBIEE version 11.1.1.7.0+ is in the following location and it does not include Windows 8. 

http://www.oracle.com/technetwork/middleware/bi/bi-11gr1certmatrix-ps6-1928219.xls

It means that installing OBIEE 11g client on Windows 8 is not supported by Oracle. I got a mail from Oracle to explicitly call it out.

This blog talks about a way to to install admin tool on Windows 8 machine.

OBIEE 11g dashboard pages outside of a dashboard

Tweet

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.

3 way switch in OBIEE 11g

Tweet

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.

Seperating numbers from characters in OBIEE 11g

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.

Easy method to create BIP 11g reports on top of OBIEE dashboards

This blog talks about a quick way to create BIP reports on top of OBIEE dashboards.

Active directory configuration in OBIEE 11.1.1.7

We will learn about configuring Active Directory (AD) for OBIEE 11.1.1.7 today.

Non Standard SQL in BIP 11g - Dynamic query implementation

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.

Increasing width of Multiselect in OBIEE 10g

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.

Privilege to see hidden objects in OBIEE 11g

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.

Smart Tables in OBIEE 11g

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.

Skipping schedule in BI Publisher 11g

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.

Drill-able hierarchical charts in OBIEE 11g

This is a discovery more than a solution. OBIEE 11g lets us use Hierarchical column as dimensional attributes for charts.

Formatting levels of a hierarchical column in OBIEE 11g

I had a requirement to indent the levels in a hierarchical column. The solution isn't really innovative but I thought of sharing this because finding the option can become a treasure hunt.

Capturing Environment Variables in Oracle APEX

 This post focuses on the Environment variables as they are vital in coding conditional statements.

String aggregation in OBIEE

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.

Using DB Link in OBIEE

This blog talks about the process to use DB Links on OBIEE.

Using BIP parameters in OBIEE dashboards without creating prompts

This is a simple trick that presents the process to have a parametrized BIP report in OBIEE without the need to create new prompts in OBIEE.

Ways to create Matrix report in Oracle APEX

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.

Consuming OBIEE 11g Web Services using Java

We will talk about consuming OBIEE 11g webservices using Java in this article. My previous articles have been about consuming webservices using JavaScript and consuming webservices using PL/SQL. This is my first on consuming webservices using Java. 
My book also has a number of recipes about consuming and creating web services. Check out a brief description of the book and the table of contents. You may also check out the online application build on top of the discussions in the sample chapter of my book.

Creating ReForms in Oracle APEX

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 Densification in Oracle APEX

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

Creating dynamic breadcrumb chains in Oracle APEX

This article is borrowed from one of the topics of my book (http://www.packtpub.com/oracle-apex-4-2-reporting/book) on Oracle APEX 4.2 reporting. Check the chapter by chapter description and the table of contents to know about the other discussions in my book. I wish to talk about the process to engineering breadcrumbs of varying lengths in this article. Let me explain the motive with an example.

Tracking the running reports in OBIEE 11g

 
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.

Sangam 2013

Just read about http://www.sangam13.info/.The event has been cohosted by AIOUG and India OAUG

Such events are indeed rare and few in India. Really hope this to be a trendsetter.

ESRI map with Google map and map viewer in APEX

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.

My take on using Mapviewer with Oracle 12c and Google maps API

Map viewer is a kool technology and I wanted to write about it for a long time. 

Let me first chalk the steps that we will follow. We will

1. Download Map Viewer and Map Builder.

2. Create a pluggable database (pdb), create a pdb user and grant the necessary privileges to it.

3. Configure Map viewer.

4. Insert spatial data.

5. Use Map Builder to create styles and themes.

6. Get a key from google and then use it to create a map tile layer using Map Viewer.

7. Write code to create a FOI layer on a base map using Map Viewer's JavaScript API.

Description of my book on Oracle APEX 4.2 reporting

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.

My upcoming book - Oracle APEX 4.2 reporting

I have finally finished writing my book on Oracle APEX reporting. The book will be  is now available at www.packtpub.com/oracle-apex-4-2-reporting/book

Creating Download Utility using PLSQL

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

Coding different page layouts in a single rtf template and the importance of Contexts in BI Publisher

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

Sorting outer group based on inner group in BI Publisher

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

Formatting time in BI Publisher

This article describes a mechanism to format time in BI Publisher.

Let me explain with an example

I have a XML file with the following tag in it.

          <SsStartTime>18:00:00</SsStartTime>

The requirement is to display this time as 6:00PM

OBIEE performance improvement tips

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

Please add your comments if you feel that I have missed something. I will update this article if I discover some new tricks

Enough said, lets roll

Using custom transformations in BI Publisher 10G

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.

Creating reports in Excel using OBI server

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.

SQL developer data modeler for OBIEE developers

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

Creating Applications in OBIEE using DAD

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

Folding table in Oracle using unpivot function

In this post, I am unsing unpivot function to change the arity and cardinality in the result. mXn table is used to get pXq result such that product(m,n) = product(p,q)

Installing SSL enabled APEX 4.1 with development environment and APEX Listener on Weblogic and standalone

This article presents a way to set SSL enabled APEX 4.1 on weblogic using APEX listener. The final part shows the process to configure standalone APEX listener and the use of Grizzly in it.

This post is divided in 4 sections

1. Installing APEX schema and related DB components

2. Installing APEX listener

3. Time to configure the listener

4. APEX Listener in standalone mode

Silenced writeback using webservices - part 2

Happy Valentine's day to all :)

This is the 2^nd 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

Silenced writeback using webservices - part 1

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

Pivotal performance improvement in OBIEE

This article shows a method to improve the performance of the pivot view of OBIEE. 

Freedom to format in OBIEE

I’m finally back to my blogging business J. This article deals with the usual OBIEE business of formatting. OBIEE gives a lot of formatting options by letting us write javascript/HTML code in a spectrum of places such as narrative, static view, text’s in dashboards, data format tab of columns etc.
I am sure you can list a lot more examples of places where we can write javascript/HTML in OBIEE.

Book review - Oracle BI Publisher 11g: A Practical Guide to Enterprise Reporting

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

Making OBIEE web service friendly

I know that I am obsessed with web services, but this is my final article in the series dealing with web services.