New post
Question
· Apr 26, 2023

Issues with Publishing Power BI Reports with DirectQuery Connection to IRIS?

#Analytics #Visualization #InterSystems IRIS

Our team has had success creating and publishing Power BI reports using an ODBC connection to an IRIS database, but there have been concerns about the responsiveness of these reports.

As an attempt to improve responsiveness, I'm trying out the "DirectQuery" connection using the InterSystems IRIS connector available in our version of Power BI Desktop (September 2021). 

The version of IRIS I'm connecting with is "IRIS for Windows (x86-64) 2022.2"

Within Power BI Desktop, I can successfully get data and populate visualizations, despite this concerning warning in the "Source" step of a query (Issue 1):

The reason this is a concerning issue is I don't want to switch to "Import" mode. 

 

If I don't switch and proceed to "Close and Apply" with my "DirectQuery" queries, I can then successfully save the report to our local report server, at least according to this dialog box:

 

However, clicking the "take me there" link takes me to my browser (Edge) and the following error dialog box (Issue 2):

 

Has anyone else had these issues and, if so, what was your solution? Perhaps Issue 1 is the reason Issue 2 fails?

The Power BI Report Server logs report the following error, making me think there is something missing related to connecting to the SQL Server, where Power BI Report configuration information is stored.

2023-04-26 17:49:35.8587|ERROR|111|PBI.Win.DataExtensionException|ExceptionType:Microsoft.PowerBI.DataExtension.Contracts.DataExtensionException;DataExtensionErrorDetails:Details: Message=Failed to get CSDL., HResult=0x80004005, ErrorCode=QuerySystemError, Language=en-US, ProviderErrorCode=0xC1070000, ProviderErrorMessage=[ABB72B1B29CE76CDB201A08493ECF088D4E0907432E24C9F7F0FACD938B20956], ProviderGenericMessage=[COM error: COM error.], ErrorSource=PowerBI, ErrorSourceOrigin=MsolapWrapper, OnPremErrorCode=, InnerErrorDetails=[Type=MsolapWrapper.MsolapWrapperException, Message=Failure encountered while getting schema];stackTrace:   at Microsoft.PowerBI.DataExtension.Msolap.SchemaCommand.GetModelMetadata(String catalogName, String perspectiveName, String supportedVersion, Boolean ignoreTranslations)
   at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.<>c__DisplayClass5_0.<GetModelMetadata>b__0(IDbSchemaCommand command)
   at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.ExecuteSchemaCommand[T](ExploreHostDataSourceInfo dataSourceInfo, IDbConnectionPool connectionPool, IConnectionFactory connectionFactory, IConnectionUserImpersonator connectionUserImpersonator, QueryExecutionOptionsBase queryExecutionOptions, ITelemetryService telemetryService, Func`2 action, String failTelemetryMessage, ServiceErrorStatusCode statusCode, ExploreBaseEvent exploreEvent);message:GetModelMetadata;| RequestID = 2e3ffe31-c3fd-c1a5-f3fe-ac3aad45903c ClientSessionID = ae5e7fd0-68e6-df94-de64-1ba95805149a 
2023-04-26 17:49:35.8587|ERROR|111|PBI.Win.ReportingServicesHostTrace|type:Error;message:library:Throwing Microsoft.ReportingServices.Diagnostics.Utilities.CannotRetrieveModelException: , Microsoft.ReportingServices.Diagnostics.Utilities.CannotRetrieveModelException: An error occurred while loading the model for the item or data source '11bfef91-70d4-484a-a22d-8ff6523d8ac4_1979538606'. Verify that the connection information is correct and that you have permissions to access the data source. ---> Microsoft.PowerBI.DataExtension.Contracts.DataExtensionException: Failed to get CSDL. ---> MsolapWrapper.MsolapWrapperException: Failure encountered while getting schema
   at MsolapWrapper.Utils.ThrowErrorIfHrFailed(Int32 hr, String message)
   at MsolapWrapper.CDbSchemaRowsetWrapper.ExecuteSchemaRowset(Guid schema, Object[] restrictions, PropertySetCollection propSets)
   at MsolapWrapper.SchemaCommand.ExecuteReader(Guid schema, Object[] restrictions)
   at MsolapWrapper.SchemaCommand.GetModelMetadata(Object[] restrictions)
   at Microsoft.PowerBI.DataExtension.Msolap.SchemaCommand.GetModelMetadata(String catalogName, String perspectiveName, String supportedVersion, Boolean ignoreTranslations)
   --- End of inner exception stack trace ---
   at Microsoft.PowerBI.DataExtension.Msolap.SchemaCommand.GetModelMetadata(String catalogName, String perspectiveName, String supportedVersion, Boolean ignoreTranslations)
   at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.<>c__DisplayClass5_0.<GetModelMetadata>b__0(IDbSchemaCommand command)
   at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.ExecuteSchemaCommand[T](ExploreHostDataSourceInfo dataSourceInfo, IDbConnectionPool connectionPool, IConnectionFactory connectionFactory, IConnectionUserImpersonator connectionUserImpersonator, QueryExecutionOptionsBase queryExecutionOptions, ITelemetryService telemetryService, Func`2 action, String failTelemetryMessage, ServiceErrorStatusCode statusCode, ExploreBaseEvent exploreEvent)
   --- End of inner exception stack trace ---;;| RequestID = 2e3ffe31-c3fd-c1a5-f3fe-ac3aad45903c ClientSessionID = ae5e7fd0-68e6-df94-de64-1ba95805149a 
