Hi,
Just a quick question to ask what happens with the SQL statement from a multi sourced universe?
Let’s say we have a .unx created from SQL Server & Oracle. What does the SQL statement show with objects from both databases? I’m about to embark on creating an multi sourced universe from Netezza & Oracle and will be getting involved at an early stage (database design, modelling) so want to ensure I’m talking sense, not nonsense!
Thank you.
BObjectz (BOB member since 2010-08-17)
Before you embark, I’m 99% sure this is not possible.
Essentially I’m guessing this is due to how the 2 platforms work, Oracle a straight RDBMS and Neteeza is more of a DB appliance. Internally they work completely differently in how they deal with processing a SQL statement.
Mak 1 
(BOB member since 2005-01-06)
Hi Mak,
Wow - okay. That could be a showstopper. I will have to do some digging on that. Do you know where I could find any further information please?
But in regards to my initial post, do you have an idea of what happens with the SQL across a multisourced .unx?
Thanks again.
BObjectz (BOB member since 2010-08-17)
Worth checking, it may be supported now.
Looking at the PAM for 4.1 SP5 it looks like it is actually compatible depending on the Netezza version and the DB driver used.
Nick Daniels suggetsed this couldn’t be done here, but unsure what SP he is running:-
https://bobj-board.org/t/194246
Unsure how exactly it works although its woth noting it has its own SQL like syntax. In addition this is not well documented.
Mak 1 (BOB member since 2005-01-06)
No idea on the use of specific databases, but I did manage to combine oracle and sql server data sources. Eventually I realised I needed some kind of hybrid generic SQL that both systems recognised! My date efforts are in the dates sticky here and most of the rest of the stuff I needed came from here:
debbie
Debbie (BOB member since 2005-03-01)
Nice one Debbie, so it is a wider used SQL hybrid then? Who put you on to the document?
Mak 1 (BOB member since 2005-01-06)
Google and sheer determination? 
debbie
Debbie (BOB member since 2005-03-01)
Thanks debbie…
Does it then send the exact same SQL statement to both databases? I’m really struggling to see the value of the multisourcing as it sound a bit of a mess. It seems to be a bit of a glorified data provider.
BObjectz (BOB member since 2010-08-17)
Multisourcing should be considered a work around as far as I’m concerned.
I can’t see the preformance will be any good either.
Mak 1 (BOB member since 2005-01-06)
So, just going back to my original question if that’s ok. Let’s say I create a unx with 2 connections:
I create a bunch of objects from both databases. I then create a query that uses objects from both databases. Which database does that SQL statement run against? Or does it create 2 SQL statements and run against both databases?
We’re having difficulties using the multi source option at the moment (hence the reason I haven’t just tried to do it myself quickly) so if anyone has an answer, that would be great.
Thanks.
BObjectz (BOB member since 2010-08-17)
Well the code would have to be run against both databases, in this scenario, for this logically to work :).
I must admit I don’t know about the translation side i.e. whether the IDT syntax is converted to proprietry SQL, however.
Mak 1 (BOB member since 2005-01-06)
Thanks Mak (again)
I’m really struggling to see the value here. There’s potential then, it would seem, to duplicate (at best) every SQL statement run against a multisourced unx. Imagine a combination of objects across 3, 4 sources, 4 identical SQL statements running against 4 different databases. That’s worse than 4 different data providers IMO, because at least each data provider has a SQL statement specific to the objects pertaining to it. Ahhh, I’m rambling… just seems very strange. Maybe I’m getting the wrong end of the stick, quite possible!
BObjectz (BOB member since 2010-08-17)
As I said clever functionality, would I want to use it, no ;).
A work around as far as I’m concernned for people that do not have / do not want to develop, a proper data warehouse.
This post gives a little more tech info:-
http://scn.sap.com/community/semantic-layer/blog/2012/10/12/bi4-performance-optimization-for-multi-source-universes-sapbw-jco-queries–part-1
Mak 1 (BOB member since 2005-01-06)
If it helps, I abandoned the project as it was too slow to be of any real use! I managed to grab some ETL time and get a data mart designed instead.
I don’t know what it did behind the scenes, but as far as I can recall, I was able to use objects from both sources. Where I ran into trouble was trying to create more complex objects with CASE statements in the SELECT. That’s where I found I was having to use very generic SQL.
To be fair, I didn’t do very much as I was using it as a tool to learn IDT as much as anything else.
debbie
Debbie (BOB member since 2005-03-01)
Amen to that 
.
Mak 1 (BOB member since 2005-01-06)
I have a problem with marts though. My users like to be able to verify their data to the source system. In an aggregated mart, you lose that capability as there’s no URN for the individual record - which means that if counts don’t match (and they often don’t due to data quality issues), they don’t trust the mart and don’t use it.
Does anybody else have this problem?
Debbie
Debbie (BOB member since 2005-03-01)
Unsure what you mean about URN, but often you store source system IDs in the fact tables (Natural Key)?
Mak 1 (BOB member since 2005-01-06)
URN I imagine is Unique Reference Number?
I would find out what process the data goes through and track it end-to-end. I’d imagine there’s some kind of data validation or data quality embedded into the ETL processes, so that if 1 record goes in, 1 record goes out and it can be traced.
I feel your pain though Debbie, because with the mart you have the ‘end result’ so to speak and you have to presume it’s been built correctly!
Maybe do some digging with the ETL/database guys and see if you can validate the data at each stage in the process.
If all else fails, just type the correct number into the field they’re questioning 
BObjectz (BOB member since 2010-08-17)