public void replaceMdxBlock(string tag, string mdx_new)
{
Cube = Database.Cubes.FindByName("KPI");
Command cmd;
//Command cmd = findCommandByPattern("KPI", "<KCALC.XPR>");
MdxScript script = Cube.MdxScripts[0];
cmd = script.Commands[0];
string mdx = cmd.Text;
string tag_start = String.Format("//<{0}>", tag);
string tag_end = String.Format("//</{0}>", tag);
int kpi_from = mdx.IndexOf(tag_start) + tag_start.Length;
int kpi_to = mdx.IndexOf(tag_end) - 1;
mdx = mdx.Remove(kpi_from, kpi_to - kpi_from + 1);
mdx = mdx.Insert(kpi_from, mdx_new);
cmd.Text = mdx;
/*
* script.Commands.Remove(cmd); // At(i);
* //script.Update();
* cmd = new Command();
* cmd.Text = mdx;
* script.Commands.Add(cmd);
*/
script.Update();
Cube.Update();
//Cube.Update(UpdateOptions.ExpandFull);
}
public void getCommands(string cube_name)
{
Cube = Database.Cubes.FindByName(cube_name);
foreach (Microsoft.AnalysisServices.MdxScript mdx in Cube.MdxScripts)
{
foreach (Microsoft.AnalysisServices.Command cmd in mdx.Commands)
{
Console.WriteLine("* * * * * * * * *");
Console.WriteLine(cmd.Text);
}
}
}
//<KCALC.XPR>
public Microsoft.AnalysisServices.Command findCommandByPattern(string cube_name, string pattern)
{
Cube = Database.Cubes.FindByName(cube_name);
//Microsoft.AnalysisServices.Command cmd;
foreach (Microsoft.AnalysisServices.MdxScript mdx in Cube.MdxScripts)
{
foreach (Microsoft.AnalysisServices.Command cmd in mdx.Commands)
{
if (cmd.Text.Contains(pattern))
{
return(cmd);
}
}
}
return(null);
}
private static void RelationshipAddReferenceMeasureGroupDimension(AMO.Database tabularDatabase,
string currentMeasureGroupId,
RelationshipPair relationshipPair,
string relationshipId = null)
{
string foreignTableId = relationshipPair.ForeignKeyEnd.TableId;
string foreignColumnId = relationshipPair.ForeignKeyEnd.ColumnId;
string pkTableId = relationshipPair.PrimaryKeyEnd.TableId;
string pkColumnId = relationshipPair.PrimaryKeyEnd.ColumnId;
// Creating the ReferenceMeasureGroupDimension that defines the 'Activeness' of a relationship
using (AMO.Cube modelCube = tabularDatabase.Cubes[0])
using (AMO.MeasureGroup currentMG = modelCube.MeasureGroups[currentMeasureGroupId])
using (AMO.ReferenceMeasureGroupDimension newReferenceMGDim = new AMO.ReferenceMeasureGroupDimension())
{
newReferenceMGDim.CubeDimensionID = pkTableId;
newReferenceMGDim.IntermediateCubeDimensionID = foreignTableId;
newReferenceMGDim.IntermediateGranularityAttributeID = foreignColumnId;
// Replicating attributes (columns) from dimension
foreach (AMO.CubeAttribute PKAttribute in modelCube.Dimensions[pkTableId].Attributes)
{
using (AMO.MeasureGroupAttribute PKMGAttribute = newReferenceMGDim.Attributes.Add(PKAttribute.AttributeID))
using (AMO.DataItem dataItem = new AMO.DataItem(pkTableId, PKAttribute.AttributeID, PKAttribute.Attribute.KeyColumns[0].DataType))
using (AMO.ColumnBinding columnBinding = new AMO.ColumnBinding(pkTableId, PKAttribute.AttributeID))
{
PKMGAttribute.KeyColumns.Add(dataItem);
PKMGAttribute.KeyColumns[0].Source = columnBinding;
}
}
newReferenceMGDim.Attributes[pkColumnId].Type = AMO.MeasureGroupAttributeType.Granularity;
// If relationship is not null or empty then this is a direct relationship and
// has to have Materialization.Regular
if (!relationshipId.IsNullOrEmptyOrWhitespace())
{
newReferenceMGDim.Materialization = AMO.ReferenceDimensionMaterialization.Regular;
newReferenceMGDim.RelationshipID = relationshipId;
}
else
{
newReferenceMGDim.Materialization = AMO.ReferenceDimensionMaterialization.Indirect;
}
// Adding the ReferenceMeasureGroupDimension to the measure group
currentMG.Dimensions.Add(newReferenceMGDim);
}
}
private static void setActiveRelationship(AMO.Cube currentCube, string MVTableName, string MVColumnName, string PKTableName, string relationshipID)
{
AMO.MeasureGroup currentMG = currentCube.MeasureGroups[MVTableName];
if (!currentMG.Dimensions.Contains(PKTableName))
{
AMO.ReferenceMeasureGroupDimension NewReferenceMGDim = new AMO.ReferenceMeasureGroupDimension();
NewReferenceMGDim.CubeDimensionID = PKTableName;
NewReferenceMGDim.IntermediateCubeDimensionID = MVTableName;
NewReferenceMGDim.Materialization = AMO.ReferenceDimensionMaterialization.Regular;
foreach (AMO.CubeAttribute PKAttribute in currentCube.Dimensions[PKTableName].Attributes)
{
AMO.MeasureGroupAttribute PKMGAttribute = NewReferenceMGDim.Attributes.Add(PKAttribute.AttributeID);
System.Data.OleDb.OleDbType PKMGAttributeType = PKAttribute.Attribute.KeyColumns[0].DataType;
PKMGAttribute.KeyColumns.Add(new AMO.DataItem(PKTableName, PKAttribute.AttributeID, PKMGAttributeType));
PKMGAttribute.KeyColumns[0].Source = new AMO.ColumnBinding(PKTableName, PKAttribute.AttributeID);
}
currentMG.Dimensions.Add(NewReferenceMGDim);
}
AMO.ReferenceMeasureGroupDimension currentReferenceMGDim = (AMO.ReferenceMeasureGroupDimension)currentMG.Dimensions[PKTableName];
currentReferenceMGDim.RelationshipID = relationshipID;
currentReferenceMGDim.IntermediateGranularityAttributeID = MVColumnName;
}
public static void DeployAggDesigns(ProjectItem projItem, DTE2 ApplicationObject)
{
Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;
bool bFoundAggDesign = false;
foreach (MeasureGroup mg in oCube.MeasureGroups)
{
if (mg.AggregationDesigns.Count > 0)
{
bFoundAggDesign = true;
break;
}
}
if (!bFoundAggDesign)
{
MessageBox.Show("There are no aggregation designs defined in this cube yet.");
return;
}
if (MessageBox.Show("This command deploys just the aggregation designs in this cube. It does not change which aggregation design is assigned to each partition.\r\n\r\nYou should run a ProcessIndex command from Management Studio on this cube after aggregation designs have been deployed.\r\n\r\nDo you wish to continue?", "BIDS Helper - Deploy Aggregation Designs", MessageBoxButtons.YesNo) != DialogResult.Yes)
{
return;
}
try
{
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 1, 5);
string sPartitionsFileName = projItem.get_FileNames(1);
sPartitionsFileName = sPartitionsFileName.Substring(0, sPartitionsFileName.Length - 5) + ".partitions";
// Check if the file is read-only (and probably checked in to a source control system)
// before attempting to save. (issue: 10327 )
FileAttributes fa = System.IO.File.GetAttributes(sPartitionsFileName);
if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly)
{
//TODO - prompt before saving?
//Save the cube
projItem.Save("");
}
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 2, 5);
// extract deployment information
DeploymentSettings deploySet = new DeploymentSettings(projItem);
// use xlst to create xmla alter command
XslCompiledTransform xslt = new XslCompiledTransform();
XmlReader xsltRdr;
XmlReader xrdr;
// read xslt from embedded resource
xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployAggDesigns));
using ((xsltRdr))
{
// read content from .partitions file
xrdr = XmlReader.Create(sPartitionsFileName);
using (xrdr)
{
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 3, 5);
// Connect to Analysis Services
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(deploySet.TargetServer);
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 4, 5);
// execute the xmla
try
{
// Build up the Alter MdxScript command using XSLT against the .partitions file
XslCompiledTransform xslta = new XslCompiledTransform();
StringBuilder sb = new StringBuilder();
XmlWriterSettings xws = new XmlWriterSettings();
xws.OmitXmlDeclaration = true;
xws.ConformanceLevel = ConformanceLevel.Fragment;
XmlWriter xwrtr = XmlWriter.Create(sb, xws);
xslta.Load(xsltRdr);
XsltArgumentList xslarg = new XsltArgumentList();
Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
if (targetDB == null)
{
throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
}
xslarg.AddParam("TargetDatabase", "", targetDB.ID);
xslarg.AddParam("TargetCubeID", "", oCube.ID);
xslta.Transform(xrdr, xslarg, xwrtr);
Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
if (oServerCube == null)
{
throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
}
// update the agg designs
XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
StringBuilder sbErr = new StringBuilder();
for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
{
for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
{
if (!string.IsNullOrEmpty(xmlaRC[iRC].Messages[iMsg].Description))
{
sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
}
}
}
if (sbErr.Length > 0)
{
MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Aggregation Designs");
}
projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
}
catch (System.Exception ex)
{
if (MessageBox.Show("The following error occured while trying to deploy the aggregation designs\r\n"
+ ex.Message
+ "\r\n\r\nDo you want to see a stack trace?"
, "BIDSHelper - Deploy Aggregation Designs"
, MessageBoxButtons.YesNo
, MessageBoxIcon.Error
, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
MessageBox.Show(ex.StackTrace);
}
}
finally
{
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 5, 5);
svr.Disconnect();
}
}
}
}
finally
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(false, "Deploying Aggregation Designs", 5, 5);
}
}
//TODO: Add direct query support
/* Complete feature list, to be noted when complete support added
* - Database
* - Direct Query
* - Datasources (Done)
* - Tables (Done)
* - Translation of table (Done)
* - Attributes (Done)
* - Translation of Attribute (Done)
* - Hierarchies (Done)
* - Translation of Hierarchies (Done)
* - Levels (Done)
* - Translation of Levels (Done)
* - Measures (Done)
* - Translation of Measures (Done)
* - KPI's (Done)
* - Perspectives (Done)
* - Roles (Done)
* - Row Level Security (Done)
* - Members (Done)
* - Relationships (Done)
*/
/// <summary>
/// Given a 1200 or 1400 model, produces an equivelant 1103 model. Incompatible features are not added.
/// </summary>
/// <param name="TOMDatabase"></param>
/// <returns></returns>
public static AMO.Database ToAMODatabase(TOM.Database TOMDatabase)
{
/* The Database representing a Tabular model rarely has a 1 to 1 mapping between
* a TOM object and an AMO object.
*
* Some objects which DO have 1:1 mappings (at least, in a logical sense):
* - DataSources
* - Hierarchies
* - Levels
* - Perspectives
* - Partitions
* - Translations (however, they are structured differently)
* - Annotations (These can also accept XML nodes, not just strings)
*
* The following do NOT:
* - Tables
* - Columns
* - Measures
* - Relationships
* - Roles
*
* The following is, surprisingly, supported:
* - Tabular Actions
* - HideMemberIf
* - Display Folders
* - Custom Format strings
* - Translations
* - Similar to 1200, more than one column with the same source column
*
* Please note this does NOT produce a file able to be opened by Visual Studio.
* Unlike 1200 models, Visual Studio cannot open all valid 1103 models.
* It relies heavily on custom annotations, understood mainly through trial and error.
*
* In addition to these custom annotations, some features have no alternative (custom format strings), and
* are completely unsupported by both Visual Studio and BIDS.
*
* Display Folders, Tabular Actions, and Translations are all supported using BIDS.
*
* These drastically increase the database/file size, and as such are optionally added in a seperate function.
*/
TOM.Model TOMModel = TOMDatabase.Model;
//Create Database
AMO.Database AMODatabase = new AMO.Database(TOMDatabase.Name, TOMDatabase.Name);
//Initialise with default values.
AMODatabase.StorageEngineUsed = AMO.StorageEngineUsed.InMemory;
AMODatabase.CompatibilityLevel = SQL2012SP1;
//DataSource has 1:1 mapping with AMO object
#region DataSources
foreach (TOM.ProviderDataSource TOMDataSource in TOMModel.DataSources)
{
AMO.DataSource AMODataSource = new AMO.RelationalDataSource(TOMDataSource.Name, TOMDataSource.Name);
AMODataSource.Description = TOMDataSource.Description;
AMODataSource.ConnectionString = TOMDataSource.ConnectionString;
AMODataSource.ImpersonationInfo = new AMO.ImpersonationInfo();
switch (TOMDataSource.ImpersonationMode)
{
case TOM.ImpersonationMode.Default:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.Default;
break;
case TOM.ImpersonationMode.ImpersonateAccount:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateAccount;
break;
case TOM.ImpersonationMode.ImpersonateAnonymous:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateAnonymous;
break;
case TOM.ImpersonationMode.ImpersonateCurrentUser:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateCurrentUser;
break;
case TOM.ImpersonationMode.ImpersonateServiceAccount:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateServiceAccount;
break;
case TOM.ImpersonationMode.ImpersonateUnattendedAccount:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateUnattendedAccount;
break;
}
AMODataSource.ImpersonationInfo.Account = TOMDataSource.Account;
AMODataSource.ImpersonationInfo.Password = TOMDataSource.Password;
switch (TOMDataSource.Isolation)
{
case TOM.DatasourceIsolation.ReadCommitted:
AMODataSource.Isolation = AMO.DataSourceIsolation.ReadCommitted;
break;
case TOM.DatasourceIsolation.Snapshot:
AMODataSource.Isolation = AMO.DataSourceIsolation.Snapshot;
break;
}
//1 "tick" = 100 nanoseconds = 1*10^-7 seconds.
AMODataSource.Timeout = new TimeSpan(TOMDataSource.Timeout * 10000000);
AMODatabase.DataSources.Add(AMODataSource);
}
#endregion
/* The DSV is surprisingly simple.
* For each physical table (but NOT partition), a DataTable needs to be added to the DSV DataSet,
* with the same name as the table.
*
* Similarly, for each distinct source column, a DataColumn needs to be added to the corresponding DataTable
*/
#region DataSourceView
using (AMO.DataSourceView dsv = new AMO.DataSourceView(AMODatabase.DataSources[0].Name))
{
System.Data.DataSet Schema = new System.Data.DataSet(AMODatabase.DataSources[0].Name);
dsv.Schema = Schema;
dsv.DataSourceID = AMODatabase.DataSources[0].Name;
AMODatabase.DataSourceViews.Add(dsv);
}
#endregion
#region Create Cube
AMO.Cube Cube = new AMO.Cube(TOMModel.Name, TOMModel.Name);
Cube.Source = new AMO.DataSourceViewBinding(AMODatabase.DataSourceViews[0].ID);
Cube.StorageMode = AMO.StorageMode.InMemory;
//Create the MdxScript for holding DAX commands.
using (AMO.MdxScript Script = Cube.MdxScripts.Add(MDXScriptName, MDXScriptName))
{
//You MUST have a "default" MDX measure for some reason.
//We make sure to hide it - it serves no real purpose, besides its own existence
System.Text.StringBuilder InitialisationCommand = new System.Text.StringBuilder();
InitialisationCommand.AppendLine("CALCULATE;");
InitialisationCommand.AppendLine("CREATE MEMBER CURRENTCUBE.Measures.[_No measures defined] AS 1, VISIBLE = 0;");
InitialisationCommand.AppendLine("ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, Default_Member = [_No measures defined];");
Script.Commands.Add(new AMO.Command(InitialisationCommand.ToString()));
}
AMODatabase.Cubes.Add(Cube);
#endregion
#region Add Tables
foreach (TOM.Table TOMTable in TOMModel.Tables)
{
//Three "parts" to a Table:
//1. A System.Data.DataTable, with the same Name as the TOMTable
//2. A dimension with the same name as the TOMTable
//3. A measure group with the same name as the TOMTable,
System.Data.DataTable SchemaTable = new System.Data.DataTable(TOMTable.Name);
AMODatabase.DataSourceViews[0].Schema.Tables.Add(SchemaTable);
string RowNumberColumnName = string.Format(System.Globalization.CultureInfo.InvariantCulture, "RowNumber_{0}", System.Guid.NewGuid());
#region Add Table Dimension
try
{
using (AMO.Dimension Dimension = AMODatabase.Dimensions.Add(TOMTable.Name, TOMTable.Name))
{
Dimension.Source = new AMO.DataSourceViewBinding(AMODatabase.DataSourceViews[0].ID);
Dimension.StorageMode = AMO.DimensionStorageMode.InMemory;
Dimension.UnknownMember = AMO.UnknownMemberBehavior.AutomaticNull;
Dimension.UnknownMemberName = "Unknown";
using (Dimension.ErrorConfiguration = new AMO.ErrorConfiguration())
{
Dimension.ErrorConfiguration.KeyNotFound = AMO.ErrorOption.IgnoreError;
Dimension.ErrorConfiguration.KeyDuplicate = AMO.ErrorOption.ReportAndStop;
Dimension.ErrorConfiguration.NullKeyNotAllowed = AMO.ErrorOption.ReportAndStop;
}
Dimension.ProactiveCaching = new AMO.ProactiveCaching();
System.TimeSpan DefaultProactiveCachingTimeSpan = new System.TimeSpan(0, 0, -1);
Dimension.ProactiveCaching.SilenceInterval = DefaultProactiveCachingTimeSpan;
Dimension.ProactiveCaching.Latency = DefaultProactiveCachingTimeSpan;
Dimension.ProactiveCaching.SilenceOverrideInterval = DefaultProactiveCachingTimeSpan;
Dimension.ProactiveCaching.ForceRebuildInterval = DefaultProactiveCachingTimeSpan;
Dimension.ProactiveCaching.Source = new AMO.ProactiveCachingInheritedBinding();
Dimension.Description = TOMTable.Description;
// Define RowNumber
using (AMO.DimensionAttribute RowNumberDimAttribute = Dimension.Attributes.Add(RowNumberColumnName, RowNumberColumnName))
{
RowNumberDimAttribute.Type = AMO.AttributeType.RowNumber;
RowNumberDimAttribute.KeyUniquenessGuarantee = true;
RowNumberDimAttribute.Usage = AMO.AttributeUsage.Key;
RowNumberDimAttribute.KeyColumns.Add(new AMO.DataItem());
RowNumberDimAttribute.KeyColumns[0].DataType = System.Data.OleDb.OleDbType.Integer;
RowNumberDimAttribute.KeyColumns[0].DataSize = 4;
RowNumberDimAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Error;
RowNumberDimAttribute.KeyColumns[0].Source = new AMO.RowNumberBinding();
RowNumberDimAttribute.NameColumn = new AMO.DataItem();
RowNumberDimAttribute.NameColumn.DataType = System.Data.OleDb.OleDbType.WChar;
RowNumberDimAttribute.NameColumn.DataSize = 4;
RowNumberDimAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
RowNumberDimAttribute.NameColumn.Source = new AMO.RowNumberBinding();
RowNumberDimAttribute.OrderBy = AMO.OrderBy.Key;
RowNumberDimAttribute.AttributeHierarchyVisible = false;
}
// Add Translations
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMTable);
if (AMOTranslation != null)
{
Dimension.Translations.Add(AMOTranslation);
}
}
}
}
catch (Exception e)
{
throw new Exception(string.Format("The following error occurred creating the DImension for table {0}: {1}", TOMTable.Name, e.Message), e);
}
#endregion
#region Add Table MeasureGroup
try
{
using (AMO.MeasureGroup TableMeasureGroup = Cube.MeasureGroups.Add(TOMTable.Name, TOMTable.Name))
{
TableMeasureGroup.StorageMode = AMO.StorageMode.InMemory;
TableMeasureGroup.ProcessingMode = AMO.ProcessingMode.Regular;
// Add Default Measure
string DefaultMeasureID = string.Concat("_Count", TOMTable.Name);
using (AMO.Measure DefaultMeasure = TableMeasureGroup.Measures.Add(DefaultMeasureID, DefaultMeasureID))
using (AMO.RowBinding DefaultMeasureRowBinding = new AMO.RowBinding(TOMTable.Name))
using (AMO.DataItem DefaultMeasureSource = new AMO.DataItem(DefaultMeasureRowBinding))
{
DefaultMeasure.AggregateFunction = AMO.AggregationFunction.Count;
DefaultMeasure.DataType = AMO.MeasureDataType.BigInt;
DefaultMeasure.Visible = false;
DefaultMeasureSource.DataType = System.Data.OleDb.OleDbType.BigInt;
DefaultMeasure.Source = DefaultMeasureSource;
}
// Add Dimension to Measure Group
using (AMO.DegenerateMeasureGroupDimension DefaultMeasureGroupDimension = new AMO.DegenerateMeasureGroupDimension(TOMTable.Name))
using (AMO.MeasureGroupAttribute MeasureGroupAttribute = new AMO.MeasureGroupAttribute(RowNumberColumnName))
using (AMO.ColumnBinding RowNumberColumnBinding = new AMO.ColumnBinding(TOMTable.Name, RowNumberColumnName))
using (AMO.DataItem RowNumberKeyColumn = new AMO.DataItem(RowNumberColumnBinding))
{
DefaultMeasureGroupDimension.ShareDimensionStorage = AMO.StorageSharingMode.Shared;
DefaultMeasureGroupDimension.CubeDimensionID = TOMTable.Name;
MeasureGroupAttribute.Type = AMO.MeasureGroupAttributeType.Granularity;
RowNumberKeyColumn.DataType = System.Data.OleDb.OleDbType.Integer;
MeasureGroupAttribute.KeyColumns.Add(RowNumberKeyColumn);
DefaultMeasureGroupDimension.Attributes.Add(MeasureGroupAttribute);
TableMeasureGroup.Dimensions.Add(DefaultMeasureGroupDimension);
}
//Partitions have a 1:1 mapping
#region Partitions
foreach (TOM.Partition TOMPartition in TOMTable.Partitions)
{
using (AMO.Partition AMOPartition = new AMO.Partition(TOMPartition.Name, TOMPartition.Name))
{
AMOPartition.StorageMode = AMO.StorageMode.InMemory;
AMOPartition.ProcessingMode = AMO.ProcessingMode.Regular;
AMOPartition.Source = new AMO.QueryBinding(
((TOM.QueryPartitionSource)TOMPartition.Source).DataSource.Name,
((TOM.QueryPartitionSource)TOMPartition.Source).Query
);
AMOPartition.Type = AMO.PartitionType.Data;
TableMeasureGroup.Partitions.Add(AMOPartition);
}
}
#endregion
}
}
catch (Exception e)
{
throw new Exception(string.Format("The following error occurred creating the MeasureGroup for table {0}: {1}", TOMTable.Name, e.Message), e);
}
#endregion
//Add the dimension to the cube
Cube.Dimensions.Add(TOMTable.Name, TOMTable.Name, TOMTable.Name);
Cube.Dimensions[TOMTable.Name].Visible = !TOMTable.IsHidden;
/* No 1:1 mapping exists for columns. Each column consists of the following:
* - A DataColumn with the same name as the column,
* attached to the DataTable correlating to the Table, located in the DSV
* - An attribute with the same name as the column, added to the Dimension
* - An AttributeRelationship with the RowNumber column
*/
#region Add Columns
foreach (TOM.Column TOMColumn in TOMTable.Columns)
{
switch (TOMColumn.Type)
{
case TOM.ColumnType.Data:
//Add the DataColumn corresponding to the SourceColumn, if it does not already exist
TOM.DataColumn TOMDataColumn = (TOM.DataColumn)TOMColumn;
if (!SchemaTable.Columns.Contains(TOMDataColumn.SourceColumn))
{
SchemaTable.Columns.Add(new System.Data.DataColumn(((TOM.DataColumn)TOMColumn).SourceColumn));
}
System.Data.OleDb.OleDbType ColumnDataType = DataTypeHelper.ToOleDbType(TOMDataColumn.DataType);
AMO.DimensionAttribute NormalAttribute = AMODatabase.Dimensions[TOMTable.Name].Attributes.Add(TOMDataColumn.Name, TOMDataColumn.Name);
NormalAttribute.Usage = AMO.AttributeUsage.Regular;
NormalAttribute.KeyUniquenessGuarantee = false;
NormalAttribute.KeyColumns.Add(new AMO.DataItem(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName, ColumnDataType));
NormalAttribute.KeyColumns[0].Source = new AMO.ColumnBinding(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName);
NormalAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
NormalAttribute.NameColumn = new AMO.DataItem(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName, System.Data.OleDb.OleDbType.WChar);
NormalAttribute.NameColumn.Source = new AMO.ColumnBinding(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName);
NormalAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
NormalAttribute.OrderBy = AMO.OrderBy.Key;
AMO.AttributeRelationship NormalAttributeRelationship = AMODatabase.Dimensions[TOMTable.Name].Attributes[RowNumberColumnName].AttributeRelationships.Add(NormalAttribute.ID);
NormalAttribute.AttributeHierarchyVisible = !TOMDataColumn.IsHidden;
NormalAttribute.Description = TOMDataColumn.Description;
NormalAttribute.AttributeHierarchyDisplayFolder = TOMDataColumn.DisplayFolder;
//Add Translations to the CalculatedAttribute
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMColumn))
if (AMOTranslation != null)
{
NormalAttribute.Translations.Add(new AMO.AttributeTranslation {
Caption = AMOTranslation.Caption, Description = AMOTranslation.Description, DisplayFolder = AMOTranslation.DisplayFolder
});
}
}
NormalAttributeRelationship.Cardinality = AMO.Cardinality.Many;
NormalAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
break;
case TOM.ColumnType.Calculated:
TOM.CalculatedColumn TOMCalculatedColumn = (TOM.CalculatedColumn)TOMColumn;
System.Data.OleDb.OleDbType CalculatedColumnDataType = DataTypeHelper.ToOleDbType(TOMColumn.DataType);
//Add Attribute to the Dimension
AMO.Dimension dim = AMODatabase.Dimensions[TOMTable.Name];
AMO.DimensionAttribute CalculatedAttribute = dim.Attributes.Add(TOMCalculatedColumn.Name, TOMCalculatedColumn.Name);
CalculatedAttribute.Usage = AMO.AttributeUsage.Regular;
CalculatedAttribute.KeyUniquenessGuarantee = false;
CalculatedAttribute.KeyColumns.Add(new AMO.DataItem(TOMTable.Name, TOMCalculatedColumn.Name, CalculatedColumnDataType));
CalculatedAttribute.KeyColumns[0].Source = new AMO.ExpressionBinding(TOMCalculatedColumn.Expression);
CalculatedAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
CalculatedAttribute.NameColumn = new AMO.DataItem(TOMTable.Name, TOMCalculatedColumn.Name, System.Data.OleDb.OleDbType.WChar);
CalculatedAttribute.NameColumn.Source = new AMO.ExpressionBinding(TOMCalculatedColumn.Expression);
CalculatedAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
CalculatedAttribute.OrderBy = AMO.OrderBy.Key;
AMO.AttributeRelationship currentAttributeRelationship = dim.Attributes[RowNumberColumnName].AttributeRelationships.Add(CalculatedAttribute.ID);
CalculatedAttribute.AttributeHierarchyVisible = !TOMCalculatedColumn.IsHidden;
CalculatedAttribute.Description = TOMCalculatedColumn.Description;
CalculatedAttribute.AttributeHierarchyDisplayFolder = TOMCalculatedColumn.DisplayFolder;
//Add Translations to the CalculatedAttribute
//Loop through each culture, and add the translation associated with that culture.
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMColumn))
if (AMOTranslation != null)
{
CalculatedAttribute.Translations.Add(new AMO.AttributeTranslation {
Caption = AMOTranslation.Caption, Description = AMOTranslation.Description, DisplayFolder = AMOTranslation.DisplayFolder
});
}
}
currentAttributeRelationship.Cardinality = AMO.Cardinality.Many;
currentAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
//Add CalculatedColumn as attribute to the MeasureGroup
AMO.MeasureGroup mg = Cube.MeasureGroups[TOMTable.Name];
AMO.DegenerateMeasureGroupDimension currentMGDim = (AMO.DegenerateMeasureGroupDimension)mg.Dimensions[TOMTable.Name];
AMO.MeasureGroupAttribute mga = new AMO.MeasureGroupAttribute(TOMCalculatedColumn.Name);
mga.KeyColumns.Add(new AMO.DataItem(TOMTable.Name, TOMCalculatedColumn.Name, System.Data.OleDb.OleDbType.Empty));
mga.KeyColumns[0].Source = new AMO.ExpressionBinding(TOMCalculatedColumn.Expression);
currentMGDim.Attributes.Add(mga);
break;
default:
throw new System.NotImplementedException(string.Format("Cannot deploy Column of type {0}", TOMColumn.Type.ToString()));
}
}
#endregion
//Add sort by columns
foreach (TOM.Column TOMColumn in TOMTable.Columns)
{
if (TOMColumn.SortByColumn != null)
{
AMODatabase.Dimensions[TOMTable.Name].Attributes[TOMColumn.Name].OrderByAttributeID = TOMColumn.SortByColumn.Name;
}
}
#region Add Hierarchies
foreach (TOM.Hierarchy TOMHierarchy in TOMTable.Hierarchies)
{
//Create the Hierarchy, and add it
AMO.Hierarchy AMOHierarchy = AMODatabase.Dimensions[TOMTable.Name].Hierarchies.Add(TOMHierarchy.Name, TOMHierarchy.Name);
AMOHierarchy.Description = TOMHierarchy.Description;
AMOHierarchy.DisplayFolder = TOMHierarchy.DisplayFolder;
AMOHierarchy.AllMemberName = "All";
foreach (TOM.Level TOMLevel in TOMHierarchy.Levels)
{
AMO.Level AMOLevel = AMOHierarchy.Levels.Add(TOMLevel.Name);
AMOLevel.SourceAttribute = AMODatabase.Dimensions[TOMTable.Name].Attributes[TOMLevel.Column.Name];
AMOLevel.Description = TOMLevel.Description;
//Add Translations to the CalculatedAttribute
//Loop through each culture, and add the translation associated with that culture.
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMLevel))
if (AMOTranslation != null)
{
AMOLevel.Translations.Add(AMOTranslation);
}
}
}
}
#endregion
}
#endregion
#region Add Measures
using (AMO.MdxScript MdxScript = Cube.DefaultMdxScript)
{
//Create a "default" measure, required for the cube to function.
MdxScript.Commands.Add(new AMO.Command("CALCULATE;"
+ "CREATE MEMBER CURRENTCUBE.Measures.[_No measures defined] AS 1, VISIBLE = 0;"
+ "ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, Default_Member = [_No measures defined];"));
foreach (TOM.Table TOMTable in TOMModel.Tables)
{
foreach (TOM.Measure TOMMeasure in TOMTable.Measures)
{
//Create the Command, which contains the definition of the Measure
AMO.Command AMOCommand = new AMO.Command(string.Format("CREATE MEASURE '{0}'[{1}]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.Expression));
if (TOMMeasure.KPI != null)
{
#region Add KPI
//Start building the final command - we add to it as we go along
string FinalKPICreate = string.Format(
"CREATE KPI CURRENTCUBE.[{1}] AS Measures.[{1}], ASSOCIATED_MEASURE_GROUP = '{0}'",
TOMTable.Name,
TOMMeasure.Name
);
//Goal/Target
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TargetExpression))
{
AMOCommand.Text += Environment.NewLine + string.Format(string.Format("CREATE MEASURE '{0}'[_{1} Goal]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.KPI.TargetExpression));
FinalKPICreate += string.Format(", GOAL = Measures.[_{0} Goal]", TOMMeasure.Name.Replace("]", "]]"));
}
//Status
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusExpression))
{
AMOCommand.Text += Environment.NewLine + string.Format(string.Format("CREATE MEASURE '{0}'[_{1} Status]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.KPI.StatusExpression));
FinalKPICreate += string.Format(", STATUS = Measures.[_{0} Status]", TOMMeasure.Name.Replace("]", "]]"));
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusGraphic))
{
FinalKPICreate += string.Format(", STATUS_GRAPHIC = '{0}'", TOMMeasure.KPI.StatusGraphic);
}
}
//Trend
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TrendExpression))
{
AMOCommand.Text += Environment.NewLine + string.Format(string.Format("CREATE MEASURE '{0}'[_{1} Trend]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.KPI.TrendExpression));
FinalKPICreate += string.Format(", TREND = Measures.[_{0} Trend]", TOMMeasure.Name.Replace("]", "]]"));
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TrendGraphic))
{
FinalKPICreate += string.Format(", TREND_GRAPHIC = '{0}'", TOMMeasure.KPI.TrendGraphic);
}
}
AMOCommand.Text += Environment.NewLine + FinalKPICreate + ";";
//Create calculation properties, hiding the "fake" measures (if they exist)
//Target
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TargetExpression))
{
AMO.CalculationProperty TargetCalculationProperty = new AMO.CalculationProperty(string.Format("[_{0} Goal]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
TargetCalculationProperty.Description = TOMMeasure.KPI.TargetDescription;
TargetCalculationProperty.CalculationType = AMO.CalculationType.Member;
TargetCalculationProperty.Visible = false;
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TargetFormatString))
{
TargetCalculationProperty.FormatString = "'" + TOMMeasure.KPI.TargetFormatString + "'";
}
MdxScript.CalculationProperties.Add(TargetCalculationProperty);
}
//Status
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusExpression))
{
AMO.CalculationProperty StatusCalculationProperty = new AMO.CalculationProperty(string.Format("[_{0} Status]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
StatusCalculationProperty.Description = TOMMeasure.KPI.StatusDescription;
StatusCalculationProperty.CalculationType = AMO.CalculationType.Member;
StatusCalculationProperty.Visible = false;
MdxScript.CalculationProperties.Add(StatusCalculationProperty);
}
//Trend
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusExpression))
{
AMO.CalculationProperty TrendCalculationProperty = new AMO.CalculationProperty(string.Format("[_{0} Trend]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
TrendCalculationProperty.Description = TOMMeasure.KPI.TrendDescription;
TrendCalculationProperty.CalculationType = AMO.CalculationType.Member;
TrendCalculationProperty.Visible = false;
MdxScript.CalculationProperties.Add(TrendCalculationProperty);
}
//Create the KPI calculation property
AMO.CalculationProperty KPICalculationProperty = new AMO.CalculationProperty(string.Format("KPIs.[{0}]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
KPICalculationProperty.Description = TOMMeasure.KPI.Description;
KPICalculationProperty.CalculationType = AMO.CalculationType.Member;
MdxScript.CalculationProperties.Add(KPICalculationProperty);
#endregion
}
//Add the Command to the MdxScript
MdxScript.Commands.Add(AMOCommand);
//Create the Calculation Property, which contains the various properties of the Measure
AMO.CalculationProperty CalculationProperty = new AMO.CalculationProperty(string.Format("[{0}]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
CalculationProperty.Description = TOMMeasure.Description;
CalculationProperty.DisplayFolder = TOMMeasure.DisplayFolder;
CalculationProperty.CalculationType = AMO.CalculationType.Member;
CalculationProperty.Visible = !TOMMeasure.IsHidden;
if (!string.IsNullOrWhiteSpace(TOMMeasure.FormatString))
{
CalculationProperty.FormatString = "'" + TOMMeasure.FormatString + "'";
}
//Add Translations to the Calculation property
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMMeasure))
if (AMOTranslation != null)
{
CalculationProperty.Translations.Add(AMOTranslation);
}
}
//Finally, add the CalculationProperty to the MDX script
MdxScript.CalculationProperties.Add(CalculationProperty);
}
}
}
#endregion
#region Add Relationships
//Relationships are just TOO awful. They are placed in their own helper function as a result.
foreach (TOM.SingleColumnRelationship TOMRelationship in TOMModel.Relationships)
{
RelationshipHelper.CreateRelationship(TOMRelationship, AMODatabase);
}
#endregion
#region Add Perspectives
foreach (TOM.Perspective TOMPerspective in TOMModel.Perspectives)
{
AMO.Perspective AMOPerspective = new AMO.Perspective(TOMPerspective.Name);
foreach (TOM.PerspectiveTable TOMTable in TOMPerspective.PerspectiveTables)
{
//Add Perspective Dimension
AMO.PerspectiveDimension PerspectiveDimension = new AMO.PerspectiveDimension(TOMTable.Name);
//Perspective Columns
foreach (TOM.PerspectiveColumn TOMPerspectiveColumn in TOMTable.PerspectiveColumns)
{
PerspectiveDimension.Attributes.Add(TOMPerspectiveColumn.Name);
}
//Perspective Hierarchies
foreach (TOM.PerspectiveHierarchy TOMPerspectiveHierarchy in TOMTable.PerspectiveHierarchies)
{
PerspectiveDimension.Hierarchies.Add(TOMPerspectiveHierarchy.Name);
}
//Add Perspective MeasureGroup
AMO.PerspectiveMeasureGroup PerspectiveMeasureGroup = new AMO.PerspectiveMeasureGroup(TOMTable.Name);
//Perspective Measures
//In this case, ']' is NOT "double quoted", unlike the calculation references, s
foreach (TOM.PerspectiveMeasure TOMPerspectiveMeasure in TOMTable.PerspectiveMeasures)
{
AMOPerspective.Calculations.Add('[' + TOMPerspectiveMeasure.Name + ']');
}
//VS does not add KPI's to Perspectives, so I have no idea how to do this...
//TODO: Add KPIs to perspectives
}
AMODatabase.Cubes[0].Perspectives.Add(AMOPerspective);
}
#endregion
#region Roles
foreach (TOM.ModelRole TOMRole in TOMDatabase.Model.Roles)
{
//Ceate database role
AMO.Role DatabaseRole = AMODatabase.Roles.Add(TOMRole.Name);
DatabaseRole.Description = TOMRole.Description;
//Add Members to Database role
foreach (TOM.ModelRoleMember TOMMember in TOMRole.Members)
{
DatabaseRole.Members.Add(new AMO.RoleMember
{
Name = TOMMember.MemberName,
Sid = TOMMember.MemberID
});
}
//Add DatabasePermission
AMO.DatabasePermission DatabasePermission = AMODatabase.DatabasePermissions.Add(TOMRole.Name, TOMRole.Name, TOMRole.Name);
//Add CubePermission
AMO.CubePermission CubePermission = AMODatabase.Cubes[0].CubePermissions.Add(TOMRole.Name, TOMRole.Name, TOMRole.Name);
switch (TOMRole.ModelPermission)
{
case TOM.ModelPermission.Administrator:
//Add ReadDefinition, Read, and Administer to DatabasePermission
DatabasePermission.ReadDefinition = AMO.ReadDefinitionAccess.Allowed;
DatabasePermission.Read = AMO.ReadAccess.Allowed;
DatabasePermission.Administer = true;
//Nothing extra to add to CubePermission, that is not added after this
// switch statement
break;
case TOM.ModelPermission.None:
//Add no permissions to DatabasePermission.
break;
case TOM.ModelPermission.Read:
//Add only Read access to Database Permission
DatabasePermission.Read = AMO.ReadAccess.Allowed;
//CubePermission
CubePermission.Read = AMO.ReadAccess.Allowed;
break;
case TOM.ModelPermission.ReadRefresh:
DatabasePermission.Read = AMO.ReadAccess.Allowed;
DatabasePermission.Process = true;
//CubePermission
CubePermission.Read = AMO.ReadAccess.Allowed;
CubePermission.Process = true;
break;
case TOM.ModelPermission.Refresh:
DatabasePermission.Process = true;
//CubePermission
CubePermission.Process = true;
break;
}
CubePermission.ReadSourceData = AMO.ReadSourceDataAccess.None;
//Finally, add the row filters
foreach (TOM.TablePermission TablePermission in TOMRole.TablePermissions)
{
AMO.DimensionPermission DimensionPermission = new AMO.DimensionPermission(TOMRole.Name, TOMRole.Name, TOMRole.Name);
DimensionPermission.AllowedRowsExpression = TablePermission.FilterExpression;
AMODatabase.Dimensions.GetByName(TablePermission.Table.Name).DimensionPermissions.Add(DimensionPermission);
}
}
#endregion
return(AMODatabase);
}
public static void DeployScript(ProjectItem projItem, DTE2 ApplicationObject)
{
Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;
try
{
//validate the script because deploying an invalid script makes cube unusable
Microsoft.AnalysisServices.Design.Scripts script = new Microsoft.AnalysisServices.Design.Scripts(oCube);
}
catch (Microsoft.AnalysisServices.Design.ScriptParsingFailed ex)
{
string throwaway = ex.Message;
MessageBox.Show("MDX Script in " + oCube.Name + " is not valid.", "Problem Deploying MDX Script");
return;
}
if (oCube.MdxScripts.Count == 0)
{
MessageBox.Show("There is no MDX script defined in this cube yet.");
return;
}
try
{
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 1, 5);
// Check if the file is read-only (and probably checked in to a source control system)
// before attempting to save. (issue: 10327 )
FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1));
if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly)
{
//TODO - can I check and maybe prompt before saving?
//Save the cube
projItem.Save("");
}
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 2, 5);
// extract deployment information
DeploymentSettings deploySet = new DeploymentSettings(projItem);
// use xlst to create xmla alter command
XslCompiledTransform xslt = new XslCompiledTransform();
XmlReader xsltRdr;
XmlReader xrdr;
// read xslt from embedded resource
xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployMdxScript));
using ((xsltRdr))
{
// read content from .cube file
xrdr = XmlReader.Create(projItem.get_FileNames(1));
using (xrdr)
{
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 3, 5);
// Connect to Analysis Services
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(deploySet.TargetServer);
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 4, 5);
// execute the xmla
try
{
Microsoft.AnalysisServices.Scripter scr = new Microsoft.AnalysisServices.Scripter();
// Build up the Alter MdxScript command using XSLT against the .cube file
XslCompiledTransform xslta = new XslCompiledTransform();
StringBuilder sb = new StringBuilder();
XmlWriterSettings xws = new XmlWriterSettings();
xws.OmitXmlDeclaration = true;
xws.ConformanceLevel = ConformanceLevel.Fragment;
XmlWriter xwrtr = XmlWriter.Create(sb, xws);
xslta.Load(xsltRdr);
XsltArgumentList xslarg = new XsltArgumentList();
Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
if (targetDB == null)
{
throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
}
string targetDatabaseID = targetDB.ID;
xslarg.AddParam("TargetDatabase", "", targetDatabaseID);
xslta.Transform(xrdr, xslarg, xwrtr);
// Extract the current script from the server and keep a temporary backup copy of it
StringBuilder sbBackup = new StringBuilder();
XmlWriterSettings xwSet = new XmlWriterSettings();
xwSet.ConformanceLevel = ConformanceLevel.Fragment;
xwSet.OmitXmlDeclaration = true;
xwSet.Indent = true;
XmlWriter xwScript = XmlWriter.Create(sbBackup, xwSet);
Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
if (oServerCube == null)
{
throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
}
else if (oServerCube.State == AnalysisState.Unprocessed)
{
throw new System.Exception(string.Format("The {0} cube is not processed the {1} server.", oCube.Name, deploySet.TargetServer));
}
if (oServerCube.MdxScripts.Count == 0)
{
scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { oServerCube }, xwScript, true);
}
else
{
MdxScript mdxScr = oServerCube.MdxScripts[0];
scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { mdxScr }, xwScript, true);
}
xwScript.Close();
// update the MDX Script
XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
if (xmlaRC.Count == 1 && xmlaRC[0].Messages.Count == 0)
{
// all OK - 1 result - no messages
}
else
{
StringBuilder sbErr = new StringBuilder();
for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
{
for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
{
sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
}
}
MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy MDX Script");
}
// Test the MDX Script
AdomdConnection cn = new AdomdConnection("Data Source=" + deploySet.TargetServer + ";Initial Catalog=" + deploySet.TargetDatabase);
cn.Open();
AdomdCommand cmd = cn.CreateCommand();
string qry = "SELECT {} ON 0 FROM [" + oCube.Name + "];";
cmd.CommandText = qry;
try
{
// test that we can query the cube without errors
cmd.Execute();
// Building the project means that the .asdatabase file gets re-built so that
// we do not break the Deployment Wizard.
// --
// This line is included in this try block so that it is only executed if we can
// successfully query the cube without errors.
projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
}
catch (System.Exception ex)
{
// undo the deployment if we caught an exception during the deployment
svr.Execute(sbBackup.ToString());
MessageBox.Show(ex.Message);
}
finally
{
cmd.Dispose();
cn.Close();
cn.Dispose();
}
}
catch (System.Exception ex)
{
if (MessageBox.Show("The following error occured while trying to deploy the MDX Script\r\n"
+ ex.Message
+ "\r\n\r\nDo you want to see a stack trace?"
, "BIDSHelper - Deploy MDX Script"
, MessageBoxButtons.YesNo
, MessageBoxIcon.Error
, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
MessageBox.Show(ex.StackTrace);
}
}
finally
{
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 5, 5);
// report any results back (status bar?)
svr.Disconnect();
svr.Dispose();
}
}
}
}
finally
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(false, "Deploying MdxScript", 5, 5);
}
}
public static void DeployPerspectives(ProjectItem projItem, DTE2 ApplicationObject)
{
Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;
if (oCube.Perspectives.Count == 0)
{
MessageBox.Show("There are no perspectives defined in this cube yet.");
return;
}
try
{
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 1, 5);
FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1));
if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly)
{
//Save the cube
projItem.Save("");
}
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 2, 5);
// extract deployment information
DeploymentSettings deploySet = new DeploymentSettings(projItem);
// use xlst to create xmla alter command
XslCompiledTransform xslt = new XslCompiledTransform();
XmlReader xsltRdr;
XmlReader xrdr;
// read xslt from embedded resource
xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployPerspectives));
using ((xsltRdr))
{
// read content from .cube file
xrdr = XmlReader.Create(projItem.get_FileNames(1));
using (xrdr)
{
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 3, 5);
// Connect to Analysis Services
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(deploySet.TargetServer);
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 4, 5);
// execute the xmla
try
{
// Build up the Alter perspectives command using XSLT against the .cube file
XslCompiledTransform xslta = new XslCompiledTransform();
StringBuilder sb = new StringBuilder();
XmlWriterSettings xws = new XmlWriterSettings();
xws.OmitXmlDeclaration = true;
xws.ConformanceLevel = ConformanceLevel.Fragment;
XmlWriter xwrtr = XmlWriter.Create(sb, xws);
xslta.Load(xsltRdr);
XsltArgumentList xslarg = new XsltArgumentList();
Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
if (targetDB == null)
{
throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
}
string targetDatabaseID = targetDB.ID;
xslarg.AddParam("TargetDatabase", "", targetDatabaseID);
xslta.Transform(xrdr, xslarg, xwrtr);
Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
if (oServerCube == null)
{
throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
}
//drop any perspectives which don't exist
svr.CaptureXml = true;
for (int i = 0; i < oServerCube.Perspectives.Count; i++)
{
Perspective p = oServerCube.Perspectives[i];
if (!oCube.Perspectives.Contains(p.ID))
{
p.Drop();
i--;
}
}
svr.CaptureXml = false;
try
{
if (svr.CaptureLog.Count > 0)
{
svr.ExecuteCaptureLog(true, false);
}
}
catch (System.Exception ex)
{
throw new System.Exception("Error dropping perspective that were deleted in the source code. " + ex.Message);
}
// update the perspectives
XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
StringBuilder sbErr = new StringBuilder();
for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
{
for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
{
sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
}
}
if (sbErr.Length > 0)
{
MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Perspectives");
}
try
{
// Building the project means that the .asdatabase file gets re-built so that
// we do not break the Deployment Wizard.
projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
catch (System.Exception ex)
{
if (MessageBox.Show("The following error occured while trying to deploy the perspectives\r\n"
+ ex.Message
+ "\r\n\r\nDo you want to see a stack trace?"
, "BIDS Helper - Deploy Perspectives"
, MessageBoxButtons.YesNo
, MessageBoxIcon.Error
, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
MessageBox.Show(ex.StackTrace);
}
}
finally
{
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 5, 5);
// report any results back (status bar?)
svr.Disconnect();
}
}
}
}
finally
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(false, "Deploying perspectives", 5, 5);
}
}
//this method is where any future code changes will need to be made
//the decisions about what extra properties are show are configured here
void ConfigureProjectItemExtraProperties(ProjectItem pi)
{
if (pi == null)
{
return;
}
if (pi.Object is Dimension)
{
Microsoft.AnalysisServices.Dimension dim = (Microsoft.AnalysisServices.Dimension)pi.Object;
System.ComponentModel.TypeDescriptor.Refresh(dim);
SetAttribute(typeof(Dimension), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(dim, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Dimension), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(dim, "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Hierarchy), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(typeof(Hierarchy), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Hierarchy), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(DimensionAttribute), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(typeof(DimensionAttribute), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(DimensionAttribute), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Translation), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(typeof(Translation), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
}
else if (pi.Object is Cube)
{
Microsoft.AnalysisServices.Cube cube = (Microsoft.AnalysisServices.Cube)pi.Object;
System.ComponentModel.TypeDescriptor.Refresh(cube);
SetAttribute(typeof(Cube), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(cube, "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(cube, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Cube), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(MeasureGroup), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(typeof(MeasureGroup), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(MeasureGroup), "Annotations", new System.ComponentModel.ReadOnlyAttribute(false), true);
SetAttribute(typeof(MeasureGroup), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Measure), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(typeof(Measure), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Measure), "Annotations", new System.ComponentModel.ReadOnlyAttribute(false), true);
SetAttribute(typeof(Measure), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
foreach (MeasureGroup mg in cube.MeasureGroups)
{
if (mg.IsLinked)
{
SetAttribute(mg, "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(mg, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(mg, "Description", new System.ComponentModel.ReadOnlyAttribute(!bInEffect), true);
SetAttribute(mg, "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
foreach (Measure m in mg.Measures)
{
SetAttribute(m, "Description", new System.ComponentModel.ReadOnlyAttribute(!bInEffect), true);
SetAttribute(m, "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(m, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(m, "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(m, "FormatString", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
}
}
foreach (Partition p in mg.Partitions)
{
SetAttribute(p, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(p, "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
}
}
SetAttribute(typeof(Perspective), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(typeof(Perspective), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Perspective), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Translation), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
SetAttribute(typeof(Translation), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
SetAttribute(typeof(Translation), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
}
}
public TabularActionsEditorForm(Microsoft.AnalysisServices.Cube cube, Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn)
{
InitializeComponent();
this.MinimumSize = this.Size;
this.Icon = BIDSHelper.Resources.Common.BIDSHelper;
this.cube = cube;
this.conn = conn;
arrEnabledControls = new Control[] { this.btnDelete, this.btnAdd, this.cmbAction, this.okButton, this.cancelButton, this.linkHelp };
_listDrillthroughColumns = new List<TabularActionsEditorPlugin.DrillthroughColumn>();
this.drillthroughColumnBindingSource.DataSource = _listDrillthroughColumns;
_listReportParameters = new List<ReportParameter>();
this.reportParameterBindingSource.DataSource = _listReportParameters;
List<string> list = new List<string>();
List<string> listAttributes = new List<string>();
list.Add(string.Empty);
listAttributes.Add(string.Empty);
foreach (Microsoft.AnalysisServices.CubeDimension cd in cube.Dimensions)
{
list.Add(cd.Name);
foreach (DimensionAttribute a in cd.Dimension.Attributes)
{
listAttributes.Add(a.Name);
}
}
DrillthroughDataGridCubeDimension.Items.AddRange(list.ToArray());
DrillthroughDataGridAttribute.Items.AddRange(listAttributes.ToArray());
long lngPerspectiveActionsCount = 0;
listPerspectives.Items.Clear();
foreach (Perspective p in cube.Perspectives)
{
listPerspectives.Items.Add(p.Name);
lngPerspectiveActionsCount += p.Actions.Count;
}
this.cmbActionType.Items.AddRange(Enum.GetNames(typeof(ActionType)));
this.cmbTargetType.Items.AddRange(Enum.GetNames(typeof(ActionTargetType)));
this.cmbTargetType.Items.Remove(ActionTargetType.Set.ToString());
this.cmbInvocation.Items.AddRange(Enum.GetNames(typeof(ActionInvocation)));
TabularActionsAnnotation annotation = BIDSHelper.TabularActionsEditorPlugin.GetAnnotation(cube);
bool bContainsPerspectiveListAnnotation = false;
_listActionClones = new List<Microsoft.AnalysisServices.Action>();
foreach (Microsoft.AnalysisServices.Action action in cube.Actions)
{
TabularAction actionAnnotation = annotation.Find(action.ID);
if (actionAnnotation == null) actionAnnotation = new TabularAction();
if (!string.IsNullOrEmpty(actionAnnotation.OriginalTarget)
&& !actionAnnotation.IsMasterClone)
{
continue;
}
Microsoft.AnalysisServices.Action clone = action.Clone();
_listActionClones.Add(clone);
if (!string.IsNullOrEmpty(actionAnnotation.OriginalTarget))
{
clone.Target = actionAnnotation.OriginalTarget;
}
List<string> lPerspectives = new List<string>();
foreach (Perspective perspective in cube.Perspectives)
{
if (perspective.Actions.Contains(action.ID))
{
lPerspectives.Add(perspective.Name);
}
}
if (!_dictActionPerspectives.ContainsKey(action.ID))
_dictActionPerspectives.Add(action.ID, lPerspectives.ToArray());
else
_dictActionPerspectives[action.ID] = lPerspectives.ToArray();
//see if this action is assigned to perspectives
if (actionAnnotation.Perspectives != null && actionAnnotation.Perspectives.Length > 0)
{
bContainsPerspectiveListAnnotation = true;
}
}
if (bContainsPerspectiveListAnnotation && lngPerspectiveActionsCount == 0 && cube.Perspectives.Count > 0)
{
//we have a backup of the perspectives list, and no actions are assigned to perspectives currently
if (MessageBox.Show("No actions are currently included in any perspectives, but BIDS Helper did retain a backup of the perspective assignments from the last actions editing session. Changes made to perspectives may have caused action assignments to be lost. Restoring action perspective assignments may be possible except when a perspective has been renamed.\r\n\r\nWould you like BIDS Helper to attempt restore the action perspective assignments now?", "BIDS Helper Tabular Actions Editor", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
{
foreach (Microsoft.AnalysisServices.Action action in _listActionClones)
{
TabularAction actionAnnotation = annotation.Find(action.ID);
if (actionAnnotation == null) actionAnnotation = new TabularAction();
if (actionAnnotation.Perspectives != null && actionAnnotation.Perspectives.Length > 0)
{
List<string> lPerspectives = new List<string>();
foreach (string sPerspectiveID in actionAnnotation.Perspectives)
{
if (cube.Perspectives.Contains(sPerspectiveID))
{
lPerspectives.Add(cube.Perspectives[sPerspectiveID].Name);
}
}
_dictActionPerspectives[action.ID] = lPerspectives.ToArray();
}
}
}
}
cmbAction.Items.AddRange(_listActionClones.ToArray());
cmbAction.DisplayMember = "Name";
cmbAction.ValueMember = "ID";
cmbAction.ResumeLayout();
if (_listActionClones.Count > 0)
cmbAction.SelectedIndex = 0;
else
DisableControls(true, arrEnabledControls);
}
public static void Main(string[] args)
{
string varSRV = "SRV-OLAP";
string varDB = "dw_olap";
string varCube = "Рух товарів";
for (int i = 0; i < args.Length; i++)
{
if (args[i].ToUpper().StartsWith("/SERVER:"))
{
varSRV = args[i].Substring(8);
}
else if (args[i].ToUpper().StartsWith("/DB:"))
{
varDB = args[i].Substring(4);
}
else if (args[i].ToUpper().StartsWith("/CUBE:"))
{
varCube = args[i].Substring(6);
}
}
string varSeparator = "\t";
Proc p = new Proc();
p.CreateLog("d:\\Cube_" + varCube + ".txt");
Microsoft.AnalysisServices.Server s = new Microsoft.AnalysisServices.Server();
s.Connect(@"Data Source=" + varSRV + ";Provider=msolap;Initial Catalog=" + varDB);
Microsoft.AnalysisServices.Database d = s.Databases.FindByName(varDB);
Microsoft.AnalysisServices.Cube c = d.Cubes.FindByName(varCube);
p.Log("База:" + varDB + " Куб:" + varCube);
p.Log("Розмірності");
foreach (Microsoft.AnalysisServices.CubeDimension dim in c.Dimensions)
{
p.Log(dim.Name + varSeparator + dim.DimensionID + varSeparator + dim.Description);
foreach (Microsoft.AnalysisServices.CubeAttribute attr in dim.Attributes)
{
p.Log(varSeparator + attr.Attribute + varSeparator + attr.AttributeID + "\t" + attr.Attribute.Description);
}
}
p.Log("Групи мір");
foreach (Microsoft.AnalysisServices.MeasureGroup mg in c.MeasureGroups)
{
p.Log(mg.Name + varSeparator + mg.ID + varSeparator + mg.Description);
foreach (Microsoft.AnalysisServices.Measure m in mg.Measures)
{
p.Log(varSeparator + m.ID + varSeparator + m.Name + varSeparator + m.Description);
}
}
p.Log("Калькульовані міри");
Microsoft.AnalysisServices.AdomdClient.AdomdConnection cn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Data Source=" + varSRV + ";Provider=msolap;Initial Catalog=" + varDB);
cn.Open();
/*foreach ( Microsoft.AnalysisServices.AdomdClient.CubeDef tt in cn.Cubes)
* p.Log(tt.Name+varSeparator+tt.Caption );*/
try
{
foreach (Microsoft.AnalysisServices.AdomdClient.Measure m in cn.Cubes[varCube].Measures)
{
if (string.IsNullOrEmpty(m.Expression) == false)
{
p.Log(m.UniqueName + varSeparator); //+m.Expression +varSeparator+ m.Description );
}
}
//Console.WriteLine("{0}: {1}",m.UniqueName,m.Expression );
}
catch { }
finally { cn.Close(); };
//Console.ReadLine();
p.CloseLog();
}
public static void RelationshipAlterActive(AMO.Database tabularDatabase,
string pkTableName,
string pkColumnName,
string foreignTableName,
string foreignColumnName,
bool active,
bool updateInstance = true)
{
// Terminology note:
// - the Foreing side of the relationship is named the From end in AMO
// - the PK side of the relationship in named the To end in AMO
//
// Relationships flow FROM the foreign side of the relationship
// TO the primary key side of the relationship in AMO
// ==> [Inactive] Relationship information is stored in the Foreign Dimension object in a relationship object
// ==> [Active] Relationship information is stored in the MeasureGroup object of the table in a ReferenceMeasureGroupDimension object
//
// Major steps in adding a relationship
//
// - Validate required input arguments and other initial preparations
// - If Activating:
// - Verify relationship exist before Activating
// - If relationship doesn't exist throw an error
// - Verify no multipath or alternate path will be created by activating this relationship
// - If ReferenceMeasureGroupDimension, for the PK table, doesn't exist add it to the Dimensions collection in the MeasureGroup
// - replicate attributes (columns) from Dimension
// - If ReferenceMeasureGroupDimension exists, update relationshipID and IntermediateGranularity
// - To the Dimensions collection in the MeasureGroup
// - Add all other intermediate relationships from 'Ancestors' paths
// - Add all other intermediate relationships to the 'Descendants' paths --> Update MeasureGroups in the 'Descendants' paths
//
//
// T1 T2 T5 T6 T7
// ---- ----- ----- ----- -----
// |a1| <-- |a2 | |a5 | |a6 | |a7 |
// | | |a2i| <-- |a5i| /| | | |a7i|
// | | | | | | / ------| |a6i| <-- | |
// ---- ----- | | / | | | -----
// | | \ Rel56 | | |
// T3 T4 | | \ ------| | | T8
// ---- ----- | | \| | | -----
// |a3| <-- |a4 | | | | | <-- |a8 |
// | | |a4i| <-- |a5j| | | |a8i|
// | | | | | | | | | |
// ---- ----- ----- ----- -----
//
// \_________ ____________/ \________ ________/
// \/ \/
// Ancestors Descendants
//
//
// When adding Rel56, as active, to the model, on top of everything that already exists, you have to:
//
// --> Add a ReferenceMeasureGroupDimension or updating existing one: (T5, T6.a6i) will include: Materialization:=Regular, RelationshipId:=<relationship id>
//
// --> After adding (T5, T6.a6i) to T6, you have to:
//
// Add Add Add
// to T6 to T7 to T8
// ...... ...... ......
// (T5, T6.a6i) (T5, T6.a6i)
// (T2, T5.a5i) (T2, T5.a5i) (T2, T5.a5i)
// (T1, T2.a2i) (T1, T2.a2i) (T1, T2.a2i)
// (T4, T5.a5j) (T4, T5.a5j) (T4, T5.a5j)
// (T3, T4.a4i) (T3, T4.a4i) (T3, T4.a4i)
//
// --> as reference dimensions to the MeasureGroup
// --> because the relationships between T6<--T7 and T6<--T8 already exist, the materialization and relationship
// definitions were added at the time the relationship were created ==> no need to do anything like
// on T6.
//
// - If De-Activating
// - Verify relationship exist before De-Activating
// - If relationship doesn't exist throw an error
// - Remove All ReferenceMeasureGroupDimension, for the PK table and Down Below tables, from MeasureGroup
//
// Note: In AMO, strings as indexers refer to the ID of the object, not the name
//
// Note: Only one DataSourceView is used in Tabular Models
// ==> tabularDatabase.DataSourceViews[0] represents the DSV of the model
//
// Note: Only one Cube is used in Tabular Models
// ==> tabularDatabase.Cubes[0] represents the cube in the model
//
//
// Note: Microsoft design tools use the following pattern to keep track of the
// datasource matching elements:
// DataSourceView->TableName <---> Dimension.ID, MeasureGroup.ID
// DataSourceView->ColumnName <---> Dimension->ColumnID, MeasureGroup.DegeneratedDimension->CoumnID
// So far, this sample follows the same pattern.
//
// WARNING: Breaking the above pattern when creating your
// own AMO to Tabular functions might lead to
// unpredictable behavior when using Microsoft
// Design tools in your models.
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (pkTableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("pkTableName");
}
if (pkColumnName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("pkColumnName");
}
if (foreignTableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("foreignTableName");
}
if (foreignColumnName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("foreignColumnName");
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
pkTableName = pkTableName.Trim();
pkColumnName = pkColumnName.Trim();
foreignTableName = foreignTableName.Trim();
foreignColumnName = foreignColumnName.Trim();
// - Obtain Id's
string pkTableId = tabularDatabase.Dimensions.GetByName(pkTableName).ID;
string pkColumnId = tabularDatabase.Dimensions[pkTableId].Attributes.GetByName(pkColumnName).ID;
string foreignTableId = tabularDatabase.Dimensions.GetByName(foreignTableName).ID;
string foreignColumnId = tabularDatabase.Dimensions[foreignTableId].Attributes.GetByName(foreignColumnName).ID;
#endregion
RelationshipGraph currentRelationshipsGraph;
// Branch on 'active'
// - true ==> set relationship to Active
// - false ==> set relationship to Inactive
switch (active)
{
case true:
#region set relationship to Active
// Verify relationship existence
// --> Throw an error if relationship doesn't exist
// - Define placeholder for the relationship id; it's needed later
string relationshipId = RelationshipTryGetRelationshipId(tabularDatabase, pkTableName, pkColumnName, foreignTableName, foreignColumnName);
if (relationshipId.IsNullOrEmptyOrWhitespace())
{
throw new InvalidOperationException(Resources.RelationshipDoesNotExistInvalidOperationException);
}
// Verify that activating this relationship does not violate the rule of no alternate paths when it will be created
// Build a graph of the actual state of relationships
currentRelationshipsGraph = new RelationshipGraph(tabularDatabase);
if (currentRelationshipsGraph.RelationshipAlternatePathExists(pkTableId, foreignTableId))
{
throw new InvalidOperationException(Resources.RelationshipViolatesAlternatePathRuleInvalidOperationException);
}
// The 'activeness' of a relationship is defined by the existence of a ReferenceMeasureGroupDimension
// that include Materialization as Regular
using (AMO.Cube modelCube = tabularDatabase.Cubes[0])
using (AMO.MeasureGroup foreignTableMG = modelCube.MeasureGroups[foreignTableId])
{
if (!foreignTableMG.Dimensions.Contains(pkTableId))
{
// Creating the ReferenceMeasureGroupDimension that defines the 'Activeness' of a relationship
RelationshipAddReferenceMeasureGroupDimension(tabularDatabase, foreignTableId, new RelationshipPair(new FullName(pkTableId, pkColumnId), new FullName(foreignTableId, foreignColumnId)), relationshipId);
// Adding Intermediate relationships for all tables in the paths from PrimaryKeyEndBelow for direct ForeignKeyEnd
foreach (RelationshipPair primaryKeyDownRelationship in currentRelationshipsGraph.RelationshipsListPrimaryKeyDown(pkTableId))
{
RelationshipAddReferenceMeasureGroupDimension(tabularDatabase, foreignTableId, primaryKeyDownRelationship);
}
// Adding Intermediate relationships for all tables in the paths from PrimaryKeyEndBelow in each of the tables
// that are ForeignKeyEndAbove
foreach (string foreignKeyTableUpId in currentRelationshipsGraph.TableListForeignKeyUp(foreignTableId))
{
using (AMO.MeasureGroup foreignKeyUpMG = tabularDatabase.Cubes[0].MeasureGroups[foreignKeyTableUpId])
{
RelationshipAddReferenceMeasureGroupDimension(tabularDatabase, foreignKeyUpMG.ID, new RelationshipPair(new FullName(pkTableId, pkColumnId), new FullName(foreignTableId, foreignColumnId)));
foreach (RelationshipPair primaryKeyDownRelationship in currentRelationshipsGraph.RelationshipsListPrimaryKeyDown(pkTableId))
{
RelationshipAddReferenceMeasureGroupDimension(tabularDatabase, foreignKeyUpMG.ID, primaryKeyDownRelationship);
}
}
}
}
else
{
// Because the ReferenceMeasureGroupDimension already exists, this probably means just a change on the foreign key used as Active
using (AMO.ReferenceMeasureGroupDimension currentReferenceMGDim = (AMO.ReferenceMeasureGroupDimension)foreignTableMG.Dimensions[pkTableId])
{
currentReferenceMGDim.RelationshipID = relationshipId;
currentReferenceMGDim.IntermediateGranularityAttributeID = foreignColumnId;
}
}
}
currentRelationshipsGraph.Clear();
break;
#endregion
case false:
#region set relationship to Inactive
// Verify relationship existence
// --> If relationship doesn't exist throw an error
if (!RelationshipExists(tabularDatabase, pkTableName, pkColumnName, foreignTableName, foreignColumnName))
{
throw new InvalidOperationException(Resources.RelationshipDoesNotExistInvalidOperationException);
}
// Build a graph of the actual state of relationships
currentRelationshipsGraph = new RelationshipGraph(tabularDatabase);
// Remove the ReferenceMeasureGroupDimension to remove the 'Activeness'
tabularDatabase.Cubes[0].MeasureGroups[foreignTableId].Dimensions.Remove(pkTableId);
// Removing Intermediate relationships for all tables in the paths from PrimaryKeyEndBelow for direct ForeignKeyEnd
foreach (string primaryKeyDownTableId in currentRelationshipsGraph.TableListPrimaryKeyDown(pkTableId))
{
tabularDatabase.Cubes[0].MeasureGroups[foreignTableId].Dimensions.Remove(primaryKeyDownTableId);
}
// Adding Intermediate relationships for all tables in the paths from PrimaryKeyEndBelow in each of the tables
// that are ForeignKeyEndAbove
foreach (string foreignKeyTableUpId in currentRelationshipsGraph.TableListForeignKeyUp(foreignTableId))
{
tabularDatabase.Cubes[0].MeasureGroups[foreignKeyTableUpId].Dimensions.Remove(pkTableId);
foreach (string primaryKeyDownTableId in currentRelationshipsGraph.TableListPrimaryKeyDown(pkTableId))
{
tabularDatabase.Cubes[0].MeasureGroups[foreignKeyTableUpId].Dimensions.Remove(primaryKeyDownTableId);
}
}
currentRelationshipsGraph.Clear();
break;
#endregion
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
/// <summary>
/// Adds the first table to the tabular model; information for all
/// columns is taken from the DSV table definition.
///
/// Subsequent tables should be added using TableAdd function.
///
/// As a consecuence of using TableAddFirstTable an AMO.Cube is created
/// to store needed objects that are part of the infrastructure of a
/// tabular model.
/// </summary>
/// <param name="tabularDatabase">A reference to an AMO database object</param>
/// <param name="cubeName">A string with the name of the AMO.Cobe object to be created</param>
/// <param name="datasourceTableName">A string with the name of the table from where the data will come to populate the destination table</param>
/// <param name="tableName">A string with the name of the tabular table added</param>
/// <param name="process">(optional) An AMO.ProcessType value to indicate the type of process required on the 'table' after creation</param>
/// <param name="hidden">(optional) A Boolean value to indicate if the table should be hidden (true)or hidden(false) in client tools</param>
/// <param name="defaultPartitionFilterClause">(optional) A boolean expression, in SQl language, that filters the rows for the default partition of the table</param>
/// <param name="modelDateColumn">(optional) The name of a date type calculatedColumn to be set as the unique key in the table. Also, as a consequence of defining this calculatedColumn, the entire table becomes the Date table of the model</param>
public static void TableAddFirstTable(AMO.Database tabularDatabase,
string cubeName,
string datasourceTableName,
string tableName,
bool updateInstance = true,
AMO.ProcessType?process = null,
bool?visible = null,
string defaultPartitionFilterClause = null,
string modelDateColumn = null)
{
// Major steps in creating the first table in the database
// NOTE: This function also creates the Cube object required for all tables
//
// - Validate required input arguments
// - Verify there are no other cubes in the database; as part of the initial conditions validations
//
// - Create local copy of cube
// - Create Table (use the TableAdd function to create the table)
//
// Note: There are no validations for duplicated names, invalid names or
// similar scenarios. It is expected the server will take care of them and
// throw exceptions on any invalid situation.
//
// Note: Only one DataSourceView is used in Tabular Models
// ==> tabularDatabase.DataSourceViews[0] represents the DSV of the model
//
// Note: Microsoft design tools use the following pattern to keep track of the
// datasource matching elements:
// DataSourceView->TableName <---> Dimension.ID, MeasureGroup.ID
// DataSourceView->ColumnName <---> Dimension->ColumnID, MeasureGroup.DegeneratedDimension->CoumnID
// So far, this sample follows the same pattern.
//
// WARNING: Breaking the above pattern when creating your
// own AMO to Tabular functions might lead to
// unpredictable behavior when using Microsoft
// Design tools in your models.
//
// Note: There are no validations on the ProcessType requested and whatever value is passed it's used
//
// Note: For tables, in tabular models, the following ProcessType values are 'valid' or have sense:
// - ProcessDefault ==> verifies if a data (at partition level) or recalc is required and issues coresponding internal process tasks
// - ProcessFull ==> forces data upload (on all partitions) and recalc, regardless of table status
// - ProcessData ==> forces data upload only (on all partitions); does not issue an internal recalc process task
// - ProcessClear ==> clears all table data (on all partitions)
//
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (cubeName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("cubeName");
}
if (datasourceTableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("datasourceTableName");
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
// Validate other intitial conditions: Verify there are no other cubes in the database
if (tabularDatabase.Cubes.Count > 0)
{
throw new InvalidOperationException(Resources.CubeAlreadyExistsInvalidOperationException);
}
// Create model cube
using (AMO.Cube cube = tabularDatabase.Cubes.Add(cubeName, cubeName))
{
// Create local copy of cube
cube.Source = new AMO.DataSourceViewBinding(tabularDatabase.DataSourceViews[0].ID);
cube.StorageMode = AMO.StorageMode.InMemory;
//
//Create initial MdxScript
//
AMO.MdxScript mdxScript = cube.MdxScripts.Add(MdxScriptStringName, MdxScriptStringName);
StringBuilder initialCommand = new StringBuilder();
initialCommand.AppendLine("CALCULATE;");
initialCommand.AppendLine("CREATE MEMBER CURRENTCUBE.Measures.[__No measures defined] AS 1, VISIBLE = 0;");
initialCommand.AppendLine("ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, Default_Member = [__No measures defined];");
mdxScript.Commands.Add(new AMO.Command(initialCommand.ToString()));
}
// Create table, but do not update server instance until comming back and procesing what the user requests in 'updateInstance'
// Note: As a best practice every time the object model is altered a database update needs to be issued; however, in this case
// to avoid multiple database updates while creating one major object (ie, Table) we are invoking TableAdd with NO updateInstance
TableAdd(tabularDatabase, datasourceTableName, tableName, false, null, visible, defaultPartitionFilterClause, modelDateColumn);
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
// Only after creating the table and updating the instance we can process it
if (process != null)
{
// Throw exception if server instance is outdated and user requests process
if (!updateInstance)
{
throw new InvalidOperationException(Resources.ProcessRequestedForOutdatedModelInvalidOperationException);
}
// Now the table can be processed according to the user request
TableProcess(tabularDatabase, tableName, process.Value);
}
}
