OLE DB or ODBC error: [Expression.Error] We cannot apply operator >= to types List and Number..

[kasimrehman]

🐛 Problem

After an cost data export in the FOCUS format has been made, when importing the data into the CostSummary PowerBI application is attempted this error appears when loading the data: "OLE DB or ODBC error: [Expression.Error] We cannot apply operator >= to types List and Number.."

This seems to happen when the data format of the PC on which the PowerBI app is running is non-US. If you change the data format settings on the Windows PC the data load works. Also, it seems that only certain records are affected by this. You can change the start range of the data to be loaded in the PowerBI app. It seems that when you then happen to leave out the offending data the data load works.

👣 Repro steps

I am not sure if the problem arises from all data exported from Cost Managment in the Azure portal, but I am working with a customer who has such a data set.

1. Export December's data from the Azure portal as described in the documentation in these GitHub pages.
2. Try to load the data in the CostSummary PowerBI app running on a Windows PC with non-US date format.
3. It fails with "OLE DB or ODBC error: [Expression.Error] We cannot apply operator >= to types List and Number.."
4. Change the date format on the PC.
5. The data load is successful

🤔 Expected

The date format of the PC should not have any effect on the data loaded.

[flanakin]

@kasimrehman What version of the report do you have? I thought we fixed this one already. I can't seem to find where it might happen anymore in the 0.7 reports. I'm also having a hard time replicating this after changing Power BI and Windows region settings. I'm hoping this is an old version of the report. If not, I may need some help to investigate what's going on.

#needs-info

[kasimrehman]

Hi Michael,

It was the 0.7 version. I will ask the customer if they can demonstrate the problem to us again.

Kind regards,

Kasim

[flanakin]

@kasimrehman Can you also ask them to clear the RangeStart/RangeEnd parameters? Last time I looked into this, I thought it was caused by those.

[flanakin]

@arthurclares Can you help here to see if you can replicate this with and without setting RangeStart/RangeEnd?

[kasimrehman]

@kasimrehman Can you also ask them to clear the RangeStart/RangeEnd parameters? Last time I looked into this, I thought it was caused by those.

Hi Michael,

Yes, I think we were setting at least one them. Is setting one of them not mandatory?

[flanakin]

@kasimrehman No, neither of them are required. They're only there to support incremental refresh.

[RolandKrummenacher]

Are we talking about the KQL or the Storage version of the CostSummary PowerBI report?

[flanakin]

@RolandKrummenacher This error only happens in the storage reports (as far as I've seen so far). That's a good point.

@kasimrehman, please consider deploying FinOps hubs with Data Explorer. If you're reporting on anything more than $100K, it's likely worth the performance and capability improvements that you'll see.

[flanakin]

@kasimrehman Can you try the latest reports in the 0.8 release? Looking at the changelog, we applied some changes to fix this and that may be why I wasn't able to reproduce it (since I was working off our latest codebase). You may find that the latest reports no longer have this problem.

We'll keep this issue open for a few weeks to give you a chance to confirm.

#needs-info

[kasimrehman]

@flanakin I recently upgraded them to 0.8 but this time we did not put anything into the date fields as you suggested and it worked. I guess, if it has not been encountered on 0.8, we can regard this issue as solved then.

[flanakin]

Thanks for confirming. I'll go ahead and close this issue, but we'll keep an eye out for any new reports.

Let us know if you happen to get a chance to test it out. It'd be great to have an explicit confirmation 😜

For anyone else, if you run into this issue, please update to 0.8. If you experience the issue on 0.8, then please create a new issue so it bubbles up in our triage process. Feel free to reference this one.