Here’s an example of one of my C# classes where I read the
results into a DataTable and return the DataTable.
This is an example of getting Age Groups vs Gender (How many
consumers Age 24-35 that are male?)
I have it returning a fake dataset for testing because my
connection to sql server 2000 is very slow.
public class MdxToDTConsumer
{
//String db =
"usush2kdm01";
String
consumerConnStr;
AdomdConnection
consumerConn;
public
MdxToDTConsumer()
{
if (ConfigurationManager.AppSettings["fakedata"].Equals("1"))
return;
string
consumer_cube = ConfigurationManager.AppSettings["consumer_cube"].ToString();
consumerConnStr = "provider=msolap.2; Data Source=" + db +
"; Initial Catalog = \"" +
consumer_cube + "\"";
////////////////
// Make
Connection here
///////////////
consumerConn = new
AdomdConnection(consumerConnStr);
consumerConn.Open();
}
public void CloseConn()
{
if
(consumerConn != null &&
consumerConn.State == ConnectionState.Open)
consumerConn.Close();
}
public DataTable
GetAgeGenderDT(string zone_id, string partner_id, string
ct_id, string p1, string
lang)
{
DataTable
AgeGenderTable = new DataTable();
AgeGenderTable.Columns.Add("label", typeof(string));
AgeGenderTable.Columns.Add("label_key", typeof(int));
AgeGenderTable.Columns.Add("all_gender", typeof(int));
AgeGenderTable.Columns.Add("male", typeof(int));
AgeGenderTable.Columns.Add("male_pct_val", typeof(double));
AgeGenderTable.Columns.Add("male_pct", typeof(string));
AgeGenderTable.Columns.Add("female", typeof(int));
AgeGenderTable.Columns.Add("female_pct_val", typeof(double));
AgeGenderTable.Columns.Add("female_pct", typeof(string));
AgeGenderTable.Columns.Add("unknown", typeof(int));
AgeGenderTable.Columns.Add("unknown_pct_val", typeof(double));
AgeGenderTable.Columns.Add("unknown_pct", typeof(string));
if (ConfigurationManager.AppSettings["fakedata"].Equals("1"))
{
for
(int i = 0; i < 20; i++)
{
DataRow
dataRow = AgeGenderTable.NewRow();
dataRow["label"] = "Item num"
+ i.ToString();
dataRow["label_key"] = i;
dataRow["male"] = i * 100;
dataRow["male_pct_val"] = 0.30;
dataRow["male_pct"] = "30%";
AgeGenderTable.Rows.Add(dataRow);
}
return
AgeGenderTable;
}
System.Globalization.CultureInfo myCI = new
CultureInfo("en-US");
DateTime
dt_p1 = DateTime.Parse(p1, myCI);
string
mdxStr =
"WITH " +
"MEMBER
[Measures].[SumOfAG] AS " +
" '( Sum({" + EcrmUtils.getCubeDate(dt_p1) + "},
[Measures].[RunningTotalSubs]))' " +
" MEMBER [Measures].[SuperSum] AS "+
" '
(Sum({[Age Group].[13 and 17],[Age Group].[18 and 24],[Age Group].[25 and
34],[Age Group].[35 and 44],[Age Group].[45 and 54],[Age Group].[over 55],[Age
Group].[less then 13],[Age Group].[UNKNOWN] },[Measures].[SumOfAG] ))' "
+
"
MEMBER [Measures].[GMale] AS " +
"
'( Sum({(" + EcrmUtils.getCubeDate(dt_p1)
+ ",[Gender].[Male])}, [Measures].[RunningTotalSubs]))' "
+
"
MEMBER [Measures].[GMalePer] AS " +
"
' ([Measures].[GMale] / [Measures].[SuperSum])' " +
"
MEMBER [Measures].[GFemale] AS " +
"
'( Sum({(" + EcrmUtils.getCubeDate(dt_p1)
+ ",[Gender].[Female])}, [Measures].[RunningTotalSubs]))' "
+
"
MEMBER [Measures].[GFemalePer] AS " +
"
' ([Measures].[GFemale] / [Measures].[SuperSum])' " +
"
MEMBER [Measures].[GUnknown] AS " +
"
'( Sum({(" + EcrmUtils.getCubeDate(dt_p1)
+ ",[Gender].[Unknown])}, [Measures].[RunningTotalSubs]))' "
+
"
MEMBER [Measures].[GUnknownPer] AS " +
"
' ([Measures].[GUnknown] / [Measures].[SuperSum])' " +
"
select {[Measures].[SumOfAG], [Measures].[GMale], [Measures].[GMalePer],
[Measures].[GFemale], [Measures].[GFemalePer], [Measures].[GUnknown],
[Measures].[GUnknownPer] } " +
"
on Columns, { ([Age Group].children) }
on Rows from [consumer_analysis] where ([Zone Id]." + EcrmUtils.getZoneDimension(zone_id, partner_id,
ct_id, "0") + ") ";
try
{
AdomdCommand
myCommand = new AdomdCommand();
myCommand.Connection =
consumerConn;
myCommand.CommandText = mdxStr;
AdomdDataReader
reader = myCommand.ExecuteReader();
while
(reader.Read())
{
DataRow
dataRow = AgeGenderTable.NewRow();
//
for each item in the resultset, print the value
for
(int i = 0; i < reader.FieldCount; i++)
{
string
rName = reader.GetName(i).ToString();
if
(rName.Contains("[Age Group].[Age
Group].[MEMBER_CAPTION]"))
{
string data_label = reader.GetValue(i).ToString();
string dl_lower = data_label.ToLower();
int i_label_key = 100;
if (dl_lower.Equals("less
then 13"))
{
if (lang.Contains("fr"))
data_label
= "moins de 13 ans";
else
data_label = "under
13";
i_label_key =
1;
}
else if
(dl_lower.Equals("13 and 17"))
{
if (lang.Contains("fr"))
data_label
= "de 13 à 17 ans";
else
data_label
= "13 to 17";
i_label_key =
2;
}
else if
(dl_lower.Equals("18 and 24"))
{
if (lang.Contains("fr"))
data_label
= "de 18 à 24 ans";
else
data_label
= "18 to 24";
i_label_key =
3;
}
else if
(dl_lower.Equals("25 and 34"))
{
if (lang.Contains("fr"))
data_label
= "de 25 à 34 ans";
else
data_label
= "25 to 34";
i_label_key =
4;
}
else if
(dl_lower.Equals("35 and 44"))
{
if (lang.Contains("fr"))
data_label
= "de 35 à 44 ans";
else
data_label
= "35 to 44";
i_label_key =
5;
}
else if
(dl_lower.Equals("45 and 54"))
{
if (lang.Contains("fr"))
data_label
= "de 45 à 54 ans";
else
data_label
= "45 to 54";
i_label_key =
6;
}
else if
(dl_lower.Equals("over 55"))
{
if (lang.Contains("fr"))
data_label
= "plus de 55 ans";
i_label_key =
7;
}
else if
(dl_lower.Contains("unknown"))
{
data_label = "Unknown";
i_label_key =
8;
}
dataRow["label"] = data_label.ToString();
dataRow["label_key"] = i_label_key;
}
else
if (rName.Contains("[Measures].[SumOfAG]"))
{
//
double d_all_subs = Convert.ToDouble(reader.GetValue(i));
dataRow["all_gender"] = Convert.ToInt32(reader.GetValue(i));
}
else
if (rName.Contains("[Measures].[GFemale]"))
{
dataRow["female"] = Convert.ToInt32(reader.GetValue(i));
}
else
if (rName.Contains("[Measures].[GFemalePer]"))
{
double d_pct = Convert.ToDouble(reader.GetValue(i));
dataRow["female_pct"] = String.Format("{0:0.00%}", d_pct).Replace("
dataRow["female_pct_val"] = d_pct;
}
else
if (rName.Contains("[Measures].[GMale]"))
{
dataRow["male"]
= Convert.ToInt32(reader.GetValue(i));
}
else
if (rName.Contains("[Measures].[GMalePer]"))
{
double d_pct = Convert.ToDouble(reader.GetValue(i));
dataRow["male_pct"] = String.Format("{0:0.00%}", d_pct).Replace("
dataRow["male_pct_val"] = d_pct;
}
else
if (rName.Contains("[Measures].[GUnknown]"))
{
dataRow["unknown"] = Convert.ToInt32(reader.GetValue(i));
}
else
if (rName.Contains("[Measures].[GUnknownPer]"))
{
double d_pct = Convert.ToDouble(reader.GetValue(i));
dataRow["unknown_pct"] = String.Format("{0:0.00%}",
d_pct).Replace("
dataRow["unknown_pct_val"] = d_pct;
}
}
AgeGenderTable.Rows.Add(dataRow);
}
reader.Close();
return
(AgeGenderTable);
}
catch (Exception e1)
{
EcrmUtils.WriteErrorMsg(e1.ToString()
+ mdxStr);
return
null;
}
}
}