Query with CTE gives error

[michaeljc70]

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 

[HI1]

What you're explaining about recommended methods is a great solution where upstream structures are well established, actively maintained, and properly optimized for downstream consumption.

What you ask about why certain processes aren't handled upstream is a perfectly valid inquiry to make, when making those adjustments is within the available allotment of bandwidth/capacities dedicated to addressing downstream requirements.

That is evidently not always the case, and if making analogies is the way to deal with explaining different perspectives, I believe yours could be modified from "nails and screwdrivers" to "hex rivets and socket wrenches", and everybody could be in agreement!

[michaeljc70]

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.  

[austinsense]

Ok, cool we're on the same page

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.

[michaeljc70]

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.

[austinsense]

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.

[michaeljc70]

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.