Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have been using Toad Data Point to query a large Vertica database with a query given to me by a peer. The query is a rather frightful mixture of joins and window functions that mere mortals like me daren't mess with. I would like to simply copy/paste this query into PowerBI Desktop in Direct Query mode. However, I haven't found a way to do this. I can access the underlying tables in Power BI, but any attempts to create this (essentially) view over the underlying tables has stumped me.
My query starts with this:
SELECT mytablename.*, CASE WHEN tbl1.column1 IS NULL THEN tbl2.col3 ELSE tbl1.col4 END AS mynewcolumnname, CASE WHEN tbl2.column2 IS NULL THEN tbl2.col3 ELSE tbl1.col5 END AS mysecondcolumnname , ROW_NUMBER( ) OVER ( PARTITION by sometable.somecolumn, sometable.anothercolumn order by sometable.examplecolumn, sometable.examplecolumn2, sometable.examplecolumn3 desc) yetanothername FROM (SELECT B.baz,
et cetera
This query goes on for 200+ lines. I have no authority to change the underlying schema; I'm stuck with this.
Is there a feature that would allow me to simply paste this SQL query into Power BI desktop and have it show up as a table (or view or whatever) from which I can create visualizations?
Please was this solved apart from saving results in a table or views?
thank you
When it comes to very complex queries, I prefer to run them individually in SQL Server and insert the results into a table. Then I get POwerBI to query that table.
EVE, If I did that, I would have to run the server-side query each time I wanted to use the dashboard, wouldn't I?
That's not very user-friendly, is it? Telling my users, "hey, before you go to powerbi.com for your reports, first log on to this other system and run this query," is a non-starter.
@Anonymous wrote:EVE, If I did that, I would have to run the server-side query each time I wanted to use the dashboard, wouldn't I?
That's not very user-friendly, is it? Telling my users, "hey, before you go to powerbi.com for your reports, first log on to this other system and run this query," is a non-starter.
Can you schedule automatic refresh?
Scheduling automatic refresh still doesn't solve the problem of running the server-side query. It's also not compatible with DirectQuery mode, if I understand Power BI correctly.
@Anonymous how you are connecting to your Vertica data source
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous got it, you are using native connector, why not you create a view in your vertica database and use that to feed to power bi
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Yeah, I'm exploring adding this query as a view in the database, but since I'm not the database admin/owner, I'm dependent on others for that. Hopefully it'll pan out, but it might not.
Is there really no way to write a SQL query within Power BI? This seems like a rather fundamental request.
@Anonymous you can give your own query to pull data
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Parry, that query textbox might be there for a SQL Server data source, but I don't see it with a Vertica data source.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |