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.