/// <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;
}