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

[maksimk]

Hi,

I tried to create a variable using the data from DirectQuery data source and I'm faced with the error:

"An unexpected error occurred (file 'xldqas.cpp', line 503, function 'DAXDefManager::AddVariableOrInlineExpr')."

Measure I used (here 'Average Story Points' is data source connected using DirectQuery):

Estimates = IF('Release Scope'[Story Points] = BLANK(),

VAR AverageBugVelocity = LOOKUPVALUE('Average Story Points'[Bug Story Points Average], 'Average Story Points'[Team], 'Release Scope'[Team])

RETURN

SWITCH(

'Release Scope'[Issue Type],

"Bug", AverageBugVelocity,

0),

'Release Scope'[Story Points])

[dpatocka]

Hello team,

I'm fasing issue with Scheduled PBI data refresh in workspace with error: Single sign-on (SSO) credential is not provided which is required to execute the current request.. A connection could not be made to the data source with ..... I created new table with function SUMMARIZECOLUMNS from table which is DirectQuery from PBI dataset. Refresh in PBI Desktop works but if I put this report to the workspace then refresh failed by mentioned error. Any suggestion?

[Anonymous]

There is a thread further down on this same issue and Microsoft have stated "Creating calculated tables over a remote dataset is not supported although it is not currently blocked in the interface." 

[PhMeDie]

Hi,

I am having trouble with a composite model. I tried to create a model based on an existing "golden" dataset. I added a calculated column and published to a premium workspace. From there I wanted to analyze in Excel. It works until I involve a calculation group. Then Excel crashes. Has someone noticed this behaviour?

Best,

P

[jeroenterheerdt]

calculation groups are not supported yet.

[PhMeDie]

I thought that was only valid with regards to the creation of calculation groups. They work perfectly in the desktop version. So you say they just don't work in Excel? Is that something to be added soo?

Thanks for your help.

P

[Anonymous]

@jeroenterheerdt Thanks in advance for providing this answer ^

[Anonymous]

Unable to refresh simple Direct Query model based on the single shared dataset after the shared dataset been changed. The error message has nothing to do to the current state of Shared semantic model which has different relations to the error messages. 

It seams the Direct Query failed to refresh semantic from the source dataset. 

[gronnerup]

We have created a simple composite model containing a model from Azure Analysis Services as well as manually entered data.

Everything is working as expected in Power BI Desktop and DepartmentName from the Product table in AAS is displayed alongside Column1 from the manual table (named Table).

After publishing the report and dataset to the Power BI Service the visual fails when it includes any data from AAS. Data from the manual table can be displayed when it is the only source for a visual.

Clicking refresh on the dataset does perform a refresh. No error occur.

The reporting and dataset is published to a workspace under the new workspace experience and is under a premium capacity with read/write for the XMLA endpoint enabled.

The connection to AAS is OAuth2/Organizational and is in the context of a guest user on the tenant hosting the AAS service. This guest user is service admin on AAS.

[Anonymous]

Hi @gronnerup - try publishing the dataset to a shared capacity workspace as a test. This might be a limitation of the AAS Client in Power BI Service. @jeroenterheerdt any ideas ?

[jeroenterheerdt]

I think the issue here is the "guest user" - there are limitations with working across tenants: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-s...

[karlk]

Hi,

We have an Azure Analysis Services data model.  This model has several perspectives to make it simpler to use for reporting (the model itself is quite big). 

I tried this functionality out on a report that connects to a Power BI Dataset where the dataset itself live connects to the AAS model and shows tables etc from a single perspective (with the rest of the model hidden - although you can make it visible).

When the report switched to the DirectQuery connection the entire AAS model was visiable and any use of the perspective seemed to be lost.

Is there any information available how this functionality is expected to behave when using AAS perspectives, please?

Thanks

Karl

[jeroenterheerdt]

perspectives are you yet supported, but this is coming soon.

[karlk]

Thanks for the update.

[Anonymous]

Some functions are not working.

Example, when I use "RELATED()" in a one-to-one relationship I'm getting the following error:

Relationship between tables:

The column was added on TF_Delivery_Header

Also, the calculate function and countrows are not working when creating columns:

The sorting of the fields on the main dataset is also gone:

My PBI version: 2.88.1385.0 64-bit (December 2020)

[jeroenterheerdt]

is this a cross-source group relationship? If so, it is limited, and as such RELATED does not work. See RELATED function (DAX) - DAX | Microsoft Docs

[Anonymous]

So far, I have really enjoyed this feature, connecting two PBI datasets and creating simple visuals, measures and calculated columns just seems to work, though perhaps a bit of a memory hog. However, now I have a scenario whereby I want to append one table from one source onto a table from another source. Curious whether this can be done with Composite Models, I tried creating a calculated table but some of the DAX table functions are throwing "unexpected exceptions" is this a Direct Query limitation? I couldn't seem to find the answer... 

*Edit* Nevermind. Found the answer. It's not supported.  🙂

[jeroenterheerdt]

correct - it is not supported. yet.

[tameezmoosa]

Hi

I have tried this and it does not work as it says i have a credential issue when refreshing on service

[Anonymous]

I have the same issue refreshing the composite model whereby the Live dataset gives this error even though the gateway allows cloud refresh and the non-live data sources are configured. How can we get past this "Processing error: COM error: Microsoft.ASWL.Service, Single sign-on (SSO) credential is not provided which is required to execute the current request.. A connection could not be made to the data source with the Name of '{"protocol":"analysis-services","address":{"server":"powerbi://api.powerbi.com/v1.0/myorg/[workspace]","database":"[model]"},"authentication":null,"query":null}" @jeroenterheerdt - please help as the composite model is unusable if it cannot be scheduled for refresh...

[jeroenterheerdt]

did you go in and set the credentials?