Random Procrastination

Darren Gosbell [MVP]

Category: Uncategorized

Fixing incorrect date formats in Power BI Report Server when using Chrome or Firefox

Reposted from 1 Mar 2019 from my old blog since geekswithblogs.net is down again at the moment: http://geekswithblogs.net/darrengosbell/archive/2019/03/01/fixing-incorrect-date-formats-in-power-bi-report-server-when.aspx

We are using Power BI Report server to publish Power BI Reports internally. And being in Australia we use a “sensible” date format of dd/mm/yyyy Smile 

However at a recent showcase with some of our end users they commented that our date format was “backwards” ie. mm/dd/yyyy

If you are using US regional settings on your PC you probably have not come across this issue, but for other regions this can be a problem.

Upon getting back to my desk I checked the report in Power BI Desktop and the date format was correctly picking up my local regional settings. I then opened the report in IE (which is what most of our End Users have as a default browser) and the report was still using the Australian format (dd/mm/yyyy):

DateSlicer-IE

However when opening the same report in Chrome or Firefox the date was using US regional settings (mm/dd/yyyy):

DateSlicer-Chrome

After doing some google/binging I found that both Chrome and Firefox have their own internal regional settings dialog and they both default to using US settings.

If you go into the Settings menu in Chrome and search for “Language” you will find the following, and it’s not just the default display language that matters the order is also important. Moving “English (Australia)” to the top of the list, then deleting cached images and files fixed this issue.

RegionalSettings-Firefox

Clicking on the 3 dots to the right of the language gives you an option to move that language to the top.

Firefox has a very similar language dialog and the same fix works for it too.

What this does behind the scenes is to change the “Accept-Language” headers that the browser sends to PBIRS to make your preferred language first in the list. This in turn alters the regional formatting setting javascript file that is sent down to the browser. Before this change I could see a file called angular-locale_en-us.js being used, after the change I now see angular-locale_en-au.js

DAX Studio 2.15 Released

This release includes a lot of small fixes, but hopefully I think I may have finally tracked down and fixed what was causing the code completion to randomly stop working.

You can get it from https://daxstudio.org

New Features

  • #559 Added a toolbar button and right-click menu to copy Server Timing metrics
  • #556 Added an option to play a sound after long running operations (this can be enabled in the Options menu)
  • Added “Duplicate Measure” right-click menu to Query Builder
  • Added Error events to All Queries trace, this can be helpful if you have another client tool that is generating invalid DAX or MDX
  • #568 Added right-click menu option in the editor to go to dax.guide for functions
  • #569 Added Function tooltip display when hovering the mouse over functions in the editor
  • Added setup cmdline parameter docs
  • Added SQLBI videos to docs
  • Added discussions link to website navigation
  • Updated Multiple Query Detection to work with 
    // Dax Query in addition to // SQL Query comments

Fixes

  • Adding extra checks around QueryEnd timeout in Server Timings
  • Close the Trace Layout ribbon tab when all documents are closed
  • Made ‘Add New’ disabled in the Query Builder if the current model has no tables
  • Fixed #458 added docs and tooltip to explain memory sizes
  • Fixed #517 updated clipboard bindings to allow for copying from the View Metrics tabs
  • Fixed #539 error starting traces when using Roles or EffectiveUserName
  • Fixed bug in retry for ViewMetrics when there is an error reading statistical information.
  • Added configuration to read statistical data for VertiPaq Analyzer. Disable reading statistical data for legacy versions of SSAS. Added retry logic and log of warning if there is an error reading statistical data.
  • Fixed #525 F1 and Context Menu to open dax.guide for functions
  • Fixed server timings trace for PowerPivot
  • Fixed drag drop issues with QueryBuilder
  • Fixed #514 Server Trace not working in Excel
  • Fixed #520 option to not truncate tables was not working when exporting to SQL Server
  • Fixed #511 Excessive metadata refreshes was causing metadata pane to collapse
  • Fixed #512 fixed Table tooltip warnings and added measure count
  • Fixed #546 corrected wording in error message
  • Fixed #549 correctly escape connection string values containing special characters
  • Fixed #557 delimiter error on Clear Cache when using non-US delimiter style
  • Fixed a bug with the Roles parameter in the advanced connection properties not accepting spaces
  • Fixed #563 metadata incorrectly resetting when using a combination of mouse selection and query builder hotkeys
  • Fixed #564 Application crashes when clicking on a ribbon tab while the ribbon is collapsed
  • Fixed crash when resetting key bindings
  • Fixed #575 autocomplete sometimes stops working
  • Disabled the Add Measure button when connected to a model with no tables
  • Fixed #576 Editing an existing Measure in Query Builder against PowerPivot causes a crash

