public static void RlsAdd(AMO.Database tabularDatabase,
string roleName,
string tableName,
string daxFilterExpression,
bool updateInstance = true)
{
// Major steps in adding a Row Level Security (RLS)
//
// - Validate required input arguments and other initial preparations
// - Add RLS to Table (as dimension) and enable ReadAccess
//
// Note: In AMO, strings as indexers refer to the ID of the object, not the name
//
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (roleName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(RoleStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (daxFilterExpression.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(DaxFilterExpressionStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
roleName = roleName.Trim();
tableName = tableName.Trim();
daxFilterExpression = daxFilterExpression.Trim();
#endregion
string dimensionPermissionName = string.Format(CultureInfo.InvariantCulture, "DimensionPermision_for_{0}", roleName);
string roleId = tabularDatabase.Roles.GetByName(roleName).ID;
using (AMO.DimensionPermission dimensionPermission = tabularDatabase.Dimensions.GetByName(tableName).DimensionPermissions.Add(roleId, dimensionPermissionName))
{
dimensionPermission.Read = AMO.ReadAccess.Allowed;
dimensionPermission.AllowedRowsExpression = daxFilterExpression;
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void MeasureDrop(AMO.Database tabularDatabase,
string tableName,
string measureName,
bool updateInstance = true)
{
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (measureName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(MeasureStringName);
}
// Validate required initial conditions
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
if (!MeasureExists(tabularDatabase, tableName, measureName))
{
throw new InvalidOperationException(Resources.MeasureDoesntExistsInvalidOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
measureName = measureName.Trim();
#endregion
switch ((CompatibilityLevel)tabularDatabase.CompatibilityLevel)
{
case CompatibilityLevel.SQL2012RTM:
MeasureDrop_2012RTM(tabularDatabase, tableName, measureName);
break;
case CompatibilityLevel.SQL2012SP1:
MeasureDrop_2012SP1(tabularDatabase, tableName, measureName);
break;
default:
throw new NotSupportedException(Resources.InvalidCompatibilityLevelOperationException);
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void RlsDrop(AMO.Database tabularDatabase,
string roleName,
string tableName,
bool updateInstance = true)
{
// Major steps in deleting/droping Row Level Security (RLS)
//
// - Validate required input arguments and other initial preparations
// - Remove DimensionPermissions in table (as dimension)
//
// Note: In AMO, strings as indexers refer to the ID of the object, not the name
//
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (roleName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(RoleStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
roleName = roleName.Trim();
tableName = tableName.Trim();
#endregion
// [Codeplex issue # 3]
// [JPJofre, 2012-10-18]
// [Description: tabularDatabase.Roles.GetByName(roleName).ID, returns the database role id not the DimensionsPermissions Id]
// [Suggested fix: TBD]
string roleId = tabularDatabase.Roles.GetByName(roleName).ID;
tabularDatabase.Dimensions.GetByName(tableName).DimensionPermissions.Remove(roleId, true);
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void TableAlterSetDateTable(AMO.Database tabularDatabase,
string tableName,
string columnName,
bool updateInstance = true)
{
// Major steps in setting the Date table in the database
//
// - Validate required input arguments
// - Set Date Column to Primary Key
// - Set table dimension type to Time
//
// 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.
//
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (columnName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(ColumnStringName);
}
//
// Set Date Column to Primary Key, 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 ColumnAlterSetPrimaryKey with NO updateInstance
ColumnAlterSetPrimaryKey(tabularDatabase, tableName, columnName, false);
// Set table dimension type to Time
tabularDatabase.Dimensions[tabularDatabase.Dimensions.GetByName(tableName).ID].Type = AMO.DimensionType.Time;
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void Main1()
{
serverAnalysis = new Microsoft.AnalysisServices.Server();
try
{
serverAnalysis.Connect("Data Source = " + strServerName);
olap = new Olap();
analysis.Database db = serverAnalysis.Databases.FindByName(strDataBaseName);
if (db != null)
{
db.Drop();
}
else
{
db = serverAnalysis.Databases.Add(strDataBaseName);
db.Update();
olap.CreateDataSource(db, strName, strConnectionString);
olap.CreateDataSourceView(db, strName, strName);
olap.CreateGeographyDimension(db, strName);
olap.CreateCustomerDimension(db, strName);
olap.CreateCube(db, strName);
}
}
catch (analysis.AmoException ex)
{
Console.WriteLine(ex.Message);
}
}
public static void TableAlterSetVisibility(AMO.Database tabularDatabase,
string tableName,
bool visible,
bool updateInstance = true)
{
// Major steps in setting the visibility of table in the database
//
// - Validate required input arguments
// - In the cube, set table dimension visible attribute
//
// 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 Cube is used in Tabular Models
// ==> tabularDatabase.Cubes[0] represents the cube in the model
//
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
// In the cube, set table dimension hidden attribute
tabularDatabase.Cubes[0].Dimensions[tabularDatabase.Dimensions.GetByName(tableName).ID].Visible = visible;
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
private void CreateDatabase()
{
try
{
AppendLogLine("");
AppendLogLine(string.Format("Creating a Analysis database: {0} ...", CbDbName));
_CbServer.Update();
if (_CbServer.Databases.Contains(CbDbName))
{
AppendLogLine(string.Format("Analysis database: [{0}] already exists, drop it?.", CbDbName));
if (MessageBox.Show(string.Format("The Analysis database '{0} - {1}' already exists. Do you want to drop it?", CbServerName, CbDbName),
"Warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.Yes)
{
_CbDatabase = _CbServer.Databases.GetByName(CbDbName);
_CbDatabase.Drop();
}
else
{
throw new Exception(string.Format("The Analysis database '{0} - {1}' already exists, but user did not want to drop it. Aborting procedure.",
CbServerName, CbDbName));
}
}
_CbDatabase = new Database();
//_CbServer.Databases.GetNewName(CbDbName)
_CbDatabase = _CbServer.Databases.Add(CbDbName);
//Save Database to the Analysis Services.
_CbDatabase.Update();
}
catch (Exception ex)
{
AppendLogLine("Error in creating a analysis database. Error Message -> " + ex.Message);
throw;
}
}
// *****************************************************************************
public static void PartitionAddWithNewConnection(AMO.Database tabularDatabase,
string datasourceConnectionString,
string connectionName,
string tableName,
string partitionName,
string SelectStatement,
bool updateInstance = true,
AMO.ProcessType?processPartition = null,
string alternateConnectionName = null)
{
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
// Process default partition
if (processPartition != null)
{
// Throw exception if server instance is outdated and user requests process
if (!updateInstance)
{
throw new InvalidOperationException(Resources.ProcessRequestedForOutdatedModelInvalidOperationException);
}
// Now the partition can be processed according to the user request
PartitionProcess(tabularDatabase, tableName, partitionName, processPartition.Value);
}
const string functionName = "PartitionAddWithNewConnection";
//
//ToDo: Add function code
//
throw new NotImplementedException(string.Format(Resources.NotImplementedExceptionFunction, functionName));
}
public static void RoleMemberDrop(AMO.Database tabularDatabase,
string roleName,
string windowsUserOrGroup,
bool updateInstance = true)
{
// Major steps in adding a RoleMember to a Role
//
// - Validate required input arguments
// - Other Initial preparations
// - Removing RoleMember from Role
//
// 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: In AMO, strings as indexers refer to the ID of the object, not the Name of the object
//
// 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 (roleName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(RoleStringName);
}
if (windowsUserOrGroup.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("windowsUserOrGroup");
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
roleName = roleName.Trim();
windowsUserOrGroup = windowsUserOrGroup.Trim();
#endregion
// [Codeplex issue # 7]
// [JPJofre, 2012-10-18]
// [Description: RoleMemberDrop(): Improper way of obtaining RoleMember for removal]
// [Suggested fix: Better iterate over all role members and remove when user matches current iterator]
string roleSid = getSid(windowsUserOrGroup);
using (AMO.Role role = tabularDatabase.Roles.GetByName(roleName))
{
foreach (AMO.RoleMember roleMember in role.Members)
{
if (0 == string.Compare(roleMember.Sid, roleSid, StringComparison.OrdinalIgnoreCase))
{
role.Members.Remove(roleMember);
break;
}
}
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
/// <summary>
/// Adds a connection object (aka DataSource object in AMO) and a
/// DataSourceView, if it doesn't exist, to a the local copy of
/// the tabular database, using the given OleDb connection string.
/// </summary>
/// <param name="tabularDatabase">A reference to an AMO server object</param>
/// <param name="datasourceOledbConnectionString">A well formed OleDb connection string to the relational data source</param>
/// <param name="connectionName">A string with the name on the connection object</param>
/// <param name="userName">(optional) A string with the user name; when given, it modifies impersonation from service account to user account</param>
/// <param name="password">(optional) A string with the user password; WARNING: this string is not encrypted</param>
public static void ConnectionAddRelationalDataSource(AMO.Database tabularDatabase,
string datasourceOledbConnectionString,
string connectionName,
bool updateInstance = true,
string userName = null,
string password = null)
{
// Major steps in creating a connection object or AMO.DataSource
// - Validate required input arguments
// - Create local copy of data source object (the connection object)
// - Verify there are no other DataSourceView view object
// - Create dsv object in database
// - Get source database schema
// - Add DataSource object to database
// - Add DSV (if created) to database
//
// 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: There are no validations on the 'datasourceOledbConnectionString'
// The user is responsible for accuracy and usability of the string.
//
// Note: In AMO only well formed OleDb connection strings are supported
// The 'Provider' key word must be the first keyword in the connection string
//
//
// Note: As of SQL Server 2012 Analysis Services, the only supported OleDb providers were:
//
// - "MICROSOFT.JET.OLEDB.4.0" <-- Microsoft OLE DB Provider for Microsoft Jet 4.0
// - "SQLOLEDB" <-- Microsoft SQL OLE DB Provider for SQL Server
// - "SNAC" <-- Microsoft SQL Native Client OLE DB Provider
// - "SQLNCLI" <-- SQL Server Native Client
// - "MSDAORA" <-- Microsoft OLE DB Provider for Oracle
// - "DB2OLEDB" <-- Microsoft OLE DB Provider for DB2
// - "TDOLEDB" <-- OLE DB Provider for Teradata
// - "IFXOLEDBC" <-- IBM Informix OLE DB Provider
// - "SYBASE ASE OLE DB PROVIDER" <-- OLE DB Provider for Sybase Adaptive Server Enterprise (ASE)
// - "ASAPROV" <-- OLE DB Provider for Sybase Adaptive Server Anywhere (ASA)
// - "SYBASE OLEDB PROVIDER" <-- OLE DB Provider for Sybase in version 12
// - "ASEOLEDB" <-- OLE DB Provider for Sybase in version 15
// - "Microsoft SQL Server MPP OLE DB Provider" <-- OLE DB Provider for SQL Server MPP OLE DB Provider
bool dsvAdded = false;
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (datasourceOledbConnectionString.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("datasourceOledbConnectionString");
}
if (connectionName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("connectionName");
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Create local copy of data source
using (AMO.DataSource connection = new AMO.RelationalDataSource(connectionName, connectionName))
using (AMO.DataSourceView dsv = new AMO.DataSourceView(connectionName, connectionName))
{
connection.ConnectionString = datasourceOledbConnectionString;
//If user name is null or blank, use default ImpersonateServiceAccount
if (userName.IsNullOrEmptyOrWhitespace())
{
connection.ImpersonationInfo = new AMO.ImpersonationInfo(AMO.ImpersonationMode.ImpersonateServiceAccount);
}
else
{// At this point, there is no verification that the given credentials will work
connection.ImpersonationInfo = new AMO.ImpersonationInfo(AMO.ImpersonationMode.ImpersonateAccount, userName.Trim(), password);
}
// Verify DSV existence and create one if needed
if (tabularDatabase.DataSourceViews.Count == 0)
{// No DSV in the database
// Note: DSV added and populated it with the entire relational database schema
dsvAdded = true;
dsv.Schema = GetDatabaseSchema(datasourceOledbConnectionString);
dsv.DataSourceID = connection.ID;
}
// Add DataSource and DataSourceView objects to database
tabularDatabase.DataSources.Add(connection);
if (dsvAdded)
{
tabularDatabase.DataSourceViews.Add(dsv);
}
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
/// <summary>
/// Adds a table to the tabular model; information for all
/// columns is taken from the DSV table definition.
/// </summary>
/// <param name="tabularDatabase">A reference to an AMO database object</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 TableAdd(AMO.Database tabularDatabase,
string dataSourcetableName,
string tableName,
bool updateInstance = true,
AMO.ProcessType?process = null,
bool?visible = null,
string defaultPartitionFilterClause = null,
string modelDateColumn = null)
{
// Table creation strategy:
// Because a table is a combination of a Dimension and a MeasureGroup
// there are different ways to create the table, here we have two
// possibilities
//
// A Create the entire Dimension object with attributes, add the
// reference to the dimension in the cube, create the entire
// MeasureGroup (associated to the dimension and attributes),
// add the default partition to the MeasureGroup, update Primary
// Key attribute from source table and, finally, process
// partition (if user wants it).
// This sequence of steps implies adding all table columns to
// the dimension and later iterate again over the same columns
// to add them to the 'degenerated' dimension of the MeasureGroup
//
// B Create an 'empty' Dimension object (with only the rownumber
// attribute), add the reference to the dimension in the cube,
// create the MeasureGroup object (based on the 'empty'dimension),
// add the default partition, add all columns in the source
// table to both dimension and degenerated dimension in
// MeasureGroup, update Primary Key and, finally, process
// partition (if user wants it).
// This sequence of steps implies creating the skeleton
// infrastructure of a table and later add all columns using
// ColumnAdd function.
// This is the approach used to create a table in this sample
//
// Major steps in creating a table in the database
//
// - Validate required input arguments
// - Verify there is 1 and only 1 cube in the database; as part of the initial conditions validations
//
// - Add 'empty' table to cube
// - Add columns from DSV source information
// - Update Primary Key
// - Update Date Table
// - Process default partition
//
// 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: 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.
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (dataSourcetableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("datasourceTableName");
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
// Validate other intitial conditions: Verify there is only one cube in the database
if (tabularDatabase.Cubes.Count != 1)
{
throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, Resources.InvalidNumberOfCubesInvalidOperationException, tabularDatabase.Cubes.Count));
}
// Add 'empty' table to model
// Create empty 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 ColumnAdd with NO updateInstance
TableAddEmptyTable(tabularDatabase, dataSourcetableName, tableName, defaultPartitionFilterClause, false);
// Add columns from DSV source information
//
// Note: Only columns of supported data types, in tabular models, are added
// --> skipping unsupported data types
foreach (DataColumn currentColumn in tabularDatabase.DataSourceViews[0].Schema.Tables[dataSourcetableName].Columns)
{
if (mapToSupportedTabularDataTypes(tabularDatabase.DataSourceViews[0].Schema.Tables[dataSourcetableName].Columns[currentColumn.ColumnName].DataType) != DataType.Unsupported)
{
// Create calculatedColumn, but do not update server instance until comming back and procesing what the user requests in 'updateInstance'
ColumnAdd(tabularDatabase, tableName, currentColumn.ColumnName, null, false);
}
}
// Update Date Table
if (!modelDateColumn.IsNullOrEmptyOrWhitespace())
{
// Set Date Table, but do not update server instance until comming back and procesing what the user requests in 'updateInstance'
TableAlterSetDateTable(tabularDatabase, tableName, modelDateColumn, false);
}
// Update table visibility
if (visible != null)
{
// Set visibility, but do not update server instance until comming back and procesing what the user requests in 'updateInstance'
TableAlterSetVisibility(tabularDatabase, tableName, visible.Value, false);
}
// 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);
}
}
public static void PerspectiveAlterMeasureAdd(AMO.Database tabularDatabase,
string perspectiveName,
string tableName,
string measureName,
bool updateInstance = true)
{
// Major steps in adding a Measure to a Perspective in the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Adding measure to perspective
// Note: A Measure is added to a perspective without its related 'table'.
// Measures are added to the perspective as part of the Calculations collection
// of the perspective
// Note: At the same time if the measure has been promoted to KPI, the corresponding KPI
// is added to the KPIs collection of the perspective
//
//
// 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: In AMO, strings as indexers refer to the ID of the object, not the Name of the object
//
// 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 (perspectiveName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(PerspectiveStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (measureName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(MeasureStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
perspectiveName = perspectiveName.Trim();
tableName = tableName.Trim();
measureName = measureName.Trim();
#endregion
using (AMO.Perspective perspective = tabularDatabase.Cubes[0].Perspectives.GetByName(perspectiveName))
using (AMO.PerspectiveCalculation measureCalculation = perspective.Calculations.Add(measureName, AMO.PerspectiveCalculationType.Member))
{
if (KpiExist(tabularDatabase, tableName, measureName))
{
perspective.Kpis.Add(measureName);
}
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void HierarchyAdd(AMO.Database tabularDatabase,
string tableName,
string hierarchyName,
bool updateInstance = true,
params LevelInfo[] levelInfo)
{
// Major steps in adding a Hierarchy to a table in the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Adding 'Empty' Hierarchy to dimension
// - Adding Levels to Hierarchy
//
// 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: In AMO, strings as indexers refer to the ID of the object, not the Name of the object
//
// 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 (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (hierarchyName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(HierarchyStringName);
}
if (levelInfo == null || levelInfo.Length == 0)
{
throw new ArgumentNullException(LevelInfo);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
hierarchyName = hierarchyName.Trim();
// - Obtain table name in DSV
string datasourceTableName = tabularDatabase.Dimensions.GetByName(tableName).ID;
#endregion
// Add 'empty' hierarchy
using (AMO.Hierarchy currentHierarchy = tabularDatabase.Dimensions[datasourceTableName].Hierarchies.Add(hierarchyName, hierarchyName))
{
currentHierarchy.AllMemberName = string.Format(CultureInfo.InvariantCulture, "(All of {0})", hierarchyName);
}
// Add levels
AMO2Tabular.HierarchyAlterLevelsAdd(tabularDatabase, tableName, hierarchyName, false, levelInfo);
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void PartitionAlterMerge(AMO.Database tabularDatabase,
string tableName,
string destinationPartitionName,
bool updateInstance = true,
params string[] sourcePartitionNames)
{
// Major steps in Merging Partitions from a table in the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Obtain source partitions Ids
// - Obtain destination partition
// - Merge source partitions into destination partition
// - Drop/delete source partitions (to eliminate duplication)
//
// 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: In AMO, strings as indexers refer to the ID of the object, not the Name of the object
//
// 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.
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (destinationPartitionName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("destinationPartitionName");
}
bool sourcePartitionNamesEmpty = true;
foreach (string sourcePartitionName in sourcePartitionNames)
{
if (!sourcePartitionName.IsNullOrEmptyOrWhitespace())
{
sourcePartitionNamesEmpty = false;
break;
}
}
if (sourcePartitionNamesEmpty)
{
throw new ArgumentNullException("sourcePartitionNames");
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
destinationPartitionName = destinationPartitionName.Trim();
// - Obtain table name in DSV
string datasourceTableName = tabularDatabase.Dimensions.GetByName(tableName).ID;
// - Obtain source partitions Ids
// - Obtain destination partition
// - Merge source partitions into destination partition
// - Drop/delete source partitions (to eliminate duplication)
List <string> sourcePartitionIds = new List <string>();;
List <AMO.Partition> sourcePartitions = new List <AMO.Partition>();
using (AMO.MeasureGroup tableMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups[datasourceTableName])
using (AMO.Partition destinationPartition = tableMeasureGroup.Partitions.GetByName(destinationPartitionName))
{
foreach (string sourcePartitionName in sourcePartitionNames)
{
if (!sourcePartitionName.IsNullOrEmptyOrWhitespace())
{
sourcePartitionIds.Add(tableMeasureGroup.Partitions.GetByName(sourcePartitionName).ID);
sourcePartitions.Add(tableMeasureGroup.Partitions.GetByName(sourcePartitionName));
}
}
destinationPartition.Merge(sourcePartitions);
foreach (string sourcePartitionId in sourcePartitionIds)
{
tableMeasureGroup.Partitions.Remove(sourcePartitionId, true);
}
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void PartitionAlterConnection(AMO.Database tabularDatabase,
string tableName,
string partitionName,
string connectionName,
bool updateInstance = true)
{
// Major steps in updating or Altering the Connection or DataSource of a Partition in a table in the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Update Query Binding in Measure Group
//
// Note: This function only updates the DataSource or Connection; does not change the select statement
// of the partition
//
//
// 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: In AMO, strings as indexers refer to the ID of the object, not the Name of the object
//
// 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.
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (partitionName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("partitionName");
}
if (connectionName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("connectionName");
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
partitionName = partitionName.Trim();
connectionName = connectionName.Trim();
// - Obtain table name in DSV
string datasourceTableName = tabularDatabase.Dimensions.GetByName(tableName).ID;
string connectionId = tabularDatabase.DataSources.GetByName(connectionName).ID;
using (AMO.MeasureGroup tableMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups[datasourceTableName])
using (AMO.Partition partition = tableMeasureGroup.Partitions.GetByName(partitionName))
{
AMO.QueryBinding qb = partition.Source as AMO.QueryBinding;
partition.Source = new AMO.QueryBinding(connectionId, qb.QueryDefinition);
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void PartitionAdd(AMO.Database tabularDatabase,
string tableName,
string partitionName,
string selectStatement,
bool updateInstance = true,
AMO.ProcessType?processPartition = null,
string alternateConnectionName = null)
{
// Major steps in adding a Partition to a table in the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Defining Datasource to use
// - Adding Partition to Measure Group
//
// 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: In AMO, strings as indexers refer to the ID of the object, not the Name of the object
//
// 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.
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (partitionName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("partitionName");
}
if (selectStatement.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("selectStatement");
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
partitionName = partitionName.Trim();
selectStatement = selectStatement.Trim();
// - Obtain table name in DSV
string datasourceTableName = tabularDatabase.Dimensions.GetByName(tableName).ID;
// - Obtain DataSourceId
string dataSourceId;
if (alternateConnectionName == null)
{
dataSourceId = tabularDatabase.DataSourceViews[0].DataSourceID;
}
else
{
dataSourceId = tabularDatabase.DataSources.GetByName(alternateConnectionName.Trim()).ID;
}
using (AMO.MeasureGroup tableMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups[datasourceTableName])
using (AMO.Partition partition = new AMO.Partition(partitionName, partitionName))
{
partition.StorageMode = AMO.StorageMode.InMemory;
partition.ProcessingMode = AMO.ProcessingMode.Regular;
partition.Source = new AMO.QueryBinding(dataSourceId, selectStatement);
partition.Type = AMO.PartitionType.Data;
tableMeasureGroup.Partitions.Add(partition);
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
// Process default partition
if (processPartition != null)
{
// Throw exception if server instance is outdated and user requests process
if (!updateInstance)
{
throw new InvalidOperationException(Resources.ProcessRequestedForOutdatedModelInvalidOperationException);
}
// Now the partition can be processed according to the user request
PartitionProcess(tabularDatabase, tableName, partitionName, processPartition.Value);
}
}
/// <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);
}
}
public static void RoleAdd(AMO.Database tabularDatabase,
string roleName,
bool readPermission,
bool processPermission,
bool administerPermission,
string roleDescription = null,
bool updateInstance = true)
{
// Major steps in adding a Role to the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Adding Role to Database
// - Assigning permissions to role:
// - if administerPermission
// user has all permssions
// else
// assign process permision accordingly to database and cube objects
// assign read permission accordingly to database and cube objects
//
// 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: In AMO, strings as indexers refer to the ID of the object, not the Name of the object
//
// 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 (roleName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(RoleStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
roleName = roleName.Trim();
#endregion
string databasePermissionName = string.Format(CultureInfo.InvariantCulture, "DatabasePermision_for_{0}", roleName);
string cubePermissionName = string.Format(CultureInfo.InvariantCulture, "CubePermision_for_{0}", roleName);
using (AMO.Role role = tabularDatabase.Roles.Add(roleName, roleName))
using (AMO.DatabasePermission databasePermission = tabularDatabase.DatabasePermissions.Add(roleName, databasePermissionName))
using (AMO.CubePermission cubePermission = tabularDatabase.Cubes[0].CubePermissions.Add(roleName, cubePermissionName))
{
if (administerPermission)
{
databasePermission.Administer = administerPermission;
databasePermission.Process = true;
databasePermission.Read = AMO.ReadAccess.Allowed;
databasePermission.ReadDefinition = AMO.ReadDefinitionAccess.Allowed;
cubePermission.Process = true;
cubePermission.Read = AMO.ReadAccess.Allowed;
cubePermission.ReadDefinition = AMO.ReadDefinitionAccess.Allowed;
}
else
{
databasePermission.Process = processPermission;
cubePermission.Process = processPermission;
if (readPermission)
{
databasePermission.Read = AMO.ReadAccess.Allowed;
cubePermission.Read = AMO.ReadAccess.Allowed;
}
else
{
databasePermission.Read = AMO.ReadAccess.None;
cubePermission.Read = AMO.ReadAccess.None;
}
}
// Note: in all cases no user has access to read source data
cubePermission.ReadSourceData = AMO.ReadSourceDataAccess.None;
if (roleDescription != null)
{
role.Description = roleDescription.Trim();
}
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void HierarchyAlterLevelAdd(AMO.Database tabularDatabase,
string tableName,
string hierarchyName,
string definingLevelColumnName,
string levelName,
bool updateInstance = true)
{
// Major steps in adding a Level to a Hierarchy in a table
//
// - Validate required input arguments
// - Other Initial preparations
// - Add Level to Hierarchy
//
// 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: In AMO, strings as indexers refer to the ID of the object, not the Name of the object
//
// 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 (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (hierarchyName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(HierarchyStringName);
}
if (definingLevelColumnName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(DefiningLevelColumnName);
}
if (levelName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(LevelName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
hierarchyName = hierarchyName.Trim();
#endregion
// [Codeplex issue # 8]
// [JPJofre, 2012-10-18]
// [Description: using (AMO.Hierarchy currentHierarchy = tabularDatabase.Dimensions[tableName].Hierarchies.Add(hierarchyName, hierarchyName));
// ... this creates a NEW hierarchy object instead of obtaining one!!!]
// [Suggested fix: replace Add() with FindByName()]
// Add level
using (AMO.Hierarchy currentHierarchy = tabularDatabase.Dimensions[tableName].Hierarchies.FindByName(hierarchyName))
{
string attributeId = tabularDatabase.Dimensions.GetByName(tableName).Attributes.GetByName(definingLevelColumnName).ID;
currentHierarchy.Levels.Add(levelName).SourceAttributeID = attributeId;
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void CalculatedColumnAdd(AMO.Database tabularDatabase,
string tableName,
string columnName,
string daxExpression,
bool updateInstance = true,
ColumnInfo?columnProperties = null,
ReportingInfo?reportingProperties = null,
AMO.ProcessType?processType = null)
{
// Major steps in adding a calculated calculatedColumn to a table in the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Adding calculatedColumn as attribute to dimension
// - Adding calculatedColumn as attribute to degenerated dimension in Measure Group
// - Set calculatedColumn properties according to optional parameters
// - Set reporting properties according to optional parameters
// - Process table/database according to optional parameters
//
// 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: In AMO, strings as indexers refer to the ID of the object, not the Name of the object
//
// 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.
//
// Note: There are no validations on the ProcessType requested and whatever value is passed it's used
//
// Note: For Calculated Columns, in tabular models, the following ProcessType values are 'valid' or have sense:
// - ProcessDefault ==> (issued at table level) verifies if a data (at partition level) or recalc is
// required and issues coresponding internal process tasks
// - ProcessFull ==> (issued at table level) forces data upload (on all partitions) and recalc,
// regardless of table status
// - ProcessRecalc ==> (issued at Database level) forces a recalc of internal structures (measures,
// calculated columns, hierarchies, etc.) at database level; doesn't load
// new data.
//
// Note: Issuing a process request (setting parameter processType != null) forces a database update
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (columnName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(ColumnStringName);
}
if (daxExpression.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(DaxExpressionStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
columnName = columnName.Trim();
daxExpression = daxExpression.Trim();
// - Obtain table name in DSV
string datasourceTableName = tabularDatabase.Dimensions.GetByName(tableName).ID;
// - Obtain "RowNumber" column id
string rowNumberColumnId = string.Empty;
foreach (AMO.DimensionAttribute da in tabularDatabase.Dimensions[datasourceTableName].Attributes)
{
if (da.Type == AMO.AttributeType.RowNumber)
{
rowNumberColumnId = da.ID;
break;
}
}
#endregion
// Add calculated calculatedColumn as attribute to the Dimension
// Note: datasourceTableName == tableId; because parity with DS object names needs to be kept
using (AMO.Dimension tableDimension = tabularDatabase.Dimensions[datasourceTableName])
using (AMO.DimensionAttribute calculatedColumnDimensionAttribute = tableDimension.Attributes.Add(columnName, columnName))
using (AMO.DataItem dataItemEmptyType = new AMO.DataItem(datasourceTableName, columnName, System.Data.OleDb.OleDbType.Empty))
using (AMO.ExpressionBinding expressionBinding = new AMO.ExpressionBinding(daxExpression))
using (AMO.DataItem dataItemWCharType = new AMO.DataItem(datasourceTableName, columnName, System.Data.OleDb.OleDbType.WChar))
{
calculatedColumnDimensionAttribute.Usage = AMO.AttributeUsage.Regular;
calculatedColumnDimensionAttribute.KeyUniquenessGuarantee = false;
calculatedColumnDimensionAttribute.KeyColumns.Add(dataItemEmptyType);
calculatedColumnDimensionAttribute.KeyColumns[0].Source = expressionBinding.Clone();
calculatedColumnDimensionAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
calculatedColumnDimensionAttribute.NameColumn = dataItemWCharType;
calculatedColumnDimensionAttribute.NameColumn.Source = expressionBinding;
calculatedColumnDimensionAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
calculatedColumnDimensionAttribute.OrderBy = AMO.OrderBy.Key;
using (AMO.AttributeRelationship calculatedColumnDimensionAttributeRelationship = tableDimension.Attributes[rowNumberColumnId].AttributeRelationships.Add(calculatedColumnDimensionAttribute.ID))
{
calculatedColumnDimensionAttributeRelationship.Cardinality = AMO.Cardinality.Many;
calculatedColumnDimensionAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
}
}
// Add calculatedColumn as attribute to the MG, in the DegeneratedMeasureGroupDimension
using (AMO.MeasureGroup tableMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups[datasourceTableName])
using (AMO.DegenerateMeasureGroupDimension tableMGDimension = (AMO.DegenerateMeasureGroupDimension)tableMeasureGroup.Dimensions[datasourceTableName])
using (AMO.MeasureGroupAttribute calculatedColumnMGAttribute = new AMO.MeasureGroupAttribute(columnName))
using (AMO.DataItem dataItemEmptyType = new AMO.DataItem(datasourceTableName, columnName, System.Data.OleDb.OleDbType.Empty))
using (AMO.ExpressionBinding expressionBinding = new AMO.ExpressionBinding(daxExpression))
{
calculatedColumnMGAttribute.KeyColumns.Add(dataItemEmptyType);
calculatedColumnMGAttribute.KeyColumns[0].Source = expressionBinding;
tableMGDimension.Attributes.Add(calculatedColumnMGAttribute);
}
// Set/update optional calculatedColumn properties
if (columnProperties != null)
{
if (!columnProperties.Value.DataFormat.IsNullOrEmptyOrWhitespace())
{
ColumnAlterFormat(tabularDatabase, tableName, columnName, columnProperties.Value.DataFormat);
}
if (columnProperties.Value.DataType != DataType.Default && columnProperties.Value.DataType != DataType.Unsupported)
{
ColumnAlterDataType(tabularDatabase, tableName, columnName, columnProperties.Value.DataType);
}
if (columnProperties.Value.Visible != null)
{
ColumnAlterVisibility(tabularDatabase, tableName, columnName, columnProperties.Value.Visible.Value);
}
if (!columnProperties.Value.SortByColumn.IsNullOrEmptyOrWhitespace())
{
ColumnAlterSortByColumnName(tabularDatabase, tableName, columnName, columnProperties.Value.SortByColumn);
}
}
// ToDo: Set/update optional reporting properties
//if (reportingProperties != null)
//{
//}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
if (processType != null)
{
// Throw exception if server instance is outdated and user requests process
if (!updateInstance)
{
throw new InvalidOperationException(Resources.ProcessRequestedForOutdatedModelInvalidOperationException);
}
// Now the table, that contains the Calculated Column, can be processed according to the user request
TableProcess(tabularDatabase, tableName, (AMO.ProcessType)processType);
// Calculated columns require a database level process recalc
tabularDatabase.Process(AMO.ProcessType.ProcessRecalc);
}
}
public static void RelationshipDrop(AMO.Database tabularDatabase,
string pkTableName,
string pkColumnName,
string foreignTableName,
string foreignColumnName,
bool updateInstance = true)
{
// Terminology note:
// - the Foreign 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
// ==> Relationship information is stored in the Foreign Dimension object
//
// Major steps in adding a relationship
//
// - Validate required input arguments and other initial preparations
// - Verify relationship exist; throw error if it doesn't exist
// - Remove Active part first
// - Remove In-Active part lastly
//
// Note: In AMO, strings as indexers refer to the ID of the object, not the name
//
#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 foreignTableId = tabularDatabase.Dimensions.GetByName(foreignTableName).ID;
#endregion
// Verify relationship existence
// --> throw an exception if relationship doesn't exist
string relationshipId = RelationshipTryGetRelationshipId(tabularDatabase, pkTableName, pkColumnName, foreignTableName, foreignColumnName);
if (relationshipId.IsNullOrEmptyOrWhitespace())
{
throw new NotSupportedException(Resources.RelationshipDoesNotExistInvalidOperationException);
}
// Remove Active part
if (RelationshipIsActive(tabularDatabase, pkTableName, pkColumnName, foreignTableName, foreignColumnName))
{
RelationshipAlterActive(tabularDatabase, pkTableName, pkColumnName, foreignTableName, foreignColumnName, false);
}
// Remove In-Active part
tabularDatabase.Dimensions[foreignTableId].Relationships.Remove(relationshipId);
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
/// <summary>
/// Adds an 'empty' table to the tabular model; no calculatedColumn informationis added
/// </summary>
/// <param name="tabularDatabase">A reference to an AMO database object</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>
public static void TableAddEmptyTable(AMO.Database tabularDatabase,
string datasourceTableName,
string tableName,
string defaultPartitionFilterClause = null,
bool updateInstance = true
)
{
// Table creation strategy:
// Because a table is a combination of a Dimension and a MeasureGroup
// there are different ways to create the table, here we have two
// possibilities
//
// A Create the entire Dimension object with attributes, add the
// reference to the dimension in the cube, create the entire
// MeasureGroup (associated to the dimension and attributes),
// add the default partition to the MeasureGroup, update Primary
// Key attribute from source table and, finally, process
// partition (if user wants it).
// This sequence of steps implies adding all table columns to
// the dimension and later iterate again over the same columns
// to add them to the 'degenerated' dimension of the MeasureGroup
//
// B Create an 'empty' Dimension object (with only the rownumber
// attribute), add the reference to the dimension in the cube,
// create the MeasureGroup object (based on the 'empty'dimension),
// add the default partition, add all columns in the source
// table to both dimension and degenerated dimension in
// MeasureGroup, update Primary Key and, finally, process
// partition (if user wants it).
// This sequence of steps implies creating the skeleton
// infrastructure of a table and later add all columns using
// ColumnAdd function.
// This is the approach used to create a table in this sample
//
// Major steps in creating a table in the database
//
// - Validate required input arguments
// - Verify there is 1 and only 1 cube in the database; as part of the initial conditions validations
//
// - Create empty local copy of Dimension object
// - Add Dimension reference to cube
// - Add empty MeasureGroup to cube
// - Adding default Measure to MeasureGroup
// - Add 'Dimension' to MeasureGroup
// - Add partition to MeasureGroup
//
// 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: 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.
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (datasourceTableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("datasourceTableName");
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
// Validate other intitial conditions: Verify there is only one cube in the database
if (tabularDatabase.Cubes.Count != 1)
{
throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, Resources.InvalidNumberOfCubesInvalidOperationException, tabularDatabase.Cubes.Count));
}
// Create empty local copy of Dimension object in two (2) steps:
// - Define Dimension general properties
// - Manualy add "RowNumber" calculatedColumn
//
// Define Dimension general properties
string rowNumberColumnName = string.Format(CultureInfo.InvariantCulture, "RowNumber_{0}", Guid.NewGuid()); // Making sure the RowNumber calculatedColumn has a unique name
using (AMO.Dimension tableDimension = tabularDatabase.Dimensions.Add(tableName, datasourceTableName))
{
tableDimension.Source = new AMO.DataSourceViewBinding(tabularDatabase.DataSourceViews[0].ID);
tableDimension.StorageMode = AMO.DimensionStorageMode.InMemory;
tableDimension.UnknownMember = AMO.UnknownMemberBehavior.AutomaticNull;
tableDimension.UnknownMemberName = "Unknown";
tableDimension.ErrorConfiguration = new AMO.ErrorConfiguration();
tableDimension.ErrorConfiguration.KeyNotFound = AMO.ErrorOption.IgnoreError;
tableDimension.ErrorConfiguration.KeyDuplicate = AMO.ErrorOption.ReportAndStop;
tableDimension.ErrorConfiguration.NullKeyNotAllowed = AMO.ErrorOption.ReportAndStop;
tableDimension.ProactiveCaching = new AMO.ProactiveCaching();
TimeSpan defaultProactiveChachingTimeSpan = new TimeSpan(0, 0, -1);
tableDimension.ProactiveCaching.SilenceInterval = defaultProactiveChachingTimeSpan;
tableDimension.ProactiveCaching.Latency = defaultProactiveChachingTimeSpan;
tableDimension.ProactiveCaching.SilenceOverrideInterval = defaultProactiveChachingTimeSpan;
tableDimension.ProactiveCaching.ForceRebuildInterval = defaultProactiveChachingTimeSpan;
tableDimension.ProactiveCaching.Source = new AMO.ProactiveCachingInheritedBinding();
// Manualy add a "RowNumber" attribute as the key attribute of the dimension
// "RowNumber" is a required calculatedColumn for a tabular model and has to be of type AMO.AttributeType.RowNumber and binding AMO.RowNumberBinding
// The name of the "RowNumber" attribute can be any name, as long as type and binding are correctly set
// By default the MS client tools set the calculatedColumn name and calculatedColumn ID of the RowNumber attribute to
// "RowNumber"; and, to "InternalRowNumber" if there is a collition with a user calculatedColumn named "RowNumber"
// In this sample, to avoid problems with any customer table that contains a calculatedColumn named "RowNumber" and/or "InternalRowNumber"
// the Name and Id value of the calculatedColumn (in the dimension object) will be renamed to
// 'RowNumber_<NewGuid()>';
// For that purpose the variable rowNumberColumnId was defined above
using (AMO.DimensionAttribute rowNumber = tableDimension.Attributes.Add(rowNumberColumnName, rowNumberColumnName))
{
rowNumber.Type = AMO.AttributeType.RowNumber;
rowNumber.KeyUniquenessGuarantee = true;
rowNumber.Usage = AMO.AttributeUsage.Key;
rowNumber.KeyColumns.Add(new AMO.DataItem());
rowNumber.KeyColumns[0].DataType = System.Data.OleDb.OleDbType.Integer;
rowNumber.KeyColumns[0].DataSize = 4;
rowNumber.KeyColumns[0].NullProcessing = AMO.NullProcessing.Error;
rowNumber.KeyColumns[0].Source = new AMO.RowNumberBinding();
rowNumber.NameColumn = new AMO.DataItem();
rowNumber.NameColumn.DataType = System.Data.OleDb.OleDbType.WChar;
rowNumber.NameColumn.DataSize = 4;
rowNumber.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
rowNumber.NameColumn.Source = new AMO.RowNumberBinding();
rowNumber.OrderBy = AMO.OrderBy.Key;
rowNumber.AttributeHierarchyVisible = false;
}
}
// Add Dimension reference to cube
tabularDatabase.Cubes[0].Dimensions.Add(datasourceTableName, tableName, datasourceTableName);
// Add empty MeasureGroup to cube
using (AMO.MeasureGroup tableMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups.Add(tableName, datasourceTableName))
{
tableMeasureGroup.StorageMode = AMO.StorageMode.InMemory;
tableMeasureGroup.ProcessingMode = AMO.ProcessingMode.Regular;
// Add default Measure to MeasureGroup
string defaultMeasureID = string.Concat("_Count ", tableName);
using (AMO.Measure defaultMeasure = tableMeasureGroup.Measures.Add(defaultMeasureID, defaultMeasureID))
using (AMO.RowBinding defaultMeasureRowBinding = new AMO.RowBinding(datasourceTableName))
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 MeasureGroup
using (AMO.DegenerateMeasureGroupDimension defaultMGDim = new AMO.DegenerateMeasureGroupDimension(tableName))
using (AMO.MeasureGroupAttribute mga = new AMO.MeasureGroupAttribute(rowNumberColumnName))
using (AMO.ColumnBinding rowNumberColumnBinding = new AMO.ColumnBinding(datasourceTableName, rowNumberColumnName))
using (AMO.DataItem rowNumberKeyColumn = new AMO.DataItem(rowNumberColumnBinding))
{
defaultMGDim.ShareDimensionStorage = AMO.StorageSharingMode.Shared;
defaultMGDim.CubeDimensionID = datasourceTableName;
mga.Type = AMO.MeasureGroupAttributeType.Granularity;
rowNumberKeyColumn.DataType = System.Data.OleDb.OleDbType.Integer;
mga.KeyColumns.Add(rowNumberKeyColumn);
defaultMGDim.Attributes.Add(mga);
tableMeasureGroup.Dimensions.Add(defaultMGDim);
}
// Add default partition to MeasureGroup
StringBuilder partitionSqlStatement = new StringBuilder();
partitionSqlStatement.Append("SELECT * FROM [");
partitionSqlStatement.Append(datasourceTableName);
partitionSqlStatement.Append("]");
if (defaultPartitionFilterClause != null)
{
partitionSqlStatement.Append(" WHERE ");
partitionSqlStatement.Append(defaultPartitionFilterClause);
}
// Create partition, 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 PartitionAdd with NO updateInstance
PartitionAdd(tabularDatabase, tableName, tableName, partitionSqlStatement.ToString(), false);
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void KpiAdd(AMO.Database tabularDatabase,
string tableName,
string measureName,
string goalExpression,
string statusExpression,
string statusGraphicImageName,
bool updateInstance = true)
{
// Major steps in adding a KPI to a table in the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Verify if the header of the Measures script needs to be created; create it if needed.
// - Store the existing MdxScript in a 'temporary' variable
// - Append the new KPI elements to the 'temporary' variable
// - Replace existing MdxScript with 'temporary' variable
// - Update Calculation Properties for KPI elements
//
// Important Conceptual Note:
// In Tabular Models a KPI is a Measure that has been promoted to KPI; so, the underlying measure still
// exists and cannot be hide
//
// Note: Measures are dynamic objects that live inside an MdxScript object in the cube
//
// IMPORTANT Note: Measures must not be created as native OLAP measure objects, from the MeasureGroup object.
// !! Native OLAP measures cannot be:
// • Referenced by DAX queries
// • Referenced by calculated columns
// • Referenced by other DAX measures
//
// 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: 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 of the model
//
// Note: Only one Commands script is used in Tabular Models to hold all ALL Measures and KPIs of the model
// ==> tabularDatabase.Cubes[0].MdxScripts["MdxScript"].Commands[1].Text contains ALL Measures and KPIs 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.
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (measureName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(MeasureStringName);
}
if (goalExpression.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("goalExpression");
}
if (statusExpression.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("statusExpression");
}
if (statusGraphicImageName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("statusGraphicImageName");
}
// Validate required initial conditions
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
if (!MeasureExists(tabularDatabase, tableName, measureName))
{
throw new InvalidOperationException(Resources.MeasureDoesntExistsInvalidOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
measureName = measureName.Trim();
goalExpression = goalExpression.Trim();
statusExpression = statusExpression.Trim();
statusGraphicImageName = statusGraphicImageName.Trim();
#endregion
switch ((CompatibilityLevel)tabularDatabase.CompatibilityLevel)
{
case CompatibilityLevel.SQL2012RTM:
KpiAdd_2012RTM(tabularDatabase, tableName, measureName, goalExpression, statusExpression, statusGraphicImageName);
break;
case CompatibilityLevel.SQL2012SP1:
KpiAdd_2012SP1(tabularDatabase, tableName, measureName, goalExpression, statusExpression, statusGraphicImageName);
break;
default:
throw new NotSupportedException(Resources.InvalidCompatibilityLevelOperationException);
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
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);
}
}
public static void PerspectiveAlterTableAdd(AMO.Database tabularDatabase,
string perspectiveName,
string tableName,
bool updateInstance = true)
{
// Major steps in adding a Table to a Perspective in the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Adding Table to perspective
// - Adding columns to table; if column already added, skip it.
// - Adding calculated columns; skip any already added
// - Adding measures; skip any already added
// - Adding hierarchies; skip any already added
//
// 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: In AMO, strings as indexers refer to the ID of the object, not the Name of the object
//
// 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 (perspectiveName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(PerspectiveStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
perspectiveName = perspectiveName.Trim();
tableName = tableName.Trim();
#endregion
// Add table to Perspective
using (AMO.Perspective perspective = tabularDatabase.Cubes[0].Perspectives.GetByName(perspectiveName))
{
perspective.Dimensions.Add(tabularDatabase.Cubes[0].Dimensions.GetByName(tableName).ID);
}
// Add table elements to Perspective
// Adding columns
foreach (string columnName in ColumnsEnumerate(tabularDatabase, tableName))
{
if (!PerspectiveContainsColumn(tabularDatabase, perspectiveName, tableName, columnName))
{
PerspectiveAlterColumnAdd(tabularDatabase, perspectiveName, tableName, columnName, false);
}
}
// Adding calculated columns
foreach (string calculatedColumnName in CalculatedColumnsEnumerate(tabularDatabase, tableName))
{
if (!PerspectiveContainsColumn(tabularDatabase, perspectiveName, tableName, calculatedColumnName))
{
PerspectiveAlterColumnAdd(tabularDatabase, perspectiveName, tableName, calculatedColumnName, false);
}
}
// Adding measures (also adds KPIs, as KPIs are promoted measures)
foreach (string measureName in MeasuresEnumerate(tabularDatabase, tableName))
{
if (!PerspectiveContainsMeasure(tabularDatabase, perspectiveName, measureName))
{
PerspectiveAlterMeasureAdd(tabularDatabase, perspectiveName, tableName, measureName, false);
}
}
// Adding hierarchies
foreach (string hierarchyName in HierarchiesEnumerate(tabularDatabase, tableName))
{
if (!PerspectiveContainsHierarchy(tabularDatabase, perspectiveName, tableName, hierarchyName))
{
PerspectiveAlterHierarchyAdd(tabularDatabase, perspectiveName, tableName, hierarchyName, false);
}
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void RelationshipAdd(AMO.Database tabularDatabase,
string pkTableName,
string pkColumnName,
string foreignTableName,
string foreignColumnName,
bool active = true,
bool updateInstance = true)
{
// Terminology note:
// - the Foreign 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
// ==> Relationship information is stored in the Foreign Dimension object
//
// Major steps in adding a relationship
//
// - Validate required input arguments and other initial preparations
// - Verify relationship doesn't exist before creating it
// - Add relationship to the Foreign dimension object
// - Set relationship to active, if requested (default setting)
//
// Note: In AMO, strings as indexers refer to the ID of the object, not the name
//
#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
// Verify relationship existence
// --> throw exception if relationship already exists
if (RelationshipExists(tabularDatabase, pkTableName, pkColumnName, foreignTableName, foreignColumnName))
{
throw new InvalidOperationException(Resources.RelationshipAlreadyExistInvalidOperationException);
}
// Add relationship
// First, create the INACTIVE relationship definitions in the MultipleValues end of the relationship
string newRelationshipID = Guid.NewGuid().ToString();
AMO.Relationship newRelationship = tabularDatabase.Dimensions[foreignTableId].Relationships.Add(newRelationshipID);
newRelationship.FromRelationshipEnd.DimensionID = foreignTableId;
newRelationship.FromRelationshipEnd.Attributes.Add(foreignColumnId);
newRelationship.FromRelationshipEnd.Multiplicity = AMO.Multiplicity.Many;
newRelationship.FromRelationshipEnd.Role = string.Empty;
newRelationship.ToRelationshipEnd.DimensionID = pkTableId;
newRelationship.ToRelationshipEnd.Attributes.Add(pkColumnId);
newRelationship.ToRelationshipEnd.Multiplicity = AMO.Multiplicity.One;
newRelationship.ToRelationshipEnd.Role = string.Empty;
// Set relationship to active
if (active)
{
RelationshipAlterActive(tabularDatabase, pkTableName, pkColumnName, foreignTableName, foreignColumnName, active, false);
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void PerspectiveAdd(AMO.Database tabularDatabase,
string perspectiveName,
bool updateInstance = true)
{
// Major steps in adding a Perspective to the database
//
// - Validate required input arguments
// - Other Initial preparations
// - Adding Perspective to 'Database'; in this case the database is
// represented by the cube. The different views (perspectives) of
// the database are stored in, the whole or universe, the cube
//
// 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: In AMO, strings as indexers refer to the ID of the object, not the Name of the object
//
// 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 (perspectiveName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(PerspectiveStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
perspectiveName = perspectiveName.Trim();
#endregion
// Add Perspective
using (AMO.Perspective perspective = new AMO.Perspective(perspectiveName, perspectiveName))
{
tabularDatabase.Cubes[0].Perspectives.Add(perspective);
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static AMO.Database TabularDatabaseAdd(AMO.Server server,
string databaseName,
string datasourceOledbConnectionString = null,
string connectionName = null,
int lcid = 0,
string collationName = null,
int dbCompatibilityLevel = 0)
{
// Major steps in creating a tabular database
// - Validate required input arguments
// - Create local copy of database
// - Create connection object in database
// - Create dsv object in database; handled by 'ConnectionAddRelationalDataSource()'
// - Add database to database collection in server
// - Update server instance
//
// 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: There are no validations on the 'datasourceOledbConnectionString'
// The user is responsible for accuracy and usability of the string.
//
// Note: In AMO only well formed OleDb connection strings are supported
// The 'Provider' key word must be the first keyword in the connection string
//
//
// Note: As of SQL Server 2012 Analysis Services, the only supported OleDb providers were:
//
// - "MICROSOFT.JET.OLEDB.4.0" <-- Microsoft OLE DB Provider for Microsoft Jet 4.0
// - "SQLOLEDB" <-- Microsoft SQL OLE DB Provider for SQL Server
// - "SNAC" <-- Microsoft SQL Native Client OLE DB Provider
// - "SQLNCLI" <-- SQL Server Native Client
// - "MSDAORA" <-- Microsoft OLE DB Provider for Oracle
// - "DB2OLEDB" <-- Microsoft OLE DB Provider for DB2
// - "TDOLEDB" <-- OLE DB Provider for Teradata
// - "IFXOLEDBC" <-- IBM Informix OLE DB Provider
// - "SYBASE ASE OLE DB PROVIDER" <-- OLE DB Provider for Sybase Adaptive Server Enterprise (ASE)
// - "ASAPROV" <-- OLE DB Provider for Sybase Adaptive Server Anywhere (ASA)
// - "SYBASE OLEDB PROVIDER" <-- OLE DB Provider for Sybase in version 12
// - "ASEOLEDB" <-- OLE DB Provider for Sybase in version 15
// - "Microsoft SQL Server MPP OLE DB Provider" <-- OLE DB Provider for SQL Server MPP OLE DB Provider
// Validate required input arguments
if (server == null)
{
throw new ArgumentNullException("server");
}
if (databaseName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("databaseName");
}
if (!IsServerCompatibilityLevelCorrect(server))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
if ((dbCompatibilityLevel != 0) && !IsCompatibilityLevelCorrect(dbCompatibilityLevel))
{
throw new ArgumentException(Resources.InvalidCompatibilityLevelOperationException);
}
databaseName = databaseName.Trim();
// Create local copy of database
// In this sample code, only InMemory tabular databases are created
using (AMO.Database newDatabase = server.Databases.Add(databaseName))
{
newDatabase.StorageEngineUsed = AMO.StorageEngineUsed.InMemory;
newDatabase.DirectQueryMode = AMO.DirectQueryMode.InMemory;
newDatabase.CompatibilityLevel = (dbCompatibilityLevel == 0) ? server.DefaultCompatibilityLevel : dbCompatibilityLevel;
if (lcid != 0)
{
newDatabase.Language = lcid;
}
if (!collationName.IsNullOrEmptyOrWhitespace())
{
newDatabase.Collation = collationName;
}
// Create connection object in database
if (!datasourceOledbConnectionString.IsNullOrEmptyOrWhitespace())
{
ConnectionAddRelationalDataSource(newDatabase, datasourceOledbConnectionString, connectionName, updateInstance: false);
}
// Update server instance
newDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
return(newDatabase);
}
}
