Datamart - SQL query Editor

[KevinMorneault]

Hi

I am new to Datamart, and i was  wandering if i create a sql query within the datamart, am i able to use this query to create a report or i still need to create within the SQL enviroment.

Basically, i brought in a table, writing a sql query  and would like to use the query to create the report.

[nabullock]

Did anyone evere answer this?
I have loaded tables into a datamart, then wrote a SQL query on those tables.  It runs and I can see results, but the query does not show up in the report writer, only the tables do. 

[CharleyKirton]

Yeah, same @nabullock .  Maybe it's only like this while it's in preview and they bring the access to the SQL queries into the report layer, otherwise it seems quite pointless - certainly for the way we work in my office.

[nabullock]

I ended up using the SQL quries as data sources within the Power BI dataset 

[CharleyKirton]

@KevinMorneault - did you ever find out the answer?  I came looking for that exact thing.  I don't see the point in writing the SQL to make a new query if you can't access that query result from the PBI report.  🤔

[daXtreme]

"Basically, i brought in a table, writing a sql query  and would like to use the query to create the report."

OK, so what's the problem? If you've brought in a table into PBI Desktop or Online, then what keeps you from using it to create reports?

[KevinMorneault]

Hi what i mean is i have the table in powerbi, if i right a query for that table in sql, the new query i have created. how can i use it for the report. Normally i would do the query in sql management then bring the query in PowerBI to create the report. with the datamart now lettting us write query in sql. i was just wandering if i can use that query against a report. 

[daXtreme]

I've never heard about anyone being able to run a SQL query against a report created in Power BI... SQL is for querying databases. DAX is for querying semantic models. Two very different things.

[KevinMorneault]

HI thanks for your reply. 

Here's an example of what i am trying to do. 

Actual Scenario

SQL Management -  Table - Inventory

As an example, i have a table in MS SQl - i would write a sql query to clean my data and only bring or have want i need to create a report. Once Completed. Within Power BI i would connect to SQL and input my sql query i have created. Then create the dashboard.

Scenario 2 

Datamart - SQL Editor

Here i connected to a ODATA connection. i have imported some table, once imported, using the slq editor, i would create a query the same way i did via the SQL Management. Once i am done with the query, am i able to use this query to create a dashboard. 

[KevinMorneault]

Sorry forgot to ask. 

If i can't do this, what is the need or purpose of having a sql editor in Datamart. 

Also if not mistaken, with the ODATA file i would have imported the data in the datamart, i would be able to connect to a the SQL management  by copying the code provided from the datamart and then i would suppose i would be able to write a sql query, then use that query for the dashboard in power bi.  Does this make sense.

[daXtreme]

To be honest... I've never used a datamart you're talking about 🙂 I've always imported data into PBI from SQL Server's tables, views or stored procedures. Ocassionally I have written SQL queries that were the source for PBI. Never had any problems whatsoever. Due to my ignorance in the area of Datamarts it might be that I'm completely missing the point... Sorry.

[KevinMorneault]

Hi 

No worries, i am new to datamart as well, just  notice few days ago it was in preview. wanted to take a peek at it while nagivating, i notice the sql query editor. I wrote a simple query and it provided the output via excel which is good. But i was wandering if i was able to write the query and having the output, am i able to use this query for a dashboard.  Obviously if this would work it would be nice. would be able to do the work all within the same enviroment.

Mainly why i came here to find out if anyone would know more about it.

[KevinMorneault]

Here's a screenshot, i have hidded the sensitive information