Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Greetings.
I have a situation where I currently use a SQL Server function to assemble the data for a report. The SQL Server code constructs a number of custom columns. These custom columns use CASE or IF statements to evaluate/compare values in other columns in the table; based on those conditions, the custom columns are assigned various values. There are also various filter conditions.
I can recreate these aspects using the data modeling features in PowerBI. However, it is definitely more time-consuming than simply using a SQL Server query, requires many steps in the query editor, & I think is more difficult for another developer to simply step in & understand.
What I am wondering is, in a situation like this, (let's say I was starting from scratch, with no prebuilt SQL Server function), is it better to simply construct a SQL Server query/function to use as a data source for PowerBI, or is it recommended instead to always use PowerBI's query editor, pull in the raw tables, & perform any data shaping & filtering there?
Thanks,
Randy
Solved! Go to Solution.
@wittr9876,
It depends on your requirement, if you are good at T-SQL, you can create SQL Server function and call the function in Power BI Desktop to shape data or filter data. And when you have large amount of data and columns in the raw tables, and you are clear about which columns and rows are useful in Power BI report, it is better to use SQL Server query/function to limit the columns and rows, which will boost performance on Power BI side. Many complex steps against large table in Power BI Desktop query editor will put pressure on memory.
However, if you are not familiar with T-SQL, you can import all data into Power BI Desktop and transform data in Query Editor as it is easy and convenient to use.
Regards,
Lydia
@wittr9876,
It depends on your requirement, if you are good at T-SQL, you can create SQL Server function and call the function in Power BI Desktop to shape data or filter data. And when you have large amount of data and columns in the raw tables, and you are clear about which columns and rows are useful in Power BI report, it is better to use SQL Server query/function to limit the columns and rows, which will boost performance on Power BI side. Many complex steps against large table in Power BI Desktop query editor will put pressure on memory.
However, if you are not familiar with T-SQL, you can import all data into Power BI Desktop and transform data in Query Editor as it is easy and convenient to use.
Regards,
Lydia
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
71 | |
66 | |
24 | |
18 | |
13 |