topic Broken relationship after RS refresh in Report Server

Broken relationship after RS refresh

robm135 — Wed, 08 Sep 2021 08:47:02 GMT

Hi I have an issue where resfreshing my data on Report Server breaks one of my datasets relationships to the date table.

The relationship is ok when refreshing the data in PowerBI desktop RS.

The relationship is ok when saving or overwriting the report to RS.

The relationship breaks after any scheduled refresh on RS. (Refresh is reported successfull)

e.g. goes from this:

to this:

I've gone into options and turned off these options

Running RS Jan21

Re: Broken relationship after RS refresh

d_gosbell — Wed, 08 Sep 2021 13:01:00 GMT

It's highly likely that the relationships are fine and that this is actually a data issue. This issue is actually happening on both the second and third columns of your output. It could be a difference in the language settings between the client and server causing the server to parse the dates differently or it could be a difference in the timezone settings causing a shift in datetime values. It depends on what your raw data looks like and what sort of transformations your are doing. Are you able to isolate this down to a handful of rows of sample data so that you can easily compare the differences between your data sets?

Re: Broken relationship after RS refresh

robm135 — Fri, 10 Sep 2021 01:09:51 GMT

yep thanks for pointing me in that direction.

my date fields in both tables are datatype of Date only (as opposed to date/time)

When I right click a date entry - copy - paste to notepad: I get a timezone in my fact table date field which wasn't there prior to getting refreshed on the report server.

Report Server fields just after being saved/overwriten by PowerBI-RS
fact: Thu Sep 09 2021 00:00:00 GMT+1000 (Australian Eastern Standard Time)
date: Thu Sep 09 2021 00:00:00 GMT+1000 (Australian Eastern Standard Time)

Report Server fields after performing a refresh
fact: Thu Sep 09 2021 14:00:00 GMT+1000 (Australian Eastern Standard Time)
date: Thu Sep 09 2021 00:00:00 GMT+1000 (Australian Eastern Standard Time)

This is confusing as I thought converting data type to a date only would strip out time & timezone info

Re: Broken relationship after RS refresh

robm135 — Tue, 14 Sep 2021 22:48:01 GMT

Here is my workaround:

Originally my fact table date was a string like '2021-09-06T01:35:22Z'

I would do a transform to date/time, then a transform to Date

This was fine until Report Server did the refreshing

I've replcaed this double step with

Split '2021-09-06T01:35:22Z' by delmiter T, change to Date

This now works when RS Server refreshes.

Using other Power Query transforms or functions to date only just didn't work.