/// <summary> /// Add cube dimension (instead of cube db) /// </summary> /// <param name="cubedb"></param> /// <param name="cube"></param> /// <param name="dimID"></param> /// <param name="dimension_type"></param> /// <param name="cube_dimName"></param> /// <param name="visible"></param> internal static void ADD_CUBE_DIMENSION( DB_SQLHELPER_BASE sqlHelper, Database cubedb, Cube cube, String dimID, String dimension_type, String cube_dimName = "", bool visible = true) { Dimension dim = cubedb.Dimensions.Find(dimID); if (dim == null) { sqlHelper.ADD_MESSAGE_LOG( String.Format("Detected dimension name [{0}] is not existed in current cube db", cube_dimName), MESSAGE_TYPE.DIMENSION, MESSAGE_RESULT_TYPE.Warning); } CubeDimension cube_dim = cube.Dimensions.Add(dim.ID); cube_dim.Visible = visible; cube_dim.Name = dim.Name; sqlHelper.ADD_MESSAGE_LOG( String.Format("Added dimension [{0}] into cube (instead of cube db)", cube.Dimensions.FindByName(dim.Name).Name), MESSAGE_TYPE.DIMENSION, MESSAGE_RESULT_TYPE.Succeed); }
/// <summary> /// Add regular dim usage /// </summary> /// <param name="cube"></param> /// <param name="measureGroup"></param> /// <param name="factDataItem"></param> /// <param name="dimID"></param> /// <param name="dimAttrId"></param> /// <returns>Regular dim usage</returns> internal static RegularMeasureGroupDimension ADD_DIM_USAGE_REGULAR_RELATIONSHIP( DB_SQLHELPER_BASE sqlHelper, Cube cube, MeasureGroup measureGroup, DataItem factDataItem, String dimID, String dimAttrId) { RegularMeasureGroupDimension regMgDim = null; CubeDimension curDim = cube.Dimensions.Find(dimID); if (curDim == null) { sqlHelper.ADD_MESSAGE_LOG( String.Format("Cann't find dimension {0}", dimID), MESSAGE_TYPE.DIM_USAGE_REGULAR, MESSAGE_RESULT_TYPE.Error); } regMgDim = new RegularMeasureGroupDimension(curDim.Name); regMgDim.CubeDimensionID = dimID; measureGroup.Dimensions.Add(regMgDim); MeasureGroupAttribute mgAttr = regMgDim.Attributes.Add(dimAttrId); mgAttr.Type = MeasureGroupAttributeType.Granularity; mgAttr.KeyColumns.Add(factDataItem); return(regMgDim); }
public void CreateMiningModel() { //connecting the server and database Server myServer = new Server(); myServer.Connect("DataSource=localhost;Catalog=FoodMart"); Database myDatabase = myServer.Databases["FoodMart"]; Cube myCube = myDatabase.Cubes["FoodMart 2000"]; CubeDimension myDimension = myCube.Dimensions["Customer"]; Microsoft.AnalysisServices.MiningStructure myMiningStructure = myDatabase.MiningStructures.Add("CustomerSegement", "CustomerSegement"); //Bind the mining structure to a cube. myMiningStructure.Source = new CubeDimensionBinding(".", myCube.ID, myDimension.ID); // Create the key column. CubeAttribute customerKey = myCube.Dimensions["Customer"].Attributes["Customer"]; ScalarMiningStructureColumn keyStructureColumn = CreateMiningStructureColumn(customerKey, true); myMiningStructure.Columns.Add(keyStructureColumn); //Member Card attribute CubeAttribute memberCard = myCube.Dimensions["Customer"].Attributes["Member Card"]; ScalarMiningStructureColumn memberCardStructureColumn = CreateMiningStructureColumn(memberCard, false); myMiningStructure.Columns.Add(memberCardStructureColumn); //Total Children attribute CubeAttribute totalChildren = myCube.Dimensions["Customer"].Attributes["Total Children"]; ScalarMiningStructureColumn totalChildrenStructureColumn = CreateMiningStructureColumn(totalChildren, false); myMiningStructure.Columns.Add(totalChildrenStructureColumn); //Store Sales measure ToDo: fix this! //Microsoft.AnalysisServices.Measure storeSales = myCube.MeasureGroups[0].Measures["Store Sales"]; //ScalarMiningStructureColumn storeSalesStructureColumn = CreateMiningStructureColumn(storeSales, false); //myMiningStructure.Columns.Add(storeSalesStructureColumn); //Create a mining model from the mining structure. By default, all the //structure columns are used. Nonkey columns are with usage input Microsoft.AnalysisServices.MiningModel myMiningModel = myMiningStructure.CreateMiningModel(true, "CustomerSegment"); //Set the algorithm to be clustering. myMiningModel.Algorithm = MiningModelAlgorithms.MicrosoftClustering; //Process structure and model try { myMiningStructure.Update(UpdateOptions.ExpandFull); myMiningStructure.Process(ProcessType.ProcessFull); } catch (Microsoft.AnalysisServices.OperationException e) { string err = e.Message; } }
///<summary> ///</summary> ///<param name="dataPoints"></param> ///<param name="dimension"></param> ///<returns></returns> public static IEnumerable GetDimension1Vector(IEnumerable <PricingStructurePoint> dataPoints, CubeDimension dimension) { var result = GetDimensionValues(dataPoints, dimension); return(result); }
public DimensionUsage(string relationshipType, MeasureGroup mg, CubeDimension dimCube, Dimension dim) //, string factTableColumn, string attributeColumn) { if (mg != null) { mCubeName = mg.Parent.Name; mDatabaseName = mg.Parent.Parent.Name; mMeasureGroup = mg.Name; } else if (dimCube != null) { mCubeName = dimCube.Parent.Name; mDatabaseName = dimCube.Parent.Parent.Name; } if (dimCube != null) { mDimensionName = dimCube.Name; if (dimCube.Name != dim.Name) { mDimensionName += " (" + dim.Name + ")"; } } mRelationshipType = relationshipType; //mFactTableColumnName = factTableColumn; //mAttributeColumnName = attributeColumn; }
private void Dimension_SelectedIndexChanged(object sender, EventArgs e) { if (ASDatabase.SelectedIndex > -1 && Cube.SelectedIndex > -1 && Dimension.SelectedIndex > -1) { using (Server S = new Server()) { S.Connect(connectionString); if (rbDimension.Checked == true) { CubeDimension CD = S.Databases.GetByName(ASDatabase.SelectedItem.ToString()).Cubes.GetByName(Cube.SelectedItem.ToString()).Dimensions.GetByName(Dimension.SelectedItem.ToString()); Dimension D = CD.Dimension; DataTable AT = GetAttributesByDimension(D); //dataGridView1.Columns.Remove("Attributes List"); dataGridView1.DataSource = AT; //dataGridView1.DataBind(); } if (rbMeasureGroups.Checked == true) { MeasureGroup MG = S.Databases.GetByName(ASDatabase.SelectedItem.ToString()).Cubes.GetByName(Cube.SelectedItem.ToString()).MeasureGroups.GetByName(Dimension.SelectedItem.ToString()); MeasureCollection MC = MG.Measures; DataTable MA = getMeasureAttributes(MC); dataGridView1.DataSource = MA; DataTable PT = getMeasureGroupPartitions(MG); PartitionsGrid.DataSource = PT; } } } }
//, string factTableColumn, string attributeColumn) public DimensionUsage(string relationshipType, MeasureGroup mg, CubeDimension dimCube, Dimension dim) { mCubeName = mg.Parent.Name; mDatabaseName = mg.Parent.Parent.Name; mDimensionName = dimCube.Name; if (dimCube.Name != dim.Name) mDimensionName += " (" + dim.Name + ")"; mMeasureGroup = mg.Name; mRelationshipType = relationshipType; //mFactTableColumnName = factTableColumn; //mAttributeColumnName = attributeColumn; }
private void Dimension_SelectedIndexChanged(object sender, EventArgs e) { using (Server S = new Server()) { string connectionString; connectionString = "DataSource=" + ServerName.Text; S.Connect(connectionString); CubeDimension CD = S.Databases.GetByName(ASDatabase.SelectedItem.ToString()).Cubes.GetByName(Cube.SelectedItem.ToString()).Dimensions.GetByName(Dimension.SelectedItem.ToString()); Dimension D = CD.Dimension; DataTable AT = GetAttributesByDimension(D); //dataGridView1.Columns.Remove("Attributes List"); dataGridView1.DataSource = AT; //dataGridView1.DataBind(); } }
// Mining sample model private void CreateMarketBasketModel() { CubeAttribute basketAttribute; CubeAttribute itemAttribute; Server myServer = new Server(); myServer.Connect("DataSource=localhost;Catalog=FoodMart"); Database myDatabase = myServer.Databases["FoodMart"]; Cube myCube = myDatabase.Cubes["FoodMart 2000"]; CubeDimension myDimension = myCube.Dimensions["Customer"]; Microsoft.AnalysisServices.MiningStructure myMiningStructure = myDatabase.MiningStructures.Add("MarketBasket", "MarketBasket"); myMiningStructure.Source = new CubeDimensionBinding(".", myCube.ID, myDimension.ID); basketAttribute = myCube.Dimensions["Customer"].Attributes["Customer"]; itemAttribute = myCube.Dimensions["Product"].Attributes["Product"]; //basket structure column ScalarMiningStructureColumn basket = CreateMiningStructureColumn(basketAttribute, true); basket.Name = "Basket"; myMiningStructure.Columns.Add(basket); //item structure column - nested table ScalarMiningStructureColumn item = CreateMiningStructureColumn(itemAttribute, true); item.Name = "Item"; MeasureGroup measureGroup = myCube.MeasureGroups[0]; TableMiningStructureColumn purchases = CreateMiningStructureColumn(measureGroup); purchases.Name = "Purchases"; purchases.Columns.Add(item); myMiningStructure.Columns.Add(purchases); Microsoft.AnalysisServices.MiningModel myMiningModel = myMiningStructure.CreateMiningModel(); myMiningModel.Name = "MarketBasket"; myMiningModel.Columns["Purchases"].Usage = MiningModelColumnUsages.PredictOnly; myMiningModel.Algorithm = MiningModelAlgorithms.MicrosoftAssociationRules; }
//=============================================================// #region Setters //Region in which new Data is set from UI public void ChangeGridCubeDimension(CubeDimension dimension, float newVal) { if (gridCubeDimensions_u == Vector3.zero) { gridCubeDimensions_u = Vector3.one; } if (dimension == CubeDimension.X) { gridCubeDimensions_u.x = newVal; } else if (dimension == CubeDimension.Y) { gridCubeDimensions_u.y = newVal; } else if (dimension == CubeDimension.Z) { gridCubeDimensions_u.z = newVal; } }
/// <summary> /// Generate cube. /// </summary> /// <param name="objCube">Cube instance.</param> /// <param name="objDimension">Dimension instance.</param> /// <param name="objMeasureGroup">MeasureGroup instance.</param> /// <param name="strFactTableName">FactTable Name.</param> /// <param name="strTableKey">Table Key.</param> private static void GenerateCube(Cube objCube, Dimension objDimension, MeasureGroup objMeasureGroup, string strFactTableName, string strTableKey) { try { CubeDimension objCubeDim = new CubeDimension(); RegularMeasureGroupDimension objRegMGDim = new RegularMeasureGroupDimension(); MeasureGroupAttribute objMGA = new MeasureGroupAttribute(); //Add Dimension to the Cube objCubeDim = objCube.Dimensions.Add(objDimension.ID); //Use Regular Relationship Between Dimension and FactTable Measure Group objRegMGDim = objMeasureGroup.Dimensions.Add(objCubeDim.ID); //Link TableKey in DimensionTable with TableKey in FactTable Measure Group objMGA = objRegMGDim.Attributes.Add(objDimension.KeyAttribute.ID); objMGA.Type = MeasureGroupAttributeType.Granularity; objMGA.KeyColumns.Add(strFactTableName, strTableKey, OleDbType.Integer); } catch (Exception ex) { Console.WriteLine("Error in Creating the Cube, MeasureGroup, Measure, and Partition Objects - GenerateCube. Error Message -> " + ex.Message); } }
/// <summary> /// Remove dimension /// </summary> /// <param name="cubedb"></param> /// <param name="cube"></param> /// <param name="dimID"></param> internal static void REMOVE_DIMENSIONS( DB_SQLHELPER_BASE sqlHelper, Database cubedb, Cube cube, String dimID) { CubeDimension cube_dim = cube.Dimensions.Find(dimID); if (cube_dim != null) { cube.Dimensions.Remove(cube_dim); } Dimension dim = cubedb.Dimensions.Find(dimID); if (dim != null) { cubedb.Dimensions.Remove(dim); sqlHelper.ADD_MESSAGE_LOG( String.Format("Delete [{0}] dimension", dim.Name), MESSAGE_TYPE.DIMENSION, MESSAGE_RESULT_TYPE.Succeed); } }
public static List <CubeAttribute> GetCubeAttributesWithSlice(string sQuerySubcubeVerbose, Microsoft.AnalysisServices.MeasureGroup mg) { List <CubeAttribute> list = new List <CubeAttribute>(); int iDimensionIndex = 0; foreach (string sLine in sQuerySubcubeVerbose.Replace("\r\n", "\n").Replace("\r", "\n").Split(new char[] { '\n' })) { if (string.IsNullOrEmpty(sLine.Trim())) { break; //stop when we get to a blank line } System.Text.RegularExpressions.Match match = _regexVerboseCubeDimension.Match(sLine); string sCubeDimensionName = match.Groups["dimension"].Value; CubeDimension cd = mg.Parent.Dimensions.FindByName(sCubeDimensionName); if (cd == null) { continue; } string sCubeDimensionID = cd.ID; string sAttributes = match.Groups["attributes"].Value; string[] arrAttributes = sAttributes.Split(new char[] { ' ' }); for (int iAttribute = 0; iAttribute < arrAttributes.Length && iAttribute < cd.Attributes.Count; iAttribute++) { string sAttributeSlice = arrAttributes[iAttribute]; if (sAttributeSlice == "0" || sAttributeSlice == "1" || sAttributeSlice == "*") { continue; } CubeAttribute ca = cd.Attributes[iAttribute]; list.Add(ca); } iDimensionIndex++; } return(list); }
public TabularItems.Table GetTable(string tableName) { Database database = GetDatabase(this.databaseName); Cube cube = database.Cubes.FindByName(cubeName); if (cube == null) { throw new InvalidOperationException(string.Format( "Cube '{0}' does not exist in database '{1}'", cubeName, database)); } CubeDimension table = cube.Dimensions.FindByName(tableName); if (table == null) { throw new InvalidOperationException(string.Format( "Table '{0}' because it does not exist in cube '{1}'", tableName, cubeName)); } return(new TabularItems.Table(table)); }
/// <summary> /// Formats all the attributes in the dimension into a comment. /// </summary> /// <param name="dimension">Dimension to parse.</param> /// <returns>A comment specific string.</returns> private static string FormatAttributesComment(CubeDimension dimension) { StringBuilder comment = new StringBuilder(); foreach (var attribute in dimension.FindAttributes() .OrderBy(x => x.AttributeHierarchyDisplayFolder) .ThenBy(x => x.Name)) { var displayFolder = FormatDisplayFolder(attribute.AttributeHierarchyDisplayFolder); comment.Append(displayFolder); comment.AppendLine(attribute.Name); Console.WriteLine("\t\t\t{0}{1}", displayFolder, attribute.Name); } return comment.ToString(); }
/* * Create mining structures and models for olap */ public string CreateCubeMiningStructure(string sStructName, string sAlgorithm, int sCubeName, string sDimensionName, string sKeyColumn, List <string> lsInputColumns, List <string> lsPredictColumns, List <string> lsMeasureInput, List <string> lsMeasurePredict, List <bool> lbPredictItems, int parOne, int parTwo) { try { // connect to cube Server objServer = new Server(); objServer.Connect("DataSource=" + sServer + ";Initial Catalog=" + sCatalog); Database objDb = objServer.Databases[sCatalog]; Cube objCube = objDb.Cubes[sCubeName]; DataSourceView myView = objDb.DataSourceViews[0]; // create mining structure CubeDimension objDimension = objCube.Dimensions.GetByName(sDimensionName); // drop the existing structures with the same name Microsoft.AnalysisServices.MiningStructure currentMiningStruct = objDb.MiningStructures.FindByName(sStructName); if (currentMiningStruct != null) { currentMiningStruct.Drop(); } Microsoft.AnalysisServices.MiningStructure myMiningStructure = objDb.MiningStructures.Add(sStructName, sStructName); myMiningStructure.Source = new CubeDimensionBinding(".", objCube.ID, objDimension.ID); /***************** Key column *****************/ // key column CubeAttribute objKey = objCube.Dimensions.GetByName(sDimensionName).Attributes[0]; ScalarMiningStructureColumn objKeyColumn = CreateMiningStructureColumn(objKey, true); objKeyColumn.Name = sKeyColumn; myMiningStructure.Columns.Add(objKeyColumn); /***************** Other columns *****************/ // create mining columns for (int i = 0; i < lsInputColumns.Count; i++) { // get attribute CubeAttribute objAttribute = new CubeAttribute(); objAttribute = objCube.Dimensions.GetByName(sDimensionName).Attributes[0]; // create mining column ScalarMiningStructureColumn objColumn = CreateMiningStructureColumn(objAttribute, false); objColumn.Name = lsInputColumns[i]; myMiningStructure.Columns.Add(objColumn); /***************** Measure column *****************/ // create mining columns for measures for (int j = 0; j < lsMeasureInput.Count; j++) { MeasureGroup objMeasureGroup = objCube.MeasureGroups[lsMeasureInput[j]]; TableMiningStructureColumn objMeasure = CreateMiningStructureColumn(objMeasureGroup); objMeasure.Name = lsMeasureInput[j]; objMeasure.Columns.Add(objColumn); myMiningStructure.Columns.Add(objMeasure); } } /***************** Columns for prediction - will be updated in model *****************/ // create mining columns for (int i = 0; i < lsPredictColumns.Count; i++) { // get attribute CubeAttribute objAttribute = new CubeAttribute(); objAttribute = objCube.Dimensions.GetByName(sDimensionName).Attributes[0]; // if value = false (input & predict) then skip if (lbPredictItems[i] == false) { continue; } else { // create mining column ScalarMiningStructureColumn objColumn = CreateMiningStructureColumn(objAttribute, false); objColumn.Name = lsPredictColumns[i]; myMiningStructure.Columns.Add(objColumn); } } // update myMiningStructure.Update(); /***************** Mining model *****************/ // create mining models CreateMiningModel(myMiningStructure, sStructName, sAlgorithm, lsPredictColumns, lsMeasurePredict, lbPredictItems, parOne, parTwo); // process myMiningStructure.Process(); return("Success"); } catch (Exception e) { return(e.Message); } }
private static List <DimensionUsage> RecurseTabularRelationships(Dimension dMG, MeasureGroup mgOuter, bool bIsBusMatrix) { List <DimensionUsage> list = new List <DimensionUsage>(); foreach (Microsoft.AnalysisServices.Relationship relOuter in dMG.Relationships) { bool bFound = false; MeasureGroup mgFrom = dMG.Parent.Cubes[0].MeasureGroups[relOuter.FromRelationshipEnd.DimensionID]; DimensionAttribute daFrom = dMG.Attributes[relOuter.FromRelationshipEnd.Attributes[0].AttributeID]; Dimension dTo = dMG.Parent.Dimensions[relOuter.ToRelationshipEnd.DimensionID]; DimensionAttribute daTo = dTo.Attributes[relOuter.ToRelationshipEnd.Attributes[0].AttributeID]; CubeDimension dToCube = dMG.Parent.Cubes[0].Dimensions[relOuter.ToRelationshipEnd.DimensionID]; foreach (MeasureGroupDimension mgdOuter in mgFrom.Dimensions) { ReferenceMeasureGroupDimension rmgdOuter = mgdOuter as ReferenceMeasureGroupDimension; if (rmgdOuter != null && rmgdOuter.Materialization == ReferenceDimensionMaterialization.Regular && rmgdOuter.RelationshipID == relOuter.ID) { //active relationships have a materialized reference relationship bFound = true; break; } } string sActiveFlag = "Active"; if (!bFound) { sActiveFlag = "Inactive"; if (bIsBusMatrix) { continue; //don't show inactive relationships in bus matrix view } } DimensionUsage usage = new DimensionUsage(sActiveFlag, mgOuter, dToCube, dTo); usage.Column1Name = "Foreign Key Column"; usage.Column1Value = daFrom.Name; usage.Column2Name = "Primary Key Column"; usage.Column2Value = daTo.Name; bool bFoundVisibleAttribute = false; foreach (DimensionAttribute da in dTo.Attributes) { if (da.AttributeHierarchyVisible) { bFoundVisibleAttribute = true; break; } } if (bFoundVisibleAttribute) //only if the To end has visible attributes should we show it as a dimension { list.Add(usage); } if (bIsBusMatrix) { //recurse if it's the bus matrix view list.AddRange(RecurseTabularRelationships(dTo, mgOuter, bIsBusMatrix)); } } return(list); }
private void SetCubeDimensions() { _XAxis = new CubeDimension(Center.X, Dimension); _YAxis = new CubeDimension(Center.Y, Dimension); _ZAxis = new CubeDimension(Center.Z, Dimension); }
public void AddMiningStructure() { Server srv = new Server(); srv.Connect("DataSource=CLARITY-7HYGMQM\\ANA;Initial Catalog=Adventure Works DW 2008"); Database db = srv.Databases["Adventure Works DW 2008"]; Cube myCube = db.Cubes["Adventure Works"]; CubeDimension myDimension = myCube.Dimensions.GetByName("Customer"); Microsoft.AnalysisServices.MiningStructure myMiningStructure = db.MiningStructures.Add("TestMining", "TestMining"); myMiningStructure.Source = new CubeDimensionBinding(".", myCube.ID, myDimension.ID); // get current mining models // Demo code foreach (Microsoft.AnalysisServices.MiningStructure ms in db.MiningStructures) { Console.WriteLine(ms.Name); foreach (Microsoft.AnalysisServices.MiningModel mm in ms.MiningModels) { Console.WriteLine(mm.Name); } } CubeAttribute basketAttribute; CubeAttribute itemAttribute; basketAttribute = myCube.Dimensions.GetByName("Customer").Attributes[0]; itemAttribute = myCube.Dimensions.GetByName("Product").Attributes[0]; //basket structure column ScalarMiningStructureColumn basket = CreateMiningStructureColumn(basketAttribute, true); basket.Name = "Basket"; myMiningStructure.Columns.Add(basket); //item structure column - nested table ScalarMiningStructureColumn item = CreateMiningStructureColumn(itemAttribute, true); item.Name = "Item"; MeasureGroup measureGroup = myCube.MeasureGroups[0]; TableMiningStructureColumn purchases = CreateMiningStructureColumn(measureGroup); purchases.Name = "Purchases"; purchases.Columns.Add(item); myMiningStructure.Columns.Add(purchases); Microsoft.AnalysisServices.MiningModel myMiningModel = myMiningStructure.CreateMiningModel(); myMiningModel.Name = "MarketBasket"; myMiningModel.Columns["Purchases"].Usage = MiningModelColumnUsages.PredictOnly; myMiningModel.Algorithm = MiningModelAlgorithms.MicrosoftAssociationRules; try { myMiningStructure.Update(UpdateOptions.ExpandFull); myMiningStructure.Process(ProcessType.ProcessFull); } catch (Microsoft.AnalysisServices.OperationException e) { this.sResult = e.StackTrace; Console.WriteLine(e.StackTrace); } }
public override void OnWindowActivated(Window GotFocus, Window LostFocus) { try { package.Log.Debug("TriStatPerspectives OnWindowActivated fired"); if (GotFocus == null) { return; } IDesignerHost designer = GotFocus.Object as IDesignerHost; if (designer == null) { return; } ProjectItem pi = GotFocus.ProjectItem; if ((pi == null) || (!(pi.Object is Cube))) { return; } EditorWindow win = (EditorWindow)designer.GetService(typeof(Microsoft.DataWarehouse.ComponentModel.IComponentNavigator)); VsStyleToolBar toolbar = (VsStyleToolBar)win.SelectedView.GetType().InvokeMember("ToolBar", getflags, null, win.SelectedView, null); Cube cube = (Cube)pi.Object; IntPtr ptr = win.Handle; string sHandle = ptr.ToInt64().ToString(); if (!windowHandlesFixedForPerspectives.ContainsKey(sHandle)) { windowHandlesFixedForPerspectives.Add(sHandle, win); win.ActiveViewChanged += new EventHandler(win_ActiveViewChanged); } //if (win.SelectedView.Caption == "Perspectives") if (win.SelectedView.MenuItemCommandID.ID == (int)BIDSViewMenuItemCommandID.Perspectives) { Microsoft.AnalysisServices.Design.Scripts mdxScriptCache = new Microsoft.AnalysisServices.Design.Scripts(cube); Control perspectiveBuilder = (Control)win.SelectedView.GetType().InvokeMember("ViewControl", getflags, null, win.SelectedView, null); //Microsoft.AnalysisServices.Design.PerspectivesBuilder Control grid = perspectiveBuilder.Controls[0]; //Microsoft.SqlServer.Management.UI.Grid.DlgGridControl if (!windowHandlesFixedForGridEvents.ContainsKey(sHandle)) { grid.MouseClick += new MouseEventHandler(grid_MouseClick); grid.KeyPress += new KeyPressEventHandler(grid_KeyPress); #if !(YUKON || KATMAI) _IsMetroOrGreater = VisualStudioHelpers.IsMetroOrGreater(win); HookCellPaintEvent(grid, true); #endif windowHandlesFixedForGridEvents.Add(sHandle, win); } System.Reflection.BindingFlags getpropertyflags = System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.GetProperty | System.Reflection.BindingFlags.DeclaredOnly | System.Reflection.BindingFlags.Instance; object dlgStorage = null; #if !(YUKON) dlgStorage = grid.GetType().BaseType.BaseType.InvokeMember("DlgStorage", getpropertyflags, null, grid, null); #else dlgStorage = grid.GetType().BaseType.InvokeMember("DlgStorage", getpropertyflags, null, grid, null); //Microsoft.SqlServer.Management.UI.Grid.IDlgStorage #endif object storage = dlgStorage.GetType().InvokeMember("Storage", getpropertyflags, null, dlgStorage, null); //Microsoft.SqlServer.Management.UI.Grid.MemDataStorage System.Reflection.BindingFlags getfieldflags = System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.GetField | System.Reflection.BindingFlags.DeclaredOnly | System.Reflection.BindingFlags.Instance; ArrayList rows = (ArrayList)storage.GetType().BaseType.InvokeMember("m_arrRows", getfieldflags, null, storage, new object[] { }); //ArrayList columns = (ArrayList)storage.GetType().BaseType.InvokeMember("m_arrColumns", getfieldflags, null, storage, new object[] { }); object[] perspectivesColumns = (object[])rows[0]; ArrayList allGridCubeObjects = (ArrayList)perspectiveBuilder.GetType().InvokeMember("allGridCubeObjects", getfieldflags, null, perspectiveBuilder, null); for (int i = 3; i < rows.Count; i++) { object[] columns = (object[])rows[i]; object cell = columns[2]; //Microsoft.SqlServer.Management.UI.Grid.GridCell string rowObjectType = (string)cell.GetType().InvokeMember("CellData", getpropertyflags, null, cell, null); if (rowObjectType == "MeasureGroup") { cell = columns[1]; string rowObjectName = (string)cell.GetType().InvokeMember("CellData", getpropertyflags, null, cell, null); MeasureGroup mg = cube.MeasureGroups.GetByName(rowObjectName); for (int j = 3; j < columns.Length; j += 2) { cell = perspectivesColumns[j + 1]; string perspectiveName = (string)cell.GetType().InvokeMember("CellData", getpropertyflags, null, cell, null); Perspective perspective = cube.Perspectives.GetByName(perspectiveName); cell = columns[j]; bool bHighlight = false; if (perspective.MeasureGroups.Contains(mg.ID)) { PerspectiveMeasureGroup pmg = perspective.MeasureGroups[mg.ID]; foreach (Measure m in mg.Measures) { if (m.Visible && !pmg.Measures.Contains(m.ID)) { bHighlight = true; break; } } } HighlightCell(bHighlight, columns, cell, j); } } else if (rowObjectType == "CubeDimension") { cell = columns[1]; string rowObjectName = (string)cell.GetType().InvokeMember("CellData", getpropertyflags, null, cell, null); CubeDimension cd = cube.Dimensions.GetByName(rowObjectName); for (int j = 3; j < columns.Length; j += 2) { cell = perspectivesColumns[j + 1]; string perspectiveName = (string)cell.GetType().InvokeMember("CellData", getpropertyflags, null, cell, null); Perspective perspective = cube.Perspectives.GetByName(perspectiveName); cell = columns[j]; bool bHighlight = false; if (perspective.Dimensions.Contains(cd.ID)) { PerspectiveDimension pcd = perspective.Dimensions[cd.ID]; foreach (CubeHierarchy h in cd.Hierarchies) { if (h.Visible && h.Enabled && !pcd.Hierarchies.Contains(h.HierarchyID)) { bHighlight = true; break; } } if (!bHighlight) { foreach (CubeAttribute a in cd.Attributes) { if (a.AttributeHierarchyVisible && a.AttributeHierarchyEnabled && a.Attribute.AttributeHierarchyVisible && a.Attribute.AttributeHierarchyEnabled && !pcd.Attributes.Contains(a.AttributeID)) { bHighlight = true; break; } } } } HighlightCell(bHighlight, columns, cell, j); } } else if (rowObjectType == "Kpi") { cell = columns[1]; string rowObjectName = (string)cell.GetType().InvokeMember("CellData", getpropertyflags, null, cell, null); rowObjectName = rowObjectName.Trim(); Kpi kpi = cube.Kpis.GetByName(rowObjectName); for (int j = 3; j < columns.Length; j += 2) { cell = perspectivesColumns[j + 1]; string perspectiveName = (string)cell.GetType().InvokeMember("CellData", getpropertyflags, null, cell, null); Perspective perspective = cube.Perspectives.GetByName(perspectiveName); cell = columns[j]; bool bHighlight = false; if (perspective.Kpis.Contains(kpi.ID)) { PerspectiveKpi pkpi = perspective.Kpis[kpi.ID]; bHighlight = ShouldPerspectiveKpiBeHighlighted(pkpi, mdxScriptCache); } HighlightCell(bHighlight, columns, cell, j); } } else if (rowObjectType.EndsWith("Action")) { cell = columns[1]; string rowObjectName = (string)cell.GetType().InvokeMember("CellData", getpropertyflags, null, cell, null); rowObjectName = rowObjectName.Trim(); Microsoft.AnalysisServices.Action action = cube.Actions.GetByName(rowObjectName); for (int j = 3; j < columns.Length; j += 2) { cell = perspectivesColumns[j + 1]; string perspectiveName = (string)cell.GetType().InvokeMember("CellData", getpropertyflags, null, cell, null); Perspective perspective = cube.Perspectives.GetByName(perspectiveName); cell = columns[j]; bool bHighlight = false; if (perspective.Actions.Contains(action.ID)) { bHighlight = true; if (action.TargetType == ActionTargetType.DimensionMembers) { foreach (PerspectiveDimension dim in perspective.Dimensions) { if (string.Compare(action.Target, "[" + dim.CubeDimension.Name + "]", true) == 0) { bHighlight = false; break; } } } else if (action.TargetType == ActionTargetType.AttributeMembers || action.TargetType == ActionTargetType.HierarchyMembers) { foreach (PerspectiveDimension dim in perspective.Dimensions) { foreach (PerspectiveAttribute attr in dim.Attributes) { if (string.Compare(action.Target, "[" + attr.Parent.CubeDimension.Name + "].[" + attr.Attribute.Name + "]", true) == 0) { bHighlight = false; break; } } } } else if (action.TargetType == ActionTargetType.Level || action.TargetType == ActionTargetType.LevelMembers) { foreach (PerspectiveDimension dim in perspective.Dimensions) { foreach (PerspectiveHierarchy hier in dim.Hierarchies) { foreach (Level level in hier.Hierarchy.Levels) { if (string.Compare(action.Target, "[" + hier.Parent.CubeDimension.Name + "].[" + hier.Hierarchy.Name + "].[" + level.Name + "]", true) == 0) { bHighlight = false; break; } } } } } else if (action.TargetType == ActionTargetType.HierarchyMembers) { foreach (PerspectiveDimension dim in perspective.Dimensions) { foreach (PerspectiveHierarchy hier in dim.Hierarchies) { if (string.Compare(action.Target, "[" + hier.Parent.CubeDimension.Name + "].[" + hier.Hierarchy.Name + "]", true) == 0) { bHighlight = false; break; } } } } else { bHighlight = false; } } HighlightCell(bHighlight, columns, cell, j); } } else if (rowObjectType == "CalculatedMember") { cell = columns[1]; string rowObjectName = (string)cell.GetType().InvokeMember("CellData", getpropertyflags, null, cell, null); rowObjectName = rowObjectName.Trim(); for (int j = 3; j < columns.Length; j += 2) { cell = perspectivesColumns[j + 1]; string perspectiveName = (string)cell.GetType().InvokeMember("CellData", getpropertyflags, null, cell, null); Perspective perspective = cube.Perspectives.GetByName(perspectiveName); cell = columns[j]; Microsoft.AnalysisServices.Design.Script calc = (Microsoft.AnalysisServices.Design.Script)allGridCubeObjects[i].GetType().InvokeMember("Object", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.GetField, null, allGridCubeObjects[i], null); if (CalcIsHidden(calc)) { continue; } bool bHighlight = false; if (calc != null && calc.CalculationProperty != null && perspective.Calculations.Contains(calc.CalculationProperty.CalculationReference) && !CalcIsHidden(calc)) { if (!string.IsNullOrEmpty(calc.CalculationProperty.AssociatedMeasureGroupID)) { if (!perspective.MeasureGroups.Contains(calc.CalculationProperty.AssociatedMeasureGroupID)) { bHighlight = true; } } } HighlightCell(bHighlight, columns, cell, j); } } } grid.Refresh(); } } catch { } }
static void PartitionInfo() { string srv_name = @"SCRBMSBDK000660"; string db_name = "FBR_FYPnL_DPRD"; string cube_name = "FYPnL Cube"; string mg_name = "USD"; //TextWriter tw = new StreamWriter("date.txt", true); Microsoft.AnalysisServices.Server srv; Microsoft.AnalysisServices.Database db; srv = new Microsoft.AnalysisServices.Server(); try { srv.Connect(srv_name); logMessageFmt("Databases on [{0}]: {1}", srv_name, srv.Databases.Count); db = srv.Databases.FindByName(db_name); Cube cube = db.Cubes.FindByName(cube_name); CubeDimension cubedim = cube.Dimensions[0]; Dimension dbdim = cubedim.Dimension; DimensionAttribute dbattr = dbdim.Attributes[0]; //var Source = dbattr.Source; System.Data.DataSet ds = dbdim.DataSourceView.Schema; string dsid = db.DataSourceViews[0].DataSourceID; DataTable dt = db.DataSourceViews[0].Schema.Tables[0]; //db.DataSources[0]. //DataTable dt = ds.Tables["SHARED_DimBrand"]; //ep = dt.ExtendedProperties. MeasureGroup mg = cube.MeasureGroups.FindByName(mg_name); MeasureGroupDimension mgd = mg.Dimensions[0]; List <MeasureGroupAttribute> alist = new List <MeasureGroupAttribute>(); if (mgd is RegularMeasureGroupDimension) { RegularMeasureGroupDimension rmgd = (RegularMeasureGroupDimension)mgd; foreach (MeasureGroupAttribute mgattr in rmgd.Attributes) { if (mgattr.Type == MeasureGroupAttributeType.Granularity) { alist.Add(mgattr); } } //MeasureGroupAttribute mgattr = rmgd.Attributes.f["Key"]; } Type t = alist[0].KeyColumns[0].Source.GetType(); Measure msr = mg.Measures[0]; foreach (Partition part in mg.Partitions) { string src; TabularBinding tb = part.Source; if (tb is QueryBinding) { src = String.Format("QUERY: {0}", ((QueryBinding)tb).QueryDefinition); } else if (tb is TableBinding) { src = String.Format("TABLE: {0}.{1}", ((TableBinding)tb).DbSchemaName, ((TableBinding)tb).DbTableName); } else if (tb is DsvTableBinding) { src = String.Format("DSV: {0}.{1}", ((DsvTableBinding)tb).DataSourceViewID, ((DsvTableBinding)tb).TableID); } else { src = String.Empty; } logMessageFmt("Partition [{0}]: {1}", part.Name, src /*part.EstimatedRows*/); //part.Process() } //Partition part = mg.Partitions[0]; //.FindByName(part_name); logMessage("Done."); Console.ReadKey(); } finally { if (srv.Connected == true) { srv.Disconnect(); } } }
///<summary> ///</summary> ///<param name="dataPoints"></param> ///<param name="dimension"></param> ///<returns></returns> ///<exception cref="ArgumentOutOfRangeException"></exception> public static IList GetDimensionValues(IEnumerable <PricingStructurePoint> dataPoints, CubeDimension dimension) { var strikeList = new List <Decimal>(); var expirationList = new List <TimeDimension>(); var termList = new List <TimeDimension>(); var volatilityList = new List <Decimal>(); foreach (var pricingStructurePoint in dataPoints) { volatilityList.Add(pricingStructurePoint.value); var pricingDataPointCoordinate = pricingStructurePoint.coordinate[0]; if (null != pricingDataPointCoordinate.strike && pricingDataPointCoordinate.strike.Length > 0) { var strike = pricingDataPointCoordinate.strike[0]; strikeList.Add(strike); } if (null != pricingDataPointCoordinate.expiration && pricingDataPointCoordinate.expiration.Length > 0) { var expiration = pricingDataPointCoordinate.expiration[0]; expirationList.Add(expiration); } if (null != pricingDataPointCoordinate.term && pricingDataPointCoordinate.term.Length > 0) { var term = pricingDataPointCoordinate.term[0]; termList.Add(term); } } switch (dimension) { case CubeDimension.Term: { return(termList.Distinct(new TimeDimensionEqualityComparer()).ToArray()); } case CubeDimension.Expiration: { return(expirationList.Distinct(new TimeDimensionEqualityComparer()).ToArray()); } case CubeDimension.Strike: { return(strikeList.Distinct().ToArray()); } case CubeDimension.Volatility: { return(volatilityList.ToArray()); } default: throw new ArgumentOutOfRangeException(nameof(dimension)); } }
///<summary> ///</summary> ///<param name="dataPoints"></param> ///<param name="dimension"></param> ///<returns></returns> ///<exception cref="NotSupportedException"></exception> ///<exception cref="ArgumentOutOfRangeException"></exception> public static int GetSizeOfDimension(IEnumerable <PricingStructurePoint> dataPoints, CubeDimension dimension) { var strikeList = new List <Decimal>(); var expirationList = new List <TimeDimension>(); var termList = new List <TimeDimension>(); foreach (var pricingStructurePoint in dataPoints) { var pricingDataPointCoordinate = pricingStructurePoint.coordinate[0]; if (null != pricingDataPointCoordinate.strike && pricingDataPointCoordinate.strike.Length > 0) { var strike = pricingDataPointCoordinate.strike[0]; strikeList.Add(strike); } if (null != pricingDataPointCoordinate.expiration && pricingDataPointCoordinate.expiration.Length > 0) { var expiration = pricingDataPointCoordinate.expiration[0]; expirationList.Add(expiration); } if (null != pricingDataPointCoordinate.term && pricingDataPointCoordinate.term.Length > 0) { var term = pricingDataPointCoordinate.term[0]; termList.Add(term); } } var numberOfMissingDimensions = 0; var numberOfUniqueStrikes = strikeList.Distinct().Count(); if (numberOfUniqueStrikes <= 1) { ++numberOfMissingDimensions; } var numberOfUniqueExpirations = expirationList.Distinct(new TimeDimensionEqualityComparer()).Count(); if (numberOfUniqueExpirations <= 1) { ++numberOfMissingDimensions; } var numberOfUniqueTerms = termList.Distinct(new TimeDimensionEqualityComparer()).Count(); if (numberOfUniqueTerms <= 1) { ++numberOfMissingDimensions; } if (1 != numberOfMissingDimensions) { var message = $"Number of missing dimensions is not 1 (actual value : '{numberOfMissingDimensions}'). Only 1 dimension out of 3 (strike, tenor, expiration) could be missing in a surface."; throw new NotSupportedException(message); } switch (dimension) { case CubeDimension.Term: { return(numberOfUniqueTerms); } case CubeDimension.Expiration: { return(numberOfUniqueExpirations); } case CubeDimension.Strike: { return(numberOfUniqueStrikes); } default: throw new ArgumentOutOfRangeException(nameof(dimension)); } }
private static List <AggValidationWarning> CheckAggDesign(AggregationDesign aggDesign, string sCorrectAggregationDesignName, string sReportTitle) { List <AggValidationWarning> masterWarnings = new List <AggValidationWarning>(); //check for m2m agg problems foreach (Aggregation agg in aggDesign.Aggregations) { foreach (AggregationDimension aggDim in agg.Dimensions) { if (aggDim.Attributes.Count > 0 && aggDim.MeasureGroupDimension is ManyToManyMeasureGroupDimension) { ManyToManyMeasureGroupDimension m2mDim = (ManyToManyMeasureGroupDimension)aggDim.MeasureGroupDimension; MeasureGroup intermediateMG = m2mDim.MeasureGroup; List <MeasureGroupAttribute> missing = new List <MeasureGroupAttribute>(); foreach (MeasureGroupDimension commonDim in intermediateMG.Dimensions) { RegularMeasureGroupDimension regCommonDim = commonDim as RegularMeasureGroupDimension; if (commonDim.CubeDimensionID != aggDim.CubeDimensionID || regCommonDim == null) { if (!aggDim.ParentMeasureGroup.Dimensions.Contains(commonDim.CubeDimensionID)) { continue; //this isn't a shared dimension } MeasureGroupDimension dataMeasureGroupDim = aggDim.ParentMeasureGroup.Dimensions[commonDim.CubeDimensionID]; if (dataMeasureGroupDim is ManyToManyMeasureGroupDimension) { continue; //this shared dimension is m2m on the data measure group so don't include it } //this is a common dimension and the granularity attribute on the intermediate measure group needs to be in the agg bool bFoundGranularityAgg = false; MeasureGroupAttribute mga = GetGranularityAttribute(regCommonDim); AggregationDimension aggCommonDim = agg.Dimensions.Find(commonDim.CubeDimensionID); if (aggCommonDim != null) { if (aggCommonDim.Attributes.Find(mga.AttributeID) != null) { bFoundGranularityAgg = true; } } if (!bFoundGranularityAgg && mga != null) { missing.Add(mga); } } } string sWarning = "This aggregation contains many-to-many dimension [" + m2mDim.CubeDimension.Name + "]. It will not be used unless it also contains "; for (int i = 0; i < missing.Count; i++) { MeasureGroupAttribute mga = missing[i]; if (i > 0) { sWarning += " and "; } sWarning += "[" + mga.Parent.CubeDimension.Name + "].[" + mga.Attribute.Name + "]"; } if (missing.Count == 0) { sWarning = ""; } else { sWarning += ". "; } sWarning += "The many-to-many dimension [" + m2mDim.CubeDimension.Name + "] itself should not be included in the aggregation to workaround a bug."; masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle)); } } } //check for non-materialized reference dimensions foreach (Aggregation agg in aggDesign.Aggregations) { foreach (AggregationDimension aggDim in agg.Dimensions) { if (aggDim.Attributes.Count > 0 && aggDim.MeasureGroupDimension is ReferenceMeasureGroupDimension) { ReferenceMeasureGroupDimension refDim = (ReferenceMeasureGroupDimension)aggDim.MeasureGroupDimension; if (refDim.Materialization == ReferenceDimensionMaterialization.Indirect) { string sWarning = "This aggregation contains a non-materialized reference dimension [" + refDim.CubeDimension.Name + "] which is not supported."; masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle)); } } } } //check whether all measures are semi-additive bool bAllMeasuresAreSemiAdditive = true; foreach (Measure m in aggDesign.Parent.Measures) { if (m.AggregateFunction == AggregationFunction.Count || m.AggregateFunction == AggregationFunction.DistinctCount || m.AggregateFunction == AggregationFunction.Sum || m.AggregateFunction == AggregationFunction.Min || m.AggregateFunction == AggregationFunction.Max || m.AggregateFunction == AggregationFunction.None) { bAllMeasuresAreSemiAdditive = false; break; } else if (m.AggregateFunction == AggregationFunction.ByAccount) { //if it's a ByAccount measure, we need to check the list of AggregationFunctions on each account foreach (Account acct in aggDesign.ParentDatabase.Accounts) { if (acct.AggregationFunction == AggregationFunction.Sum) //Sum is the only additive AggregationFunction allowed in account intelligence { bAllMeasuresAreSemiAdditive = false; break; } } } } //if all measures are semi-additive, find the Time dimension the semi-additive behavior operates on (which we think is the first Time dimension) if (bAllMeasuresAreSemiAdditive) { CubeDimension semiAdditiveDim = null; MeasureGroupDimension semiAdditiveMgDim = null; foreach (CubeDimension cd in aggDesign.ParentCube.Dimensions) { MeasureGroupDimension mgd = aggDesign.Parent.Dimensions.Find(cd.ID); if (mgd != null && mgd.Dimension.Type == DimensionType.Time) { semiAdditiveDim = mgd.CubeDimension; semiAdditiveMgDim = mgd; break; } } if (semiAdditiveDim == null || semiAdditiveMgDim == null || !(semiAdditiveMgDim is RegularMeasureGroupDimension)) { //TODO: should we warn about this? } else { foreach (Aggregation agg in aggDesign.Aggregations) { AggregationDimension semiAdditiveAggDim = agg.Dimensions.Find(semiAdditiveDim.ID); MeasureGroupAttribute granularity = GetGranularityAttribute((RegularMeasureGroupDimension)semiAdditiveMgDim); if (semiAdditiveAggDim == null || semiAdditiveAggDim.Attributes.Find(granularity.AttributeID) == null) { string sWarning = "This measure group contains only semi-additive measures. This aggregation will not be used when semi-additive measure values are retrieved because it does not include the granularity attribute of the semi-additive dimension ([" + semiAdditiveDim.Name + "].[" + granularity.Attribute.Name + "]). (The Exists-with-a-measure-group function can still run off this aggregation, though.)"; masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle)); } } } } //check for aggs on parent-child attributes foreach (Aggregation agg in aggDesign.Aggregations) { foreach (AggregationDimension aggDim in agg.Dimensions) { foreach (AggregationAttribute attr in aggDim.Attributes) { if (attr.Attribute.Usage == AttributeUsage.Parent) { string sWarning = "This aggregation contains [" + aggDim.CubeDimension.Name + "].[" + attr.Attribute.Name + "] which is a parent-child attribute. This is not allowed. The aggregation should include [" + aggDim.CubeDimension.Name + "].[" + aggDim.Dimension.KeyAttribute.Name + "] instead."; masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle)); } } } } //check for aggs on AttributeHierarchyEnabled=false attributes foreach (Aggregation agg in aggDesign.Aggregations) { foreach (AggregationDimension aggDim in agg.Dimensions) { foreach (AggregationAttribute attr in aggDim.Attributes) { if (!attr.CubeAttribute.AttributeHierarchyEnabled) { string sWarning = "This aggregation contains [" + aggDim.CubeDimension.Name + "].[" + attr.Attribute.Name + "] which is not enabled as an attribute hierarchy. This is not allowed."; masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle)); } } } } //find a list of ALTER statements that alter the DEFAULT_MEMBER property in the calc script List <string> attributesWithDefaultMemberAlteredInCalcScript = new List <string>(); System.Text.RegularExpressions.Regex regEx = new System.Text.RegularExpressions.Regex(@"ALTER\s+CUBE\s+(CURRENTCUBE|\[?" + aggDesign.ParentCube.Name + @"\]?)\s+UPDATE\s+DIMENSION\s+(.+?)\s*\,\s*DEFAULT_MEMBER\s+", System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Multiline); foreach (MdxScript script in aggDesign.ParentCube.MdxScripts) { if (script.DefaultScript) { StringBuilder sCommands = new StringBuilder(); foreach (Command cmd in script.Commands) { sCommands.AppendLine(cmd.Text); } foreach (System.Text.RegularExpressions.Match match in regEx.Matches(sCommands.ToString())) { try { attributesWithDefaultMemberAlteredInCalcScript.Add(match.Groups[2].Captures[0].Value.ToLower()); } catch { } } break; } } //build list of cube dimension attributes that have default members, are not aggregatable, or are marked as AggregationUsage=Full foreach (MeasureGroupDimension mgDim in aggDesign.Parent.Dimensions) { if (mgDim is ManyToManyMeasureGroupDimension) { continue; //don't suggest adding any m2m dimensions } CubeDimension cd = mgDim.CubeDimension; foreach (CubeAttribute ca in cd.Attributes) { if (!ca.AttributeHierarchyEnabled) { continue; } if (!IsAtOrAboveGranularity(ca.Attribute, mgDim)) { continue; } foreach (Aggregation agg in aggDesign.Aggregations) { AggregationDimension aggDim = agg.Dimensions.Find(cd.ID); if (ca.Attribute.Usage == AttributeUsage.Parent) { if (!(mgDim is RegularMeasureGroupDimension)) { continue; } if (!IsAtOrAboveGranularity(cd.Dimension.KeyAttribute, mgDim)) { continue; } //if this is a parent-child attribute and the key isn't in the agg, then check whether the parent-child attribute has a DefaultMember or is aggregatable if (aggDim == null || aggDim.Attributes.Find(cd.Dimension.KeyAttribute.ID) == null) { string sWarning = ""; if (!string.IsNullOrEmpty(ca.Attribute.DefaultMember) || attributesWithDefaultMemberAlteredInCalcScript.Contains(((string)("[" + cd.Name + "].[" + ca.Attribute.Name + "]")).ToLower())) { sWarning += "has a DefaultMember"; } if (!ca.Attribute.IsAggregatable) { if (!string.IsNullOrEmpty(sWarning)) { sWarning += " and "; } sWarning += "is not aggregatable"; } if (!string.IsNullOrEmpty(sWarning)) { sWarning = "This aggregation should probably contain [" + cd.Name + "].[" + cd.Dimension.KeyAttribute.Name + "] because [" + cd.Name + "].[" + ca.Attribute.Name + "] is a parent-child attribute which " + sWarning + "."; masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle)); } } } //for non-parent-child attributes... else if (aggDim == null || aggDim.Attributes.Find(ca.AttributeID) == null) { string sWarning = ""; if (!string.IsNullOrEmpty(ca.Attribute.DefaultMember) || attributesWithDefaultMemberAlteredInCalcScript.Contains(((string)("[" + cd.Name + "].[" + ca.Attribute.Name + "]")).ToLower())) { sWarning += "has a DefaultMember"; } if (!ca.Attribute.IsAggregatable) { if (!string.IsNullOrEmpty(sWarning)) { sWarning += " and "; } sWarning += "is not aggregatable"; } if (ca.AggregationUsage == AggregationUsage.Full) { if (!string.IsNullOrEmpty(sWarning)) { sWarning += " and "; } sWarning += "is marked as AggregationUsage=Full"; } if (aggDim != null && AggContainsChild(aggDim, ca.Attribute)) { continue; //if this attribute is redundant, then no need to warn about it } if (!string.IsNullOrEmpty(sWarning)) { sWarning = "This aggregation should probably contain [" + cd.Name + "].[" + ca.Attribute.Name + "] which " + sWarning + "."; masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle)); } } } } } //look for aggs with redundant attributes foreach (Aggregation agg in aggDesign.Aggregations) { bool bHasRedundancy = false; foreach (AggregationDimension aggDim in agg.Dimensions) { foreach (AggregationAttribute attr in aggDim.Attributes) { if (AggContainsParent(aggDim, attr.Attribute)) { bHasRedundancy = true; break; } } if (bHasRedundancy) { break; } } if (bHasRedundancy) { string sWarning = "This aggregation contains redundant attributes which unnecessarily bloat the size of the aggregation."; masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle)); } } //check for aggs on below granularity attributes foreach (Aggregation agg in aggDesign.Aggregations) { foreach (AggregationDimension aggDim in agg.Dimensions) { foreach (AggregationAttribute attr in aggDim.Attributes) { if (!IsAtOrAboveGranularity(attr.Attribute, aggDim.MeasureGroupDimension)) { string sWarning = "This aggregation contains [" + aggDim.CubeDimension.Name + "].[" + attr.Attribute.Name + "] which is below granularity. This is not allowed."; masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle)); } } } } return(masterWarnings); }
private void PopulateProperties() { // find the datasourceid for the Table - and also the datasourceid foreach (DataSourceView dsv in _parentTabularModel.AmoDatabase.DataSourceViews) { _datasourceId = dsv.DataSourceID; foreach (DataTable tbl in dsv.Schema.Tables) { if ((tbl.TableName == _amoDimension.ID) || (_amoDimension.Source is DsvTableBinding && tbl.TableName == ((DsvTableBinding)_amoDimension.Source).TableID) ) { _amoTable = tbl; //_tableInDsv = tbl; if (tbl.ExtendedProperties["DataSourceID"] != null) { _datasourceId = tbl.ExtendedProperties["DataSourceID"].ToString(); break; } } } } foreach (CubeDimension cd in _parentTabularModel.AmoDatabase.Cubes[0].Dimensions) { if (cd.ID == _amoDimension.ID) { _amoCubeDimension = cd; break; } } foreach (MeasureGroup mg in _parentTabularModel.AmoDatabase.Cubes[0].MeasureGroups) { if (mg.ID == _amoDimension.ID) { _amoMeasureGroup = mg; break; } } string baseColumns = ""; string calculatedColumns = ""; foreach (DimensionAttribute attribute in _amoDimension.Attributes) { // ignore key attribute - which is internal built in "RowNumber" //if (attribute.ID != _amoDimension.KeyAttribute.ID) if (attribute.ID != "__XL_RowNumber") //if (attribute.ID != "RowNumber") //Before SQL 2016, was just "RowNumber" { // if calculated column, show expression if (attribute.NameColumn.Source is ExpressionBinding) { string expression = ((ExpressionBinding)attribute.NameColumn.Source).Expression; calculatedColumns += "[" + attribute.Name + "]:=" + expression + "; " + SetColumnFormatAndVisibility(attribute) + "\n"; } else { string baseColumn = "[" + attribute.Name + "]"; /* DATA TYPE MAPPING * Text: WChar * Whole Number: BigInt * Decimal Number: Double * True/False: Boolean * Currency: Currency * Date: Date * Binary: Binary */ // insert spaces to line up data types nicely if (baseColumn.Length < _spacing) { baseColumn += new String(' ', _spacing - baseColumn.Length); } switch (attribute.KeyColumns[0].DataType) { case System.Data.OleDb.OleDbType.WChar: baseColumn += " Data Type: Text, "; break; case System.Data.OleDb.OleDbType.BigInt: case System.Data.OleDb.OleDbType.Integer: case System.Data.OleDb.OleDbType.SmallInt: baseColumn += " Data Type: Whole Number, "; break; case System.Data.OleDb.OleDbType.Double: baseColumn += " Data Type: Decimal Number, "; break; case System.Data.OleDb.OleDbType.Boolean: baseColumn += " Data Type: True/False, "; break; case System.Data.OleDb.OleDbType.Currency: baseColumn += " Data Type: Currency, "; break; case System.Data.OleDb.OleDbType.Date: baseColumn += " Data Type: Date, "; break; case System.Data.OleDb.OleDbType.Binary: baseColumn += " Data Type: Binary, "; break; default: break; } // Format & visibility baseColumn += SetColumnFormatAndVisibility(attribute); baseColumns += baseColumn + "\n"; } } } _objectDefinition += "Base Columns:\n" + baseColumns + "\n"; _objectDefinition += "Calculated Columns:\n" + calculatedColumns + "\n"; _objectDefinition += "Hierarchies:\n"; if (_amoDimension.Hierarchies.Count == 0) { _objectDefinition += "\n"; } else { foreach (Hierarchy hierarchy in _amoDimension.Hierarchies) { _objectDefinition += "[" + hierarchy.Name + "] "; //if ((_parentTabularModel.ComparisonInfo.OptionsInfo.OptionDisplayFolders || _parentTabularModel.ComparisonInfo.OptionsInfo.OptionTranslations) && hierarchy.Name.Length + 2 < _spacing) //{ // _objectDefinition += new String(' ', _spacing - hierarchy.Name.Length - 2); //} //if (_parentTabularModel.ComparisonInfo.OptionsInfo.OptionDisplayFolders) //{ // _objectDefinition += "Display Folder: "; // if (hierarchy.DisplayFolder != null) // { // _objectDefinition += hierarchy.DisplayFolder; // } // if (_parentTabularModel.ComparisonInfo.OptionsInfo.OptionTranslations) _objectDefinition += ", "; //} //if (_parentTabularModel.ComparisonInfo.OptionsInfo.OptionTranslations) //{ // _objectDefinition += "Hierarchy Translations: "; // if (hierarchy.Translations.Count > 0) // { // _objectDefinition += "["; // foreach (Translation hierarchyTranslation in hierarchy.Translations) // { // _objectDefinition += CultureInfo.GetCultureInfo(hierarchyTranslation.Language).DisplayName + ": " + hierarchyTranslation.Caption + ", "; // } // _objectDefinition = _objectDefinition.Substring(0, _objectDefinition.Length - 2) + "]"; // } // if (_parentTabularModel.ComparisonInfo.OptionsInfo.OptionDisplayFolders) // { // _objectDefinition += ", Display Folder Translations: "; // if (hierarchy.Translations.Count > 0) // { // _objectDefinition += "["; // foreach (Translation hierarchyTranslation in hierarchy.Translations) // { // _objectDefinition += CultureInfo.GetCultureInfo(hierarchyTranslation.Language).DisplayName + ": " + hierarchyTranslation.DisplayFolder + ", "; // } // _objectDefinition = _objectDefinition.Substring(0, _objectDefinition.Length - 2) + "]"; // } // } //} _objectDefinition += "\nLevels:\n"; foreach (Level level in hierarchy.Levels) { _objectDefinition += " [" + level.Name + "]"; //if (_parentTabularModel.ComparisonInfo.OptionsInfo.OptionTranslations && level.Name.Length + 4 < _spacing) //{ // _objectDefinition += new String(' ', _spacing - level.Name.Length - 4); //} //if (_parentTabularModel.ComparisonInfo.OptionsInfo.OptionTranslations) //{ // _objectDefinition += "Level Translations: "; // if (level.Translations.Count > 0) // { // _objectDefinition += "["; // foreach (Translation levelTranslation in level.Translations) // { // _objectDefinition += CultureInfo.GetCultureInfo(levelTranslation.Language).DisplayName + ": " + levelTranslation.Caption + ", "; // } // _objectDefinition = _objectDefinition.Substring(0, _objectDefinition.Length - 2) + "]"; // } // if (_parentTabularModel.ComparisonInfo.OptionsInfo.OptionDisplayFolders) // { // _objectDefinition += ", Display Folder Translations: "; // if (level.Translations.Count > 0) // { // _objectDefinition += "["; // foreach (Translation levelTranslation in level.Translations) // { // _objectDefinition += CultureInfo.GetCultureInfo(levelTranslation.Language).DisplayName + ": " + levelTranslation.DisplayFolder + ", "; // } // _objectDefinition = _objectDefinition.Substring(0, _objectDefinition.Length - 2) + "]"; // } // } //} _objectDefinition += "\n"; } _objectDefinition += "\n"; } } if (_amoCubeDimension != null) { _objectDefinition += "Format & Visibility:\nHidden:" + (!_amoCubeDimension.Visible).ToString(); //if (_parentTabularModel.ComparisonInfo.OptionsInfo.OptionTranslations) //{ // _objectDefinition += ", Table Translations: "; // if (_amoCubeDimension.Translations.Count > 0) // { // _objectDefinition += "["; // foreach (Translation tableTranslation in _amoCubeDimension.Translations) // { // _objectDefinition += CultureInfo.GetCultureInfo(tableTranslation.Language).DisplayName + ": " + tableTranslation.Caption + ", "; // } // _objectDefinition = _objectDefinition.Substring(0, _objectDefinition.Length - 2) + "]"; // } // if (_parentTabularModel.ComparisonInfo.OptionsInfo.OptionDisplayFolders) // { // _objectDefinition += ", Display Folder Translations: "; // if (_amoCubeDimension.Translations.Count > 0) // { // _objectDefinition += "["; // foreach (Translation tableDisplayFolderTranslation in _amoCubeDimension.Translations) // { // _objectDefinition += CultureInfo.GetCultureInfo(tableDisplayFolderTranslation.Language).DisplayName + ": " + tableDisplayFolderTranslation.DisplayFolder + ", "; // } // _objectDefinition = _objectDefinition.Substring(0, _objectDefinition.Length - 2) + "]"; // } // } //} _objectDefinition += "\n"; } if (_parentTabularModel.ComparisonInfo.OptionsInfo.OptionPartitions && _amoMeasureGroup != null) { _objectDefinition += "\nPartitions:\n"; List <string> partitionNames = new List <string>(); // put in here to sort foreach (Partition partition in _amoMeasureGroup.Partitions) { partitionNames.Add(partition.Name); } partitionNames.Sort(); foreach (string partitionName in partitionNames) { foreach (Partition partition in _amoMeasureGroup.Partitions) { if (partition.Name == partitionName) { _objectDefinition += "Name: [" + partition.Name + "]\nSQL:\n" + ((QueryBinding)partition.Source).QueryDefinition + "\n"; break; } } } } foreach (Microsoft.AnalysisServices.Relationship relationship in _amoDimension.Relationships) { _relationships.Add(new Relationship(this, relationship)); } }
/// <summary> /// Generates a sample MDX query for the supplied parameters. /// </summary> /// <param name="measure">Measure to query.</param> /// <param name="dimension">Dimension to slice by.</param> /// <param name="cube">Cube to query.</param> /// <returns>An MDX query</returns> private string GenerateMdxQueryFor(Measure measure, CubeDimension dimension, Cube cube) { const string MdxTemplate = @" select { [Measures].[$(MeasureName)] } on 0 from [$(CubeName)]"; const string SlicerTemplate = @" where ( [$(DimensionName)].[$(AllMemberName)] )"; StringBuilder mdxBuilder = new StringBuilder(MdxTemplate); mdxBuilder.Replace("$(MeasureName)", measure.Name); mdxBuilder.Replace("$(CubeName)", cube.Name); if (dimension != null) { mdxBuilder.Append(SlicerTemplate); mdxBuilder.Replace("$(DimensionName)", dimension.Name); mdxBuilder.Replace("$(AllMemberName)", dimension.Dimension.AttributeAllMemberName); } return mdxBuilder.ToString(); }
/// <summary> /// Checks to see if this is a match to the cube metadata. /// </summary> /// <param name="dimension">Dimension to match.</param> /// <param name="measureGroup">Measure group to match.</param> /// <returns>True if its a match.</returns> public bool IsMetadataMatch(CubeDimension dimension, MeasureGroup measureGroup) { string cubeName = measureGroup.Parent.Name.Replace(" ", string.Empty); if (cubeName != this.CubeName) { return false; } string measureGroupName = measureGroup.Name.Replace(" ", string.Empty); if (measureGroupName != this.MeasureGroupName) { return false; } string dimensionName = dimension.Name.Replace(" ", string.Empty); if (dimensionName != this.DimensionName) { return false; } return true; }