All of the topics discussed here in this blog comes from my real life encounters. They serve as references for future research. All of the data, contents and information presented in my entries have been altered and edited to protect the confidentiality and privacy of the clients.

Various scenarios of designing RPD and data modeling

Find the easiest and most straightforward way of designing RPD and data models that are dynamic and robust

Countless examples of dashboard and report design cases

Making the dashboard truly interactive

The concept of Business Intelligence

The most important concept ever need to understand to implement any successful OBIEE projects

Making it easy for beginners and business users

The perfect place for beginners to learn and get educated with Oracle Business Intelligence

Friday, December 6, 2013

Basic Understanding of OBIEE Report Vs BI Publisher Report

Hello All

I recently came across a lot of beginners asking me questions about the difference between BI Publisher and OBIEE reports. To them, BI Publisher is either a 'different Feature' within OBIEE, or it's a completely different Application who competes with OBIEE in 'reporting business'. So today, i am hoping to make it easier to understand the difference between the two.

BI Publisher was originally a different application, which is now part of OBIEE. The best way to put it is, BI Publisher is another technology that has been integrated with OBIEE, so now the two are both parts of Oracle Business Intelligence.

Now let's understand what are each for:

OBIEE analysis provides dynamic and interactive ad-hoc reporting capabilities that allows users to dynamically change queries on the dashboard as well as interacting with other objections on the dashboard through action links. These are the original features of OBIEE, these capabilities can date back to nquire or Siebel Analytics time. The idea was to create a reporting platform and have users stay there, like the dashboards.

However, certain business requires very specific type of reports that are highly standardized across the industry that has to be pixel perfect in very specific ways. These can be financial statement, invoice statements, certain transactional statements in Auto industry and so on. These reports are typically generated by the department and have to be eventually mailed out to external customers, therefore the look and feel of these reports will have to pixel perfect, meaning that the space between each line has to be specific, the colors, the fond and layouts among all the fields have to be arranged in specific ways. Moreover, when you print these reports out, it should ideally fit in to one page (in most cases). All these requirements can't be achieved easily using OBIEE's native dashboard features. Therefore, Oracle has decided to integrate BI Publisher into OBIEE platform and making it additional reporting layer on top of dashboard.

BI Publisher allows you to define your data model using existing OBIEE analysis (or others) as data set where reports draw data from onto it's templates. The template provides features similar to words document that allows you the design your own pixel perfect layouts like the below as an example:

The above report will be printed out on 1 page, pixel perfect. However, it does not have the ability to drill down, interact with other elements on OBIEE dashboard. This is something OBIEE can do but not PI Publisher.

Now that Oracle owns both technology, both are available in the OBIEE product that satisfies different needs. Now in the latest version of OBIEE 11g patch, Oracle has also introduced a Mobile App, which allows you to create custom Mobile App displays of OBIEE reports, which will work for all smart phone and browser independent. This will be additional layer on top of BI Publisher within OBIEE.

Like I said, there are different needs and therefore there will be different technologies. The important thing is to understand what different technologies are for in order to make better distinction among them


Until next time

Sunday, November 24, 2013

Configure change password for users in OBIEE 11G


In all OBIEE products, it is not so simple for users to change passwords themselves. The user accounts are typically managed by the Admin. This poses some realistic problems. Lets say I have a training environment set up and I have to make it available to a list of internal users for their daily activities. What do I do? I go create these users in Weblogic and access passwords to them. Do I send out one email to all of them with their username and password, since the password will be all the same?  Inevitably, the users are going request to update their passwords. If you have a list of 20 users, it's almost impossible to create passwords for each, it's the best to let them update passwords themselves.

Unfortunately, OBIEE doesn't have any out-of-the-box features for it. However, the good news is that, this can be done and has been done.

This article right here is excellent. It is very detailed, easy-to-follow article about how to make password change features available for users. I followed the steps without thinking, and it worked for me, I am sure it will work for you too. Check it out.


Until next time.


This implementation is for cases where users are managed and authenticated through weblogic. If you are using external table for authentication, then its different.

Typically, when external table is used for authentication, there is usually an integration of OBIEE with home-grown applications. This happens a lot for projects that use OBIEE as a part of product that the company is developing, rather than an BI Platform for internal usages. In this case, there is typically a SSO implementation where users can authenticated to OBIEE after they log on to other apps. A lot of password changing features would be typically implemented there and OBIEE just authenticate using updated user information.

Monday, October 21, 2013

Mathematically calculate the difference between month when the data is numeric 'yyyymm' in OBIEE


I just found something fun and maybe useful to do in OBIEE. Calculating the difference between timestamps is pretty easy by using timestamp diff function. When it comes to calculating the difference between month, I know 'sql_tsi_month' can be used, which will give you results in month. However, you don't always want to put date columns into the equation if your report is aggregating at monthly level, and sometimes the date column is simply not available. So how do you go about calculating the difference in month.

To show the point, let's look at the following report to understand the requirement:

We need to calculate the difference in month between check in date and KPI month and year

So in this case, the report is going against the monthly aggregate fact since the date dimension field is KPI month and year, we don't want to introduce KPI Date into the formula (even if we do have this field) for comparing timestamps. So we need a way to figure out the difference between (in this case) 3/08/2010 and 201101 in months. Now the good new is that, it can be a rounded estimate, meaning that as long as the date is in March, any date between 1 to 31, it is March. So the difference between March 31th and April 1st is 1 month. This is a good news, because it basically means I need to mathematically calculate the difference between something like 201003 and 201101, but the result should be in month.

Now let's figure out how to achieve this mathematically.

We all know that 201101 - 201003 = 98. Obviously this is going to be wrong. In order to do the right calculation, we need to calculate this in two parts: 2011 - 2010 and 01 - 03, then add the result back together to give the final result.

Now let's break it down step by step:

1. Assign KPI Month (201101) is X and Check in Date (201003) is Y.

2. do: X/100 and round off, this will give you something like 2011 and Y/100 will be 2010.

3. Divide the above and multiply by 12 since there are 12 months in a year. In this case, it will give you 1*12 = 12.

4. Do: X- (X/100)*100 and Y - (Y/100)*100. Both will give you the last 2 digits: 03 and 01.

