· 6 years ago · Jun 28, 2019, 03:02 PM
1using System;
2using System.Collections.Generic;
3using System.Linq;
4using System.Text;
5using System.IO;
6using Microsoft.AnalysisServices.AdomdClient;
7using System.Runtime.Serialization.Formatters.Binary;
8
9namespace Tests
10{
11 public class BaseMDXTest
12 {
13 private string connectionstring;
14 private string baseOutputDir;
15 private string outputDir;
16 private Boolean recreateTemplate = false;
17 private static string testReportHtml;
18 private static int numPassed = 0;
19 private static int numFailed = 0;
20
21 /** Construct BaseMDX Test
22 * connectionstring - The URL of the Analysis Server on which the MDX query is executed
23 * baseOutputDir - The output directory under which the MDX query results are stored
24 * recreateTemplate - Force the recreation of the MDX query result template
25 **/
26 public BaseMDXTest(string connectionstring, string baseOutputDir, Boolean recreateTemplate = false)
27 {
28 this.connectionstring = connectionstring;
29 this.baseOutputDir = baseOutputDir;
30 this.recreateTemplate = recreateTemplate;
31 if (testReportHtml == null)
32 testReportHtml = "<html><h1>Test Report</h1><table border=\"1\"><tr><th>Test Case</th><th>Pass/Fail</th></tr>";
33 }
34
35 /** Open a connection to the Analysis Services database **/
36 static AdomdConnection GetAdomdConnection(string connectionstring)
37 {
38 var connection = new AdomdConnection(connectionstring);
39 connection.Open();
40 return connection;
41 }
42
43 /** Runs the given MDX query, creates a template if it doesn't exist, and stores the results
44 * mdxTestTitle - The name of the unit-test
45 * mdxQuery - The MDX query to test
46 **/
47 public Boolean TestMDXQuery(string mdxTestTitle, string mdxQuery)
48 {
49 Dictionary<string, CubeRow> templateCubeDict = new Dictionary<string, CubeRow>();
50 string templateHtml = null;
51 string resultHtml = "<html><h2>Test Title</h2><p>" + mdxTestTitle + "</p><h2>MDX Query</h2><p>" + mdxQuery + "</p>\n";
52 // If the template for the given test already exists and template recreation has not been requested
53 // then run the comparison.
54 if (!recreateTemplate && TemplateExists(mdxTestTitle))
55 {
56 resultHtml += "<h2>Original Template</h2>\n";
57 // Lots the origional template HTML
58 templateHtml = loadTemplate(mdxTestTitle, templateCubeDict);
59 resultHtml += templateHtml + "\n";
60 resultHtml += "<h2>Test Results</h2>\n";
61 // Run the MDX query and compare against the template
62 CubeComparisonResult result = RunComparison(mdxQuery, templateCubeDict);
63 resultHtml += result.getResultHtml() + "\n";
64 resultHtml += result.hasPassed() ? "<h2 stlye=\"color:green\";>Test Passes!</h2>" : "<h2 style=\"color:red;\">Test Fails</h2>";
65 resultHtml += "</html>";
66 // Persist the MDX query result
67 string testReportFile = PersistResult(mdxTestTitle, resultHtml);
68 if (result.hasPassed())
69 numPassed++;
70 else
71 numFailed++;
72 testReportHtml += "<tr><td><a href=\"file:\\" + testReportFile + "\">" + mdxTestTitle + "</a></td><td>" + (result.hasPassed() ? "Pass" : "Fail") + "</td></tr>";
73 // Persist the HTML summary report
74 PersistTestReport(numPassed, numFailed);
75 return result.hasPassed();
76 }
77 else // Else build the MDX query result template if the template does not previously exist or a request to force the re-creation of the template.
78 {
79 resultHtml += "<h2>Original Template</h2>\n";
80 // Build the template using the MDXQuery and store it with the Dictionary<string, CubeRow>
81 templateHtml = BuildTemplate(mdxQuery, templateCubeDict);
82 resultHtml += templateHtml + "\n";
83 resultHtml += "<h2 style=\"color:red\";>Test Fails. No Template exists. You must validate the output by hand</h2>";
84 resultHtml += "</html>";
85 // Persist the template. This includes the Dictionary<string, CubeRow> and the HTML version of the MDX query result
86 string resultHtmlFile = PersistTemplate(mdxTestTitle, templateHtml, templateCubeDict);
87 testReportHtml += "<tr><td><a href=\"file:\\" + resultHtmlFile + "\">" + mdxTestTitle + "</a></td><td>Fail - Template Did not Exist</td></tr>";
88 // Creation of the template is always considered a failure since the unit-test developer must hand validate
89 // the results before the template is considered usable.
90 numFailed++;
91 // Persist the HTML summary report
92 PersistTestReport(numPassed, numFailed);
93 return false;
94 }
95 }
96
97 /** Save a summary of the test results **/
98 private void PersistTestReport(int numPassed, int numFailed)
99 {
100 string testReportFile = baseOutputDir + "\\TestReport.html";
101 string tempReport = testReportHtml;
102 tempReport += "</table>";
103 tempReport += "<h3>Num Passed: " + numPassed + "</h3>";
104 tempReport += "<h3>Num Failed: " + numFailed + "</h3>";
105 tempReport += "</html>";
106 using (StreamWriter sw = File.CreateText(testReportFile))
107 {
108 sw.WriteLine(tempReport);
109 }
110 }
111
112 /** Load both the HTML and Object version of the MDX query result template **/
113 private string loadTemplate(string mdxTestTitle, Dictionary<string, CubeRow> templateCubeDict)
114 {
115 string templateFile = GetTemplateDir(mdxTestTitle) + "\\template.html";
116 string templateHtml = null;
117 using (StreamReader sr = File.OpenText(templateFile))
118 {
119 templateHtml = sr.ReadToEnd();
120 }
121
122 string cubeDictFile = GetTemplateDir(mdxTestTitle) + "\\CubeDict";
123 using (Stream stream = File.Open(cubeDictFile, FileMode.Open))
124 {
125 BinaryFormatter binaryFormatter = new BinaryFormatter();
126 Dictionary<string, CubeRow> templateDict = (Dictionary<string, CubeRow>)binaryFormatter.Deserialize(stream);
127 foreach (var cubeRow in templateDict)
128 {
129 templateCubeDict.Add(cubeRow.Key, cubeRow.Value);
130 }
131 }
132 return templateHtml;
133 }
134
135 /** Save the HTML file that contains the MDX query comparison results **/
136 private string PersistResult(string mdxTestTitle, string resultHtml)
137 {
138 string outputDir = baseOutputDir + "\\result\\" + mdxTestTitle;
139 if (!System.IO.Directory.Exists(@outputDir))
140 System.IO.Directory.CreateDirectory(@outputDir);
141
142 string resultHtmlFile = outputDir + "\\result.html";
143 using (StreamWriter sw = File.CreateText(resultHtmlFile))
144 {
145 sw.WriteLine(resultHtml);
146 }
147 return resultHtmlFile;
148 }
149
150 /** Save the HTML file and Dictionary<string, CubeRow> Object that contains the initial version of the
151 * MDX query results.
152 * mdxTestTitle - The name of the unit-test
153 * resultHTML - The HTML representation of the MDX query results.
154 * templateCubeDict - THe object representation of the MDX query results.
155 **/
156 private string PersistTemplate(string mdxTestTitle, string resultHtml, Dictionary<string, CubeRow> templateCubeDict)
157 {
158 string templateDir = GetTemplateDir(mdxTestTitle);
159 if (!System.IO.Directory.Exists(@templateDir))
160 System.IO.Directory.CreateDirectory(@templateDir);
161
162 string resultHtmlFile = templateDir + "\\template.html";
163 using (StreamWriter sw = File.CreateText(resultHtmlFile))
164 {
165 sw.WriteLine(resultHtml);
166 }
167
168 string cubeDictFile = templateDir + "\\CubeDict";
169 using (Stream stream = File.Open(cubeDictFile, FileMode.Create))
170 {
171 BinaryFormatter formatter = new BinaryFormatter();
172 formatter.Serialize(stream, templateCubeDict);
173 }
174 return resultHtmlFile;
175 }
176
177 /**
178 * Compares the results of a MDX query with the previously peristed results
179 **/
180 private CubeComparisonResult RunComparison(string mdxQuery, Dictionary<string, CubeRow> templateCubeDict)
181 {
182 string tableResult = "<table border=\"1\">";
183 Boolean testPass = true;
184 // Open a connection to the Analysis server
185 using (var connection = GetAdomdConnection(connectionstring))
186 // Execute the MDX query
187 using (var command = new AdomdCommand(mdxQuery, connection))
188 {
189 // Get the MDX query results as a CellSet object
190 CellSet cst = command.ExecuteCellSet();
191
192 // Make sure that the result contains at least two axes. Columns and rows.
193 int axesCount = cst.Axes.Count;
194 if (axesCount != 2)
195 throw new Exception("The MDX Query did not return two axes. Please check your query.");
196
197 // Make sure that data is contained within the two axes.
198 for (int count = 0; count < axesCount; count++)
199 {
200 if (cst.Axes[count].Positions.Count == 0)
201 throw new Exception("The MDX query return no data. Please check your query.");
202 }
203
204 // Get the number of column dimensions
205 int colDimCount = cst.Axes[0].Positions[0].Members.Count;
206 // Get the number of row dimensions
207 int rowDimCount = 0;
208 if (cst.Axes[1].Positions[0].Members.Count > 0)
209 rowDimCount = cst.Axes[1].Positions[0].Members.Count;
210
211 // The row count is the total number of Position(s) on the row axis plus the number of column dimensions
212 // The reason the column dimension count is included is that the column headers themselves make up rows
213 int rowCount = cst.Axes[1].Positions.Count + colDimCount;
214 // The column count is the total number of Position(s) on the column axis plus the number of row dimensions.
215 // The reason the row dimension count is included is that the row headers themselves make up columns
216 int colCount = cst.Axes[0].Positions.Count + rowDimCount;
217
218 // Create an array of string(s) that hold the column headers
219 string[] columnHeader = new string[cst.Axes[0].Positions.Count];
220
221 // Create a point to hold the current CubeRow dictionary
222 Dictionary<string, CubeRow> currentCubeDict = templateCubeDict;
223 // Create a pointer to hold the current CubeRow object
224 CubeRow currentCubeRow = null;
225 // Loop through all the rows within the MDX result
226 for (int currentRow = 0; currentRow < rowCount; currentRow++)
227 {
228 // Create a new HTML table row
229 tableResult += "<tr>";
230
231 // If the current row is greater than the number of column dimensions then
232 // processing of actual row data is occurring. Set the CubeRow pointer to a new
233 // CubeRow and set the CubeRow Dictionary pointer to the original Dictionary passed in.
234 // This is also the head of the Dictionary structure.
235 if (currentRow >= colDimCount)
236 {
237 currentCubeRow = new CubeRow();
238 currentCubeDict = templateCubeDict;
239 }
240
241 // Loop through all the columns within the MDX result
242 for (int currentCol = 0; currentCol < colCount; currentCol++)
243 {
244 // Check if the current row is dealing with column dimension captions
245 if (currentRow < colDimCount)
246 {
247 if (currentCol < rowDimCount)
248 {
249 // If the current column is has not reached the row dimension headers create a blank HTML cell
250 // These cells make up the uper left hand corner of the HTML table as place holders before the
251 // HTML column headers and row headers meet.
252 tableResult += "<th> </th>";
253 }
254 else
255 {
256 // A column header is being processed. The column header may be many levels deep depending on
257 // how many dimensions are included in the column portion of the MDX query. The location of the column header is
258 // computed by taking the current column count minus the number of row dimensions since getting the total number of
259 // columns involved adding the total number of row dimensions.
260
261 // Get the current column index which is the current column count - the number of row dimensions since
262 // the row dimensions captions are part of the columns.
263 int columnIndex = currentCol - rowDimCount;
264
265 // If the column header at the given location does not exist add it.
266 if (columnHeader[columnIndex] == null)
267 columnHeader[columnIndex] = cst.Axes[0].Positions[columnIndex].Members[currentRow].Caption;
268 else
269 // Otherwise if the column header does exist append to it.
270 columnHeader[columnIndex] += ":" + cst.Axes[0].Positions[columnIndex].Members[currentRow].Caption;
271
272 // Create an HTML table header cell to hold the column name and add it to the HTML template.
273 tableResult += "<th>" + cst.Axes[0].Positions[columnIndex].Members[currentRow].Caption + "</th>";
274 }
275 }
276 else
277 {
278 // Each cell of the HTML table will be colored green (to indicate a match with the template) or red (to indicate a mismatch)
279 // Always the optomist the cell color is initialized as green.
280 string color = "green";
281 if (currentCol < rowDimCount)
282 {
283 // Get the current row index which is the current row count - the number of column dimensions since the
284 // column dimension captions are part of the rows.
285 int rowIndex = currentRow - colDimCount;
286 // Capture the row caption since processing at this point is not within the row values
287 string rowCaption = cst.Axes[1].Positions[rowIndex].Members[currentCol].Caption;
288
289 if (!currentCubeDict.ContainsKey(rowCaption))
290 {
291 // If the template does not contain the row caption fail the test and set the color to red.
292 // Color all cells within this row red since this is extra data that is not in the template.
293 color = "red";
294 testPass = false;
295 }
296 else
297 {
298 // If the template contains the row caption extract the CubeRow dictionary that
299 // applies to this row caption and point the current CubeRow and CubeRow Dictionary
300 // to the extracted CubeRow.
301 currentCubeRow = currentCubeDict[rowCaption];
302 currentCubeDict = currentCubeRow.GetCubeDict();
303 }
304 // Add the row caption to the HTML table appropriately colored.
305 tableResult += "<th bgcolor=\"" + color + "\">" + rowCaption + "</th>";
306 }
307 else
308 {
309 // Get the current column index which is the current column count - the number of row dimensions since
310 // the row dimensions captions are part of the columns.
311 int columnIndex = currentCol - rowDimCount;
312 // Get the current row index which is the current row count - the number of column dimensions since the
313 // column dimension captions are part of the rows.
314 int rowIndex = currentRow - colDimCount;
315 // Get the current column name for the array of column headers
316 string columnName = columnHeader[columnIndex];
317 // Get the current column value based on the current column and row
318 string value = cst[columnIndex, rowIndex].FormattedValue;
319 //if (value.Equals("621369"))
320 // value = "621370";
321 if (currentCubeRow == null)
322 {
323 // if the current cube row does not exist color all cells red and fail the test.
324 color = "red";
325 testPass = false;
326 }
327 else
328 {
329 // Get the CubeTuple for the the current column name
330 CubeTuple templateValue = currentCubeRow.GetCubeTuple(columnName);
331 if (templateValue == null)
332 {
333 // If the column name returned by the MDX query does not exist in the template.
334 // then the test is considered to have failed.
335 color = "red";
336 testPass = false;
337 }
338 else if (!templateValue.getColumnValue().Equals(value))
339 {
340 // If the column name does exist within the tempalte but the value associated with
341 // the column name does not equal what is in the tempplate then the test is
342 // considered to have failed.
343 color = "red";
344 testPass = false;
345 }
346 // Remove the CubeTuple for the current column name from the template data structure.
347 currentCubeRow.RemoveCubeTuple(columnName);
348 }
349 tableResult += "<td white-space:nowrap bgcolor=\"" + color + "\">" + value + "</td>";
350 }
351 }
352 }
353 tableResult += "</tr>";
354 }
355 tableResult += "</table>";
356
357 if (testPass)
358 {
359 // If the tests thus far has passed check to see if there is still CubeTuple(s) that exist within the
360 // template. If there are then extra data exists in the template that was not returned by the current
361 // MDX query.
362 testPass = !CheckForExtraDataInTemplate(templateCubeDict);
363 }
364 return new CubeComparisonResult(testPass, tableResult);
365 }
366 }
367
368 /** Recursively checks the MDX query result dictionary for extra data that was not found
369 * during a subsequent run. What this means is that the test fails because it does not have as much
370 * data returned as the origional query.
371 **/
372 private Boolean CheckForExtraDataInTemplate(Dictionary<string, CubeRow> templateCubeDict)
373 {
374 foreach (string key in templateCubeDict.Keys)
375 {
376 CubeRow cubeRow = templateCubeDict[key];
377 if (cubeRow.GetCubeDict() != null)
378 return CheckForExtraDataInTemplate(cubeRow.GetCubeDict());
379 else
380 {
381 if (cubeRow.GetCubeTupleSize() > 0)
382 return true;
383 }
384 }
385 return false;
386 }
387
388 /** Builds the HTML and Dictionary<string, CubeRow> MDX query result objects give an MDX query
389 * mdxQuery - The MDX query that contains the test
390 * templateCubeDict - The datastructure that holds the MDX query results
391 */
392 private string BuildTemplate(string mdxQuery, Dictionary<string, CubeRow> templateCubeDict)
393 {
394 string tableResult = "<table border=\"1\">";
395 // Open a connection to the Analysis server
396 using (var connection = GetAdomdConnection(connectionstring))
397 // Execute the MDX query
398 using (var command = new AdomdCommand(mdxQuery, connection))
399 {
400 // Get the MDX query results as a CellSet object
401 CellSet cst = command.ExecuteCellSet();
402 // Make sure that the result contains at least two axes. Columns and rows.
403 int axesCount = cst.Axes.Count;
404 if (axesCount != 2)
405 throw new Exception("The MDX Query did not return two axes. Please check your query.");
406
407 // Make sure that data is contained within the two axes.
408 for (int count = 0; count < axesCount; count++)
409 {
410 if (cst.Axes[count].Positions.Count == 0)
411 throw new Exception("The MDX query return no data. Please check your query.");
412 }
413
414 // Get the number of column dimensions
415 int colDimCount = cst.Axes[0].Positions[0].Members.Count;
416 // Get the number of row dimensions
417 int rowDimCount = 0;
418 if (cst.Axes[1].Positions[0].Members.Count > 0)
419 rowDimCount = cst.Axes[1].Positions[0].Members.Count;
420
421 // The row count is the total number of Position(s) on the row axis plus the number of column dimensions
422 // The reason the column dimension count is included is that the column headers themselves make up rows
423 int rowCount = cst.Axes[1].Positions.Count + colDimCount;
424 // The column count is the total number of Position(s) on the column axis plus the number of row dimensions.
425 // The reason the row dimension count is included is that the row headers themselves make up columns
426 int colCount = cst.Axes[0].Positions.Count + rowDimCount;
427
428 // Create an array of string(s) that hold the column headers
429 string[] columnHeader = new string[cst.Axes[0].Positions.Count];
430
431 // Create a pointer to hold the current CubeRow object
432 CubeRow currentCubeRow = null;
433 // Create a point to hold the current CubeRow dictionary
434 Dictionary<string, CubeRow> currentCubeDict = templateCubeDict;
435
436 // Loop through all the rows within the MDX result
437 for (int currentRow = 0; currentRow < rowCount; currentRow++)
438 {
439 // Create a new HTML table row
440 tableResult += "<tr>";
441
442 // If the current row is greater than the number of column dimensions then
443 // processing of actual row data is occurring. Set the CubeRow pointer to a new
444 // CubeRow and set the CubeRow Dictionary pointer to the original Dictionary passed in.
445 // This is also the head of the Dictionary structure.
446 if (currentRow >= colDimCount)
447 {
448 currentCubeRow = new CubeRow();
449 currentCubeDict = templateCubeDict;
450 }
451
452 // Loop through all the columns within the MDX result
453 for (int currentCol = 0; currentCol < colCount; currentCol++)
454 {
455 // Check if the current row is dealing with column dimension captions
456 if (currentRow < colDimCount)
457 {
458 if (currentCol < rowDimCount)
459 {
460 // If the current column is has not reached the row dimension headers create a blank HTML cell
461 // These cells make up the uper left hand corner of the HTML table as place holders before the
462 // HTML column headers and row headers meet.
463 tableResult += "<th> </th>";
464 }
465 else
466 {
467 // A column header is being processed. The column header may be many levels deep depending on
468 // how many dimensions are included in the column portion of the MDX query. The location of the column header is
469 // computed by taking the current column count minus the number of row dimensions since getting the total number of
470 // columns involved adding the total number of row dimensions.
471
472 // Get the current column index which is the current column count - the number of row dimensions since
473 // the row dimensions captions are part of the columns.
474 int columnIndex = currentCol - rowDimCount;
475
476 // If the column header at the given location does not exist add it.
477 if (columnHeader[columnIndex] == null)
478 columnHeader[columnIndex] = cst.Axes[0].Positions[columnIndex].Members[currentRow].Caption;
479 else
480 // Otherwise if the column header does exist append to it.
481 columnHeader[columnIndex] += ":" + cst.Axes[0].Positions[columnIndex].Members[currentRow].Caption;
482
483 // Create an HTML table header cell to hold the column name and add it to the HTML template.
484 tableResult += "<th>" + cst.Axes[0].Positions[columnIndex].Members[currentRow].Caption + "</th>";
485 }
486 }
487 else // If current row is greater then or equal to the column dimension count then processing of the MDX row data commences
488 {
489 // If the current column count is less than the row count then a row header is being traversed.
490 if (currentCol < rowDimCount)
491 {
492 if (currentCol > 0)
493 {
494 // If the current column count is greater than 0 then a new row header is being processed.
495 // Create a new CubeRow Dictionary to store the contents of the given row header.
496 if (currentCubeRow.GetCubeDict() == null)
497 currentCubeRow.setCubeDict(new Dictionary<string, CubeRow>());
498 currentCubeDict = currentCubeRow.GetCubeDict();
499 currentCubeRow = new CubeRow();
500 }
501
502 // Get the current row index which is the current row count - the number of column dimensions since the
503 // column dimension captions are part of the rows.
504 int rowIndex = currentRow - colDimCount;
505 // Store the row header caption
506 string rowCaption = cst.Axes[1].Positions[rowIndex].Members[currentCol].Caption;
507 if (!currentCubeDict.ContainsKey(rowCaption))
508 {
509 // If the row header caption does not exist in the current CubeRow dictionary add it and the
510 // new CubeRow
511 currentCubeDict.Add(rowCaption, currentCubeRow);
512 }
513 else
514 {
515 // if the row header does exist in the current CubeRow dictionary then use it to retrieve the current
516 // CubeRow value.
517 currentCubeRow = currentCubeDict[rowCaption];
518 }
519
520 // Update the HTML to include the row header caption
521 tableResult += "<th>" + rowCaption + "</th>";
522 }
523 else
524 {
525 // Get the current column index which is the current column count - the number of row dimensions since
526 // the row dimensions captions are part of the columns.
527 int columnIndex = currentCol - rowDimCount;
528 // Get the current row index which is the current row count - the number of column dimensions since the
529 // column dimension captions are part of the rows.
530 int rowIndex = currentRow - colDimCount;
531 // Add a CubeTuple at the current column and row location with the current column header and Cube value
532 currentCubeRow.AddTuple(columnHeader[columnIndex], cst[columnIndex, rowIndex].FormattedValue);
533 // Add the Cube cell value within an HTML cell.
534 tableResult += "<td white-space:nowrap>" + cst[columnIndex, rowIndex].FormattedValue + "</td>";
535 }
536 }
537 }
538 tableResult += "</tr>";
539 }
540 tableResult += "</table>";
541
542 return tableResult;
543 }
544 }
545
546 /** Check if the MDX query result template with the given MDX test name exists **/
547 private bool TemplateExists(string mdxTestTitle)
548 {
549 string templateDir = GetTemplateDir(mdxTestTitle);
550 return System.IO.Directory.Exists(templateDir);
551 }
552
553 /** Returns the template directory for the given MDX test name **/
554 private string GetTemplateDir(string mdxTestTitle)
555 {
556 return baseOutputDir + "\\templates\\" + mdxTestTitle;
557 }
558 }
559}