OTBI: Join Corporate Card Data with Expenses Data

Question

I'm trying to build a report in OTBI that details the status of corporate card transactions. We would like to see what transactions have not been placed on an expense report at the end of a billing cycle. I'm using the "Expenses - Expense Transactions Real Time" subject area. What I'm seeing is that billed amounts are multiplying based on how many expense report lines there are. Are there any tricks for combining corporate card data with expense report data?  Thank you.

Nathan CCC · Answer

Hi Ashley, This is now in the OTBI forum. Yes we had the same problem with this use case.  My assumption is that this is a feature of design in this subject area in the metadata repository database (RPD). Basically this subject area has 2 "star shemas"; one for expese reports/items business process and another for credit card process. Unfortunately if you select measures from "both" facts then you can get these incorrect results becuase the entity are not joined correctly in teh data model leading to a cartesian join situation where the amounts are multiples of the value.

If you cutpaste your logical sql from the advanced tab to page /analytics/saw.dll?issuerawsql then view the log then get the with clasue into your favourite text editor so you can color it in/nest/add carriage returns etc to make sense of it you can see where it goes wrong compared to two analyis that just query one or other of the facts. The other thing we noticed is that there is no data security on the credit card fact like there is on expense reports/items so you get to see all transactions no matter what business unit you are setup with.

This was about 2 years ago on my previous implementation. I did rasie a SR with my oracle support at the time but have no access to it anymore. Sorry I cant remember if a bug number was generated or not. I think the workaround we came up with was to take the 2 seperate bits of logical sql from each analysis just with each fact then join the 2 data sets together in a outer logical sql select ourselves so we can control the columns in the on clause then used the create analysis from sql to get a new analysis.  Regards, Nathan

Jamie Anderson-Oracle · Answer

Hi Ashley, this forum is for Fusion ERP Analytics, which is separate from OTBI.

https://www.oracle.com/business-analytics/fusion-erp-analytics/

I am going to move your question to the OTBI Forum.