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 UpdateServerProperties()
{
var res = new AdomdRestrictionCollection();
res.Add(new AdomdRestriction("ObjectExpansion", "ReferenceOnly"));
var props = _adomdConn.GetSchemaDataSet("DISCOVER_XML_METADATA", res, true);
var xmla = props.Tables[0].Rows[0][0].ToString();
var xdoc = new XmlDocument();
var oMgr = new XmlNamespaceManager(xdoc.NameTable);
oMgr.AddNamespace("d", "http://schemas.microsoft.com/analysisservices/2003/engine");
oMgr.AddNamespace("ddl400", "http://schemas.microsoft.com/analysisservices/2012/engine/400");
xdoc.LoadXml(xmla);
try
{
_serverLocation = xdoc.SelectSingleNode("//ddl400:ServerLocation", oMgr).InnerText ?? "";
} catch
{
_serverLocation = "";
}
try
{
_serverEdition = xdoc.SelectSingleNode("//d:Edition", oMgr).InnerText ?? "";
} catch
{
_serverEdition = "";
}
}
public void Visit(ADOTabularTableCollection tables)
{
//var ret = new SortedDictionary<string, ADOTabularTable>();
var resColl = new AdomdRestrictionCollection
{
{"CUBE_NAME", tables.Model.Name },
{
"DIMENSION_VISIBILITY",
_conn.ShowHiddenObjects
? (int) (MdschemaVisibility.Visible | MdschemaVisibility.NonVisible)
: (int) (MdschemaVisibility.Visible)
}
};
DataTable dtTables = _conn.GetSchemaDataSet("MDSCHEMA_DIMENSIONS", resColl).Tables[0];
foreach (DataRow dr in dtTables.Rows)
{
tables.Add(
new ADOTabularTable(_conn, dr["DIMENSION_NAME"].ToString()
,dr["DIMENSION_NAME"].ToString()
,dr["DIMENSION_CAPTION"].ToString()
,dr["DESCRIPTION"].ToString()
,bool.Parse(dr["DIMENSION_IS_VISIBLE"].ToString())
)
);
}
}
public void WrapsGetSchemaDataSet()
{
var mockConn = new Mock <IAdomdConnection>();
var conn = new GlimpseAdomdConnection(mockConn.Object, new Mock <ITimedMessagePublisher>().Object);
var guid = Guid.NewGuid();
var restrictions = new object[] {};
var nativeRestrictions = new AdomdRestrictionCollection();
conn.GetSchemaDataSet(guid, restrictions);
mockConn.Verify(p => p.GetSchemaDataSet(guid, restrictions), Times.Once);
conn.GetSchemaDataSet(guid, restrictions, true);
mockConn.Verify(p => p.GetSchemaDataSet(guid, restrictions, true), Times.Once);
conn.GetSchemaDataSet(guid.ToString(), nativeRestrictions);
mockConn.Verify(p => p.GetSchemaDataSet(guid.ToString(), nativeRestrictions), Times.Once);
conn.GetSchemaDataSet(guid.ToString(), nativeRestrictions, false);
mockConn.Verify(p => p.GetSchemaDataSet(guid.ToString(), nativeRestrictions, false), Times.Once);
conn.GetSchemaDataSet(guid.ToString(), "schemaNs", nativeRestrictions);
mockConn.Verify(p => p.GetSchemaDataSet(guid.ToString(), "schemaNs", nativeRestrictions), Times.Once);
conn.GetSchemaDataSet(guid.ToString(), "schemaNs", nativeRestrictions, false);
mockConn.Verify(p => p.GetSchemaDataSet(guid.ToString(), "schemaNs", nativeRestrictions, false), Times.Once);
}
public void Visit(ADOTabularTableCollection tables)
{
//var ret = new SortedDictionary<string, ADOTabularTable>();
var resColl = new AdomdRestrictionCollection
{
{ "CUBE_NAME", tables.Model.Name },
{
"DIMENSION_VISIBILITY",
_conn.ShowHiddenObjects
? (int)(MdschemaVisibility.Visible | MdschemaVisibility.NonVisible)
: (int)(MdschemaVisibility.Visible)
}
};
DataTable dtTables = _conn.GetSchemaDataSet("MDSCHEMA_DIMENSIONS", resColl).Tables[0];
foreach (DataRow dr in dtTables.Rows)
{
tables.Add(
new ADOTabularTable(_conn, dr["DIMENSION_NAME"].ToString()
, dr["DIMENSION_NAME"].ToString()
, dr["DIMENSION_CAPTION"].ToString()
, dr["DESCRIPTION"].ToString()
, bool.Parse(dr["DIMENSION_IS_VISIBLE"].ToString())
, false // Private
, false // ShowAsVariationsOnly
)
);
}
}
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 static SortedDictionary <string, ADOTabularMeasure> GetMdSchemaMeasures(ADOTabularMeasureCollection measures, IADOTabularConnection conn)
{
var ret = new SortedDictionary <string, ADOTabularMeasure>();
var resCollMeasures = new AdomdRestrictionCollection
{
{ "CATALOG_NAME", conn.Database.Name },
{ "CUBE_NAME", conn.Database.Models.BaseModel.Name },
{ "MEASUREGROUP_NAME", measures.Table.Caption },
{
"MEASURE_VISIBILITY",
conn.ShowHiddenObjects
? (int)(MdschemaVisibility.Visible | MdschemaVisibility.NonVisible)
: (int)(MdschemaVisibility.Visible)
}
};
DataTable dtMeasures = conn.GetSchemaDataSet("MDSCHEMA_MEASURES", resCollMeasures).Tables[0];
foreach (DataRow dr in dtMeasures.Rows)
{
ret.Add(dr["MEASURE_NAME"].ToString()
, new ADOTabularMeasure(measures.Table
, dr["MEASURE_NAME"].ToString()
, dr["MEASURE_NAME"].ToString()
, dr["MEASURE_CAPTION"].ToString()
, dr["DESCRIPTION"].ToString()
, bool.Parse(dr["MEASURE_IS_VISIBLE"].ToString())
, dr["EXPRESSION"].ToString()
)
);
}
return(ret);
}
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;
}
public DataSet GetSchemaDataSet(string schemaName, AdomdRestrictionCollection restrictions)
{
if (_type == AdomdType.AnalysisServices)
{
AsAdomdClient.AdomdRestrictionCollection coll = new AsAdomdClient.AdomdRestrictionCollection();
foreach (AdomdRestriction res in restrictions)
{
coll.Add(new AsAdomdClient.AdomdRestriction(res.Name, res.Value));
}
return(_conn.GetSchemaDataSet(schemaName, coll));
}
else
{
ExcelAdoMdConnections.ReturnDelegate <DataSet> f = delegate
{
ExcelAdomdClient.AdomdRestrictionCollection coll = new ExcelAdomdClient.AdomdRestrictionCollection();
foreach (AdomdRestriction res in restrictions)
{
coll.Add(new ExcelAdomdClient.AdomdRestriction(res.Name, res.Value));
}
return(_connExcel.GetSchemaDataSet(schemaName, coll));
};
return(f());
}
}
internal string GetMeasuresText()
{
if (this._database == null)
{
if (_isConnecting)
{
return("-- Reading metadata from the database. Please retry operation later.");
}
return("-- Not connected to the database.");
}
var sb = new StringBuilder();
var restrictions = new AdomdRestrictionCollection();
restrictions.Add("DatabaseID", _database.ID);
var dataSet = _adomdConn.GetSchemaDataSet("DISCOVER_XML_METADATA", restrictions);
Debug.Assert(dataSet.Tables.Count == 1);
var table = dataSet.Tables[0];
Debug.Assert(table.Columns.Count == 1);
Debug.Assert(table.Rows.Count == 1);
var script = table.Rows[0][0] as string;
Debug.Assert(!string.IsNullOrEmpty(script));
var mc = MeasuresContainer.ParseText(script);
sb.Append(mc.GetDaxText());
return(sb.ToString());
}
public DataSet GetSchemaDataSet(string schemaName, AdomdRestrictionCollection restrictionCollection, bool throwOnInlineErrors)
{
if (_adomdConn.State != ConnectionState.Open)
{
_adomdConn.Open();
}
return(_adomdConn.GetSchemaDataSet(schemaName, restrictionCollection, throwOnInlineErrors));
}
public DataSet GetSchemaDataSet(string schemaName, AdomdRestrictionCollection restrictionCollection)
{
if (_adomdConn.State != ConnectionState.Open)
{
_adomdConn.Open();
}
return(_adomdConn.GetSchemaDataSet(schemaName, restrictionCollection, true));
}
public Task <DataSet> GetSchemaDataSetAsync(string schemaName, AdomdRestrictionCollection restrictionCollection)
{
return(Task.Run(() =>
{
if (_adomdConn.State != ConnectionState.Open)
{
_adomdConn.Open();
}
return _adomdConn.GetSchemaDataSet(schemaName, restrictionCollection, true);
}));
}
private bool IsResellerSalesTable(AdomdRestrictionCollection res)
{
foreach (AdomdRestriction r in res)
{
if (r.Name == "TableID" && r.Value.ToString() == "1")
{
return(true);
}
}
return(false);
}
private bool IsResellerSalesMeasureGroup(AdomdRestrictionCollection res)
{
foreach (AdomdRestriction r in res)
{
if (r.Name == "MEASUREGROUP_NAME" && r.Value.ToString() == "Reseller Sales")
{
return(true);
}
}
return(false);
}
public SortedDictionary<string, ADOTabularModel> Visit(ADOTabularModelCollection models)
{
var ret = new SortedDictionary<string, ADOTabularModel>();
var resColl = new AdomdRestrictionCollection { { "CUBE_SOURCE", 1 } };
var dtModels = _conn.GetSchemaDataSet("MDSCHEMA_CUBES", resColl).Tables[0];
foreach (DataRow dr in dtModels.Rows)
{
ret.Add(dr["CUBE_NAME"].ToString()
, new ADOTabularModel(_conn, dr["CUBE_NAME"].ToString(), dr["CUBE_CAPTION"].ToString(), dr["DESCRIPTION"].ToString(), dr["CUBE_NAME"].ToString()));
}
return ret;
}
//</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));
}
void IMetaDataVisitor.Visit(ADOTabularTableCollection tables)
{
var resColl = new AdomdRestrictionCollection { { "CATALOG_NAME", _conn.Database.Name } };
// restrict the metadata to the selected perspective
if (tables.Model.IsPerspective)
resColl.Add(new AdomdRestriction("PERSPECTIVE_NAME",tables.Model.Name));
// if we are SQL 2012 SP1 or greater ask for v1.1 of the Metadata (includes KPI & Hierarchy information)
if (_conn.ServerVersion.VersionGreaterOrEqualTo("11.0.3368.0"))
resColl.Add(new AdomdRestriction("VERSION", "2.0"));
else if (_conn.ServerVersion.VersionGreaterOrEqualTo("11.0.3000.0")
|| (_conn.IsPowerPivot && _conn.ServerVersion.VersionGreaterOrEqualTo("11.0.2830.0")) )
resColl.Add(new AdomdRestriction("VERSION", "1.1"));
var ds = _conn.GetSchemaDataSet("DISCOVER_CSDL_METADATA", resColl);
string csdl = ds.Tables[0].Rows[0]["Metadata"].ToString();
/*
// debug code
using (StreamWriter outfile = new StreamWriter( @"d:\data\csdl.xml"))
{
outfile.Write(csdl);
}
*/
// get hierarchy structure
var hierResCol = new AdomdRestrictionCollection { { "CATALOG_NAME", _conn.Database.Name }, { "CUBE_NAME", _conn.Database.Models.BaseModel.Name }, { "HIERARCHY_VISIBILITY" , 3} };
var dsHier = _conn.GetSchemaDataSet("MDSCHEMA_HIERARCHIES", hierResCol);
_hierStructure = new Dictionary<string,Dictionary<string,string>>();
foreach (DataRow row in dsHier.Tables[0].Rows)
{
var dimUName = row["DIMENSION_UNIQUE_NAME"].ToString();
var dimName = dimUName.Substring(1,dimUName.Length-2); // remove square brackets
var hierName = row["HIERARCHY_NAME"].ToString();
Dictionary<string,string> hd;
if (!_hierStructure.ContainsKey(dimName))
{
hd = new Dictionary<string,string>();
_hierStructure.Add(dimName, hd);
}
else
{
hd = _hierStructure[dimName];
}
hd.Add(hierName, row["STRUCTURE_TYPE"].ToString());
}
using (XmlReader rdr = new XmlTextReader(new StringReader(csdl)))
{
GenerateTablesFromXmlReader(tables, rdr);
}
}
public SortedDictionary <string, ADOTabularColumn> Visit(ADOTabularColumnCollection columns)
{
var ret = new SortedDictionary <string, ADOTabularColumn>();
var resColl = new AdomdRestrictionCollection
{
{ "HIERARCHY_ORIGIN", 2 },
{ "CUBE_NAME", columns.Table.Model.Name },
{ "DIMENSION_UNIQUE_NAME", $"[{columns.Table.Caption}" },
{ "HIERARCHY_VISIBILITY", _conn.ShowHiddenObjects ? (int)(MdschemaVisibility.Visible | MdschemaVisibility.NonVisible) : (int)(MdschemaVisibility.Visible) }
};
DataTable dtHier = _conn.GetSchemaDataSet("MDSCHEMA_HIERARCHIES", resColl).Tables[0];
foreach (DataRow dr in dtHier.Rows)
{
ret.Add(dr[""].ToString()
, new ADOTabularColumn(columns.Table
, dr["HIERARCHY_NAME"].ToString()
, dr["HIERARCHY_NAME"].ToString()
, dr["HIERARCHY_CAPTION"].ToString()
, dr["DESCRIPTION"].ToString()
, bool.Parse(dr["HIERARCHY_IS_VISIBLE"].ToString())
, ADOTabularObjectType.Column
, "")
);
}
var resCollMeasures = new AdomdRestrictionCollection
{
{ "CUBE_NAME", columns.Table.Model.Name },
{ "MEASUREGROUP_NAME", columns.Table.Caption },
{
"MEASURE_VISIBILITY",
_conn.ShowHiddenObjects
? (int)(MdschemaVisibility.Visible | MdschemaVisibility.NonVisible)
: (int)(MdschemaVisibility.Visible)
}
};
DataTable dtMeasures = _conn.GetSchemaDataSet("MDSCHEMA_MEASURES", resCollMeasures).Tables[0];
foreach (DataRow dr in dtMeasures.Rows)
{
ret.Add(dr["MEASURE_NAME"].ToString()
, new ADOTabularColumn(columns.Table
, dr["MEASURE_NAME"].ToString()
, dr["MEASURE_NAME"].ToString()
, dr["MEASURE_CAPTION"].ToString()
, dr["DESCRIPTION"].ToString()
, bool.Parse(dr["MEASURE_IS_VISIBLE"].ToString())
, ADOTabularObjectType.Measure
, "")
);
}
return(ret);
}
public SortedDictionary<string, ADOTabularColumn> Visit(ADOTabularColumnCollection columns)
{
var ret = new SortedDictionary<string, ADOTabularColumn>();
var resColl = new AdomdRestrictionCollection
{
{"HIERARCHY_ORIGIN", 2},
{"CUBE_NAME", string.Format("{0}", columns.Table.Model.Name)},
{"DIMENSION_UNIQUE_NAME", string.Format("[{0}]", columns.Table.Caption)},
{"HIERARCHY_VISIBILITY", _conn.ShowHiddenObjects ? (int)(MdschemaVisibility.Visible | MdschemaVisibility.NonVisible) : (int)(MdschemaVisibility.Visible)}
};
DataTable dtHier = _conn.GetSchemaDataSet("MDSCHEMA_HIERARCHIES", resColl).Tables[0];
foreach (DataRow dr in dtHier.Rows)
{
ret.Add(dr[""].ToString()
, new ADOTabularColumn(columns.Table
,dr["HIERARCHY_NAME"].ToString()
,dr["HIERARCHY_NAME"].ToString()
, dr["HIERARCHY_CAPTION"].ToString()
,dr["DESCRIPTION"].ToString()
,bool.Parse(dr["HIERARCHY_IS_VISIBLE"].ToString())
,ADOTabularColumnType.Column
,"")
);
}
var resCollMeasures = new AdomdRestrictionCollection
{
{"CUBE_NAME", string.Format("{0}", columns.Table.Model.Name)},
{"MEASUREGROUP_NAME", string.Format("{0}", columns.Table.Caption)},
{
"MEASURE_VISIBILITY",
_conn.ShowHiddenObjects
? (int) (MdschemaVisibility.Visible | MdschemaVisibility.NonVisible)
: (int) (MdschemaVisibility.Visible)
}
};
DataTable dtMeasures = _conn.GetSchemaDataSet("MDSCHEMA_MEASURES", resCollMeasures).Tables[0];
foreach (DataRow dr in dtMeasures.Rows)
{
ret.Add(dr["MEASURE_NAME"].ToString()
, new ADOTabularColumn(columns.Table
,dr["MEASURE_NAME"].ToString()
,dr["MEASURE_NAME"].ToString()
, dr["MEASURE_CAPTION"].ToString()
,dr["DESCRIPTION"].ToString()
,bool.Parse(dr["MEASURE_IS_VISIBLE"].ToString())
,ADOTabularColumnType.Measure
,"")
);
}
return ret;
}
/// <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 DataTable QueryMondrianMembers(CubeOperate co, Dictionary <string, object> dic)
{
AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
foreach (var item in dic)
{
restrictions.Add(item.Key, item.Value);
}
if (co.Conn.State != ConnectionState.Open)
{
co.Conn.Open();
}
return(co.Conn.GetSchemaDataSet("MDSCHEMA_MEMBERS", restrictions).Tables[0]);
}
public SortedDictionary <string, ADOTabularModel> Visit(ADOTabularModelCollection models)
{
var ret = new SortedDictionary <string, ADOTabularModel>();
var resColl = new AdomdRestrictionCollection {
{ "CUBE_SOURCE", 1 }
};
var dtModels = _conn.GetSchemaDataSet("MDSCHEMA_CUBES", resColl).Tables[0];
foreach (DataRow dr in dtModels.Rows)
{
ret.Add(dr["CUBE_NAME"].ToString()
, new ADOTabularModel(_conn, models.Database, dr["CUBE_NAME"].ToString(), dr["CUBE_CAPTION"].ToString(), dr["DESCRIPTION"].ToString(), dr["CUBE_NAME"].ToString()));
}
return(ret);
}
private DataTable GetDiscoverResults(string requestType, NameValueCollection restrictions)
{
DataSet ds = null;
AdomdRestrictionCollection col = null;
// build collection of restrictions for the discover
if (restrictions != null && restrictions.Count > 0)
{
col = new AdomdRestrictionCollection();
NameValueCollection.KeysCollection keys = restrictions.Keys;
for (int i = 0; i < keys.Count; i++)
{
col.Add(new AdomdRestriction(keys[i], restrictions[keys[i]]));
}
}
// establish connection to the server
try
{
lock (this.connection)
{
if (!this.IsConnected)
{
this.Connect();
}
}
}
catch (System.Exception ex)
{
throw new ConnectionException("Error trying to open connection.", ex);
}
// execute discover command
try
{
lock (this.connection)
{
ds = this.connection.GetSchemaDataSet(requestType, col);
}
}
catch (System.Exception ex)
{
throw new ConnectionException("Error executing command on the server.", ex);
}
// return results of the discover
return(ds.Tables[0]);
}
private static SortedDictionary <string, ADOTabularMeasure> GetTmSchemaMeasures(ADOTabularMeasureCollection measures, IADOTabularConnection conn)
{
var resCollTables = new AdomdRestrictionCollection
{
{ "Name", measures.Table.Caption },
};
// need to look up the TableID in TMSCHEMA_TABLES
DataTable dtTables = conn.GetSchemaDataSet("TMSCHEMA_TABLES", resCollTables).Tables[0];
var tableId = dtTables.Rows[0].Field <System.UInt64>("ID");
var ret = new SortedDictionary <string, ADOTabularMeasure>();
var resCollMeasures = new AdomdRestrictionCollection
{
{ "DatabaseName", conn.Database.Name },
{ "TableID", tableId },
};
if (!conn.ShowHiddenObjects)
{
resCollMeasures.Add(new AdomdRestriction("IsHidden", false));
}
// then get all the measures for the current table
DataTable dtMeasures = conn.GetSchemaDataSet("TMSCHEMA_MEASURES", resCollMeasures).Tables[0];
foreach (DataRow dr in dtMeasures.Rows)
{
ret.Add(dr["Name"].ToString()
, new ADOTabularMeasure(measures.Table
, dr["Name"].ToString()
, dr["Name"].ToString()
, dr["Name"].ToString() // TODO - TMSCHEMA_MEASURES does not have a caption property
, dr["Description"].ToString()
, !bool.Parse(dr["IsHidden"].ToString())
, dr["Expression"].ToString()
)
);
}
return(ret);
}
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);
}
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);
}
/// <summary>
/// Extracts the list of DAX functions
/// </summary>
private List <DaxFunction> ExtractDaxFunctions()
{
// delete existing data
List <DaxFunction> daxFunctions = null;
try
{
LogLine("Start reading DAX functions.");
// create a new list of functions to be loaded
daxFunctions = new List <DaxFunction>();
// extract scalar functions
AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
restrictions.Add(FUNCTIONS_RESTRICTION_ORIGIN, FUNCTIONS_ORIGIN_SCALAR);
daxFunctions.AddRange(
ExtractDaxFunctionsWithRestrictions(restrictions)
);
// extract relational functions
restrictions.Clear();
restrictions.Add(FUNCTIONS_RESTRICTION_ORIGIN, FUNCTIONS_ORIGIN_RELATIONAL);
daxFunctions.AddRange(
ExtractDaxFunctionsWithRestrictions(restrictions)
);
_completionDataSnapshot = new CompletionDataSnapshot(_completionDataSnapshot, daxFunctions);
LogLine("End reading DAX functions.");
}
catch (Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException)
{
// If for some reason connection to the server cannot be established return null, it will be handled outside of this class
LogLine("Error reading DAX functions.");
}
return(daxFunctions);
}
public DataSet GetSchemaDataSet(string schemaName, string schemaNamespace, AdomdRestrictionCollection restrictions, bool throwOnInlineErrors)
{
throw new NotImplementedException();
}
private void LoadSchema(SchemaValues schema)
{
try
{
AdomdRestrictionCollection oList = new AdomdRestrictionCollection();
object[] oValues = new object[0];
//if (schema.Name == "Dimensions")
//{
// oValues = new object[2];
// oValues[0] = "CATALOG_NAME";
// oValues[1] = "Adventure Works";
//}
DataSet ds = moConn.GetSchemaDataSet(schema.Value, oValues);
dataGridView1.DataSource = ds;
dataGridView1.DataMember = ds.Tables[0].TableName;
}
catch (Exception ex)
{
MessageBox.Show("Unable to display values for that item, it probably requires restrictions\r\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void WrapsGetSchemaDataSet()
{
var mockConn = new Mock<IAdomdConnection>();
var conn = new GlimpseAdomdConnection(mockConn.Object, new Mock<ITimedMessagePublisher>().Object);
var guid = Guid.NewGuid();
var restrictions = new object[] {};
var nativeRestrictions = new AdomdRestrictionCollection();
conn.GetSchemaDataSet(guid, restrictions);
mockConn.Verify(p => p.GetSchemaDataSet(guid, restrictions), Times.Once);
conn.GetSchemaDataSet(guid, restrictions, true);
mockConn.Verify(p => p.GetSchemaDataSet(guid, restrictions, true), Times.Once);
conn.GetSchemaDataSet(guid.ToString(), nativeRestrictions);
mockConn.Verify(p => p.GetSchemaDataSet(guid.ToString(), nativeRestrictions), Times.Once);
conn.GetSchemaDataSet(guid.ToString(), nativeRestrictions, false);
mockConn.Verify(p => p.GetSchemaDataSet(guid.ToString(), nativeRestrictions, false), Times.Once);
conn.GetSchemaDataSet(guid.ToString(), "schemaNs", nativeRestrictions);
mockConn.Verify(p => p.GetSchemaDataSet(guid.ToString(), "schemaNs", nativeRestrictions), Times.Once);
conn.GetSchemaDataSet(guid.ToString(), "schemaNs", nativeRestrictions, false);
mockConn.Verify(p => p.GetSchemaDataSet(guid.ToString(), "schemaNs", nativeRestrictions, false), Times.Once);
}
public DataSet GetSchemaDataSet(string schemaName, AdomdRestrictionCollection restrictionCollection)
{
if (_adomdConn.State != ConnectionState.Open)
{
_adomdConn.Open();
}
return _adomdConn.GetSchemaDataSet(schemaName, restrictionCollection,true);
}
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 Task<DataSet> GetSchemaDataSetAsync(string schemaName, AdomdRestrictionCollection restrictionCollection)
{
return Task.Factory.StartNew(() =>
{
if (_adomdConn.State != ConnectionState.Open)
_adomdConn.Open();
return _adomdConn.GetSchemaDataSet(schemaName, restrictionCollection,true);
});
}
/// <inheritdoc />
public DataSet GetSchemaDataSet(string schemaName, string schemaNamespace, AdomdRestrictionCollection restrictions, bool throwOnInlineErrors)
{
return(_innerConnection.GetSchemaDataSet(schemaName, schemaNamespace, restrictions, throwOnInlineErrors));
}
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());
}
}
/// <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);
}
}
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();
}
/// <inheritdoc />
public DataSet GetSchemaDataSet(string schemaName, string schemaNamespace, AdomdRestrictionCollection restrictions, bool throwOnInlineErrors)
{
return _innerConnection.GetSchemaDataSet(schemaName, schemaNamespace, restrictions, throwOnInlineErrors);
}
/// <inheritdoc />
public DataSet GetSchemaDataSet(string schemaName, string schemaNamespace, AdomdRestrictionCollection restrictions)
{
return _innerConnection.GetSchemaDataSet(schemaName, schemaNamespace, restrictions);
}
public DataSet GetSchemaDataSet(string schemaName, string schemaNamespace, AdomdRestrictionCollection restrictions, bool throwOnInlineErrors)
{
throw new NotImplementedException();
}
public DataSet GetSchemaDataSet(string schemaName, AdomdRestrictionCollection restrictions)
{
throw new NotImplementedException();
}
public DataSet GetSchemaDataSet(string schemaName, AdomdRestrictionCollection restrictionCollection, bool throwOnInlineErrors)
{
if (_adomdConn.State != ConnectionState.Open)
{
_adomdConn.Open();
}
return _adomdConn.GetSchemaDataSet(schemaName, restrictionCollection, throwOnInlineErrors);
}
/// <inheritdoc />
public DataSet GetSchemaDataSet(string schemaName, string schemaNamespace, AdomdRestrictionCollection restrictions)
{
return(_innerConnection.GetSchemaDataSet(schemaName, schemaNamespace, restrictions));
}
private DataTable QueryMondrianMembers(CubeOperate co, Dictionary<string, object> dic)
{
AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
foreach (var item in dic)
{
restrictions.Add(item.Key, item.Value);
}
if (co.Conn.State != ConnectionState.Open)
{
co.Conn.Open();
}
return co.Conn.GetSchemaDataSet("MDSCHEMA_MEMBERS", restrictions).Tables[0];
}
void IMetaDataVisitor.Visit(ADOTabularTableCollection tables)
{
var resColl = new AdomdRestrictionCollection {
{ "CATALOG_NAME", _conn.Database.Name }
};
// restrict the metadata to the selected perspective
if (tables.Model.IsPerspective)
{
resColl.Add(new AdomdRestriction("PERSPECTIVE_NAME", tables.Model.Name));
}
// if we are SQL 2012 SP1 or greater ask for v1.1 of the Metadata (includes KPI & Hierarchy information)
if (_conn.ServerVersion.VersionGreaterOrEqualTo("11.0.3368.0"))
{
resColl.Add(new AdomdRestriction("VERSION", "2.0"));
}
else if (_conn.ServerVersion.VersionGreaterOrEqualTo("11.0.3000.0") ||
(_conn.IsPowerPivot && _conn.ServerVersion.VersionGreaterOrEqualTo("11.0.2830.0")))
{
resColl.Add(new AdomdRestriction("VERSION", "1.1"));
}
var ds = _conn.GetSchemaDataSet("DISCOVER_CSDL_METADATA", resColl);
string csdl = ds.Tables[0].Rows[0]["Metadata"].ToString();
/*
* // debug code
* using (StreamWriter outfile = new StreamWriter( @"d:\data\csdl.xml"))
* {
* outfile.Write(csdl);
* }
*/
// get hierarchy structure
var hierResCol = new AdomdRestrictionCollection {
{ "CATALOG_NAME", _conn.Database.Name }, { "CUBE_NAME", _conn.Database.Models.BaseModel.Name }, { "HIERARCHY_VISIBILITY", 3 }
};
var dsHier = _conn.GetSchemaDataSet("MDSCHEMA_HIERARCHIES", hierResCol);
_hierStructure = new Dictionary <string, Dictionary <string, string> >();
foreach (DataRow row in dsHier.Tables[0].Rows)
{
var dimUName = row["DIMENSION_UNIQUE_NAME"].ToString();
var dimName = dimUName.Substring(1, dimUName.Length - 2); // remove square brackets
var hierName = row["HIERARCHY_NAME"].ToString();
Dictionary <string, string> hd;
if (!_hierStructure.ContainsKey(dimName))
{
hd = new Dictionary <string, string>();
_hierStructure.Add(dimName, hd);
}
else
{
hd = _hierStructure[dimName];
}
hd.Add(hierName, row["STRUCTURE_TYPE"].ToString());
}
using (XmlReader rdr = new XmlTextReader(new StringReader(csdl)))
{
GenerateTablesFromXmlReader(tables, rdr);
}
}
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);
}
}
}
/// <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 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 DataSet GetSchemaDataSet(string schemaName, AdomdRestrictionCollection restrictions, bool throwOnInlineErrors)
{
if (_type == AdomdType.AnalysisServices)
{
global::Microsoft.AnalysisServices.AdomdClient.AdomdRestrictionCollection coll = new global::Microsoft.AnalysisServices.AdomdClient.AdomdRestrictionCollection();
if (restrictions != null)
{
foreach (AdomdClientWrappers.AdomdRestriction res in restrictions)
{
coll.Add(new global::Microsoft.AnalysisServices.AdomdClient.AdomdRestriction( res.Name, res.Value));
}
}
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
return _conn.GetSchemaDataSet(schemaName, coll, throwOnInlineErrors);
}
else
{
ExcelAdoMdConnections.ReturnDelegate<DataSet> f = delegate
{
ExcelAdomdClientReference::Microsoft.AnalysisServices.AdomdClient.AdomdRestrictionCollection coll = new ExcelAdomdClientReference::Microsoft.AnalysisServices.AdomdClient.AdomdRestrictionCollection();
if (restrictions != null)
{
foreach (AdomdRestriction res in restrictions)
{
coll.Add(new ExcelAdomdClientReference::Microsoft.AnalysisServices.AdomdClient.AdomdRestriction(res.Name, res.Value));
}
}
if (_connExcel.State != ConnectionState.Open)
{
_connExcel.Open();
}
return _connExcel.GetSchemaDataSet(schemaName, coll, throwOnInlineErrors);
};
return f();
}
}
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 DataTable GetDiscoverResults(string requestType, NameValueCollection restrictions)
{
DataSet ds = null;
AdomdRestrictionCollection col = null;
// build collection of restrictions for the discover
if (restrictions != null && restrictions.Count > 0)
{
col = new AdomdRestrictionCollection();
NameValueCollection.KeysCollection keys = restrictions.Keys;
for (int i = 0; i < keys.Count; i++)
{
col.Add(new AdomdRestriction(keys[i], restrictions[keys[i]]));
}
}
// establish connection to the server
try
{
lock (this.connection)
{
if (!this.IsConnected)
{
this.Connect();
}
}
}
catch (System.Exception ex)
{
throw new ConnectionException("Error trying to open connection.", ex);
}
// execute discover command
try
{
lock (this.connection)
{
ds = this.connection.GetSchemaDataSet(requestType, col);
}
}
catch (System.Exception ex)
{
throw new ConnectionException("Error executing command on the server.", ex);
}
// return results of the discover
return ds.Tables[0];
}