5. Divide the two numbers from step 4, this will give you difference in month, which is -2.

6. Now add the result from step 5 and step 3, which is then -2+12 = 10. This is your answer.

Now the programming of this in OBIEE is very simple. You convert the data from 03/08/2010 into numeric 201003, the formula of achieving such becomes your 'Y'. And the rest are basically the exact carry-out of what has been described above, so I will skip both of the steps and just show you the result.

Defining the OBIEE formula:

Result (and it's working):


Until next time

Tuesday, October 15, 2013

De-mystify OBIEE with Big Data Integration

Hello there

I know over the last few years, the word 'big data' has become all of the tech world. People talk about it, project management talks about, even those that don't know anything about anything talks about it as if when throwing words like 'cloud computing', 'Hadoop', 'Map Reduce' into the conversation makes one person sound that much smarter and sophisticated. Big Data is a concept, which has been done for many years, it's just that it wasn't well-known until much later. The concept is powerful yet simple. This is the product of the ever-evolving modern society where information means money.

Inevitably, this new concept, new framework will have to be integrated with technologies that can display the pre-computed data with business meanings. This is when the integration between Big Data framework and reporting platform happens. In the last few Oracle Open World, I have seen companies with all kinds of products and services tapping into the world of big data and cloud computing as if this is the rocket science. Let me tell this, it's not big deal. The integration between OBIEE and Hadoop Hive isn't as complicated as you think. There is no special configuration nor special coding to make this happen. It is what it is, Hadoop pulls data from various nodes and stores them into a database (Hbase or anything else), in the middle of the process, Hadoop does what it does, group by or sort in Map Reduce, but at the end the output is what OBIEE reports on. It's no big deal, of course I don't mean it's a waste, I mean it's brilliant because it's no big deal yet powerful.

Now that OBIEE allows to pull data directly from Hadoop, Informatica has a new Hadoop plug in, just about every ETL tools are working to recognize Hadoop, it is making the integration easier from configuration point of view. However, the main engineering work isn't the integration part, it is the architecture of such framework that not only has to be cost effective, but also highly scale-able. This is the money.

I have been dealing with Hadoop with OBIEE since its 10G version. I could get that to work, 11G is just becoming simpler.

The idea of Big Data is to handle highly unstructured data, data that are not conventionally defined as attributes or facts. Stuffs people write about on facebook comments, tweets, blog comments are all unstructured data, yet they are highly valuable stuffs. These data are all over the internet, not just in 1 source OLTP like most of the conventional stuffs that BI Projects deal with. These data comes in huge volumes in matters of minutes. Most powerful of all, the concept of cloud computing is to be able to do the job instead of using 1 super computer that costs millions, but using said 2000 ordinary laptops each acting as a node. That way, you can freely plug your laptop in and out of the network without destructing others -- things you can't do with mainframe system. This translations to cost-effectiveness.

While Bid Data is so hot and so Big now, it is more of a concept, a framework. It will still need to stand the test of time to be accepted and standardized. This concept has been more popular among start-up companies, or tech-giants who want to build software products using this concepts. However, it will take more time more big banks and financial giants to accept. The main risk of Hadoop framework in my opinion is the risk of losing data during the transactions. Just like any software, there will be error during the process. Unlike big banks who stores data in their mainframe that are highly secured and protected, Hadoop may lose some data during the distribution. This is why sometimes when your google search turns out empty, or your facebook comment is not found, or whatever cool pictures you uploaded was unsuccessful. If it's not big deal to lose transaction once a while, like in this case, you will just upload again or refresh the search page again, then it's no big deal, enjoy those more benefits that the framework offers. But if you are talking about financial transactions, stock transactions that you absolutely can not afford to miss at specific time, then Big Data's risk way outweighs the gain. Therefore, one big evaluation of the product is the analysis of the nature of your data to determine how much can you afford to miss some data (as known risk in any engineered system).

Despite all these, the integration between reporting and big data framework does have a lot of futures. Most of these projects will be different from the traditional OBIEE projects that handles company's internal reporting, rather, they will be more outward to external consumers and marketing.

This is a typical project I went through using Hadoop integration with OBIEE, which really is no big deal:

Building a software product that does telecom network analysis to diagnosis network performance. Hadoop was used to collect data from routers and devices (500 to 2000 routers), ETL framework was used to transform data into analytic data for trending while Hbase provides more real time data. OBIEE reports on top of them with dashboard and charts that shows network performance including various network elements, towers, stations, pseudowires in the network. Since data that are traveling through the network contents customer information, such as numbers, IP Address, gender etc, marketing analysis was also provided as dashboards. The product is not for internal stuffs, rather it is sold to network carriers as pre-built solutions, similar to BI Apps. Therefore, OBIEE maybe replaced by Pentaho for carriers who don't want to spend too much money.

Anyways, more project in this nature will become available as I see it. Once people have the data, they don't want to lost it, they want to do something about it. Hadoop framework is a good solution. Along with that, there are several DB technologies that handles unstructured data, such as Cassandra, Mongol DB and so on, they all do the job differently, but the idea is comparable. OBIEE on the other hand, really isn't gonna make much difference here. The integration of OBIEE and all that stuff really isn't much different from Integrating with said Oracle DB or Informatica.

The bottom-line is, all of the engineering work will be done to collect data and group by as single output, then OBIEE somehow manage to arrange these schemas into a star to report on. Well, if you are not using OBIEE to report, do you still care about logical Star Schemas? Something for you to think about.


Until next time.

Saturday, October 12, 2013

A comprehensive way to understand Oracle BI Apps

As you spend more time developing reports using OBIEE, you will realize that the needs for better and more optimized data models and ETL frameworks become more important. You realize that most of the time required to spend is on designing database schemas, tables and objects as well as ETL frameworks to load the data into analytic warehouse. Needless to say, the time required to research and understand the specific business process and subject matter is more crucial than the technical implementation. Often, millions of dollars and years of time are invested in this matter, but still things can go wrong. This is the price people pay when it invent the wheel.

What if there is a complete, end-to-end solution for any specific business needs that are pre-engineered and packaged for you, which can be deployed and installed in your system fairly simply, that will save you endless times and risk of mistakes? This is what BI Apps is all about. With a complete end-to-end structure that consists of the groundwork of analytic warehouse where all the required schemas and objects have been created, the most optimized ETL framework that takes care of all the data transformation needs, and most eye-catching reports and dashboards with the most valuable information displayed in highly visual and communicative ways. Most importantly, BI App is a project after years of research and engineering that are designed to solve most of the problems for business. In other words, the wheel is already invented, you just need to put it on your car. This is the reason why BI Apps have become the solution for so many companies, it's all about cost-effectiveness.

In order to understand how BI Apps work and how to study and gain more insight about it, you should first start thinking in terms of problems and how BI Apps solves them. If you were just trying to study and memorize things by reading documents, it is hard.

So start asking yourself these questions:

1. Knowing that BI Apps is an end-to-end solution package that comes with a pre-build data warehouse, Pre-defined Informatica framework, DAC and OBIEE, what does it take to put these things in your business so it can be operating? In other words, should I install all these things on 1 machine or multiple machines?

hint: Informatica and DAC have to be installed on the same machine. OBIEE has to be on different machine. Staging DB and warehouse can be on 1 machine. Since OBIEE's admin tool is a windows based application, a windows machine is needed to install OBIEE client tool so that the pre-build OBIEE objects (rpd) of the BI Apps can be installed. 

2. Once this thing is installed, it is not running yet. Which means, your live data from OLTP is not yet being loaded into your warehouse, so none of the OBIEE reports will work. How does BI Apps pull data from your OLTP source, if your OLTP source is Siebel, Peoplesoft, EBS, JD Edwards or your home grown one?

Hint: Oracle provides adapter for pulling data from various sources. If you open the Informatica repository, you will see a list of Adapters available. These are nothing but folders where different mappings are stored. If your source is EBS, then you will use EBS adapter where all of the informatica mappings are pre-built to pull data from EBS source. All of the source table definitions there will match exactly with the tables in your EBS. If you use home-grown source, then there is universal adapter for you where the input files will have to be customized into CSV format for Informatica to load from. 

3. One you start loading data into your warehouse, how does BI Apps actually do it successfully based on your needs? If you think about it, in any projects you are going to face the same needs such as defining your calendar, time dimensions, your dimensional hierarchies, your fact tables and aggregation tables. How does BI App actually do it?

Hint: This article gives some helpful hints. To configure for the initial full load, you need to go through several configuration steps in Dac and Informatica. Such as defining initial extract date parameter in DAC to avoid loading way too much record, configure DAC Source system parameters for global currencies, exchange rate types, Nullify the refresh date tab in DAC to enable full load to W_Day_D dimension (based table for all calendars in BI Apps) and define $start_date and $end_date parameter for Task SIL_Daydimension so that DAC will load data into Date dimension based on those date ranges. Run PLP tasks to load aggregate tables, configure GL Account hierarchies, etc

4. If your business is international, you are likely to deal with transactions that are not in just 1 currency, but multiple currencies. How does BI Apps handle this?

Hint: Found above

5. Inevitably, any business is going to want to customize the BI App to make it more specific to their business, how does BI App make it easier for such customization? What are the levels of customization?

Hint: There are category 1 and category 2 customization. For cat 1, it is about bringing in additional fields from the source into target. In all of the pre-built mappings, there is an unmapped field 'x_custom' in pretty much every transformation. Your new field should go through the same sequence and flow as x_custom to ensure it's safe arrival to the target. For Cat 2, it is about bringing new tables and dimensions into the mapping. Use your informatica knowledge to do it against the requirement. The best way and the only way Oracle will support your customization is by creating custom folders in your Informatica and Custom container in DAC, then copy the pre-built mappings/workflows into this folder to make your customization, change DAC task to point to these new folders so that the execution plan will run these workflows instead of the original ones. DON'T EVER CUSTOMIZE DIRECTLY IN ORIGINAL MAPPING.

6. Inevitably, there will be records deleted in the OLTP system later on, how does BI App handle this situation so that the deleted records won't show up in its warehouse?

Hint: Informatica has pre-built mappings that handle deletion to the warehouse records. This article gives a good explanation.

7. In any ETL framework, there is going to be needs for maintaining historical records in the tables. How does BI App handle the loading of SCD? What's BI App's way of checking and flagging records that become history?

Hint: There are a lot of approach to this problem. The bottom-line is, how to identify records that have been updated and once it's been identified, what to do about it? Using orahash value has been a traditional way by a lot of DBAs to identify records that have been changed. When any of the key attributes related to this record has changed (such as address, phone number, name etc related to this specific account changes), new orahash key gets generated. There must be fields that stores information such as record start date, end date and history flag in the dimension where the primary key will be attached to timestamp to ensure uniqueness. Knowing the idea, how does BI App handle it there way (no necessarily using Orahash )? This article can be helpful.

8. How does BI App handle incremental load?

Hint: In a lot of the pre-built mapping, you will notice that it has 2 workflow sessions. The suffix will suggest which one is full load and which is incremental. The DAC Task window will also provide setting for both full load and incremental load sessions as mentioned here.

9. When it comes to data modeling in OBIEE, there are going to be a lot of snowflaking of dimensions? If you are dealing with financial analytic, you will see that a lot of the dimensions are at different granularity that won't join to the fact. Fact tables are aggregated at different levels for optimizing performance, how are these being modeled in the rpd?

Hint: In your OBIEE rpd, you will notice that in the BMM layer, there are lots of logical tables being mapped to multiple LTS. This is creating logical star schema out of highly normalized physical snowflake schema. This is almost like creating a logical view that joins multiple physical tables.

10. Any applications will have to deal with upgrades and migrations. How does that work in BI Apps?

Hint: If your customization is done accordingly, the upgrade should be fairly easy.

11. Security is always a big concern for any software, what are the security models provided by BI Apps?

Hint: This document gives good information

12. Last but not least, having some domain knowledge about the business is always important. If you are dealing with Financial analytics, what does business unit means, what about chart of account, line of business, GL stuffs? What business information do dashboards like Payables, receivables, profitability provide for financial analytics? What about HR Analytics?

Hint: This is something you will gain through experience, research and curiosity. 

These are areas that you have to investigate and research to gain better understanding of how BI Apps work, if you want to excel in interviews. Not only you have to be familiar with the topics, the customization and handling of such a vast application, but also start thinking in terms of what is the nature of the business problems and how BI App solves it. If you can relate BI Apps solutions with data warehouse solutions in general, things will start making more sense, because if you have ever designed and implement any solutions with your own resource before, you will realize that a lot of the problems and concerns have already been addressed in BI Apps. Therefore, the engineering of it becomes minimum, the customization and handling of the operation is what BI App is all about.


Until next time

Tuesday, August 20, 2013

Oracle DAC interview questions and answers

Oracle DAC is an essential part of BI Apps, which is seldom being introduced in a systematic training course, although we use them all the time. There can be quite a lot of things to ask about when it comes to working with DAC, especially during interviews for BI Apps related projects. So I am going to gather some of the common interview questions with regard to DAC.

1. Name some of the DAC source system parameters:
TYPE2_FLAG, GLOBOL1_CURR_CODE, Initial_extract_date etc.. (The goal is just to name a few and of course, nobody remembers exactly the spelling)

2. To configure for initial full load, what are the things that needs to be done:
A, in DAC, set the value for initial_extract_date to avoid loading way too many data into target
B, to load base table W_DAY_D, nullify all of the refresh date to enable to full load. Do the same for all other aggregated time table like W_Week_D etc. At each task level where day dimension is being part of (SIL_daydimension), set the $$start date and $$end date parameter values at the task level to determine how long period your day dimension should store.
C. If your company does have multiple currency, then you need to configure currency in DAC by assigning currency code and exchange rate to DAC parameters like globol1 (2,3)_curr_code and globol1 (2,3)_curr_rate_type. BI Apps support up to 3 types of currency.
D. Configure GL Hierarchy so the info stores in W_Hierarchy_D. No DAC configuration needed
E. DATASOURCE_NUM_ID is a DAC parameters that determine which datasource system the extraction is taking place. In physical data source tab under 'setup' view, this field can be edited with integer number from 1 to 10 to represent different DB source.

3. Explain how to set up metadata in DAC to load data into the target

For basic intro on how DAC work in terms of executing the tasks, find out here

4. How to configure incremental loading in DAC
A. The refresh date under physical data source stores the last ETL run time, by nullifying this, the DAC will run full load or it will run incremental load based on the refresh date value.
B. Under task, there is 'incremental load' commend, by checking this, it will do either full load or incremental load regardless of refresh date.


Below are the list of questions about DAC found through googling, since these questions have NOT been provided with answers, I have provided my answers, feel free to read it for your reference:

1. Over all architecture of DAC ?
DAC server and DAC Client. They must co-locate with Informatica Integration service, repository service and Informatica repository

2. Why we should use DAC and not control all execution through informatica ?
For better performance management, such as creating index, dropping index, truncating before load. Without DAC a custom ETL process will be needed, which has to survive the upgrate

3. Can we run multiple execution plan at the same time in DAC ?
Yes. only if the execution plan are not loading into the same table or using the same phyiscal table source

4. Explain DAC export/import
A way to import or export DAC repository metadata for upgrade or backup. Logica, System, runtime objects can be import/export

5. Have you change any of the DAC parameters ? If so which one and why ?
You have to understand what are the DAC parameters and the purpose of each. For example, Initial_extract_date can be modified when configure for initial full load, so the value for initial extract date will be used to filter out records from the source that are older than this date.

6. How do you Determine the Informatica Server Maximum Sessions Parameter Setting in DAC?
One you register informatica server in Dac client

7. Can dac send an email just in case of any failures ?
In DAC Client, toolbar, click email recipient, then in Tools--> DAC Server setup, Email configuration

8. Can you execute the sql scrip through DAC ? If yes how ?

Yes, at task level, in execution type, select SQL file. As a bonus to this answer, this article explains how to run store procedures in DAC.

9. in DAC How you can disable table indexes before loading and enable the index once load is complete ?
Just drop and recreate index

10.Let say you are running the normal incremental load. But just for today you want to extract data from AP_INVOCIES_ALL from 12/12/2011? How you can achieve this ?

Modify the refresh date to be 12/12/2011

11.How DAC Determines the Order of Task Execution within an Execution Plan ?
Based on tasks source/target table, Task phase (extract dim, load fact etc) and 'truncate always' properties, to run them in particular order, create task group

12.What are Micro ETL Execution Plans ? How can you Build and run them ?

According to Oracle document:
Micro ETL execution plans are ETL processes that you schedule at very frequent intervals, such as hourly or half-hourly. They usually handle small subject areas or subsets of larger subject areas. The DAC tracks refresh dates for tables in micro ETL execution plans separately from other execution plans and uses these refresh dates in the change capture process.

in design -- subject areas, create copy of subject area, inactive the unwanted tasks and create new execution plan for this subject area

13.From you past experience – explain scenario where Micro ETL Execution Plans produced wrong results on reports?

According to Oracle Document:
CAUTION:  Micro ETL processes can cause issues with data inconsistencies, data availability, and additional load on the transactional database. Therefore, you should consider the following factors before implementing a micro ETL process:

For related star schemas, if one schema is omitted from a micro ETL execution plan, the cross-star reports may be inaccurate. For example, if the Person fact table is refreshed more frequently than the Revenue fact table, a report that spans the Person and Revenue dimensional schemas may produce inconsistent results.
If you omit dimension tables from a micro ETL execution plan, the foreign keys for the fact tables will point to Unspecified rows for the new dimension records. The foreign key references will be resolved when the Complete ETL execution plan is run, but users of the reports should be aware of such inconsistencies.
If you do not include aggregate tables in micro ETL execution plans, the reports that use data from these tables will be inconsistent with the reports that use data from the detailed fact tables. However, if aggregate tables are included in the micro ETL execution plan, the aggregate calculations are performed for each ETL process, which will take a constant amount of time and may be inefficient to perform at such frequent intervals.
Hierarchy tables are rebuilt during every ETL execution plan by querying the base dimension tables. This operation takes a constant amount of time. If the base tables are big, this operation may take a long time and may be inefficient if the micro ETL execution plan runs several times a day. However, if you avoid populating the hierarchy tables during micro ETL processes, data inconsistencies will occur.
With micro ETL execution plans, caching will occur more frequently, which may have performance implications.
Micro ETL execution plans will put more load on the transactional database because of the frequent extracts.

14. Let say you can not use DAC scheduler to schedule you execution plan. What other options do you have ? How you can achieve this ?

Use Informatica scheduler.

15.Does DAC keeps track of refresh dates for all the source/target tables ?

According to Oracle Document:
Refresh dates are tracked only for tables that are either a primary source or a primary target on tasks in a completed run of an execution plan. The DAC runs the full load command for tasks on which a table is a primary source or target if the refresh date against the table is null. When there are multiple primary sources, the earliest of the refresh dates will trigger a full load or an incremental load. If any one of the primary source tables has no refresh date, then the DAC will run the full load command.

16.Consider the scenario as below for task T1
Primary Source has not null last refresh date
Primary Target has null last refresh date
Will task T1 executes in full or incremental ?

Based on answers provided from question 15, what do you think?

17.Explain the upgrade/merge options for DAC 7.8.4 & below and new versions ?

Use upgrade/merge wizzard.
1. Repository Upgrade (DAC 784) --- upgrade Dac
2. Refresh Base --- For upgrading BI Apps
3. Simplified Refresh From Base -- This option is similar to the Refresh Base option. It allows you to upgrade the DAC Repository from an older release of Oracle BI Applications to a new release without comparing repositories and creating a Difference Report.
4. Replace Base --- Upgrade when phasing out older transaction system to newer one
5. Peer to Peer Merge  --- Mergre different DAC instance of repository

18. Using DAC command line – write a script to check weather informatica services are up or not ?

use dacCmdLine InformaticaStatus. Below is the list of all commend lines according to Oracle:

19.Can we have two DAC server on the same machine ?
You can run two DAC servers on the same machine as long as they are listening on different ports and pointing to two different repositories

20.Explain briefly What kind of DAC Repository Objects Held in Source System Containers ?

Subject Areas -- A logical grouping of tables related to a particular subject or application context. It also includes the tasks that are associated with the tables, as well as the tasks required to load the tables. Subject areas are assigned to execution plans, which can be scheduled for full or incremental loads

tables -- Phsyical tables in DB

Indexes -- Just like your physical DB indexes

Tasks -- Unit of work for loading tables

Task groups  ---- Grouping of tasks that can be bundled to run as a group

Execution plans -- A data transformation plans defined on subject areas that needs to be transformed at certain frequencies of time

Schedules -- Determine how often execution plan runs.

21.What is Authentication file ? If you have dac client installed can you access DAC repository without Authentication file ?

According to Oracle Document:
When you configure a connection to the DAC Repository, the configuration process includes creating a new authentication file or selecting an existing authentication file. The authentication file authenticates the database in which the repository resides. If you create a new authentication file, you will specify the table owner and password for the database.

22.Explain Index, Table and Task Actions in DAC ?

According to Oracle Document:
Index action: Override the default behavior for dropping and creating indexes

Table action: Override the default behavior for truncating and analyzing tables

Task action: Can add new functionality of task behavior, such as precedinf action, success action, failure action, upon failure restart

23.How DAC Handles Parameters at Runtime ?

According to Oracle Document:
During an ETL execution, DAC reads and evaluates all parameters associated with that ETL run, including static and runtime parameters defined in DAC, parameters held in flat files, and parameters defined externally to DAC. DAC consolidates all the parameters for the ETL run, deduplicates any redundant parameters, and then creates an individual parameter file for each Informatica session. This file contains the evaluated name-value pairs for all parameters, both static and runtime, for each workflow that DAC executes. The parameter file contains a section for each session under a workflow. DAC determines the sessions under a workflow during runtime by using the Informatica pmrep function ListObjectDependencies.

The naming convention for the parameter file is


DAC writes this file to a location specified in the DAC system property InformaticaParameterFileLocation. The location specified by the property InformaticaParameterFileLocation must be the same as the location specified by the Informatica parameter property $PMSourcefileDir.

24. How DAC Determines Tasks Required for any given subject area ?

According to Oracle Document:
You define a subject area by specifying a fact table or set of fact tables to be the central table or tables in the subject area. When a subject area is defined, DAC performs the following process to determine the relevant tasks:

DAC identifies the dimension tables associated with the facts and adds these tables to the subject area.

DAC identifies the related tables, such as aggregates, associated with the fact or dimension tables and adds them to the subject area definition.

DAC identifies the tasks for which the dimension and fact tables listed in the two processes above are targets tables and adds these tasks into the subject area.

Tasks that DAC automatically assigns to a subject area are indicated with the Autogenerated flag (in the Tasks subtab of the Subject Areas tab).

You can inactivate a task from participating in a subject area by selecting the Inactive check box (in the Tasks subtab of the Subject Areas tab). When the Inactive check box is selected, the task remains inactive even if you reassemble the subject area.

You can also remove a task from a subject area using the Add/Remove command in the Tasks subtab of the subject Areas tab, but when you remove a task it is only removed from the subject area until you reassemble the subject area.

DAC identifies the source tables for the tasks identified in the previous process and adds these tables to the subject area.

DAC performs this process recursively until all necessary tasks have been added to the subject area. A task is added to the subject area only once, even if it is associated with several tables in the subject area. DAC then expands or trims the total number of tasks based on the configuration rules, which are defined as configuration tags. This process can be resource intensive because DAC loads all of the objects in the source system container into memory before parsing.

25.Difference between Homogeneous and Heterogeneous execution plans.

According to Oracle Document:


This type of execution plan extracts data from multiple instances of the same source system. For example, a business might have an instance of Oracle EBS 11i in one location and time zone and another instance of Oracle EBS 11i in another location and time zone. In such cases, the timing of data extraction from the different instances can be staggered to meet your business requirements.


This type of execution plan extracts data from one or more instances of dissimilar source systems. For example, a business might have an instance of Siebel 7.8 in one location, an instance of Oracle EBS 11i in another location, and a second instance of Oracle EBS 11i in yet a third location. You can also stagger the timing of data extraction when you use this type of execution plan.

Wednesday, July 31, 2013

Understanding the implementation of value based hierarchy (parent-child) in OBIEE 11G


As we all know, from 11g onwards, the feature of implementing value based hierarchy is available. There are several great articles out there that explains the steps of such implementation in 11G, therefore I am not going to repeat these things. What I want to talk about is more of a concept of value based hierarchy implementation, which will hopefully answer the questions that a lot of beginners reading about this topic may have as such "How do I know the right table structures for this type of hierarchy?"

As we know, employee dimension is a very typical example of value based hierarchy, but I don't want you to stop right here. There could be so many other tables that can be designed to support value based hierarchy based on reasons we will touch upon.

Conceptually, we all know what value based hierarchy is from looking at sample diagrams of hierarchical trees where branches are labelled with employee names. Now, if you look at the physical table where this type of hierarchy is supported, it will look like this:

Employee                 Manager                   Level (or other names)                other attributes
Me :)                                                                8                                            phone number, address etc
Superlek                     Me                              7                                             ......
Attachai                     Superlek                        6                                            .......
Sombat                      Attachai                         5                                            .......
Dekkers                     Sombat                         4                                            .........
Orono                        Dekkers                        3                                            ...........
Yodsanan                   Orono                          2                                             ...........
Mick                          Yodsanan                     1                                             ...........
Gina                           Mick                            0                                             ..........
Decharwin                  Me                               7                                             ............

From the above example, it clearly stores Employees in relation to the immediate boss in a value based hierarchical fashion. The level (or whatever name it can be) will keep an integer that indicates the person's level in this hierarchy. This table can have other attributes that handles changes and updates of employee depending on how the business wants to keep track of historical record. But the idea is pretty clear here.

Now if you want to redesign this table to store structure based hierarchy similar to time dimension, then this table structure will look like this:

Employee            Manager           Sr Manager        Director         ......       CEO
Gina                      Mick                Yodsanan            Orono                       Me :)

The reason structure based hierarchy isn't the best for employee dimension is that it's very rigid. All of the columns are fixed and levels are pretty much hard coded. In the future, if someone gets promoted, demoted, retired, or given a new rank that didn't exist before, it will become very difficult to maintain this kind of records in structure based hierarchy. The reason why time dimension is great for structure based hierarchy is that, the calender system that we use in our daily life doesn't change. In other words, 'February' will always be in 'Month' column, it will never get 'promoted' and become 'quarter' nor will be demoted to be 'Week'.

Based on the simple concept, you should be able to think of other dimensions that can be good candidates for either structure based hierarchy or value based hierarchy. It can also depend on the business process of each individual company or industry, it can also base on determining factors, such as pricing based hierarchy or promotion based hierarchy and so on.

