Selecting temp tables & CTEs using PowerBI
-
I have a pretty difficult assignment. This is the structure that I need to have (or smth very close to it). I need to have 4 temp tables in one query. I am droping them each time I am running and each of those temp tables consists of CTEs inside.
The schema is below.
This works just fine in SQL Server Management Studio. But when I try to add it to PowerBI I am getting an error:
I really don't understand why PowerBI doesn't accept it. Does someone know what can cause the issue in PowerBI while SQL Server does accept it?
if object_id('TempDB..##t1','U') IS NOT NULL
drop table ##t1;
if object_id('TempDB..##t2','U') IS NOT NULL
drop table ##t2;
if object_id('TempDB..##t3','U') IS NOT NULL
drop table ##t3;
if object_id('TempDB..##t4','U') IS NOT NULL
drop table ##t4;
;with cte1 AS (select)
, cte2 AS (select)
, cte3 AS (select)
, cte4 AS (select ... INTO ##t1 )
;with cte1 AS (select)
, cte2 AS (select)
, cte3 AS (select)
, cte4 AS (select ... INTO ##t2 )
;with cte1 AS (select)
, cte2 AS (select)
, cte3 AS (select)
, cte4 AS (select ... INTO ##t3 )
;with cte1 AS (select)
, cte2 AS (select)
, cte3 AS (select)
, cte4 AS (select ... INTO ##t4 )
and then I create new CTEs from where I am selecting
;with cte1 ( select various fields from diff tables )
, cte2 ( select various fields from diff tables )
, cte3 ( select various fields from diff tables )
, cte4 ( select various fields from diff tables )Works in SQL, doesn't work in PowerBI.
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
Sounds rather complex. You might be better off putting this into a view or stored procedure.
Are you using global temp tables rather than local ones for a reason? Local temp tables are usually to be preferred in code like this.
Presumably you are using Direct Query?
How long does the query take to run in SSMS? Did you set the command timeout to be longer than this?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Create a stored procedure that creates and uses all the temp tables you want. Then at the end return records from your temp tables.
In PowerBI, Get Data -> From SQL Server -> Specify the Connection to the server, and then click the Down Arrow next to Advanced Options. In the box that magically appears, call your stored procedure.
EXEC MyDb.SchemaName.StoredProcName @Param1=....
Just to make sure I wasn't making anything up, I created a dummy stored procedure to return data from a temp table. (Yes, I am fully aware that this is the DUMBEST stored procedure I've probably ever written. Feels a bit tortured, like the plots in Cervantes' Exemplary Novels, but anyway... Here's a stored procedure that creates and populates a temp table, uses it in the stored procedure to join to a bunch of Common Table Expressions. Just to prove it can be done, and consumed in PowerBI.
CREATE PROC StoredProcWithTempTables
AS
BEGIN
CREATE TABLE #T(ID INT, Firstname VARCHAR(20));
INSERT INTO #T VALUES (1,'Joe'),(2,'Fred'),(3,'Bill');
SELECT *
FROM #T;
DROP TABLE #T;
ENDand that was fine. The values I inserted are returned. So it's not temp table that's the problem.
I think the whole CTE thing is a red herring. As long as you define them all in one statement, it should be fine. So I tried it... like this. (Yes, I know, it's a completely absurd query, but that's beside the point)
CREATE PROC HeartDataNoDuplicates
AS
WITH cteHeartData(PatientID, SymptomName,Severity, ConsentDate, dupeNo)
AS
(SELECT Individual_ID,
Long_name,
Delivery_level,
Consent_date,
ROW_NUMBER() OVER (PARTITION BY Individual_ID, Long_Name ORDER BY Consent_Date)
FROM dbo.SekouTestData td),
cteMultiSymptomPatients(PatientID)
AS (
SELECT Individual_ID
FROM dbo.SekouTestData td2
GROUP BY Individual_ID
HAVING COUNT(DISTINCT(Long_Name))>1
)
SELECT msp.PatientID, hd.ConsentDate, hd.SymptomName, hd.Severity
FROM cteHeartData hd
INNER JOIN cteMultiSymptomPatients msp
ON hd.PatientID = msp.PatientID;So that's the "it doesn't work with a CTE" claim dealt with. Oh, a Temp table.
CREATE PROC HeartDataNoDuplicates3
@MinSeverity TINYINT
AS
/*
create a temp table and join to it...
*/
DROP TABLE IF EXISTS #SamplePatients;
CREATE TABLE #SamplePatients (PatientID INT);
/* pick 50 random multi-symptom patients */
INSERT INTO #SamplePatients
SELECT TOP 50 Individual_ID
FROM SekouTestData std
GROUP BY Individual_ID, Long_Name
HAVING COUNT(*)>1
ORDER BY NEWID();
WITH cteHeartData(PatientID, SymptomName,Severity, ConsentDate, dupeNo)
AS
(SELECT Individual_ID,
Long_name,
Delivery_level,
Consent_date,
ROW_NUMBER() OVER (PARTITION BY Individual_ID, Long_Name ORDER BY Consent_Date)
FROM dbo.SekouTestData td)
SELECT hd.ConsentDate, hd.SymptomName, hd.Severity
FROM cteHeartData hd
INNER JOIN #SamplePatients sp
ON hd.PatientID = sp.PatientID
WHERE hd.Severity>@MinSeverity;If you're declaring multiple CTEs, just have commas between each definition, and then use them in the final SELECT, and it should work fine. If that doesn't solve your problem, post your CTEs.
-
I had similar issues using OLE DB driver in Power BI. Give it a try with SQL Server Native Client driver.
=======================================================================
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply