SQL Server OLEDB or ODBC Error when Direct importing Query

pu3pd22g 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(114)

I have a query that has around 160 million rows that would take me 4 hours to import into Power BI.

Ever since my company changed their server to Azure, I can never import this query successfully. It would start loading for 1 million rows-ish, after 1 minute or two this error always pops out.

I tried:

changing the command time out to 200 minutes, still errors out within loading for a minute or two, sometimes within 10 seconds
if I select top 1000 rows in my query, it will complete without error. But when I switch back to the original query, it always fails.

Attaching the error message. I have talked to the DE in my team and they don't seem to have a clue. Does anyone have any idea on how to fix this?

this is the error message

sql-server

来源：https://stackoverflow.com/questions/73488881/oledb-or-odbc-error-when-direct-importing-query

2条答案

按热度按时间

csbfibhn1#

We had the same problem and went for a delta lake in combination with option 1.

You have to ask your self first why you are importing so much data. Always keep your model as small as possible. I can't imagine you are looking at every row. If this is needed you could you could use a combination of direct query for details and loading an aggregate for your reporting. But load aggregates instead of everything.
Maybe your can load less history, like the last two years.
You could look into loading incrementally, you could load per partition.
You can try to increase the DTU's for your server.

赞(0）回复(0）举报 2023-08-02

sg2wtvxw2#

Try to clean the data load cache: File -> Options and Settings -> Options -> Data Load -> Clear cache and Save the file and Load again

赞(0）回复(0）举报 2023-08-02