private CubeDef FindCube(String cubeName)
{
if (!String.IsNullOrEmpty(cubeName))
{
String name = OlapHelper.ConvertToNormalStyle(cubeName);
AdomdConnection conn = GetConnection();
foreach (CubeDef cube in conn.Cubes)
{
if (cube.Name.ToLower() == name.ToLower())
{
return(cube);
}
}
}
String str = String.Format(Localization.MetadataResponseException_CubeNotFound, cubeName, Connection.ConnectionID);
System.Diagnostics.Trace.TraceError("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider: {1} \r\n",
DateTime.Now.ToString(), cubeName, str);
throw new OlapMetadataResponseException(str);
//return null;
}
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);
}
}
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);
}
}