topic Re: Query with CTE gives error in Desktop

Query with CTE gives error

michaeljc70 — Wed, 09 Mar 2016 23:12:11 GMT

I used SQL Server and a query that uses a CTE. It gives me an error "Incorrent syntax near the Keyword 'WITH'. ......."if this is a common table expression......previous statement must be terminated witha semicolon."

The query runs fine outside of Power BI. I tried putting a semi-colon before the 'WITH', but that didn't work. I see the data in the grid, but can't apply query changes and get the error

Re: Query with CTE gives error

michaeljc70 — Wed, 09 Mar 2016 23:15:46 GMT

One more thing...though I can see my data under "Edit Queries", I cannot create a visualization because the query doesn't appear under "Fields".

Re: Query with CTE gives error

austinsense — Thu, 10 Mar 2016 02:26:16 GMT

Are you accessing a view that uses a CTE or did you write a SQL query that uses a CTE?

If it's the latter, then I'd suggest creating a view and then querying the view.

Re: Query with CTE gives error

michaeljc70 — Thu, 10 Mar 2016 03:59:40 GMT

I wrote a query that uses a CTE. I don't have permissions to create a view or stored procedure.

Re: Query with CTE gives error

austinsense — Thu, 10 Mar 2016 12:01:32 GMT

Gotcha, putting a custom sql query into the query builder is not recommended - as you can see :smileyhappy:

Here are your options ...

1. Create a view - sounds like this isn't an option for you

2. Re-write the query without using a CTE - that would be a good test of your SQL skills

3. Stop using the custom sql code, query the underlying tables using the GUI, and use PQ to create the output you want - that would be a good test of your PQ skills

Re: Query with CTE gives error

michaeljc70 — Thu, 10 Mar 2016 14:12:20 GMT

I am not sure why it isn't recommended. I don't see the documentation saying that and I don't see a list of query limitiations.

I am evaluating Power BI to determine in my organization wants to buy and use it as a visualization tool. I am not trying to "test my skills". I am trying to evaluate products to see how they work (or if they work in this case).

I cannot import the tables and join them because they are too large. I cannot use Direct Query because it doesn't support many to many relationships. All I see are a lot of llmitations.

Power BI is supposed to be self-service. That typically means not writing views or stored procedures (and generally not CTEs) and if a feature is there, I expect it to work.

Re: Query with CTE gives error

austinsense — Thu, 10 Mar 2016 15:41:49 GMT

FYI, most people start these conversations with "thanks for trying to help me ..."

Writing custom SQL query generally works just fine - it's just recommended that you don't do it that way, that you create a view. Along those lines self-service BI doesn't mean that people are going to write SQL queries with CTEs to access the data, so I don't see not being able to do that in the tool as a major limitation to self-service BI. The only limitation that you have here is that you need a view created in your database, and that's not a limitation of this product.

To get around the limitation of creating a view, this solution does allow you (as a self-service tool) to take the tables out of your database and do the joining yourself using the query editor GUI. Your data must be very, very large if you can't get that to work in Power BI, and if your data is that large I'll ask you again, why aren't you doing that in a view in your database? If you have a nail that you need to put in a wall and you reach in your bag and get a screwdriver, well I don't think that's the screwdriver's fault.

One last point, I realize you're not trying to "test your skills" but I will say that this product is not BI magic dust - it does require some skills to use. If you're the type of person that can write a SQL query with a CTE then you'll be able to figure it out. Unless you don't want to figure it out.

Re: Query with CTE gives error

michaeljc70 — Thu, 10 Mar 2016 15:53:12 GMT

I do appreciate any help. I was just hoping for an answer as to why it doesn't work rather than how to work around it. If there are limitations in queries in Power BI, that really should be documented by Microsoft.

This would be used with a data warehouse, so the tables often have millions of rows.

As a developer, of course I can write queries (or views or stored procedures), but management doesn't really understand how this all works and thinks anyone can get a tool and drag tables and fields around and get reports and visualizations. They see a 2 hour demo and think it is so easy (which it is if you know the tool and have cultivated data). At a minimum, I believe unless your dataset is small and/or carefully cultivated, IT (or technical business people) needs to create views, cubes or queries.

I completely understand that that is not a Power BI limitation strictly, but a self-service limitation that all these tools have.

I'm going to communicate this all to my client, and if they don't buy it suggest they have several potential report writers try and create reports with the data warehouse as is.

Re: Query with CTE gives error