2023-04-26 17:49:35.8587|ERROR|111|Failure in conceptualschema catalogObjectId=11bfef91-70d4-484a-a22d-8ff6523d8ac4| RequestID = 2e3ffe31-c3fd-c1a5-f3fe-ac3aad45903c ClientSessionID = ae5e7fd0-68e6-df94-de64-1ba95805149a Microsoft.ReportingServices.Diagnostics.Utilities.CannotRetrieveModelException: An error occurred while loading the model for the item or data source '11bfef91-70d4-484a-a22d-8ff6523d8ac4_1979538606'. Verify that the connection information is correct and that you have permissions to access the data source. ---> Microsoft.PowerBI.DataExtension.Contracts.DataExtensionException: Failed to get CSDL. ---> MsolapWrapper.MsolapWrapperException: Failure encountered while getting schema

Thanks in advance,

Product version: IRIS 2022.2
$ZV: IRIS for Windows (x86-64) 2022.2 (Build 334U) Fri Sep 9 2022 00:07:09 EDT
Discussion (3)1
Log in or sign up to continue
Carmen Logue · Apr 27, 2023

Hi Jean -- I think the message about switching to Import mode is a recommendation from Microsoft because it allows them to control the performance if the data is local.  That message should not cause the next problem if you ignore it.  If you are able to see the report in PowerBI desktop but not in PowerBI report server, I wonder if it is some kind of connection issue.  This link seems to describe your scenario and a possible solution: https://spgeeks.devoworx.com/power-bi-we-couldnt-connect-to-the-analysis...

1 0
Jean Millette · Apr 27, 2023

Hi Carmen, thank you for the clarification on the "Import mode" recommendation and for the link to something to try. Unfortunately, although the report shows up in the right spot on the report server, when I try to "Manage" the "Data Sources", that link is greyed out:

 

I don't believe it's a folder/file permissions issue since I've saved other reports to this folder before.

The example in the link you sent me used Direct Query to the SQL Server for its data. Since report configuration information is also stored in SQL Server and my report has no explicitly-defined connection to SQL Server, could that be an issue?

I'm now looking at the backend SQL Server database where report information is stored for clues. I've found "rsInternalError" entries in the "....ExecutionLogStorage" table for each of the times I've tried to display the problem report from the server, but now looking for details of these errors.

I'll update this post with more details as I find them, but other ideas (how to enable the "Data sources" link?) are most welcome.

Thanks again,

Jean

0 0
Jean Millette · Apr 28, 2023

An update on this...

I had assumed that all was configured properly on the Report Server, particularly with respect to the required connections and gateways as described here: https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

I was mistaken. Also, we are running an older version of the report server (Version 1.12.7981.11681, from Sept. 2021), which does not provide the "New data source and gateway management experience" introduced in May, 2022.

We're planning an upgrade of our Power BI Report Server and backing SQL Server and I'll revisit the use of DirectQuery to IRIS with some of our reports after that upgrade.

Thanks again Carmen for the pointers and I'll update this post after getting this working as expected.

0 0