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

[tessahurr]

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

[jeroenterheerdt]

did you go in and set the credentials?

[Anonymous]

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?

[jeroenterheerdt]

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."

[Anonymous]

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

[jeroenterheerdt]

yeah, I know 🙂 still working on that.

[Lumalytiq_TonyD]

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]

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.

[Anonymous]

@teosantoro try publishing the excel to Sharepoint / Onedrive and access it from Excel Desktop, click File - Info - Copy Path and use the URL in a new Web connector query (removing ?web=1 from the URL).

You won't need a gateway if you use this pattern.

[Anonymous]

Im having a similar issue

[deepti1309]

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

[Anonymous]

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.

[daamruth]

Hi @Anonymous , @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]

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

[dd-david]

No ideas? 😞

[MiguelMartinez]

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.

[InsightBob]

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

[jeroenterheerdt]

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]

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)

[jeroenterheerdt]

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

[jane01]

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

[jeroenterheerdt]

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