PowerBi doesn't honour an SSAS OLAP cube date field

3
votes

We have an SSAS OLAP cube, in production since long and perfectly tested by dozens of users, with a normal Time dimension (two hierarchies, Months and Weeks, but this is irrelevant).

The Time dimension key is a date field. On the data view it is defined as DataType: System.DataTime. On the dimension as Calendar -> Date, Usage: Key.

Using this date field on an Excel table accessing the OLAP cube is fully operational, it is a date and "Date filter" options are available as expected.

But trying to use this field on PowerBI defeated all our efforts! No way to have PowerBI interpret the field as a date, so no date filters are available. PowerBI thinks it's a text field and nothing we can think makes it change its behaviour.

We tested PowerBI with an external Excel and adding the SSAS OLAP dimension so to be able to modify the field format and oh surprise! the field is interpreted as text. Changing the format to date makes it work. But there is no way to change the format for an SSAS OLAP cube when accessed directly as the primary (and only) source of data for the PowerBI repport.

Any idea how to define an SSAS OLAP date field so PowerBI understands it is a date?

2
datessaspowerbiolap
Now, I know it can be done, but I don't know how! Read a curious history here – DeepButi
"Same field, defined exactly the same way; same dimension defined exactly the same; everything seems identical, but it works on one OLAP SSAS cube (test) and it doesn't on another (the full company SSAS)" - are these on different servers? If yes, have you checked "compatibility level" of both? – user5226582
@user5226582 : Same server. We had to rebuild a brand new production OLAP from scratch. And PowerBI is able to read the date on the new one!! The only difference we can see is that the original (some years old) is a .sln file while the new one is a .dwproj file, but the contens "seems" identical to us. – DeepButi
To be precise, we open it using the .sln solution in one case and the dwproj in the other. Both cubes have all the required files (.sln, .dwproj, etc etc). It's just an historical habit :) – DeepButi

2 Answers

4
votes

Found it! One minute before getting mad forever I spotted the diference.

I was able to create two dimensions on the same cube, with the same field. One works, the other doesn't.

Define the date field as Order by Key and PowerBi treats it as a date.

Define the date field as Order by Name and PowerBi treats it as text.

1
votes

I came across same situation. I don't know OP's calendar dimension properties, but his solution does not helped me.

In dimension design I used ValueColumn property

enter image description here

And in Power BI I saw a new field, that worked pretty nice with Slicer visual:

enter image description here

And Excel all remained the same:

enter image description here