SAP BW connection with variables

[sioux]

Hi

I can connect to BW from desktop successfully as long as the query has no parameters. When trying to connect to a query with params, I get an error.

PowerBi Desktop version: 2.43.4647.541 

I did a trace ....

Extracted from the traces:

SAP Business Warehouse: RfcInvoke failed(RFC_ABAP_RUNTIME_FAILURE)
Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwMdxCubeMetadataProvider

at Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwMdxCubeMetadataProvider.<>c__DisplayClasse.<GetLevels>b__d()
at Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwMdxCubeMetadataProvider.GetLevels()
at Microsoft.Mashup.Engine1.Library.Mdx.MdxCubeMetadataProviderCube.LoadDimensions()
at Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwParametersTableValue.SapBwParametersTableValueBuilder.TryGetHierarchyMembers(SapBwVariable variable, Boolean allowNonAssigned, IEnumerable`1& values)
at Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwParametersTableValue.SapBwParametersTableValueBuilder.NewFunctionParameterTypeValue(SapBwVariable variable,
at Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwParametersTableValue.SapBwParametersTableValueBuilder.NewMultipleSingleValue(SapBwVariable variable)\r\n
at Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwParametersTableValue.SapBwParametersTableValueBuilder.Build(IList`1 variables)\r\n

complete line (i guess here you can see all the required infos):
DataMashup.Trace Warning: 24579 : {"Start":"2017-03-02T07:45:20.7945671Z","Action":"SimpleDocumentEvaluator/GetResult","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Runtime.ValueException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: [DataSource.Error] SAP Business Warehouse: RfcInvoke failed(RFC_ABAP_RUNTIME_FAILURE): RAISE_EXCEPTION\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwService.HandleExceptions(Action action)\r\n at Microsoft.Mashup.Engine1.Library.Common.DbData.CachingDbDataReader.WrappedRead()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbData.CachingDbDataReader.Read()\r\n at Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwMdxCubeMetadataProvider.<>c__DisplayClasse.<GetLevels>b__d()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.RunWithRetryGuard[T](Int32 maxRetryAttempts, Func`1 action)\r\n at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.Invoke[T](Int32 maxRetry, Func`1 action)\r\n at Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwMdxCubeMetadataProvider.GetLevels()\r\n at Microsoft.Mashup.Engine1.Library.Mdx.MdxCubeMetadataProviderCube.LoadDimensions()\r\n at Microsoft.Mashup.Engine1.Library.Mdx.MdxCubeMetadataProviderCube.EnsureInitialized()\r\n at Microsoft.Mashup.Engine1.Library.Mdx.MdxCubeMetadataProviderCube.get_Dimensions()\r\n at Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwParametersTableValue.SapBwParametersTableValueBuilder.TryGetHierarchyMembers(SapBwVariable variable, Boolean allowNonAssigned, IEnumerable`1& values)\r\n at Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwParametersTableValue.SapBwParametersTableValueBuilder.NewFunctionParameterTypeValue(SapBwVariable variable, String defaultValueCaption, Object defaultValue, Boolean allowNonAssignedMember)\r\n at Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwParametersTableValue.SapBwParametersTableValueBuilder.NewMultipleSingleValue(SapBwVariable variable)\r\n at Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwParametersTableValue.SapBwParametersTableValueBuilder.Build(IList`1 variables)\r\n at Microsoft.Mashup.Engine1.Library.SapBusinessWarehouse.SapBwCubeContextProvider.SapBwCubeContext.GetParameters(CubeValue cube)\r\n at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue1`2.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.DebugExpression.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.ExpressionInvocationExpression2.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.DebugExpression.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Runtime.Library.Linker.BindFunctionValue.TypedInvoke(RecordValue environment, Value section, TextValue name)\r\n at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue3`4.Invoke(Value arg0, Value arg1, Value arg2)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue0.Invoke()\r\n at Microsoft.Mashup.Engine1.Language.ListExpression.RuntimeListValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.ListExpression.RuntimeListValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.FunctionInvocationExpression2.Execute(Value frame)\r\n at Microsoft.Mashup.Engine1.Language.Expression.ExecuteCondition(Value frame)\r\n at Micro","ProductVersion":"2.43.4647.541 (PBIDesktop)","ActivityId":"52aca5d7-aac9-462a-8472-ec17479a7b03","Process":"Microsoft.Mashup.Container.NetFX40","Pid":18856,"Tid":1,"Duration":"00:00:00.0006911"}

DataMashup.Trace Information: 24579 : {"Start":"2017-03-02T07:45:20.8793247Z","Action":"Engine/IO/SAP Business Warehouse/ExecuteDbDataReader","Behavior":"Default","CommandText":"EXECUTE FUNCTION 'BAPI_MDPROVIDER_GET_LEVELS' EXPORTS CAT_NAM = 'ZMSFIPO20', CUBE_NAM = 'ZMSFIPO20/ZS_LO_MPRO_ZMSFIPO20_7_00175' TABLES 'LEVELS' INTO RESULTSET;","ProductVersion":"2.43.4647.541

The complete trace https://drive.google.com/drive/folders/0B18Jggm0GB14eVI4c29oXzF0WVE?usp=sharing

Martin

[TORNALLY]

sioux, this looks way too complicated.  Take a look at Simplement's Data Liberator:

       www.simplement.us

This platform surfaces SAP data in real-time, right into SQL Server, ready for Power BI - any table, transparent, cluster, pool table, etc - a very comprehensive tool, and easily installed.

Mike McNally

mmcnally@simplement.us

[Anonymous]

Hi @sioux,

Can you share something detail about your query? It will be help for troubleshooting.

Regards,

Xiaoxin Sheng

[sioux]

Hi

For sure I can / want & will. 

Not sure what details are of interest? Did you see the detailed trace file or any problems with access?

My colleague has tried other queries with params and was not successful either. So my assumption was, that it is something generic.

Thanks Martin

[Anonymous]

Hi @sioux,

>>Not sure what details are of interest? Did you see the detailed trace file or any problems with access?

The additional query formula which you used.

 

Regards,

Xiaoxin Sheng

[sioux]

this is keept empty in my case. I've choosen to select the query in the dialog which appears afterwards.

[PowerBIStudent]

Hello @Anonymous , @sioux

Could you please share the details of how did you resolve this issue?

Appreciate as many details as possible.

Thanks,

PowerBIStudent