cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tessahurr
Microsoft
Microsoft

Share your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)

Hit Reply and let us know what you think of the DirectQuery for Power BI datasets and Azure Analysis Services.  To learn more about this feature, please visit this blog post or our documentation.

 

Here are some areas that we'd like to hear about in particular:

  • Performance
  • Query editor experience--the remote model query doesn't show up in the query editor and only in the data source settings dialog. What are your thoughts?
  • Navigator experience
  • Thoughts around governance and permissions for models that leverage this feature
  • Nesting models, i.e. building a composite model on top of a composite model
  • Automatic page refresh for live connect in composite models

Thanks and we look forward to hearing your feedback!

 

- The Power BI Modeling Team

344 REPLIES 344

did you go in and set the credentials?

Yes I had set the credentials. I have since recreated the composite model step by step and have found that the SSO error occurs after adding a calculated table into the model e.g. Account = DISTINCT(UNION( VALUES('Table1'[AccountCode]), VALUES('Table2'[Account]) ))

Is this an expected known limitation of composite models?

from the docs/blog:"

  • Some queries may return wrong results when there is a relationship between calculated tables and table(s) in a remote source. Creating calculated tables over a remote dataset is not supported although it is not currently blocked in the interface."

Thanks Jeroen, I guess this must be the cause of the error - although the message referring to SSO etc is not very helpful 😉

yeah, I know 🙂 still working on that.

Lumalytiq_TonyD
Frequent Visitor

Question on the current RLS functionality in the preview, running into some issues when testing. We have an ex. 'Parent Dataset Test' with 'Parent RLS Test' RLS role currently applied. Published to the service, applied the role, added the needed AD Group to the role, everything works like normal off any visualizations connected to 'Parent Dataset Test'. We then have 'Remote Dataset1' connected to 'Parent Dataset Test' added in 'All Measures' table and converted the connection to DirectQuery to create the local model. Published 'Remote Dataset1' to the service and no data is visible. "Couldn't load the data for this visual. The following system error occurred: (this is blank). Is the RLS rule from the parent dataset currently supposed to flow through to the remote dataset or is it unavailable for now as a preview limitation? Or is there something that needs to be done in the service to push the RLS roles?

teosantoro
Regular Visitor

Hi,

I mixed a PBI Dataset and an Excel table.

In PBI Desktop all worked correctly, but after i published the pbix to services i received a "There is no available gateway" error when I opened the report.

The orginal PBI Dataset works correctly. The orginal PBI Dataset is in the same workspace of the mixed Excel dataset.
When Excel is imported no gateway is needed, anyways I tried also to install a gateway for the Excel but Excel doesn't find it and the error remain.

 

Capture.PNG

Anonymous
Not applicable

Im having a similar issue

deepti1309
Regular Visitor

We are having issues with this feature as well and get ODBC and OLEDB errors

ab14
Advocate I
Advocate I

Hi,

 

I am also running into the same issue as some other users have pointed out "An error occurred while loading the model. Verify that the connection information is correct and that you have permissions to access the data source." 

 

I'm Live connected to a PBI dataset that is being fed data from an on-prem SQL server and I'm trying to add a connection to another such dataset. Both datasets are in the same Premium Capacity workspace and are pulling data from the same on-prem SQL server.

ab14_0-1611296196115.png

 



daamruth
Frequent Visitor

Hi @ab14 , @deepti1309 , 

 

we had the same issue. Can you try without any proxy and firewall? It seems that some URLs and ports are still blocked in our FW / Proxy. It works when we deactivate. 

 

According to MS these URLs and ports need to ne opened: https://docs.microsoft.com/en-us/power-bi/admin/power-bi-whitelist-urls

 

Daniel 

dd-david
Frequent Visitor

Hi guys, 

 

this new feature sounds really good and hopefully solves our problem to work with push datasets. I can't see any resctrions regarding those push datasets. So i tried the following steps to work with my dataset: 