austinsense — Thu, 10 Mar 2016 15:59:03 GMT

Ok, cool we're on the same page :smileyhappy:

We build and work with models pretty regularly that have 10 million+ rows in them so I'm curious what issues you're having - is Power BI throwing out some kind of error when you try to bring in your data? Someone from this community could definitely help you work through this issue.

Re: Query with CTE gives error

michaeljc70 — Thu, 10 Mar 2016 16:19:21 GMT

If you aren't writing a query, Power BI doesn't seem to support many to many relationships if you can get the data loaded.

Some of the tables have 100 million rows. That is why I don't think it is realistic to not be writing quereis or at least views that break down the data further.

As an example, we have around 500k accounts and then daily information about each account in a table. Over 100 days, that is 50 million records.

Re: Query with CTE gives error

austinsense — Thu, 10 Mar 2016 16:46:35 GMT

That's a lot of data. For starters you could strip off most of the columns just to get the data loaded - Power BI has a very good compression engine depending on the data in your tables but no need to load everything right from the start.

You're right M2M relationships aren't possible directly - you'll need a bridge table (1:M,M;1) which is where the query editor comes in. There are lots of good posts on here about writing calculations with M2M table relationships.

Re: Query with CTE gives error

michaeljc70 — Thu, 10 Mar 2016 17:06:30 GMT

Thanks for the information.

In a lot of the data I'm dealing with, the record counts explode because of history. Typically, they would be working with the last available date. They could also be using the last month end or comparing 2 months or weeks. To cover all those scenarios with views would be difficult. Stored procedures an end user can call with a date(s) might work. Or an automated process to create a view per month might work. But there are many other subject areas too that would need to be addressed.

I think that the term "self-service" needs to be used more carefully.

From a philosophical perspective , a large company uses a staff of accountants to do the accounting, not some guy in each area doing pieces of it part-time that he learned from a 2 day training class. Most (large) companies have a legal team. They don't expect each team or area to train someone with another job to learn to handle legal issues. It seems that IT professionals are viewed differently and that their work (or some of it) can be done efficeintly by anybody with a couple days training. Obviously, at smaller companies things are different than at larger companies. Personally, I think it is a lot more efficient having IT professionals write and test reports. It is fine having people do ad-hoc things will carefully cultivated data too. Ultimately, I do whatever my clients want.

This current client had two business area people from finance create their own SQL Server "warehouse". They were self taught, which is not necessarily bad as I am mostly self-taught. However, it wasn't properly tested or designed and much of the data was inconsistent and eventually it got thrown away and was dollars down the drain.

Re: Query with CTE gives error

rowlandshaw — Tue, 01 Nov 2016 16:56:21 GMT

The detail I'd add here, is that the query preview works, it's when evaluating the query that it falls over

Re: Query with CTE gives error

rbbi — Tue, 04 Apr 2017 06:30:52 GMT

your issue and thoughts about it are exactly what I'm facing today. Would be nice if it jsut passed through the query and used the results like it does in the preview. My query also works fine in PowerPivot with a recursive CTE and I was just trying it in Power BI but the same code in Power BI isn't accepted. cheers

Re: Query with CTE gives error

JimLuo — Tue, 18 Apr 2017 14:34:49 GMT

I have the same issue with CTE. It will be much convenient to support CTE. I use a workaround to save data into excel and import it.

Re: Query with CTE gives error

Mandr0id — Fri, 21 Apr 2017 20:39:36 GMT

Same issue with CTE... Someone got it working?

Re: Query with CTE gives error

geoffwill — Wed, 10 May 2017 14:38:50 GMT

Having the same issue with CTEs, however was able to re-write to use a subquery and it works

Re: Query with CTE gives error

Nobby — Wed, 21 Jun 2017 12:17:50 GMT

It's a shame. I also have this problem. I've removed the CTE but the code is much less readable/maintainable. Ho hum.

Re: Query with CTE gives error

jimjao — Thu, 26 Apr 2018 22:49:17 GMT

Hi all,

Using Power BI Desktop (October 2017), I was able to use CTE query along with an OLE DB connection to the SQL Server.

This is no longer truth.

Jim Jao

Re: Query with CTE gives error

jimjao — Thu, 26 Apr 2018 22:55:12 GMT

Hi all,

Using Power BI Desktop (October 2017), I was able to use CTE query with an OLE DB connection to the SQL Server.

So, Power BI does support CTE.

Jim Jao