PageRenderTime 56ms CodeModel.GetById 21ms RepoModel.GetById 1ms app.codeStats 0ms

/bidshelper/SSAS/AggManager/EditAggs.cs

#
C# | 1381 lines | 1094 code | 179 blank | 108 comment | 232 complexity | 6236ccdbdba4d0f7b98f74b217f27a3c MD5 | raw file
Possible License(s): CC-BY-SA-3.0

Large files files are truncated, but you can click here to view the full file

  1. /*============================================================================
  2. File: EditAggs.cs
  3. Summary: Contains the form to add, delete, and change aggregations
  4. Part of Aggregation Manager
  5. Date: January 2007
  6. ------------------------------------------------------------------------------
  7. This file is part of the Microsoft SQL Server Code Samples.
  8. Copyright (C) Microsoft Corporation. All rights reserved.
  9. This source code is intended only as a supplement to Microsoft
  10. Development Tools and/or on-line documentation. See these other
  11. materials for detailed information regarding Microsoft code samples.
  12. THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
  13. KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
  14. IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  15. PARTICULAR PURPOSE.
  16. ============================================================================*/
  17. /*
  18. * This file has been incorporated into BIDSHelper.
  19. * http://www.codeplex.com/BIDSHelper
  20. * and may have been altered from the orginal version which was released
  21. * as a Microsoft sample.
  22. *
  23. * The official version can be found on the sample website here:
  24. * http://www.codeplex.com/MSFTASProdSamples
  25. *
  26. ============================================================================*/
  27. using System;
  28. using System.Collections.Generic;
  29. using System.ComponentModel;
  30. using System.Data;
  31. using System.Drawing;
  32. using System.Text;
  33. using System.Windows.Forms;
  34. using Microsoft.AnalysisServices;
  35. using Microsoft.AnalysisServices.AdomdClient;
  36. namespace AggManager
  37. {
  38. public partial class EditAggs : Form
  39. {
  40. private MeasureGroup mg1;
  41. private AggregationDesign aggDes;
  42. string[,] dimAttributes;
  43. string[] dimNames;
  44. string[] dimIDs;
  45. bool boolHandleClick = false;
  46. bool boolIsRigid = false;
  47. bool boolInExpandOrCollapse = false;
  48. private Color nonMaterializedColor = Color.SteelBlue;
  49. private Color parentChildAttributeColor = Color.SlateGray;
  50. private Color belowGranularityColor = Color.LightSlateGray;
  51. enum SynchControls
  52. {
  53. SynchTreeToGrid = 1,
  54. SynchGridToTreeView = 2,
  55. Unknown = 3
  56. }
  57. SynchControls sychContr = SynchControls.Unknown;
  58. public EditAggs()
  59. {
  60. InitializeComponent();
  61. }
  62. public void Init(string strAggDesign,
  63. MeasureGroup mg,
  64. string[,] inDimAttributes,
  65. string[] inDimNames ,
  66. string[] inDimIDs)
  67. {
  68. this.Text = this.Text + " Aggregation Design: " + strAggDesign;
  69. mg1 = mg;
  70. aggDes = mg.AggregationDesigns.GetByName(strAggDesign);
  71. dimAttributes = inDimAttributes;
  72. dimNames = inDimNames;
  73. dimIDs = inDimIDs;
  74. DataTable myTable = new DataTable("Aggregations");
  75. DataColumn colItem = new DataColumn("Name", Type.GetType("System.String"));
  76. myTable.Columns.Add(colItem);
  77. colItem = new DataColumn("Aggregations", Type.GetType("System.String"));
  78. myTable.Columns.Add(colItem);
  79. colItem = new DataColumn("Type", Type.GetType("System.String"));
  80. myTable.Columns.Add(colItem);
  81. DataView myDataView = new DataView(myTable);
  82. dataGrid1.DataSource = myDataView;
  83. DataRow NewRow;
  84. foreach (Aggregation agg in aggDes.Aggregations)
  85. {
  86. NewRow = myTable.NewRow();
  87. NewRow["Aggregations"] = ConvertAggToSting(agg);
  88. NewRow["Name"] = agg.Name;
  89. myTable.Rows.Add(NewRow);
  90. }
  91. AddGridStyle();
  92. PopulateTreeView();
  93. checkBoxRelationships.Checked = true;
  94. sychContr = SynchControls.Unknown;
  95. int i = 0;
  96. foreach (DataRow dRow in myDataView.Table.Rows)
  97. {
  98. dataGrid1.CurrentRowIndex = i;
  99. dataGrid1_Click(null, null);
  100. i++;
  101. }
  102. myDataView.AllowNew = false;
  103. }
  104. /// <summary>
  105. /// Setting data grid column captions and width
  106. /// </summary>
  107. private void AddGridStyle()
  108. {
  109. DataView myDataView = (DataView)dataGrid1.DataSource;
  110. int iWidth0 = 100;
  111. int iWidth1 = 400;
  112. Graphics Graphics = dataGrid1.CreateGraphics();
  113. if (myDataView.Table.Rows.Count > 0)
  114. {
  115. int iColWidth = (int)(Graphics.MeasureString
  116. (myDataView.Table.Rows[0].ItemArray[0].ToString(),
  117. dataGrid1.Font).Width);
  118. iWidth0 = (int)System.Math.Max(iWidth0, iColWidth);
  119. iColWidth = (int)(Graphics.MeasureString
  120. (myDataView.Table.Rows[0].ItemArray[1].ToString(),
  121. dataGrid1.Font).Width);
  122. iWidth1 = (int)System.Math.Max(iWidth1, iColWidth);
  123. }
  124. DataGridTableStyle myGridStyle = new DataGridTableStyle();
  125. myGridStyle.MappingName = "Aggregations";
  126. DataGridTextBoxColumn nameColumnStyle = new DataGridTextBoxColumn();
  127. nameColumnStyle.MappingName = "Name";
  128. nameColumnStyle.HeaderText = "Name";
  129. nameColumnStyle.Width = iWidth0;
  130. myGridStyle.GridColumnStyles.Add(nameColumnStyle);
  131. DataGridTextBoxColumn nameColumnStyle2 = new DataGridTextBoxColumn();
  132. nameColumnStyle2.MappingName = "Type";
  133. nameColumnStyle2.HeaderText = "Type";
  134. nameColumnStyle2.Width = 50;
  135. myGridStyle.GridColumnStyles.Add(nameColumnStyle2);
  136. DataGridTextBoxColumn nameColumnStyle1 = new DataGridTextBoxColumn();
  137. nameColumnStyle1.MappingName = "Aggregations";
  138. nameColumnStyle1.HeaderText = "Aggregation Definition";
  139. nameColumnStyle1.Width = iWidth1;
  140. myGridStyle.GridColumnStyles.Add(nameColumnStyle1);
  141. dataGrid1.TableStyles.Add(myGridStyle);
  142. }
  143. private void buttonCancel_Click(object sender, EventArgs e)
  144. {
  145. this.Close();
  146. }
  147. private void buttonOK_Click(object sender, EventArgs e)
  148. {
  149. try
  150. {
  151. DataView myDataView;
  152. int i = 0;
  153. string strRow;
  154. string strAggName;
  155. if (!AggNamesAreValid()) return;
  156. this.Cursor = Cursors.WaitCursor;
  157. aggDes.Aggregations.Clear();
  158. myDataView = (DataView)dataGrid1.DataSource;
  159. foreach (DataRow dRow in myDataView.Table.Rows)
  160. {
  161. if (dRow.RowState.ToString() != "Deleted")
  162. {
  163. strAggName = dRow.ItemArray[0].ToString();
  164. strRow = dRow.ItemArray[1].ToString();
  165. i++;
  166. if (AddAggregationToAggDesign(
  167. aggDes,
  168. strRow,
  169. strAggName) == false)
  170. i--; // No aggregation has been added
  171. }
  172. }
  173. this.Cursor = Cursors.Default;
  174. //MessageBox.Show("Aggregation design: " + aggDes.Name + " has been updated with " + i.ToString() + " aggregations");
  175. this.DialogResult = DialogResult.OK;
  176. this.Close();
  177. }
  178. catch (Exception ex)
  179. {
  180. this.Cursor = Cursors.Default;
  181. if (!String.IsNullOrEmpty(ex.Message)) MessageBox.Show("Error: " + ex.Message);
  182. }
  183. }
  184. private bool AggNamesAreValid()
  185. {
  186. DataView myDataView = (DataView)dataGrid1.DataSource;
  187. string strAggName;
  188. List<string> aggNames = new List<string>();
  189. string sInvalidChars = BIDSHelper.SsasCharacters.Invalid_Name_Characters;
  190. foreach (DataRow dRow in myDataView.Table.Rows)
  191. {
  192. if (dRow.RowState != DataRowState.Deleted)
  193. {
  194. strAggName = dRow.ItemArray[0].ToString();
  195. if (strAggName.IndexOfAny(sInvalidChars.ToCharArray()) > 0)
  196. {
  197. MessageBox.Show(strAggName + " cannot contain any of the following characters: " + sInvalidChars);
  198. return false;
  199. }
  200. if (aggNames.Contains(strAggName))
  201. {
  202. MessageBox.Show(strAggName + " is a duplicate aggregation name.");
  203. return false;
  204. }
  205. aggNames.Add(strAggName);
  206. }
  207. }
  208. return true;
  209. }
  210. /// <summary>
  211. /// Helper function takes aggregation as input and returns string representation of aggregation
  212. /// </summary>
  213. private string ConvertAggToSting(Aggregation agg)
  214. {
  215. string outStr = "";
  216. AggregationAttribute aggAttr;
  217. AggregationDimension aggDim;
  218. foreach (MeasureGroupDimension mgDim in mg1.Dimensions)
  219. {
  220. aggDim = agg.Dimensions.Find(mgDim.CubeDimensionID);
  221. if (aggDim == null)
  222. {
  223. foreach (CubeAttribute cubeDimAttr in mgDim.CubeDimension.Attributes)
  224. outStr = outStr + "0";
  225. }
  226. else
  227. {
  228. foreach (CubeAttribute cubeDimAttr in mgDim.CubeDimension.Attributes)
  229. {
  230. aggAttr = aggDim.Attributes.Find(cubeDimAttr.AttributeID);
  231. if (aggAttr == null)
  232. outStr = outStr + "0";
  233. else
  234. outStr = outStr + "1";
  235. }
  236. }
  237. outStr = outStr + ",";
  238. }
  239. return outStr.Substring(0, outStr.Length - 1);
  240. }
  241. private void SetEstimatedSize(Aggregation agg)
  242. {
  243. double size = 0;
  244. double minSize = 0;
  245. bool bAggContainsAllGranularityAttributes = true;
  246. try
  247. {
  248. EstimatedAggSize oEstSize = GetEstimatedSize(agg, mg1);
  249. size = oEstSize.size;
  250. minSize = oEstSize.minSize;
  251. bAggContainsAllGranularityAttributes = oEstSize.bAggContainsAllGranularityAttributes;
  252. }
  253. catch { }
  254. finally
  255. {
  256. if (size != 0)
  257. {
  258. if (minSize != 0 && minSize < size && !bAggContainsAllGranularityAttributes)
  259. {
  260. lblEstimatedSize.Text = agg.Name + " Estimated Size: " + (minSize * 100).ToString("#0.00") + "% to " + (size * 100).ToString("#0.00") + "% of fact data";
  261. lblEstimatedSize.ForeColor = (size > .3333 ? Color.Red : Color.Black);
  262. }
  263. else
  264. {
  265. lblEstimatedSize.Text = agg.Name + " Estimated Size: " + (size * 100).ToString("#0.00") + "% of fact data";
  266. lblEstimatedSize.ForeColor = (size > .3333 ? Color.Red : Color.Black);
  267. }
  268. }
  269. else
  270. {
  271. lblEstimatedSize.Text = agg.Name + " Estimated Size: Unknown (please update EstimatedRows on measure group)";
  272. lblEstimatedSize.ForeColor = Color.Black;
  273. }
  274. }
  275. }
  276. public class EstimatedAggSize
  277. {
  278. public double size = 0;
  279. public double minSize = 0;
  280. public bool bAggContainsAllGranularityAttributes = true;
  281. public Aggregation agg;
  282. }
  283. /// <summary>
  284. /// Returns the estimated size of this aggregation.
  285. /// </summary>
  286. /// <param name="agg"></param>
  287. /// <returns></returns>
  288. public static EstimatedAggSize GetEstimatedSize(Aggregation agg)
  289. {
  290. return GetEstimatedSize(agg, agg.ParentMeasureGroup);
  291. }
  292. /// <summary>
  293. /// Returns the estimated size of this aggregation. Use this signature which takes in the MeasureGroup when the agg is not attached to a ParentMeasureGroup.
  294. /// </summary>
  295. /// <param name="agg"></param>
  296. /// <param name="mg1"></param>
  297. /// <returns></returns>
  298. public static EstimatedAggSize GetEstimatedSize(Aggregation agg, MeasureGroup mg1)
  299. {
  300. double size = 0;
  301. double minSize = 0;
  302. bool bAggContainsAllGranularityAttributes = true;
  303. AggregationAttribute aggAttr;
  304. AggregationDimension aggDim;
  305. double dblAggCardinality = 1;
  306. long iNumSurrogateKeysInAgg = 0;
  307. int iMeasureGroupDimensionsCount = 0;
  308. long lngMaxAggDimensionCardinality = 0;
  309. foreach (MeasureGroupDimension mgDim in mg1.Dimensions)
  310. {
  311. long iDimGranularityCardinality = 0;
  312. double dblDimAggCardinality = 1;
  313. bool bDimAggCardinalityFound = false;
  314. if (!(mgDim is RegularMeasureGroupDimension)) continue; //m2m dimensions apparently aren't stored in the agg since they're calculated at runtime
  315. iMeasureGroupDimensionsCount++; //don't count m2m dimensions
  316. MeasureGroupAttribute granularity = null;
  317. RegularMeasureGroupDimension regMgDim = (RegularMeasureGroupDimension)mgDim;
  318. foreach (MeasureGroupAttribute mgDimAttr in regMgDim.Attributes)
  319. {
  320. if (mgDimAttr.Type == MeasureGroupAttributeType.Granularity)
  321. {
  322. iDimGranularityCardinality = mgDimAttr.Attribute.EstimatedCount;
  323. granularity = mgDimAttr;
  324. break;
  325. }
  326. }
  327. aggDim = agg.Dimensions.Find(mgDim.CubeDimensionID);
  328. if (aggDim == null || granularity == null || aggDim.Attributes.Find(granularity.AttributeID) == null)
  329. bAggContainsAllGranularityAttributes = false;
  330. if (aggDim != null)
  331. {
  332. foreach (CubeAttribute cubeAttr in mgDim.CubeDimension.Attributes)
  333. {
  334. aggAttr = aggDim.Attributes.Find(cubeAttr.AttributeID);
  335. if (aggAttr != null)
  336. {
  337. if (!CanReachAttributeFromChildInAgg(aggAttr, mgDim.Dimension.KeyAttribute, false)) //redundant attributes don't increase the cardinality of the attribute
  338. {
  339. dblDimAggCardinality *= (cubeAttr.Attribute.EstimatedCount == 0 ? 1 : cubeAttr.Attribute.EstimatedCount);
  340. }
  341. bDimAggCardinalityFound = true;
  342. iNumSurrogateKeysInAgg++; //apparently every key, even redundant keys, get stored in the agg
  343. }
  344. }
  345. }
  346. if (dblDimAggCardinality > iDimGranularityCardinality)
  347. {
  348. //shouldn't be more than granularity cardinality because auto-exists prevents that
  349. dblDimAggCardinality = (iDimGranularityCardinality == 0 ? 1 : iDimGranularityCardinality);
  350. }
  351. if (bDimAggCardinalityFound)
  352. {
  353. dblAggCardinality *= dblDimAggCardinality;
  354. if (lngMaxAggDimensionCardinality < dblAggCardinality) lngMaxAggDimensionCardinality = (long)dblDimAggCardinality;
  355. }
  356. }
  357. if (mg1.EstimatedRows != 0 && dblAggCardinality != 0)
  358. {
  359. long iMeasureBytes = 0;
  360. foreach (Microsoft.AnalysisServices.Measure m in mg1.Measures)
  361. {
  362. if (m.DataType == MeasureDataType.Inherited)
  363. {
  364. if (m.Source.DataSize > 0)
  365. iMeasureBytes += m.Source.DataSize;
  366. else if (m.Source.DataType == System.Data.OleDb.OleDbType.Integer)
  367. iMeasureBytes += 4;
  368. else if (m.Source.DataType == System.Data.OleDb.OleDbType.SmallInt)
  369. iMeasureBytes += 2;
  370. else if (m.Source.DataType == System.Data.OleDb.OleDbType.TinyInt)
  371. iMeasureBytes += 1;
  372. else
  373. iMeasureBytes += 8;
  374. }
  375. else
  376. {
  377. if (m.DataType == MeasureDataType.Integer)
  378. iMeasureBytes += 4;
  379. else if (m.DataType == MeasureDataType.SmallInt)
  380. iMeasureBytes += 2;
  381. else if (m.DataType == MeasureDataType.TinyInt)
  382. iMeasureBytes += 1;
  383. else
  384. iMeasureBytes += 8;
  385. }
  386. }
  387. //the size of each row is 4 bytes for each surrogate key plus the size of measures
  388. long lngFactTableRowSize = (iMeasureGroupDimensionsCount * 4 + iMeasureBytes);
  389. long lngAggRowSize = (iNumSurrogateKeysInAgg * 4 + iMeasureBytes);
  390. if (dblAggCardinality > mg1.EstimatedRows) //this is not possible in the data
  391. {
  392. dblAggCardinality = mg1.EstimatedRows;
  393. }
  394. //multiply the estimated rows by the size of each row
  395. size = ((double)(dblAggCardinality * lngAggRowSize)) / ((double)(mg1.EstimatedRows * lngFactTableRowSize));
  396. //purposefully don't prevent size from being over 1 because an agg can be larger than the fact table if it has more dimension attribute keys than the fact table
  397. if (lngMaxAggDimensionCardinality > mg1.EstimatedRows) //this is not possible in the data
  398. {
  399. lngMaxAggDimensionCardinality = mg1.EstimatedRows;
  400. }
  401. //calculate the min size (best case scenario when there is lots of sparsity in fact table) so you can present a range to the user and give the user an idea of the uncertainty
  402. minSize = ((double)(lngMaxAggDimensionCardinality * lngAggRowSize)) / ((double)(mg1.EstimatedRows * lngFactTableRowSize));
  403. }
  404. EstimatedAggSize ret = new EstimatedAggSize();
  405. ret.minSize = minSize;
  406. ret.size = size;
  407. ret.bAggContainsAllGranularityAttributes = bAggContainsAllGranularityAttributes;
  408. ret.agg = agg;
  409. return ret;
  410. }
  411. public static string GetEstimatedAggSizeRange(EstimatedAggSize estimate)
  412. {
  413. if (estimate.size == 0)
  414. {
  415. return null;
  416. }
  417. else if (estimate.minSize != 0 && estimate.minSize < estimate.size && !estimate.bAggContainsAllGranularityAttributes)
  418. {
  419. return (estimate.minSize * 100).ToString("#0.00") + "% to " + (estimate.size * 100).ToString("#0.00") + "%";
  420. }
  421. else
  422. {
  423. return (estimate.size * 100).ToString("#0.00") + "%";
  424. }
  425. }
  426. private static bool CanReachAttributeFromChildInAgg(AggregationAttribute attr, DimensionAttribute current, bool bChildIsInAgg)
  427. {
  428. bChildIsInAgg = bChildIsInAgg || attr.Parent.Attributes.Contains(current.ID);
  429. foreach (AttributeRelationship r in current.AttributeRelationships)
  430. {
  431. if (r.AttributeID == attr.AttributeID && bChildIsInAgg)
  432. {
  433. return true;
  434. }
  435. else
  436. {
  437. if (CanReachAttributeFromChildInAgg(attr, r.Attribute, bChildIsInAgg)) return true;
  438. }
  439. }
  440. return false;
  441. }
  442. Boolean AddAggregationToAggDesign(AggregationDesign aggDesign, string instr, string aggName)
  443. {
  444. try
  445. {
  446. Aggregation agg;
  447. agg = aggDesign.Aggregations.Find(aggName);
  448. if (agg != null)
  449. aggName = "Aggregation " + aggDesign.Aggregations.Count.ToString();
  450. if (aggName == "")
  451. aggName = "Aggregation " + aggDesign.Aggregations.Count.ToString();
  452. agg = aggDesign.Aggregations.Add(aggName, aggName);
  453. string a1;
  454. int dimNum = 0;
  455. int attrNum = 0;
  456. bool newDim = true;
  457. for (int i = 0; i < instr.Length; i++)
  458. {
  459. a1 = instr[i].ToString();
  460. switch (a1)
  461. {
  462. case ",":
  463. dimNum++;
  464. attrNum = -1;
  465. newDim = true;
  466. break;
  467. case "0":
  468. break;
  469. case "1":
  470. if (newDim)
  471. {
  472. agg.Dimensions.Add(dimIDs[dimNum]);
  473. newDim = false;
  474. }
  475. agg.Dimensions[dimIDs[dimNum]].Attributes.Add(dimAttributes[dimNum, attrNum]);
  476. break;
  477. default:
  478. break;
  479. }
  480. attrNum++;
  481. }
  482. return true;
  483. }
  484. catch (Exception ex)
  485. {
  486. MessageBox.Show("Problem saving " + aggName + ": " + ex.Message);
  487. throw new Exception(""); //blank exception means not to report it
  488. }
  489. }
  490. private Aggregation GetAggregationFromString(string aggregationName, string instr)
  491. {
  492. Aggregation agg = new Aggregation();
  493. agg.Name = aggregationName;
  494. string a1;
  495. int dimNum = 0;
  496. int attrNum = 0;
  497. bool newDim = true;
  498. for (int i = 0; i < instr.Length; i++)
  499. {
  500. a1 = instr[i].ToString();
  501. switch (a1)
  502. {
  503. case ",":
  504. dimNum++;
  505. attrNum = -1;
  506. newDim = true;
  507. break;
  508. case "0":
  509. break;
  510. case "1":
  511. if (newDim)
  512. {
  513. agg.Dimensions.Add(dimIDs[dimNum]);
  514. newDim = false;
  515. }
  516. agg.Dimensions[dimIDs[dimNum]].Attributes.Add(dimAttributes[dimNum, attrNum]);
  517. break;
  518. default:
  519. break;
  520. }
  521. attrNum++;
  522. }
  523. return agg;
  524. }
  525. /// <summary>
  526. /// Called to populate tree view representing dimension attributes
  527. /// </summary>
  528. public void PopulateTreeView()
  529. {
  530. treeViewAggregation.SuspendLayout();
  531. treeViewAggregation.Nodes.Clear();
  532. if (!checkBoxRelationships.Checked)
  533. {
  534. foreach (MeasureGroupDimension mgDim in mg1.Dimensions)
  535. {
  536. TreeNode parentNode = treeViewAggregation.Nodes.Add(mgDim.CubeDimensionID, mgDim.CubeDimension.Name);
  537. if (mgDim is ManyToManyMeasureGroupDimension)
  538. parentNode.StateImageIndex = 3;
  539. else
  540. parentNode.StateImageIndex = 2;
  541. foreach (CubeAttribute cubeDimAttr in mgDim.CubeDimension.Attributes)
  542. {
  543. TreeNode childNode = parentNode.Nodes.Add(cubeDimAttr.AttributeID, cubeDimAttr.Attribute.Name);
  544. if (!cubeDimAttr.AttributeHierarchyEnabled)
  545. {
  546. childNode.NodeFont = new Font(treeViewAggregation.Font, FontStyle.Italic);
  547. childNode.ForeColor = Color.Gray;
  548. }
  549. else if (mgDim is ReferenceMeasureGroupDimension)
  550. {
  551. ReferenceMeasureGroupDimension refDim = (ReferenceMeasureGroupDimension)mgDim;
  552. if (refDim.Materialization == ReferenceDimensionMaterialization.Indirect)
  553. {
  554. childNode.ForeColor = Color.Red;
  555. }
  556. }
  557. else if (cubeDimAttr.Attribute.Usage == AttributeUsage.Parent)
  558. {
  559. childNode.ForeColor = Color.Red;
  560. }
  561. }
  562. }
  563. }
  564. else
  565. {
  566. foreach (MeasureGroupDimension mgDim in mg1.Dimensions)
  567. {
  568. TreeNode parentNode = treeViewAggregation.Nodes.Add(mgDim.CubeDimensionID, mgDim.CubeDimension.Name);
  569. if (mgDim is ManyToManyMeasureGroupDimension)
  570. parentNode.StateImageIndex = 3;
  571. else
  572. parentNode.StateImageIndex = 2;
  573. foreach (CubeAttribute cubeDimAttr in mgDim.CubeDimension.Attributes)
  574. if (cubeDimAttr.Attribute.Usage == AttributeUsage.Key)
  575. {
  576. parentNode = parentNode.Nodes.Add(cubeDimAttr.AttributeID, cubeDimAttr.Attribute.Name);
  577. AddTreeViewNodeChildren(parentNode, cubeDimAttr,mgDim);
  578. }
  579. }
  580. }
  581. treeViewAggregation.ExpandAll();
  582. treeViewAggregation.Nodes[0].EnsureVisible(); //scroll to top
  583. treeViewAggregation.ResumeLayout(false);
  584. }
  585. /// <summary>
  586. /// Recursive function. Adds nodes to the tree view control.
  587. /// Adds nodes accourding to attribute relationships
  588. /// </summary>
  589. private void AddTreeViewNodeChildren(TreeNode node, CubeAttribute cubeDimAttr , MeasureGroupDimension mgDim)
  590. {
  591. bool bIsAtOrAboveGranularity = ValidateAggs.IsAtOrAboveGranularity(cubeDimAttr.Attribute, mgDim);
  592. if (!bIsAtOrAboveGranularity)
  593. {
  594. node.ForeColor = belowGranularityColor;
  595. }
  596. else if (mgDim is ReferenceMeasureGroupDimension)
  597. {
  598. ReferenceMeasureGroupDimension refDim = (ReferenceMeasureGroupDimension)mgDim;
  599. if (refDim.Materialization == ReferenceDimensionMaterialization.Indirect)
  600. {
  601. node.ForeColor = nonMaterializedColor;
  602. }
  603. }
  604. else if (cubeDimAttr.Attribute.Usage == AttributeUsage.Parent)
  605. {
  606. node.ForeColor = parentChildAttributeColor;
  607. }
  608. foreach (AttributeRelationship attRel in cubeDimAttr.Attribute.AttributeRelationships)
  609. {
  610. CubeAttribute childAttr = cubeDimAttr.Parent.Attributes.Find(attRel.AttributeID);
  611. if (childAttr == null) break;
  612. TreeNode childNode = node.Nodes.Add(childAttr.AttributeID, childAttr.Attribute.Name);
  613. if (!childAttr.AttributeHierarchyEnabled)
  614. {
  615. childNode.NodeFont = new Font(treeViewAggregation.Font, FontStyle.Italic);
  616. childNode.ForeColor = Color.Gray;
  617. }
  618. childNode.Tag = attRel;
  619. AddTreeViewNodeChildren( childNode, childAttr,mgDim);
  620. }
  621. }
  622. private void EditAggs_Load(object sender, EventArgs e)
  623. {
  624. //Add menu item allowing for adding new aggregations to agg design
  625. MenuItem item1 = new MenuItem("Add Aggregation", new EventHandler(AddAggregationHandler));
  626. ContextMenu menu = new ContextMenu(new MenuItem[] { item1});
  627. this.dataGrid1.ContextMenu = menu;
  628. dataGrid1_Click(sender, e);
  629. //Add Expand/Collapse Events after everything else is loaded
  630. //this.treeViewAggregation.AfterCollapse += new System.Windows.Forms.TreeViewEventHandler(this.treeViewAggregation_AfterExpandOrCollapse);
  631. this.treeViewAggregation.BeforeExpand += new System.Windows.Forms.TreeViewCancelEventHandler(this.treeViewAggregation_ExpandOrCollapse);
  632. this.treeViewAggregation.BeforeCollapse += new System.Windows.Forms.TreeViewCancelEventHandler(this.treeViewAggregation_ExpandOrCollapse);
  633. //this.treeViewAggregation.AfterExpand += new System.Windows.Forms.TreeViewEventHandler(this.treeViewAggregation_AfterExpandOrCollapse);
  634. }
  635. void AddAggregationHandler(object sender, EventArgs e)
  636. {
  637. DataView myDataView;
  638. myDataView = (DataView)dataGrid1.DataSource;
  639. String strAgg = "";
  640. int i = 0, j = 0;
  641. while (dimNames[i] != null)
  642. {
  643. j = 0;
  644. while (dimAttributes[i, j] != null)
  645. {
  646. strAgg = strAgg + "0";
  647. j++;
  648. }
  649. i++;
  650. strAgg = strAgg + ",";
  651. }
  652. strAgg = strAgg.Remove(strAgg.Length - 1);
  653. DataRow NewRow = myDataView.Table.NewRow();
  654. NewRow["Aggregations"] = strAgg;
  655. NewRow["Name"] = "Aggregation " + myDataView.Table.Rows.Count.ToString();
  656. myDataView.Table.Rows.Add(NewRow);
  657. dataGrid1.CurrentRowIndex = NewRow.Table.Rows.IndexOf(NewRow);
  658. dataGrid1_Click(null, null);
  659. }
  660. /// <summary>
  661. /// Helps select the correct data source row even when grid is sorted
  662. /// </summary>
  663. /// <returns></returns>
  664. private DataRow GetCurrentDataGridRow()
  665. {
  666. DataView myDataView = (DataView)dataGrid1.DataSource;
  667. return myDataView.Table.Select(null, myDataView.Sort)[dataGrid1.CurrentRowIndex];
  668. }
  669. /// <summary>
  670. /// Helps synchronizing aggregation definition in current row data grid control to
  671. /// aggregation definition presented as checked boxes for appropriate attributes in
  672. /// tree view control
  673. /// </summary>
  674. private void dataGrid1_Click(object sender, EventArgs e)
  675. {
  676. if (sychContr == SynchControls.SynchGridToTreeView)
  677. return;
  678. try
  679. {
  680. DataRow dr = GetCurrentDataGridRow();
  681. String strAgg = dr[1].ToString();
  682. String strAggName = dr[0].ToString();
  683. if (checkBoxRelationships.Checked)
  684. {
  685. sychContr = SynchControls.SynchGridToTreeView;
  686. if (CheckOffTreeViewAndReturnRigid(strAgg, true))
  687. dr[2] = "Rigid";
  688. else
  689. dr[2] = "Flexible";
  690. }
  691. else
  692. {
  693. int i = 0;
  694. foreach (TreeNode parentNode in treeViewAggregation.Nodes)
  695. {
  696. foreach (TreeNode childNode in parentNode.Nodes)
  697. {
  698. if (strAgg[i].ToString() == "1")
  699. {
  700. sychContr = SynchControls.SynchGridToTreeView;
  701. childNode.Checked = true;
  702. childNode.BackColor = dataGrid1.HeaderBackColor;
  703. }
  704. else
  705. {
  706. sychContr = SynchControls.SynchGridToTreeView;
  707. childNode.Checked = false;
  708. childNode.BackColor = treeViewAggregation.BackColor;
  709. }
  710. i++;
  711. }
  712. i++;
  713. }
  714. }
  715. SetEstimatedSize(GetAggregationFromString(strAggName, strAgg));
  716. txtSummary.Text = GetCheckedNodeText(treeViewAggregation.Nodes);
  717. }
  718. catch (Exception ex)
  719. {
  720. System.Diagnostics.Debug.WriteLine(ex.Message + "\r\n" + ex.StackTrace);
  721. }
  722. sychContr = SynchControls.Unknown;
  723. }
  724. private string GetCheckedNodeText(TreeNodeCollection nodes)
  725. {
  726. string text = "";
  727. foreach (TreeNode tn in nodes)
  728. {
  729. if (tn.Checked)
  730. {
  731. int iCntMatchingName = 0;
  732. foreach (MeasureGroupDimension mgd in mg1.Dimensions)
  733. {
  734. foreach (CubeAttribute ca in mgd.CubeDimension.Attributes)
  735. {
  736. if (ca.AttributeHierarchyEnabled && ca.Attribute.AttributeHierarchyEnabled && string.Compare(ca.Attribute.Name, tn.Text, true) == 0)
  737. {
  738. iCntMatchingName++;
  739. }
  740. }
  741. }
  742. if (iCntMatchingName > 1)
  743. {
  744. //put the cube dimension name on the end to disambiguate
  745. TreeNode parentNode = tn;
  746. while (parentNode.Parent != null) //loop until you hit the dimension
  747. parentNode = parentNode.Parent;
  748. text += tn.Text + " (" + parentNode.Text + ")\r\n";
  749. }
  750. else
  751. {
  752. text += tn.Text + "\r\n";
  753. }
  754. }
  755. if (tn.Nodes.Count > 0)
  756. {
  757. text += GetCheckedNodeText(tn.Nodes);
  758. }
  759. }
  760. return text;
  761. }
  762. private void dataGrid1_CurrentCellChanged(object sender, EventArgs e)
  763. {
  764. dataGrid1_Click(sender, e);
  765. }
  766. /// <summary>
  767. /// Main function called when user checks/uncheckes dimension attribute.
  768. /// If attribute checked it needs to be added to the string representation of an aggregations
  769. /// </summary>
  770. private void treeViewAggregation_AfterCheck(object sender, TreeViewEventArgs e)
  771. {
  772. // If happen to synch Tree to grid, we should quit
  773. if (sychContr == SynchControls.SynchTreeToGrid)
  774. return;
  775. if (e.Action == TreeViewAction.Unknown && !boolHandleClick ) return;
  776. if (e.Node.Parent == null)
  777. return;
  778. DataRow dr = GetCurrentDataGridRow();
  779. String strAgg = dr[1].ToString();
  780. int i = 0;
  781. int intAttrCount = 0;
  782. TreeNode parent = e.Node.Parent;
  783. while (parent.Parent != null)
  784. {
  785. parent = parent.Parent;
  786. }
  787. if (parent == null) MessageBox.Show("Cannot find parent");
  788. string parentName = parent.Name;
  789. while (treeViewAggregation.Nodes[i].Name != parentName)
  790. {
  791. intAttrCount++;
  792. intAttrCount += mg1.Dimensions[treeViewAggregation.Nodes[i].Name].CubeDimension.Attributes.Count;
  793. i++;
  794. }
  795. if (checkBoxRelationships.Checked)
  796. {
  797. //Find a place of an attribute in the attr string
  798. int j = 0;
  799. foreach (CubeAttribute cubeDimAttr in mg1.Dimensions[treeViewAggregation.Nodes[i].Name].CubeDimension.Attributes)
  800. {
  801. if (cubeDimAttr.AttributeID == e.Node.Name)
  802. intAttrCount += j;
  803. j++;
  804. }
  805. }
  806. else
  807. {
  808. intAttrCount += e.Node.Index;
  809. }
  810. if (e.Node.Checked)
  811. {
  812. strAgg = strAgg.Insert(intAttrCount, "1");
  813. strAgg = strAgg.Remove(intAttrCount + 1, 1);
  814. System.Drawing.Color ii = dataGrid1.HeaderBackColor;
  815. e.Node.BackColor = ii;
  816. }
  817. else
  818. {
  819. strAgg = strAgg.Insert(intAttrCount, "0");
  820. strAgg = strAgg.Remove(intAttrCount + 1, 1);
  821. e.Node.BackColor = treeViewAggregation.BackColor;
  822. }
  823. dr[1] = strAgg;
  824. if (sychContr == SynchControls.SynchTreeToGrid)
  825. dataGrid1_Click(null, null);
  826. sychContr = SynchControls.Unknown;
  827. }
  828. private void checkBoxRelationships_CheckedChanged(object sender, EventArgs e)
  829. {
  830. PopulateTreeView();
  831. DataView myDataView;
  832. myDataView = (DataView)dataGrid1.DataSource;
  833. boolHandleClick = true;
  834. int i = 0;
  835. foreach (DataRow dataGridRow in myDataView.Table.Rows)
  836. {
  837. dataGrid1.CurrentRowIndex = i;
  838. if (checkBoxRelationships.Checked)
  839. {
  840. dataGrid1_Click(null, null);
  841. }
  842. else
  843. {
  844. dataGridRow[2] = string.Empty; //not rigid or flexible since we're not in relationships view
  845. }
  846. i++;
  847. }
  848. boolHandleClick = false;
  849. dataGrid1_Click(sender, e);
  850. }
  851. /// <summary>
  852. /// Takes aggregation string as an input and interates over nodes in tree view control
  853. /// if attribute participates in aggregation, function will check the check box for
  854. /// the attribute
  855. /// </summary>
  856. private bool CheckOffTreeViewAndReturnRigid(string strAgg, bool bSetTreeChecks)
  857. {
  858. string a1;
  859. int dimNum = 0;
  860. int attrNum = 0;
  861. bool newDim = true;
  862. boolIsRigid = true;
  863. TreeNode dimNode = treeViewAggregation.Nodes[0];
  864. TreeNode attrNode;
  865. for (int i = 0; i < strAgg.Length; i++)
  866. {
  867. a1 = strAgg[i].ToString();
  868. switch (a1)
  869. {
  870. case ",":
  871. dimNum++;
  872. attrNum = -1;
  873. newDim = true;
  874. dimNode = treeViewAggregation.Nodes[dimNum];
  875. break;
  876. case "0":
  877. if (bSetTreeChecks)
  878. {
  879. attrNode = FindChildNode(dimNode, dimAttributes[dimNum, attrNum]);
  880. if (attrNode != null)
  881. {
  882. attrNode.Checked = false;
  883. attrNode.BackColor = treeViewAggregation.BackColor;
  884. }
  885. }
  886. break;
  887. case "1":
  888. if (newDim) newDim = false;
  889. attrNode = FindChildNodeAndRelationships(dimNode, dimAttributes[dimNum, attrNum]);
  890. if (attrNode != null && bSetTreeChecks)
  891. {
  892. attrNode.Checked = true;
  893. attrNode.BackColor = dataGrid1.HeaderBackColor;
  894. }
  895. break;
  896. default:
  897. break;
  898. }
  899. attrNum++;
  900. }
  901. if (bSetTreeChecks)
  902. treeViewAggregation.ExpandAll();
  903. return boolIsRigid;
  904. }
  905. private TreeNode FindChildNodeAndRelationships(TreeNode node, string strNodeID)
  906. {
  907. TreeNode returnNode;
  908. foreach (TreeNode childNode in node.Nodes)
  909. {
  910. if (childNode.Name == strNodeID)
  911. {
  912. if (childNode.Tag != null)
  913. if (((AttributeRelationship)childNode.Tag).RelationshipType == RelationshipType.Flexible)
  914. boolIsRigid = false;
  915. return childNode;
  916. }
  917. returnNode = FindChildNodeAndRelationships(childNode, strNodeID);
  918. if (returnNode != null)
  919. {
  920. if (node.Tag != null)
  921. if (((AttributeRelationship)node.Tag).RelationshipType == RelationshipType.Flexible)
  922. boolIsRigid = false;
  923. return returnNode;
  924. }
  925. }
  926. return null;
  927. }
  928. private TreeNode FindChildNode(TreeNode node, string strNodeID)
  929. {
  930. TreeNode returnNode;
  931. foreach (TreeNode childNode in node.Nodes)
  932. {
  933. if (childNode.Name == strNodeID)
  934. return childNode;
  935. returnNode = FindChildNode(childNode, strNodeID);
  936. if (returnNode != null)
  937. return returnNode;
  938. }
  939. return null;
  940. }
  941. /// <summary>
  942. /// Goes over every aggregation in the aggregation design and eliminates
  943. /// redundant attributes from every aggregation
  944. /// Redundant is an attribute in aggregation that in the attribute relationship chain appears on top
  945. /// of another attribute.
  946. /// For example State attribute should not be selected if City attribute appears in the aggregation.
  947. /// </summary>
  948. private void buttonOptimizeAgg_Click(object sender, EventArgs e)
  949. {
  950. if (checkBoxRelationships.Checked == false)
  951. {
  952. MessageBox.Show("Please switch to relationships view");
  953. return;
  954. }
  955. this.Cursor = Cursors.WaitCursor;
  956. DataView myDataView;
  957. myDataView = (DataView)dataGrid1.DataSource;
  958. boolHandleClick = true;
  959. int i = 0;
  960. foreach( DataRow dataGridRow in myDataView.Table.Rows)
  961. {
  962. dataGrid1.CurrentRowIndex = i;
  963. dataGrid1_Click(null,null);
  964. foreach (TreeNode node in treeViewAggregation.Nodes)
  965. OptimizeNode(node, false);
  966. dataGrid1_Click(null, null); //reset Rigid
  967. i++;
  968. }
  969. boolHandleClick = false;
  970. DataRow dr = GetCurrentDataGridRow();
  971. SetEstimatedSize(GetAggregationFromString(dr[0].ToString(), dr[1].ToString()));
  972. txtSummary.Text = GetCheckedNodeText(treeViewAggregation.Nodes);
  973. this.Cursor = Cursors.Default;
  974. }
  975. private void OptimizeNode(TreeNode node, bool removeChecks)
  976. {
  977. bool localRemoveChecks = removeChecks;
  978. foreach (TreeNode childNode in node.Nodes)
  979. {
  980. localRemoveChecks = removeChecks;
  981. if (childNode.Checked == true)
  982. if (removeChecks) childNode.Checked = false;
  983. else localRemoveChecks = true;
  984. OptimizeNode(childNode, localRemoveChecks);
  985. }
  986. }
  987. private void buttonEliminateDupe_Click(object sender, EventArgs e)
  988. {
  989. DataView myDataView;
  990. myDataView = (DataView)dataGrid1.DataSource;
  991. int i = 0;
  992. List<string> uniqueAggs = new List<string>();
  993. while (i < myDataView.Table.Rows.Count)
  994. {
  995. if (uniqueAggs.Contains(myDataView.Table.Rows[i].ItemArray[1].ToString()))
  996. {
  997. myDataView.Table.Rows.Remove(myDataView.Table.Rows[i]);
  998. }
  999. else
  1000. {
  1001. uniqueAggs.Add(myDataView.Table.Rows[i].ItemArray[1].ToString());
  1002. i++;
  1003. }
  1004. }
  1005. }
  1006. private void treeViewAggregation_Click(object sender, EventArgs e)
  1007. {
  1008. if (e is MouseEventArgs)
  1009. {
  1010. if (!boolInExpandOrCollapse)
  1011. {
  1012. MouseEventArgs me = (MouseEventArgs)e;
  1013. TreeNode node = treeViewAggregation.GetNodeAt(me.Location);
  1014. if (node.StateImageIndex >= 2) //this node is a dimension, so ignore clicks
  1015. return;
  1016. if (node.NodeFont != null && node.NodeFont.Italic && !node.Checked)
  1017. {
  1018. MessageBox.Show("The cube dimension attribute " + node.Text + " is marked AttributeHierarchyEnabled=false");
  1019. }
  1020. else if (node.ForeColor == nonMaterializedColor && !node.Checked) //show this warning unless they're unchecking it
  1021. {
  1022. MessageBox.Show("This dimension is related through a non-materialized reference relationship\n\nCreating aggregations on such a relationship is not valid is it can produce incorrect figures.");
  1023. }
  1024. else if (node.ForeColor == parentChildAttributeColor && !node.Checked) //show this warning unless they're unchecking it
  1025. {
  1026. MessageBox.Show("This attribute is a parent-child attribute. Aggregations are not allowed on it.");
  1027. }
  1028. else if (node.ForeColor == belowGranularityColor && !node.Checked) //show this warning unless they're unchecking it
  1029. {
  1030. MessageBox.Show("This attribute is below granularity. Aggregations are not allowed on it.");
  1031. }
  1032. else
  1033. {
  1034. bool bFlipCheck = true;
  1035. TreeNode dimensionNode = node.Parent;
  1036. while (dimensionNode.Parent != null)
  1037. dimensionNode = dimensionNode.Parent;
  1038. MeasureGroupDimension mgDim = mg1.Dimensions.Find(dimensionNode.Name);
  1039. if (!node.Checked && mgDim is ManyToManyMeasureGroupDimension)
  1040. {
  1041. //check that all the joining dimensions are in the agg, if not, offer to do that
  1042. //also suggest not including the m2m dimension in the agg
  1043. DataRow currentDR = GetCurrentDataGridRow();
  1044. String strAgg = currentDR[1].ToString();
  1045. String strAggName = currentDR[0].ToString();
  1046. Aggregation agg = GetAggregationFromString(strAggName, strAgg);
  1047. ManyToManyMeasureGroupDimension m2mDim = (ManyToManyMeasureGroupDimension)mgDim;
  1048. MeasureGroup intermediateMG = m2mDim.MeasureGroup;
  1049. List<MeasureGroupAttribute> missing = new List<MeasureGroupAttribute>();
  1050. foreach (MeasureGroupDimension commonDim in intermediateMG.Dimensions)
  1051. {
  1052. if (!mgDim.Parent.Dimensions.Contains(commonDim.CubeDimensionID)) continue; //this isn't a shared dimension
  1053. MeasureGroupDimension dataMeasureGroupDim = mgDim.Parent.Dimensions[commonDim.CubeDimensionID];
  1054. if (dataMeasureGroupDim is ManyToManyMeasureGroupDimension) continue; //this shared dimension is m2m on the data measure group so don't include it
  1055. RegularMeasureGroupDimension regCommonDim = commonDim as RegularMeasureGroupDimension;
  1056. if (com

Large files files are truncated, but you can click here to view the full file