Date import SSAS Cube live connection

[mattk]

Hello,

I am connecting live to a SSAS cube with Power-BI desktop (version July 2016). In the cube I created a dimension containing a date (tried different formats like: 2014-01-01, 2014.01.01, 01.01.2014 etc.) with its dimension attribute type set to "Date". Power-BI is not recognizing this dimension as a date since I can't filter it like dates imported from an excel file. Also when I load the data from my cube into Power-BI (no live connection), the columns of my dimension have the data type "text" and I can not change it to "date".

Do my date values have to have a special format or how else can I fix that problem?

Thank you for your help,

Matthäus

[DeepButi]

Any known plan to change this behaviour?

Using dates for any serious business PowerBI report is critical. Not being able to use dates as dates makes selecting the data you want to analyze extremely hard.

We have an SSAS with dozens of nice calculated measures for previous periods and the like, our PowerBI users love PowerBI ... but working with date periods is simply painfull!

[DeepButi]

Life is curious. After some weeks dealing with this problem I found last friday the authorized answer of Microsoft on this post: no way to have dates as dates on PowerBI coming from SSAS live.

Five minutes later (literally, five) I saw one of my colleagues using an SSAS live date field as date on PowerBI. I was astonished ...

-How did you do that?

- I did nothing. I took one of your test databases, tried it, and it works. Good job! When will we upload it to our production systems?

I run to my desk and have been trying for hours. 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). No way to make it fail on the test olap; no way to make it work on the production olap.

Checked everything I could imagine, added hierarchy, dropped it. Defined flexible/rigid relationships. I cannot see the slightest difference. Tested and tested and tested again. Nothing.

So now I face the fact that it can be done ... but I don't know how neither why!!

Any idea 

[DeepButi]

Found it!

Read the correct way to do it here

[Anonymous]

It appears its not that straight forward. My dimension already is defined with order by key and yet the same behaviour.

[Anonymous]

Date.Value also will not show if its part of a hierarchy or if its attributehierarchyenabled is false.

[vrocca]

Sounds like your issues was with a Multidimensional SSAS cube? Any idea if/how to get it to work on a Tabular cube?

[v-sihou-msft]

@mattk

In Power BI Desktop, when using live connection to SSAS, no matter what the format is defined in source cube, the date attributes is always resolved as text field. To use it as date field, you have to build a Date hierarchy in your source dimension. When you drag the date field within hierarchy into visual, the entire hierarchy will be displayed in visual. 

In import mode, if the date column is in a proper format (like 2016/1/1, 2016-01-01), you should be able to convert it into a date type. 

Regards,

[mattk]

@v-sihou-msft

thank you very much for your reply!

Do I undestand you correctly, that the date will still be shown as a text field when it is in a hierachy? I alreay tried putting it in a hierachy, but it is still veiwed as text. That means I cannot use visuals which only work with datetype data when I have a live connection to a SSAS cube? Also the filtering of a date (after a certain day, hour etc.) is not possible with a live connection?

Thank you,

Matthäus

[Habib]

You need to fix the date format in Cube. Also, i you want to use hierarchy as you mentioned in excel, you need to create that hierarchy in Cube. Your date will start beaving according to that hierarchy.

In live connection, you can't play arround with data in Power BI.