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(); } } }
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(); } }