• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Query to Connect to Oracle Database

Hi,

Below is the URL I use to connect to Oracle database in Squirrel SQL. What is the Server name that I need to give for connecting to Oracle Database from Power Query. Kindly help here. I have installed Oracle Addin and Power Query add in.

URL : jdbc:eek:racle:thin:mad:(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = qa-skyhawk-sc01.abcd.com)(PORT = 19123))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = edmsqa.abcd.com)))
 
There are few things you need to be aware of...
1. You should have "Oracle.DataAccess.Client" installed. There may be legacy connector present, but that's been deprecated by MS.
2. Bitness of connector/client and PowerQuery/Excel must match. I.E. 32 bit or 64 bit.

Then there are few things you can try for your Sever:
1. ServerName/SID
2. ServerName/ServiceName
Where service name is alias used when connecting.
3. If still having trouble, use following string (replace values).
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_name)(PORT=port_num))(CONNECT_DATA=(SERVICE_NAME=service_name)))
 
Hi,

I have a database with millions of records to which I am connecting. When I make the connection It is taking a long time to bring the data in the worksheet. After refreshing for a long time, excel sheet gets hanged. What to do in this kind of tables with millions of records.
 
1. Excel's row limit is 1,048,576. So if your record is over that limit, data should be loaded directly from query to data model using Get & Transform / PQ. Then use Pivot Table to report on data.

2. If query itself isn't optimized it will slow down data import. Typically, I'd make sure table(s) are properly indexed, to avoid table scans. Avoid using functions in predicates. Avoid use of wildcard at start of predicates. Only select necessary columns. Whenever possible, use inner join instead of outer join. Avoid use of Union/Distinct. Avoid joins on Views. etc etc...

3. If some of above isn't unavoidable, consider using temp tables in query or using stored proc etc.
 
Back
Top