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

Hello

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):

Thanks

Until next time

Read More

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.

Thanks

Until next time.

Read More

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.

Thanks

Until next time

Read More

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

....txt

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:

Homogeneous

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.

Heterogeneous

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.

Read More

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

Hello

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.

Thanks

Until next time

                    

Read More