New Enterprise DNA Initiatives

DirectQuery question

I’m working with a client who has a report set up with directquery (the report was built by another developer). He wants me to make some adjustments to the model and report, but I can’t view the actual data. I’ve read some articles about directquery but I’m still fuzzy on how it works from a developer point of view.

My question is on the process of building reports when using directquery. Since I can’t view the queries themselves (the actual columns and rows of the tables), how can I build measures or understand what’s going on below the hood? Right now all I can see are the visuals in the report and the names of the tables in the datamodel view, but I can’t view the actual dataset in the data view.

What’s the normal order of operations for using directquery as a developer? Do I need to import the tables, work with the data to build the model, measures and visualizations, and once this is all done, then transfer back to directquery?

Or is there a way for me to work with the dataset while directquery is set up? My main question is whether I need the client to allow me to import the tables to work on the reports, and once I’m finished, transfer everything back to directquery.

Thanks!

Hi @pete.langlois , we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

Hello @pete.langlois, due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread. When starting a new post, I suggest linking this thread, provide as much context to a question as possible and include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details. Thanks!

Hi @pete.langlois
This may assist a bit:

  • During the initial Get Data experience, the source is selected. For relational sources, a set of tables are selected and each still define a query that logically returns a set of data. For multidimensional sources, like SAP BW, only the source is selected.
  • However, upon load, no data is imported into the Power BI store. Instead, upon building a visual within Power BI Desktop, queries are sent to the underlying data source to retrieve the necessary data. The time taken to refresh the visual depends on the performance of the underlying data source.
  • Any changes to the underlying data aren’t immediately reflected in any existing visuals. It’s still necessary to refresh. The necessary queries are resent for each visual, and the visual is updated as necessary.
  • Upon publishing the report to the Power BI service, it will again result in a dataset in the Power BI service, the same as for import. However, no data is included with that dataset.
  • When opening an existing report in the Power BI service, or authoring a new one, the underlying data source is again queried to retrieve the necessary data. Depending upon the location of the original data source, it might be necessary to configure an on-premises data gateway, as is needed for import mode if the data is refreshed.
  • Visuals, or entire report pages, can be pinned as Dashboard tiles. To ensure that opening a dashboard is fast, the tiles are automatically refreshed on a schedule, for example, every hour.

Source:

Also see:

Take care, Leon

1 Like