April 2, 2012 at 6:12 am
afternoon! (or morning, whatever your TZ)
I am about to move onto a project that is causing me some head scratching. Basically, within our business we have several very large but bespoke applications that come supplied with Business Objects and a universe to interrogate and report from the data.
Without giving the massive back story as to why, it would be extremely useful if we could interrogate a BOXI universe within SSIS directly. Has anyone done this / know if this is possible? Someone on another site suggested an ODBC connection with the relevant TNSNAMES file but before I try to figure this out (I have no experience using Oracle at all, even as a datasource) I wondered if this is definately possible.
Your help would be appreciated.
Thanks
April 2, 2012 at 7:18 am
If the data is stored in a relational database engine, chances are good that you can get it with SSIS.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 2, 2012 at 12:37 pm
I am sure it is, but, do you know how? because that's what I was after! 🙂
April 2, 2012 at 2:29 pm
Hi
Connecting to Oracle is not a big deal
Just you have to do down load the client tools on your dev and prod box
Use the ODBC connection to get the data from oracle
Thans,
Mark
April 2, 2012 at 3:44 pm
sqlserver8650 (4/2/2012)
HiConnecting to Oracle is not a big deal
Just you have to do down load the client tools on your dev and prod box
Use the ODBC connection to get the data from oracle
Thans,
Mark
If the data is indeed in Oracle, you can also use the Oracle OLEDB provider.
Make sure you install the right versions: 32 and/or 64 bit.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 3, 2012 at 12:12 am
But can you connect to the boxi universe using the oleb db for oracle?
April 3, 2012 at 12:16 am
ProKelly (4/3/2012)
But can you connect to the boxi universe using the oleb db for oracle?
You mean connect directly to the universe? Not sure if that is possible with SSIS. SSIS is used to extract data, while the universe is a fancy GUI on top of the relational datasource that translates user requests into SQL.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 3, 2012 at 4:30 am
The universe in our instances is a supplier provided translation layer of the relational database. Whenever one of those products is upgraded, the supplier also provides a modified / updated universe to query with, which is great because it means I dont have to unpick the relational model for each of these products to understand where the data is. That aside if we had to unpick that and access it without the universe we would have to sack the job off as we don't have the resource to get to grips with the schema of each of the products, and then amend SQL everytime the database changes following an upgrade.
That's why I am where I am. If anyone has been in the same situation or has managed to query through a BOXI universe I would be much obliged. That said, if someone has encountered the same problem I have and have an insightful approach to tackling I would quite happily place a fresh beer on the payment table for your keen advice!
April 5, 2012 at 6:51 am
You cannot interrogate the universe directly, but if you create a web intelligence document with all the requried objects, the query generates SQL that you can access and copy and paste into SQL Server query
James
April 5, 2012 at 12:50 pm
WHAT!!??
This is amazing news, you have a man hug and several thousand prokelly points if you can tell me if you can see the sql within the WEBII report or you need to run an oracle equivalent of sql trace?
April 5, 2012 at 3:15 pm
Yes,
create a web intelligence document, introduce your objects to the query and you should see a SQL button - when you click it, you see the SQL.
James
April 5, 2012 at 3:50 pm
Thank you, James.
This gives us the ability to be able to understand the translation the universe is applying as well as getting round the horrible way of pulling data into Integration Services that we had planned - schedule an excel report to push to a file location which we then suck in and convert. Very messy.
This is very helpful.
Thanks again.
ProK
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply