Forum Discussion
Create dataset from Data Mart visual or SQL queries
Hi. I don't think you would connect the query, instead you can write the query. You can get the SQL Connection string or endpoint of the datamart. Then create a new dataset with Power Bi Desktop and get data from SQL Server. Paste the servername you have copy. Open advanced setting and write your SQL query.
That way you will get the query result as table.
If you are not sure how to get the endpoint. Check this link: https://learn.microsoft.com/en-us/power-bi/transform-model/datamarts/datamarts-analyze#get-the-t-sql-connection-string
I hope that helps,
Thanks for your answer ibarrau . I'm aware you can create datasets from PBI Desktop + SQL Server endpoint, but I was looking for an easier, less convoluted solution.
My scenario is comprised of non tech end users using the datamart in their daily jobs, wanting to derive niched/filtered datasets with others. In my point of view, this should be possible for them using only datamart's interface on Power BI Service, especially with no/low code interfaces such as visual queries, without the need to leave the interface or interact with more technical assets such as SQL Server endpoints. Is there a way to do so or is it still a feature request?
Well. I'm having trouble to understand why would you do that. If you want to build DIMs and FACTs you can do it in tables in datamart, no in a query that can be called later. You can transform and build the "ready" tables you want for the users instead of letting use a prebuilt query.
If you want them to run queries they can just connect to the endpoint.
If you are not leting the user run queries and you don't want to share them a dataset that already has the connection with the queries load as tables built it with a PowerBi Desktop. You could use dataflows to build tables that are running queries to datamarts. That way you will keep all the work at Power Bi Service. Still thinking you could just create the tables in the data mart with a query, joining, etc to build a ready table.
I hope that make sense