cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mkabel
Frequent Visitor

Direct query SAP HANA variables and date format

Hello experts,

 

I would like to use PowerBI Desktop as a frontend for SAP BW HANA. Looks fine on the first glance, but there are some obstacles.

If I use direct query without "Treat HANA as relational database" all date fields are treated as text and the data type is greyed out. But I can change variables in the transformation menu

If I use direct query with "Treat HANA as relational database" I can change the data type of my date fields to date but in the transformation menu the entry "variables" is greyed out.

 

I don't think this a feature. Both requests from business are valid. Date fields in SAP HANA should be date fields and variables should be variable.

 

Any suggestions how to solve this?

 

Thanks in advance

Matthias

7 REPLIES 7
mkabel
Frequent Visitor

Problem still exists with December version of Power BI Desktop.

 

Sorry to say, but this is real needed if you want to use PowerBI Desktop with SAP BW

 

Kind regards 

Matthias

v-angzheng-msft
Community Support
Community Support

Hi, @mkabel 

 

May I ask if your problem has been solved? Is the above post helpful to you?

Have you tried to check the above setting? Does it work for you?

 

And it would be better if you could post the solution here which will benefit other users who might have the same issue.

 

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Zeon Zheng,

 

unfortunately this option doesn't exist in PowerBI desktop and it wouldn't solve the date format problem either.

 

Kidn regards

Matthias

mkabel
Frequent Visitor

Hi,

sorry for german screenshots but I think "transform data" and "Daten transformieren" is similiar as "Date" and "Datum", both are wrong pronounced latin. 😉

 

first variables are variables from the SAP calculation view like date, business year or planning version.

They will be asked when connection to the database the first time, but should be changeable after creating the report.

Variables when connection to the calculation viewVariables when connection to the calculation view

 

 

 

 

 

 

 

 

 

 

 

 

After the connection is established the PowerBI Desktop behaves different depending on "Treat SAP HANA as relational database".

 

If the flag is set I can change the format of a date column from text to date:

2021-11-05_18h55_23.png

But I'm not able to change the variables from the first connection any more, as this menu entry is greyed out.

2021-11-05_18h56_16.png

 

 

Without this flag the PowerBI Desktop I can not change the format of a column and a date column is text. (On HANA side it's formated as date and also the semantic information is set)

 

Change format is greyed outChange format is greyed out

But I can change the variables like date or planning version 

 

2021-11-05_12h20_10.png

 

So there is always a killer for using PowerBI with a SAP BW HANA calculation view, which would be great as this is such a great tool for handling data.

 

Kind regards 

Matthias

 

v-angzheng-msft
Community Support
Community Support

Hi, @mkabel 

 

Could you please consider sharing some screenshots to illustrate your situation?

 


If I use direct query without "Treat HANA as relational database" all date fields are treated as text and the data type is greyed out. But I can change variables in the transformation menu

If I use direct query with "Treat HANA as relational database" I can change the data type of my date fields to date but in the transformation menu the entry "variables" is greyed out.


What does the entry "variables" in the transformation menu refer to?

 

 

Dear v-angzheng-msft,

 

where the screenshots helpful? 

Can I add any information to resolve this problem?

 

Kind regards

Matthias 

Hi, @mkabel 

 

Sorry for the late reply, I actually wasn't able to reproduce your question due to the fact that I don't have that data source to test.

I checked a lot of information and found a piece of information that might be helpful.

 

To enable the SAP edit variables feature, in Power BI Desktop connect to an SAP HANA or SAP BW data source. Then go to File > Options and settings > Options and then, in the Current File section in the left pane, select DirectQuery. When you select that, in the right pane you see DirectQuery options, and a checkbox where you can Allow end users to change SAP variables in the report, as shown in the following image.

vangzhengmsft_2-1636704521960.png

 

Refer:

To enable the SAP edit variables feature.

Connect to SAP HANA databases in Power BI

 

Hope this helps

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.