Fixing “Attempted to read or write protected memory” Refresh Errors in Power BI Report Server

Warning: I am just sharing what worked for me in our environment in the hopes that it may help others who might hit this issue. But you should make sure to test changes like this yourself and that you have a good backups and a rollback strategy before changing your own production environments. Any changes you make to your servers are done so at your own risk.

After doing a fairly extensive round of testing we recently updated our production environment for our Power BI Report Servers to the Jan 2021 release. However unfortunately during the verification testing after the upgrade I found an issue on the production servers that had not occurred on any of our other environments.

The issue was that doing a scheduled refresh of a pbix file linked to an access file was failing with an error that “We’re sorry, an error occurred during evaluation.; Attempted to read or write protected memory” and searching for this error message only brought back hits from 2-3 years ago which were not very helpful.

The error reporting in the Report Server UI is often not very comprehensive, but even checking the RSPowerBI* logs in the Report Server log folder did not help all that much (I’ve included the relevant lines from an example of this error at the end of this post)

Initially I tried running a repair operation by re-running the Power BI Report Server installer, I could see that one of the things it checked was the Access 2016 Engine, but unfortunately even after doing a reboot the issue persisted.

Upon checking in “Add or Remove Programs” I found that the servers had both the Access 2010 and Access 2016 Engine runtimes installed. I thought this was a little odd since the 2016 version should supersede the 2010 version and these servers only run Power BI Report Server so it was not like there was other software that my have had specific requirements for the 2010 engine. So I did wonder if something had gone wrong with the registration of these 2 versions and somehow the server was expecting to run using the 2016 version, but was actually running using the 2010 version.

However upon checking our non-prod environments I found that they all had both versions installed. But by watching the installer I could see that the Jan 2021 release was definitely only installing the Access 2016 runtime. So as a test on one of our non-production environments I tried uninstalling both runtimes then only re-installing the Access 2016 Engine runtime (which I found by searching for “access 2016 engine download“).

I did not get prompted to reboot after doing the re-install of the Access 2016 engine so I simply did a stop and start of the Power BI Report Server using the Report Server Configuration Manager. After doing this refreshing one of my test reports using an Access file data source still worked. So since production was already in a broken state as far as Access refreshes were concerned I tried this same process on the production environment and this fixed our issue so we now have a fully operation install of the Jan 2021 release of Power BI Report Server.

For reference the full error message from the RSPowerBI log files is below:

