internal void FromMeasureGroup(MeasureGroup mg)
{
this.SetAllNull();
if (mg.Name != null) this.Name = mg.Name.Value;
if (mg.Caption != null) this.Caption = mg.Caption.Value;
}
public void Init(
EnvDTE.ProjectItem projItem,
MeasureGroup mg,
string[,] inDimAttributes,
string[] inDimNames,
string[] inDimIDs,
bool inAddNew,
String inAggDesName)
{
bool IsOnlineMode = false;
Cube selectedCube = projItem.Object as Cube;
string sTargetDatabase = "";
if ((selectedCube != null) && (selectedCube.ParentServer != null))
{
// if we are in Online mode there will be a parent server
server = selectedCube.ParentServer;
sTargetDatabase = selectedCube.Parent.Name;
IsOnlineMode = true;
}
else
{
// if we are in Project mode we will use the server name from
// the deployment settings
DeploymentSettings deploySet = new DeploymentSettings(projItem);
server = new Server();
server.Connect(deploySet.TargetServer);
sTargetDatabase = deploySet.TargetDatabase;
}
mg1 = mg;
dimAttributes = inDimAttributes;
dimNames = inDimNames;
dimIDs = inDimIDs;
//Variable defining whether form is invoked to create new aggregation design or add aggregations to exising one.
addNew = inAddNew;
aggDesName = inAggDesName;
strSQLQuery = textBoxSQLQuery.Text;
textBoxSQLQuery.Text = textBoxSQLQuery.Text +
" Where MSOLAP_Database = '" +
mg1.ParentDatabase.Name +
"' and MSOLAP_ObjectPath = '" +
server.Name + "." +
sTargetDatabase + "." +
mg1.Parent.ID + "." +
mg1.ID + "'" +
" and duration >= 100";
textBoxSQLConnectionString.Text = server.ServerProperties.Find("Log\\QueryLog\\QueryLogConnectionString").Value.ToString(); ;
textBoxSQLConnectionString.Text = MakeConnectionStringRemote(server.Name, textBoxSQLConnectionString.Text);
if (!addNew)
{
textBoxNewAggDesign.Text = inAggDesName;
textBoxNewAggDesign.Enabled = false;
}
txtServerNote.Text = string.Format("Note: the QueryLog details have been taken from the '{0}' server,\n" +
"which is the one currently configured as the deployment target.", server.Name);
txtServerNote.Visible = !IsOnlineMode; // hide the note if we are in online mode.
}
internal MeasureGroup ToMeasureGroup()
{
MeasureGroup mg = new MeasureGroup();
mg.Name = this.Name;
mg.Caption = this.Caption;
return mg;
}
//, 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;
}
public void Init(string strAggDesign,
MeasureGroup mg,
string[,] inDimAttributes,
string[] inDimNames ,
string[] inDimIDs)
{
this.Text = this.Text + " Aggregation Design: " + strAggDesign;
mg1 = mg;
aggDes = mg.AggregationDesigns.GetByName(strAggDesign);
dimAttributes = inDimAttributes;
dimNames = inDimNames;
dimIDs = inDimIDs;
DataTable myTable = new DataTable("Aggregations");
DataColumn colItem = new DataColumn("Name", Type.GetType("System.String"));
myTable.Columns.Add(colItem);
colItem = new DataColumn("Aggregations", Type.GetType("System.String"));
myTable.Columns.Add(colItem);
colItem = new DataColumn("Type", Type.GetType("System.String"));
myTable.Columns.Add(colItem);
DataView myDataView = new DataView(myTable);
dataGrid1.DataSource = myDataView;
DataRow NewRow;
foreach (Aggregation agg in aggDes.Aggregations)
{
NewRow = myTable.NewRow();
NewRow["Aggregations"] = ConvertAggToSting(agg);
NewRow["Name"] = agg.Name;
myTable.Rows.Add(NewRow);
}
AddGridStyle();
PopulateTreeView();
checkBoxRelationships.Checked = true;
sychContr = SynchControls.Unknown;
int i = 0;
foreach (DataRow dRow in myDataView.Table.Rows)
{
dataGrid1.CurrentRowIndex = i;
dataGrid1_Click(null, null);
i++;
}
myDataView.AllowNew = false;
}
private void MarkMeasureGroupAsModified(TreeNode node, MeasureGroup mg)
{
if (node.Tag is MeasureGroup && ((MeasureGroup)node.Tag).ID == mg.ID)
{
if (!node.Text.EndsWith(MODIFIED_SUFFIX))
{
node.Text = node.Text + MODIFIED_SUFFIX;
}
return;
}
foreach (TreeNode child in node.Nodes)
{
MarkMeasureGroupAsModified(child, mg);
}
}
static public Partition FindLastPartition(MeasureGroup parMG)
{
TypePeriod varType = GetTypePartition(parMG);
DateTime varMax = new DateTime(1, 1, 1), varCur;
Partition rezPar = null;
foreach (Partition p in parMG.Partitions)
{
varCur = GetDateStartPartition(p);
if (varCur > varMax)
{
rezPar = p;
varMax = varCur;
}
}
return(rezPar);
}
public void Write_OneElementAtEachStep_FileIsCorrectlyBuilt()
{
var header = GetHeader();
var expectedContent = header + "\"p\";\"mg\";\"m1\";\"[m1]\";\"d\";\"[d]\";\"h1\";\"[h1]\";\"l1\";\"[l1]\";\"0\";\"p1\";\"[p1]\"\r\n";
var expectedFilename = Path.Combine(DiskOnFile.GetDirectoryPath(), "ExpectedCSV-" + MethodBase.GetCurrentMethod() + ".csv");
if (File.Exists(expectedFilename))
{
File.Delete(expectedFilename);
}
File.AppendAllText(expectedFilename, expectedContent, Encoding.UTF8);
var filename = Path.Combine(DiskOnFile.GetDirectoryPath(), @"ActualCSV-" + MethodBase.GetCurrentMethod() + ".csv");
var metadata = new CubeMetadata();
var p = new Perspective("p");
metadata.Perspectives.Add(p);
var mg = new MeasureGroup("mg");
p.MeasureGroups.Add(mg);
var m1 = new Measure("[m1]", "m1", "df");
mg.Measures.Add(m1);
var d = new Dimension("[d]", "d");
mg.LinkedDimensions.Add(d);
var h1 = new Hierarchy("[h1]", "h1", "df");
d.Hierarchies.Add(h1);
var l1 = new Level("[l1]", "l1", 0);
h1.Levels.Add(l1);
var p1 = new Property("[p1]", "p1");
l1.Properties.Add(p1);
//set the object to test
var mcw = new MetadataCsvWriter(filename);
mcw.Write(metadata);
//Assertion
FileAssert.AreEqual(expectedFilename, filename);
}
private void PopupDeleteUnusedAggsForm(MeasureGroup mg)
{
MeasureGroup cloneMG = null;
if (mg != null)
{
cloneMG = this.cloneDB.Cubes[mg.Parent.ID].MeasureGroups[mg.ID];
}
AggManager.DeleteUnusedAggs form1 = new AggManager.DeleteUnusedAggs();
form1.Init(mProjItem, cloneDB, cloneMG);
if (form1.ShowDialog(this) == DialogResult.OK)
{
foreach (TreeNode node in form1.treeViewAggregation.Nodes)
{
DeleteAggsAndRecurse(node);
}
}
}
public void Init(string strAggDesign, MeasureGroup mg)
{
int i = 0;
mg1 = mg;
foreach (Partition part in mg1.Partitions)
{
listBox1.Items.Add(part.Name);
if (part.AggregationDesign != null)
if (part.AggregationDesign.Name == strAggDesign )
listBox1.SelectedIndices.Add(i);
i++;
}
strAggDes = strAggDesign;
labelAggDesign.Text = labelAggDesign.Text +" " + strAggDesign;
}
static string CreatePartition(MeasureGroup mg, string part_key, string query, string slice, DataSource ds, int part_size)
{
string part_name = String.Format("{0}_{1}", mg.Name, part_key);
Partition part = mg.Partitions.FindByName(part_name);
if (part != null)
{
part.Drop();
}
part = mg.Partitions.Add(part_name);
part.StorageMode = StorageMode.Molap;
part.Source = new QueryBinding(ds.ID, String.Format(query, part_key));
part.Slice = String.Format(slice, part_key);
part.EstimatedRows = part_size;
//part.Annotations.Add("AccountMonthKey", part_key);
part.Update();
return(part_name);
}
// 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;
}
public void Init(
EnvDTE.ProjectItem projItem,
Database cloneDB,
MeasureGroup cloneMG)
{
Cube selectedCube = projItem.Object as Cube;
if ((selectedCube != null) && (selectedCube.ParentServer != null))
{
// if we are in Online mode there will be a parent server
this.liveServer = selectedCube.ParentServer;
this.liveDB = selectedCube.Parent;
this.liveCube = selectedCube;
}
else
{
// if we are in Project mode we will use the server name from
// the deployment settings
DeploymentSettings deploySet = new DeploymentSettings(projItem);
this.liveServer = new Server();
this.liveServer.Connect(deploySet.TargetServer);
this.liveDB = this.liveServer.Databases.GetByName(deploySet.TargetDatabase);
this.liveCube = this.liveDB.Cubes.GetByName(selectedCube.Name);
}
sCubePath = this.liveServer.ID + "." + this.liveDB.ID + "." + this.liveCube.ID + ".";
this.cloneMG = cloneMG;
this.cloneDB = cloneDB;
this.cloneCube = cloneDB.Cubes[liveCube.ID];
this.lblServer.Text = this.liveServer.Name;
this.lblDatabaseName.Text = this.liveDB.Name;
this.iWindowFullHeight = this.Height;
this.iWindowShortHeight = this.btnExecute.Bottom + 40;
this.Height = iWindowShortHeight;
this.iWindowFullWidth = this.Width;
this.iWindowNarrowWidth = this.grpProgress.Left - 30;
this.Width = iWindowNarrowWidth;
this.buttonCancel.Visible = false;
this.buttonOK.Visible = false;
this.treeViewAggregation.Visible = false;
this.lblUnusedAggregationsToDelete.Visible = false;
}
/// <summary>
/// Create aggregation design
/// </summary>
/// <param name="mg"></param>
/// <param name="sqlHelper"></param>
/// <param name="asMeta"></param>
/// <returns></returns>
public AggregationDesign CREATE_AGGREGATION_DESIGN(MeasureGroup mg, DB_SQLHELPER_BASE sqlHelper
, AS_METADATA asMeta)
{
DataTable agg_design_list = null;
AggregationDesign agg_design = null;
try
{
agg_design_list = asMeta.GET_SSAS_AGGREGATION_DESIGN_SET(sqlHelper, mg.ID);
foreach (DataRow measure in agg_design_list.Rows)
{
String AggregationDesignName = measure["aggregation_design_name"].ToString();
//agg_design=AggregationDesignName;
String AggregationName = measure["aggregation_name"].ToString();
String DimensionID = measure["dimension_id"].ToString();
String AttributeID = measure["attribute_id"].ToString();
if (mg.AggregationDesigns.Find(AggregationDesignName) == null)
{
mg.AggregationDesigns.Add(AggregationDesignName);
}
agg_design = mg.AggregationDesigns[AggregationDesignName];
Aggregation agg = agg_design.Aggregations.Find(AggregationName);
if (agg == null)
{
agg = agg_design.Aggregations.Add(AggregationName, AggregationName);
}
AggregationDimension agg_dim = agg.Dimensions.Find(DimensionID);
if (agg_dim == null)
{
agg.Dimensions.Add(DimensionID);
}
agg.Dimensions[DimensionID].Attributes.Add(AttributeID);
}
}
catch (Exception ex)
{
sqlHelper.ADD_MESSAGE_LOG(ex.Message.ToString(), MESSAGE_TYPE.AGGREGATION_DESIGN, MESSAGE_RESULT_TYPE.Error);
throw (ex);
}
return(agg_design);
}
public void DemoMethod02()
{
Database d = srv.Databases.FindByName("ContosoDCDemo");
Cube c = d.Cubes.FindByName("OperationA");
MeasureGroup mg = c.MeasureGroups.FindByName("Inventory");
foreach (Measure m in mg.Measures)
{
// https://blog.crossjoin.co.uk/2005/06/30/measure-expressions/
Console.WriteLine(
$"{m.Name,-40}, " +
$"{m.Visible, -5}, " +
$"{m.Source,-40}, " +
$"{m.AggregateFunction,-10}, " +
$"{m.MeasureExpression}"
);
}
}
public void Init(
EnvDTE.ProjectItem projItem,
Database cloneDB,
MeasureGroup cloneMG)
{
Cube selectedCube = projItem.Object as Cube;
if ((selectedCube != null) && (selectedCube.ParentServer != null))
{
// if we are in Online mode there will be a parent server
this.liveServer = selectedCube.ParentServer;
this.liveDB = selectedCube.Parent;
this.liveCube = selectedCube;
}
else
{
// if we are in Project mode we will use the server name from
// the deployment settings
DeploymentSettings deploySet = new DeploymentSettings(projItem);
this.liveServer = new Server();
this.liveServer.Connect(deploySet.TargetServer);
this.liveDB = this.liveServer.Databases.GetByName(deploySet.TargetDatabase);
this.liveCube = this.liveDB.Cubes.GetByName(selectedCube.Name);
}
sCubePath = this.liveServer.ID + "." + this.liveDB.ID + "." + this.liveCube.ID + ".";
this.cloneMG = cloneMG;
this.cloneDB = cloneDB;
this.cloneCube = cloneDB.Cubes[liveCube.ID];
this.lblServer.Text = this.liveServer.Name;
this.lblDatabaseName.Text = this.liveDB.Name;
this.iWindowFullHeight = this.Height;
this.iWindowShortHeight = this.btnExecute.Bottom + 40;
this.Height = iWindowShortHeight;
this.iWindowFullWidth = this.Width;
this.iWindowNarrowWidth = this.grpProgress.Left - 30;
this.Width = iWindowNarrowWidth;
this.buttonCancel.Visible = false;
this.buttonOK.Visible = false;
this.treeViewAggregation.Visible = false;
this.lblUnusedAggregationsToDelete.Visible = false;
}
private void cmdDeleteAggDes_Click(object sender, EventArgs e)
{
try
{
TreeNode nd = treeView1.SelectedNode;
if (MessageBox.Show("Would you like to delete Aggregation design:" + nd.Text + "?", "Delete Aggregation design", MessageBoxButtons.OKCancel) == DialogResult.Cancel)
{
return;
}
//Delete aggregation design from the measure group
MeasureGroup mg = (MeasureGroup)nd.Parent.Parent.Tag;
AggregationDesign aggD = mg.AggregationDesigns.GetByName(nd.Text);
foreach (Partition pt in mg.Partitions)
{
if (pt.AggregationDesignID == aggD.ID)
{
pt.AggregationDesignID = null;
}
}
mg.AggregationDesigns.Remove(aggD.ID);
//Remove agg design from the tree
treeView1.SelectedNode = treeView1.SelectedNode.Parent.Parent;
treeView1.SelectedNode.Nodes.RemoveAt(0);
CreateNode(treeView1.SelectedNode.Nodes, TagAggdesigns, TagAggdesigns, ImgListMetadataFolderIndex);
treeView1.SelectedNode.Nodes[0].Expand();
if (!treeView1.SelectedNode.Text.EndsWith(MODIFIED_SUFFIX))
{
treeView1.SelectedNode.Text = treeView1.SelectedNode.Text + MODIFIED_SUFFIX;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
}
}
/// <summary>
/// Create cube core measure
/// </summary>
/// <param name="sqlHelper"></param>
/// <param name="asMeta"></param>
/// <param name="cube"></param>
public void CREATE_CUBE_CORE_MEASURES(DB_SQLHELPER_BASE sqlHelper
, AS_METADATA asMeta
, Cube cube)
{
try
{
DataTable coreMeasureSet = asMeta.GET_SSAS_CORE_MEASURES_SET(sqlHelper);
foreach (DataRow row in coreMeasureSet.Rows)
{
String measureGroupID = row["measure_group_id"].ToString();
String MeasureId = row["measure_id"].ToString();
String MeasureName = row["measure_name"].ToString();
String DSVSchemaName = row["dsv_schema_name"].ToString();
String DisplayFolder = row["display_folder"].ToString();
String FormatString = row["format_string"].ToString();
String MeasureDataType = row["measure_data_type"].ToString();
String DBColumn = row["db_column"].ToString();
String AggregationFunction = row["aggregation_function"].ToString();
MeasureGroup measureGroup = cube.MeasureGroups.Find(measureGroupID);
AS_API.ADD_MEASURE_TO_MEASURE_GROUP(
sqlHelper
, measureGroup
, DSVSchemaName
, DBColumn
, MeasureName
, MeasureId
, DisplayFolder
, FormatString
, AggregationFunction
, true
, MeasureDataType
, MeasureDataType);
measureGroup.Update();
}
}
catch (Exception ex)
{
sqlHelper.ADD_MESSAGE_LOG("Failed to create cube core measures:" + ex.Message.ToString(), MESSAGE_TYPE.MEASURES, MESSAGE_RESULT_TYPE.Error);
throw (ex);
}
}
static void CreateMeasureGroupPartitions(MeasureGroup mg, string query, string slice, DataSource ds, int year, int month_from, int month_to
, int part_size)
{
//mg.Partitions.Clear();
/*
* while (mg.Partitions.Count > 0)
* {
* mg.Partitions[0].Drop();
* }
*/
string pkey, pname;
for (int i = month_from; i <= month_to; i++)
{
pkey = String.Format("{0}{1:00}", year, i);
pname = CreatePartition(mg, pkey, query, slice, ds, part_size);
logMessageFmt("[{0}]: added partition [{1}].", mg.Name, pname);
}
mg.Update();
}
static private Partition PartitionFind(MeasureGroup aMeasureGroup, DateTime varDT)
{
string suffix = ToYYYYMM(varDT);
foreach (Partition p in aMeasureGroup.Partitions)
{
if (p.Name.EndsWith(suffix))
{
return(p);
}
}
suffix = ToYYYYMMDD(varDT);
foreach (Partition p in aMeasureGroup.Partitions)
{
if (p.Name.EndsWith(suffix))
{
return(p);
}
}
return(null);
}
/// <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);
}
}
public override void Exec()
{
try
{
IDesignerHost designer = (IDesignerHost)this.ApplicationObject.ActiveWindow.Object;
MeasureGroup mg = GetCurrentlySelectedMeasureGroup(designer);
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(true, "Checking Measure Group Health...", 1, 2);
Check(mg, designer);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(false, "Checking Measure Group Health...", 2, 2);
}
}
/// <summary>
/// Split out the Partitions and AggregationDesignss from a base cube
/// into their own cube for deserialization into a Partitions file
/// </summary>
/// <param name="baseCube">Cube to split</param>
/// <returns>Cube containing only partitions and aggregations</returns>
private static Cube SplitPartitionCube(Cube baseCube)
{
Cube partitionCube = new Cube();
foreach (MeasureGroup mg in baseCube.MeasureGroups)
{
MeasureGroup newMG = new MeasureGroup(mg.Name, mg.ID);
if ((mg.Partitions.Count == 0) && (mg.AggregationDesigns.Count == 0))
{
continue;
}
partitionCube.MeasureGroups.Add(newMG);
// Heisenberg principle in action with these objects; use 'for' instead of 'foreach'
if (mg.Partitions.Count > 0)
{
for (int i = 0; i < mg.Partitions.Count; ++i)
{
Partition partitionCopy = mg.Partitions[i].Clone();
newMG.Partitions.Add(partitionCopy);
}
}
// Heisenberg principle in action with these objects; use 'for' instead of 'foreach'
if (mg.AggregationDesigns.Count > 0)
{
for (int i = 0; i < mg.AggregationDesigns.Count; ++i)
{
AggregationDesign aggDesignCopy = mg.AggregationDesigns[i].Clone();
newMG.AggregationDesigns.Add(aggDesignCopy);
}
}
}
return(partitionCube);
}
public void Can_Deserialize_Measure_Groups()
{
string content = File.ReadAllText(SampleData.PathFor("MeasureGroupsResult.txt"));
RestResponse resp = new RestResponse()
{
Content = content
};
List <MeasureGroup> measureGroups = WithingsResourceResponse.DeserializeMeasuresResponse(resp);
Assert.IsNotNull(measureGroups);
Assert.IsTrue(measureGroups.Count == 2);
MeasureGroup grp = measureGroups[1];
Assert.AreEqual(grp.Date, new DateTime(2013, 09, 08, 15, 0, 40, 0, DateTimeKind.Utc));
Assert.IsTrue(grp.Attrib == AttributionType.ByDeviceAndValid);
Assert.IsTrue(grp.Grpid == 144518950);
Assert.IsTrue(grp.Measures.Count == 4);
Measure measure = grp.Measures[0];
Assert.IsTrue(measure.value == 90550);
Assert.IsTrue(measure.type == MeasureType.Weight);
Assert.IsTrue(measure.unit == -3);
}
private string ConvertAggToSting(Aggregation agg)
{
string outStr = "";
AggregationAttribute aggAttr;
AggregationDimension aggDim;
MeasureGroup mg1 = agg.ParentMeasureGroup;
foreach (MeasureGroupDimension mgDim in mg1.Dimensions)
{
aggDim = agg.Dimensions.Find(mgDim.CubeDimensionID);
if (aggDim == null)
{
foreach (CubeAttribute cubeDimAttr in mgDim.CubeDimension.Attributes)
{
outStr = outStr + "0";
}
}
else
{
foreach (CubeAttribute cubeDimAttr in mgDim.CubeDimension.Attributes)
{
aggAttr = aggDim.Attributes.Find(cubeDimAttr.AttributeID);
if (aggAttr == null)
{
outStr = outStr + "0";
}
else
{
outStr = outStr + "1";
}
}
}
outStr = outStr + ",";
}
return(outStr.Substring(0, outStr.Length - 1));
}
/// <summary>
/// Add reference dim usage
/// </summary>
/// <param name="measureGroup"></param>
/// <param name="referenceDimID"></param>
/// <param name="referenceDimAttrID"></param>
/// <param name="interDimID"></param>
/// <param name="interDimAttrID"></param>
internal static void ADD_DIM_USAGE_REFERENCE_RELATIONSHIP(
MeasureGroup measureGroup,
String referenceDimID,
String referenceDimAttrID,
String interDimID,
String interDimAttrID)
{
MeasureGroupDimension regDim = measureGroup.Dimensions.Find(referenceDimID);
if (regDim != null)
{
measureGroup.Dimensions.Remove(regDim);
}
ReferenceMeasureGroupDimension regMgDim = new ReferenceMeasureGroupDimension();
regMgDim.CubeDimensionID = referenceDimID;
regMgDim.IntermediateCubeDimensionID = interDimID;
MeasureGroupAttribute mgAttr = regMgDim.Attributes.Add(referenceDimAttrID);
mgAttr.Type = MeasureGroupAttributeType.Granularity;
regMgDim.IntermediateGranularityAttributeID = interDimAttrID;
regMgDim.Materialization = ReferenceDimensionMaterialization.Regular;
measureGroup.Dimensions.Add(regMgDim);
}
// Compare if attributeA is included in AttributeB
private static Boolean IsRedundantAttribute(MeasureGroup mg1, string DimId, string AttributeA, string AttributeB, Boolean CompareEstimatedCount, long OriginalEstimatedCount)
{
if (mg1 == null) return false;
long lngMembersDelta;
float flDeltaRatio;
float flPonderatedDeltaRatio;
long lngEstimatedCountToSendRecursively;
MeasureGroupDimension dimension = mg1.Dimensions.Find(DimId);
if (dimension == null) return false;
CubeAttribute cubeattribute = dimension.CubeDimension.Attributes.Find(AttributeB);
if (cubeattribute == null) return false;
// Check if AttributeA is included in AttributeB by
// standing on AttributeB and recursively searching for AttributeA in all its child relationships
foreach (AttributeRelationship attrRel in cubeattribute.Attribute.AttributeRelationships)
{
CubeAttribute childAttr = cubeattribute.Parent.Attributes.Find(attrRel.AttributeID);
if (attrRel.AttributeID == AttributeA)
{
// if CompareEstimatedCount is turn off, attribute is definitely included
if (!CompareEstimatedCount)
{ return true; }
else
{
// Else calculate the delta between Estimated Counts
if (OriginalEstimatedCount > 0)
{ lngMembersDelta = (OriginalEstimatedCount - childAttr.Attribute.EstimatedCount); }
else
{ lngMembersDelta = (cubeattribute.Attribute.EstimatedCount - childAttr.Attribute.EstimatedCount); }
// Calculate the ratio between the delta and the child estimated count.
flDeltaRatio = (float)lngMembersDelta / (float)childAttr.Attribute.EstimatedCount;
// Ponderate delta ratio by multiplying it by intMembersDelta
flPonderatedDeltaRatio = flDeltaRatio * (float)lngMembersDelta;
// Testings on different scenarios demostrated that if flPonderatedDeltaRatio > 1000 both attributes have much different cardinality.
if (flPonderatedDeltaRatio < 1000)
{
// if AttributeA is included in AttributeB and besides their cardinality are similar
// then A is definitely included in B.
return true;
}
}
}
if (childAttr.Attribute.AttributeRelationships.Count > 0)
{
// If in first iteration, OriginalEstimatedCount is null and base Estimated Count es get from AttributeB
// if second or later iteration, it pushes the OriginalEstimatedCount
if (OriginalEstimatedCount == -1)
{ lngEstimatedCountToSendRecursively = cubeattribute.Attribute.EstimatedCount; }
else
{ lngEstimatedCountToSendRecursively = OriginalEstimatedCount; }
if (IsRedundantAttribute(mg1, DimId, AttributeA, childAttr.AttributeID, CompareEstimatedCount, lngEstimatedCountToSendRecursively))
return true;
}
}
return false;
}
/*
* Create table mining structure (for measuregroups)
*/
private TableMiningStructureColumn CreateMiningStructureColumn(MeasureGroup measureGroup)
{
TableMiningStructureColumn column = new TableMiningStructureColumn();
column.Name = measureGroup.Name;
column.SourceMeasureGroup = new MeasureGroupBinding(".", ((Cube)measureGroup.Parent).ID, measureGroup.ID);
return column;
}
/// <summary>
/// Create measure group
/// </summary>
/// <param name="dsv"></param>
/// <param name="sqlHelper"></param>
/// <param name="asMeta"></param>
/// <param name="cube"></param>
/// <param name="is_rolap"></param>
/// <param name="measure_group_id"></param>
public void CREATE_MEASURE_GROUP(DataSourceView dsv
, DB_SQLHELPER_BASE sqlHelper
, AS_METADATA asMeta
, Cube cube
, int is_rolap_cube
, String measure_group_id = null)
{
try
{
DataTable MGSet = asMeta.GET_SSAS_MEASURE_GROUPS_SET(sqlHelper, is_rolap_cube);
String DSVSchemaName = "";
foreach (DataRow row in MGSet.Rows)
{
String measureGroupID = row["measure_group_id"].ToString();
String measureGroupName = row["measure_group_name"].ToString();
String DependedFactTable = row["depended_fact_table"].ToString();
String KeyNotFound_Action = row["key_not_found_action"].ToString();
int is_rolap_mg = Convert.ToInt16(row["is_rolap_mg"].ToString());
MeasureGroup newMG = AS_API.ADD_MEASURE_GROUP(sqlHelper, cube, measureGroupName, measureGroupID, is_rolap_mg, KeyNotFound_Action);
DataTable dimUsageSet = asMeta.GET_SSAS_DIM_USAGE_SET(sqlHelper, measureGroupID);
DataTable CoreMeasureSet = asMeta.GET_SSAS_CORE_MEASURES_SET(sqlHelper, measureGroupID);
foreach (DataRow measure in CoreMeasureSet.Rows)
{
measureGroupID = measure["measure_group_id"].ToString();
measureGroupName = measure["measure_group_name"].ToString();
String MeasureId = measure["measure_id"].ToString();
String MeasureName = measure["measure_name"].ToString();
String MeasureDataType = measure["measure_data_type"].ToString();
String DBColumn = measure["db_column"].ToString();
DSVSchemaName = measure["dsv_schema_name"].ToString();
String AggregationFunction = measure["aggregation_function"].ToString();
String DisplayFolder = measure["display_folder"].ToString();
String FormatString = measure["format_string"].ToString();
AS_API.ADD_MEASURE_TO_MEASURE_GROUP(
sqlHelper,
newMG,
DSVSchemaName,
DBColumn,
MeasureName,
MeasureId,
DisplayFolder,
FormatString,
AggregationFunction,
true,
MeasureDataType,
MeasureDataType);
}
foreach (DataRow dimUsage in dimUsageSet.Rows)
{
String DimUsageType = dimUsage["dim_usage_type"].ToString();
String InternalDimID = dimUsage["internal_dim_id"].ToString();
String InternalDimAttrID = dimUsage["internal_dim_attrid"].ToString();
DSVSchemaName = dimUsage["dsv_schema_name"].ToString();
String factFKDimColumnName = dimUsage["fact_fk_dim_column_name"].ToString();
String DataType = dimUsage["fact_fk_dim_column_data_type"].ToString();
String DimensionID = dimUsage["dimension_id"].ToString();
String AttributeID = dimUsage["attribute_id"].ToString();
String InternalMeasureGroupID = dimUsage["internal_measure_group_id"].ToString();
switch (DimUsageType.ToLower())
{
case "regular":
DataItem factDataItem = AS_API.CREATE_DATA_ITEM(
sqlHelper,
dsv,
DSVSchemaName,
factFKDimColumnName,
AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME(DataType));
AS_API.ADD_DIM_USAGE_REGULAR_RELATIONSHIP(
sqlHelper,
cube,
newMG,
factDataItem,
DimensionID,
AttributeID);
break;
case "reference":
AS_API.ADD_DIM_USAGE_REFERENCE_RELATIONSHIP(newMG,
DimensionID,
AttributeID,
InternalDimID,
InternalDimAttrID);
break;
case "manytomany":
AS_API.ADD_DIM_USAGE_MANY_RELATIONSHIP(newMG, InternalMeasureGroupID, DimensionID);
break;
case "fact":
AS_API.ADD_DIM_USAGE_FACT_RELATIONSHIP(newMG, InternalDimAttrID, DimensionID);
break;
default:
break;
}
}
AggregationDesign agg_design = CREATE_AGGREGATION_DESIGN(newMG, sqlHelper, asMeta);
CREATE_CUBE_PARTITION_FOR_MEASURE_GROUP(sqlHelper, asMeta, cube, is_rolap_cube, newMG, agg_design, is_rolap_mg, DependedFactTable, DSVSchemaName);
newMG.Update();
}
}
catch (Exception ex)
{
sqlHelper.ADD_MESSAGE_LOG(ex.Message.ToString(), MESSAGE_TYPE.MEASURE_GROUP, MESSAGE_RESULT_TYPE.Error);
throw (ex);
}
}
/*
* Fact Col1 - Granularity Attribute
* Col2 - Source Table name
*/
private static void getFactMeasureGroupAttributeUsage(List <DimensionUsage> dimUsage, MeasureGroup mg, DegenerateMeasureGroupDimension factMGdim)
{
DimensionUsage usage = null;
usage = new DimensionUsage("Fact", mg, factMGdim.CubeDimension, factMGdim.Dimension);
usage.Column1Name = "Granularity Attribute";
usage.Column2Name = "Source Table";
foreach (MeasureGroupAttribute mga in factMGdim.Attributes)
{
//mga.
if (mga.Type == MeasureGroupAttributeType.Granularity)
{
usage.Column1Value = mga.Attribute.Name;
foreach (DataItem di in mga.KeyColumns)
{
if (di.Source is ColumnBinding)
{
usage.Column2Value = ((ColumnBinding)di.Source).TableID;
DataSourceView dsv = mga.ParentCube.DataSourceView;
if (dsv.Schema.Tables.Contains(usage.Column2Value))
{
DataTable oTable = dsv.Schema.Tables[dsv.Schema.Tables.IndexOf(usage.Column2Value)];
if (oTable.ExtendedProperties.ContainsKey("FriendlyName"))
{
usage.Column2Value = oTable.ExtendedProperties["FriendlyName"].ToString();
}
}
}
}
}
}
dimUsage.Add(usage);
}
/*
* DataMining Col1 - Source Dimension Name
*
*/
private static void getDataMiningMeasureGroupAttributeUsage(List <DimensionUsage> dimUsage, MeasureGroup mg, DataMiningMeasureGroupDimension dmMDdim)
{
DimensionUsage usage = new DimensionUsage("Data Mining", mg, dmMDdim.CubeDimension, dmMDdim.Dimension);
usage.Column1Name = "Source Dimension";
usage.Column1Value = dmMDdim.Dimension.Name;
dimUsage.Add(usage);
}
/*
* All relationships - Relationship Type
* - Measure Group Name
* - Dim Name
*/
/*
* Regular Col1 - Ganularity Attribute Name
* Col2 - Dimension Column(s) Name
* Col3 - Measure Group Columns(s) Name
*/
private static void getRegularMeasureGroupAttributeUsage(List <DimensionUsage> dimUsage, MeasureGroup mg, RegularMeasureGroupDimension regMDdim)
{
string tableId = string.Empty;
DimensionUsage usage = new DimensionUsage("Regular", mg, regMDdim.CubeDimension, regMDdim.Dimension);//, cb.TableID, cb.ColumnID);
usage.Column1Name = "Granularity Attribute";
usage.Column2Name = "Dimension Column";
usage.Column3Name = "Measure Group Columns";
foreach (MeasureGroupAttribute mga in regMDdim.Attributes)
{
if (mga.Type == MeasureGroupAttributeType.Granularity)
{
tableId = string.Empty;
usage.Column1Value = mga.Attribute.Name;
System.Diagnostics.Trace.Write(",mga " + mga.CubeAttribute.Attribute.Name);
//foreach (DataItem di in mga.KeyColumns)
//{
//ColumnBinding cb = (ColumnBinding)di.Source;
// TODO - get the key columns for the attribute, not just its name
//System.Diagnostics.Trace.WriteLine(",di " + di.Source.ToString());
foreach (DataItem di2 in mga.Attribute.KeyColumns)
{
if (di2.Source is ColumnBinding)
{
tableId = ((ColumnBinding)di2.Source).TableID;
DataSourceView dsv = mga.Parent.Dimension.DataSourceView;
if (dsv.Schema.Tables.Contains(tableId))
{
DataTable oTable = dsv.Schema.Tables[dsv.Schema.Tables.IndexOf(tableId)];
if (oTable.ExtendedProperties.ContainsKey("FriendlyName"))
{
usage.Column2Value += oTable.ExtendedProperties["FriendlyName"] + ".";
}
}
else
{
usage.Column2Value += ((ColumnBinding)di2.Source).TableID + ".";
}
usage.Column2Value += ((ColumnBinding)di2.Source).ColumnID + "\n";
}
}
foreach (DataItem di3 in mga.KeyColumns)
{
if (di3.Source is ColumnBinding)
{
tableId = ((ColumnBinding)di3.Source).TableID;
DataSourceView dsv = mga.ParentCube.DataSourceView;
if (dsv.Schema.Tables.Contains(tableId))
{
DataTable oTable = dsv.Schema.Tables[dsv.Schema.Tables.IndexOf(tableId)];
if (oTable.ExtendedProperties.ContainsKey("FriendlyName"))
{
usage.Column3Value += oTable.ExtendedProperties["FriendlyName"] + ".";
}
}
else
{
usage.Column3Value += ((ColumnBinding)di3.Source).TableID + ".";
}
usage.Column3Value += ((ColumnBinding)di3.Source).ColumnID;
usage.Column3Value += " (" + di3.NullProcessing.ToString().Substring(0, 1) + ")";
usage.Column3Value += "\n";
}
}
//}
}
}
dimUsage.Add(usage);
}
public static void ShowAggsSimilaritiesReport(MeasureGroup mg, string sCorrectAggregationDesignName, Boolean bCountMembers)
{
AggregationDesign aggDesign = mg.AggregationDesigns.GetByName(sCorrectAggregationDesignName);
List<SimilarAgg> aggs = ListSimilarAggs(aggDesign, sCorrectAggregationDesignName, aggDesign.ParentCube.Name + " - " + aggDesign.Parent.Name + " measure group", bCountMembers);
ShowReport(aggs);
}
private void PopupDeleteUnusedAggsForm(MeasureGroup mg)
{
MeasureGroup cloneMG = null;
if (mg != null)
cloneMG = this.cloneDB.Cubes[mg.Parent.ID].MeasureGroups[mg.ID];
AggManager.DeleteUnusedAggs form1 = new AggManager.DeleteUnusedAggs();
form1.Init(mProjItem, cloneDB, cloneMG);
if (form1.ShowDialog(this) == DialogResult.OK)
{
foreach (TreeNode node in form1.treeViewAggregation.Nodes)
DeleteAggsAndRecurse(node);
}
}
public void Init(MeasureGroup mg,
string strParition,
EnvDTE.ProjectItem projItem)
{
try
{
bool IsOnlineMode = false;
Cube selectedCube = projItem.Object as Cube;
string serverName = "";
string databaseName = "";
string connectionString = "";
if ((selectedCube != null) && (selectedCube.ParentServer != null))
{
// if we are in Online mode there will be a parent server
serverName = selectedCube.ParentServer.Name;
databaseName = selectedCube.Parent.Name;
IsOnlineMode = true;
connectionString = selectedCube.ParentServer.ConnectionString;
}
else
{
// if we are in Project mode we will use the server name from
// the deployment settings
DeploymentSettings deploySet = new DeploymentSettings(projItem);
serverName = deploySet.TargetServer;
databaseName = deploySet.TargetDatabase; //use the target database instead of selectedCube.Parent.Name because selectedCube.Parent.Name only reflects the last place it was deployed to, and we want the user to be able to use the deployment settings to control which deployed server/database to check against
connectionString = "Data Source=" + serverName;
}
mg1 = mg;
part1 = mg.Partitions.FindByName(strParition);
aggDes = part1.AggregationDesign;
this.Text = " Aggregation sizes for partition " + strParition;
//lblSize.Text = part1.EstimatedRows.ToString() + " records"; //base this not on estimated rows but on actual rows... see below
lablPartName.Text = strParition;
txtServerNote.Text = string.Format("Note: The Partition size details have been taken from the currently deployed '{1}' database on the '{0}' server, " +
"which is the one currently configured as the deployment target.", serverName, databaseName);
txtServerNote.Visible = !IsOnlineMode;
//--------------------------------------------------------------------------------
// Open ADOMD connection to the server and issue DISCOVER_PARTITION_STAT request to get aggregation sizes
//--------------------------------------------------------------------------------
AdomdConnection adomdConnection = new AdomdConnection(connectionString);
adomdConnection.Open();
partitionDetails = adomdConnection.GetSchemaDataSet(AdomdSchemaGuid.PartitionStat, new object[] { databaseName, mg1.Parent.Name, mg1.Name, strParition });
DataColumn colItem1 = new DataColumn("Percentage", Type.GetType("System.String"));
partitionDetails.Tables[0].Columns.Add(colItem1);
AddGridStyle();
dataGrid1.DataSource = partitionDetails.Tables[0];
long iPartitionRowCount = 0;
if (partitionDetails.Tables[0].Rows.Count > 0)
{
iPartitionRowCount = Convert.ToInt64(partitionDetails.Tables[0].Rows[0]["AGGREGATION_SIZE"]);
}
lblSize.Text = iPartitionRowCount + " records";
double ratio = 0;
foreach (DataRow row in partitionDetails.Tables[0].Rows)
{
ratio = 100.0 * ((long)row["AGGREGATION_SIZE"] / (double)iPartitionRowCount);
row["Percentage"] = ratio.ToString("#0.00") + "%";
}
CurrencyManager cm = (CurrencyManager)this.BindingContext[dataGrid1.DataSource, dataGrid1.DataMember];
((DataView)cm.List).AllowNew = false;
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message);
try
{
this.Close();
}
catch { }
}
}
private static List<DimensionUsage> RecurseTabularRelationships(Dimension dMG, MeasureGroup mgOuter, bool bIsBusMatrix)
{
List<DimensionUsage> list = new List<DimensionUsage>();
foreach (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;
}
public void Init(MeasureGroup mg)
{
mg1 = mg;
}
public static void Check(MeasureGroup mg, IDesignerHost designer)
{
if (mg.Measures.Count == 0) throw new Exception(mg.Name + " has no measures.");
if (mg.IsLinked) throw new Exception(mg.Name + " is a linked measure group. Run this Measure Group Health Check on the source measure group.");
DataSource oDataSource = mg.Parent.DataSource;
DsvTableBinding oTblBinding = new DsvTableBinding(mg.Parent.DataSourceView.ID, GetTableIdForDataItem(mg.Measures[0].Source));
DataTable dtTable = mg.ParentDatabase.DataSourceViews[oTblBinding.DataSourceViewID].Schema.Tables[oTblBinding.TableID];
//check whether this fact table uses an alternate datasource
if (dtTable.ExtendedProperties.ContainsKey("DataSourceID"))
{
oDataSource = mg.ParentDatabase.DataSources[dtTable.ExtendedProperties["DataSourceID"].ToString()];
}
Microsoft.DataWarehouse.Design.DataSourceConnection openedDataSourceConnection = Microsoft.DataWarehouse.DataWarehouseUtilities.GetOpenedDataSourceConnection((object)null, oDataSource.ID, oDataSource.Name, oDataSource.ManagedProvider, oDataSource.ConnectionString, oDataSource.Site, false);
try
{
if (openedDataSourceConnection != null)
{
openedDataSourceConnection.QueryTimeOut = (int)oDataSource.Timeout.TotalSeconds;
}
}
catch { }
sq = openedDataSourceConnection.Cartridge.IdentStartQuote;
fq = openedDataSourceConnection.Cartridge.IdentEndQuote;
string sBitSqlDatatype = "bit";
string sCountBig = "count_big(";
string sCountBigEnd = ")";
string sFloorFunctionBegin = "floor(";
string sFloorFunctionEnd = ")";
if (openedDataSourceConnection.DBServerName == "Oracle")
{
sBitSqlDatatype = "number(1,0)";
sCountBig = "count(";
}
else if (openedDataSourceConnection.DBServerName == "Teradata")
{
sBitSqlDatatype = "numeric(1,0)";
sCountBig = "cast(count(";
sCountBigEnd = ") as bigint)";
sFloorFunctionBegin = "cast(";
sFloorFunctionEnd = " as bigint)";
}
string sFactQuery = GetQueryDefinition(mg.ParentDatabase, mg, oTblBinding, null);
StringBuilder sOuterQuery = new StringBuilder();
foreach (Measure m in mg.Measures)
{
//TODO: measure expressions
if ((m.AggregateFunction == AggregationFunction.Sum && !(m.Source.Source is RowBinding))
|| m.AggregateFunction == AggregationFunction.AverageOfChildren
|| m.AggregateFunction == AggregationFunction.ByAccount
|| m.AggregateFunction == AggregationFunction.FirstChild
|| m.AggregateFunction == AggregationFunction.FirstNonEmpty
|| m.AggregateFunction == AggregationFunction.LastChild
|| m.AggregateFunction == AggregationFunction.LastNonEmpty
|| m.AggregateFunction == AggregationFunction.Min
|| m.AggregateFunction == AggregationFunction.Max
|| m.AggregateFunction == AggregationFunction.None)
{
ColumnBinding cb = GetColumnBindingForDataItem(m.Source);
DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID];
if (col.DataType == typeof(DateTime))
continue; //DateTime not supported by BIDS Helper except for count and distinct count aggregates
if (sOuterQuery.Length > 0)
sOuterQuery.Append(",");
else
sOuterQuery.Append("select ");
string sNegativeSign = string.Empty;
if (col.DataType == typeof(bool))
sNegativeSign = "-"; //true in sql is 1, but SSAS treats true as -1
if (m.AggregateFunction == AggregationFunction.Min
|| m.AggregateFunction == AggregationFunction.Max
|| m.AggregateFunction == AggregationFunction.None)
sOuterQuery.Append("max(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine();
else
sOuterQuery.Append("sum(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine();
sOuterQuery.Append(",min(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine();
sOuterQuery.Append(",max(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine();
sOuterQuery.Append(",cast(max(case when ").Append(sFloorFunctionBegin).Append(sq).Append(cb.ColumnID).Append(fq).Append(sFloorFunctionEnd).Append(" <> ").Append(sq).Append(cb.ColumnID).Append(fq).Append(" then 1 else 0 end) as ").Append(sBitSqlDatatype).Append(")").AppendLine();
sOuterQuery.Append(",cast(max(case when ").Append(sFloorFunctionBegin).Append(sq).Append(cb.ColumnID).Append(fq).Append("*10000.0").Append(sFloorFunctionEnd).Append(" <> cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append("*10000.0 as float) then 1 else 0 end) as ").Append(sBitSqlDatatype).Append(")").AppendLine();
}
else if (m.AggregateFunction == AggregationFunction.Count
|| m.AggregateFunction == AggregationFunction.DistinctCount
|| (m.AggregateFunction == AggregationFunction.Sum && m.Source.Source is RowBinding))
{
if (sOuterQuery.Length > 0)
sOuterQuery.Append(",");
else
sOuterQuery.Append("select ");
if (m.Source.Source is RowBinding)
{
if (m.AggregateFunction == AggregationFunction.DistinctCount)
throw new Exception("RowBinding on a distinct count not allowed by Analysis Services");
else
sOuterQuery.Append(sCountBig).Append("*").Append(sCountBigEnd).AppendLine();
sOuterQuery.Append(",0").AppendLine();
sOuterQuery.Append(",1").AppendLine();
sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine();
sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine();
}
else
{
ColumnBinding cb = GetColumnBindingForDataItem(m.Source);
DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID];
if (m.AggregateFunction == AggregationFunction.DistinctCount)
sOuterQuery.Append(sCountBig).Append("distinct ").Append(sq).Append(cb.ColumnID).Append(fq).Append(sCountBigEnd).AppendLine();
else if (col.DataType == typeof(Byte[]))
sOuterQuery.Append("sum(cast(case when ").Append(sq).Append(cb.ColumnID).Append(fq).Append(" is not null then 1 else 0 end as float))").AppendLine();
else
sOuterQuery.Append(sCountBig).Append(sq).Append(cb.ColumnID).Append(fq).Append(sCountBigEnd).AppendLine();
sOuterQuery.Append(",0").AppendLine();
sOuterQuery.Append(",1").AppendLine();
sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine();
sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine();
}
}
else
{
throw new Exception("Aggregation function " + m.AggregateFunction.ToString() + " not supported!");
}
}
if (sOuterQuery.Length == 0) return;
sOuterQuery.AppendLine("from (").Append(sFactQuery).AppendLine(") fact");
DataSet ds = new DataSet();
//openedDataSourceConnection.QueryTimeOut = 0; //just inherit from the datasource
openedDataSourceConnection.Fill(ds, sOuterQuery.ToString());
DataRow row = ds.Tables[0].Rows[0];
openedDataSourceConnection.Close();
List<MeasureHealthCheckResult> measureResults = new List<MeasureHealthCheckResult>();
int i = 0;
foreach (Measure m in mg.Measures)
{
if (m.AggregateFunction == AggregationFunction.Sum
|| m.AggregateFunction == AggregationFunction.AverageOfChildren
|| m.AggregateFunction == AggregationFunction.ByAccount
|| m.AggregateFunction == AggregationFunction.FirstChild
|| m.AggregateFunction == AggregationFunction.FirstNonEmpty
|| m.AggregateFunction == AggregationFunction.LastChild
|| m.AggregateFunction == AggregationFunction.LastNonEmpty
|| m.AggregateFunction == AggregationFunction.Count
|| m.AggregateFunction == AggregationFunction.DistinctCount
|| m.AggregateFunction == AggregationFunction.Min
|| m.AggregateFunction == AggregationFunction.Max
|| m.AggregateFunction == AggregationFunction.None)
{
double dsvColMaxValue = 0;
bool dsvColAllowsDecimals = false;
if (m.Source.Source is ColumnBinding && m.AggregateFunction != AggregationFunction.Count && m.AggregateFunction != AggregationFunction.DistinctCount)
{
ColumnBinding cb = GetColumnBindingForDataItem(m.Source);
DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID];
if (col.DataType == typeof(DateTime))
continue; //DateTime not supported by BIDS Helper except for count and distinct count aggregates
MeasureDataTypeOption dsvOption = GetMeasureDataTypeOptionForType(col.DataType);
if (dsvOption != null)
{
dsvColMaxValue = dsvOption.max;
dsvColAllowsDecimals = dsvOption.allowsDecimals;
}
}
double? total = (!Convert.IsDBNull(row[i * 5]) ? Convert.ToDouble(row[i * 5]) : (double?)null);
double? min = (!Convert.IsDBNull(row[i * 5 + 1]) ? Convert.ToDouble(row[i * 5 + 1]) : (double?)null);
double? max = (!Convert.IsDBNull(row[i * 5 + 2]) ? Convert.ToDouble(row[i * 5 + 2]) : (double?)null);
bool hasDecimals = (!Convert.IsDBNull(row[i * 5 + 3]) ? Convert.ToBoolean(row[i * 5 + 3]) : false);
bool hasMoreThan4Decimals = (!Convert.IsDBNull(row[i * 5 + 4]) ? Convert.ToBoolean(row[i * 5 + 4]) : false);
MeasureDataTypeOption oldDataTypeOption = GetMeasureDataTypeOptionForMeasure(m);
double recommendedMaxValue = double.MaxValue;
List<MeasureDataTypeOption> possible = new List<MeasureDataTypeOption>();
foreach (MeasureDataTypeOption option in dataTypeOptions)
{
if (
(total == null || (option.max >= total && option.min <= total))
&& (max == null || option.max >= max)
&& (min == null || option.min <= min)
&& (!hasDecimals || option.allowsDecimals)
&& (!hasMoreThan4Decimals || option.allowsMoreThan4Decimals)
)
{
possible.Add(option);
if (
(total == null || (total * FreeSpaceFactor < option.max && total * FreeSpaceFactor > option.min))
&& option.max < recommendedMaxValue
&& option.max >= dsvColMaxValue
&& (dsvColAllowsDecimals == option.allowsDecimals)
&& (option.oleDbType != OleDbType.Single) //never recommend Single
)
{
recommendedMaxValue = option.max;
}
}
}
foreach (MeasureDataTypeOption option in dataTypeOptions)
{
if (option.max == recommendedMaxValue)
{
Type dsvDataType = null; //don't bother getting the DSV datatype for count or DistinctCount measures
if (m.Source.Source is ColumnBinding && m.AggregateFunction != AggregationFunction.Count && m.AggregateFunction != AggregationFunction.DistinctCount)
{
ColumnBinding cb = GetColumnBindingForDataItem(m.Source);
DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID];
dsvDataType = col.DataType;
}
MeasureHealthCheckResult result = new MeasureHealthCheckResult(m, FormatDouble(total), FormatDouble(min), FormatDouble(max), hasDecimals, hasMoreThan4Decimals, possible.ToArray(), option, oldDataTypeOption, dsvDataType);
measureResults.Add(result);
break;
}
}
i++;
}
else
{
throw new Exception("Aggregation function " + m.AggregateFunction.ToString() + " not supported");
}
}
BIDSHelper.SSAS.MeasureGroupHealthCheckForm form = new BIDSHelper.SSAS.MeasureGroupHealthCheckForm();
form.measureDataTypeOptionBindingSource.DataSource = dataTypeOptions;
form.measureHealthCheckResultBindingSource.DataSource = measureResults;
form.Text = "Measure Group Health Check: " + mg.Name;
DialogResult dialogResult = form.ShowDialog();
if (dialogResult == DialogResult.OK)
{
foreach (MeasureHealthCheckResult r in measureResults)
{
if (r.CurrentDataType != r.DataType)
{
//save change
if (r.Measure.AggregateFunction == AggregationFunction.Count
|| r.Measure.AggregateFunction == AggregationFunction.DistinctCount)
{
r.Measure.DataType = r.DataType.dataType;
}
else
{
r.Measure.Source.DataType = r.DataType.oleDbType;
r.Measure.DataType = MeasureDataType.Inherited;
}
//mark cube object as dirty
IComponentChangeService changesvc = (IComponentChangeService)designer.GetService(typeof(IComponentChangeService));
changesvc.OnComponentChanging(mg.Parent, null);
changesvc.OnComponentChanged(mg.Parent, null, null, null); //marks the cube designer as dirty
}
}
}
}
// Generates content of pivotTableCacheDefinitionPart1.
private void GeneratePivotTableCacheDefinitionPart1Content(PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1)
{
PivotCacheDefinition pivotCacheDefinition1 = new PivotCacheDefinition(){ SaveData = false, RefreshedBy = "Paul Berruti", RefreshedDate = 40855.575896064816D, BackgroundQuery = true, CreatedVersion = 5, RefreshedVersion = 5, MinRefreshableVersion = 3, RecordCount = (UInt32Value)0U, SupportSubquery = true, SupportAdvancedDrill = true };
pivotCacheDefinition1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
CacheSource cacheSource1 = new CacheSource(){ Type = SourceValues.External, ConnectionId = (UInt32Value)3U };
CacheFields cacheFields1 = new CacheFields(){ Count = (UInt32Value)0U };
CacheHierarchies cacheHierarchies1 = new CacheHierarchies(){ Count = (UInt32Value)11U };
CacheHierarchy cacheHierarchy1 = new CacheHierarchy(){ UniqueName = "[Query].[account_id]", Caption = "account_id", Attribute = true, DefaultMemberUniqueName = "[Query].[account_id].[All]", AllUniqueName = "[Query].[account_id].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false };
CacheHierarchy cacheHierarchy2 = new CacheHierarchy(){ UniqueName = "[Query].[account_parent]", Caption = "account_parent", Attribute = true, DefaultMemberUniqueName = "[Query].[account_parent].[All]", AllUniqueName = "[Query].[account_parent].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false };
CacheHierarchy cacheHierarchy3 = new CacheHierarchy(){ UniqueName = "[Query].[account_description]", Caption = "account_description", Attribute = true, DefaultMemberUniqueName = "[Query].[account_description].[All]", AllUniqueName = "[Query].[account_description].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false };
CacheHierarchy cacheHierarchy4 = new CacheHierarchy(){ UniqueName = "[Query].[account_type]", Caption = "account_type", Attribute = true, DefaultMemberUniqueName = "[Query].[account_type].[All]", AllUniqueName = "[Query].[account_type].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false };
CacheHierarchy cacheHierarchy5 = new CacheHierarchy(){ UniqueName = "[Query].[account_rollup]", Caption = "account_rollup", Attribute = true, DefaultMemberUniqueName = "[Query].[account_rollup].[All]", AllUniqueName = "[Query].[account_rollup].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false };
CacheHierarchy cacheHierarchy6 = new CacheHierarchy(){ UniqueName = "[Query].[Custom Members]", Caption = "Custom Members", Attribute = true, DefaultMemberUniqueName = "[Query].[Custom Members].[All]", AllUniqueName = "[Query].[Custom Members].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false };
CacheHierarchy cacheHierarchy7 = new CacheHierarchy(){ UniqueName = "[Query].[CurrentUser]", Caption = "CurrentUser", Attribute = true, DefaultMemberUniqueName = "[Query].[CurrentUser].[All]", AllUniqueName = "[Query].[CurrentUser].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false };
CacheHierarchy cacheHierarchy8 = new CacheHierarchy(){ UniqueName = "[Query].[RefreshTime]", Caption = "RefreshTime", Attribute = true, DefaultMemberUniqueName = "[Query].[RefreshTime].[All]", AllUniqueName = "[Query].[RefreshTime].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false };
CacheHierarchy cacheHierarchy9 = new CacheHierarchy(){ UniqueName = "[Query].[RowNumber]", Caption = "RowNumber", Attribute = true, KeyAttribute = true, DefaultMemberUniqueName = "[Query].[RowNumber].[All]", AllUniqueName = "[Query].[RowNumber].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false, Hidden = true };
CacheHierarchy cacheHierarchy10 = new CacheHierarchy(){ UniqueName = "[Measures].[__XL_Count Query]", Caption = "__XL_Count Query", Measure = true, DisplayFolder = "", MeasureGroup = "Query", Count = (UInt32Value)0U, Hidden = true };
CacheHierarchy cacheHierarchy11 = new CacheHierarchy(){ UniqueName = "[Measures].[__XL_Count of Models]", Caption = "__XL_Count of Models", Measure = true, DisplayFolder = "", Count = (UInt32Value)0U, Hidden = true };
cacheHierarchies1.Append(cacheHierarchy1);
cacheHierarchies1.Append(cacheHierarchy2);
cacheHierarchies1.Append(cacheHierarchy3);
cacheHierarchies1.Append(cacheHierarchy4);
cacheHierarchies1.Append(cacheHierarchy5);
cacheHierarchies1.Append(cacheHierarchy6);
cacheHierarchies1.Append(cacheHierarchy7);
cacheHierarchies1.Append(cacheHierarchy8);
cacheHierarchies1.Append(cacheHierarchy9);
cacheHierarchies1.Append(cacheHierarchy10);
cacheHierarchies1.Append(cacheHierarchy11);
Kpis kpis1 = new Kpis(){ Count = (UInt32Value)0U };
Dimensions dimensions1 = new Dimensions(){ Count = (UInt32Value)2U };
Dimension dimension1 = new Dimension(){ Measure = true, Name = "Measures", UniqueName = "[Measures]", Caption = "Measures" };
Dimension dimension2 = new Dimension(){ Name = "Query", UniqueName = "[Query]", Caption = "Query" };
dimensions1.Append(dimension1);
dimensions1.Append(dimension2);
MeasureGroups measureGroups1 = new MeasureGroups(){ Count = (UInt32Value)1U };
MeasureGroup measureGroup1 = new MeasureGroup(){ Name = "Query", Caption = "Query" };
measureGroups1.Append(measureGroup1);
Maps maps1 = new Maps(){ Count = (UInt32Value)1U };
MeasureDimensionMap measureDimensionMap1 = new MeasureDimensionMap(){ MeasureGroup = (UInt32Value)0U, Dimension = (UInt32Value)1U };
maps1.Append(measureDimensionMap1);
PivotCacheDefinitionExtensionList pivotCacheDefinitionExtensionList1 = new PivotCacheDefinitionExtensionList();
PivotCacheDefinitionExtension pivotCacheDefinitionExtension1 = new PivotCacheDefinitionExtension(){ Uri = "{725AE2AE-9491-48be-B2B4-4EB974FC3084}" };
pivotCacheDefinitionExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.PivotCacheDefinition pivotCacheDefinition2 = new X14.PivotCacheDefinition(){ SupportSubqueryNonVisual = true, SupportSubqueryCalcMem = true, SupportAddCalcMems = true };
pivotCacheDefinitionExtension1.Append(pivotCacheDefinition2);
pivotCacheDefinitionExtensionList1.Append(pivotCacheDefinitionExtension1);
pivotCacheDefinition1.Append(cacheSource1);
pivotCacheDefinition1.Append(cacheFields1);
pivotCacheDefinition1.Append(cacheHierarchies1);
pivotCacheDefinition1.Append(kpis1);
pivotCacheDefinition1.Append(dimensions1);
pivotCacheDefinition1.Append(measureGroups1);
pivotCacheDefinition1.Append(maps1);
pivotCacheDefinition1.Append(pivotCacheDefinitionExtensionList1);
pivotTableCacheDefinitionPart1.PivotCacheDefinition = pivotCacheDefinition1;
}
private static List<string> GetRelatedTables(Dimension dMG, MeasureGroup mgOuter)
{
List<string> list = new List<string>();
foreach (Relationship relOuter in dMG.Relationships)
{
bool bFound = false;
MeasureGroup mgFrom = dMG.Parent.Cubes[0].MeasureGroups[relOuter.FromRelationshipEnd.DimensionID];
Dimension dTo = dMG.Parent.Dimensions[relOuter.ToRelationshipEnd.DimensionID];
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;
}
}
if (!bFound)
{
continue; //don't show inactive relationships
}
list.Add(dToCube.Name);
list.AddRange(GetRelatedTables(dTo, mgOuter));
}
return list;
}
public void Init(MeasureGroup mg)
{
mg1 = mg;
textBoxFile.Text = mg.Name + textBoxFile.Text;
}
public void CreatePartition(MeasureGroup measureGroup, DataSource relationalDataSource, String tableName, DateTime dateTime)
{
string partitionName = tableName + DateTime.Now.ToString("yyyyMM");
if (measureGroup.Partitions.IndexOfName(partitionName) < 0)
{
var partition = measureGroup.Partitions.Add(partitionName);
partition.Source = new QueryBinding(relationalDataSource.ID, "Select * From " + tableName);
partition.StorageMode = StorageMode.Molap;
}
}
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);
}
/*
* Referenced Col1 - Ref Dim Attrib Name
* Col2 - Intermediate Dim Name
* Col3 - Intermediate Dim Attib Name
* Col4 - Path
* ?? Materialized
*/
private static void getReferencedMeasureGroupAttributeUsage(List<DimensionUsage> dimUsage, MeasureGroup mg, ReferenceMeasureGroupDimension refMgDim)
{
DimensionUsage usage = new DimensionUsage("Referenced",mg, refMgDim.CubeDimension, refMgDim.Dimension);
string tableId = string.Empty;
usage.Column1Name = "Reference Dimension Attribute";
foreach (CubeAttribute a in refMgDim.CubeDimension.Attributes)
{
if (a.Attribute.Usage == AttributeUsage.Key)
{
usage.Column1Value = a.Attribute.Name;
break;
}
}
usage.Column2Name = "Intermediate Dimension";
usage.Column2Value = refMgDim.IntermediateDimension.Name;
usage.Column3Name = "Intermediate Dimension Attribute";
usage.Column3Value = refMgDim.IntermediateGranularityAttribute.Attribute.Name;
// not currently exposed on the report due to space limitation
// the string (Materialized) is added after the dim name instead.
usage.Column4Name = "Materialized";
usage.Column4Value = refMgDim.Materialization.ToString();
usage.Materialized = (refMgDim.Materialization == ReferenceDimensionMaterialization.Regular);
dimUsage.Add(usage);
}
/*
* Many Col1 - Intermediate Measure Group Name
*
*/
private static void getManyToManyMeasureGroupAttributeUsage(List <DimensionUsage> dimUsage, MeasureGroup mg, ManyToManyMeasureGroupDimension m2mMDdim)
{
DimensionUsage usage = new DimensionUsage("Many to Many", mg, m2mMDdim.CubeDimension, m2mMDdim.Dimension);
usage.Column1Name = "Intermediate Measure Group";
usage.Column1Value = m2mMDdim.MeasureGroup.Name;
dimUsage.Add(usage);
}
/*
* Fact Col1 - Granularity Attribute
* Col2 - Source Table name
*/
private static void getFactMeasureGroupAttributeUsage(List<DimensionUsage> dimUsage, MeasureGroup mg, DegenerateMeasureGroupDimension factMGdim)
{
DimensionUsage usage = null;
usage = new DimensionUsage("Fact", mg, factMGdim.CubeDimension, factMGdim.Dimension);
usage.Column1Name = "Granularity Attribute";
usage.Column2Name = "Source Table";
foreach (MeasureGroupAttribute mga in factMGdim.Attributes)
{
//mga.
if (mga.Type == MeasureGroupAttributeType.Granularity)
{
usage.Column1Value = mga.Attribute.Name;
foreach (DataItem di in mga.KeyColumns)
{
if (di.Source is ColumnBinding)
{
usage.Column2Value = ((ColumnBinding)di.Source).TableID;
DataSourceView dsv = mga.ParentCube.DataSourceView;
if (dsv.Schema.Tables.Contains(usage.Column2Value))
{
DataTable oTable = dsv.Schema.Tables[dsv.Schema.Tables.IndexOf(usage.Column2Value)];
if (oTable.ExtendedProperties.ContainsKey("FriendlyName"))
{
usage.Column2Value = oTable.ExtendedProperties["FriendlyName"].ToString();
}
}
}
}
}
}
dimUsage.Add(usage);
}
/*
* Referenced Col1 - Ref Dim Attrib Name
* Col2 - Intermediate Dim Name
* Col3 - Intermediate Dim Attib Name
* Col4 - Path
* ?? Materialized
*/
private static void getReferencedMeasureGroupAttributeUsage(List <DimensionUsage> dimUsage, MeasureGroup mg, ReferenceMeasureGroupDimension refMgDim)
{
DimensionUsage usage = new DimensionUsage("Referenced", mg, refMgDim.CubeDimension, refMgDim.Dimension);
string tableId = string.Empty;
usage.Column1Name = "Reference Dimension Attribute";
foreach (CubeAttribute a in refMgDim.CubeDimension.Attributes)
{
if (a.Attribute.Usage == AttributeUsage.Key)
{
usage.Column1Value = a.Attribute.Name;
break;
}
}
usage.Column2Name = "Intermediate Dimension";
usage.Column2Value = refMgDim.IntermediateDimension.Name;
usage.Column3Name = "Intermediate Dimension Attribute";
usage.Column3Value = refMgDim.IntermediateGranularityAttribute.Attribute.Name;
// not currently exposed on the report due to space limitation
// the string (Materialized) is added after the dim name instead.
usage.Column4Name = "Materialized";
usage.Column4Value = refMgDim.Materialization.ToString();
usage.Materialized = (refMgDim.Materialization == ReferenceDimensionMaterialization.Regular);
dimUsage.Add(usage);
}
private void CreateNewPartition(MeasureGroup mg)
{
Partition part;
part = mg.Partitions.Add(this.CurrentPartitionID);
part.StorageMode = StorageMode.Molap;
part.Source = new QueryBinding(mg.Parent.Source.DataSourceViewID, "NotNeeded");
part.Annotations.Add(new Annotation(NUM_SNAPSHOTS_ANNOTATION_NAME, "1"));
//setting the slice won't work because you can't modify it for future data without unprocessing the partition... have to rely on the auto-slice which should do fine in this situation for determining which partition a snapshot date is in
if (!mg.AggregationDesigns.Contains(AGG_DESIGN_NAME))
{
AggregationDesign design = mg.AggregationDesigns.Add(AGG_DESIGN_NAME);
Aggregation agg = design.Aggregations.Add("Agg1");
AggregationDimension aggdim;
aggdim = agg.Dimensions.Add("Snapshot Date");
aggdim.Attributes.Add(aggdim.Dimension.KeyAttribute.ID);
aggdim = agg.Dimensions.Add("Memory");
aggdim.Attributes.Add(aggdim.Dimension.Attributes["Level 4"].ID);
}
part.AggregationDesignID = AGG_DESIGN_NAME;
}
public static List <DimensionUsage> GetTabularDimensionUsage(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox, bool bIsBusMatrix)
{
Cube c = sandbox.Cube;
List <CubeDimension> listCubeDimensions = new List <CubeDimension>();
List <DimensionUsage> dimUsage = new List <DimensionUsage>();
foreach (CubeDimension cd in c.Dimensions)
{
bool bFoundVisibleAttribute = false;
foreach (DimensionAttribute da in cd.Dimension.Attributes)
{
if (da.AttributeHierarchyVisible)
{
bFoundVisibleAttribute = true;
break;
}
}
if (bFoundVisibleAttribute)
{
listCubeDimensions.Add(cd);
}
bool bFoundVisibleMeasure = false;
foreach (Microsoft.AnalysisServices.BackEnd.DataModelingMeasure m in sandbox.Measures)
{
if (m.Table == cd.Dimension.Name && !m.IsPrivate)
{
bFoundVisibleMeasure = true;
break;
}
}
if (!bFoundVisibleMeasure && bIsBusMatrix)
{
continue;
}
MeasureGroup mg = c.MeasureGroups[cd.DimensionID];
List <DimensionUsage> tmp = RecurseTabularRelationships(cd.Dimension, mg, bIsBusMatrix);
dimUsage.AddRange(tmp);
if (bFoundVisibleAttribute && bFoundVisibleMeasure) //if this table had a measure but no dimension relationships (except to itself)
{
DimensionUsage du = new DimensionUsage("Fact", mg, cd, cd.Dimension);
dimUsage.Add(du);
}
else if (tmp.Count == 0 && bIsBusMatrix && bFoundVisibleMeasure) //if this table with a measure had no dimension relationships, add it as such...
{
DimensionUsage du = new DimensionUsage(string.Empty, mg, null, null);
dimUsage.Add(du);
}
}
//remove dimensions in relationships
foreach (DimensionUsage du in dimUsage)
{
for (int i = 0; i < listCubeDimensions.Count; i++)
{
if (listCubeDimensions[i].Name == du.DimensionName)
{
listCubeDimensions.RemoveAt(i);
i--;
}
}
}
//add any cube dimensions which aren't related to any measure groups
foreach (CubeDimension cd in listCubeDimensions)
{
DimensionUsage du = new DimensionUsage(string.Empty, null, cd, cd.Dimension);
dimUsage.Add(du);
}
return(dimUsage);
}
private static DateTime FindMaxLastProcessedDateInMeasureGroup(MeasureGroup MeasureGroup, string PartitionName, bool IncludeMeasureGroupLastProcessed)
{
DateTime result = DateTime.MinValue;
if (IncludeMeasureGroupLastProcessed)
{
result = MeasureGroup.LastProcessed;
}
//Look at one specific partition
if (!string.IsNullOrEmpty(PartitionName))
{
Partition partition = MeasureGroup.Partitions.GetByName(PartitionName);
if (result < partition.LastProcessed)
{
result = partition.LastProcessed;
}
}
else
{
//Iterate over all partitions and search for max()
foreach (Partition curPartition in MeasureGroup.Partitions)
{
if (result < curPartition.LastProcessed)
{
result = curPartition.LastProcessed;
}
}
}
return result;
}
/// <summary>
/// CREATE_CUBE_PARTITION_FOR_MEASURE_GROUP
/// </summary>
/// <param name="sqlHelper"></param>
/// <param name="asMeta"></param>
/// <param name="cube"></param>
/// <param name="is_rolap_cube"></param>
/// <param name="mg"></param>
/// <param name="aggregation_design"></param>
/// <param name="is_rolap_mg"></param>
/// <param name="depended_fact_table"></param>
/// <param name="DSVSchemaName"></param>
public void CREATE_CUBE_PARTITION_FOR_MEASURE_GROUP(
DB_SQLHELPER_BASE sqlHelper
, AS_METADATA asMeta
, Cube cube
, int is_rolap_cube
, MeasureGroup mg
, AggregationDesign aggregation_design
, int is_rolap_mg
, String depended_fact_table
, String DSVSchemaName)
{
DataTable partition_date_filter = asMeta.GET_SSAS_PARTITION_SET(sqlHelper, mg.ID);
String aggregation_design_id = null;
if (aggregation_design != null)
{
aggregation_design_id = aggregation_design.ID.ToString();
}
if (partition_date_filter != null && partition_date_filter.Rows != null && partition_date_filter.Rows.Count > 0)
{
String factFKDimColumnName = partition_date_filter.Rows[0]["fact_fk_dim_column_name"].ToString();
int month_volumn_per_partition = Convert.ToInt32(CONFIGURATION_HELPER.GET_METADATA_PROPERTY("month_volumn_per_partition"));
int year_volumn_per_cube = Convert.ToInt32(CONFIGURATION_HELPER.GET_METADATA_PROPERTY("year_volumn_per_cube"));
int partitionCount = 0;
partitionCount = year_volumn_per_cube * 12 / month_volumn_per_partition;
for (int i = 1; i <= partitionCount + 1; i++)
{
String partitionSelectQuery = "";// String.Format("SELECT * FROM {0} WHERE 1=1 ", DSVSchemaName);
//if rolap cube, and current mg is molap, then add where 1=2 filter, select * from tb_name where 1=1 and 1=2
if (is_rolap_cube == 1 && is_rolap_mg == 0)
{
partitionSelectQuery = partitionSelectQuery + " AND 1=2";
}
//if rolap cube, then no need additional date column filter, if molap , then need date column filter,
//select * from tb_name where 1=1 and dateid>=20100101 and dateid<20100201
if (is_rolap_cube == 0)
{
partitionSelectQuery = partitionSelectQuery + " " + asMeta.GET_SSAS_PARTITION_FILTER(factFKDimColumnName, i, month_volumn_per_partition);
}
AS_API.CREATE_MOLAP_PARTITION(
sqlHelper,
mg,
"DW_DataSource",
mg.ID.ToString() + "_" + i.ToString(),
DSVSchemaName,
partitionSelectQuery,
aggregation_design_id,
is_rolap_mg,
depended_fact_table
);
//if rolap cube, then only need one partition
if (is_rolap_cube == 1)
{
break;
}
}
}
else
{
sqlHelper.ADD_MESSAGE_LOG(String.Format("Create cube partition-> No partition date column been detected for mg {0}", mg.ID), MESSAGE_TYPE.PARTITION, MESSAGE_RESULT_TYPE.Warning);
AS_API.CREATE_MOLAP_PARTITION(
sqlHelper,
mg,
"DW_DataSource",
mg.ID.ToString() + "_1",
DSVSchemaName,
"",
aggregation_design_id,
is_rolap_mg,
depended_fact_table
);
}
}
private void FillTree(MeasureGroup mg)
{
TreeNode parentNode = treeViewAggregation.Nodes.Add(mg.Name);
parentNode.Tag = mg;
parentNode.ImageIndex = parentNode.SelectedImageIndex = 0;
bool bAllAggDesignsChecked = true;
foreach (AggregationDesign aggDesign in mg.AggregationDesigns)
{
if (aggDesign.Aggregations.Count == 0) continue;
bool bAggDesignUsed = false;
foreach (Partition p in mg.Partitions)
{
if (p.AggregationDesignID == aggDesign.ID)
{
bAggDesignUsed = true;
break;
}
}
if (!bAggDesignUsed) continue;
TreeNode aggDesignNode = parentNode.Nodes.Add(aggDesign.Name);
aggDesignNode.Tag = aggDesign;
aggDesignNode.ImageIndex = aggDesignNode.SelectedImageIndex = 1;
bool bAllAggsChecked = true;
foreach (Aggregation agg in aggDesign.Aggregations)
{
int iAggHits = (dictHitAggs.ContainsKey(agg) ? dictHitAggs[agg] : 0);
TreeNode aggNode = aggDesignNode.Nodes.Add(agg.Name + (iAggHits > 0 ? " (" + iAggHits.ToString("d") + " agg hits)" : ""));
aggNode.Tag = agg;
aggNode.ImageIndex = aggNode.SelectedImageIndex = 2;
aggNode.Checked = !dictHitAggs.ContainsKey(agg);
bAllAggsChecked = bAllAggsChecked && aggNode.Checked;
}
aggDesignNode.Checked = bAllAggsChecked;
aggDesignNode.StateImageIndex = 2;
bAllAggDesignsChecked = bAllAggDesignsChecked && bAllAggsChecked;
}
parentNode.Checked = bAllAggDesignsChecked;
if (parentNode.Nodes.Count == 0) treeViewAggregation.Nodes.Remove(parentNode);
}
/*
* All relationships - Relationship Type
* - Measure Group Name
* - Dim Name
*/
/*
* Regular Col1 - Ganularity Attribute Name
* Col2 - Dimension Column(s) Name
* Col3 - Measure Group Columns(s) Name
*/
private static void getRegularMeasureGroupAttributeUsage(List<DimensionUsage> dimUsage, MeasureGroup mg, RegularMeasureGroupDimension regMDdim)
{
string tableId = string.Empty;
DimensionUsage usage = new DimensionUsage("Regular", mg, regMDdim.CubeDimension, regMDdim.Dimension);//, cb.TableID, cb.ColumnID);
usage.Column1Name = "Granularity Attribute";
usage.Column2Name = "Dimension Column";
usage.Column3Name = "Measure Group Columns";
foreach (MeasureGroupAttribute mga in regMDdim.Attributes)
{
if (mga.Type == MeasureGroupAttributeType.Granularity)
{
tableId = string.Empty;
usage.Column1Value = mga.Attribute.Name;
System.Diagnostics.Trace.Write(",mga " + mga.CubeAttribute.Attribute.Name);
//foreach (DataItem di in mga.KeyColumns)
//{
//ColumnBinding cb = (ColumnBinding)di.Source;
// TODO - get the key columns for the attribute, not just its name
//System.Diagnostics.Trace.WriteLine(",di " + di.Source.ToString());
foreach (DataItem di2 in mga.Attribute.KeyColumns)
{
if (di2.Source is ColumnBinding)
{
tableId = ((ColumnBinding)di2.Source).TableID;
DataSourceView dsv = mga.Parent.Dimension.DataSourceView;
if (dsv.Schema.Tables.Contains(tableId))
{
DataTable oTable = dsv.Schema.Tables[dsv.Schema.Tables.IndexOf(tableId)];
if (oTable.ExtendedProperties.ContainsKey("FriendlyName"))
{
usage.Column2Value += oTable.ExtendedProperties["FriendlyName"] + ".";
}
}
else
{
usage.Column2Value += ((ColumnBinding)di2.Source).TableID + ".";
}
usage.Column2Value += ((ColumnBinding)di2.Source).ColumnID + "\n";
}
}
foreach (DataItem di3 in mga.KeyColumns)
{
if (di3.Source is ColumnBinding)
{
tableId = ((ColumnBinding)di3.Source).TableID;
DataSourceView dsv = mga.ParentCube.DataSourceView;
if (dsv.Schema.Tables.Contains(tableId))
{
DataTable oTable = dsv.Schema.Tables[dsv.Schema.Tables.IndexOf(tableId)];
if (oTable.ExtendedProperties.ContainsKey("FriendlyName"))
{
usage.Column3Value += oTable.ExtendedProperties["FriendlyName"] + ".";
}
}
else
{
usage.Column3Value += ((ColumnBinding)di3.Source).TableID + ".";
}
usage.Column3Value += ((ColumnBinding)di3.Source).ColumnID;
usage.Column3Value += " (" + di3.NullProcessing.ToString().Substring(0, 1) + ")";
usage.Column3Value += "\n";
}
}
//}
}
}
dimUsage.Add(usage);
}
/// <summary>
/// Returns the estimated size of this aggregation. Use this signature which takes in the MeasureGroup when the agg is not attached to a ParentMeasureGroup.
/// </summary>
/// <param name="agg"></param>
/// <param name="mg1"></param>
/// <returns></returns>
public static EstimatedAggSize GetEstimatedSize(Aggregation agg, MeasureGroup mg1)
{
double size = 0;
double minSize = 0;
bool bAggContainsAllGranularityAttributes = true;
AggregationAttribute aggAttr;
AggregationDimension aggDim;
double dblAggCardinality = 1;
long iNumSurrogateKeysInAgg = 0;
int iMeasureGroupDimensionsCount = 0;
long lngMaxAggDimensionCardinality = 0;
foreach (MeasureGroupDimension mgDim in mg1.Dimensions)
{
long iDimGranularityCardinality = 0;
double dblDimAggCardinality = 1;
bool bDimAggCardinalityFound = false;
if (!(mgDim is RegularMeasureGroupDimension)) continue; //m2m dimensions apparently aren't stored in the agg since they're calculated at runtime
iMeasureGroupDimensionsCount++; //don't count m2m dimensions
MeasureGroupAttribute granularity = null;
RegularMeasureGroupDimension regMgDim = (RegularMeasureGroupDimension)mgDim;
foreach (MeasureGroupAttribute mgDimAttr in regMgDim.Attributes)
{
if (mgDimAttr.Type == MeasureGroupAttributeType.Granularity)
{
iDimGranularityCardinality = mgDimAttr.Attribute.EstimatedCount;
granularity = mgDimAttr;
break;
}
}
aggDim = agg.Dimensions.Find(mgDim.CubeDimensionID);
if (aggDim == null || granularity == null || aggDim.Attributes.Find(granularity.AttributeID) == null)
bAggContainsAllGranularityAttributes = false;
if (aggDim != null)
{
foreach (CubeAttribute cubeAttr in mgDim.CubeDimension.Attributes)
{
aggAttr = aggDim.Attributes.Find(cubeAttr.AttributeID);
if (aggAttr != null)
{
if (!CanReachAttributeFromChildInAgg(aggAttr, mgDim.Dimension.KeyAttribute, false)) //redundant attributes don't increase the cardinality of the attribute
{
dblDimAggCardinality *= (cubeAttr.Attribute.EstimatedCount == 0 ? 1 : cubeAttr.Attribute.EstimatedCount);
}
bDimAggCardinalityFound = true;
iNumSurrogateKeysInAgg++; //apparently every key, even redundant keys, get stored in the agg
}
}
}
if (dblDimAggCardinality > iDimGranularityCardinality)
{
//shouldn't be more than granularity cardinality because auto-exists prevents that
dblDimAggCardinality = (iDimGranularityCardinality == 0 ? 1 : iDimGranularityCardinality);
}
if (bDimAggCardinalityFound)
{
dblAggCardinality *= dblDimAggCardinality;
if (lngMaxAggDimensionCardinality < dblAggCardinality) lngMaxAggDimensionCardinality = (long)dblDimAggCardinality;
}
}
if (mg1.EstimatedRows != 0 && dblAggCardinality != 0)
{
long iMeasureBytes = 0;
foreach (Microsoft.AnalysisServices.Measure m in mg1.Measures)
{
if (m.DataType == MeasureDataType.Inherited)
{
if (m.Source.DataSize > 0)
iMeasureBytes += m.Source.DataSize;
else if (m.Source.DataType == System.Data.OleDb.OleDbType.Integer)
iMeasureBytes += 4;
else if (m.Source.DataType == System.Data.OleDb.OleDbType.SmallInt)
iMeasureBytes += 2;
else if (m.Source.DataType == System.Data.OleDb.OleDbType.TinyInt)
iMeasureBytes += 1;
else
iMeasureBytes += 8;
}
else
{
if (m.DataType == MeasureDataType.Integer)
iMeasureBytes += 4;
else if (m.DataType == MeasureDataType.SmallInt)
iMeasureBytes += 2;
else if (m.DataType == MeasureDataType.TinyInt)
iMeasureBytes += 1;
else
iMeasureBytes += 8;
}
}
//the size of each row is 4 bytes for each surrogate key plus the size of measures
long lngFactTableRowSize = (iMeasureGroupDimensionsCount * 4 + iMeasureBytes);
long lngAggRowSize = (iNumSurrogateKeysInAgg * 4 + iMeasureBytes);
if (dblAggCardinality > mg1.EstimatedRows) //this is not possible in the data
{
dblAggCardinality = mg1.EstimatedRows;
}
//multiply the estimated rows by the size of each row
size = ((double)(dblAggCardinality * lngAggRowSize)) / ((double)(mg1.EstimatedRows * lngFactTableRowSize));
//purposefully don't prevent size from being over 1 because an agg can be larger than the fact table if it has more dimension attribute keys than the fact table
if (lngMaxAggDimensionCardinality > mg1.EstimatedRows) //this is not possible in the data
{
lngMaxAggDimensionCardinality = mg1.EstimatedRows;
}
//calculate the min size (best case scenario when there is lots of sparsity in fact table) so you can present a range to the user and give the user an idea of the uncertainty
minSize = ((double)(lngMaxAggDimensionCardinality * lngAggRowSize)) / ((double)(mg1.EstimatedRows * lngFactTableRowSize));
}
EstimatedAggSize ret = new EstimatedAggSize();
ret.minSize = minSize;
ret.size = size;
ret.bAggContainsAllGranularityAttributes = bAggContainsAllGranularityAttributes;
ret.agg = agg;
return ret;
}
private void FillCollections( MeasureGroup mg1)
{
if (mg1 == mgCurrent) return;
mgCurrent = mg1;
dimAttributes = new string[100, 300];
dimNames = new string[100];
dimIDs = new string[100];
int dimI = 0;
int attI = 0;
foreach (MeasureGroupDimension mgDim in mg1.Dimensions)
{
dimNames[dimI] = mgDim.CubeDimension.Name;
dimIDs[dimI] = mgDim.CubeDimensionID;
attI = 0;
foreach (CubeAttribute cubeDimAttr in mgDim.CubeDimension.Attributes)
{
dimAttributes[dimI, attI] = cubeDimAttr.AttributeID;
attI++;
}
dimI++;
}
}
/*
* Many Col1 - Intermediate Measure Group Name
*
*/
private static void getManyToManyMeasureGroupAttributeUsage(List<DimensionUsage> dimUsage, MeasureGroup mg, ManyToManyMeasureGroupDimension m2mMDdim)
{
DimensionUsage usage = new DimensionUsage("Many to Many", mg, m2mMDdim.CubeDimension, m2mMDdim.Dimension);
usage.Column1Name = "Intermediate Measure Group";
usage.Column1Value = m2mMDdim.MeasureGroup.Name;
dimUsage.Add(usage);
}
private void MarkMeasureGroupAsModified(TreeNode node, MeasureGroup mg)
{
if (node.Tag is MeasureGroup && ((MeasureGroup)node.Tag).ID == mg.ID)
{
if (!node.Text.EndsWith(MODIFIED_SUFFIX))
node.Text = node.Text + MODIFIED_SUFFIX;
return;
}
foreach (TreeNode child in node.Nodes)
{
MarkMeasureGroupAsModified(child, mg);
}
}
/*
* DataMining Col1 - Source Dimension Name
*
*/
private static void getDataMiningMeasureGroupAttributeUsage(List<DimensionUsage> dimUsage, MeasureGroup mg, DataMiningMeasureGroupDimension dmMDdim)
{
DimensionUsage usage = new DimensionUsage("Data Mining", mg, dmMDdim.CubeDimension, dmMDdim.Dimension);
usage.Column1Name = "Source Dimension";
usage.Column1Value = dmMDdim.Dimension.Name;
dimUsage.Add(usage);
}