XMLA with MDX issues (timeouts and process multiplication)

Hello 

I am trying to retrieve data from Essbase to PBI.

For this i am using the Harry Gates method (thanks to him! :) )

When the Essbase has been newly started, i can retrieve my data very fast (less than 30 sec).

The issue, is that if afterwards I try to retrieve the same MDX or something different, i got a timeout despite i set the timeouts high. And, the Essbase server gets blocked, busy with the several XMLA requests he received. This of course does not make the Essbase admin happy. I could not find answers elsewhere, so here are my questions:

- when we execute an XMLA statement in PBI, when is the query executed: each time an object changes in PBI, or only when the refresh button is pressed?

- How is it possible that this XMLA script generates several requests to Essbase?

- I thought that when the query is done successfully the process is then stopped automatically. Apparently I an not right. Does anybody knows if it is a PBI issue or Essbase issue?

- I set the same timeout in the XMLA as in the registery.  So I don t think the problem comes from here then.

Please find below the query i am using in PBI. It is an MDX query (below in bold) embededd in a XMLA script, embededd into an M query. This query works fine when the Essbase server has been rebooted, but if i start a second time this query i already get a timeout, and then the server keeps on being busy / do not answer. Our server has enough power for the SmartView retrievals, so i don t think the hardware is a problem.

Thanks in advance for your support / hints!

Best regards

Ghuiles

let
Source = Xml.Tables(Web.Contents("http://xxxxx:xxxx/aps/XMLA",
[
Headers = [#"Content-Type"="text/xml; charset=utf-8"],
Content=Text.ToBinary("
<SOAP-ENV:Envelope xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'
xmlns:wsse='http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd'
xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
<SOAP-ENV:Header>
<Security xmlns='wsse'>
<UsernameToken>
<Username>xxxxx</Username>
<Password>xxxxx</Password>
</UsernameToken>
</Security>
</SOAP-ENV:Header>
<SOAP-ENV:Body>
<Execute>
<Command>
<Statement>

SELECT NON EMPTY ( {[Local Currency]})
ON COLUMNS,
NON EMPTY (
crossjoin({[Y2016]},
crossjoin({[Actual]},
crossjoin({[Local Reporting]},
crossjoin({[Jan]},
crossjoin({[Germany]},
crossjoin({leaves([Total Cost Center])},
crossjoin({[Total Service Type]},
crossjoin(DESCENDANTS([Accounts], [GAC 32014].LEVEL),
leaves([Total Customers]))))))))))
on rows from [Group.HL]

</Statement>
</Command>
<Properties>
<PropertyList>
<DataSourceInfo>Essbase;Data Source=xxxxx</DataSourceInfo>
<Content>SchemaData</Content>
<Catalog>xxxxx</Catalog>
<Format>Multidimensional</Format>
<AxisFormat>TupleFormat</AxisFormat>
<Content>SchemaData</Content>
<Timeout>90000000</Timeout>
</PropertyList>
</Properties>
</Execute>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
")
]
)),
Body = Source{0}[Table],
xmlAnalysis = Body{0}[Table],
ExecuteResponse = xmlAnalysis{0}[Table],
return = ExecuteResponse{0}[Table],
mddataset = return{0}[Table],
root = mddataset{0}[Table],
Axes = root{2}[Table],
Axis = Axes{0}[Table],
AxisTextType = Table.TransformColumnTypes(Axis,{{"Attribute:name", type text}}),
ExpandTuples = Table.ExpandTableColumn(AxisTextType, "Tuples", {"Tuple"}, {"Tuples.Tuple"}),
AxisOrdinalMap = Table.ExpandTableColumn(ExpandTuples, "Tuples.Tuple", {"Member", "Attribute:Ordinal"}, {"Tuples.Tuple.Member", "Tuples.Tuple.Attribute:Ordinal"}),
AddDimensionMembers = Table.AddColumn(AxisOrdinalMap, "DimensionMembers", each Text.Combine([Tuples.Tuple.Member][UName], ",")),
AxesTable = Table.AddColumn(AddDimensionMembers, "DimensionAliases", each Text.Combine([Tuples.Tuple.Member][Caption], ",")),
Axis0 = Table.SelectRows(AxesTable, each ([#"Attribute:name"] = "Axis0")),
Axis1Text = Table.SelectRows(AxesTable, each [#"Attribute:name"] <> "Axis0" and [#"Attribute:name"] <> "SlicerAxis"),
Axis1 = Table.TransformColumnTypes(Axis1Text,{{"Tuples.Tuple.Attribute:Ordinal", type number}}),
SlicerAxisText = Table.SelectRows(AxesTable, each ([#"Attribute:name"] = "SlicerAxis")),
SlicerAxis = Table.TransformColumnTypes(SlicerAxisText,{{"Tuples.Tuple.Attribute:Ordinal", type number}}),
CellData = root{3}[Table],
Cells = Table.ExpandTableColumn(CellData, "Table", {"Value", "FmtValue", "Attribute:CellOrdinal"}, {"Table.Value", "Table.FmtValue", "Table.Attribute:CellOrdinal"}),
#"Changed Type" = Table.TransformColumnTypes(Cells,{{"Table.Attribute:CellOrdinal", type number}}),
SetColumnNumber = Table.AddColumn(#"Changed Type", "Axis0", each Number.Mod([#"Table.Attribute:CellOrdinal"], Table.RowCount(Axis0))),
GroupedRows = Table.Group(SetColumnNumber, {"Axis0"}, {{"ColumnNum", each _, type table}}),
AddedClusteredIndex = Table.TransformColumns(GroupedRows, {"ColumnNum", each Table.AddIndexColumn(_,"ClusteredIndex",0,1)}),
ExpandClusteredColumn = Table.ExpandTableColumn(AddedClusteredIndex, "ColumnNum", {"Table.Value", "Table.Attribute:CellOrdinal", "ClusteredIndex"}, {"ColumnNum.Table.Value", "ColumnNum.Table.Attribute:CellOrdinal", "ColumnNum.ClusteredIndex"}),
#"Sorted Rows" = Table.Sort(ExpandClusteredColumn,{{"ColumnNum.Table.Attribute:CellOrdinal", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"ColumnNum.Table.Attribute:CellOrdinal"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Axis0", type text}}),
MergeAxis0 = Table.NestedJoin(#"Changed Type2",{"Axis0"}, Axis0, {"Tuples.Tuple.Attribute:Ordinal"},"Columns",JoinKind.LeftOuter),
ExpandColumns = Table.ExpandTableColumn(MergeAxis0, "Columns", {"DimensionMembers", "DimensionAliases"}, {"Columns.DimensionMembers", "Columns.DimensionAliases"}),
MergeAxis1 = Table.NestedJoin(ExpandColumns,{"ColumnNum.ClusteredIndex"}, Axis1,{"Tuples.Tuple.Attribute:Ordinal"},"Rows",JoinKind.LeftOuter),
ExpandRows = Table.ExpandTableColumn(MergeAxis1, "Rows", {"DimensionMembers", "DimensionAliases"}, {"Rows.DimensionMembers.1", "Rows.DimensionAliases.1"}),
AddSlicerMemberNames = Table.AddColumn(ExpandRows, "SlicerMemberNames", each SlicerAxis[DimensionMembers]),
ExpandSlicerMemberNames = Table.ExpandListColumn(AddSlicerMemberNames, "SlicerMemberNames"),
AddSlicerMemberAliases = Table.AddColumn(ExpandSlicerMemberNames, "SlicerMemberAliases", each SlicerAxis[DimensionAliases]),
ExpandSlicerMemberAliases = Table.ExpandListColumn(AddSlicerMemberAliases, "SlicerMemberAliases"),
result = Table.RemoveColumns(ExpandSlicerMemberAliases,{"Axis0", "ColumnNum.ClusteredIndex", "Columns.DimensionMembers", "Rows.DimensionMembers.1"}),
#"Split Column by Delimiter" = Table.SplitColumn(result, "Rows.DimensionAliases.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Rows.DimensionAliases.1.1", "Rows.DimensionAliases.1.2", "Rows.DimensionAliases.1.3", "Rows.DimensionAliases.1.4", "Rows.DimensionAliases.1.5", "Rows.DimensionAliases.1.6", "Rows.DimensionAliases.1.7", "Rows.DimensionAliases.1.8", "Rows.DimensionAliases.1.9"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Rows.DimensionAliases.1.1", type text}, {"Rows.DimensionAliases.1.2", type text}, {"Rows.DimensionAliases.1.3", type text}, {"Rows.DimensionAliases.1.4", type text}, {"Rows.DimensionAliases.1.5", type text}, {"Rows.DimensionAliases.1.6", type text}, {"Rows.DimensionAliases.1.7", type text}, {"Rows.DimensionAliases.1.8", type text}, {"Rows.DimensionAliases.1.9", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"SlicerMemberNames", "SlicerMemberAliases"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Columns.DimensionAliases", "Currency"}, {"Rows.DimensionAliases.1.1", "Year"}, {"Rows.DimensionAliases.1.2", "Scenario"}, {"Rows.DimensionAliases.1.3", "Reporting"}, {"Rows.DimensionAliases.1.4", "Month"}, {"Rows.DimensionAliases.1.5", "Country"}, {"Rows.DimensionAliases.1.6", "Cost Center"}, {"Rows.DimensionAliases.1.7", "Service Typ"}, {"Rows.DimensionAliases.1.8", "Account"}, {"Rows.DimensionAliases.1.9", "Customer"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"ColumnNum.Table.Value", type number}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type3","Y","",Replacer.ReplaceText,{"Year"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Replaced Value",{{"Year", type number}})
in
#"Changed Type4"