2021-02-24 18:49:46.7357|ERROR|19|Error Processing Data Model Refresh: SessionId: b3c84c62-2cf0-4df0-b22c-fde90857c4b8, Status: Error Failed to refresh the model, Exception Microsoft.AnalysisServices.OperationException: Failed to save modifications to the server. Error returned: 'We're sorry, an error occurred during evaluation.;Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.. The exception was raised by the IDbCommand interface.
'.
at Microsoft.AnalysisServices.Tabular.Model.SaveChangesImpl(SaveOptions options)
at Microsoft.PowerBI.ReportServer.AsServer.TOMWrapper.RefreshModel(Database database)
at Microsoft.PowerBI.ReportServer.AsServer.AnalysisServicesServer.RefreshDatabase(String databaseName, IEnumerable1 dataSources, IEnumerable1 parameters, String clientSessionId)
at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.AnalysisServicesDataRefresh.RefreshDatabase(AsDatabaseInfo asDatabaseInfo)
at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<>c__DisplayClass34_0.b__0()
at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.d__33`1.MoveNext()

DAX Studio 2.14 Release

Updates

The following are some of the new features and improvements that you will find in v2.14 of DAX Studio

Blank Canvas Help Text

Help text has been added when the edit control is empty. This is designed to help people that are new to writing DAX queries. There are links to an article with information on writing DAX queries as well as a link to display the query builder. This help text will automatically fade away as soon as you start typing and once you no longer need it you can turn it off.

New Privacy Settings

There are new privacy settings now available in the options. We don’t recommend enabling these for general use, but if you are working with data of a highly sensitive nature and you need to ensure that DAX Studio does not send any information externally you can enable these options.

Note: DAX Studio never sends any data from your model externally.

However if the text of your queries contain sensitive information such as customer names or account numbers and you choose to use the Format DAX option that query text gets sent to daxformatter.com. And if you happen to suffer a fatal crash the crash reporter includes the option to send a screenshot and if you don’t untick the option to include the screenshot there maybe some data visible or some information in the query text.

So you as the user have complete control over any information that is sent externally. But if you work in a highly secure environment and you need to ensure that no accidental information leakage can occur then you can enable some or all of these options.

Enabled Discussions on the Github site

Discussions are a new feature on Github and this now gives us a place where people can ask questions or make suggestions without having to log a formal issue.

Multiple Queries Detected dialog

The “Multiple Queries Detected” dialog will appear when pasting in queries from Power BI Performance Analyzer that came from a Composite model. Such queries will sometimes have 2 queries where the second query is prefixed with a // SQL Query comment. The query after this comment will sometimes be SQL however if your model is a composite model over AzureAS or Power BI it might be in DAX.

New Server Timings Options

There are new options for Server Timings that help make the xmSQL text easier to understand. These new processing options are enabled by default and strip out internal IDs, Aliases and Lineage guids and highlight Formula Engine callback operations to make these events easier to understand

Other Miscellaneous Updates

  • Help Ribbon updates
    • Updating link in help ribbon to Analysis Services forum
    • Adding link to Discussions https://github.com/daxstudio/daxstudio/discussions
  • Query Builder moved out of preview status
  • Benchmarking moved out of preview status
  • Adding Query Builder documentation to daxstudio.org
  • Updating AdomdClient and TOM references to v19.14
  • Added retry logic when fetching Database and Model lists
  • #450 Database Last Updated Datetime is now shown in local time instead of UTC (and the timezone information is displayed)
  • Added keyboard short cuts in metadata pane to work with Query Builder
  • #492 added description to function tooltip

Fixes

  • Fixed crash when doing Replace All with an invalid RegEx expression
  • Fixed crash when clicking Refresh Metadata after connection has dropped
  • Fixed #469 Server Timings busy spinner was activating incorrectly when doing Clear Cache
  • Fixed #467 Database Name was being incorrectly injected into all new connections when DAX Studio was opened from External Tools in Power BI Desktop
  • Fixed #466 PBI XMLA connections not being parsed correctly
  • Fixed #457 crashing on startup after setting view hidden objects = false in options
  • Fixed #356 Clear Cache throwing errors against PBI XMLA endpoint
  • Fixed #449 Connection lost during Benchmarking
  • Fixed a trace timeout issue when connecting to PBI XMLA endpoint (sometimes it is still necessary to increase the default timeout to 90 seconds or more)
  • Query Builder
    • Fixed a bug when filtering dates that reported an error parsing an empty string
    • Added the ability to drag KPI components into the Query Builder
    • Fixed a crash when trying to alter the expression for a dynamic format string for a calculation group item
  • Fixed hang when pasting in code with extremely long lines (tens of thousands of characters)
  • Fixed #451 up/down keys not work consistently
  • Fixed #446 Aggregate rewrite events causing an error
  • Fixed #444 to use correct culture in csv export
  • Fixed #445 defaulting the selected model to the base model not the first perspective
  • Fixed an issue with the trace layout options not displaying
  • Fixed a bug with swap delimiters feature and block comments
  • Fixed drag/drop of text not working within editor
  • Fixed #475 proxy auth error when entering AAD credentials
  • Fixed #485 Swap delimiters incorrectly affecting functions with periods in the name
  • Fixed #486 Define Measure not using Separator specified in Options
  • Fixed #497 Define Measures not working when connected to a model with active translations
  • Fixed #499 “Show Objects that Reference this Column” feature did not include table name.
  • Fixed #501 avoiding bug with EXCEPT function in Query Builder

Miscellaneous Crash fixes

In addition to the fixes listed above a number of other fixes have been made that should address some issues that came in via crash reports, but the majority of these reports were submitted anonymously. What this means is that for many of them it is impossible to verify that the fix has worked as we have no steps to reproduce the issue and the fix is based on assumptions and best guesses of the root cause of the issue.

If you are experiencing a regular crash or issue I would encourage you to open a bug on github so that I can help resolve your issues.

DAX Studio 2.12.1 Released

There are just a few minor updates to DAX Studio in this release

New Features

  • Pasting a connection string into the server name of the Connection dialog will parse out the Data Source and Initial Catalog settings

Fixes

  • #391 – issues when copying Power BI Performance data and pasting into Excel
  • #393 – application crashed if you attempted to open the Initial Catalog list before specifying a server
  • #394 – output to text file was not working for multiple results sets
  • #397 – “All Queries” trace was not working against PowerPivot connections

DAX Studio 2.12 release

DAX Studio 2.12 has just been released and I wanted to take some time to walk through some of the significant new features in this release.

The Installer is now signed with EV Certificate!

The fact that Installer now signed with an EV certificate might sound like technobabble but this should hopefully prevent all the issues and warnings people have been getting from from Windows SmartScreen when attempting to download and install DAX Studio.

A HUGE thank-you goes out to the guys at SQLBI.com. To acquire an EV certificate not only requires a financial outlay, but you also need to have a legal company entity with a physical office and financial records and things like that. Both of these things make it pretty much impossible for a free/open source tool to obtain an EV certificate on their own without assistance.

Note: Even though Windows may show SQLBI as the publisher this is due to the fact that they have funded the code signing certificate and it is a condition of the EV certificate that it has to bear the legal name of the entity that purchased it. DAX Studio is still an open source tool and all support requests still go through our github issue register.

Initial Catalog option added to Connection Dialog

An Initial Catalog setting has been added to the Advanced Options in the connection dialog, if you have a very large model or slow connection (like AzureAS or VPN) then you can now choose the initial database you are connected to. This saves you having to wait for the metadata to load for the default database, then change to another database and wait for the second lot of metadata to refresh.

In future I also want to look at improving the ability for DAX Studio to automatically re-connect to dropped connections. But that will have to wait for a future release.

Format Query button updated

The Format Query button has been changed into a split-button so that you can choose the style of formatting by clicking on the bottom half of the button.

Clicking the top half of the button will still trigger the default formatting style (which can be changed in the Options menu)

Portable version

DAX Studio now has a portable download option. The portable version is just a zip file which you can unzip then run by clicking on DaxStudio.exe. This is helpful if you need to run DAX Studio on a machine where you may not have admin rights or where you do not want to run the installer.

The portable version has the following limitations:

  • The Excel addin is not included (as we need to write to the registry with admin rights to register the addin with Excel)
  • The Power BI Desktop External Tools feature will not work (as it requires admin rights to add the pbitools.json file under c:\program files\…)
  • The ability to double click on a .dax file to open DAX Studio will not work (this also requires making registry changes to the machine)

Otherwise the portable version is identical to the installed version. Apart from the above limitations the only difference is that settings, query history, autosave files and error logs are all redirected to local files and folders underneath the folder where daxstudio.exe is launched from

Other New Features

  • Added Roles and IsAdmin properties to Database tooltip that can be useful when testing security settings.

Fixes

  • #378 Power BI External Tools – installer not correctly updating path in daxstudio.pbitools.json file if a non-default install location is used
  • #377 exporting tables with no rows would throw an error
  • #376 autosave not recovering open files for all types of crashes
  • #374 improved tooltip sampling to prevent full column materialization
  • Fixed crash when attempting to export using invalid manual connection string
  • #381 re-connecting to a pbix file that has been closed and re-opened after launching from External Tools does not work

DAX Studio 2.11.2 Released

I’ve just pushed out a minor update for DAX Studio, it fixes a few issues I’ve received from crash reports as well as the following items:

Updates

  • Adds support for the “External Tools” ribbon in Power BI Desktop

Fixes

  • #373 Fixes “Define Measure” when connected to a SSAS instance without admin rights
  • #372 Duplicate MyMeasures created when adding more than 1 custom measure in the Query Builder
  • #367 Measure information duplicated in VPAX files
  • Fixed crash when highlighting
  • Fixed crash when closing last tab and connection had been lost

DAX Studio 2.11.1 Release

I just published a point release for DAX Studio that fixes the following bugs. You can get it from https://daxstudio.org

Fixes

  • Filters on datetime columns where we cannot parse the date value now report the error rather than crashing when using the “Edit Query” button
  • #343 Fixed spelling of KEEPFILTERS for IS NOT filters in the new Query Builder
  • #345 Comma placement changed to match the style used by DaxFormatter
  • #346 / #347 DaxFormatter now works in the Expression Editor
  • #351 error while exporting tables to SQL Server
  • #352 hierarchies in display folders not expanding
  • #357 leading/trailing comments missing when doing “define measure”

A New Begining

After many happy years on my previous blog platform I decided that it was time for a move. All my previous posts are still available at http://geekswithblogs.net/darrengosbell but from now on all my new content will be posted on this site. Although geekswithblogs.net had a bright start many years ago it is now suffering from neglect, the rss feed had unresolved issues, the comment engine and contact pages were completely broken and they don’t support https.

So I decided that now was the time to move my blog and give it a facelift in the process.