public SCCatD[] GetProductSalesPrDay()
{
using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube"))
{
conn.Open();
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT [Measures].[Fact Sale Count] ON COLUMNS,
NONEMPTY( [Dim Product].[Category].[Category]*[Dim Date].[Day].[Day]) ON ROWS
FROM [F Club DW]";
AdomdDataReader dr = cmd.ExecuteReader();
List <SCCatD> days = new List <SCCatD>();
while (dr.Read())
{
SCCatD day = new SCCatD
{
cat = dr[0].ToString(),
day = int.Parse(dr[1].ToString()),
saleCount = int.Parse(dr[2].ToString())
};
days.Add(day);
}
dr.Close();
SCCatD[] daySales = days.ToArray();
conn.Close();
return(daySales);
}
}
public void ExecuteNonQuery(string command)
{
var cmd = _adomdConn.CreateCommand();
try
{
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
/// <summary>
/// 根据mdx语句、连接字符串
/// </summary>
/// <param name="strMdx"></param>
/// <param name="ConnectionStringName"></param>
/// <returns></returns>
public DataTable GetDataByCon(string strMdx, string ConStr)
{
DataTable dt = new DataTable();
try
{
using (AdomdConnection _connection = new AdomdConnection(ConStr))
{
if (_connection != null)
{
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
AdomdCommand cmd = _connection.CreateCommand();
cmd.CommandText = strMdx;
var executexml = cmd.ExecuteXmlReader();
CellSet cellset = CellSet.LoadXml(executexml);
//_connection.GetSchemaDataSet
_connection.Close();
dt = ToDataTable(cellset);
}
}
return(dt);
}
catch (Exception ex)
{
throw ex;
}
}
public static CellSet ExecuteOLAP(string mdx, string conexion, int timeout)
{
AdomdConnection con = new AdomdConnection(conexion);
try
{
con.Open();
}
catch (Exception ex)
{
throw new Exception("Ocurrió un error al conectarse al Origen de Datos.", ex);
}
AdomdCommand command = con.CreateCommand();
command.CommandText = mdx;
command.CommandTimeout = timeout;
command.CommandType = CommandType.Text;
CellSet cs = null;
try
{
cs = command.ExecuteCellSet();
}
catch
{
throw new Exception("Ocurrió un error al Ejecutar la consulta MDX");
}
return(cs);
}
public long Execute(AdomdConnection con)
{
Stopwatch timer = new Stopwatch();
using (AdomdCommand cmd = con.CreateCommand())
{
timer.Start();
try
{
cmd.CommandText = this.ToString(MdxScriptCommandDisplayOption.Original_Trimmed);
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
if (!this.IgnoreErrors)
{
throw e;
}
}
timer.Stop();
_durationAssignment = timer.ElapsedMilliseconds;
}
return(DurationAssignment);
}
public SCMonth[] GetSalesPrMonth()
{
StringBuilder result = new StringBuilder();
using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube"))
{
conn.Open();
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT [Measures].[Fact Sale Count] ON COLUMNS,
NONEMPTY( {[Dim Date].[Month].[Month]} ) ON ROWS
FROM [F Club DW]";
CellSet cs = cmd.ExecuteCellSet();
TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;
TupleCollection tupleCollection = cs.Axes[1].Set.Tuples;
SCMonth[] monthSales = new SCMonth[tupleCollection.Count];
for (int row = 0; row < tupleCollection.Count; row++)
{
SCMonth month = new SCMonth();
month.month = int.Parse(tupleCollection[row].Members[0].Caption);
for (int col = 0; col < tuplesOnColumns.Count; col++)
{
month.saleCount = int.Parse(cs.Cells[col, row].FormattedValue);
monthSales[row] = month;
}
}
conn.Close();
return(monthSales);
}
}
public ProductCat[] GetProductsByCategory()
{
using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube"))
{
conn.Open();
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT
[Measures].[Fact Sale Count] ON COLUMNS,
{[Dim Product].[Product Id].[Product Id]*[Dim Product].[Name].[Name]*[Dim Product].[Category].[Category]*[Dim Product].[Sub Category].[Sub Category]*[Dim Product].[Sub Sub Category].[Sub Sub Category]} ON ROWS
From [F Club DW]";
AdomdDataReader dr = cmd.ExecuteReader();
List <ProductCat> productCats = new List <ProductCat>();
while (dr.Read())
{
ProductCat productCat = new ProductCat
{
productID = int.Parse(dr[0].ToString()),
name = dr[1].ToString(),
cat = dr[2].ToString(),
subCat = dr[3].ToString(),
subSubCat = dr[4].ToString(),
};
productCats.Add(productCat);
}
dr.Close();
ProductCat[] productSubCats = productCats.ToArray();
conn.Close();
return(productSubCats);
}
}
private void Button_Click_1(object sender, RoutedEventArgs e)
{
string connectionString = "Data Source=localhost;Catalog=MDX Step-by-Step;ConnectTo=11.0;Integrated Security=SSPI";
AdomdConnection _connection = new AdomdConnection(connectionString);
if (_connection != null)
{
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
}
AdomdCommand command = _connection.CreateCommand();
StringBuilder sb = new StringBuilder();
sb.Append("WITH");
sb.Append(" MEMBER [Product].[Category].[All Products].[X] AS 1+1");
sb.Append("SELECT{ ([Date].[Calendar].[CY 2002]),([Date].[Calendar].[CY 2003])}*{([Measures].[Reseller Sales Amount]) } ON COLUMNS,");
sb.Append("{ ([Product].[Category].[Accessories]),([Product].[Category].[Bikes]),([Product].[Category].[Clothing]),");
sb.Append("([Product].[Category].[Components]),([Product].[Category].[X])} ON ROWS");
sb.Append(" FROM [Step-by-Step]");
command.CommandText = sb.ToString();
var xmlreader = command.ExecuteXmlReader();
CellSet cellSet = CellSet.LoadXml(xmlreader);
_connection.Close();
var dt = ToDataTable(cellSet);
var v = dt.Rows.Count;
}
public SemesterSales[] GetSalesPrSemester()
{
using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube"))
{
conn.Open();
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT [Measures].[Fact Sale Count] ON COLUMNS,
NONEMPTY([Dim Member].[Semester].[Semester]*[Dim Product].[Sub Sub Category].[Sub Sub Category]) ON ROWS
FROM [F Club DW]";
AdomdDataReader dr = cmd.ExecuteReader();
List <SemesterSales> semesters = new List <SemesterSales>();
while (dr.Read())
{
SemesterSales semester = new SemesterSales
{
semester = dr[0].ToString(),
SubSubCategory = dr[1].ToString(),
saleCount = int.Parse(dr[2].ToString())
};
semesters.Add(semester);
}
dr.Close();
SemesterSales[] semesterSales = semesters.FindAll(FindBeer).ToArray();
conn.Close();
return(semesterSales);
}
}
static AdomdDataReader GetDaxResult(string daxExpression)
{
var command = _connection.CreateCommand();
command.CommandText = daxExpression;
return(command.ExecuteReader());
}
//</snippetOutputCommandWithDataReader>
//<snippetExecuteXMLAProcessCommand>
void ExecuteXMLAProcessCommand()
{
//Open a connection to the local server
AdomdConnection conn = new AdomdConnection("Data Source=localhost");
conn.Open();
//Create a command, and assign it an XMLA command to process the cube.
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = "<Process xmlns=\"https://schemas.microsoft.com/analysisservices/2003/engine\">\r\n" +
@"<Object>
<DatabaseID>Adventure Works DW Standard Edition</DatabaseID>
<CubeID>Adventure Works DW</CubeID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>";
//Execute the command
int result = cmd.ExecuteNonQuery();
//Close the connection
conn.Close();
}
public DataTable Adomd()
{
//连接的字符串
string conStr = "provider=msolap ;Integrated Security =SSPI ;Data Source= localhost ;Catalog =Scal B2CReport Mdx ;";
//创建个连接对象
AdomdConnection con = new AdomdConnection();
con.ConnectionString = conStr;
con.Open();
// 创建个命令
AdomdCommand cmm = con.CreateCommand();
cmm.CommandText = @"select
{[Measures].[订票数量],
[Measures].[Add Fare],[Measures].[Par Price]} on columns,
{[Sale Sta View Time].[Create Day].[2011-12-01]:[Sale Sta View Time].[Create Day].[2011-12-31]} on rows
from [SCAL3 Test]
where
{
{[Sale Sta View Generic].[Is Member].&[1]}
}";
//执行命令返回单元集合
CellSet result = cmm.ExecuteCellSet();
DataTable table = CellSetToTable(result);
con.Close();
return table;
}
static void Main(string[] args)
{
// prepare adomd connection
using (AdomdConnection mdConn = new AdomdConnection())
{
mdConn.ConnectionString = "provider=msolap;Data Source=(local);initial catalog=HabraCube;";
mdConn.Open();//new comment
AdomdCommand mdCommand = mdConn.CreateCommand();
mdCommand.CommandText = "SELECT {[Measures].[Vote], [Measures].[Votes Count]} ON COLUMNS, [Dim Time].[Month Name].MEMBERS ON ROWS FROM [Habra DW]"; // << MDX Query // work with CellSet
CellSet cs = mdCommand.ExecuteCellSet(); // our method supports only 2-Axes CellSets
if (cs.Axes.Count != 2) return;
TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;
TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples; // output column headers
Console.Write("{0,-12}", "Item");
for (int col = 0; col < tuplesOnColumns.Count; col++)
{
Console.Write("{0,-12}", tuplesOnColumns[col].Members[0].Caption);
}
Console.WriteLine(); // output rows
for (int row = 0; row < tuplesOnRows.Count; row++)
{
Console.Write("{0,-12}", tuplesOnRows[row].Members[0].Caption); // fill columns
for (int col = 0; col < tuplesOnColumns.Count; col++)
{ Console.Write("{0,-12}", cs.Cells[col, row].Value); } Console.WriteLine();
}
Console.ReadLine();
}
}
private void getBoughtToghtherMovies(String input, List <string> output)
{
String newInput = input.Trim();
AdomdConnection CON = new AdomdConnection(
"Data Source=.;Catalog=ADMF18");
CON.Open();
AdomdCommand COM = CON.CreateCommand();
string s = "SELECT Flattened PREDICT([Movies],5" + ") FROM [CustomersMM] NATURAL PREDICTION JOIN (SELECT (" + "Select '" + newInput + "' as [movie]" + ") AS [Movies]) As T";
COM.CommandText = s;
AdomdDataReader DR = COM.ExecuteReader();
while (DR.Read())
{
if (DR[0] != null)
{
output.Add(DR[0].ToString());
}
}
DR.Close();
CON.Close();
}
public CellSet Query(string query)
{
AdomdCommand cmd = cnx.CreateCommand();
cmd.CommandText = query;
return(cmd.ExecuteCellSet());
}
public string HandleRequest([FromBody] RequestData requestData)
{
String requestText = Utilities.Utilities.BuildMDXRequest(requestData);
using (AdomdConnection mdConn = new AdomdConnection())
{
mdConn.ConnectionString = "provider=msolap;Data Source=V1LGORPC\\ASMAIN;initial catalog=AdventureWorksDW2014Multidimensional-EE;";
mdConn.Open();
AdomdCommand mdCommand = mdConn.CreateCommand();
mdCommand.CommandText = requestText; // << MDX Query
CellSet cs;
try
{
// work with CellSet
cs = mdCommand.ExecuteCellSet();
}
catch (AdomdErrorResponseException e)
{
return(e.Message);
}
MdxResultDataSet resultDataSet = new MdxResultDataSet();
// our method supports only 2-Axes CellSets
if (cs.Axes.Count != 2)
{
return("Request error: axes count > 2.");
}
TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;
TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;
for (int row = 0; row < tuplesOnRows.Count; row++)
{
resultDataSet.RowNameList.Add(tuplesOnRows[row].Members[0].Caption);
}
for (int col = 0; col < tuplesOnColumns.Count; col++)
{
resultDataSet.ColumnNameList.Add(tuplesOnColumns[col].Members[0].Caption);
resultDataSet.Cells.Add(new List <String>());
for (int row = 0; row < tuplesOnRows.Count; row++)
{
if (cs.Cells[col, row].Value == null)
{
resultDataSet.Cells[col].Add("null");
}
else
{
resultDataSet.Cells[col].Add(cs.Cells[col, row].Value.ToString());
}
}
}
return(JsonConvert.SerializeObject(resultDataSet, Formatting.Indented));
//return requestText;
}
}
/// <summary>
/// Constructs command to query AAS.
/// </summary>
/// <param name="connection">Connection to AAS cluster.</param>
/// <returns>Command to execute against AAS.</returns>
private static AdomdCommand ConstructCommand(AdomdConnection connection)
{
//Create a command, using given connection
AdomdCommand command = connection.CreateCommand();
command.CommandText = query;
return(command);
}
public static void Run()
{
// open connection
// string connStr="Data Source=localhost;Initial Catalog=Foodmart 2000;";
string connStr = "Data Source=10.3.0.247;Initial Catalog=Adventure Works DW Standard Edition;";
// string connStr="Data Source=http://localhost/xmla/msxisapi.dll;Initial Catalog=Foodmart 2000;";
AdomdConnection conn = new AdomdConnection(connStr);
AdomdCommand cmd = null;
// execute mdx
string queryStr =
// @"select
// {Product.Members} on rows,
// {{Time.Members}} on columns
//from Sales
//";
@"WITH SET [Promotions_set] AS '{[Promotions].[All Promotions].[Bag Stuffers],[Promotions].[All Promotions].[Best Savings],[Promotions].[All Promotions].[Big Promo],[Promotions].[All Promotions].[Big Time Discounts],[Promotions].[All Promotions].[Big Time Savings],[Promotions].[All Promotions].[Bye Bye Baby],[Promotions].[All Promotions].[Cash Register Lottery],[Promotions].[All Promotions].[Coupon Spectacular],[Promotions].[All Promotions].[Dimes Off],[Promotions].[All Promotions].[Dollar Cutters],[Promotions].[All Promotions].[Dollar Days],[Promotions].[All Promotions].[Double Down Sale],
[Promotions].[All Promotions].[Double Your Savings],[Promotions].[All Promotions].[Fantastic Discounts],[Promotions].[All Promotions].[Free For All],[Promotions].[All Promotions].[Go For It],[Promotions].[All Promotions].[Green Light Days],[Promotions].[All Promotions].[Green Light Special],[Promotions].[All Promotions].[High Roller Savings],[Promotions].[All Promotions].[I Cant Believe It Sale],[Promotions].[All Promotions].[Money Grabbers],[Promotions].[All Promotions].[Money Savers],[Promotions].[All Promotions].[Mystery Sale],[Promotions].[All Promotions].[No Promotion],[Promotions].[All Promotions].[One Day Sale],[Promotions].[All Promotions].[Pick Your Savings],[Promotions].[All Promotions].[Price Cutters],[Promotions].[All Promotions].[Price Destroyers],[Promotions].[All Promotions].[Price Savers],[Promotions].[All Promotions].[Price Slashers],[Promotions].[All Promotions].[Price Smashers],[Promotions].[All Promotions].[Price Winners],[Promotions].[All Promotions].[Sale Winners],[Promotions].[All Promotions].[Sales Days],[Promotions].[All Promotions].[Sales Galore],[Promotions].[All Promotions].[Save-It Sale],[Promotions].[All Promotions].[Saving Days],[Promotions].[All Promotions].[Savings Galore],[Promotions].[All Promotions].[Shelf Clearing Days],
[Promotions].[All Promotions].[Shelf Emptiers],[Promotions].[All Promotions].[Super Duper Savers],[Promotions].[All Promotions].[Super Savers],[Promotions].[All Promotions].[Super Wallet Savers],[Promotions].[All Promotions].[Three for One],[Promotions].[All Promotions].[Tip Top Savings],[Promotions].[All Promotions].[Two Day Sale],[Promotions].[All Promotions].[Two for One],[Promotions].[All Promotions].[Unbeatable Price Savers],[Promotions].[All Promotions].[Wallet Savers],[Promotions].[All Promotions].[Weekend Markdown],[Promotions].[All Promotions].[You Save Days],[Promotions].[All Promotions]}'
SET [Promotions_set_wcalc] AS '{{[Promotions_set]}}' SET [Time_set] AS '{[*SET Time.1997.Children*],[*SET Time.Q1.Children*],[*SET Time.Q2.Children*],[*SET Time.Q3.Children*],[*SET Time.Q4.Children*]}' SET [*SET Time.Q4.Children*] AS '{[Time].[1997].[Q4].Children}' SET [*SET Time.Q3.Children*] AS '{[Time].[1997].[Q3].Children}' SET [*SET Time.Q2.Children*] AS '{[Time].[1997].[Q2].Children}' SET [*SET Time.Q1.Children*] AS '{[Time].[1997].[Q1].Children}' SET [*SET Time.1997.Children*] AS '{[Time].[1997].Children}' SET [Time_set_wcalc] AS '{{[Time_set]}}' SET [Store Size in SQFT_set] AS '{[*SET Store Size in SQFT.All Store Size in SQFT.Children*]}' SET [*SET Store Size in SQFT.All Store Size in SQFT.Children*] AS '{[Store Size in SQFT].[All Store Size in SQFT].Children}' SET [Store Size in SQFT_set_wcalc] AS '{{[Store Size in SQFT_set]}}' SET [Product_set] AS '{[*SET Product.Drink.Children*],[*SET Product.Food.Children*],[*SET Product.Alcoholic Beverages.Children*],[*SET Product.Beverages.Children*],[*SET Product.Dairy.Children*],[*SET Product.Baked Goods.Children*],
[*SET Product.Baking Goods.Children*],[*SET Product.Breakfast Foods.Children*],[*SET Product.Canned Foods.Children*],[*SET Product.Canned Products.Children*],[*SET Product.Deli.Children*],[*SET Product.Eggs.Children*],[*SET Product.Frozen Foods.Children*],[*SET Product.Meat.Children*],[*SET Product.Produce.Children*],[*SET Product.Seafood.Children*],[*SET Product.Snack Foods.Children*],[*SET Product.Snacks.Children*],[*SET Product.Starchy Foods.Children*],[*SET Product.Non-Consumable.Children*],[*SET Product.Carousel.Children*],[*SET Product.Checkout.Children*],[*SET Product.Health and Hygiene.Children*],[*SET Product.Household.Children*],[*SET Product.Periodicals.Children*]}' SET [*SET Product.Periodicals.Children*] AS '{[Product].[All Products].[Non-Consumable].[Periodicals].Children}' SET [*SET Product.Household.Children*] AS '{[Product].[All Products].[Non-Consumable].[Household].Children}' SET [*SET Product.Health and Hygiene.Children*] AS '{[Product].[All Products].[Non-Consumable].[Health and Hygiene].Children}' SET [*SET Product.Checkout.Children*] AS '{[Product].[All Products].[Non-Consumable].[Checkout].Children}'
SET [*SET Product.Carousel.Children*] AS '{[Product].[All Products].[Non-Consumable].[Carousel].Children}' SET [*SET Product.Non-Consumable.Children*] AS '{[Product].[All Products].[Non-Consumable].Children}' SET [*SET Product.Starchy Foods.Children*] AS '{[Product].[All Products].[Food].[Starchy Foods].Children}' SET [*SET Product.Snacks.Children*] AS '{[Product].[All Products].[Food].[Snacks].Children}' SET [*SET Product.Snack Foods.Children*] AS '{[Product].[All Products].[Food].[Snack Foods].Children}' SET [*SET Product.Seafood.Children*] AS '{[Product].[All Products].[Food].[Seafood].Children}' SET [*SET Product.Produce.Children*] AS '{[Product].[All Products].[Food].[Produce].Children}' SET [*SET Product.Meat.Children*] AS '{[Product].[All Products].[Food].[Meat].Children}' SET [*SET Product.Frozen Foods.Children*] AS '{[Product].[All Products].[Food].[Frozen Foods].Children}' SET [*SET Product.Eggs.Children*] AS '{[Product].[All Products].[Food].[Eggs].Children}' SET [*SET Product.Deli.Children*] AS '{[Product].[All Products].[Food].[Deli].Children}' SET [*SET Product.Canned Products.Children*] AS '{[Product].[All Products].[Food].[Canned Products].Children}' SET [*SET Product.Canned Foods.Children*] AS '{[Product].[All Products].[Food].[Canned Foods].Children}'
SET [*SET Product.Breakfast Foods.Children*] AS '{[Product].[All Products].[Food].[Breakfast Foods].Children}' SET [*SET Product.Baking Goods.Children*] AS '{[Product].[All Products].[Food].[Baking Goods].Children}' SET [*SET Product.Baked Goods.Children*] AS '{[Product].[All Products].[Food].[Baked Goods].Children}' SET [*SET Product.Dairy.Children*] AS '{[Product].[All Products].[Food].[Dairy].Children}' SET [*SET Product.Beverages.Children*] AS '{[Product].[All Products].[Drink].[Beverages].Children}' SET [*SET Product.Alcoholic Beverages.Children*] AS '{[Product].[All Products].[Drink].[Alcoholic Beverages].Children}' SET [*SET Product.Food.Children*] AS '{[Product].[All Products].[Food].Children}' SET [*SET Product.Drink.Children*] AS '{[Product].[All Products].[Drink].Children}'
SET [Product_set_wcalc] AS '{{[Product_set]}}' MEMBER [Promotion Media].[*AGGREGATE*] AS 'AGGREGATE({[Promotion Media].[All Media].[Bulk Mail],[Promotion Media].[All Media].[Cash Register Handout],[Promotion Media].[All Media].[Daily Paper],[Promotion Media].[All Media].[Daily Paper, Radio],[Promotion Media].[All Media].[Daily Paper, Radio, TV],[Promotion Media].[All Media].[In-Store Coupon],[Promotion Media].[All Media].[No Media],[Promotion Media].[All Media].[Product Attachment],[Promotion Media].[All Media].[Radio],[Promotion Media].[All Media].[Street Handout],[Promotion Media].[All Media].[Sunday Paper],[Promotion Media].[All Media].[Sunday Paper, Radio],[Promotion Media].[All Media].[Sunday Paper, Radio, TV],[Promotion Media].[All Media].[TV]})' , SOLVE_ORDER=-100
MEMBER [Store Type].[*AGGREGATE*] AS 'AGGREGATE({[Store Type].[All Store Type].[Deluxe Supermarket],[Store Type].[All Store Type].[Gourmet Supermarket],[Store Type].[All Store Type].[HeadQuarters],[Store Type].[All Store Type].[Mid-Size Grocery],[Store Type].[All Store Type].[Small Grocery],[Store Type].[All Store Type].[Supermarket]})' , SOLVE_ORDER=-100 MEMBER [Yearly Income].[*AGGREGATE*] AS 'AGGREGATE({[Yearly Income].[All Yearly Income].[$10K - $30K],[Yearly Income].[All Yearly Income].[$110K - $130K],[Yearly Income].[All Yearly Income].[$130K - $150K],[Yearly Income].[All Yearly Income].[$150K +],[Yearly Income].[All Yearly Income].[$30K - $50K],[Yearly Income].[All Yearly Income].[$50K - $70K],[Yearly Income].[All Yearly Income].[$70K - $90K],[Yearly Income].[All Yearly Income].[$90K - $110K]})' , SOLVE_ORDER=-100 SELECT NON EMPTY HIERARCHIZE({{[Promotions_set_wcalc]}*{[Time_set_wcalc]}*{[Store Size in SQFT_set_wcalc]}}) ON Columns, NON EMPTY HIERARCHIZE({{[Product_set_wcalc]}}) ON Rows FROM [Sales] WHERE ([Customers].[All Customers],[Education Level].[All Education Level],[Gender].[All Gender],[Marital Status].[All Marital Status],[Measures].[Unit Sales],[Promotion Media].[*AGGREGATE*],[Store].[All Stores],[Store Type].[*AGGREGATE*],[Yearly Income].[*AGGREGATE*])";
try
{
conn.Open();
cmd = conn.CreateCommand();
_cmdList.Add(cmd);
cmd.CommandText = queryStr;
CellSet cst = cmd.ExecuteCellSet();
cst = null;
}
catch (Exception exc)
{
throw exc;
}
finally
{
if (cmd != null)
{
_cmdList.Remove(cmd);
}
conn.Close();
conn.Dispose();
}
}
//</snippetDemonstrateDisconnectedCellset>
//<snippetReturnCommandUsingCellSet>
string ReturnCommandUsingCellSet()
{
//Create a new string builder to store the results
System.Text.StringBuilder result = new System.Text.StringBuilder();
//Connect to the local server
using (AdomdConnection conn = new AdomdConnection("Data Source=localhost;"))
{
conn.Open();
//Create a command, using this connection
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
WITH MEMBER [Measures].[FreightCostPerOrder] AS
[Measures].[Reseller Freight Cost]/[Measures].[Reseller Order Quantity],
FORMAT_STRING = 'Currency'
SELECT
[Geography].[Geography].[Country].&[United States].Children ON ROWS,
[Date].[Calendar].[Calendar Year] ON COLUMNS
FROM [Adventure Works]
WHERE [Measures].[FreightCostPerOrder]";
//Execute the query, returning a cellset
CellSet cs = cmd.ExecuteCellSet();
//Output the column captions from the first axis
//Note that this procedure assumes a single member exists per column.
result.Append("\t");
TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;
foreach (Tuple column in tuplesOnColumns)
{
result.Append(column.Members[0].Caption + "\t");
}
result.AppendLine();
//Output the row captions from the second axis and cell data
//Note that this procedure assumes a two-dimensional cellset
TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;
for (int row = 0; row < tuplesOnRows.Count; row++)
{
result.Append(tuplesOnRows[row].Members[0].Caption + "\t");
for (int col = 0; col < tuplesOnColumns.Count; col++)
{
result.Append(cs.Cells[col, row].FormattedValue + "\t");
}
result.AppendLine();
}
conn.Close();
return(result.ToString());
} // using connection
}
public void ExecuteNonQuery(string command)
{
var cmd = _adomdConn.CreateCommand();
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
public long ExecuteTestQuery(AdomdConnection con, string testQuery, int commandTimeout, bool executeTwice)
{
Stopwatch timer = new Stopwatch();
int iterations = 1;
if (executeTwice)
{
iterations = 2;
}
using (AdomdCommand cmd = con.CreateCommand())
{
if (commandTimeout > 0)
{
cmd.CommandTimeout = commandTimeout;
}
timer.Start();
for (int i = 1; i <= iterations; i++)
{
timer.Restart();
try
{
cmd.CommandText = testQuery;
_intermediateResults = cmd.ExecuteCellSet();
}
catch (Exception e)
{
if (!this.IgnoreErrors)
{
throw e;
}
}
timer.Stop();
if (i == 1) // first iteration is on cold cache
{
this.Duration = timer.ElapsedMilliseconds;
}
if (i == 2) // second iteration is on warm cache
{
this.DurationWarm = timer.ElapsedMilliseconds;
}
}
}
return(this.Duration);
}
public override IDictionary <string, string> Execute(ProcessAzureASContext context, IActivityLogger logger)
{
logger.Write("Starting ProcessAzureASActivity");
if (string.IsNullOrEmpty(context.AdvancedASProcessingScriptPath))
{
logger.Write("No custom TMSL script specified, process perform full process of the database");
try
{
Model tabularModel = GetTabularModel(context.AzureASConnectionString, context.TabularDatabaseName);
ProcessTabularModel(tabularModel, logger);
logger.Write("Finalizing ProcessAzureASActivity");
}
catch (Exception ex)
{
logger.Write(ex.Message);
throw;
}
}
else
{
logger.Write("Custom TMSL script specified, perform action defined in TMSL script");
try
{
using (AdomdConnection asConn = new AdomdConnection(context.AzureASConnectionString))
{
asConn.Open();
foreach (string scriptPath in context.AdvancedASProcessingScriptPath.Split(';'))
{
string commandText = ReadBlob(context.BlobStorageConnectionString, scriptPath);
AdomdCommand asCmd = asConn.CreateCommand();
asCmd.CommandText = commandText;
asCmd.ExecuteNonQuery();
logger.Write("Azure AS was successfully processed");
}
}
}
catch (Exception ex)
{
logger.Write(ex.Message);
throw;
}
}
return(new Dictionary <string, string>());
}
public static void ExecuteForPrepare(string connectionString, string commandText)
{
using (var connection = new AdomdConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandTimeout = DefaultTimeout;
command.CommandType = CommandType.Text;
command.CommandText = commandText;
command.Prepare();
}
}
}
public AdomdDataReader GetDaxResult(string dax, List <AdomdParameter> daxParameters = null)
{
var command = _connection.CreateCommand();
command.CommandText = dax;
if (daxParameters != null)
{
foreach (var p in daxParameters)
{
command.Parameters.Add(new AdomdParameter(p.ParameterName, p.Value));
}
}
return(command.ExecuteReader());
}
public List <string> predict(List <string> input)
{
List <string> output = new List <string>();
//Connection
AdomdConnection CON = new AdomdConnection(
"Data Source=.;Catalog=AnalysisDB");
CON.Open();
// command
AdomdCommand COM = CON.CreateCommand();
// Query Command to Retrive Result
string s = @"SELECT Flattened PREDICT([Orders Details],3)
FROM [OrdersMM]
NATURAL PREDICTION JOIN
(SELECT ( ";
foreach (string x in input)
{
if (input.IndexOf(x) > 0)
{
s += " Union ";
}
s += "Select '" + x + "' as [Product Name]";
}
s += " ) AS [Orders Details]) As T";
COM.CommandText = s;
// Read result
AdomdDataReader DR = COM.ExecuteReader();
while (DR.Read())
{
if (DR[0] != null)
{
output.Add(DR[0].ToString());
}
}
DR.Close();
CON.Close();
return(output);
}
/// <summary>
/// Returns a dictionary of row label to value retrieved from a query (command) applied to a cube (CubeInfo).
/// </summary>
/// <param name="cubeInfo"></param>
/// <param name="command"></param>
/// <returns></returns>
public Dictionary <string, double> GetSlicedMeasureData(CubeInfo cubeInfo, string command)
{
Dictionary <string, double> datalist = new Dictionary <string, double>();
string connection = cubeInfo.getStandardConnectionString();
try
{
using (AdomdConnection conn = new AdomdConnection(connection))
{
conn.Open();
using (AdomdCommand cmd = conn.CreateCommand())
{
cmd.CommandText = command;
using (AdomdDataReader result = cmd.ExecuteReader())
{
while (result.Read())
{
string label = "";
if (!result.IsDBNull(0))
{
label = result[0].ToString();
}
double value = 0;
if (!result.IsDBNull(2))
{
value = result.GetDouble(2);
}
datalist.Add(label, value);
}
}
}
conn.Close();
}
}
catch (AdomdErrorResponseException aere)
{
Utility.WriteToConsole(string.Format(Constants.invalid, aere.Message), 100);
}
return(datalist);
}
public static void ExecuteXMLA(AdomdConnection con, string xmla, bool ignoreError)
{
try
{
using (AdomdCommand cmd = con.CreateCommand())
{
cmd.CommandText = xmla;
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
if (!ignoreError)
{
throw e;
}
}
}
public Stream GetResults(string session, string sequence)
{
CellSet cellSet;
using (AdomdConnection connection = new AdomdConnection(connectionString))
{
connection.Open();
AdomdCommand command = connection.CreateCommand();
command.CommandText = mdx;
cellSet = command.ExecuteCellSet();
}
string result = JsonConvert.SerializeObject(cellSet, new CellSetConverter());
WebOperationContext.Current.OutgoingResponse.ContentType = JsonMimeType;
Encoding encoding = Encoding.UTF8;
byte[] bytes = encoding.GetBytes(result);
return(new MemoryStream(bytes));
}
static void Main()
{
// Import MDX query from file
var query = new QueryImporter("../../../../LearningMDX.mdx");
string mdxQuery1 = query.FromFile("Query2");
using (AdomdConnection conn = new AdomdConnection(connectionString))
{
conn.Open();
using (AdomdCommand command = conn.CreateCommand())
{
command.CommandText = mdxQuery1;
CellSet cellSet1 = command.ExecuteCellSet();
}
}
}
static void Main()
{
// Import MDX query from file
var query = new QueryImporter("../../../../LearningMDX.mdx");
string mdxQuery1 = query.FromFile("Query2");
using (AdomdConnection conn = new AdomdConnection(connectionString))
{
conn.Open();
using (AdomdCommand command = conn.CreateCommand())
{
command.CommandText = mdxQuery1;
CellSet cellSet1 = command.ExecuteCellSet();
}
}
}
private void getFilterData()
{
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
select {[Люди].[Имя сотрудника].Members} on rows,
{[Проекты].[Название проекта]} on columns
from [Часы работы];";
CellSet cs = cmd.ExecuteCellSet();
listEmployees = new List <string>();
TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;
foreach (Microsoft.AnalysisServices.AdomdClient.Tuple row in tuplesOnRows)
{
listEmployees.Add(row.Members[0].Caption);
}
}
/// <summary>
/// Gets the command.
/// </summary>
/// <param name="conn">The connection.</param>
/// <param name="commandText">The command text.</param>
/// <param name="parameters">The parameters.</param>
/// <returns>The command.</returns>
private static AdomdCommand GetCommand(AdomdConnection connection, string commandText, params AdomdParameter[] parameters)
{
var cmd = connection.CreateCommand();
cmd.CommandText = commandText;
// TODO: read from configuration
cmd.CommandTimeout = 0;
if (parameters != null)
{
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
return(cmd);
}
public static DataSet ExecuteDataSet(string connectionString, string strMdx)
{
using (Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = new AdomdConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
using (AdomdCommand command = conn.CreateCommand())
{
command.CommandText = strMdx;
command.CommandTimeout = 60;
CellSet cellSet = command.ExecuteCellSet();
System.Data.DataTable table = CellSetToDataTable(cellSet);
System.Data.DataSet ds = new DataSet();
ds.Tables.Add(table);
return(ds);
}
}
}
private string ClearCubeCache()
{
string cacheCleared = String.Empty; // an empty string will be returned if the method executes successfully, else the exception text will be returned for display
AdomdConnection cubeConnection = new AdomdConnection();
try
{
cubeConnection.ConnectionString = ConfigurationManager.ConnectionStrings["GrReportingCube"].ConnectionString;
cubeConnection.Open();
AdomdCommand cubeCommand = cubeConnection.CreateCommand();
cubeCommand.CommandType = CommandType.Text;
cubeCommand.CommandText = String.Format(@"<Batch xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">
<ClearCache>
<Object>
<DatabaseID>{0}</DatabaseID>
</Object>
</ClearCache>
</Batch>", cubeConnection.Database);
cubeCommand.Execute();
}
catch (Exception exception)
{
cacheCleared = exception.ToString();
}
finally
{
if (cubeConnection.State == ConnectionState.Open)
{
cubeConnection.Close();
}
cubeConnection.Dispose();
}
return cacheCleared;
}
/// <summary>
/// Creates a DBCommand that you can use for loving your database.
/// </summary>
AdomdCommand CreateCommand(string sql, AdomdConnection conn, params object[] args)
{
var result = conn.CreateCommand();
//result.Connection = conn;
result.CommandText = sql;
if (args.Length > 0)
{
result.AddParams(args);
}
return result;
}
public static DataTable RunMDXWithDataTable(String mdx)
{
try
{
AdomdConnection conn = new AdomdConnection(ConfigurationManager.ConnectionStrings["Cube"].ConnectionString);
conn.Open();
AdomdCommand command = conn.CreateCommand();
command.CommandText = mdx;
command.CommandType = CommandType.Text;
//AdomdDataReader reader = command.ExecuteReader();
CellSet cs = command.ExecuteCellSet();
conn.Close();
return GetDataTableFromCellSet(cs);
}
catch (Exception ex)
{
SystemHelper.LogEntry("Fail in DbHelper.cs\\RunMDX(): " + ex.ToString() + "\n");
throw ex;
}
}
public static void DeployScript(ProjectItem projItem, DTE2 ApplicationObject)
{
Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;
try
{
//validate the script because deploying an invalid script makes cube unusable
Microsoft.AnalysisServices.Design.Scripts script = new Microsoft.AnalysisServices.Design.Scripts(oCube);
}
catch (Microsoft.AnalysisServices.Design.ScriptParsingFailed ex)
{
string throwaway = ex.Message;
MessageBox.Show("MDX Script in " + oCube.Name + " is not valid.", "Problem Deploying MDX Script");
return;
}
if (oCube.MdxScripts.Count == 0)
{
MessageBox.Show("There is no MDX script defined in this cube yet.");
return;
}
try
{
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 1, 5);
// Check if the file is read-only (and probably checked in to a source control system)
// before attempting to save. (issue: 10327 )
FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1));
if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly )
{
//TODO - can I check and maybe prompt before saving?
//Save the cube
projItem.Save("");
}
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 2, 5);
// extract deployment information
DeploymentSettings deploySet = new DeploymentSettings(projItem);
// use xlst to create xmla alter command
XslCompiledTransform xslt = new XslCompiledTransform();
XmlReader xsltRdr;
XmlReader xrdr;
// read xslt from embedded resource
xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployMdxScript));
using ((xsltRdr))
{
// read content from .cube file
xrdr = XmlReader.Create(projItem.get_FileNames(1));
using (xrdr)
{
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 3, 5);
// Connect to Analysis Services
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(deploySet.TargetServer);
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 4, 5);
// execute the xmla
try
{
Microsoft.AnalysisServices.Scripter scr = new Microsoft.AnalysisServices.Scripter();
// Build up the Alter MdxScript command using XSLT against the .cube file
XslCompiledTransform xslta = new XslCompiledTransform();
StringBuilder sb = new StringBuilder();
XmlWriterSettings xws = new XmlWriterSettings();
xws.OmitXmlDeclaration = true;
xws.ConformanceLevel = ConformanceLevel.Fragment;
XmlWriter xwrtr = XmlWriter.Create(sb, xws);
xslta.Load(xsltRdr);
XsltArgumentList xslarg = new XsltArgumentList();
Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
if (targetDB == null)
{
throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
}
string targetDatabaseID = targetDB.ID;
xslarg.AddParam("TargetDatabase", "", targetDatabaseID);
xslta.Transform(xrdr, xslarg, xwrtr);
// Extract the current script from the server and keep a temporary backup copy of it
StringBuilder sbBackup = new StringBuilder();
XmlWriterSettings xwSet = new XmlWriterSettings();
xwSet.ConformanceLevel = ConformanceLevel.Fragment;
xwSet.OmitXmlDeclaration = true;
xwSet.Indent = true;
XmlWriter xwScript = XmlWriter.Create(sbBackup,xwSet);
Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
if (oServerCube == null)
{
throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
}
else if (oServerCube.State == AnalysisState.Unprocessed)
{
throw new System.Exception(string.Format("The {0} cube is not processed the {1} server.", oCube.Name, deploySet.TargetServer));
}
if (oServerCube.MdxScripts.Count == 0)
{
scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { oServerCube }, xwScript, true);
}
else
{
MdxScript mdxScr = oServerCube.MdxScripts[0];
scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { mdxScr }, xwScript, true);
}
xwScript.Close();
// update the MDX Script
XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
if (xmlaRC.Count == 1 && xmlaRC[0].Messages.Count == 0)
{
// all OK - 1 result - no messages
}
else
{
StringBuilder sbErr = new StringBuilder();
for (int iRC = 0; iRC < xmlaRC.Count;iRC ++)
{
for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
{
sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
}
}
MessageBox.Show(sbErr.ToString(),"BIDSHelper - Deploy MDX Script" );
}
// Test the MDX Script
AdomdConnection cn = new AdomdConnection("Data Source=" + deploySet.TargetServer + ";Initial Catalog=" + deploySet.TargetDatabase);
cn.Open();
AdomdCommand cmd = cn.CreateCommand();
string qry = "SELECT {} ON 0 FROM [" + oCube.Name +"];";
cmd.CommandText = qry;
try
{
// test that we can query the cube without errors
cmd.Execute();
// Building the project means that the .asdatabase file gets re-built so that
// we do not break the Deployment Wizard.
// --
// This line is included in this try block so that it is only executed if we can
// successfully query the cube without errors.
projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
}
catch (System.Exception ex)
{
// undo the deployment if we caught an exception during the deployment
svr.Execute(sbBackup.ToString());
MessageBox.Show(ex.Message);
}
}
catch (System.Exception ex)
{
if (MessageBox.Show("The following error occured while trying to deploy the MDX Script\r\n"
+ ex.Message
+ "\r\n\r\nDo you want to see a stack trace?"
,"BIDSHelper - Deploy MDX Script"
, MessageBoxButtons.YesNo
, MessageBoxIcon.Error
, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
MessageBox.Show(ex.StackTrace);
}
}
finally
{
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 5, 5);
// report any results back (status bar?)
svr.Disconnect();
}
}
}
}
finally
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(false, "Deploying MdxScript", 5, 5);
}
}
public static void Run()
{
// open connection
// string connStr="Data Source=localhost;Initial Catalog=Foodmart 2000;";
string connStr="Data Source=10.3.0.247;Initial Catalog=Adventure Works DW Standard Edition;";
// string connStr="Data Source=http://localhost/xmla/msxisapi.dll;Initial Catalog=Foodmart 2000;";
AdomdConnection conn = new AdomdConnection(connStr);
AdomdCommand cmd=null;
// execute mdx
string queryStr=
// @"select
// {Product.Members} on rows,
// {{Time.Members}} on columns
//from Sales
//";
@"WITH SET [Promotions_set] AS '{[Promotions].[All Promotions].[Bag Stuffers],[Promotions].[All Promotions].[Best Savings],[Promotions].[All Promotions].[Big Promo],[Promotions].[All Promotions].[Big Time Discounts],[Promotions].[All Promotions].[Big Time Savings],[Promotions].[All Promotions].[Bye Bye Baby],[Promotions].[All Promotions].[Cash Register Lottery],[Promotions].[All Promotions].[Coupon Spectacular],[Promotions].[All Promotions].[Dimes Off],[Promotions].[All Promotions].[Dollar Cutters],[Promotions].[All Promotions].[Dollar Days],[Promotions].[All Promotions].[Double Down Sale],
[Promotions].[All Promotions].[Double Your Savings],[Promotions].[All Promotions].[Fantastic Discounts],[Promotions].[All Promotions].[Free For All],[Promotions].[All Promotions].[Go For It],[Promotions].[All Promotions].[Green Light Days],[Promotions].[All Promotions].[Green Light Special],[Promotions].[All Promotions].[High Roller Savings],[Promotions].[All Promotions].[I Cant Believe It Sale],[Promotions].[All Promotions].[Money Grabbers],[Promotions].[All Promotions].[Money Savers],[Promotions].[All Promotions].[Mystery Sale],[Promotions].[All Promotions].[No Promotion],[Promotions].[All Promotions].[One Day Sale],[Promotions].[All Promotions].[Pick Your Savings],[Promotions].[All Promotions].[Price Cutters],[Promotions].[All Promotions].[Price Destroyers],[Promotions].[All Promotions].[Price Savers],[Promotions].[All Promotions].[Price Slashers],[Promotions].[All Promotions].[Price Smashers],[Promotions].[All Promotions].[Price Winners],[Promotions].[All Promotions].[Sale Winners],[Promotions].[All Promotions].[Sales Days],[Promotions].[All Promotions].[Sales Galore],[Promotions].[All Promotions].[Save-It Sale],[Promotions].[All Promotions].[Saving Days],[Promotions].[All Promotions].[Savings Galore],[Promotions].[All Promotions].[Shelf Clearing Days],
[Promotions].[All Promotions].[Shelf Emptiers],[Promotions].[All Promotions].[Super Duper Savers],[Promotions].[All Promotions].[Super Savers],[Promotions].[All Promotions].[Super Wallet Savers],[Promotions].[All Promotions].[Three for One],[Promotions].[All Promotions].[Tip Top Savings],[Promotions].[All Promotions].[Two Day Sale],[Promotions].[All Promotions].[Two for One],[Promotions].[All Promotions].[Unbeatable Price Savers],[Promotions].[All Promotions].[Wallet Savers],[Promotions].[All Promotions].[Weekend Markdown],[Promotions].[All Promotions].[You Save Days],[Promotions].[All Promotions]}'
SET [Promotions_set_wcalc] AS '{{[Promotions_set]}}' SET [Time_set] AS '{[*SET Time.1997.Children*],[*SET Time.Q1.Children*],[*SET Time.Q2.Children*],[*SET Time.Q3.Children*],[*SET Time.Q4.Children*]}' SET [*SET Time.Q4.Children*] AS '{[Time].[1997].[Q4].Children}' SET [*SET Time.Q3.Children*] AS '{[Time].[1997].[Q3].Children}' SET [*SET Time.Q2.Children*] AS '{[Time].[1997].[Q2].Children}' SET [*SET Time.Q1.Children*] AS '{[Time].[1997].[Q1].Children}' SET [*SET Time.1997.Children*] AS '{[Time].[1997].Children}' SET [Time_set_wcalc] AS '{{[Time_set]}}' SET [Store Size in SQFT_set] AS '{[*SET Store Size in SQFT.All Store Size in SQFT.Children*]}' SET [*SET Store Size in SQFT.All Store Size in SQFT.Children*] AS '{[Store Size in SQFT].[All Store Size in SQFT].Children}' SET [Store Size in SQFT_set_wcalc] AS '{{[Store Size in SQFT_set]}}' SET [Product_set] AS '{[*SET Product.Drink.Children*],[*SET Product.Food.Children*],[*SET Product.Alcoholic Beverages.Children*],[*SET Product.Beverages.Children*],[*SET Product.Dairy.Children*],[*SET Product.Baked Goods.Children*],
[*SET Product.Baking Goods.Children*],[*SET Product.Breakfast Foods.Children*],[*SET Product.Canned Foods.Children*],[*SET Product.Canned Products.Children*],[*SET Product.Deli.Children*],[*SET Product.Eggs.Children*],[*SET Product.Frozen Foods.Children*],[*SET Product.Meat.Children*],[*SET Product.Produce.Children*],[*SET Product.Seafood.Children*],[*SET Product.Snack Foods.Children*],[*SET Product.Snacks.Children*],[*SET Product.Starchy Foods.Children*],[*SET Product.Non-Consumable.Children*],[*SET Product.Carousel.Children*],[*SET Product.Checkout.Children*],[*SET Product.Health and Hygiene.Children*],[*SET Product.Household.Children*],[*SET Product.Periodicals.Children*]}' SET [*SET Product.Periodicals.Children*] AS '{[Product].[All Products].[Non-Consumable].[Periodicals].Children}' SET [*SET Product.Household.Children*] AS '{[Product].[All Products].[Non-Consumable].[Household].Children}' SET [*SET Product.Health and Hygiene.Children*] AS '{[Product].[All Products].[Non-Consumable].[Health and Hygiene].Children}' SET [*SET Product.Checkout.Children*] AS '{[Product].[All Products].[Non-Consumable].[Checkout].Children}'
SET [*SET Product.Carousel.Children*] AS '{[Product].[All Products].[Non-Consumable].[Carousel].Children}' SET [*SET Product.Non-Consumable.Children*] AS '{[Product].[All Products].[Non-Consumable].Children}' SET [*SET Product.Starchy Foods.Children*] AS '{[Product].[All Products].[Food].[Starchy Foods].Children}' SET [*SET Product.Snacks.Children*] AS '{[Product].[All Products].[Food].[Snacks].Children}' SET [*SET Product.Snack Foods.Children*] AS '{[Product].[All Products].[Food].[Snack Foods].Children}' SET [*SET Product.Seafood.Children*] AS '{[Product].[All Products].[Food].[Seafood].Children}' SET [*SET Product.Produce.Children*] AS '{[Product].[All Products].[Food].[Produce].Children}' SET [*SET Product.Meat.Children*] AS '{[Product].[All Products].[Food].[Meat].Children}' SET [*SET Product.Frozen Foods.Children*] AS '{[Product].[All Products].[Food].[Frozen Foods].Children}' SET [*SET Product.Eggs.Children*] AS '{[Product].[All Products].[Food].[Eggs].Children}' SET [*SET Product.Deli.Children*] AS '{[Product].[All Products].[Food].[Deli].Children}' SET [*SET Product.Canned Products.Children*] AS '{[Product].[All Products].[Food].[Canned Products].Children}' SET [*SET Product.Canned Foods.Children*] AS '{[Product].[All Products].[Food].[Canned Foods].Children}'
SET [*SET Product.Breakfast Foods.Children*] AS '{[Product].[All Products].[Food].[Breakfast Foods].Children}' SET [*SET Product.Baking Goods.Children*] AS '{[Product].[All Products].[Food].[Baking Goods].Children}' SET [*SET Product.Baked Goods.Children*] AS '{[Product].[All Products].[Food].[Baked Goods].Children}' SET [*SET Product.Dairy.Children*] AS '{[Product].[All Products].[Food].[Dairy].Children}' SET [*SET Product.Beverages.Children*] AS '{[Product].[All Products].[Drink].[Beverages].Children}' SET [*SET Product.Alcoholic Beverages.Children*] AS '{[Product].[All Products].[Drink].[Alcoholic Beverages].Children}' SET [*SET Product.Food.Children*] AS '{[Product].[All Products].[Food].Children}' SET [*SET Product.Drink.Children*] AS '{[Product].[All Products].[Drink].Children}'
SET [Product_set_wcalc] AS '{{[Product_set]}}' MEMBER [Promotion Media].[*AGGREGATE*] AS 'AGGREGATE({[Promotion Media].[All Media].[Bulk Mail],[Promotion Media].[All Media].[Cash Register Handout],[Promotion Media].[All Media].[Daily Paper],[Promotion Media].[All Media].[Daily Paper, Radio],[Promotion Media].[All Media].[Daily Paper, Radio, TV],[Promotion Media].[All Media].[In-Store Coupon],[Promotion Media].[All Media].[No Media],[Promotion Media].[All Media].[Product Attachment],[Promotion Media].[All Media].[Radio],[Promotion Media].[All Media].[Street Handout],[Promotion Media].[All Media].[Sunday Paper],[Promotion Media].[All Media].[Sunday Paper, Radio],[Promotion Media].[All Media].[Sunday Paper, Radio, TV],[Promotion Media].[All Media].[TV]})' , SOLVE_ORDER=-100
MEMBER [Store Type].[*AGGREGATE*] AS 'AGGREGATE({[Store Type].[All Store Type].[Deluxe Supermarket],[Store Type].[All Store Type].[Gourmet Supermarket],[Store Type].[All Store Type].[HeadQuarters],[Store Type].[All Store Type].[Mid-Size Grocery],[Store Type].[All Store Type].[Small Grocery],[Store Type].[All Store Type].[Supermarket]})' , SOLVE_ORDER=-100 MEMBER [Yearly Income].[*AGGREGATE*] AS 'AGGREGATE({[Yearly Income].[All Yearly Income].[$10K - $30K],[Yearly Income].[All Yearly Income].[$110K - $130K],[Yearly Income].[All Yearly Income].[$130K - $150K],[Yearly Income].[All Yearly Income].[$150K +],[Yearly Income].[All Yearly Income].[$30K - $50K],[Yearly Income].[All Yearly Income].[$50K - $70K],[Yearly Income].[All Yearly Income].[$70K - $90K],[Yearly Income].[All Yearly Income].[$90K - $110K]})' , SOLVE_ORDER=-100 SELECT NON EMPTY HIERARCHIZE({{[Promotions_set_wcalc]}*{[Time_set_wcalc]}*{[Store Size in SQFT_set_wcalc]}}) ON Columns, NON EMPTY HIERARCHIZE({{[Product_set_wcalc]}}) ON Rows FROM [Sales] WHERE ([Customers].[All Customers],[Education Level].[All Education Level],[Gender].[All Gender],[Marital Status].[All Marital Status],[Measures].[Unit Sales],[Promotion Media].[*AGGREGATE*],[Store].[All Stores],[Store Type].[*AGGREGATE*],[Yearly Income].[*AGGREGATE*])";
try
{
conn.Open();
cmd=conn.CreateCommand();
_cmdList.Add(cmd);
cmd.CommandText=queryStr;
CellSet cst=cmd.ExecuteCellSet();
cst=null;
}
catch(Exception exc)
{
throw exc;
}
finally
{
if(cmd!=null)
_cmdList.Remove(cmd);
conn.Close();
conn.Dispose();
}
}