Actuals and Budgets in the GL_BALANCES table.

Dt :17 Nov 2012

The Oracle EBS table stores the actuals and budgets amounts.Any given record in the GL_BALANCES table is classsifed as either a budget or an Actual by the ACTUAL_FLAG column .

The ACTUAL FLAG column has two values 'A' for actuals and 'B' for budgets.This flag has been exploited in the Oracle warehouse to divide the actuals and budgets into two differenet tables.

So in case you are using the OLAP table W_GL_BALANCE_F  table(assuming that its a replica of GL_BALANCES table ) to get a OBIEE report out and you are expecting to get both the budgets and the actuals information from this table then you may be in for a surprise.
Coz the budgets are not available in the W_GL_BALANCE_F table.This can be verified using the BALANCE_TYPE flag in this table.

You will have to either modify the prebuilt  Informatica map to get budgets information into this table
or the other option that you have is to use the budgets data filtered into the W_ACCT_BUDGET_F table.

Hope this piece of info suits you well for your Finance reports.

Happy reporting :-)

AP_INVOICE_PAYMENTS_ALL & AP_PAYMENT_HIST_DISTS_ALL

Hi,

Recently we were working on a customized report to get  details of all paid invoices into a report.
We decided to use the  prebuilt W_AP_XACT_F,fact  table available in OBIEE to get the reports out.
The fact table supported all the report requirements and all the columns requested by business could be pulled out of the AP Financials subject area.

All was hunky dory till we reached functional testing where we realized that the report was missing some of the invoices which were present in the AP_INVOICE_PAYMENTS_ALL table which held all the paid invoices.

Further digging into the W_AP_XACT_F table we realized that the fact used records from the AP_PAYMENTS_HIST_DISTS_ALL table .
Now ideally since a payment is done its payment history must be recorded in this Oracle EBS table but we realized that some of the invoices which were present int he AP_INVOICE_PAYMENTS_ALL table missed an entry in the history distributions table.
Or to rephrase it some payments in the AP_INVOICE_PAYMENTS_ALL table did not have an entry AP_PASYMENT_HIST_DISTS_ALL table.

We have been trying to link AP_PAYMENTS_SCHEDULE ,AP_INVOICE_PAYMENTS_ALL & AP_PAYMENT_HIST_DISTS_ALL tables and to get the flow of invoices.But with not much luck as of now.

Any guidance on this front will be welcome.

W_MCAL_DAY_D & W_AP_XACT_F

Dt:20 July  2012.

Applicable to OBI Apps 7.9.6.2 and higher

We were going through some financial reports prepared using W_AP_XACT_F and second guessing on why a few invoices were not being  displayed in the OBIEE reports.

Problem with W_AP_XACT_F is that it gets loaded by different arms like Payments,Schedule,Invoice Distribution etc.Each flow having a different level of granularity.

For example the Payments flow just records invoices for which payments are done.So if a particular invoice has not been paid it does not show up in the payment flow.Logically correct

Now consider the new calendar dimension W_MCAL_DAY_D. It has records corresponding to 3 different types of calendars with MCAL_CAL_WID taking values 1000,1001,1002 .

But every dimension table has a default value(zero wid row) which is assigned to those records in the fact for which no matching dimension is present .In case of dates it ideally refers to a scenario where no date could be found.

In W_MCAL_DAY_D though this record has a MCAL_CAL_WID=0 stating that it doesn't belong to any calendar type.

Now, W_AP_XACT_F has records from INV,DIST flow which do not have Payment due dates...For these records the fact has PAYMENT_DUE_DT_WID=0,MCAL_CAL_WID=1000.1000 being calendar type from Ledgers.

So the prebuilt payment due date dimension uses following joins

W_AP_XACT_F.PAYMENT_DUE_DT_WID = W_MCAL_DAY_D.ROW_WID

W_AP_XACT_F.MCAL_CAL_WID= W_MCAL_DAY_D.MCAL_CAL_WID.

In the current scenario whenever  an OBIEE report is developed using this dimension,records from INVDIST,DIST flows are automatically filtered out.

If anyone out there has faced a similar issue then your comments/suggestions will be welcome.

Oracle Tablespaces : Logical or Physical

Dt: 5 Aug ,2012

A tablespace is a storage location where the actual data underlying database objects can be kept. It provides a layer of abstraction between physical and logical data,[1] and serves to allocate storage for all DBMS managed segments. (A database segment is a database object which occupies physical space such as table data and indexes.) Once created, a tablespace can be referred to by name when creating database segments.

Tablespaces specify only the database storage locations, not the logical database structure, or database schema. For instance, different objects in the same schema may have different underlying tablespaces. Similarly, a tablespace may service segments for more than one schema.

By using tablespaces, an administrator can control the disk layout of an installation. A common use of tablespaces is to optimize performance. For example, a heavily used index can be placed on a fast SCSI disk. On the other hand, a database table which contains archived data that is rarely accessed could be stored on a less expensive but slower IDE disk.

While it is common for tablespaces to store their data in a filesystem file, a single file must be part of a single tablespace. Some database management systems allow tablespaces to be configured directly over operating-system device entries, called raw devices, providing better performance by avoiding the OS filesystem overheads.


Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.

SCENARIO
Has anyone of you come across cases where a single table is allocated muliple tablespaces.This scenario is something new to me because we always tend toincrease the size o fteh tablespace whenever the data begins to grow in size. ??
Waiting for your inputs on this one.

Difference between Teradata and Oracle databases

Dt: 5Aug,2012

You will have experts giving a lot of shit whenit come sto doing a comparison between teradata and oracle.Most of their answers end with either 'Oracle is faster/better' or that 'Teradata can handle large data better' .But you find it diffficult to get a basic differentiation between the two.

So here it is the difference boils down to the way in which they store data.

• Teradata is different from other DMBS (like Oracle) because it uses logical addresses rather than physical addresses to store data. This allows teradata to perform (automatic) defragmentation/re-organiazation of data anytime without effecting the addresses.
•  It stores data on blocks, unlike other PAGE-BASED databases. These blocks are variable length that can grow and shrink based on data.
•  Moreover teradata is DBA-FRIENDLY because:
  - It does automatic disk management
  - It automaticly manage indexes (which are store in internal tables rather than B-tress or other structures)
  - It does automatic defragmentation, garbage collection etc
  - It uses automatic partitioning/distribution of data using hashing algos.

Now if someone has  a different perspective on this then you are free to add your comments.

Informatica Architecture 9.1

Dt: 05 August 2012.

While we have had a look at the OBIEE  architecture its worthwhile to have a glance at the new architecture of Informatica 9.1.
This is available at the Informatica support site.Quite a few bit of new components that have been added.Will analyze and report about them as and when I can have a hands on on the latest version.

MUST read -- Page 1

COPYRIGHT NOTE

This blog is written by a consultant working with the  Oracle Business Intelligence Suite. The primary goal of the blog is to help beginners with Oracle BI Apps .I am planning to add learnings ,experiences and scenarios .


1. Paid – To- Blog Disclosure – I am NOT paid to write this blog. This is a personal blog.

2. Responsibility
The opinions on this are personal &  represent my personal views only. I do not represent or write on behalf of any agency, company or employer. I don’t represent views of my employer(s) or client(s). I’m not affiliated with Oracle, nor do I have any claim to their intellectual property.


3. Libel & Do No Harm
My intention is to “do no harm” and my posts are not counsel they are just my opinions and advice. What I write on my blog is not to be taken as fact nor absolute. I can not guarantee accuracy.  If people use my advice, tips, techniques, and recommendations, and are injured or damaged, I am  not to be held responsible.
 

4.Comments
I am not responsible, nor will be held liable, for anything anyone says on my blog in the blog comments, nor the laws which they may break in my country or theirs through their comments’ content, implication, and intent. I have to approve comments first and have the right to refuse them.

5. Copyright
I don’t claim copyright to the works that I quote using fair use principle. If you find copyrighted material that you own on this site – please let me know and I’ll remove it or credit you for it whatever that works for you.


**********************************************************************************
Note: Would like to thank (http://obiee-blog.info/about/)  for this post and credit this post to the mentioned blog.I just picked the copyright clauses and I hope you wont mind this.
**********************************************************************************

Oracle Apps : AP AR Financials module

Dt:25July 2012

If someone asks you to customize the AP,AR mappings in the financials subject areas, then you may consider yourself to be very unlucky.

The AP,AR prebuilt modules utilize some of the most complex mappings.You may take a week or two just to understand the flow of data into the W_AP_XACT_FS table and then finally into W_AP_XACT_F table.

Once you take efforts and understand that logic ,modify ETL & ask for a full load of the target, you   assume that your job is done.But chances are that you will be told that the data has not been loaded properly and that there is a bug in the ETL customizations.

Problem with the AP,AR maps are that there are many restricting conditions and even the most experienced ETL resources in your team cannot say for sure whether a particular record in the EBS table should actually make its way to W_AP_XACT_F .

So even we are faced with one such issue and are currently in a deadlock with the BI and ETL developers blaming each other.Eventually this will be resolved. Will post back the findings once we  deliver this code to the client.

************************************************************************
Dt:26July 2012

Pre built Mappings were modified in custom to get project ids from distribution table .Though Business was expecting multiple projects there are only single projects connected to an invoice as Lookup transformations were used(Informatica 8.6.1) to populate the project Ids. 

So eventually it comes back to the ETL developer as a bug.

Category 1 customizations to SCD2 dimensions-W_PARTY_ORG_D

Dt:24 July 2012

I extended the W_PARTY_ORG_D dimension to add few new columns to this table.I modified the SIL mappings to accommodate the change.

To my surprise I found out that the new records were inserted with proper data in the extended columns but the updates to the newly added  columns were not working.

A little digging in and I realised that W_PARTY_ORG_D is a SCD2 dimension and will require a few settings to the update flag to get the updates working properly.

If anyone of you out there are facing a similar problem or have a solution to this then please right in .In the mean time I wll go about trying to find an ideal solution to this problem.

*******************************************************************************

 Solution:

Well it had nothing to do with SCD2 .It was just that I added extended columns from a secondary source which was not originally taken care of by the update logic .The output port which checks for the SCD1 change is SYSTEM_COLS_DIFF.This port checks for CHANGED_ON_DT ,AUX_CHANGE_ON_DT columns to  find if there is an update on any of the source tables.

AUX_CHANGED_ON_DT columns were provided by ORACLE specifically to handle these type of scenarios.But this is rarely used by Informatica developers doing customizations on prebuilt ETL.

 

Opening Note

Dt :2 may 2012

While many of us techies want to learn a new technology , most of us dont like to do all the hardwork .Just like all of you even I lost a lot of time  to begin the process of learning OBIEE .

Once you started though you want the learning process to be continuous and not punctuated by interrruptions.

This blog consists of easy lessons that I learn every week .The packaging will make it easy for you to learn OBIEE at a rapid pace.