Therefore, when it comes to determining what type of hierarchy should be implemented in OBIEE, the decision should be made way prior to it gets to OBIEE. It should be a decision made during the designing of project roadmap.


Until next time


Saturday, July 20, 2013

Navigating between reports and dashboards when there is no common fields to pass parameters

Hello All

Today I want to talk about navigating between reports and dashboards. As we all know, this feature can be done in 11G through action frameworks, this allows us to click on any particular value of the actionlink defined column and navigate us to another dashboard page (BI Content if we choose it in the actionlink) and the value we clicked previously will be filtered.

This is a pretty standard feature that has been part of OBIEE product since it was still called Siebel Analytics. However, in order for this feature to work as straightforward as it is, the condition is that there must be a common field used in both reports. In other words, if you want to navigate from report 1 to report 2 by clicking on the values of a column, then that column needs to exist in both reports for this to work.

This becomes an issue sometimes when that column is technically not the same column yet it is the same column from the business perspective. In other words, we have all used alias in our data modeling and it gives us a lot more flexibility in creating the kind of reports we want. Alias of the same physical table can be used in different star and snow-flake schemas without causing conflicts. However, these alias columns, although being referred to the same physical table, aren't being treated the same column from OBIEE.

Here is the case:

We have a dashboard here that I am going to click on any values of 'Vlan Interface', then the page should become another dashboard with only that 'Vlan interface' filtered

