Let us know if you hit a snag ...

OR build a proper Calendar dimension and dispense with the calculations each time you need a variation on it. Calc ONCE use MANY ...

ORA-01861: literal does not match format string ^ means OBIEE sees the date/timestamp different than you think it does ... refer to that link from @joel_acha for more info.

Amazing things can be done wil the formation of a proper calendar dimension ... FIRST thing I look for at EVERY client I go to. If they don't have one - they get one on day one!

Using fully qualified names you shouldn't have an issue ... providing your one connection pool can 'see' both schemas. The issue of performance will arise if the schemas are in different databases - a cross database join will be slow. To add the two tables to the physical diagram to build the join - treat them as if they…

It's a governance nightmare let alone the management complications

Why not configure Usage Tracking and model it yourself ... there's only a handful of database objects and then you can do what you want - which is what you will end up doing anyways.

I'll ask again ... what have you tried? ever put YEAR AND MONTH on the x-axis? what allows you to do that? treat HK1 (etc) as year and 100G,10G,1G as months ...

Kabu, Then ALL of his presented data has to be text/varchar ...

joining on EXTRACT (YEAR from DimDate.ACTUALDATE)-1 will still give you a row with a year number FOR EVERY detail row you have ... this is not the way to aggregate. You need a proper calendar dimension with proper logical hierarchies set and (as been said) tell the system how those work by setting the appropriate content…

Test for it ... "I am sure some..." is a conclusion based on what?

what do the physical joins look like? between DWF_SALES and DWD_TIME_DAY? between A_DWF_SALES and A_DWD_TIME_DAY?

These two time dimensions ... did you start with aliases in the physical layer? I'm wondering if you get no results because of the resulting physical SQL joins that result in how things are modeled.

"is that they also want to self govern their own users." = shadow-team = enterprise additional costs and risk

It's working as designed ... unless you know 100% that you've timed out a query OR that you've blown through a row limitation ... ALL other failures get lumped to #3 SuccessSUCCESS_FLGThe completion status of the query: 0 - The query completed successfully with no errors. 1 - The query timed out. 2 - The query failed…

If you want to learn the 'backend' then get yourself another 'front end' job on an OBIA implementation and then ask the ETL lead(s) for access to learn. To stand-up your own infrastructure just to kick the tires is going to be a huge effort. You'll have to have the source ERP database as well.

ODI is not strictly based on SQL ... it's SQL buried in many scenarios based on configurations (especially with OBIA).

1. import the object (as already counseled) 2. reserve opaque view for teporary/POC work (there's a serious performance consideration present in this technique) 3. if you have to hint ANYTHING in OBIEE your data design is deficient (I have been handed RPDs with hinting that has crippled performance)

What have you tried?

Federate your data physically... using a logical layer for integration is good for POC/discovery work, but if in a production mode physically co-locate the data. One of the issues with the logical cross-database joining is the fact that the predicate filtering will get done on the logical end, so physically you will move…