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 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 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 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);
}
/*
* 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 void Open()
{
if (_type == AdomdType.AnalysisServices)
{
_conn.Open();
}
else
{
ExcelAdoMdConnections.VoidDelegate f = delegate
{
_connExcel.Open();
};
f();
}
}
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 static object ExecuteScalar(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;
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();
}
}
public static void Main(string[] args)
{
string varSRV = "SRV-OLAP";
string varDB = "dw_olap";
string varCube = "Рух товарів";
for (int i = 0; i < args.Length; i++)
{
if (args[i].ToUpper().StartsWith("/SERVER:"))
{
varSRV = args[i].Substring(8);
}
else if (args[i].ToUpper().StartsWith("/DB:"))
{
varDB = args[i].Substring(4);
}
else if (args[i].ToUpper().StartsWith("/CUBE:"))
{
varCube = args[i].Substring(6);
}
}
string varSeparator = "\t";
Proc p = new Proc();
p.CreateLog("d:\\Cube_" + varCube + ".txt");
Microsoft.AnalysisServices.Server s = new Microsoft.AnalysisServices.Server();
s.Connect(@"Data Source=" + varSRV + ";Provider=msolap;Initial Catalog=" + varDB);
Microsoft.AnalysisServices.Database d = s.Databases.FindByName(varDB);
Microsoft.AnalysisServices.Cube c = d.Cubes.FindByName(varCube);
p.Log("База:" + varDB + " Куб:" + varCube);
p.Log("Розмірності");
foreach (Microsoft.AnalysisServices.CubeDimension dim in c.Dimensions)
{
p.Log(dim.Name + varSeparator + dim.DimensionID + varSeparator + dim.Description);
foreach (Microsoft.AnalysisServices.CubeAttribute attr in dim.Attributes)
{
p.Log(varSeparator + attr.Attribute + varSeparator + attr.AttributeID + "\t" + attr.Attribute.Description);
}
}
p.Log("Групи мір");
foreach (Microsoft.AnalysisServices.MeasureGroup mg in c.MeasureGroups)
{
p.Log(mg.Name + varSeparator + mg.ID + varSeparator + mg.Description);
foreach (Microsoft.AnalysisServices.Measure m in mg.Measures)
{
p.Log(varSeparator + m.ID + varSeparator + m.Name + varSeparator + m.Description);
}
}
p.Log("Калькульовані міри");
Microsoft.AnalysisServices.AdomdClient.AdomdConnection cn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Data Source=" + varSRV + ";Provider=msolap;Initial Catalog=" + varDB);
cn.Open();
/*foreach ( Microsoft.AnalysisServices.AdomdClient.CubeDef tt in cn.Cubes)
* p.Log(tt.Name+varSeparator+tt.Caption );*/
try
{
foreach (Microsoft.AnalysisServices.AdomdClient.Measure m in cn.Cubes[varCube].Measures)
{
if (string.IsNullOrEmpty(m.Expression) == false)
{
p.Log(m.UniqueName + varSeparator); //+m.Expression +varSeparator+ m.Description );
}
}
//Console.WriteLine("{0}: {1}",m.UniqueName,m.Expression );
}
catch { }
finally { cn.Close(); };
//Console.ReadLine();
p.CloseLog();
}
public static void 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 { }
}
}
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 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 { }
}
}
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");
}
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;
}
/*
* 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;
}
