Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I've got hundreds of databases with identical tables structure and tens of queries to run against these dbs. To avoid loading list of db for each query cursor, I've created a seperate query in PBI - DBs - that keeps the list of dbs which I need to incorporate in my SQL queries. The queries are too complicated for me to transform them into DAX so I'd like to keep them in pure SQL. Please see the below example and advise how to do it.
DECLARE @db VARCHAR(128), @query VARCHAR(MAX), @sql VARCHAR(MAX) SET @query = 'SELECT DB_NAME(), TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE''' CREATE TABLE #Tables (DBNAME VARCHAR(128), TABLE_NAME VARCHAR(128)) DECLARE db_cursor CURSOR FOR --I want to replace this line with a already loaded recordset 'DBs' SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @db WHILE @@FETCH_STATUS = 0 BEGIN SET @sql =' USE '+@db+'; INSERT INTO #Tables '+@query+'' FETCH NEXT FROM db_cursor INTO @db EXEC (@sql) END CLOSE db_cursor DEALLOCATE db_cursor SELECT * FROM #Tables DROP TABLE #Tables
Regards,
Przemek
Hi All,
I've got hundreds of databases with identical tables structure and tens of queries to run against these dbs. To avoid loading list of db for each query cursor, I've created a seperate query in PBI - DBs - that keeps the list of dbs which I need to incorporate in my SQL queries. The queries are too complicated for me to transform them into DAX so I'd like to keep them in pure SQL. Please see the below example and advise how to do it.
DECLARE @db VARCHAR(128), @query VARCHAR(MAX), @sql VARCHAR(MAX) SET @query = 'SELECT DB_NAME(), TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE''' CREATE TABLE #Tables (DBNAME VARCHAR(128), TABLE_NAME VARCHAR(128)) DECLARE db_cursor CURSOR FOR --I want to replace this line with a already loaded recordset 'DBs' SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @db WHILE @@FETCH_STATUS = 0 BEGIN SET @sql =' USE '+@db+'; INSERT INTO #Tables '+@query+'' FETCH NEXT FROM db_cursor INTO @db EXEC (@sql) END CLOSE db_cursor DEALLOCATE db_cursor SELECT * FROM #Tables DROP TABLE #Tables
Regards,
Przemek
Hi @przemek_pbi
For this requirement,
"I've created a seperate query in PBI - DBs - that keeps the list of dbs which I need to incorporate in my SQL queries."
you could consider to disable"parallel loading of tables" so that power bi would not load mutiple queries in parallel.
"The queries are too complicated for me to transform them into DAX so I'd like to keep them in pure SQL. Please see the below example and advise how to do it."
From above I'm not clear which situation you meet,
did you import data from database and need filter the columns or rows which needs to be load in,
or, have you alreadly load data in, then you need to create measures or columns,a new table based on your dataset in power bi?
If it is the first scenario, also if your database is a sql database, you could write sql statement to define which data should be load in.
Reference here
if it the second one, for the bold line, it can be transformed to DAX formula as below
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')
New table
new table = SUMMARIZE(FILTER(ALL(Table1),NOT[name] IN {"master","model","msdb","tempdb"}),[name])
Or column
Column = CALCULATE(MAX([name]),FILTER(ALL(Table1),NOT[name] IN {"master","model","msdb","tempdb"}))
Best Regards
Maggie
Hi Maggie,
In my report there're tens of queries, each query needs to be run in each database (there hundreds of them). What I need here is to use the once imported list of databases (DBs) to run my queries in without the necessity to import the list from SQL in each query.
I'd imagine my code would look like but PBI doesn't allow that.
DECLARE @db VARCHAR(128), @query VARCHAR(MAX), @sql VARCHAR(MAX) SET @query = 'SELECT DB_NAME(), TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE''' CREATE TABLE #Tables (DBNAME VARCHAR(128), TABLE_NAME VARCHAR(128)) DECLARE db_cursor CURSOR FOR SELECT name FROM DBs[name] OPEN db_cursor FETCH NEXT FROM db_cursor INTO @db WHILE @@FETCH_STATUS = 0 BEGIN SET @sql =' USE '+@db+'; INSERT INTO #Tables '+@query+'' FETCH NEXT FROM db_cursor INTO @db EXEC (@sql) END CLOSE db_cursor DEALLOCATE db_cursor SELECT * FROM #Tables DROP TABLE #Tables
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |