/// <summary>
/// Updates the Olap_Periods table. Checks if the max year is equal to the current year + 2 and if it is not, updates the periods
/// </summary>
private void UpdateOlapPeriods()
{
object connectionManager = SessionManager.GetConnectionManager(this);
if (connectionManager == null)
{
throw new IndException("Connection Manager is null!");
}
OlapHelper olapHelper = new OlapHelper(connectionManager);
olapHelper.GetEntity().ExecuteCustomProcedure("UpdateOlapPeriods", olapHelper);
}
public String GenerateFromSet(String cubeName, String subCube)
{
String from_Set = String.Empty;
if (String.IsNullOrEmpty(subCube))
{
from_Set = OlapHelper.ConvertToQueryStyle(cubeName);
}
else
{
from_Set = OlapHelper.ConvertSubCubeToQueryStyle(subCube);
}
return(from_Set);
}
String BuildGetMembers()
{
StringBuilder builder = new StringBuilder();
if (m_CurrentTupleItem != null && m_DevileryModeCombo.Combo.SelectedIndex >= 0)
{
builder.Append("SELECT TOPCOUNT({");
if (m_DevileryModeCombo.Combo.SelectedIndex == 0)
{
// Запросить дочерние
builder.AppendFormat("{0}.Children - {0}.DataMember", m_CurrentTupleItem.UniqueName);
}
if (m_DevileryModeCombo.Combo.SelectedIndex == 1)
{
// Запросить соседние
builder.AppendFormat("{0}.Siblings - {0}", m_CurrentTupleItem.UniqueName);
}
if (m_DevileryModeCombo.Combo.SelectedIndex == 2)
{
// Запросить выбранные
builder.AppendFormat("{0}", m_MembersChoice.SelectedSet);
}
builder.Append("}, 1000)");
builder.AppendFormat(" on 0 FROM {0} ", OlapHelper.ConvertToQueryStyle(CubeName));
// Условие WHERE
String where = String.Empty;
IDictionary<String, MemberInfo> tuple = m_Cell.GetTuple();
foreach (MemberInfo member in tuple.Values)
{
if (member.UniqueName != m_CurrentTupleItem.UniqueName)
{
if (!String.IsNullOrEmpty(where))
{
where = where + ", ";
}
where = where + member.UniqueName;
}
}
if (!String.IsNullOrEmpty(where))
{
builder.AppendFormat("WHERE ({0})", where);
}
// дочерние select {[Персона].[Персонал].&[0x806600000000024F].Children - [Персона].[Персонал].&[0x806600000000024F].DataMember} on 0 from [Бюджет] where ([Сценарий].[Сценарии].&[План], [Период].[ГКМ].[Год].&[2008], [Measures].[Сумма], [Статья].[Статьи].&[и_Ресурсы_Загрузка], [ЦФО].[Менеджмент].&[У-5], [Контрагент].[Контрагенты].[Все контрагенты], [Проект].[Проекты].[Все проекты], [Договор].[Договоры].[Все договоры], [Подразделение].[Подразделения].[Все подразделения], [Номенклатура].[Вид-Группа-Номенклатура].[Вся номенклатура], [Бизнес-процесс].[Бизнес-процессы].[Все бизнес-процессы], [Вид Деятельности].[Вид-Группа-Деятельность].[Вид].&[Технологические работы])
// соседние select {[Персона].[Персонал].&[0x806600000000019E].Siblings - [Персона].[Персонал].&[0x806600000000019E]} on 0 from [Бюджет] where ([Сценарий].[Сценарии].&[План], [Период].[ГКМ].[Год].&[2008], [Measures].[Сумма], [Статья].[Статьи].&[и_Ресурсы_Загрузка], [ЦФО].[Менеджмент].&[У-5], [Контрагент].[Контрагенты].[Все контрагенты], [Проект].[Проекты].[Все проекты], [Договор].[Договоры].[Все договоры], [Подразделение].[Подразделения].[Все подразделения], [Номенклатура].[Вид-Группа-Номенклатура].[Вся номенклатура], [Бизнес-процесс].[Бизнес-процессы].[Все бизнес-процессы], [Вид Деятельности].[Вид-Группа-Деятельность].[Вид].&[Технологические работы])
}
return builder.ToString();
}
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);
}
}
public MemberDataWrapper GetMember(String cubeName, String memberUniqueName, List <LevelPropertyInfo> memberProperties)
{
StringBuilder builder = new StringBuilder();
StringBuilder members = new StringBuilder();
members.Append("{");
if (memberProperties != null && memberProperties.Count > 0)
{
// Формируем строку WITH MEMBER
builder.Append("WITH ");
int i = 0;
List <String> used_properties = new List <String>();
foreach (LevelPropertyInfo prop in memberProperties)
{
if (!used_properties.Contains(prop.Name))
{
String memberName = String.Format("[Measures].[-{0}-]", prop.Name);
if (i > 0)
{
members.Append(", ");
}
members.Append(memberName);
builder.AppendFormat("MEMBER {0} AS {1} ", memberName, String.Format("'{0}.Level.Hierarchy.CurrentMember.Properties(\"{1}\", TYPED)'", memberUniqueName, prop.Name));
i++;
used_properties.Add(prop.Name);
}
}
}
members.Append("}");
// Select
builder.AppendFormat("Select {0} on 0, {1} on 1 from {2}", memberUniqueName, members, OlapHelper.ConvertToQueryStyle(cubeName));
CellSetData cs_descr = GetDescription(builder.ToString());
List <MemberDataWrapper> list = GetMembers(cs_descr);
if (list != null && list.Count > 0)
{
return(list[0]);
}
return(null);
}
