public ADOMD.AdomdDataReader ExecuteReader(string qry, ADOMD.AdomdConnection cnn)
{
var cmd = new ADOMD.AdomdCommand(qry, cnn);
ADOMD.AdomdDataReader reader = cmd.ExecuteReader();
return(reader);
}
public AdomdClient.AdomdConnection GetAnalyzeConnection()
{
#if ASQASSAS11 || ASQASSAS12
if (_currentConnectionID != null)
{
throw new ApplicationException("Connection already created");
}
var connection = new AdomdClient.AdomdConnection(ConnectionString);
{
connection.Open();
connection.Disposed += (s, e) => _currentConnectionID = null;
var connectionId = AdomdClientHelper.ExecuteScalar(ConnectionString, CommandDiscoverSessionConnectionId.FormatWith(connection.SessionID));
if (connectionId == null)
{
throw new ApplicationException("session_connection_id is null");
}
_currentConnectionID = Convert.ToString(connectionId);
}
#else
var connection = new AdomdClient.AdomdConnection(ConnectionString);
{
connection.Open();
}
#endif
return(connection);
}
public static void ExecutePrepare(string connectionString, string commandText)
{
#region Argument exceptions
if (commandText == null)
{
throw new ArgumentNullException("commandText");
}
if (connectionString == null)
{
throw new ArgumentNullException("connectionString");
}
#endregion
using (var connection = new AdomdClient.AdomdConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandTimeout = DefaultTimeout;
command.CommandType = CommandType.Text;
command.CommandText = commandText;
command.Prepare();
}
}
}
public static object ExecuteScalar(AdomdClient.AdomdConnection connection, string commandText)
{
#region Argument exceptions
if (commandText == null)
{
throw new ArgumentNullException("commandText");
}
if (connection == null)
{
throw new ArgumentNullException("connection");
}
#endregion
using (var command = connection.CreateCommand())
{
command.CommandTimeout = DefaultTimeout;
command.CommandType = CommandType.Text;
command.CommandText = commandText;
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
return(reader.GetValue(0));
}
}
return(null);
}
}
private static void RunAQuery(object o)
{
ParallelQueryThreadInfo info = null;
try
{
info = (ParallelQueryThreadInfo)o;
Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(info.connectionString);
conn.Open();
try
{
CellSet queryCellset;
Microsoft.AnalysisServices.AdomdClient.AdomdCommand queryCommand = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand();
queryCommand.CommandText = info.query;
queryCommand.Connection = conn;
queryCellset = queryCommand.ExecuteCellSet();
info.returnValue = queryCellset[0].Value.ToString();
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
info.ex = ex;
}
finally
{
info.autoEvent.Set();
}
}
private static void DrillThroughThreadSafe(QueryClient queryClient)
{
Excel.Range rngCell = queryClient.RangeCell;
// create sheet
Excel.Sheets sheets = xlApp.Sheets;
Excel.Worksheet sheet = (Excel.Worksheet)sheets.Add();
// show message to user we are retrieving records
Excel.Range rngHead = sheet.Range["A1"];
int maxDrillThroughRecords = ExcelHelper.GetMaxDrillthroughRecords(rngCell);
rngHead.Value2 = string.Format("Retrieving TOP {0} records",
maxDrillThroughRecords);
// set up connection
var connString = ExcelHelper.GetConnectionString(rngCell);
var commandText = queryClient.GetDAXQuery(connString);
var daxClient = new DaxClient();
var cnnStringBuilder = new TabularConnectionStringBuilder(connString);
var cnn = new ADOMD.AdomdConnection(cnnStringBuilder.StrippedConnectionString);
// retrieve result
var dtResult = daxClient.ExecuteTable(commandText, cnn);
// output result to sheet
Excel.Range rngOut = sheet.Range["A3"];
ExcelHelper.FillRange(dtResult, rngOut);
ExcelHelper.FormatRange(dtResult, rngOut);
rngHead.Value2 = string.Format("Retrieved TOP {0} records", maxDrillThroughRecords);
}
public static string GetCurrentCommand()
{
AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient("Data Source=" + Context.CurrentServerID + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Application Name=ASSP");
try
{
AdomdClient.AdomdRestrictionCollection restrictions = new AdomdClient.AdomdRestrictionCollection();
//a restriction on SESSION_ID causes it to return no rows: http://msdn.microsoft.com/en-us/library/ee301976(v=sql.105).aspx#id253
string sSessionID = Context.CurrentConnection.SessionID;
System.Data.DataSet dataSet = TimeoutUtility.GetSchemaDataSet(conn, "DISCOVER_SESSIONS", restrictions);
if (dataSet != null &&
dataSet.Tables.Count > 0 &&
dataSet.Tables[0].Rows.Count > 0)
{
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
if (string.Compare(sSessionID, Convert.ToString(row["SESSION_ID"]), true) == 0)
{
return(Convert.ToString(row["SESSION_LAST_COMMAND"]));
}
}
throw new Exception("Can't find the current command");
}
else
{
throw new Exception("Can't get the current command");
}
}
finally
{
conn.Close();
}
}
public static object GetDBVal(string dbName)
{
object returnValue;
string measuresText = "";
string dimensionsText = "";
Boolean addcomma = false;
foreach (AMDS.Dimension d in Context.CurrentCube.Dimensions)
{
foreach (AMDS.Hierarchy h in d.AttributeHierarchies)
{
if (d.DimensionType == DimensionTypeEnum.Measure)
{
measuresText = h.CurrentMember.UniqueName;
}
else
//eliminate comparison dimension from the query
if ((h.CurrentMember.UniqueName != h.DefaultMember) && (!h.CurrentMember.UniqueName.Contains("[Comparison Operations]")))
{
if (addcomma == false)
{
addcomma = true;
}
else
{
dimensionsText += ",";
}
dimensionsText += h.CurrentMember.UniqueName;
}
}
}
//if all the dimensions in default members do not compose where clause of the query
if (dimensionsText != "")
{
dimensionsText = " where (" + dimensionsText + ")";
}
string query = " select " + measuresText + " on 0 from [" + Context.CurrentCube.Name + "] " + dimensionsText;
Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Data Source=" + Context.Server.Name + ";Catalog=" + dbName);
conn.Open();
try
{
AMDC.CellSet queryCellset;
Microsoft.AnalysisServices.AdomdClient.AdomdCommand queryCommand = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand();
queryCommand.CommandText = query;
queryCommand.Connection = conn;
queryCellset = queryCommand.ExecuteCellSet();
returnValue = queryCellset[0].Value;
}
finally
{
conn.Close();
}
return(returnValue);
}
public DataTable ExecuteTable(string qry, ADOMD.AdomdConnection cnn)
{
ADOMD.AdomdDataAdapter dataAdapter = new ADOMD.AdomdDataAdapter(qry, cnn);
DataTable tabularResults = new DataTable();
dataAdapter.Fill(tabularResults);
return(tabularResults);
}
public DataTable ExecuteTable(string qry, ADOMD.AdomdConnection cnn)
{
ADOMD.AdomdDataAdapter dataAdapter = new ADOMD.AdomdDataAdapter(qry, cnn);
DataTable tabularResults = new DataTable();
dataAdapter.Fill(tabularResults);
foreach (System.Data.DataColumn column in tabularResults.Columns)
{
column.ColumnName = DaxDrillParser.RemoveBrackets(column.ColumnName);
}
return(tabularResults);
}
/*
* Return results from query
*/
public AdomdDataReader GetQueryResult(string sQuery)
{
try
{
string sConnString = "Data Source=" + sServer + "; Initial Catalog=" + sCatalog;
Microsoft.AnalysisServices.AdomdClient.AdomdConnection objConn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(sConnString);
objConn.Open();
Microsoft.AnalysisServices.AdomdClient.AdomdCommand objCmd = objConn.CreateCommand();
objCmd.CommandText = sQuery;
//Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objReader = objCmd.ExecuteReader();
//Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter objDataAdaptor = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(objCmd);
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objDataReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection);
/*
* try
* {
* for (int i = 0; i < objDataReader.FieldCount; i++)
* {
* Console.Write(objDataReader.GetName(i) + "\t");
* }
* Console.WriteLine();
* while (objDataReader.Read())
* {
* for (int i = 0; i < objDataReader.FieldCount; i++)
* {
* object value = objDataReader.GetValue(i);
* string strValue = (value == null) ?
* string.Empty : value.ToString();
* Console.Write(strValue + "\t");
* }
* Console.WriteLine();
* }
* }
* finally
* {
* objDataReader.Close();
* }
*/
return(objDataReader);
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
return(null);
}
public AdomdConnection(string connectionString, AdomdType type)
{
_type = type;
if (_type == AdomdType.AnalysisServices)
{
_conn = new AsAdomdClient.AdomdConnection(connectionString);
}
else
{
ExcelAdoMdConnections.VoidDelegate f = delegate
{
_connExcel = new ExcelAdomdClient.AdomdConnection(connectionString);
};
f();
}
}
public static DataTable ExecuteDrillthroughAndFixColumns(string sDrillthroughMDX)
{
AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient("Data Source=" + Context.CurrentServerID + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Application Name=ASSP;");
try
{
AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand();
cmd.Connection = conn;
cmd.CommandText = sDrillthroughMDX;
DataTable tbl = new DataTable();
AdomdClient.AdomdDataAdapter adp = new AdomdClient.AdomdDataAdapter(cmd);
TimeoutUtility.FillAdomdDataAdapter(adp, tbl);
Dictionary <string, int> dictColumnNames = new Dictionary <string, int>(StringComparer.InvariantCultureIgnoreCase);
foreach (DataColumn col in tbl.Columns)
{
string sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('.') + 1).Replace("[", "").Replace("]", "");
if (dictColumnNames.ContainsKey(sNewColumnName))
{
dictColumnNames[sNewColumnName]++;
}
else
{
dictColumnNames.Add(sNewColumnName, 1);
}
}
foreach (DataColumn col in tbl.Columns)
{
string sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('.') + 1).Replace("[", "").Replace("]", "");
if (dictColumnNames[sNewColumnName] > 1)
{
sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('[') + 1).Replace("[", "").Replace("]", "").Replace("$", "");
}
if (!tbl.Columns.Contains(sNewColumnName))
{
col.ColumnName = sNewColumnName;
}
}
return(tbl);
}
finally
{
conn.Close();
}
}
public TabularItems.Measure GetMeasureFromDMV(string measureName)
{
if (!server.Connected)
{
throw new InvalidOperationException("You must be connected to the server");
}
var dmv = string.Format(
@"SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [TABLE],[MEASURE_CAPTION] AS [MEASURE],
[MEASURE_IS_VISIBLE]
FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE CUBE_NAME ='Model'
AND MEASURE_CAPTION = '{0}'" ,
measureName);
var daxClient = new DaxHelpers.DaxClient();
System.Data.DataTable dtResult = null;
using (var cnn = new ADOMD.AdomdConnection(connectionString))
{
cnn.Open();
dtResult = daxClient.ExecuteTable(dmv, cnn);
cnn.Close();
}
TabularItems.Measure measure = null;
foreach (System.Data.DataRow drow in dtResult.Rows)
{
if (Convert.ToString(drow["MEASURE"]) == measureName)
{
measure = new TabularItems.Measure(Convert.ToString(drow["TABLE"]), measureName);
break;
}
}
if (measure == null)
{
throw new InvalidOperationException("Measure " + measureName + " was not found in database " + this.databaseName);
}
return(measure);
}
public void ProcessCurrent(XPObjectSpace objSpace)
{
var prevSshot = CashFlowHelper.GetPreviousSnapshot(objSpace);
if (prevSshot == null)
{
ProcessCurrentWithoutPrev();
_LastReturnMessage = "Processed without previous snapshot is defined in XPO.";
return;
}
var dax = new DaxClient();
string connectionString = AppConfig.SsasConnectionString;
var cnn = new ADOMD.AdomdConnection(connectionString);
cnn.Open();
var reader = dax.ExecuteReader(@"EVALUATE
ROW
(
""Result"",
CALCULATE (
COUNTROWS( CashFlow ),
CashFlow[Snapshot OID] = ""{OID}""
)
)".Replace("{OID}", "{" + prevSshot.Oid + "}"), cnn);
reader.Read();
var prevRowCount = reader.GetValue(0);
if (prevRowCount == null)
{
ProcessCurrentWithPrev();
_LastReturnMessage = "Processed previous snapshot as it currently does not exist in SSAS.";
}
else
{
ProcessCurrentWithoutPrev();
_LastReturnMessage = "Did not process previous snapshot because previous snapshot already exists in SSAS.";
}
}
public static DataTable ExecuteDataTable(string connectionString, string commandText)
{
#region Argument exceptions
if (commandText == null)
{
throw new ArgumentNullException("commandText");
}
if (connectionString == null)
{
throw new ArgumentNullException("connectionString");
}
#endregion
using (var connection = new AdomdClient.AdomdConnection(connectionString))
{
connection.Open();
return(ExecuteDataTable(connection, CancellationToken.None, commandText));
}
}
public void GetTable_SSAS2014()
{
var commandText = "EVALUATE TOPN( 100, CashFlow )";
string serverName = "FINSERV01";
string connectionString = string.Format(
"Integrated Security=SSPI;Persist Security Info=True;Initial Catalog={1};Data Source={0};", serverName, "CashFlow");
var cnn = new ADOMD.AdomdConnection(connectionString);
var daxClient = new DaxClient();
var dtResult = daxClient.ExecuteTable(commandText, cnn);
var columnNames = "";
foreach (System.Data.DataColumn column in dtResult.Columns)
{
if (string.IsNullOrEmpty(columnNames))
{
columnNames += ",";
}
columnNames += column.ColumnName;
}
Console.WriteLine(columnNames);
foreach (System.Data.DataRow row in dtResult.Rows)
{
var rowText = "";
foreach (object item in row.ItemArray)
{
if (!string.IsNullOrEmpty(rowText))
{
rowText += ",";
}
rowText += Convert.ToString(item);
}
Console.WriteLine(rowText);
}
}
private static AnalyzerTaskResult InternalAnalyzeAsync(ProcedureContext procedureContext, AdomdClient.AdomdConnection connection)
{
var queryResult = default(DataTable);
var profilerResult = default(ProfilerResult);
var performanceResult = default(PerformanceResult);
using (var collectorsSynchronizer = CollectorsSynchronizer.Create(procedureContext.CancellationToken))
using (var performanceTask = PerformanceCollector.StartAsync(procedureContext, collectorsSynchronizer))
{
try
{
if (!performanceTask.IsFaulted && !procedureContext.IsCancellationRequested)
{
#region profiler collector
using (var profilerCancellation = CancellationTokenSource.CreateLinkedTokenSource(procedureContext.CancellationToken))
using (var profilerTask = ProfilerCollector.StartAsync(procedureContext, collectorsSynchronizer, profilerCancellation.Token))
{
try
{
if (!profilerTask.IsFaulted && !profilerCancellation.IsCancellationRequested)
{
try
{
queryResult = AdomdClientHelper.ExecuteDataTable(
connection,
procedureContext.CancellationToken,
procedureContext.Statement,
activityID: procedureContext.ClientActivityID,
rowsLimit: procedureContext.QueryResultRowLimit,
beforeStart: () => EventsNotifier.Instance.Notify(ProcedureEvents.ProcedureAnalyzeStepExecuteStatement),
beforeWait: () => EventsNotifier.Instance.Notify(ProcedureEvents.ProcedureAnalyzeStepPendingOutcome)
);
}
finally
{
profilerCancellation.Cancel();
}
}
}
finally
{
profilerResult = profilerTask.Result;
}
}
#endregion
}
}
finally
{
performanceResult = performanceTask.Result;
}
}
return(AnalyzerTaskResult.Create(queryResult, profilerResult, performanceResult));
}
public static DataTable ExecuteDataTable(AdomdClient.AdomdConnection connection, CancellationToken cancellationToken, string commandText, Guid activityID = default(Guid), int rowsLimit = Int32.MaxValue, Action beforeStart = null, Action beforeWait = null)
{
#region Argument exceptions
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (commandText == null)
{
throw new ArgumentNullException("commandText");
}
if (cancellationToken == null)
{
throw new ArgumentNullException("cancellationToken");
}
#endregion
var table = new DataTable();
using (var command = connection.CreateCommand())
{
command.CommandTimeout = DefaultTimeout;
command.CommandType = CommandType.Text;
command.CommandText = commandText;
if (activityID != Guid.Empty)
{
command.ActivityID = activityID;
}
beforeStart?.Invoke();
using (var task = Task.Factory.StartNew(() =>
{
if (rowsLimit == 0)
{
command.ExecuteNonQuery();
}
else
{
CellsetToDataTable(command.ExecuteCellSet(), table, rowsLimit);
}
return(table);
//using (var adapter = new AdomdClient.AdomdDataAdapter(command))
// adapter.Fill(table);
//return table;
},
cancellationToken, TaskCreationOptions.LongRunning | TaskCreationOptions.AttachedToParent, TaskScheduler.Default))
{
try
{
beforeWait?.Invoke();
task.Wait(cancellationToken);
}
catch (OperationCanceledException)
{
command.Cancel();
while (!task.IsCompleted)
{
Thread.Sleep(50);
}
throw;
}
finally
{
table = task.Result;
}
}
}
return(table);
}
private static void CreateStringDistinctCountPartitions(string CubeName, string MeasureGroupName, string SetString, string PartitionGrouperExpressionString, string[] BoundaryValues)
{
int NumSubPartitions = BoundaryValues.Length + 1;
if (AdomdServer.Context.ExecuteForPrepare) return;
AdomdClient.Set s = null;
AdomdClient.AdomdConnection conn = new AdomdClient.AdomdConnection("Data Source=" + AdomdServer.Context.CurrentServerID + ";Initial Catalog=" + AdomdServer.Context.CurrentDatabaseName);
conn.ShowHiddenObjects = true; //ShowHiddenObjects=true allows you to see properties (like member.ParentLevel) of dimension attributes which aren't visible: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265114
conn.Open();
Server server = new Server();
server.Connect("*"); //connect to the current session... important to connect this way or else you will get a deadlock when you go to save the partition changes
AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel
try
{
AdomdServer.Context.TraceEvent(0, 0, "Retrieving Template Partition");
Database db = server.Databases.GetByName(AdomdServer.Context.CurrentDatabaseName);
Cube cube = db.Cubes.GetByName(CubeName);
MeasureGroup mg = cube.MeasureGroups.GetByName(MeasureGroupName);
Partition template = mg.Partitions[0];
if (cube.State == AnalysisState.Unprocessed) throw new Exception("The cube [" + cube.Name + "] is unprocessed currently. Run ProcessStructure on it before partitioning it with ASSP.");
////////////////////////////////////////////////////////////////
//Distinct Count stuff
AdomdServer.Context.TraceEvent(0, 0, "Calculating min/max distinct count value");
Measure distinctMeasure = null;
foreach (Measure m in mg.Measures)
{
if (m.AggregateFunction == AggregationFunction.DistinctCount)
{
if (distinctMeasure != null) throw new Exception("CreateStringDistinctCountPartitions does not support more than one distinct count measure on measure group " + mg.Name + ".");
distinctMeasure = m;
}
}
if (distinctMeasure == null) throw new Exception("Could not find a distinct count measure in measure group " + mg.Name + ".");
ColumnBinding distinctColumnBinding = distinctMeasure.Source.Source as ColumnBinding;
if (distinctColumnBinding == null) throw new Exception("Distinct count measure " + distinctMeasure.Name + " was not bound to a column.");
AdomdServer.Context.TraceEvent(0, 0, "Boundary values: " + String.Join(", ", BoundaryValues));
AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel
//////////////////////////////////////////////////////////////////
AdomdServer.Context.TraceEvent(0, 0, "Resolving Set"); //will show up under the User Defined trace event which is selected by default
AdomdClient.CellSet cs;
AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand();
cmd.Connection = conn;
if (String.IsNullOrEmpty(PartitionGrouperExpressionString))
{
cmd.CommandText = "select {} on 0, {" + SetString + "} on 1 "
+ "from [" + CubeName + "]";
try
{
cs = cmd.ExecuteCellSet();
}
catch (Exception ex)
{
throw new Exception("The set specified was not valid: " + ex.Message);
}
}
else
{
cmd.CommandText = "with member [Measures].[_ASSP_PartitionGrouper_] as " + PartitionGrouperExpressionString + " "
+ "select [Measures].[_ASSP_PartitionGrouper_] on 0, "
+ "{" + SetString + "} on 1 "
+ "from [" + CubeName + "]";
try
{
cs = cmd.ExecuteCellSet();
}
catch (Exception ex)
{
throw new Exception("The set or partition grouper specified was not valid: " + ex.Message);
}
}
s = cs.Axes[1].Set;
AdomdServer.Context.TraceEvent(0, 0, "Determining Partition Scheme");
Dictionary<string, PartitionMetadata> dictPartitions = new Dictionary<string, PartitionMetadata>();
List<string> listPartitionNames = new List<string>(dictPartitions.Count);
for (int iTuple = 0; iTuple < s.Tuples.Count; iTuple++)
{
AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel
AdomdClient.Tuple t = s.Tuples[iTuple];
string tostring = t.ToString();
string sTupleUniqueName = GetTupleUniqueName(t);
string sTupleName = GetTupleName(t);
string sOriginalGrouper = sTupleUniqueName;
if (!String.IsNullOrEmpty(PartitionGrouperExpressionString))
{
//if a partition grouper has been specified, then group by it
sTupleName = sOriginalGrouper = cs.Cells[0, iTuple].Value.ToString();
}
for (int iSubPartition = 1; iSubPartition <= NumSubPartitions; iSubPartition++)
{
string sGrouper = sOriginalGrouper + " - DistinctCountSegment" + iSubPartition;
if (!dictPartitions.ContainsKey(sGrouper))
{
string sPartitionName = mg.Name + " - " + sTupleName + " - DistinctCountSegment" + iSubPartition;
sPartitionName = sPartitionName.Trim();
if (String.IsNullOrEmpty(PartitionGrouperExpressionString))
{
//make sure partition name is unique
int i = 1;
while (listPartitionNames.Contains(sPartitionName))
{
sPartitionName = mg.Name + " - " + sTupleName + " - DistinctCountSegment" + iSubPartition + " - " + (++i);
}
}
dictPartitions.Add(sGrouper, new PartitionMetadata(sPartitionName, t));
listPartitionNames.Add(sPartitionName);
DataColumn dc = distinctMeasure.ParentCube.DataSourceView.Schema.Tables[distinctColumnBinding.TableID].Columns[distinctColumnBinding.ColumnID];
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
dictPartitions[sGrouper].DistinctCountColumn = "[" + distinctMeasure.ParentCube.DataSourceView.Schema.Tables[distinctColumnBinding.TableID].ExtendedProperties["FriendlyName"].ToString() + "].[" + GetColumnName(dc) + "]";
}
else
{
dictPartitions[sGrouper].DistinctCountColumn = dc.ExtendedProperties["ComputedColumnExpression"].ToString();
}
dictPartitions[sGrouper].DistinctCountRangeEndInclusive = false;
if (iSubPartition > 1)
dictPartitions[sGrouper].DistinctCountRangeStart = "'" + BoundaryValues[iSubPartition - 2].Replace("'", "''") + "'";
if (iSubPartition < NumSubPartitions)
dictPartitions[sGrouper].DistinctCountRangeEnd = "'" + BoundaryValues[iSubPartition - 1].Replace("'", "''") + "'";
}
else
{
dictPartitions[sGrouper].tuples.Add(t);
}
}
}
//remove all existing partitions except template
for (int iPartition = mg.Partitions.Count - 1; iPartition > 0; iPartition--)
{
mg.Partitions.RemoveAt(iPartition);
}
bool bNeedToDeleteTemplate = true;
foreach (PartitionMetadata pm in dictPartitions.Values)
{
AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel
AdomdServer.Context.TraceEvent(0, 0, "Building Partition: " + pm.PartitionName);
if (template.ID == pm.PartitionName)
{
pm.Partition = template;
bNeedToDeleteTemplate = false;
pm.Partition.Process(ProcessType.ProcessClear); //unprocess it
}
else
{
pm.Partition = template.Clone();
}
pm.Partition.Slice = pm.PartitionSlice;
pm.Partition.Name = pm.PartitionName;
pm.Partition.ID = pm.PartitionName;
if (template.ID != pm.PartitionName) mg.Partitions.Add(pm.Partition);
//if we're only building one partition, it must be the All member
if (s.Tuples.Count == 1) pm.TupleMustBeOnlyAllMembers = true;
string sQuery = "";
sQuery = pm.OldQueryDefinition;
string sWhereClause = pm.NewPartitionWhereClause;
sQuery += "\r\n" + sWhereClause;
pm.Partition.Source = new QueryBinding(pm.Partition.DataSource.ID, sQuery);
}
if (bNeedToDeleteTemplate) mg.Partitions.Remove(template);
AdomdServer.Context.TraceEvent(0, 0, "Saving changes");
mg.Update(UpdateOptions.ExpandFull);
AdomdServer.Context.TraceEvent(0, 0, "Done creating partitions");
}
finally
{
try
{
conn.Close();
}
catch { }
try
{
server.Disconnect();
}
catch { }
}
}
public static void CreatePartitions(string CubeName, string MeasureGroupName, string SetString, string PartitionGrouperExpressionString)
{
if (AdomdServer.Context.ExecuteForPrepare) return;
AdomdClient.Set s = null;
AdomdClient.AdomdConnection conn = new AdomdClient.AdomdConnection("Data Source=" + AdomdServer.Context.CurrentServerID + ";Initial Catalog=" + AdomdServer.Context.CurrentDatabaseName);
conn.ShowHiddenObjects = true; //ShowHiddenObjects=true allows you to see properties (like member.ParentLevel) of dimension attributes which aren't visible: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265114
conn.Open();
Server server = new Server();
server.Connect("*"); //connect to the current session... important to connect this way or else you will get a deadlock when you go to save the partition changes
AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel
try
{
AdomdServer.Context.TraceEvent(0, 0, "Retrieving Template Partition");
Database db = server.Databases.GetByName(AdomdServer.Context.CurrentDatabaseName);
Cube cube = db.Cubes.GetByName(CubeName);
MeasureGroup mg = cube.MeasureGroups.GetByName(MeasureGroupName);
Partition template = mg.Partitions[0];
if (cube.State == AnalysisState.Unprocessed) throw new Exception("The cube [" + cube.Name + "] is unprocessed currently. Run ProcessStructure on it before partitioning it with ASSP.");
AdomdServer.Context.TraceEvent(0, 0, "Resolving Set"); //will show up under the User Defined trace event which is selected by default
AdomdClient.CellSet cs;
AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand();
cmd.Connection = conn;
if (String.IsNullOrEmpty(PartitionGrouperExpressionString))
{
cmd.CommandText = "select {} on 0, {" + SetString + "} on 1 "
+ "from [" + CubeName + "]";
try
{
cs = cmd.ExecuteCellSet();
}
catch (Exception ex)
{
throw new Exception("The set specified was not valid: " + ex.Message);
}
}
else
{
cmd.CommandText = "with member [Measures].[_ASSP_PartitionGrouper_] as " + PartitionGrouperExpressionString + " "
+ "select [Measures].[_ASSP_PartitionGrouper_] on 0, "
+ "{" + SetString + "} on 1 "
+ "from [" + CubeName + "]";
try
{
cs = cmd.ExecuteCellSet();
}
catch (Exception ex)
{
throw new Exception("The set or partition grouper specified was not valid: " + ex.Message);
}
}
s = cs.Axes[1].Set;
AdomdServer.Context.TraceEvent(0, 0, "Determining Partition Scheme");
Dictionary<string, PartitionMetadata> dictPartitions = new Dictionary<string, PartitionMetadata>();
List<string> listPartitionNames = new List<string>(dictPartitions.Count);
for (int iTuple = 0; iTuple < s.Tuples.Count; iTuple++)
{
AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel
AdomdClient.Tuple t = s.Tuples[iTuple];
string tostring = t.ToString();
string sTupleUniqueName = GetTupleUniqueName(t);
string sTupleName = GetTupleName(t);
string sGrouper = sTupleUniqueName;
if (!String.IsNullOrEmpty(PartitionGrouperExpressionString))
{
//if a partition grouper has been specified, then group by it
sTupleName = sGrouper = cs.Cells[0, iTuple].Value.ToString();
}
if (!dictPartitions.ContainsKey(sGrouper))
{
string sPartitionName = mg.Name + " - " + sTupleName;
sPartitionName = sPartitionName.Trim();
if (String.IsNullOrEmpty(PartitionGrouperExpressionString))
{
//make sure partition name is unique
int i = 1;
while (listPartitionNames.Contains(sPartitionName))
{
sPartitionName = mg.Name + " - " + sTupleName + " " + (++i);
}
}
dictPartitions.Add(sGrouper, new PartitionMetadata(sPartitionName, t));
listPartitionNames.Add(sPartitionName);
}
else
{
dictPartitions[sGrouper].tuples.Add(t);
}
}
//remove all existing partitions except template
for (int iPartition = mg.Partitions.Count - 1; iPartition > 0; iPartition--)
{
mg.Partitions.RemoveAt(iPartition);
}
bool bNeedToDeleteTemplate = true;
foreach (PartitionMetadata pm in dictPartitions.Values)
{
AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel
AdomdServer.Context.TraceEvent(0, 0, "Building Partition: " + pm.PartitionName);
if (template.ID == pm.PartitionName)
{
pm.Partition = template;
bNeedToDeleteTemplate = false;
pm.Partition.Process(ProcessType.ProcessClear); //unprocess it
AdomdServer.Context.TraceEvent(0, 0, "ProcessClear partition");
}
else
{
pm.Partition = template.Clone();
AdomdServer.Context.TraceEvent(0, 0, "cloned partition");
}
pm.Partition.Slice = pm.PartitionSlice;
pm.Partition.Name = pm.PartitionName;
pm.Partition.ID = pm.PartitionName;
if (template.ID != pm.PartitionName) mg.Partitions.Add(pm.Partition);
//if we're only building one partition, it must be the All member
if (s.Tuples.Count == 1) pm.TupleMustBeOnlyAllMembers = true;
string sQuery = "";
sQuery = pm.OldQueryDefinition;
string sWhereClause = pm.NewPartitionWhereClause;
sQuery += "\r\n" + sWhereClause;
pm.Partition.Source = new QueryBinding(pm.Partition.DataSource.ID, sQuery);
}
if (bNeedToDeleteTemplate) mg.Partitions.Remove(template);
AdomdServer.Context.TraceEvent(0, 0, "Saving changes");
mg.Update(UpdateOptions.ExpandFull);
AdomdServer.Context.TraceEvent(0, 0, "Done creating partitions");
}
catch (Exception ex)
{
AdomdServer.Context.TraceEvent(0, 0, "error: " + ex.Message + " - " + ex.StackTrace);
throw ex;
}
finally
{
try
{
conn.Close();
}
catch { }
try
{
server.Disconnect();
}
catch { }
}
}
public static void CreateDistinctCountPartitions(string CubeName, string MeasureGroupName, string SetString, string PartitionGrouperExpressionString, int NumSubPartitions)
{
if (AdomdServer.Context.ExecuteForPrepare) return;
AdomdClient.Set s = null;
AdomdClient.AdomdConnection conn = new AdomdClient.AdomdConnection("Data Source=" + AdomdServer.Context.CurrentServerID + ";Initial Catalog=" + AdomdServer.Context.CurrentDatabaseName);
conn.ShowHiddenObjects = true; //ShowHiddenObjects=true allows you to see properties (like member.ParentLevel) of dimension attributes which aren't visible: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265114
conn.Open();
Server server = new Server();
server.Connect("*"); //connect to the current session... important to connect this way or else you will get a deadlock when you go to save the partition changes
AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel
try
{
AdomdServer.Context.TraceEvent(0, 0, "Retrieving Template Partition");
Database db = server.Databases.GetByName(AdomdServer.Context.CurrentDatabaseName);
Cube cube = db.Cubes.GetByName(CubeName);
MeasureGroup mg = cube.MeasureGroups.GetByName(MeasureGroupName);
Partition template = mg.Partitions[0];
if (cube.State == AnalysisState.Unprocessed) throw new Exception("The cube [" + cube.Name + "] is unprocessed currently. Run ProcessStructure on it before partitioning it with ASSP.");
////////////////////////////////////////////////////////////////
//Distinct Count stuff
AdomdServer.Context.TraceEvent(0, 0, "Calculating min/max distinct count value");
Measure distinctMeasure = null;
foreach (Measure m in mg.Measures)
{
if (m.AggregateFunction == AggregationFunction.DistinctCount)
{
if (distinctMeasure != null) throw new Exception("CreateDistinctCountPartitions does not support more than one distinct count measure on measure group " + mg.Name + ".");
distinctMeasure = m;
}
}
if (distinctMeasure == null) throw new Exception("Could not find a distinct count measure in measure group " + mg.Name + ".");
ColumnBinding distinctColumnBinding = distinctMeasure.Source.Source as ColumnBinding;
if (distinctColumnBinding == null) throw new Exception("Distinct count measure " + distinctMeasure.Name + " was not bound to a column.");
MeasureGroupAttribute distinctMGDimensionAttribute = null;
foreach (MeasureGroupDimension mgDim in mg.Dimensions)
{
if (mgDim is RegularMeasureGroupDimension)
{
MeasureGroupAttribute mga = PartitionMetadata.GetGranularityAttribute(mgDim);
if (mga.KeyColumns.Count == 1)
{
ColumnBinding cb = mga.KeyColumns[0].Source as ColumnBinding;
if (cb.ColumnID == distinctColumnBinding.ColumnID && cb.TableID == distinctColumnBinding.TableID)
{
distinctMGDimensionAttribute = mga;
break;
}
}
AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel
}
}
if (distinctMGDimensionAttribute == null) throw new Exception("Couldn't find a dimension joined to this measure group on the distinct count column [" + distinctColumnBinding.TableID + "].[" + distinctColumnBinding.ColumnID + "].");
if (distinctMGDimensionAttribute.KeyColumns[0].DataType != System.Data.OleDb.OleDbType.Integer && distinctMGDimensionAttribute.KeyColumns[0].DataType != System.Data.OleDb.OleDbType.BigInt && distinctMGDimensionAttribute.KeyColumns[0].DataType != System.Data.OleDb.OleDbType.UnsignedInt && distinctMGDimensionAttribute.KeyColumns[0].DataType != System.Data.OleDb.OleDbType.UnsignedBigInt)
throw new Exception("ASSP encountered a problem partitioning on distinct count column [" + distinctColumnBinding.TableID + "].[" + distinctColumnBinding.ColumnID + "] as ASSP only allows partitioning on a distinct count measure of data type Int or BigInt (or the unsigned equivalents) with CreateDistinctCountPartitions. Use CreateStringDistinctCountPartitions which allows boundary values to be passed in.");
string sDistinctCountCubeDim = "[" + distinctMGDimensionAttribute.Parent.CubeDimension.Name + "]";
string sDistinctCountAttribute = sDistinctCountCubeDim + ".[" + distinctMGDimensionAttribute.Attribute.Name + "]";
string sDistinctCountLevel = sDistinctCountAttribute + ".[" + distinctMGDimensionAttribute.Attribute.Name + "]";
AdomdClient.CellSet distinctKeysCS;
AdomdClient.AdomdCommand distinctKeysCmd = new AdomdClient.AdomdCommand();
distinctKeysCmd.Connection = conn;
//doing a min and max across 20 million members took 14 minutes, so only scan the top and bottom million should should give us a pretty good estimate in a reasonable time unless the sorting is completely strange
distinctKeysCmd.CommandText = @"
with
member [Measures].[_MinKey_] as Min(Head(" + sDistinctCountLevel + ".Members,1000000), " + sDistinctCountAttribute + @".CurrentMember.Properties(""Key0"", TYPED))
member [Measures].[_MaxKey_] as Max(Tail(" + sDistinctCountLevel + ".Members,1000000), " + sDistinctCountAttribute + @".CurrentMember.Properties(""Key0"", TYPED))
select {[Measures].[_MinKey_], [Measures].[_MaxKey_]} on 0
from [" + CubeName + @"]
";
try
{
distinctKeysCS = distinctKeysCmd.ExecuteCellSet();
}
catch (Exception ex)
{
throw new Exception("Could not retrieve the min and max key value for the dimension matching the distinct count measure: " + ex.Message);
}
long lngMinDistinctValue = Convert.ToInt64(distinctKeysCS.Cells[0].Value);
long lngMaxDistinctValue = Convert.ToInt64(distinctKeysCS.Cells[1].Value);
AdomdServer.Context.TraceEvent(0, 0, "Min distinct value: " + lngMinDistinctValue);
AdomdServer.Context.TraceEvent(0, 0, "Max distinct value: " + lngMaxDistinctValue);
AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel
//////////////////////////////////////////////////////////////////
AdomdServer.Context.TraceEvent(0, 0, "Resolving Set"); //will show up under the User Defined trace event which is selected by default
AdomdClient.CellSet cs;
AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand();
cmd.Connection = conn;
if (String.IsNullOrEmpty(PartitionGrouperExpressionString))
{
cmd.CommandText = "select {} on 0, {" + SetString + "} on 1 "
+ "from [" + CubeName + "]";
try
{
cs = cmd.ExecuteCellSet();
}
catch (Exception ex)
{
throw new Exception("The set specified was not valid: " + ex.Message);
}
}
else
{
cmd.CommandText = "with member [Measures].[_ASSP_PartitionGrouper_] as " + PartitionGrouperExpressionString + " "
+ "select [Measures].[_ASSP_PartitionGrouper_] on 0, "
+ "{" + SetString + "} on 1 "
+ "from [" + CubeName + "]";
try
{
cs = cmd.ExecuteCellSet();
}
catch (Exception ex)
{
throw new Exception("The set or partition grouper specified was not valid: " + ex.Message);
}
}
s = cs.Axes[1].Set;
AdomdServer.Context.TraceEvent(0, 0, "Determining Partition Scheme");
Dictionary<string, PartitionMetadata> dictPartitions = new Dictionary<string, PartitionMetadata>();
List<string> listPartitionNames = new List<string>(dictPartitions.Count);
for (int iTuple = 0; iTuple < s.Tuples.Count; iTuple++)
{
AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel
AdomdClient.Tuple t = s.Tuples[iTuple];
string tostring = t.ToString();
string sTupleUniqueName = GetTupleUniqueName(t);
string sTupleName = GetTupleName(t);
string sOriginalGrouper = sTupleUniqueName;
if (!String.IsNullOrEmpty(PartitionGrouperExpressionString))
{
//if a partition grouper has been specified, then group by it
sTupleName = sOriginalGrouper = cs.Cells[0, iTuple].Value.ToString();
}
for (int iSubPartition = 1; iSubPartition <= NumSubPartitions; iSubPartition++)
{
string sGrouper = sOriginalGrouper + " - DistinctCountSegment" + iSubPartition;
if (!dictPartitions.ContainsKey(sGrouper))
{
string sPartitionName = mg.Name + " - " + sTupleName + " - DistinctCountSegment" + iSubPartition;
sPartitionName = sPartitionName.Trim();
if (String.IsNullOrEmpty(PartitionGrouperExpressionString))
{
//make sure partition name is unique
int i = 1;
while (listPartitionNames.Contains(sPartitionName))
{
sPartitionName = mg.Name + " - " + sTupleName + " - DistinctCountSegment" + iSubPartition + " - " + (++i);
}
}
dictPartitions.Add(sGrouper, new PartitionMetadata(sPartitionName, t));
listPartitionNames.Add(sPartitionName);
DataColumn dc = distinctMeasure.ParentCube.DataSourceView.Schema.Tables[distinctColumnBinding.TableID].Columns[distinctColumnBinding.ColumnID];
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
dictPartitions[sGrouper].DistinctCountColumn = "[" + distinctMeasure.ParentCube.DataSourceView.Schema.Tables[distinctColumnBinding.TableID].ExtendedProperties["FriendlyName"].ToString() + "].[" + GetColumnName(dc) + "]";
}
else
{
dictPartitions[sGrouper].DistinctCountColumn = dc.ExtendedProperties["ComputedColumnExpression"].ToString();
}
if (iSubPartition > 1)
dictPartitions[sGrouper].DistinctCountRangeStart = (((lngMaxDistinctValue - lngMinDistinctValue) / NumSubPartitions) * (iSubPartition - 1) + lngMinDistinctValue + 1).ToString();
if (iSubPartition < NumSubPartitions)
dictPartitions[sGrouper].DistinctCountRangeEnd = (((lngMaxDistinctValue - lngMinDistinctValue) / NumSubPartitions) * iSubPartition + lngMinDistinctValue).ToString();
}
else
{
dictPartitions[sGrouper].tuples.Add(t);
}
}
}
//remove all existing partitions except template
for (int iPartition = mg.Partitions.Count - 1; iPartition > 0; iPartition--)
{
mg.Partitions.RemoveAt(iPartition);
}
bool bNeedToDeleteTemplate = true;
foreach (PartitionMetadata pm in dictPartitions.Values)
{
AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel
AdomdServer.Context.TraceEvent(0, 0, "Building Partition: " + pm.PartitionName);
if (template.ID == pm.PartitionName)
{
pm.Partition = template;
bNeedToDeleteTemplate = false;
pm.Partition.Process(ProcessType.ProcessClear); //unprocess it
}
else
{
pm.Partition = template.Clone();
}
pm.Partition.Slice = pm.PartitionSlice;
pm.Partition.Name = pm.PartitionName;
pm.Partition.ID = pm.PartitionName;
if (template.ID != pm.PartitionName) mg.Partitions.Add(pm.Partition);
//if we're only building one partition, it must be the All member
if (s.Tuples.Count == 1) pm.TupleMustBeOnlyAllMembers = true;
string sQuery = "";
sQuery = pm.OldQueryDefinition;
string sWhereClause = pm.NewPartitionWhereClause;
sQuery += "\r\n" + sWhereClause;
pm.Partition.Source = new QueryBinding(pm.Partition.DataSource.ID, sQuery);
}
if (bNeedToDeleteTemplate) mg.Partitions.Remove(template);
AdomdServer.Context.TraceEvent(0, 0, "Saving changes");
mg.Update(UpdateOptions.ExpandFull);
AdomdServer.Context.TraceEvent(0, 0, "Done creating partitions");
}
finally
{
try
{
conn.Close();
}
catch { }
try
{
server.Disconnect();
}
catch { }
}
}
//as a workaround to a bug: https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=260636&siteid=68&wa=wsignin1.0
private static object[] GetMemberKeys(AdomdClient.Member m, int iNumKeys)
{
AdomdClient.AdomdConnection conn = new AdomdClient.AdomdConnection("Data Source=" + AdomdServer.Context.CurrentServerID + ";Initial Catalog=" + AdomdServer.Context.CurrentDatabaseName);
conn.ShowHiddenObjects = true; //ShowHiddenObjects=true allows you to see properties (like member.ParentLevel) of dimension attributes which aren't visible: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265114
conn.Open();
try
{
StringBuilder sCmd = new StringBuilder();
sCmd.AppendLine("with");
for (int i = 0; i < iNumKeys; i++)
{
sCmd.AppendLine("member [Measures].[_ASSP_MemberKey" + i + "] as " + m.ParentLevel.ParentHierarchy.UniqueName + ".CurrentMember.Properties('Key" + i + "')");
}
sCmd.Append("select {");
for (int i = 0; i < iNumKeys; i++)
{
if (i > 0) sCmd.Append(", ");
sCmd.Append("[Measures].[_ASSP_MemberKey" + i + "]");
}
sCmd.AppendLine("} on 0,");
sCmd.Append(m.UniqueName).AppendLine(" on 1");
sCmd.Append("from [").Append(m.ParentLevel.ParentHierarchy.ParentDimension.ParentCube.Name).AppendLine("]");
AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand();
cmd.Connection = conn;
cmd.CommandText = sCmd.ToString();
AdomdClient.CellSet cs = cmd.ExecuteCellSet();
List<object> keys = new List<object>(cs.Cells.Count);
foreach (AdomdClient.Cell c in cs.Cells)
{
keys.Add(c.CellProperties["FORMATTED_VALUE"].Value); //the FORMATTED_VALUE will be null if it should be null
}
return keys.ToArray();
}
finally
{
try
{
conn.Close();
}
catch { }
}
}
public AdomdConnection(AsAdomdClient.AdomdConnection obj)
{
_type = AdomdType.AnalysisServices;
_conn = obj;
}
public static DataTable ExecuteDrillthroughAndTranslateColumns(string sDrillthroughMDX)
{
Regex columnNameRegex = new Regex(@"\[(?<cube>[^]]*)]\.\[(?<level>[^]]*)]", RegexOptions.Compiled);
string connStr = "Data Source=" + Context.CurrentServerID + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Application Name=ASSP;Locale Identifier=" + Context.CurrentConnection.ClientCulture.LCID;
AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient(connStr);
Context.TraceEvent(999, 0, string.Format("ExecuteDrillthroughAndTranslateColumns ConnectionString: {0}", connStr));
try
{
Dictionary <string, string> translations = new Dictionary <string, string>();
// get level names
var resColl = new AdomdClient.AdomdRestrictionCollection();
resColl.Add("CUBE_SOURCE", "3"); // dimensions
resColl.Add("LEVEL_VISIBILITY", "3"); // visible and non-visible
resColl.Add("CUBE_NAME", Context.CurrentCube); // visible and non-visible
var dsLevels = conn.GetSchemaDataSet("MDSCHEMA_LEVELS", resColl);
foreach (DataRow dr in dsLevels.Tables[0].Rows)
{
var sColName = string.Format("[${0}.[{1}]", dr["DIMENSION_UNIQUE_NAME"].ToString().Substring(1), dr["LEVEL_NAME"].ToString());
if (!translations.ContainsKey(sColName))
{
translations.Add(sColName, dr["LEVEL_CAPTION"].ToString());
}
}
// get measure names
resColl.Clear();
resColl.Add("CUBE_NAME", Context.CurrentCube);
resColl.Add("MEASURE_VISIBILITY", 3); // visible and non-visible
var dsMeasures = conn.GetSchemaDataSet("MDSCHEMA_MEASURES", resColl);
foreach (DataRow dr in dsMeasures.Tables[0].Rows)
{
if (!translations.ContainsKey(string.Format("[{0}].[{1}]", dr["MEASUREGROUP_NAME"].ToString(), dr["MEASURE_NAME"].ToString())))
{
translations.Add(string.Format("[{0}].[{1}]", dr["MEASUREGROUP_NAME"].ToString(), dr["MEASURE_NAME"].ToString()), dr["MEASURE_CAPTION"].ToString());
}
}
// get dimension names
resColl.Clear();
resColl.Add("CUBE_NAME", Context.CurrentCube);
var dsDims = conn.GetSchemaDataSet("MDSCHEMA_DIMENSIONS", resColl);
AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand();
cmd.Connection = conn;
cmd.CommandText = sDrillthroughMDX;
DataTable tbl = new DataTable();
AdomdClient.AdomdDataAdapter adp = new AdomdClient.AdomdDataAdapter(cmd);
TimeoutUtility.FillAdomdDataAdapter(adp, tbl);
// loop through the columns looking for duplicate translation names
Dictionary <string, int> dictColumnNames = new Dictionary <string, int>(StringComparer.InvariantCultureIgnoreCase);
foreach (DataColumn col in tbl.Columns)
{
var colKey = col.ColumnName.Substring(0, col.ColumnName.LastIndexOf(']') + 1);
if (translations.ContainsKey(colKey))
{
string sNewColumnName = translations[colKey];
if (dictColumnNames.ContainsKey(sNewColumnName))
{
dictColumnNames[sNewColumnName]++;
}
else
{
dictColumnNames.Add(sNewColumnName, 1);
}
}
else
{
Context.TraceEvent(999, 0, string.Format("The translation for the column '{0}' was not found", col.ColumnName));
}
}
foreach (DataColumn col in tbl.Columns)
{
var colKey = col.ColumnName.Substring(0, col.ColumnName.LastIndexOf(']') + 1);
var suffix = col.ColumnName.Substring(col.ColumnName.LastIndexOf("]") + 1);
if (translations.ContainsKey(colKey))
{
string sNewName = translations[colKey];
if (dictColumnNames[sNewName] > 1)
{
//if (string.IsNullOrWhiteSpace( suffix)){
//prefix with tablename
var m = columnNameRegex.Matches(col.ColumnName);
var dimName = m[0].Groups["cube"].Value.TrimStart('$');
var dimCaption = dsDims.Tables[0].Select(string.Format("DIMENSION_NAME = '{0}'", dimName))[0]["DIMENSION_CAPTION"].ToString();
sNewName = dimCaption + "." + sNewName + suffix;
//}
//else {
// col.ColumnName = sNewName + suffix;
//}
}
Context.TraceEvent(999, 0, string.Format("translating: '{0}' to '{1}'", col.ColumnName, sNewName));
col.ColumnName = sNewName;
}
}
//foreach (DataColumn col in tbl.Columns)
//{
// string sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('.') + 1).Replace("[", "").Replace("]", "");
// if (dictColumnNames[sNewColumnName] > 1)
// sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('[') + 1).Replace("[", "").Replace("]", "").Replace("$", "");
// if (!tbl.Columns.Contains(sNewColumnName))
// col.ColumnName = sNewColumnName;
//}
return(tbl);
}
catch (Exception ex)
{
Context.TraceEvent(999, 0, string.Format("Unhandled Exception: {0}", ex.Message));
return(null);
}
finally
{
conn.Close();
}
}
/*
* Return results from query
*/
public AdomdDataReader GetQueryResult(string sQuery)
{
try
{
string sConnString = "Data Source=" + sServer + "; Initial Catalog=" + sCatalog;
Microsoft.AnalysisServices.AdomdClient.AdomdConnection objConn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(sConnString);
objConn.Open();
Microsoft.AnalysisServices.AdomdClient.AdomdCommand objCmd = objConn.CreateCommand();
objCmd.CommandText = sQuery;
//Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objReader = objCmd.ExecuteReader();
//Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter objDataAdaptor = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(objCmd);
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objDataReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection);
/*
try
{
for (int i = 0; i < objDataReader.FieldCount; i++)
{
Console.Write(objDataReader.GetName(i) + "\t");
}
Console.WriteLine();
while (objDataReader.Read())
{
for (int i = 0; i < objDataReader.FieldCount; i++)
{
object value = objDataReader.GetValue(i);
string strValue = (value == null) ?
string.Empty : value.ToString();
Console.Write(strValue + "\t");
}
Console.WriteLine();
}
}
finally
{
objDataReader.Close();
}
*/
return objDataReader;
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
return null;
}
public static object GetDBVal(string dbName)
{
object returnValue;
string measuresText = "";
string dimensionsText = "";
Boolean addcomma = false;
foreach (AMDS.Dimension d in Context.CurrentCube.Dimensions)
{
foreach (AMDS.Hierarchy h in d.AttributeHierarchies)
{
if (d.DimensionType == DimensionTypeEnum.Measure)
{
measuresText = h.CurrentMember.UniqueName;
}
else
//eliminate comparison dimension from the query
if ((h.CurrentMember.UniqueName != h.DefaultMember) && (!h.CurrentMember.UniqueName.Contains("[Comparison Operations]")))
{
if (addcomma == false)
addcomma = true;
else
dimensionsText += ",";
dimensionsText += h.CurrentMember.UniqueName;
}
}
}
//if all the dimensions in default members do not compose where clause of the query
if(dimensionsText!="") dimensionsText = " where (" + dimensionsText + ")";
string query=" select "+ measuresText+" on 0 from ["+Context.CurrentCube.Name+"] "+ dimensionsText;
Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Data Source=" + Context.Server.Name+";Catalog="+dbName);
conn.Open();
try
{
AMDC.CellSet queryCellset;
Microsoft.AnalysisServices.AdomdClient.AdomdCommand queryCommand = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand();
queryCommand.CommandText = query;
queryCommand.Connection = conn;
queryCellset = queryCommand.ExecuteCellSet();
returnValue = queryCellset[0].Value;
}
finally
{
conn.Close();
}
return returnValue;
}
private void LoadSessions()
{
Microsoft.AnalysisServices.AdomdServer.Context.TraceEvent(0, 4, "Starting LoadSessions");
AdomdClient.AdomdConnection conn = new AdomdClient.AdomdConnection("Data Source=" + AdomdServer.Context.CurrentServerID);
conn.Open();
DataSet ds = conn.GetSchemaDataSet("DISCOVER_SESSIONS", null);
conn.Close();
foreach (DataRow dr in ds.Tables[0].Rows)
{
sessions.Add(Convert.ToInt32(dr["SESSION_SPID"]), Convert.ToString(dr["SESSION_USER_NAME"]));
}
Microsoft.AnalysisServices.AdomdServer.Context.TraceEvent(0, 5, "Finishing LoadSessions");
}
private static void RunAQuery(object o)
{
ParallelQueryThreadInfo info = null;
try
{
info = (ParallelQueryThreadInfo)o;
Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(info.connectionString);
conn.Open();
try
{
CellSet queryCellset;
Microsoft.AnalysisServices.AdomdClient.AdomdCommand queryCommand = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand();
queryCommand.CommandText = info.query;
queryCommand.Connection = conn;
queryCellset = queryCommand.ExecuteCellSet();
info.returnValue = queryCellset[0].Value.ToString();
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
info.ex = ex;
}
finally
{
info.autoEvent.Set();
}
}
public static void Main(string[] args)
{
string varSRV = "SRV-OLAP";
string varDB = "dw_olap";
string varCube = "Рух товарів";
for (int i = 0; i < args.Length; i++)
{
if (args[i].ToUpper().StartsWith("/SERVER:"))
{
varSRV = args[i].Substring(8);
}
else if (args[i].ToUpper().StartsWith("/DB:"))
{
varDB = args[i].Substring(4);
}
else if (args[i].ToUpper().StartsWith("/CUBE:"))
{
varCube = args[i].Substring(6);
}
}
string varSeparator = "\t";
Proc p = new Proc();
p.CreateLog("d:\\Cube_" + varCube + ".txt");
Microsoft.AnalysisServices.Server s = new Microsoft.AnalysisServices.Server();
s.Connect(@"Data Source=" + varSRV + ";Provider=msolap;Initial Catalog=" + varDB);
Microsoft.AnalysisServices.Database d = s.Databases.FindByName(varDB);
Microsoft.AnalysisServices.Cube c = d.Cubes.FindByName(varCube);
p.Log("База:" + varDB + " Куб:" + varCube);
p.Log("Розмірності");
foreach (Microsoft.AnalysisServices.CubeDimension dim in c.Dimensions)
{
p.Log(dim.Name + varSeparator + dim.DimensionID + varSeparator + dim.Description);
foreach (Microsoft.AnalysisServices.CubeAttribute attr in dim.Attributes)
{
p.Log(varSeparator + attr.Attribute + varSeparator + attr.AttributeID + "\t" + attr.Attribute.Description);
}
}
p.Log("Групи мір");
foreach (Microsoft.AnalysisServices.MeasureGroup mg in c.MeasureGroups)
{
p.Log(mg.Name + varSeparator + mg.ID + varSeparator + mg.Description);
foreach (Microsoft.AnalysisServices.Measure m in mg.Measures)
{
p.Log(varSeparator + m.ID + varSeparator + m.Name + varSeparator + m.Description);
}
}
p.Log("Калькульовані міри");
Microsoft.AnalysisServices.AdomdClient.AdomdConnection cn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Data Source=" + varSRV + ";Provider=msolap;Initial Catalog=" + varDB);
cn.Open();
/*foreach ( Microsoft.AnalysisServices.AdomdClient.CubeDef tt in cn.Cubes)
* p.Log(tt.Name+varSeparator+tt.Caption );*/
try
{
foreach (Microsoft.AnalysisServices.AdomdClient.Measure m in cn.Cubes[varCube].Measures)
{
if (string.IsNullOrEmpty(m.Expression) == false)
{
p.Log(m.UniqueName + varSeparator); //+m.Expression +varSeparator+ m.Description );
}
}
//Console.WriteLine("{0}: {1}",m.UniqueName,m.Expression );
}
catch { }
finally { cn.Close(); };
//Console.ReadLine();
p.CloseLog();
}
