"Data could not be retrieved from the database" error in Excel 2016

[Etienne Dube]

Hello,

I am using the latest (1.0.0.103) XMLA connect provider with Excel 2016 and Mondrian 4 (version 4.3.1.3-175). Connecting to a data cube works fine, except on the first time a query is issued, i.e. when adding the first measure (Value) to the pivot table, Excel shows an error message as follows:

"Data could not be retrieved from the database.
Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again."

After clicking OK, the query results are shown in the pivot table as if no error occurred, and further queries (e.g. adding dimensions or other measures, drilling down, etc.) work fine without any error messages popping up. The error will be shown again once only if I establish a new connection from Excel (e.g. closing and opening the worksheet), or if the Mondrian server is restarted (the error will pop up the next time the pivot table is refreshed).

The error seems to be occurring when Excel receives the response for the MDSCHEMA_CUBES Discover request it sends (just after receiving the MDX query result for the Execute request). After clicking OK on the message, another MDSCHEMA_CUBES request is sent, followed by DBSCHEMA_CATALOGS, and no more errors will show up. There does not seem to be any ill effects caused by the error other than the annoyance of having to close the pop-up. No errors or warnings are present in the Mondrian logs on the server side.

Is anyone experiencing the same issue, and is there a known solution? If not, is there a way to get some debugging information from the XMLA connect provider, e.g. an option to enable writing to a log file?

Thanks,
Etienne

[lengistonjorry]

When encountering the "Data could not be retrieved from the database" error in Excel 2016, frustration can quickly set in. This error typically occurs when Excel is unable to establish a connection with the database to retrieve the requested data. There are several potential causes for this issue, ranging from connectivity problems to issues with the database itself or the query being used.

Firstly, ensure that your network lucky me I see Ghosts Hoodie connection is stable and that you have the necessary permissions to access the database. Verify that the database server is running and that there are no firewall or security settings blocking the connection.

[kathrynmorrison2k@gmail.com]

It is common.

[kathrynmorrison2k@gmail.com]

There's an issue with the XMLA connect provider (version 1.0.0.103) in Excel 2016 when connecting to Mondrian 4 (version 4.3.1.3-175) for a Baddiehub project. Upon first querying a data cube, Excel displays an error: "Data could not be retrieved from the database." Subsequent queries function correctly until a new connection or server restart triggers the error again. The impact is minimal beyond the initial error message, prompting the need for logging options from the XMLA connect provider to facilitate troubleshooting.

[John smith]

Hello Etienne,

Thank you for detailing the issue. The behavior you described seems related to how Excel handles the initial MDSCHEMA_CUBES request during the connection setup. Although the error doesn’t affect functionality after dismissal, it’s understandably frustrating.

Here are some suggestions to address the issue:

Debugging with XMLA Connect: Check if the XMLA provider has logging options you can enable. This might provide more detailed information on what triggers the error. Refer to the provider's documentation for instructions on enabling debug or trace logs.

Excel Logging: Enable diagnostic logging in Excel. To do this:

Go to File > Options > Advanced.
Scroll down to General and check Enable troubleshooting logging.
After reproducing the error, examine the generated logs for additional insights.
Provider Version: Verify that you're using the latest XMLA connect provider. If not, updating to a newer version could resolve potential compatibility issues.

Mondrian Configuration: Double-check the Mondrian server logs at higher verbosity levels to confirm there aren’t underlying issues with the MDSCHEMA_CUBES request. It might be worthwhile to test with a different Mondrian version to rule out version-specific quirks.

Excel Cache: Clear Excel’s cache to ensure no corrupted metadata is affecting the connection. To do this:

Close Excel.
Delete files in the %LOCALAPPDATA%\Microsoft\Office\16.0\Caches folder.
Restart Excel and reconnect.
If none of these solutions work, it might be helpful to share the issue with the XMLA provider's support team or relevant community forums, as it may be a known bug requiring a patch. And also tell how much time it take to solve using this tool.

Let me know if you need further assistance!

Best regards,