1) Created a workspace

2) Created a new Dataset via API: 

    URL = f'https://api.powerbi.com/v1.0/myorg/groups/{tenant_id}/datasets?defaultRetentionPolicy=basicFIFO'
{
    "name": "Test",
    "defaultMode": "Push",
    "tables": [
        {
            "name": "Table1",
            "columns": [
            {
                "name": "ID",
                "dataType": "Int64"
            },
            {
                "name": "Randomstuff",
                "dataType": "string"
            }
            ]
        }
    ]
}

 3) Pushed some Rows to new Dataset

    URL = f'https://api.powerbi.com/v1.0/myorg/groups/{tenant_id}/datasets/{dataset_id}/tables/Table1/rows'
{
  "rows": [
    {
      "ID": 1,
      "Randomstuff": "Adjustable Race"
    },
    {
      "ID": 2,
      "Randomstuff": "LL Crankarm"
    },
    {
      "ID": 3,
      "Randomstuff": "HL Mountain Frame - Silver"
    }
  ]
}

4) Power BI Desktop: Get Data -> Power BI Datasets -> Test

5) Transform data with Power Query Editor (Power BI forced to change the dataset connection from live connection to Query direct)

6) Now my big issue: Power Query Editor doesnt show my dataset / tables. 

 

Is there any special to consider while use this feature with push datasets?

 

Thank you in advance for your help

No ideas? 😞

Hi @dd-david and apologies for the delay here. Wanted to make sure I repro this case and compared it to "regular" PBI datasets. In a nutshell, the PUSH datasets behaves like any other Power BI dataset in this case and transformation through Power Query is not supported since you are connectin to them using DirectQuery. That's it.

 

Having said that and if you don't midn me asking what type of transformation are you looking for? If it's something straighforward most likely you can do that at the model level using DAX. An example that worked in my case with PUSH: a had a very unique date format not being picked up and I added a custom column using DAX extracting the characters that I needed and done. The effect is the same and you keep the DQ connection. Let me know if that makes sense and what type of transformation you're looking at.

Data for Everyone! We mean it
InsightBob
Frequent Visitor

I'm wondering if anyone can help me understand an error message?

I've been tinkering wth the composite models functionality,  when i connect to one of my datasets (with direct query), all of the measures within that model do not work and are flagged with the following error message:

"Cannot find data source 'DirectQuery to AS - [DATASET NAME]' in function 'EXTERNALMEASURE'

So far i have:

- Been able to connect to other datasets without issue
- Experience the same issue when connecting to this particular dataset, and this dataset alone using DQ
- Experience the same issue if i publish the dataset to another workspace
- In all scenarios i am able to recreate the measure, by writing it again, without issue and with expected results

DesktopVersion: 2.88.1144.0 64-bit (December 2020)

It feels like an issue with the dataset, i don't understand the error message well enogh to know what the problem is, particularly the EXTERNALMEASURE bit

 

thanks

ok, so can you give a bit more detail about the dataset in question? What source is it? Can you open the same dataset in Live connect mode without any problem? Can you share an example measure that is not working?

marc_frei
Frequent Visitor

do I understand it right, when I connect to another model which has an RLS implemented which loads the security table e.g. via a dataflow that in this case we can't use the new feature? (Once published in Power BI Service)

that depends. If you need to flow RLS rules between datasets, that will not happen at the moment.

jane01
Frequent Visitor

Hi @jeroenterheerdt 

I posted this above but haven't had a response so not sure if it's been seen - 

 

I'm getting the same errors as people above (An error occurred while loading the model. Verify that the connection information is correct and that you have permissions to access the data source.) and assume it shouldn't be a deployment issue now - has there been any updates on this error? My colleague gets the same, we both tried connecting to datasets in premium workspaces

please open a support ticket so we can look at this in detail. thanks.

Thanks @jeroenterheerdt ! Ticket raised: 121011326000013

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors