public DataSet GetSchemaDataSet(string schemaName)
{
if (_adomdConn.State != ConnectionState.Open)
{
_adomdConn.Open();
}
return(_adomdConn.GetSchemaDataSet(schemaName, null, true));
}
/// <summary>
/// Extracts the list of DAX functions with specific restrictions.
/// </summary>
/// <param name="restrictions">Restrictions to be applied.</param>
private List <DaxFunction> ExtractDaxFunctionsWithRestrictions(AdomdRestrictionCollection restrictions)
{
List <DaxFunction> daxFunctions = new List <DaxFunction>();
DataSet functionData = null;
if (_adomdConn == null || _adomdConn.State != ConnectionState.Open)
{
return(daxFunctions);
}
try
{
functionData = _adomdConn.GetSchemaDataSet(FUNCTIONS_SCHEMA, restrictions);
// Expecting to receive two tables (one with the functions rowset another with parameter info rows)
Debug.Assert(functionData.Tables.Count == 2, "Expected MDSCHEMA_FUNCTION to return 2 tables.");
// Expecting that the tables are related
Debug.Assert(functionData.Relations.Count > 0, "Expected a relationship in the MDSCHEMA_FUNCTION return dataset.");
DataRelation parameterInfoRelation = functionData.Relations[FUNCTIONS_RELATION_PARAMETERINFO];
DataTable functionTable = functionData.Tables[0];
Debug.Assert(parameterInfoRelation != null, "Cannot find appropriate relation in MDSCHEMA_FUNCTION result.");
foreach (DataRow row in functionTable.Rows)
{
try
{
daxFunctions.Add(LoadFunctionDataRow(parameterInfoRelation, row));
}
catch
{
// We are ignoring any errors that occur while loading a particular function,
// so the rest may be loaded correctly.
Debug.Fail("Failed to load function description.");
}
}
}
finally
{
// Ensure we dispose the DataSet
if (functionData != null)
{
functionData.Dispose();
functionData = null;
}
}
return(daxFunctions);
}
private void btnRequest_Click(object sender, EventArgs e)
{
AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
try
{
foreach (DataGridViewRow row in gridRestrictions.Rows)
{
if (row.Cells["AdomdRestrictionName"].Value != null && row.Cells["AdomdRestrictionName"].Value != DBNull.Value && !string.IsNullOrEmpty(row.Cells["AdomdRestrictionName"].Value.ToString()))
{
restrictions.Add(row.Cells["AdomdRestrictionName"].Value.ToString().Trim(), row.Cells["AdomdRestrictionValue"].Value.ToString().Trim());
}
}
AdomdConnection conn = new AdomdConnection(txtConnStr.Text);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
DataTable table = conn.GetSchemaDataSet(cboxRequestType.Text, restrictions).Tables[0];
gridResults.DataSource = table;
}
catch (Exception ex)
{
this.ShowMessage(ex);
}
}
private void btnRequest_Click(object sender, EventArgs e)
{
AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
try
{
foreach (DataGridViewRow row in gridRestrictions.Rows)
{
if (row.Cells["AdomdRestrictionName"].Value != null && row.Cells["AdomdRestrictionName"].Value != DBNull.Value && !string.IsNullOrEmpty(row.Cells["AdomdRestrictionName"].Value.ToString()))
restrictions.Add(row.Cells["AdomdRestrictionName"].Value.ToString().Trim(), row.Cells["AdomdRestrictionValue"].Value.ToString().Trim());
}
AdomdConnection conn = new AdomdConnection(txtConnStr.Text);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
DataTable table = conn.GetSchemaDataSet(cboxRequestType.Text, restrictions).Tables[0];
gridResults.DataSource = table;
}
catch (Exception ex)
{
this.ShowMessage(ex);
}
}
static public List <string> getAllMeasures(string datasource, string catalog, string cube)
{
List <string> measureNames = new List <string>();
string connectionString = string.Format(Constants.dataSource, datasource, catalog);
using (AdomdConnection conn = new AdomdConnection(connectionString))
{
conn.Open();
using (DataSet measures = conn.GetSchemaDataSet(Constants.mdSchema, null, true))
{
foreach (DataTable dta in measures.Tables)
{
foreach (DataRow dro in dta.Rows)
{
foreach (DataColumn dco in dta.Columns)
{
// Get non-null unique measure names
if (dro[dco] != null && dco.ColumnName.ToString() == Constants.measureUniqueName)
{
measureNames.Add(dro[dco].ToString());
}
}
}
}
}
conn.Close();
}
return(measureNames);
}
/// <summary>
/// 通过SchemaDataSet的方式获取制定立方体的维度
/// </summary>
/// <param name="connection">AdomdConnection对象的实例</param>
/// <param name="connectionString">连接字符串</param>
/// <returns></returns>
public string[] GetSchemaDataSet_Dimensions(ref AdomdConnection connection, string connectionString, string cubeName)
{
string[] strDimensions = null;
bool connected = true; //判断connection是否已与数据库连接
DataTable objTable = new DataTable();
if (IsConnected(ref connection) == false)
{
try
{
Connect(ref connection, connectionString);
connected = false;
}
catch (Exception err)
{
throw err;
}
}
string[] strRestriction = new string[] { null, null, cubeName, null, null };
objTable = connection.GetSchemaDataSet(AdomdSchemaGuid.Dimensions, strRestriction).Tables[0];
if (connected == false)
{
Disconnect(ref connection, false);
}
strDimensions = new string[objTable.Rows.Count];
int rowcount = 0;
foreach (DataRow tempRow in objTable.Rows)
{
strDimensions[rowcount] = tempRow["DIMENSION_NAME"].ToString();
rowcount++;
}
return(strDimensions);
}
/// <summary>
/// 获取OLAP数据库。
/// </summary>
/// <param name="connection">AdomdConnection对象的实例</param>
/// <param name="connectionString">连接字符串</param>
/// <returns></returns>
public DataTable GetSchemaDataSet_Catalogs(ref AdomdConnection connection, string connectionString)
{
bool connected = true; //判断connection在调用此函数时，是否已经处于连接状态
DataTable objTable = new DataTable();
try
{
// Check if a valid connection was provided.
if (IsConnected(ref connection) == false)
{
//如果连接不存在，则建立连接
Connect(ref connection, connectionString);
connected = false; //更改connection为未连接状态。
}
objTable = connection.GetSchemaDataSet(AdomdSchemaGuid.Catalogs, null).Tables[0];
if (connected == false)
{
//关闭连接
Disconnect(ref connection, false);
}
}
catch (Exception err)
{
throw err;
}
return(objTable);
}
public static List <string> getAllAttributes(string datasource, string catalog, string cube)
{
List <string> attributeNames = new List <string>();
string connectionString = string.Format(Constants.dataSource, datasource, catalog);
using (AdomdConnection conn = new AdomdConnection(connectionString))
{
conn.Open();
using (DataSet attributes = conn.GetSchemaDataSet(Constants.mdSchemaLevels, null, true))
{
foreach (DataTable dta in attributes.Tables)
{
foreach (DataRow dro in dta.Rows)
{
if (Convert.ToInt32((dro.ItemArray[9].ToString())) != 0) // Removes levels of type (All)
{
foreach (DataColumn dco in dta.Columns)
{
// Get non-null unique names
if (dro[dco] != null && dco.ColumnName.ToString() == Constants.levelUniqueName)
{
attributeNames.Add(dro[dco].ToString());
}
}
}
}
}
}
conn.Close();
}
return(attributeNames);
}
protected virtual string InquireFurtherAnalysisService(string connectionString)
{
try
{
var parsedMode = string.Empty;
using (var conn = new AdomdConnection(connectionString))
{
conn.Open();
var restrictions = new AdomdRestrictionCollection();
restrictions.Add(new AdomdRestriction("ObjectExpansion", "ReferenceOnly"));
var ds = conn.GetSchemaDataSet("DISCOVER_XML_METADATA", restrictions);
var xml = ds.Tables[0].Rows[0].ItemArray[0].ToString();
var doc = new XmlDocument();
doc.LoadXml(xml);
parsedMode = ParseXmlaResponse(doc);
}
switch (parsedMode)
{
case "Default": return Olap;
case "Multidimensional": return Olap;
case "SharePoint": return Tabular;
case "Tabular": return Tabular;
}
}
catch (Exception ex)
{
Trace.WriteLineIf(NBiTraceSwitch.TraceWarning,"Can't detect server mode for SSAS, using Olap. Initial message:" + ex.Message);
return Olap;
}
return Olap;
}
private void btnConnect_Click(object sender, EventArgs e)
{
try
{
cos.Clear();
if (this.ConnStr.ToLower().Contains("catalog"))
{
CubeOperate co = new CubeOperate(this.ConnStr);
cos.Add(co.Conn.Database, co);
}
else
{
AdomdConnection conn = new AdomdConnection(this.ConnStr);
conn.Open();
DataSet dsCatalogs = conn.GetSchemaDataSet("DBSCHEMA_CATALOGS", null);
conn.Close();
foreach (DataRow catalogRow in dsCatalogs.Tables[0].Rows.Cast <DataRow>())
{
string catalog = catalogRow[0].ToString();
string connStr = string.Format("{0};Catalog ={1};", this.ConnStr.EndsWith(";") ? this.ConnStr.Remove(this.ConnStr.Length - 1) : this.ConnStr, catalog);
cos.Add(catalog, new CubeOperate(connStr));
}
}
BindServerInfo();
}
catch (Exception ex)
{
this.ShowMessage(ex);
}
}
public static DataTable GetSchemaRowset(AdomdConnection conn, Guid schemaGuid, string rowFilter = null, params object[] restrictions)
{
if (conn == null)
{
throw new ArgumentNullException("conn");
}
if (schemaGuid == null)
{
throw new ArgumentNullException("schemaGuid");
}
// Open the connection if not open
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var dt = conn.GetSchemaDataSet(schemaGuid, restrictions).Tables[0];
//Apply row filter & return the filtered table
if (!string.IsNullOrEmpty(rowFilter))
{
dt.DefaultView.RowFilter = rowFilter;
dt = dt.DefaultView.ToTable();
}
return(dt);
}
//</snippetRetrieveCubesAndDimensions>
//<snippetGetActions>
//The following function can be called with the following data:
//ae.GetActions(conn, "Adventure Works","[Geography].[City]",6 );
//This would return a DataSet containing the actions available for cells
//in the Adventure Works cube on [Geography].[City].
private System.Data.DataSet GetActions(AdomdConnection Connection, string Cube, string Coordinate, int CoordinateType)
{
//Create a restriction collection to restrict the schema information to be returned.
AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
restrictions.Add("CUBE_NAME", Cube);
restrictions.Add("COORDINATE", Coordinate);
restrictions.Add("COORDINATE_TYPE", CoordinateType); //6 = Cell coordinate
//Open and return a schema rowset, given the correct restictions
return(Connection.GetSchemaDataSet("MDSCHEMA_ACTIONS", restrictions));
}
public static List <string> getVisibleHierarchiesByCube(string datasource, string catalog, string cube)
{
List <string> hierarchies = new List <string>();
string connectionString = string.Format(Constants.dataSource, datasource, catalog);
AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
restrictions.Add(Constants.cubeName, cube);
restrictions.Add(Constants.hierarchyVisibility, 1);
using (AdomdConnection conn = new AdomdConnection(connectionString))
{
conn.Open();
using (DataSet results = conn.GetSchemaDataSet(Constants.mdSchemaHierarchies, restrictions, true))
{
foreach (DataTable dta in results.Tables)
{
foreach (DataRow dro in dta.Rows)
{
foreach (DataColumn dco in dta.Columns)
{
// Get non-null unique measure names
if (dro[dco] != null && dco.ColumnName.ToString() == Constants.hierarchyUniqueName)
{
hierarchies.Add(dro[dco].ToString());
}
}
}
}
}
conn.Close();
}
return(hierarchies);
}
protected virtual string InquireFurtherAnalysisService(string connectionString)
{
try
{
var parsedMode = string.Empty;
using (var conn = new AdomdConnection(connectionString))
{
conn.Open();
var restrictions = new AdomdRestrictionCollection
{
new AdomdRestriction("ObjectExpansion", "ReferenceOnly")
};
var ds = conn.GetSchemaDataSet("DISCOVER_XML_METADATA", restrictions);
var xml = ds.Tables[0].Rows[0].ItemArray[0].ToString();
var doc = new XmlDocument();
doc.LoadXml(xml);
parsedMode = ParseXmlaResponse(doc);
}
switch (parsedMode)
{
case "Default": return(Olap);
case "Multidimensional": return(Olap);
case "SharePoint": return(Tabular);
case "Tabular": return(Tabular);
}
}
catch (Exception ex)
{
Trace.WriteLineIf(Extensibility.NBiTraceSwitch.TraceWarning, "Can't detect server mode for SSAS, using Olap. Initial message:" + ex.Message);
return(Olap);
}
return(Olap);
}
//</snippetExecuteXMLAProcessCommand>
//<snippetOutputDimensionSchemaRowset>
void OutputDimensionSchemaRowset()
{
//Open a connection to the local server
AdomdConnection conn = new AdomdConnection("Data Source=localhost");
conn.Open();
//Retrieve the schema rowset that describes the dimensions on the server
System.Data.DataSet reader = conn.GetSchemaDataSet(AdomdSchemaGuid.Dimensions, null);
//Retrieve the first table that has been returned
//Some schema rowsets return more than one table
System.Data.DataTable data = reader.Tables[0];
//Loop through the columns and print them out as column headers.
foreach (System.Data.DataColumn col in data.Columns)
{
Console.Write(col.ColumnName + "/t");
}
Console.WriteLine();
//Loop through the cells, printing them out
foreach (System.Data.DataRow row in data.Rows)
{
foreach (System.Data.DataColumn col in data.Columns)
{
Console.Write(row[col.ColumnName].ToString() + "/t");
}
Console.WriteLine();
}
//Close the connection and await user input.
conn.Close();
Console.ReadLine();
}
void _worker_DoWork(object sender, DoWorkEventArgs e)
{
string connectionString = string.Format("Provider=MSOLAP;Application Name=DAX Editor;Data Source={0}", _serverName);
try
{
using (var _adomdConn = new AdomdConnection(connectionString))
{
_adomdConn.Open();
var dataSet = _adomdConn.GetSchemaDataSet("DBSCHEMA_CATALOGS", null);
Debug.Assert(dataSet != null);
Debug.Assert(dataSet.Tables.Count == 1);
Debug.Assert(dataSet.Tables[0] != null);
foreach (DataRow row in dataSet.Tables[0].Rows)
{
_databases.Add(row[0] as string);
}
}
}
catch (Exception)
{
_databases.Add("Error while reading list of databases");
}
}
private void FillTree()
{
Dictionary <string, long> dictHierarchyCardinality = new Dictionary <string, long>();
try
{
AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
restrictions.Add(new AdomdRestriction("CATALOG_NAME", this.liveDB.Name));
restrictions.Add(new AdomdRestriction("CUBE_NAME", this.liveCube.Name));
restrictions.Add(new AdomdRestriction("HIERARCHY_VISIBILITY", 3)); //visible and non-visible hierarchies
foreach (System.Data.DataRow r in adomdConnection.GetSchemaDataSet("MDSCHEMA_HIERARCHIES", restrictions).Tables[0].Rows)
{
dictHierarchyCardinality.Add(Convert.ToString(r["HIERARCHY_UNIQUE_NAME"]), Convert.ToInt64(r["HIERARCHY_CARDINALITY"]));
}
}
catch { }
StringBuilder sbExport = new StringBuilder();
sbExport.Append("Cube Dimension Name").Append("\t").Append("Attribute Name").Append("\t").Append("Number of Slices on this Attribute").Append("\t").Append("Attribute Cardinality").Append("\t").Append("Related Partition Count").Append("\t").Append("Currently Effective Optimized State").Append("\t").Append("Recommendation").AppendLine();
foreach (CubeDimension cd in cloneCube.Dimensions)
{
TreeNode parentNode = treeViewAggregation.Nodes.Add(cd.Name);
parentNode.Tag = cd;
parentNode.ImageIndex = parentNode.SelectedImageIndex = 0;
bool bAllIndexesChecked = true;
foreach (CubeAttribute ca in cd.Attributes)
{
if (ca.AttributeHierarchyEnabled && ca.Attribute.AttributeHierarchyEnabled)
{
CubeAttribute caSliced = null;
foreach (CubeAttribute sliced in dictHitIndexes.Keys)
{
if (sliced.Parent.ID == ca.Parent.ID && sliced.AttributeID == ca.AttributeID)
{
caSliced = sliced;
break;
}
}
string sNodeName = ca.Attribute.Name;
int iIndexHits = 0;
if (caSliced != null)
{
iIndexHits = dictHitIndexes[caSliced];
sNodeName += " (Index hits: " + iIndexHits.ToString("g") + ")";
}
TreeNode attributeNode = parentNode.Nodes.Add(sNodeName);
attributeNode.Tag = ca;
attributeNode.ImageIndex = attributeNode.SelectedImageIndex = 1;
attributeNode.Checked = (caSliced == null);
bAllIndexesChecked = bAllIndexesChecked && attributeNode.Checked;
bool bInEnabledHierarchy = false;
foreach (CubeHierarchy ch in ca.Parent.Hierarchies)
{
foreach (Microsoft.AnalysisServices.Level l in ch.Hierarchy.Levels)
{
if (l.SourceAttributeID == ca.AttributeID && ch.Enabled && ch.OptimizedState == OptimizationType.FullyOptimized)
{
bInEnabledHierarchy = true;
}
}
}
OptimizationType optimized = ca.Attribute.AttributeHierarchyOptimizedState;
if (optimized == OptimizationType.FullyOptimized)
{
optimized = ca.AttributeHierarchyOptimizedState;
}
if (optimized == OptimizationType.NotOptimized && bInEnabledHierarchy)
{
optimized = OptimizationType.FullyOptimized;
}
string sRecommendation = "";
if (optimized == OptimizationType.FullyOptimized && iIndexHits == 0)
{
attributeNode.ForeColor = Color.Black;
sRecommendation = "Disable";
attributeNode.BackColor = Color.Green;
attributeNode.ToolTipText = "Currently indexed but the index was not used during the profiler trace. If left checked, BIDS Helper will disable the indexes when you click OK.";
}
else if (optimized == OptimizationType.NotOptimized)
{
attributeNode.ForeColor = Color.DarkGray;
attributeNode.ToolTipText = "Indexes not currently being built.";
if (iIndexHits > 0)
{
sRecommendation = "Enable";
attributeNode.ForeColor = Color.Black;
attributeNode.BackColor = Color.Red;
attributeNode.ToolTipText = "Currently not indexed but the queries observed during the profiler trace would have used the index if it had been built. If left unchecked, BIDS Helper will re-enable indexing when you click OK.";
}
}
else
{
attributeNode.ForeColor = Color.Black;
attributeNode.ToolTipText = "Indexes are being built and are used during the queries observed during the profiler trace.";
}
long? iCardinality = null;
string sAttributeUniqueName = "[" + ca.Parent.Name + "].[" + ca.Attribute.Name + "]";
if (dictHierarchyCardinality.ContainsKey(sAttributeUniqueName))
{
iCardinality = dictHierarchyCardinality[sAttributeUniqueName];
}
int iPartitions = 0;
try
{
foreach (MeasureGroup mg in liveCube.MeasureGroups)
{
if (mg.Dimensions.Contains(cd.ID))
{
try
{
RegularMeasureGroupDimension rmgd = mg.Dimensions[cd.ID] as RegularMeasureGroupDimension;
if (rmgd == null)
{
continue;
}
if (!AggManager.ValidateAggs.IsAtOrAboveGranularity(ca.Attribute, rmgd))
{
continue;
}
iPartitions += mg.Partitions.Count;
}
catch { }
}
}
}
catch { }
sbExport.Append(cd.Name).Append("\t").Append(ca.Attribute.Name).Append("\t").Append(iIndexHits).Append("\t").Append(iCardinality).Append("\t").Append(iPartitions).Append("\t").Append(optimized.ToString()).Append("\t").Append(sRecommendation).AppendLine();
}
}
parentNode.Checked = bAllIndexesChecked;
}
treeViewAggregation.Sort();
sExport = sbExport.ToString();
}
public void Init(MeasureGroup mg,
string strParition,
EnvDTE.ProjectItem projItem)
{
try
{
bool IsOnlineMode = false;
Cube selectedCube = projItem.Object as Cube;
string serverName = "";
string databaseName = "";
string connectionString = "";
if ((selectedCube != null) && (selectedCube.ParentServer != null))
{
// if we are in Online mode there will be a parent server
serverName = selectedCube.ParentServer.Name;
databaseName = selectedCube.Parent.Name;
IsOnlineMode = true;
connectionString = selectedCube.ParentServer.ConnectionString;
}
else
{
// if we are in Project mode we will use the server name from
// the deployment settings
DeploymentSettings deploySet = new DeploymentSettings(projItem);
serverName = deploySet.TargetServer;
databaseName = deploySet.TargetDatabase; //use the target database instead of selectedCube.Parent.Name because selectedCube.Parent.Name only reflects the last place it was deployed to, and we want the user to be able to use the deployment settings to control which deployed server/database to check against
connectionString = "Data Source=" + serverName;
}
mg1 = mg;
part1 = mg.Partitions.FindByName(strParition);
aggDes = part1.AggregationDesign;
this.Text = " Aggregation sizes for partition " + strParition;
//lblSize.Text = part1.EstimatedRows.ToString() + " records"; //base this not on estimated rows but on actual rows... see below
lablPartName.Text = strParition;
txtServerNote.Text = string.Format("Note: The Partition size details have been taken from the currently deployed '{1}' database on the '{0}' server, " +
"which is the one currently configured as the deployment target.", serverName, databaseName);
txtServerNote.Visible = !IsOnlineMode;
//--------------------------------------------------------------------------------
// Open ADOMD connection to the server and issue DISCOVER_PARTITION_STAT request to get aggregation sizes
//--------------------------------------------------------------------------------
AdomdConnection adomdConnection = new AdomdConnection(connectionString);
adomdConnection.Open();
partitionDetails = adomdConnection.GetSchemaDataSet(AdomdSchemaGuid.PartitionStat, new object[] { databaseName, mg1.Parent.Name, mg1.Name, strParition });
DataColumn colItem1 = new DataColumn("Percentage", Type.GetType("System.String"));
partitionDetails.Tables[0].Columns.Add(colItem1);
AddGridStyle();
dataGrid1.DataSource = partitionDetails.Tables[0];
long iPartitionRowCount = 0;
if (partitionDetails.Tables[0].Rows.Count > 0)
{
iPartitionRowCount = Convert.ToInt64(partitionDetails.Tables[0].Rows[0]["AGGREGATION_SIZE"]);
}
lblSize.Text = iPartitionRowCount + " records";
double ratio = 0;
foreach (DataRow row in partitionDetails.Tables[0].Rows)
{
ratio = 100.0 * ((long)row["AGGREGATION_SIZE"] / (double)iPartitionRowCount);
row["Percentage"] = ratio.ToString("#0.00") + "%";
}
CurrencyManager cm = (CurrencyManager)this.BindingContext[dataGrid1.DataSource, dataGrid1.DataMember];
((DataView)cm.List).AllowNew = false;
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message);
try
{
this.Close();
}
catch { }
}
}
/// <summary>
/// Возвращает список кубов
/// </summary>
/// <returns></returns>
public CubeDefInfo GetCubeMetadata(String cubeName, MetadataQueryType type)
{
try
{
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Getting Cube '{1}' Metadata Started",
DateTime.Now.ToString(), cubeName);
CubeDef cube = FindCube(cubeName);
if (cube != null)
{
CubeDefInfo cube_info = InfoHelper.CreateCubeInfo(cube);
foreach (Dimension dim in cube.Dimensions)
{
DimensionInfo dim_info = InfoHelper.CreateDimensionInfo(dim);
cube_info.Dimensions.Add(dim_info);
foreach (Hierarchy hierarchy in dim.Hierarchies)
{
HierarchyInfo hier_info = InfoHelper.CreateHierarchyInfo(hierarchy);
dim_info.Hierarchies.Add(hier_info);
foreach (Level level in hierarchy.Levels)
{
LevelInfo level_info = InfoHelper.CreateLevelInfo(level);
hier_info.Levels.Add(level_info);
}
//AdomdConnection conn = GetConnection(ConnectionString);
//// Для каждой иерархии пытаемся определить элемент, который имеет тип MemberTypeEnum.All
//try
//{
// AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
// restrictions.Add("CATALOG_NAME", conn.Database);
// restrictions.Add("CUBE_NAME", OlapHelper.ConvertToNormalStyle(cubeName));
// restrictions.Add("DIMENSION_UNIQUE_NAME", dim.UniqueName);
// restrictions.Add("HIERARCHY_UNIQUE_NAME", hierarchy.UniqueName);
// restrictions.Add("MEMBER_TYPE", 2/*MemberTypeEnum.All*/);
// DataSet ds = conn.GetSchemaDataSet("MDSCHEMA_MEMBERS", restrictions);
// if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
// {
// DataTable table = ds.Tables[0];
// if (table.Columns.Contains("MEMBER_UNIQUE_NAME"))
// {
// object obj = ds.Tables[0].Rows[0]["MEMBER_UNIQUE_NAME"];
// if (obj != null)
// hier_info.Custom_AllMemberUniqueName = obj.ToString();
// }
// }
//}catch
//{
//}
//// Для каждой иерархии пытаемся определить элемент, который имеет тип MemberTypeEnum.Unknown
//try
//{
// AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
// restrictions.Add("CATALOG_NAME", conn.Database);
// restrictions.Add("CUBE_NAME", OlapHelper.ConvertToNormalStyle(cubeName));
// restrictions.Add("DIMENSION_UNIQUE_NAME", dim.UniqueName);
// restrictions.Add("HIERARCHY_UNIQUE_NAME", hierarchy.UniqueName);
// restrictions.Add("MEMBER_TYPE", 0 /*MemberTypeEnum.Unknown.All*/);
// DataSet ds = conn.GetSchemaDataSet("MDSCHEMA_MEMBERS", restrictions);
// if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
// {
// DataTable table = ds.Tables[0];
// if (table.Columns.Contains("MEMBER_UNIQUE_NAME"))
// {
// object obj = ds.Tables[0].Rows[0]["MEMBER_UNIQUE_NAME"];
// if (obj != null)
// hier_info.Custom_UnknownMemberUniqueName = obj.ToString();
// }
// }
//}
//catch
//{
//}
}
}
foreach (Kpi kpi in cube.Kpis)
{
KpiInfo kpi_info = InfoHelper.CreateKpiInfo(kpi);
cube_info.Kpis.Add(kpi_info);
}
foreach (Measure measure in cube.Measures)
{
MeasureInfo measure_info = InfoHelper.CreateMeasureInfo(measure);
cube_info.Measures.Add(measure_info);
}
foreach (NamedSet set in cube.NamedSets)
{
NamedSetInfo set_info = InfoHelper.CreateNamedSetInfo(set);
cube_info.NamedSets.Add(set_info);
}
if (type == MetadataQueryType.GetCubeMetadata_AllMembers)
{
AdomdConnection conn = GetConnection();
// Для каждой иерархии пытаемся определить элемент, который имеет тип MemberTypeEnum.All
try
{
AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
restrictions.Add("CATALOG_NAME", conn.Database);
restrictions.Add("CUBE_NAME", OlapHelper.ConvertToNormalStyle(cubeName));
restrictions.Add("MEMBER_TYPE", 2 /*MemberTypeEnum.All*/);
DataSet ds = conn.GetSchemaDataSet("MDSCHEMA_MEMBERS", restrictions);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataTable table = ds.Tables[0];
if (table.Columns.Contains("MEMBER_UNIQUE_NAME") &&
table.Columns.Contains("HIERARCHY_UNIQUE_NAME") &&
table.Columns.Contains("DIMENSION_UNIQUE_NAME"))
{
foreach (DataRow row in ds.Tables[0].Rows)
{
String dimension_UniqueName = row["DIMENSION_UNIQUE_NAME"] != null ? row["DIMENSION_UNIQUE_NAME"].ToString() : String.Empty;
String hierarchy_UniqueName = row["HIERARCHY_UNIQUE_NAME"] != null ? row["HIERARCHY_UNIQUE_NAME"].ToString() : String.Empty;
String member_UniqueName = row["MEMBER_UNIQUE_NAME"] != null ? row["MEMBER_UNIQUE_NAME"].ToString() : String.Empty;
if (!String.IsNullOrEmpty(dimension_UniqueName) &&
!String.IsNullOrEmpty(hierarchy_UniqueName) &&
!String.IsNullOrEmpty(member_UniqueName))
{
DimensionInfo dimension = cube_info.GetDimension(dimension_UniqueName);
if (dimension != null)
{
HierarchyInfo hierarchy = dimension.GetHierarchy(hierarchy_UniqueName);
if (hierarchy != null)
{
hierarchy.Custom_AllMemberUniqueName = member_UniqueName;
}
}
}
}
}
}
}
catch (Exception ex)
{
//throw ex;
}
}
cube_info.MeasureGroups = GetMeasureGroups(cubeName);
return(cube_info);
}
return(null);
}
finally {
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Getting Cube '{1}' Metadata Completed",
DateTime.Now.ToString(), cubeName);
}
}
public Dictionary <String, LevelPropertyInfo> GetLevelProperties(string cubeName, string dimensionUniqueName, string hierarchyUniqueName, String levelUniqueName)
{
try
{
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Level '{1}' Properties Started \r\n cubeName: '{2}' \r\n dimensionUniqueName: '{3}' \r\n hierarchyUniqueName: '{4}' ",
DateTime.Now.ToString(), levelUniqueName, cubeName, dimensionUniqueName, hierarchyUniqueName);
Dictionary <String, LevelPropertyInfo> list = new Dictionary <String, LevelPropertyInfo>();
// Ищем уровень
Level level = FindLevel(cubeName, dimensionUniqueName, hierarchyUniqueName, levelUniqueName);
if (level != null)
{
// Свойства уровня - атрибуты
AdomdConnection conn = GetConnection();
AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
restrictions.Add("CATALOG_NAME", conn.Database);
restrictions.Add("CUBE_NAME", OlapHelper.ConvertToNormalStyle(cubeName));
restrictions.Add("DIMENSION_UNIQUE_NAME", dimensionUniqueName);
restrictions.Add("HIERARCHY_UNIQUE_NAME", hierarchyUniqueName);
restrictions.Add("LEVEL_UNIQUE_NAME", level.UniqueName);
DataSet ds = conn.GetSchemaDataSet("MDSCHEMA_PROPERTIES", restrictions);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataTable table = ds.Tables[0];
if (ds.Tables[0].Columns.Count > 0)
{
object obj = null;
foreach (DataRow row in ds.Tables[0].Rows)
{
Type type = null;
if (table.Columns.Contains("DATA_TYPE"))
{
obj = row["DATA_TYPE"];
System.Data.OleDb.OleDbType oleDbType = (System.Data.OleDb.OleDbType)(Convert.ToInt32(obj));
type = OleDbTypeConverter.Convert(oleDbType);
}
String name = String.Empty;
if (table.Columns.Contains("PROPERTY_NAME"))
{
obj = row["PROPERTY_NAME"];
if (obj != null)
{
name = obj.ToString();
}
}
String caption = String.Empty;
if (table.Columns.Contains("PROPERTY_CAPTION"))
{
obj = row["PROPERTY_CAPTION"];
if (obj != null)
{
caption = obj.ToString();
}
}
String description = String.Empty;
if (table.Columns.Contains("DESCRIPTION"))
{
obj = row["DESCRIPTION"];
if (obj != null)
{
description = obj.ToString();
}
}
int propertyType = 0;
if (table.Columns.Contains("PROPERTY_TYPE"))
{
obj = row["PROPERTY_TYPE"];
if (obj != null)
{
propertyType = Convert.ToInt32(obj);
}
}
LevelPropertyInfo lpi = new LevelPropertyInfo();
lpi.Caption = caption;
lpi.Description = description;
lpi.Name = name;
lpi.ParentLevelId = level.UniqueName;
//lpi.DataType = type;
if ((propertyType & 0x04) == 0x04)
{
lpi.IsSystem = true;
}
lpi.PropertyType = propertyType;
//info.LevelProperties.Add(lpi);
list.Add(lpi.Name, lpi);
}
}
}
//list.Add(info);
}
return(list);
}
finally {
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Level '{1}' Properties Completed ", DateTime.Now.ToString(), levelUniqueName);
}
}
public void StartTest()
{
try
{
Dictionary<Aggregation, long> dictAggRowCount = new Dictionary<Aggregation, long>();
Dictionary<AggregationDesign, long> dictAggDesignRowCount = new Dictionary<AggregationDesign, long>();
AdomdConnection conn = new AdomdConnection("Data Source=" + _currentAggD.ParentServer.Name + ";Initial Catalog=" + _currentAggD.ParentDatabase.Name);
conn.Open();
_sessionID = conn.SessionID;
if (_cancelled) return;
foreach (Partition p in _currentAggD.Parent.Partitions)
{
if (p.AggregationDesignID != _currentAggD.ID) continue;
RaiseProgressEvent(0, "Retrieving list of processed aggs in partition " + p.Name + "...");
AdomdRestrictionCollection coll = new AdomdRestrictionCollection();
coll.Add("DATABASE_NAME", _currentAggD.ParentDatabase.Name);
coll.Add("CUBE_NAME", _currentAggD.ParentCube.Name);
coll.Add("MEASURE_GROUP_NAME", p.Parent.Name);
coll.Add("PARTITION_NAME", p.Name);
DataSet aggDS = conn.GetSchemaDataSet("DISCOVER_PARTITION_STAT", coll);
foreach (DataRow row in aggDS.Tables[0].Rows)
{
if (!string.IsNullOrEmpty(Convert.ToString(row["AGGREGATION_NAME"])))
{
Aggregation a = p.AggregationDesign.Aggregations.FindByName(Convert.ToString(row["AGGREGATION_NAME"]));
if (a == null) throw new Exception("Couldn't find aggregation [" + row["AGGREGATION_NAME"] + "]");
long lngAggRowCount = Convert.ToInt64(row["AGGREGATION_SIZE"]);
if (lngAggRowCount > 0)
{
if (!dictAggRowCount.ContainsKey(a))
dictAggRowCount.Add(a, lngAggRowCount);
else
dictAggRowCount[a] += lngAggRowCount;
}
}
else
{
long lngPartitionRowCount = Convert.ToInt64(row["AGGREGATION_SIZE"]);
if (!dictAggDesignRowCount.ContainsKey(p.AggregationDesign ?? _emptyAggregationDesign))
dictAggDesignRowCount.Add(p.AggregationDesign ?? _emptyAggregationDesign, lngPartitionRowCount);
else
dictAggDesignRowCount[p.AggregationDesign ?? _emptyAggregationDesign] += lngPartitionRowCount;
}
if (_cancelled) return;
}
}
if (dictAggRowCount.Count == 0) return;
//figure out any DefaultMember that aren't the all member
string sDefaultMembersCalcs = "";
string sDefaultMembersCols = "";
foreach (MeasureGroupDimension mgd in _currentAggD.Parent.Dimensions)
{
RegularMeasureGroupDimension rmgd = mgd as RegularMeasureGroupDimension;
if (rmgd == null) continue;
foreach (MeasureGroupAttribute mga in rmgd.Attributes)
{
if (mga.CubeAttribute.AttributeHierarchyEnabled && mga.Attribute.AttributeHierarchyEnabled)
{
sDefaultMembersCalcs += "MEMBER [Measures].[|" + mga.CubeAttribute.Parent.Name + " | " + mga.CubeAttribute.Attribute.Name + "|] as iif([" + mga.CubeAttribute.Parent.Name + "].[" + mga.CubeAttribute.Attribute.Name + "].DefaultMember.Level.Name = \"(All)\", null, [" + mga.CubeAttribute.Parent.Name + "].[" + mga.CubeAttribute.Attribute.Name + "].DefaultMember.UniqueName)\r\n";
if (sDefaultMembersCols.Length > 0) sDefaultMembersCols += ",";
sDefaultMembersCols += "[Measures].[|" + mga.CubeAttribute.Parent.Name + " | " + mga.CubeAttribute.Attribute.Name + "|]\r\n";
}
}
}
RaiseProgressEvent(1, "Detecting DefaultMember on each dimension attribute...");
AdomdCommand cmd = new AdomdCommand();
cmd.Connection = conn;
cmd.CommandText = "with\r\n"
+ sDefaultMembersCalcs
+ "select {\r\n"
+ sDefaultMembersCols
+ "} on 0\r\n"
+ "from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]";
CellSet cs = cmd.ExecuteCellSet();
int iCol = 0;
_dictDefaultMembers.Clear();
foreach (MeasureGroupDimension mgd in _currentAggD.Parent.Dimensions)
{
RegularMeasureGroupDimension rmgd = mgd as RegularMeasureGroupDimension;
if (rmgd == null) continue;
foreach (MeasureGroupAttribute mga in rmgd.Attributes)
{
if (mga.CubeAttribute.AttributeHierarchyEnabled && mga.Attribute.AttributeHierarchyEnabled)
{
string sValue = Convert.ToString(cs.Cells[iCol++].Value);
if (!string.IsNullOrEmpty(sValue))
{
_dictDefaultMembers.Add(mga, sValue);
}
}
}
}
conn.Close(false);
if (_cancelled) return;
RaiseProgressEvent(2, "Starting trace...");
Server s = new Server();
s.Connect("Data Source=" + _currentAggD.ParentServer.Name, _sessionID);
Server sAlt = new Server();
sAlt.Connect("Data Source=" + _currentAggD.ParentServer.Name);
MeasureGroup mgAlt = sAlt.Databases.GetByName(_currentAggD.ParentDatabase.Name).Cubes.GetByName(_currentAggD.ParentCube.Name).MeasureGroups.GetByName(_currentAggD.Parent.Name);
try
{
Database db = s.Databases.GetByName(_currentAggD.ParentDatabase.Name);
string sTraceID = "BIDS Helper Aggs Performance Trace " + System.Guid.NewGuid().ToString();
_trc = s.Traces.Add(sTraceID, sTraceID);
_trc.OnEvent += new TraceEventHandler(trace_OnEvent);
_trc.Stopped += new TraceStoppedEventHandler(trace_Stopped);
_trc.AutoRestart = false;
TraceEvent te;
te = _trc.Events.Add(TraceEventClass.QueryEnd);
te.Columns.Add(TraceColumn.Duration);
te.Columns.Add(TraceColumn.SessionID);
te = _trc.Events.Add(TraceEventClass.GetDataFromAggregation);
te.Columns.Add(TraceColumn.ObjectPath);
te.Columns.Add(TraceColumn.TextData);
te.Columns.Add(TraceColumn.SessionID);
te.Columns.Add(TraceColumn.ConnectionID);
_trc.Update();
_trc.Start();
if (_cancelled) return;
s.BeginTransaction();
UnprocessOtherPartitions(s);
int i = 0;
if (_testAgg)
{
foreach (Aggregation a in dictAggRowCount.Keys)
{
RaiseProgressEvent(3 + (int)(87.0 * i++ / dictAggRowCount.Count / _totalIterations), "Testing performance with agg " + i + " of " + dictAggRowCount.Count + " (" + a.Name + ")...");
AggregationPerformance aggP = new AggregationPerformance(a);
aggP.AggregationRowCount = dictAggRowCount[a];
aggP.PartitionRowCount = dictAggDesignRowCount[a.Parent];
aggP.MeasureGroupRowCount = aggP.PartitionRowCount; //if there are multiple aggregation designs, outside code will fix that
ServerExecute(s, "<ClearCache xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " </ClearCache>");
_queryEnded = false;
//initialize the MDX script with a no-op query
ServerExecuteMDX(db, "with member [Measures].[_Exec MDX Script_] as null select [Measures].[_Exec MDX Script_] on 0 from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]", _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
aggP.ScriptPerformanceWithAgg = _queryDuration;
_queryEnded = false;
//don't clear dictHitAggs because if an agg got hit during the ExecuteMDXScript event, then it will be cached for the query
ServerExecuteMDX(db, aggP.PerformanceTestMDX, _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
aggP.QueryPerformanceWithAgg = _queryDuration;
if (_dictHitAggs.ContainsKey(a))
aggP.HitAggregation = true;
aggP.AggHits = _dictHitAggs;
_dictHitAggs = new Dictionary<Aggregation, int>();
_listAggPerf.Add(aggP);
if (_cancelled) return;
}
}
if (_testWithoutSomeAggs && _listAggPerf.Count > 0)
{
RaiseProgressEvent(4 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Dropping some aggs inside a transaction...");
//build list of all aggs which were hit, and which are contained within another agg
List<AggregationPerformance> allAggs = new List<AggregationPerformance>();
foreach (AggregationPerformance ap in _listAggPerf)
{
if (!ap.HitAggregation) continue;
foreach (AggregationPerformance ap2 in _listAggPerf)
{
if (ap.Aggregation != ap2.Aggregation && ap.Aggregation.Parent == ap2.Aggregation.Parent && SearchSimilarAggs.IsAggregationIncluded(ap.Aggregation, ap2.Aggregation, false))
{
allAggs.Add(ap);
break;
}
}
}
allAggs.Sort(delegate(AggregationPerformance a, AggregationPerformance b)
{
int iCompare = 0;
try
{
if (a == b || a.Aggregation == b.Aggregation) return 0;
iCompare = a.AggregationRowCount.CompareTo(b.AggregationRowCount);
if (iCompare == 0)
{
//if the aggs are the same rowcount, then sort by whether one is contained in the other
if (SearchSimilarAggs.IsAggregationIncluded(a.Aggregation, b.Aggregation, false))
return -1;
else if (SearchSimilarAggs.IsAggregationIncluded(b.Aggregation, a.Aggregation, false))
return 1;
else
return 0;
}
}
catch { }
return iCompare;
});
List<AggregationPerformance> deletedAggregationPerfs = new List<AggregationPerformance>();
List<AggregationPerformance> nextAggs = new List<AggregationPerformance>();
List<AggregationPerformance> aggsToSkipTesting = new List<AggregationPerformance>();
System.Diagnostics.Stopwatch timerProcessIndexes = new System.Diagnostics.Stopwatch();
long lngLastProcessIndexesTime = 0;
AggregationPerformance lastDeletedAggregationPerf = null;
while (allAggs.Count > 0)
{
AggregationPerformance aggP = null;
if (nextAggs.Count == 0)
{
aggP = allAggs[0];
allAggs.RemoveAt(0);
}
else
{
aggP = nextAggs[0];
nextAggs.RemoveAt(0);
allAggs.Remove(aggP);
}
deletedAggregationPerfs.Add(aggP);
//capture XMLA for deleting aggs
AggregationDesign aggD = mgAlt.AggregationDesigns.GetByName(aggP.Aggregation.Parent.Name);
aggD.ParentServer.CaptureXml = true;
foreach (AggregationPerformance ap in deletedAggregationPerfs)
{
if (aggD.Aggregations.ContainsName(ap.Aggregation.Name))
{
aggD.Aggregations.RemoveAt(aggD.Aggregations.IndexOfName(ap.Aggregation.Name));
}
}
aggD.Update(UpdateOptions.ExpandFull);
string sAlterXMLA = aggD.ParentServer.CaptureLog[0];
aggD.ParentServer.CaptureLog.Clear();
aggD.ParentServer.CaptureXml = false;
aggD.Refresh(true); //get the deleted aggs back
ServerExecute(s, sAlterXMLA);
if (_cancelled) return;
RaiseProgressEvent(5 + (int)(87.0 * i++ / dictAggRowCount.Count / _totalIterations), "Processing aggs without some aggs " + ((i - 1) % dictAggRowCount.Count + 1) + " of " + dictAggRowCount.Count + " (" + aggP.AggregationName + ")...");
timerProcessIndexes.Reset();
timerProcessIndexes.Start();
//process aggs to delete existing aggs
ServerExecute(s, "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Parallel>" + "\r\n"
+ " <Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " <MeasureGroupID>" + _currentAggD.Parent.ID + "</MeasureGroupID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " <Type>ProcessIndexes</Type>" + "\r\n"
+ " <WriteBackTableCreation>UseExisting</WriteBackTableCreation>" + "\r\n"
+ " </Process>" + "\r\n"
+ " </Parallel>" + "\r\n"
+ "</Batch>" + "\r\n");
if (!string.IsNullOrEmpty(_errors)) throw new Exception(_errors);
timerProcessIndexes.Stop();
//record time it took to process aggs... compare how long the prior one took, then you can determine how much incremental time was spent on the newly deleted agg
if (lastDeletedAggregationPerf != null)
lastDeletedAggregationPerf.ProcessIndexesDuration = lngLastProcessIndexesTime - timerProcessIndexes.ElapsedMilliseconds;
lngLastProcessIndexesTime = timerProcessIndexes.ElapsedMilliseconds;
lastDeletedAggregationPerf = aggP;
if (_cancelled) return;
int j = 0;
foreach (AggregationPerformance deleteAP in deletedAggregationPerfs)
{
RaiseProgressEvent(6 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Testing performance without some aggs " + ((i - 1) % dictAggRowCount.Count + 1) + " of " + dictAggRowCount.Count + "\r\nTesting agg " + (++j) + " of " + deletedAggregationPerfs.Count + " (" + deleteAP.AggregationName + ")...");
if (aggsToSkipTesting.Contains(deleteAP)) continue; //skip this agg if we've already determined it won't hit another agg
ServerExecute(s, "<ClearCache xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " </ClearCache>");
_queryEnded = false;
//initialize the MDX script with a no-op query
ServerExecuteMDX(db, "with member [Measures].[_Exec MDX Script_] as null select [Measures].[_Exec MDX Script_] on 0 from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]", _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
long lngScriptDuration = _queryDuration;
_queryEnded = false;
//don't clear dictHitAggs because if an agg got hit during the ExecuteMDXScript event, then it will be cached for the query
ServerExecuteMDX(db, deleteAP.PerformanceTestMDX, _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
long lngQueryDuration = _queryDuration;
List<Aggregation> deletedAggregations = new List<Aggregation>();
foreach (AggregationPerformance a in deletedAggregationPerfs)
{
deletedAggregations.Add(a.Aggregation);
}
MissingAggregationPerformance missingAggPerf = new MissingAggregationPerformance(deleteAP, deletedAggregations.ToArray(), _dictHitAggs);
_listMissingAggPerf.Add(missingAggPerf);
missingAggPerf.QueryPerformance = lngQueryDuration;
missingAggPerf.ScriptPerformance = lngScriptDuration;
foreach (Aggregation a in missingAggPerf.AggHitsDiff)
{
foreach (AggregationPerformance ap in allAggs)
{
if (ap.Aggregation == a && !nextAggs.Contains(ap))
nextAggs.Add(ap);
}
}
if (missingAggPerf.AggHitsDiff.Length == 0)
{
aggsToSkipTesting.Add(deleteAP);
}
else
{
bool bThisAggContainedInRemainingAgg = false;
foreach (AggregationPerformance ap2 in allAggs)
{
if (deleteAP.Aggregation != ap2.Aggregation && deleteAP.Aggregation.Parent == ap2.Aggregation.Parent && SearchSimilarAggs.IsAggregationIncluded(deleteAP.Aggregation, ap2.Aggregation, false))
{
bThisAggContainedInRemainingAgg = true;
break;
}
}
if (!bThisAggContainedInRemainingAgg)
aggsToSkipTesting.Add(deleteAP); //stop testing this agg when it's not contained in any remaining aggs that need to be tested
}
_dictHitAggs = new Dictionary<Aggregation, int>();
}
if (_cancelled) return;
s.RollbackTransaction();
s.BeginTransaction();
UnprocessOtherPartitions(s);
}
}
if (_testNoAggs)
{
//ensure the counter is where it's supposed to be since the "test with some aggs" test may not have done iterations for every agg
i = Math.Max(i, dictAggRowCount.Count * (_totalIterations - 1));
RaiseProgressEvent(4 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Dropping all aggs inside a transaction...");
//delete all aggs in all aggregation designs
string sXMLA = "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\" xmlns:as=\"http://schemas.microsoft.com/analysisservices/2003/engine\" xmlns:dwd=\"http://schemas.microsoft.com/DataWarehouse/Designer/1.0\">" + "\r\n"
+ " <Alter AllowCreate=\"true\" ObjectExpansion=\"ExpandFull\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.Parent.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.Parent.Parent.ID + "</CubeID>" + "\r\n"
+ " <MeasureGroupID>" + _currentAggD.Parent.ID + "</MeasureGroupID>" + "\r\n"
+ " <AggregationDesignID>" + _currentAggD.ID + "</AggregationDesignID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " <ObjectDefinition>" + "\r\n"
+ " <AggregationDesign>" + "\r\n"
+ " <ID>" + _currentAggD.ID + "</ID>" + "\r\n"
+ " <Name>" + _currentAggD.Name + "</Name>" + "\r\n"
+ " <Aggregations>" + "\r\n"
+ " </Aggregations>" + "\r\n"
+ " </AggregationDesign>" + "\r\n"
+ " </ObjectDefinition>" + "\r\n"
+ " </Alter>" + "\r\n"
+ "</Batch>" + "\r\n";
ServerExecute(s, sXMLA);
RaiseProgressEvent(5 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Processing empty aggregation design...");
if (_cancelled) return;
//process aggs to delete existing aggs
ServerExecute(s, "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Parallel>" + "\r\n"
+ " <Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " <MeasureGroupID>" + _currentAggD.Parent.ID + "</MeasureGroupID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " <Type>ProcessIndexes</Type>" + "\r\n"
+ " <WriteBackTableCreation>UseExisting</WriteBackTableCreation>" + "\r\n"
+ " </Process>" + "\r\n"
+ " </Parallel>" + "\r\n"
+ "</Batch>" + "\r\n");
if (!string.IsNullOrEmpty(_errors)) throw new Exception(_errors);
if (_cancelled) return;
foreach (AggregationPerformance aggP in _listAggPerf)
{
RaiseProgressEvent(10 + (int)(87.0 * i++ / dictAggRowCount.Count / _totalIterations), "Testing performance with no aggs " + ((i - 1) % dictAggRowCount.Count + 1) + " of " + dictAggRowCount.Count + " (" + aggP.AggregationName + ")...");
ServerExecute(s, "<ClearCache xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " </ClearCache>");
if (_cancelled) return;
_queryEnded = false;
//initialize the MDX script with a no-op query
ServerExecuteMDX(db, "with member [Measures].[_Exec MDX Script_] as null select [Measures].[_Exec MDX Script_] on 0 from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]", _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
aggP.ScriptPerformanceWithoutAggs = _queryDuration;
_queryEnded = false;
ServerExecuteMDX(db, aggP.PerformanceTestMDX, _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
aggP.QueryPerformanceWithoutAggs = _queryDuration;
}
} //end of testing with no aggs
RaiseProgressEvent(100, "Finished measure group " + _currentAggD.Parent.Name);
if (!string.IsNullOrEmpty(_errors)) throw new Exception(_errors);
}
finally
{
try
{
if (!s.Connected)
{
s.Connect("Data Source=" + _currentAggD.ParentServer.Name, _sessionID);
}
}
catch
{
try
{
if (!s.Connected)
{
s.Connect("Data Source=" + _currentAggD.ParentServer.Name); //can't connect to that session, so just reconnect
}
}
catch { }
}
try
{
s.RollbackTransaction();
}
catch { }
try
{
_trc.Drop();
}
catch { }
try
{
s.Disconnect();
}
catch { }
}
}
catch (Exception ex)
{
if (!_cancelled)
{
_errors += ex.Message + "\r\n" + ex.StackTrace + "\r\n";
System.Windows.Forms.MessageBox.Show(_errors);
}
}
}
public List <MeasureGroupInfo> GetMeasureGroups(String cubeName)
{
try
{
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Measures Groups List Started \r\n cubeName: '{1}'",
DateTime.Now.ToString(), cubeName);
Dictionary <String, MeasureGroupInfo> list = new Dictionary <String, MeasureGroupInfo>();
AdomdConnection conn = GetConnection();
AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
restrictions.Add("CATALOG_NAME", conn.Database);
if (!String.IsNullOrEmpty(cubeName))
{
restrictions.Add("CUBE_NAME", OlapHelper.ConvertToNormalStyle(cubeName));
}
#region Получение списка групп мер
DataSet ds = conn.GetSchemaDataSet("MDSCHEMA_MEASUREGROUPS", restrictions);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataTable table = ds.Tables[0];
if (ds.Tables[0].Columns.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
MeasureGroupInfo info = new MeasureGroupInfo();
if (table.Columns.Contains("CATALOG_NAME"))
{
if (row["CATALOG_NAME"] != null)
{
info.CatalogName = row["CATALOG_NAME"].ToString();
}
}
if (table.Columns.Contains("CUBE_NAME"))
{
if (row["CUBE_NAME"] != null)
{
info.CubeName = row["CUBE_NAME"].ToString();
}
}
if (table.Columns.Contains("MEASUREGROUP_NAME"))
{
if (row["MEASUREGROUP_NAME"] != null)
{
info.Name = row["MEASUREGROUP_NAME"].ToString();
}
}
if (table.Columns.Contains("DESCRIPTION"))
{
if (row["DESCRIPTION"] != null)
{
info.Description = row["DESCRIPTION"].ToString();
}
}
if (table.Columns.Contains("MEASUREGROUP_CAPTION"))
{
if (row["MEASUREGROUP_CAPTION"] != null)
{
info.Caption = row["MEASUREGROUP_CAPTION"].ToString();
}
}
if (table.Columns.Contains("IS_WRITE_ENABLED"))
{
if (row["IS_WRITE_ENABLED"] != null)
{
info.IsWriteEnabled = Convert.ToBoolean(row["IS_WRITE_ENABLED"]);
}
}
if (!list.ContainsKey(info.Name))
{
list.Add(info.Name, info);
}
}
}
}
#endregion Получение списка групп мер
#region Получение списка мер и распознавание для каких групп мер они относятся
ds = conn.GetSchemaDataSet("MDSCHEMA_MEASURES", restrictions);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataTable table = ds.Tables[0];
if (ds.Tables[0].Columns.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
String measuresGroupName = string.Empty;
if (table.Columns.Contains("MEASUREGROUP_NAME"))
{
if (row["MEASUREGROUP_NAME"] != null)
{
measuresGroupName = row["MEASUREGROUP_NAME"].ToString();
}
}
String measureUniqueName = String.Empty;
if (table.Columns.Contains("MEASURE_UNIQUE_NAME"))
{
if (row["MEASURE_UNIQUE_NAME"] != null)
{
measureUniqueName = row["MEASURE_UNIQUE_NAME"].ToString();
}
}
if (!String.IsNullOrEmpty(measuresGroupName) &&
!String.IsNullOrEmpty(measureUniqueName))
{
if (list.ContainsKey(measuresGroupName))
{
if (!list[measuresGroupName].Measures.Contains(measureUniqueName))
{
list[measuresGroupName].Measures.Add(measureUniqueName);
}
}
}
}
}
}
#endregion Получение списка мер и распознавание для каких групп мер они относятся
#region Получение списка KPI и распознавание для каких групп мер они относятся
ds = conn.GetSchemaDataSet("MDSCHEMA_KPIS", restrictions);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataTable table = ds.Tables[0];
if (ds.Tables[0].Columns.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
String kpiName = string.Empty;
if (table.Columns.Contains("KPI_NAME"))
{
if (row["KPI_NAME"] != null)
{
kpiName = row["KPI_NAME"].ToString();
}
}
String measuresGroupName = string.Empty;
if (table.Columns.Contains("MEASUREGROUP_NAME"))
{
if (row["MEASUREGROUP_NAME"] != null)
{
measuresGroupName = row["MEASUREGROUP_NAME"].ToString();
}
}
if (!String.IsNullOrEmpty(measuresGroupName) &&
!String.IsNullOrEmpty(kpiName))
{
if (list.ContainsKey(measuresGroupName))
{
if (!list[measuresGroupName].Kpis.Contains(kpiName))
{
list[measuresGroupName].Kpis.Add(kpiName);
}
}
}
}
}
}
#endregion Получение списка KPI и распознавание для каких групп мер они относятся
#region Получение списка измерений для каждой группы мер (MDSCHEMA_DIMENSIONS как оказалось не содержит информации о принадлежности измерения к группе мер - поэтому делаем несколько запросов MDSCHEMA_MEASUREGROUP_DIMENSIONS)
foreach (MeasureGroupInfo info in list.Values)
{
AdomdRestrictionCollection restrictions1 = new AdomdRestrictionCollection();
restrictions1.Add("CATALOG_NAME", conn.Database);
restrictions1.Add("CUBE_NAME", OlapHelper.ConvertToNormalStyle(cubeName));
restrictions1.Add("MEASUREGROUP_NAME", info.Name);
ds = conn.GetSchemaDataSet("MDSCHEMA_MEASUREGROUP_DIMENSIONS", restrictions1);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataTable table = ds.Tables[0];
if (ds.Tables[0].Columns.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
if (table.Columns.Contains("DIMENSION_UNIQUE_NAME"))
{
if (row["DIMENSION_UNIQUE_NAME"] != null)
{
String dimensionUniqueName = row["DIMENSION_UNIQUE_NAME"].ToString();
if (!String.IsNullOrEmpty(dimensionUniqueName))
{
if (!info.Dimensions.Contains(dimensionUniqueName))
{
info.Dimensions.Add(dimensionUniqueName);
}
}
}
}
}
}
}
}
#endregion Получение списка KPI и распознавание для каких групп мер они относятся
List <MeasureGroupInfo> result = new List <MeasureGroupInfo>();
foreach (MeasureGroupInfo info in list.Values)
{
result.Add(info);
}
return(result);
}
finally
{
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Measures Groups List Completed ",
DateTime.Now.ToString());
}
}
private void btnConnect_Click(object sender, EventArgs e)
{
try
{
cos.Clear();
if (this.ConnStr.ToLower().Contains("catalog"))
{
CubeOperate co = new CubeOperate(this.ConnStr);
cos.Add(co.Conn.Database, co);
}
else
{
AdomdConnection conn = new AdomdConnection(this.ConnStr);
conn.Open();
DataSet dsCatalogs = conn.GetSchemaDataSet("DBSCHEMA_CATALOGS", null);
conn.Close();
foreach (DataRow catalogRow in dsCatalogs.Tables[0].Rows.Cast<DataRow>())
{
string catalog = catalogRow[0].ToString();
string connStr = string.Format("{0};Catalog ={1};", this.ConnStr.EndsWith(";") ? this.ConnStr.Remove(this.ConnStr.Length - 1) : this.ConnStr, catalog);
cos.Add(catalog, new CubeOperate(connStr));
}
}
BindServerInfo();
}
catch (Exception ex)
{
this.ShowMessage(ex);
}
}