locked
SQL instructions length in the PowerPivot QueryEditor RRS feed

  • Question

  • Hi,

    I try to write and execute a query into PowerPivot with the QueryEditor.

    I need to merge a lot of similar files and tables, and so I've created the correct SQL UNION instructions for that.

    The problem is that my Query has 260 lines, and doesn't run.

    The following error appears : "ERROR [HY000] : Microsoft Excel ODBC - Query too complex.

    If I reduce the number of UNION instructions to 100 lines aprox., it's ok, it works.

    Any idea ?

    Thanks a lot for any help !

    Jean

    Thursday, July 30, 2015 1:01 PM

Answers

  • Hi !

    Thanks for your suggestion.

    I've installed PowerQuery, and it's very useful add-in. Thanks a lot !

    I will use it certainly.

    At the end, before testing PowerQuery (that I am discovering), I've written a VB script which transform my flat files (as UNPIVOT), and then I load them into PwPivot and it's ok.

    I prefer PowerQuery, I've the impression that I will be more productive and the solutions will probably be easier to maintain. On the other side, a VB script can be executed super easily, and doesn't require an add-in or other on the Excel client.

    But thanks a lot .... I've solved my problem

    • Marked as answer by Charlie Liao Friday, August 7, 2015 5:40 AM
    Wednesday, August 5, 2015 1:53 PM

All replies

  • Take your SQL UNION query, put it in a stored procedure and use EXEC to execute it.
    Thursday, July 30, 2015 3:08 PM
  • Hi,

    Thanks for your answer.

    It's a solution that I was studying, but I've understood I need for that a SQL Server, and my problem is that I only use flat files or excel files. I use an ODBC driver to connect to the flat files, and it works .. but when the query instructions is too long, the query is then no supported.

    Can I create a stored procedure without a database engine ?

    Thanks for any help ?

    Friday, July 31, 2015 8:14 AM
  • Ah, I assumed that  you had a SQL Server. Any possibility you can paste your SQL union here and see if the community has some input on how to shorten it up?

    Another possibility is that you import all the flat files and do you unions in DAX, but you would need 2016 functionality for that:

    https://support.office.com/en-US/article/UNION-Function-DAX-0e5f69cd-9fe3-412a-a796-3cc9de67587b

    Friday, July 31, 2015 10:32 AM
  • I only use flat files or excel files.

    Hello,

    To work with flat file in Power Pivot I would suggest to use Power Query to load & model the data instead, it's also a free AddIn for MS Excel.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 31, 2015 11:00 AM
  • Hi !

    Thanks for your suggestion.

    I've installed PowerQuery, and it's very useful add-in. Thanks a lot !

    I will use it certainly.

    At the end, before testing PowerQuery (that I am discovering), I've written a VB script which transform my flat files (as UNPIVOT), and then I load them into PwPivot and it's ok.

    I prefer PowerQuery, I've the impression that I will be more productive and the solutions will probably be easier to maintain. On the other side, a VB script can be executed super easily, and doesn't require an add-in or other on the Excel client.

    But thanks a lot .... I've solved my problem

    • Marked as answer by Charlie Liao Friday, August 7, 2015 5:40 AM
    Wednesday, August 5, 2015 1:53 PM