Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mattk
New Member

Date import SSAS Cube live connection

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

9 REPLIES 9
DeepButi
Helper I
Helper I

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 Smiley Sad 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!

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 Smiley Wink

Found it!

 

Read the correct way to do it here

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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

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

Thanks,
Victor Rocca | www.victorrocca.com
v-sihou-msft
Microsoft Employee
Microsoft Employee

@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. 

 

Capture23.PNG

 

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,

@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
Responsive Resident
Responsive Resident

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.

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors