So , the problem is

1) We have one dimension with 40000 members and select them
using Hierarchize({[Accounts].Members})
2) I use some code like in listing below to get values for each cell
from ADOMD.Cellset.

Result: Performance is very bad 1 minute for 4000 of rows and C#
application takes 40 MB of memory without any additional operations only
for reading (m_oAdoMDCellSet.get_Item(ref coords)). The same MDX in Visual
Basic MDXSample application takes 30 sec for get and dislpay ALL data.

What can I do in such situation ?


ADOMD.Catalog m_oAdoMDCat;
ADOMD.Cellset m_oAdoMDCellSet;
m_oAdoMDCat = new ADOMD.CatalogClass();
m_oAdoMDCellSet = new ADOMD.Cellset();
ADOMD.Cell newcell;
string m_constr =""";
string st = "";
st = st +"WITH member [Measures].Year as '[Calendar periods]
..CurrentMember.Parent.Parent.Name',solve_order=2 ";
st = st + "member [Measures].Month as '[Calendar periods]
..CurrentMember.Name',solve_order=2 ";
st = st + "member Measures.SubAccount as 'iif([Accounts]
..CurrentMember.Level is [Sub Account],[Accounts].CurrentMember.Properties(\
"s_code\"),\"\")' ";
st = st + "member Measures.ExpenseCode as 'iif([Accounts]
..CurrentMember.Level is [Sub Account],[Accounts].CurrentMember.Properties(\
"s_expense_code\"),\"\")' ";
st = st + "SELECT { {[Calendar periods].[2005].[Quarter 2].[May]}
* {[Measures].Year,[Measures].Month,[Measures].[SubAccount],[Measures]
..ExpenseCode,Measures.[Amount Actual]} } ON COLUMNS ,";
st = st + "{Hierarchize({[Accounts].Members}) } ON ROWS ";
st = st + "FROM [Trial Balance] ";
st = st + "WHERE ([Fiscal periods].[All Fiscal periods])";


m_oAdoMDCat.let_ActiveConnection(m_constr);
m_oAdoMDCellSet.Open(st, m_oAdoMDCat.ActiveConnection);

int cols = m_oAdoMDCellSet.Axes[0].Positions.Count;
int rows = m_oAdoMDCellSet.Axes[1].Positions.Count;
int dims = m_oAdoMDCellSet.Axes[1].DimensionCount;

object[] coords = new object[2];

for (int i=0;i<rows;i++)
{
for(int j=0;j<dims;j++)
{
string str = m_oAdoMDCellSet.Axes[1].Positions[i].Members[j].Caption;
}

for (int m=0;m<cols;m++)
{
coords[0] = m; //column
coords[1] = i; //row
newcell = m_oAdoMDCellSet.get_Item(ref coords);
}
}

m_oAdoMDCellSet.Close();


}

Since you are using C#, have you tried it with ADOMD.NET ?
That would be my first piece of advice.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Dmitry Penzar via SQLMonster.com" <forum (AT) nospam (DOT) SQLMonster.com> wrote in
message news:444a805213a54273a27c68c1a6dd4413 (AT) SQLMonster (DOT) com...

Ok , I've installed XMLa & ADOMD.NET and wrote the same code , smth like:


////////////////////////////////////////
string st = "";
st = st +"WITH member [Measures].Year as '[Calendar.... ";
Adomd.AdomdConnection con = new Adomd.AdomdConnection();
con.ConnectionString = "Data source=http://localhost/xmla/msxisapi.dll;
Catalog=New Financial Report";
con.Open();
if (con.State == ConnectionState.Open)
{
//Create a new AdomdCommand object for the specified connection.
Adomd.AdomdCommand cmd = con.CreateCommand();
// Supply the command text for the AdomdCommand object.
cmd.CommandText = st;
Adomd.CellSet cs;
// Attempt to run the command and retrieve a cellset.
cs = cmd.ExecuteCellSet();
int cols = cs.Axes[0].Positions.Count; // 5 items
int rows = cs.Axes[1].Positions.Count; // 44 000 items

Adomd.Cell cl;

for (int i=0;i<rows;i++)
{
for (int m=0;m<cols;m++)
{
cl = cs.Cells[m,i];
}
}
/////////////////////////////////////

The result was: 100 MB memory + 100 MB VM after
cs = cmd.ExecuteCellSet() and reading rows for aproximately 50-100 per
second... so query executed 30 sec for opening cellset + 400-800 sec for
reading...

So the conclusion is ADO.MD with interop or ADOMD.NET gives the same
result .

So , do you have any ideas ?

--
Message posted via http://www.sqlmonster.com