However, it is not working. The destiny report is successfully being fetched, however the filter box is empty. This means the value that we clicked was being passed from the first page, but it is not received in the destiny page.

The reason here is that, although both the Vlan Interface column and Interface prompt has the same data and they are from the same interface column in the physical table, they are different columns according to OBIEE. They are from different folders and they have different names, they simply exist in different places.

 I wish OBIEE can have the intelligence, or an added feature that allow users to define actionlinks by including or excluding the navigation of the actual physical column. Well, until that happens in the future, we just have to work around it now.

To make the destiny dashboard receive the values passed from the first dashboard, there must be a common field between the 2. Since we want to click on 'Vlan Interface' and go to the second dashboard, then we will create a dashboard prompt using 'Vlan Interface' columns and then place it on the destiny dashboard:

In this prompt, we will set the default section as SQL statement:

Select "Node Interface", "Node Logical Interface" from table where rcount("Node Interface", "Node Logical Interface") = 2.

This is nothing but setting a default value so that if you do come to this dashboard by direct access, this prompt will have some default value to run against all of the reports there. If you access this page by clicking on 'Vlan Interface' value from the other dashboard, then the value you click will be shown in this prompt after the navigation. This is basically the idea. I choose rcount = '2' simply because I know the first value in my table is 'N/A'.

Set the presentation variables here, we will pass this variable to the existing 'interface' prompt on this page later.

Now that we have created this prompt, we will put it on the destiny dashboard

Now that this prompt is on the dashboard, but I want it be work behind the scene only. I don't want users to see this prompt and the 'interface' prompt at the same time. So it's just the matter of hiding this section all the time.

Simply create a report that always returns some data and use it as the condition:

Define a condition in the section Condition:

True if row count = 0 determines that this section will display only when the row count of this report = 0, which is always going to be false. This implies that this section will always be hidden.

Now, we need to modify the existing 'interface' prompt to make it affected by the 'Vlan interface' prompt that we just created.

Here, the idea is to change the default section of this prompt so that it's default value is whatever 'Vlan Interface prompt' passes :

SELECT "Node Interface"."Node Logical Interface" from Table where "Node Interface"."Node Logical Interface" = '@{V}'

Here, the presentation variable 'V' could be the value of rcount = 2 in the physical table or whatever Vlan Interface value the user click from the first page:

Now, let's give it a test. First, we go to this dashboard page with direct access. We see that the 'Vlan Interface' prompt is hidden, and the interface prompt is showing a default interface value, this is good.

Now, let's go back to the first dashboard page to test the navigation part:

Click on Vlan interface 'ETH Vlan 1410 U1/M1/IF0':

We arrive at the destiny dashboard with 'ETH Vlan 1410 U1/M1/IF0' being passed successfully:

Everything looks as if nothing extra has been done, this is perfect.


Until next time

Saturday, July 6, 2013

Calling Javascript function from OBIEE dashboard

Hello again

This time I am going to show how to call javascript function from OBIEE dashboard. The script can be written and saved in the common.js file located in:


I have written a simple script as the follow and I saved it in the bottom of the common.js file:

// MY Stuff
function obi_popup()
alert("Hello World")

So as you can see, the function name is called 'obi_popup' and the function itself is supposed to return a text 'Hello World' when called.

Once this is done, restart the OBIEE opmn service.

Go to the dashboard and add a text section. In there add the following text:

check "contain HTML markup" box

This will appear a button labelled 'hi there!', and by clicking on it, the javascript 'obi_popup' should be called

Click and the result:


until next time

Saturday, June 22, 2013

Restarting all OBIEE 11G domain services in linux and common issues


Although there are a lot of articles out there that talks about how to restart OBIEE 11G, I find them tend to be less clear to beginners. Trust me, if you have just started using 11G, you will find out that there are some common issues that you will run into when you are restarting your OBIEE system. So today, I want to gets these things straighten out.

Let's start and assume that your OBIEE is already up and running, and you have made some configuration related changes or you have to go through some other system maintenance, which requires you to shut down OBIEE and start it again. Now, I am not going to explain why to do or how OBIEE architecture works,  I find that unhelpful to a lot of people, so let's start with steps.

To Stop OBIEE 11G domain components:
(Duh! restarting means stop, then start)

1. Stop Opmn

in your Linux terminal window, run the following command line:
Go to the following path:
$your OBIEE main folder/instances/instance1/bin/

Then type
./opmnctl stopall

2. Stop Managed Server

Go to the following path:

$Your OBIEE folder/user_projects/domains/bifoundation_domain/bin/

Then type
./stopManagedWebLogic.sh bi_server1

Enter the credentials for the WebLogic administrator (in my case it is weblogic/weblogic123) when prompted.

Let it run and it will be done eventually. (Duh!)

3. Stop Node Manager

Since there is no script that stops the node manager, you will have to kill it, so type the following in your terminal:

ps -ef |grep Node|grep nodemanager |cut -c10-15

It will show the Node Manager process, there is an PID that comes with it the result, note it down and then type:


After having done so, you can re-execute : ps -ef |grep Node|grep nodemanager |cut -c10-15

This time, it shouldn't return any results, that means node manager has been stopped.

4. Stop WebLogic Domain

In the same Terminal go to the following path:

$Your OBIEE folder/user_projects/domains/bifoundation_domain/bin/

Then type:

Let it execute and eventually it will complete the stoppage.

Now starting all OBIEE domain components:
You will eventually realize that the sequence of starting each components is pretty much in reverse of the stopping sequence

A. Start WebLogic Domain

In the following folder:

$Your OBIEE folder/user_projects/domains/bifoundation_domain/bin/


If you want to have it run in the background, which means the component remains started after you close the terminal session (you pretty much have to do it this way), type this:

./startWebLogic.sh &

Now if you are restarting, it is likely you will run into some issues at this step, so scroll down to where I provide the error handling

B. Start Node Manager

Go to the following path:

$Your OBIEE folder/wlserver_10.3/server/bin/

./startNodeManager.sh &
(for running in the background)

Let it run and keep the windows opened for a while

C. Start Managed Server

Go to the following path:

$Your OBIEE folder/user_projects/domains/bifoundation_domain/bin

./startManagedWebLogic.sh bi_server1 &
(for running in the background)

Just like step A, it is likely you will run into some common issues, if you do, please scroll to the error handling section of this post to see if the errors I mention here applies to you or not.

D. Start Oracle BI System Components (OPMN)

go to the following path:

$Your OBIEE Folder/instances/instance1/bin

./opmnctl startall &
(again the '&' is for running it in the background)

Error Handling:

Error 1:
There are 1 nested errors:

Weblogic.manaement.managementException: Unable to obtain lock on /path......../AdminServer.Lok. Server may already be running

It is pretty common that you will run into this error, it looks like this in my system:

When you see this error, just go to path which I highlighted and you will find the AdminServer.lok file or bi_server1.lok file and delete it:

Basically, AdminServer.Lok and BI_Server1.lok can be found here:

If AdminServer.lok exists, it will cause errors when you start Weblogic (In Step A)
If Bi_server1.lok exists, it will cause errors when you start Manage Server (In Step C)

Simply delete these files and execute the run script again, the errors will go away.

Error 2:

When you are running these starting scripts in the background as indicated in Step A and C, the system prompts you to enter the username, when you do so, you end up getting 'weblogic: comment not found' and the system doesn't get started. When you run the script without '&', everything worked normally. The behavior is seen as following in my system:

When you see a behavior like this, it has something to do with boot.properties file not found in the following directory:

/user_projects/domains/bifoundation_domains/servers/AdminServer/security --- This will cause above error when you run step A 

/user_projects/domains/bifoundation_domains/servers/bi_server1/security/ -- This will cause above error when you run step C

Now you can either create your own boot.properties file in both directory, or you can find the existing boot.properties file in the following directory:

/user_projects/domains/bifoundation_domains/servers/bi_server1/data/nodemanager as shown:

If you open this file, you will see that the weblogic admin user password has been encrypted. If you would, just copy the boot.Properties file from '/user_projects/domains/bifoundation_domains/servers/bi_server1/data/nodemanager ' to the following 2 directories:

/user_projects/domains/bifoundation_domains/servers/AdminServer/security --- This will take care of the above error when you run step A 

/user_projects/domains/bifoundation_domains/servers/bi_server1/security/ -- This will take care of the above error when you run step C

In Summary:

When you get these 2 errors handled, your restarting of OBIEE will be smooth 90% of the time.


Until next time

Thursday, May 23, 2013

Upgrading OBIEE from 10G to 11G and post upgrade activities Part 2

