SAP BW Unable to adress a Field (In a Hierarchy)

[Anonymous]

When trying to do a DISTINCTCOUNT of a field that is in a defined Hierarchy) we are always getting a error.

Any idea's

Feedback Type:Frown (Error)
Timestamp:2017-08-23T13:07:24.2082480Z
Local Time:2017-08-23T15:07:24.2082480+02:00
Product Version:2.49.4831.521 (PBIDesktop) (x64)
Release:August, 2017
IE Version:11.0.9600.18762
OS Version:Microsoft Windows NT 6.1.7601 Service Pack 1 (x64 en-US)
CLR Version:4.6.1 or later [Release Number = 394271]
Workbook Package Info:1* - en-GB, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.
Peak Working Set:579 MB
Private Memory:469 MB
Peak Virtual Memory:3.85 GB
Error Message:OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..
Telemetry Enabled:True
Model Default Mode:DirectQuery
Snapshot Trace Logs:C:\Users\EXK528\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot458615073.zip
Performance Trace Logs:C:\Users\EXK528\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip
Enabled Preview Features:PBI_shapeMapVisualEnabled
Enabled DirectQuery Options:PBI_DirectQuery_Unrestricted
Error Code:QueryUserError
OData Error Message:Failed to execute the DAX query.
DPI Scale:100%
Supported Services:Power BI
Formulas:

section Section1;
shared Cube = let
Source = SapBusinessWarehouse.Cubes("xxx", "xxx", "xxx", [ScaleMeasures=false, LanguageCode="NL"]),
#"$INFOCUBE" = Source{[Name="$INFOCUBE"]}[Data],
#"$BPA_M03" = #"$INFOCUBE"{[Id="$BPA_M03"]}[Data],
#"Added Items" = Cube.Transform(#"$BPA_M03",
{
}),
AddUniqueIds = let
NewDimensions = Table.TransformColumns(Cube.Dimensions(#"Added Items"),
{
"Data",
(dimTable) => List.Accumulate(
Table.ColumnNames(dimTable),
dimTable,
(table, column) => Table.AddColumn(table, column & ".UniqueId", each Cube.AttributeMemberId(Record.Field(_, column)))
)
}),
NewCube = Cube.ReplaceDimensions(#"Added Items", NewDimensions)
in
NewCube
in
AddUniqueIds;

[v-yuezhe-msft · Accepted solution]

@Anonymous,

It seems that you hit the limitation which is described in this official article, it is not possible to use an aggregate function over a table in DirectQuery mode and it is not possible to change the aggregation for a column.

Besides, currently, this new DirectQuery mode of SAP connector is available as a Beta feature, with the main goal of gathering customer feedback about overall capabilities and performance. Regarding to your issue, click "send a frown" button to report it.

Regards,
Lydia

[v-yuezhe-msft · Accepted solution]

@Anonymous,

It seems that you hit the limitation which is described in this official article, it is not possible to use an aggregate function over a table in DirectQuery mode and it is not possible to change the aggregation for a column.

Besides, currently, this new DirectQuery mode of SAP connector is available as a Beta feature, with the main goal of gathering customer feedback about overall capabilities and performance. Regarding to your issue, click "send a frown" button to report it.

Regards,
Lydia

[Anonymous]

Lydia,

Can I ask something about the plans with the directquery option towards SAP-BW ?

Not for immediate use but a real design issue towards the future.

[v-yuezhe-msft · Accepted solution]

@Anonymous,

I am not sure the detailed plan about the directquery option towards SAP, however, this new DirectQuery mode of SAP connector is available as a Beta feature, with the main goal of gathering customer feedback, if you have any feature requests, you can submit an idea in this forum: https://ideas.powerbi.com/forums/265200 .

Regards,
Lydia

[Anonymous]

I'm connecting to a SAP BW with directquery.

Using the $INFOCUBES.

Connection works fine (but slow), normal visuals are working fine.

Now I'm trying to add a Measure

Measure SelCount Table = COUNTROWS(Stamkostenplaats)

This works fine on a normal 'Table', but when the tables contains Hierarchies it goes bananas and I can't find a way to have it defined correctly.

When trying to have it defined by a distinctcount on a field

Measure SelCount Jaar = DISTINCTCOUNT(Stamkostenplaats[[0MAST_CCTR                    2020NMBS/SNCB]].[LEVEL03]].UniqueId])

Whatever I try, always getting erros in the visuals ...

Feedback Type:Frown (Error)
Product Version:2.49.4831.521 (PBIDesktop) (x64)
Release:August, 2017
IE Version:11.0.9600.18762
OS Version:Microsoft Windows NT 6.1.7601 Service Pack 1 (x64 en-US)
CLR Version:4.6.1 or later [Release Number = 394271]
Workbook Package Info:1* - en-GB, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.
Peak Working Set:735 MB
Private Memory:570 MB
Peak Virtual Memory:3.93 GB
Error Message:OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..
User ID:3821d278-b3b7-468c-8d82-5c4041a1a6ed
Session ID:55429681-9860-46d7-b830-a5648cc8ee2f
Telemetry Enabled:True
Model Default Mode:DirectQuery
Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_numericSlicerEnabled
PBI_daxTemplatesEnabled
Disabled Preview Features:
PBI_SpanishLinguisticsEnabled
CustomConnectors
Enabled DirectQuery Options:
PBI_DirectQuery_Unrestricted

Cloud:GlobalCloud
Activity ID:null
Time:Wed Aug 23 2017 16:02:49 GMT+0200 (Romance Daylight Time)
Error Code:QueryUserError
OData Error Message:Failed to execute the DAX query.
DPI Scale:100%
Supported Services:Power BI
Formulas:

section Section1;

shared Cube = let
    Source = SapBusinessWarehouse.Cubes("sapbbp1.belgianrail.be", "02", "001", [ScaleMeasures=false, LanguageCode="NL"]),
    #"$INFOCUBE" = Source{[Name="$INFOCUBE"]}[Data],
    #"$BPA_M03" = #"$INFOCUBE"{[Id="$BPA_M03"]}[Data],
    #"Added Items" = Cube.Transform(#"$BPA_M03",
        {
        }),
    AddUniqueIds = let
    NewDimensions = Table.TransformColumns(Cube.Dimensions(#"Added Items"),
    {
        "Data",
        (dimTable) => List.Accumulate(
            Table.ColumnNames(dimTable),
            dimTable,
            (table, column) => Table.AddColumn(table, column & ".UniqueId", each Cube.AttributeMemberId(Record.Field(_, column)))
        )
    }),
    NewCube = Cube.ReplaceDimensions(#"Added Items", NewDimensions)
in
    NewCube
in
    AddUniqueIds;