Error Communicating with Analysis Services - Failed to create the MSOLAP command.

Anonymous

Not applicable

1 year ago

Hi,ucmaoTWG .Thank you for your reply.
In fact, I don't think you need to set up a gateway connection for your paged reports anymore (at this point, both the data source and the paged reports are in the Power BI Service in the cloud, and there is no need for a gateway to act as a bridge, the system will automatically retrieve and update the paged reports)
The gateway is only needed if the data source and the report are not in the same location (whether it's a local enterprise gateway or a cloud gateway)
After my testing, I used pbix reports in Power BI Service (AS model in the cloud) as the data source after creating paged reports. The final paging report published to Power BI Service does not require a gateway connection (the system doesn't even give a button to set it up)
As you can see I can't change the data source credentials for this paging report and configure the gateway link. (The data source is semantic model i.e. AS model in the cloud)

For other paging reports made with local data sources you need to configure a gateway to complete the refresh. (The data source is a local SQL Server database.)

Below is my test:

Publish pbix to Power BI Service and use the semantic model (In Power BI Service)
create the rdl file in Power BI Report Builder.

Can not edit the RDL report.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable
1 year ago
Hi,ucmaoTWG .Thank you for your reply.

It has come to my attention that you may have some misconceptions. I hope my suggestions below will help you.
1. Whether a gateway is required to create a paged report using semantic model on Power BI Service as a data source.
As you can see from my previous test in Report Builder, I did not set up any gateway connection.
The need for a gateway connection depends on where the report and the data source are located. If both are in the cloud (both stored in Power BI Service), there is no need to configure a gateway connection.
I don't think you need to create a gateway connection for the paged reports you produce.
2. About the connection string issue
If you are using other report's semantic model as data source in Power BI Service and want to see the connection string of that semantic model.

Semantic model> settings> Server settings

And you are using a cloud data source and creating the same cloud gateway connection. Windows authentication is not supported when the data source is in the cloud. You have to authenticate via OAuth 2.0.
The root cause of this is that the semantic model you are using is stored in Power BI Service and you can only access this semantic model by logging in through your Power BI Service account and not through your windows account (this is by design).

Connecting using a connection string is essentially the same as connecting graphically.
I recommend that you connect semantic models graphically rather than comfortable strings yourself to avoid data errors. (In Power BI Report Builder)

3. About Power BI semantic model (AS model) as a data source , service support for data source authentication.
Currently, the only authentication methods for semantic model are OAuth 2.0 and Basic.
I have reproduced your operation (making RDL report in service)

create rdl report

I think where you are really having problems is with the connected semantic model.
As you can see I didn't set up any gateways during the whole test.
If you are having problems with your data source credentials. Then it is likely that you are having problems connecting to the data source gateway of the semantic model itself when you are connecting to the semantic model.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- ucmaoTWG
  Helper I
  1 year ago
  Thank you Carson, We have tested a few things based off of what you provided and have ruled out a few issues.
  
  Based on what you said, we have set up a new connection string, based off of OAuth2.0 credentials and verified that it can connect, pull, and publish semantic models that can be connected to by users through powerbi.
  
  The semantic model connects to an Azure SQL Server with Azure Active Directory - Universal with MFA authentication utilizing our EntraID.
  
  When testing reports with this connection string, we return results within PowerBI Service, and powerbi desktop, so the OAuth connection to both is correct.
  
  When we tested the creation of a paginated report, we're still getting the MSOLAP error even with the federated OAUTH2.0 connection through the connection string, and semantic model.
  
  my question is now- Is it possible that the analysis engine behind paginated reports is reading my on-premises active directory account credentials instead of the EntraID credentials?
  
  You also mentioned this earlier-
  The root cause of this is that the semantic model you are using is stored in Power BI Service and you can only access this semantic model by logging in through your Power BI Service account and not through your windows account (this is by design).
  
  We have never utilized service accounts, only regular login accounts. I have also tested trying to connect to the semantic model through powerbi desktop and i'm getting an error as well in that instance- mimicing the steps you took above
  
  I assume these two issues are both connected- do you have any insight into how the connection to a semantic model works for powerbi desktop? Could it have anything to do with my local AD connections not being linked to my Entra ID?
  - Anonymous
    Not applicable
    1 year ago
    Hi,ucmaoTWG .Thank you for your reply.
    Your understanding is correct, for paging reports, I think the system prefers to look for local geographic accounts as data source credentials.
    According to the documentation for data sources supported by Power BI Pagination Reports, the system also requires users to configure the local Active Directory (AD) to integrate and map with Microsoft Entra ID (formerly known as Azure Active Directory) when using an Azure SQL database as a data source
    URL:
    Configure Microsoft Entra authentication - Azure SQL Database & SQL Managed Instance & Azure Synapse Analytics | Microsoft Learn
    Supported data sources for Power BI paginated reports - Power BI | Microsoft Learn
    
    For the service account I mentioned, it is in fact the account you use to log into Power BI Service (typically a Microsoft 365 account)
    I suggest you read the above document carefully, I think it may help solve your problem. Pagination reports are generally more restrictive than PBIX reports
    
    Going back to your error screenshot, I think this error may be caused by the fact that the semantic model you are connecting to has actually been modified in Power BI Service (maybe the storage location has been changed, or maybe the name information has been changed), so that Desktop can't find the corresponding model.
    I recommend that you re-edit the pbix report corresponding to the semantic model you want to connect to in desktop, republish it to Power BI Service (set up the data source credentials and gateway connection), and then connect to the semantic model of the pbix report through Power BI Desktop. model through Power BI Desktop and check if the problem can be solved.
    And maybe you can change the connection.
    
    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    
    Best Regards,
    
    Carson Jian,
    
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.