From the previous entry of this series, we went through what was like to upgrade OBIEE 10G to 11G, which was not bad. We also covered some of the post upgrade activities that we pretty much had to do for the system to start working. This time, I want to expand on that and add one more post upgrade activity that is almost a must for everybody going from 10g to 11g.

In order for 11G to successfully connect to the DB and fetch data, we must have the proper Tnsname file stored on the server machine. Normally, the Tnsname file is stored in the OracleDB Home/Network/Admin directory. For OBIEE 11G, there is one more directory the Tnsname file needs to be copied to:

Your OBIEE 11G Home/Oracle_BI1/Network/Admin

Your Tnsname.ora file should have all of the connectivity information for the DB machine, if not you should consult with your DBA.. If you are not sure whether the Tnsname is correct or not, you can always try connecting from your OBIEE server machine to the DB Machine through sqlplus.

In you Putty terminal:

$ sqlplus username/password@instance name

If your connection is successful, that means the TNS Entry is working in the file, then move on to the following step.

Modify the user.cmd file from the following directory:

Your OBIEE 11G Home/instance/instance1/BIfoundation/OracleBIApplication/coreapplication/setup.

The user.cmd file has the environment variable $TNS_ADMIN, which by default has no value. The path where tnsname.ora file was located from the above-mentioned step will need to be entered in the user.cmd file. Below is a sample file from my environment after modification, which is linux:

@echo off
REM This file is a placeholder for setting environment variables

After that, restart OBIEE service in Weblogic's em interface.

You should be able to connect to the DB from OBIEE.


Tuesday, March 26, 2013

How to locate the reoccurring character in the string


Here is an interesting requirement. Look at the below report:

You notice that all of the PWE IDs have 3 '_'. Now I want to know the location of the last '_' in the string, what do I do?

OBIEE has locate and LocateN function at our disposal. However, the locateN function doesn't work that well, but the Locate Function does work.

So let's start with that:

Create a new column and enter: LOCATE('_', Pseudowire."PWE ID", -1)

the '-1' part is going to locate the '_' from the reverse side of the string, this is what we want

The resulting integers are the correct location of the last '_' if you count them :

Now, let's get rid of all of the strings after the last '_', maybe I will include the '_' as well.

Create another column in the report with the following formula:

Substring(Pseudowire."PWE ID" from 0 for LOCATE('_', Pseudowire."PWE ID", -1) -1)

Now we will use substring function. The 'for' part will be the location of the '_' that we located from the above step, and the 'from' part will be 0, which means we start from the beginning of the left side and we keep all of the strings up to the location of the last '_'. The last -1 in the formula is basically getting rid of the last '_' as well.

Now test:

Works perfectly.


Until Next Time

Friday, March 22, 2013

Working with Go URL and passing parameters to dashboards and reports


It's been a while. Today, I am going to talk about GO URL and how to make it work. This can be very handy once you make it work. 

The idea of GO URL is that, it will be able to navigate to other BI Contents, such as reports and dashboards while passing certain parameters along. This can be very useful if you are coming from external application navigating into OBIEE. 

So let's look at a few URLs I wrote and we will break it down:

1. This one takes you to the report and passes 2 parameters, components and event time

So here lets look at the part 'Go&Path=' onward.

/shared/inSight+SMB/Administration/Audit+Log/Audit+Log+Report : This represent the location of the report that I want to get to. I am filling all of the spaces here with '+'.

'&Action=Navigate': This is the commend for navigation.

Following are the parameters which are represented by P0, P1, P2 etc.
P0 determines the number of parameters I am going to pass. In this case, it is 2: column 'Component' and column 'Event Time', which you will see later in the url.

P1 represents the operators. 'eq' means equal.

P2 represents the column name in the format of table name.column name. Here if the name contains space, then the whole name needs to be double quoted and space needs to be replaced with %20. Therefore, so Audit Log. Event Time becomes “Audit%20Log”. “Event%20Time”.

P3 Represents the value that you are going to pass, the hardcoded value needs to be double quoted as well.

When you want to pass more parameters, first change the value of P0 accordingly, in my case it is 2. You will then use P4, P5,P6 the same way as P1,P2,P3. This will go on until all of your parameters are defined.  The value of my P4 is 'bet', which means the operator for this one is 'between'. The way you define 2 values for 'between' is = 2+"value1"+"value2"

Last but not least, you attach '&NQUser=Administrator&NQPassword=justforyou', which is the username and password of OBIEE to your url.

That's it, now we can test it out in both OBIEE 10G and 11G:



This one takes you to the dashboard and passes 1 parameter Date Day = 2013-03-12:

Here, things are pretty much the same way as the above url, just a few different things. Here the 'Go&Path=' doesn't quite work, therefore we have to use  'Dashboard&Action=navigate' followed with parameters.. We are just passing 1 parameter in this case, which is date.

'&Page=' part defines the name of the dashboard page I am going.

 '&PortalPath=' This part is the path of the dashboard. Notice that every '/' has to be replaced by '%2F' and all of the spaces filled with '%20' or '+'.

Now let's test this url in both 10g and 11g:



This is pretty much it.

The third link does the same thing as the second link except the parameter is a little different, I will let you figure it out then.


Here are a list of operators and meanings from Oracle Documents.

Equal to or in.
Not equal to or not in.
Less than.
Greater than.
Greater than or equal to.
Less than or equal to.
Begins with.
Ends with.
Contains any (of the values in &P3).
Contains all (of the values in &P3).
You need to type %25 in place of the usual % wildcard. See the examples that follow.
&P3 contains 1+n, where n is the number of top items to display.
&P3 contains 1+n, where n is the number of bottom items to display.
Between (&P3 must have two values).
Is null (&P3 must be 0 or omitted).
Is not null (&P3 must be 0 or omitted).
In this parameter, ttt is the table name and ccc is the column name. If the table or column contains spaces, it must be quoted with double-quotes. Spaces should be escaped as %20, for example, Measures."Dollar%20Sales".
In this parameter, n is the number of values, and xxx, yyy, and zzz are the actual values.
Note: If the value of P3 begins with a numeric character, the entire value must be enclosed in quotes. For example:


Until Next time

Related Posts Plugin for WordPress, Blogger...