public static DataSet Create(ADOTabularConnection cnn) { DataSet result = new DataSet(); var db = GetAmoDatabase(cnn); AddDatabaseTable(db, result); foreach (var qry in GetQueries()) { // skip over this query if the compatibility level is not supported for the current database if (qry.MinCompatibilityLevel > db.CompatibilityLevel) { continue; } if (qry.MaxCompatibilityLevel < db.CompatibilityLevel) { continue; } System.Diagnostics.Debug.WriteLine(String.Format("Processing Table - {0}", qry.TableName)); var dt = cnn.ExecuteDaxQueryDataTable(qry.Query); dt.TableName = qry.TableName + "." + qry.MinCompatibilityLevel.ToString(); result.Tables.Add(dt); } // replace some of the lookups between tables PostProcessColumCardinality(result); PostProcessTables(result); PostProcessUnusedColumns(db, result); AddRelationshipsTable(db, result); return(result); }
static void Main(string[] args) { ADOTabularConnection srcConx = new ADOTabularConnection("Data Source=ntedatsgv77", ADOTabular.Enums.AdomdType.AnalysisServices); srcConx.ChangeDatabase("R12 FRR Summary In-Memory"); System.Data.DataTable dataTable = srcConx.ExecuteDaxQueryDataTable("EVALUATE 'D Time'"); Console.WriteLine(dataTable.Rows.Count); Console.ReadLine(); }
public static DataSet Create(ADOTabularConnection cnn) { DataSet result = new DataSet(); Dictionary <string, Exception> _processingExceptions = new Dictionary <string, Exception>(); var db = GetAmoDatabase(cnn); AddDatabaseTable(db, result); foreach (var qry in GetQueries()) { // skip over this query if the compatibility level is not supported for the current database if (qry.MinCompatibilityLevel > db.CompatibilityLevel) { continue; } if (qry.MaxCompatibilityLevel < db.CompatibilityLevel) { continue; } try { System.Diagnostics.Debug.WriteLine(String.Format("Processing Table - {0}", qry.TableName)); var dt = cnn.ExecuteDaxQueryDataTable(qry.Query); dt.TableName = qry.TableName + "." + qry.MinCompatibilityLevel.ToString(); result.Tables.Add(dt); } catch (Exception ex) { _processingExceptions.Add("Executing Query: " + qry.TableName, ex); } } if (_processingExceptions.Count != 0) { var msg = string.Join("/n", _processingExceptions.Keys.ToArray()); var aggEx = new AggregateException("ModelAnalyzerException: " + msg, _processingExceptions.Values.ToArray()); throw aggEx; } // replace some of the lookups between tables PostProcessColumnMetadata(result); PostProcessMeasureExpressions(result); PostProcessColumCardinality(result); PostProcessTables(result); PostProcessUnusedColumns(db, result); AddRelationshipsTable(db, result); return(result); }
private static DataTable RunQuery(string server, string model, string query) { ADOTabularConnection srcConx = null; String srcErrorMsg = "", status = "PASS"; DataTable srcDT = new DataTable(); DataTable ret = new DataTable(); try { try { status = "PASS"; srcConx = new ADOTabularConnection(server, ADOTabular.Enums.AdomdType.AnalysisServices); srcConx.ChangeDatabase(model); srcDT = srcConx.ExecuteDaxQueryDataTable(query); //recordCount = srcDT.Rows.Count; //SPID = srcConx.SPID; //Console.WriteLine("Session:" + srcConx.SPID); } catch (Exception ee) { Console.WriteLine(ee.Message); Console.WriteLine(ee.StackTrace); srcErrorMsg = ee.Message; } finally { //srcEndTime = DateTime.Now; srcConx.Close(); } } catch (Exception e) { Console.WriteLine(e.Message); Console.WriteLine(e.StackTrace); status = "FAILED"; } finally { srcConx.Close(); //utils.getStatusRow(overallStatusDT, testName, status, srcStartTime, srcEndTime, (double)srcEndTime.Subtract(srcStartTime).TotalMilliseconds, // recordCount, srcErrorMsg, srcQuery, SPID); } return(srcDT); }
private static void RunQuery(string server, string model, string query, string testName) { DateTime srcStartTime = DateTime.Now, srcEndTime = DateTime.Now; ADOTabularConnection srcConx = null; String srcErrorMsg = "", status = null; DataTable srcDT = new DataTable(); int recordCount = 0; int SPID = 0; try { try { status = "PASS"; srcConx = new ADOTabularConnection(server, ADOTabular.Enums.AdomdType.AnalysisServices); srcConx.ChangeDatabase(model); srcDT = srcConx.ExecuteDaxQueryDataTable(query); recordCount = srcDT.Rows.Count; SPID = srcConx.SPID; srcEndTime = DateTime.Now; Console.WriteLine("Test Name:[" + testName + "] | Start:[" + srcStartTime + "] | End:[" + srcEndTime + "] | Total Time:[" + (srcEndTime - srcStartTime).TotalSeconds.ToString() + "]"); } catch (Exception ee) { Console.WriteLine(ee.Message); Console.WriteLine(ee.StackTrace); srcErrorMsg = ee.Message; } finally { srcConx.Close(); } } catch (Exception e) { Console.WriteLine(e.Message); Console.WriteLine(e.StackTrace); status = "FAILED"; } finally { srcConx.Close(); } }
static void Main(string[] args) { Parser.Default.ParseArguments <Options>(args) .WithParsed <Options>(o => { Utils utils = new Utils(); DataTable excel = utils.ImportExceltoDatatable(o.inExcel, "Queries"); DirectoryInfo od = new DirectoryInfo(o.outDir); od.Create(); DataTable dtSummary = new DataTable(); dtSummary.Columns.Add(new DataColumn("SSASModel", typeof(String))); dtSummary.Columns.Add(new DataColumn("TableName", typeof(String))); dtSummary.Columns.Add(new DataColumn("TestResults", typeof(String))); dtSummary.Columns.Add(new DataColumn("TDQueryStartTime", typeof(DateTime))); dtSummary.Columns.Add(new DataColumn("TDQueryEndTime", typeof(DateTime))); dtSummary.Columns.Add(new DataColumn("TDQueryExecutionTime(minutes)", typeof(System.Double))); dtSummary.Columns.Add(new DataColumn("SSASQueryStartTime", typeof(DateTime))); dtSummary.Columns.Add(new DataColumn("SSASQueryEndTime", typeof(DateTime))); dtSummary.Columns.Add(new DataColumn("SSASQueryExecutionTime(minutes)", typeof(System.Double))); dtSummary.Columns.Add(new DataColumn("Exception", typeof(String))); dtSummary.Columns.Add(new DataColumn("TDQuery", typeof(String))); dtSummary.Columns.Add(new DataColumn("DAX", typeof(String))); foreach (DataRow row in excel.Rows) { string ssasModel = row["SSAS_MODEL"].ToString(); string tableName = row["TABLE_NAME"].ToString(); string tdQuery = row["TD_QUERY"].ToString(); string daxQuery = row["DAX"].ToString(); DateTime tdStartTime = DateTime.Now, tdEndTime = DateTime.Now, SSASStartTime = DateTime.Now, SSASEndTime = DateTime.Now; if (tableName.ToString().Equals("")) { continue; } DataRow drSummary = dtSummary.NewRow(); drSummary["SSASModel"] = ssasModel; drSummary["TableName"] = tableName; drSummary["TDQuery"] = tdQuery; drSummary["DAX"] = daxQuery; drSummary["TestResults"] = "Failed"; Console.WriteLine("---------------------------------------------------------------"); Console.WriteLine(ssasModel + " -> " + tableName); using (var workbook = new XLWorkbook()) { try { Console.WriteLine(tdQuery); TdConnection cn = new TdConnection(o.Server); cn.Open(); TdCommand cmd = null; TdDataAdapter adapter = null; DataTable dtt = new DataTable(); tdStartTime = DateTime.Now; try { cmd = new TdCommand(tdQuery, cn); cmd.CommandTimeout = 1200; adapter = new TdDataAdapter(cmd); tdStartTime = DateTime.Now; adapter.Fill(dtt); tdEndTime = DateTime.Now; workbook.Worksheets.Add(dtt, "TD"); cmd.Connection.Close(); cmd.Dispose(); adapter.Dispose(); } catch (Exception e) { tdEndTime = DateTime.Now; cmd.Connection.Close(); cmd.Dispose(); adapter.Dispose(); throw e; } drSummary["TDQueryStartTime"] = tdStartTime; drSummary["TDQueryEndTime"] = tdEndTime; drSummary["TDQueryExecutionTime(minutes)"] = (double)tdEndTime.Subtract(tdStartTime).Seconds / 60; //ADOTabularConnection conx = new ADOTabularConnection(@"Data Source = .; Catalog = AW;", AdomdType.AnalysisServices); System.Data.DataTable dt = new DataTable(); ADOTabularConnection conx = null; try { conx = new ADOTabularConnection(o.SSAS, ADOTabular.Enums.AdomdType.AnalysisServices); if (!ssasModel.Trim().Equals("")) { conx.ChangeDatabase(ssasModel); } Console.WriteLine("--------------------DAX---------------------------"); Console.WriteLine(daxQuery); SSASStartTime = DateTime.Now; drSummary["SSASQueryStartTime"] = SSASStartTime; dt = conx.ExecuteDaxQueryDataTable(daxQuery); SSASEndTime = DateTime.Now; conx.Close(); conx.Dispose(); } catch (Exception e) { SSASEndTime = DateTime.Now; conx.Close(); conx.Dispose(); throw e; } drSummary["SSASQueryEndTime"] = SSASEndTime; drSummary["SSASQueryExecutionTime(minutes)"] = (double)SSASEndTime.Subtract(SSASStartTime).Seconds / 60; int noOfRows = dt.Rows.Count; if (noOfRows > MAX_ROWS) { dt = dt.Rows.Cast <DataRow>().Take(MAX_ROWS).CopyToDataTable(); } workbook.Worksheets.Add(dt, "SSAS"); DataTable ret = utils.getDifferentRecords(dtt, dt); DataTable dtOutput = new DataTable(); dtOutput.Columns.Add(new DataColumn("SSASModel", typeof(String))); dtOutput.Columns.Add(new DataColumn("TableName", typeof(String))); dtOutput.Columns.Add(new DataColumn("TDQuery", typeof(String))); dtOutput.Columns.Add(new DataColumn("DAX", typeof(String))); dtOutput.Columns.Add(new DataColumn("TestResults", typeof(String))); dtOutput.Columns.Add(new DataColumn("TDQueryStartTime", typeof(DateTime))); dtOutput.Columns.Add(new DataColumn("TDQueryEndTime", typeof(DateTime))); dtOutput.Columns.Add(new DataColumn("TDQueryExecutionTime(minutes)", typeof(System.Double))); dtOutput.Columns.Add(new DataColumn("SSASQueryStartTime", typeof(DateTime))); dtOutput.Columns.Add(new DataColumn("SSASQueryEndTime", typeof(DateTime))); dtOutput.Columns.Add(new DataColumn("SSASQueryExecutionTime(minutes)", typeof(System.Double))); DataRow dr = dtOutput.NewRow(); dr["SSASModel"] = ssasModel; dr["TableName"] = tableName; dr["TDQuery"] = tdQuery; dr["DAX"] = daxQuery; dr["TDQueryStartTime"] = tdStartTime; dr["TDQueryEndTime"] = tdEndTime; dr["TDQueryExecutionTime(minutes)"] = (double)tdEndTime.Subtract(tdStartTime).Seconds / 60; dr["SSASQueryStartTime"] = SSASStartTime; dr["SSASQueryEndTime"] = SSASEndTime; dr["SSASQueryExecutionTime(minutes)"] = (double)SSASEndTime.Subtract(SSASStartTime).Seconds / 60; if (noOfRows == 0) { //tableName = tableName + "_NODATA"; dr["TestResults"] = "No data"; drSummary["TestResults"] = "No data"; } else if (ret.Rows.Count > 0) { workbook.Worksheets.Add(ret, "ERRORS"); //tableName = tableName + "_FAILED"; dr["TestResults"] = "Failed"; drSummary["TestResults"] = "Failed"; } else { //tableName = tableName + "_PASSED"; dr["TestResults"] = "Passed"; drSummary["TestResults"] = "Passed"; } dtOutput.Rows.Add(dr); workbook.Worksheets.Add(dtOutput, "TestOutput"); } catch (Exception e) { Console.WriteLine(e.Message); Console.WriteLine(e.StackTrace); tableName = tableName + "_Exception"; DataTable ex = new DataTable("Exception"); ex.Columns.AddRange(new DataColumn[2] { new DataColumn("ERROR1"), new DataColumn("ERROR2") }); drSummary["Exception"] = e.Message; ex.Rows.Add("Failed", e.Message); ex.Rows.Add("", e.StackTrace); workbook.Worksheets.Add(ex, "Exception"); } finally { dtSummary.Rows.Add(drSummary); workbook.SaveAs(od.FullName + "\\" + tableName + ".xlsx"); XLWorkbook summaryworkbook = new XLWorkbook(); summaryworkbook.Worksheets.Add(dtSummary, "TestSummary"); summaryworkbook.SaveAs(od.FullName + "\\TestSummary.xlsx"); } } } }); }
public DataTable ExecuteDaxQueryDataTable(string query) { return(_retry.Execute(() => _connection.ExecuteDaxQueryDataTable(query))); }
public static void runAllDAX(Options o) { Utils utils = new Utils(); DataTable excel = utils.ImportExceltoDatatable(o.inExcel, "Queries"); int recordCount = 0; DirectoryInfo od = new DirectoryInfo(o.outDir); od.Create(); DataTable overallStatusDT = utils.getStatusDataTable(); using (var statusWorkbook = new XLWorkbook()) { try { foreach (DataRow row in excel.Rows) { if (!keepRunning) { break; } string testName = row["NAME"].ToString(); if (testName.Trim().Equals("")) { continue; } string srcSSAS = "Data Source = " + row["SRC_SSAS"].ToString(); string srcSSASModel = row["SRC_SSAS_MODEL"].ToString(); string srcQuery = row["SRC_DAX"].ToString(); DateTime srcStartTime = DateTime.Now, srcEndTime = DateTime.Now; String srcErrorMsg = "", status = "PASS"; int SPID = 0; ADOTabularConnection srcConx = null; try { Console.WriteLine("--------------------Src DAX (" + srcSSASModel + ")---------------------------"); Console.WriteLine(srcQuery); DataTable srcDT = new DataTable(); DataTable ret = new DataTable(); try { status = "PASS"; srcConx = new ADOTabularConnection(srcSSAS, ADOTabular.Enums.AdomdType.AnalysisServices); srcConx.ChangeDatabase(srcSSASModel); srcStartTime = DateTime.Now; srcDT = srcConx.ExecuteDaxQueryDataTable(srcQuery); recordCount = srcDT.Rows.Count; SPID = srcConx.SPID; Console.WriteLine("Session:" + srcConx.SPID); } catch (Exception ee) { Console.WriteLine(ee.Message); Console.WriteLine(ee.StackTrace); srcErrorMsg = ee.Message; } finally { srcEndTime = DateTime.Now; srcConx.Close(); } } catch (Exception e) { Console.WriteLine(e.Message); Console.WriteLine(e.StackTrace); status = "FAILED"; } finally { srcConx.Close(); utils.getStatusRow(overallStatusDT, testName, status, srcStartTime, srcEndTime, (double)srcEndTime.Subtract(srcStartTime).TotalMilliseconds, recordCount, srcErrorMsg, srcQuery, SPID); } } } catch (Exception e) { Console.WriteLine(e.Message); Console.WriteLine(e.StackTrace); } finally { statusWorkbook.AddWorksheet(overallStatusDT, "Status"); statusWorkbook.SaveAs(od.FullName + "\\" + "_OverallStatus.xlsx"); } } }
public DataTable ExecuteDaxQueryDataTable(string query) { return(retry.Execute <DataTable>(() => { return _connection.ExecuteDaxQueryDataTable(query); })); }
public static Utils.StatusRow runComparison(DataRow row, DirectoryInfo od, Utils.StatusRow statusrow) { string testName = row["NAME"].ToString(); Console.WriteLine("Test [" + testName + "] Started executing"); string srcSSAS = "Data Source = " + row["SRC_SSAS"].ToString(); string tgtSSAS = "Data Source = " + row["TGT_SSAS"].ToString(); string srcSSASModel = row["SRC_SSAS_MODEL"].ToString(); string tgtSSASModel = row["TGT_SSAS_MODEL"].ToString(); string srcQuery = row["SRC_DAX"].ToString(); string tgtQuery = row["TGT_DAX"].ToString(); DateTime srcStartTime = DateTime.Now, srcEndTime = DateTime.Now, tgtStartTime = DateTime.Now, tgtEndTime = DateTime.Now; String srcErrorMsg = "", tgtErrorMsg = "", status = "PASS"; int srcNoOfRows = 0, tgtNoOfRows = 0, srcNoOfColumn = 0, tgtNoOfColumn = 0; bool srcError = false, tgtError = false; ADOTabularConnection srcConx = null; ADOTabularConnection tgtConx = null; try { //Console.WriteLine("--------------------Src DAX---------------------------"); //Console.WriteLine(srcQuery); //Console.WriteLine("--------------------tgt DAX---------------------------"); //Console.WriteLine(tgtQuery); DataTable srcDT = new DataTable(); DataTable tgtDT = new DataTable(); DataTable ret = new DataTable(); try { srcConx = new ADOTabularConnection(srcSSAS, ADOTabular.Enums.AdomdType.AnalysisServices); srcConx.ChangeDatabase(srcSSASModel); srcStartTime = DateTime.Now; srcDT = srcConx.ExecuteDaxQueryDataTable(srcQuery); } catch (Exception ee) { Console.WriteLine(ee.Message); Console.WriteLine(ee.StackTrace); srcErrorMsg = ee.Message; srcError = true; } finally { srcEndTime = DateTime.Now; srcConx.Close(); } try { tgtConx = new ADOTabularConnection(tgtSSAS, ADOTabular.Enums.AdomdType.AnalysisServices); tgtConx.ChangeDatabase(tgtSSASModel); tgtStartTime = DateTime.Now; tgtDT = tgtConx.ExecuteDaxQueryDataTable(tgtQuery); } catch (Exception ee) { Console.WriteLine(ee.Message); Console.WriteLine(ee.StackTrace); tgtErrorMsg = ee.Message; tgtError = true; } finally { tgtEndTime = DateTime.Now; tgtConx.Close(); } if (srcError || tgtError) { status = "FAILED"; } else { srcNoOfRows = srcDT.Rows.Count; tgtNoOfRows = tgtDT.Rows.Count; srcNoOfColumn = srcDT.Columns.Count; tgtNoOfColumn = tgtDT.Columns.Count; if (srcNoOfRows == 0 || tgtNoOfRows == 0) { status = "FAILED"; srcErrorMsg = "No Data:" + srcNoOfRows; tgtErrorMsg = "No Data:" + tgtNoOfRows; } else if (srcNoOfRows != tgtNoOfRows) { status = "FAILED"; srcErrorMsg = "Number of rows:" + srcNoOfRows; tgtErrorMsg = "Number of rows:" + tgtNoOfRows; } else if (srcNoOfColumn != tgtNoOfColumn) { status = "FAILED"; srcErrorMsg = "Number of Columns:" + srcNoOfColumn; tgtErrorMsg = "Number of Columns:" + tgtNoOfColumn; } else { ret = Utils.getDifferentRecords(srcDT, tgtDT); if (ret.Rows.Count > 0) { status = "FAILED"; srcErrorMsg = "Data Mismatch"; } } } DataTable statusDT = Utils.getStatusDataTable(); statusrow.NAME = testName; statusrow.STATUS = status; statusrow.SRC_SSASQueryStartTime = srcStartTime; statusrow.SRC_SSASQueryEndTime = srcEndTime; statusrow.SRC_SSASQueryExecutionTime = (double)srcEndTime.Subtract(srcStartTime).Seconds; statusrow.TGT_SSASQueryStartTime = tgtStartTime; statusrow.TGT_SSASQueryEndTime = tgtEndTime; statusrow.TGT_SSASQueryExecutionTime = (double)tgtEndTime.Subtract(tgtStartTime).Seconds; statusrow.SRC_Exception = srcErrorMsg; statusrow.TGT_Exception = tgtErrorMsg; statusrow.SRC_DAX = srcQuery; statusrow.TGT_DAX = tgtQuery; Utils.getStatusRow(statusDT, statusrow); using (var workbook = new XLWorkbook()) { try { workbook.AddWorksheet(statusDT, "Status"); workbook.AddWorksheet(srcDT, "Source"); workbook.AddWorksheet(tgtDT, "Target"); workbook.AddWorksheet(ret, "Delta"); } catch (Exception e) { Console.WriteLine(e.Message); Console.WriteLine(e.StackTrace); } finally { workbook.SaveAs(od.FullName + "\\" + status + "_" + testName + ".xlsx"); } } } catch (Exception e) { Console.WriteLine(e.Message); Console.WriteLine(e.StackTrace); statusrow.STATUS = "FAILED"; } finally { srcConx.Close(); tgtConx.Close(); //Utils.getStatusRow(overallStatusDT, statusrow); //Console.WriteLine("Tast [" + testName + "] Finished with Status:" + statusrow.STATUS); } return(statusrow); }