private void AddMeasures(CubeDef cube, TreeNode oCubeNode)
{
TreeNode oMeasuresNode = oCubeNode.Nodes.Add("Measure", "Measure", "Measure", "Measure");
DataSet dsMeasures = moCube.ParentConnection.GetSchemaDataSet(AdomdSchemaGuid.Measures, new object[0]);
foreach (Measure oMeasure in cube.Measures)
{
string sDisplayFolder = "";
bool bIsCalc = false;
GetMeasureInfo(oMeasure, dsMeasures, out sDisplayFolder, out bIsCalc);
TreeNode oDisplayFolder = GetDisplayFolder(oMeasuresNode, sDisplayFolder);
TreeNode oNode = oDisplayFolder.Nodes.Add(oMeasure.Caption);
oNode.Tag = oMeasure;
if (!bIsCalc)
{
oNode.ImageKey = "Measure";
oNode.SelectedImageKey = "Measure";
}
else
{
oNode.ImageKey = "CalcMeasure";
oNode.SelectedImageKey = "CalcMeasure";
}
}
}
public static string CreateDefaultQuery(CubeDef cube)
{
// find the first time dimension
Dimension oTimeDim = GetTimeDim(cube);
Dimension oOtherDim = GetFirstNonTimeDim(cube);
string oTimeMember = GetAllMember(oTimeDim);
string oOtherMember = GetAllMember(oOtherDim);
string sTime = oTimeMember + ".Children";
string sOther = oOtherMember + ".Children";
string sCube = cube.Name;
if (sCube[0] != '[')
sCube = "[" + sCube + "]";
string sMDX = mcsQuery.Replace("{0}", sTime);
sMDX = sMDX.Replace("{1}", sOther);
sMDX = sMDX.Replace("{2}", sCube);
//return string.Format(mcsQuery, sTime, sOther, cube.ToString());
return sMDX;
}
public static CubeDefInfo CreateCubeInfo(CubeDef cube)
{
if (cube == null)
return null;
CubeDefInfo info = new CubeDefInfo();
info.Caption = cube.Caption;
info.Description = cube.Description;
info.Name = cube.Name;
info.LastProcessed = cube.LastProcessed;
info.LastUpdated = cube.LastUpdated;
info.Type = (CubeInfoType)(cube.Type);
// Свойства
foreach (Property prop in cube.Properties)
{
//PropertyInfo pi = new PropertyInfo(prop.Name, prop.Type, prop.Value);
PropertyInfo pi = new PropertyInfo(prop.Name, prop.Value);
info.Properties.Add(pi);
}
return info;
}
private void BindCubeInfo(string catalogName, string cubeName)
{
TreeNode catalogNode = null;
#region 没有就新建一个CatalogNode
if (tvCubeInfo.Nodes[catalogName] == null)
{
tvCubeInfo.Nodes.Add(catalogName, catalogName, "Catalog", "Catalog");
}
catalogNode = tvCubeInfo.Nodes[catalogName];
catalogNode.Tag = cos[catalogName];
catalogNode.ToolTipText = string.Format("Catalog Name:[{0}]", catalogNode.Name);
#endregion
//重新绑定该Cube
#region 重新生成一个Cube节点
if (catalogNode.Nodes[cubeName] != null)
{
catalogNode.Nodes.RemoveByKey(cubeName);
}
CubeDef cubeDef = cos[catalogName].GetCube(cubeName);
catalogNode.Nodes.Add(cubeDef.Name, cubeDef.Caption, "Cube", "Cube");
catalogNode.Nodes[cubeDef.Name].Tag = cubeDef;
catalogNode.Nodes[cubeDef.Name].ToolTipText = string.Format("Cube Name:[{0}] Caption:[1]", cubeDef.Name, cubeDef.Caption);
#endregion
//在该Cube节点上添加Measures文件夹
catalogNode.Nodes[cubeDef.Name].Nodes.Add("Measures_", "Measures", "Measure", "Measure");
catalogNode.Nodes[cubeDef.Name].Expand();
catalogNode.Nodes[cubeDef.Name].ContextMenuStrip = cubeMenu;
BindMeasuresForCube(catalogNode.Nodes[cubeDef.Name].Nodes["Measures_"], cubeName, cos[catalogName]);
BindDimensionsForCube(catalogNode.Nodes[cubeDef.Name], cubeDef);
catalogNode.Expand();
}
private object GetSchemaObject(CubeDef cube, SchemaObjectType type, string uniqueName, bool throwNotFound)
{
try
{
return(cube == null ? null : cube.GetSchemaObject(type, uniqueName, true));
}
catch (Exception exc)
{
// object not found from 9.0 and 8.0 analysis services connections
if (exc.Message.Contains("object was not found") || exc.Message.StartsWith("Invalid syntax for schema"))
{
if (throwNotFound)
{
throw new Exception(string.Format("Member '{0}' not found in cube '{1}'", uniqueName, cube.Name));
}
return(null);
}
throw exc; // other exception
}
}
public string GetDimensions(string cubeName)
{
CubeDef cubeDef = GetCubeDef(cubeName);
StringBuilder strDimBldr = new StringBuilder("<ContentMenu>");
for (int i = 0; i < cubeDef.Dimensions.Count; i++)
{
// Write out the field or level names.
Dimension curDim = cubeDef.Dimensions[i];
string sFolderCaption = "";
sFolderCaption = curDim.Name;
if (curDim.Hierarchies[0].Name != "")
{
sFolderCaption += "." + curDim.Hierarchies[0].Name;
}
strDimBldr.Append("<MenuItem id=\"" + curDim.Hierarchies[0].UniqueName + "\" type=\"dimension\" dimension=\"" + curDim.UniqueName + "\" name=\"" + sFolderCaption + "\" />");
}
strDimBldr.Append("</ContentMenu>");
strDimBldr.Replace("&", "&");
return(strDimBldr.ToString());
//return doc.rer;
}
/// <summary>
/// Возвращает список KPI
/// </summary>
/// <param name="cubeName"></param>
/// <returns></returns>
public Dictionary <String, KpiInfo> GetKPIs(String cubeName)
{
try
{
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Kpis List Started \r\n cubeName: '{1}'",
DateTime.Now.ToString(), cubeName);
Dictionary <String, KpiInfo> list = new Dictionary <String, KpiInfo>();
CubeDef cube = FindCube(cubeName);
if (cube != null)
{
foreach (Kpi kpi in cube.Kpis)
{
KpiInfo info = InfoHelper.CreateKpiInfo(kpi);
if (info != null)
{
if (!list.ContainsKey(info.Name))
{
list.Add(info.Name, info);
}
}
}
}
else
{
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(list);
}
finally {
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Kpis List Completed \r\n", DateTime.Now.ToString());
}
}
public static object LookupAdomdSchemaObject(CubeDef cube, SchemaObjectType type, string uniqueName)
{
try
{
return(cube.GetSchemaObject(type, uniqueName, true));
}
catch (Exception exc)
{
// object not found from old analysis services connection
if (exc.Message.Contains("Invalid syntax for schema"))
{
return(null);
}
// object not found from new analysis services connection
if (exc.Message.Contains("object was not found"))
{
return(null);
}
throw exc;
}
}
public static CubeDefInfo CreateCubeInfo(CubeDef cube)
{
if (cube == null)
{
return(null);
}
CubeDefInfo info = new CubeDefInfo();
info.Caption = cube.Caption;
info.Description = cube.Description;
info.Name = cube.Name;
info.LastProcessed = cube.LastProcessed;
info.LastUpdated = cube.LastUpdated;
info.Type = (CubeInfoType)(cube.Type);
// Свойства
foreach (Property prop in cube.Properties)
{
//PropertyInfo pi = new PropertyInfo(prop.Name, prop.Type, prop.Value);
PropertyInfo pi = new PropertyInfo(prop.Name, prop.Value);
info.Properties.Add(pi);
}
return(info);
}
private List <FieldInfo> LoadFields()
{
//String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[OLAPConnector.OLAPConnectionString].ConnectionString;
String connectionString = "Data source=(local);Initial Catalog=SWordBI_SSAS";
//Dictionary<String, String> fields = new Dictionary<String, String>();
List <FieldInfo> fields = new List <FieldInfo>();
using (AdomdConnection conn = new AdomdConnection(connectionString))
{
conn.Open();
CubeDef cubeDef = null;
foreach (CubeDef cube in conn.Cubes)
{
if (cube.Type == CubeType.Cube)
{
cubeDef = cube;
break;
}
}
if (cubeDef == null)
{
return(fields);
}
DimensionCollection dimCollection = cubeDef.Dimensions;
foreach (Dimension dim in dimCollection)
{
HierarchyCollection hierarchyColl = dim.AttributeHierarchies;
foreach (Hierarchy h in hierarchyColl)
{
//fields.Add(h.UniqueName, h.Caption);
fields.Add(new FieldInfo(h.UniqueName, h.Caption));
}
}
}
return(fields);
}
private Dimension FindDimension(String cubeName, String dimensionUniqueName)
{
AdomdConnection conn = GetConnection();
CubeDef cube = FindCube(cubeName);
if (cube != null)
{
foreach (Dimension dim in cube.Dimensions)
{
if (dim.UniqueName.ToLower() == dimensionUniqueName.ToLower())
{
return(dim);
}
}
}
String str = String.Format(Localization.MetadataResponseException_DimensionByUniqueName_InCube_NotFound, dimensionUniqueName, 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 async Task GetMembersAsync2()
{
var connection = TestHelper.CreateConnectionToSsas();
await connection.OpenAsync();
CubeDef cube = await TestHelper.GetCubeAsync(connection);
//DimensionCollection dims = await cube.GetDimensionsAsync();
//var hiersTasks = dims.Select(d => d.GetHierarchiesAsync()).ToArray();
//var hierscols = await Task.WhenAll(hiersTasks);
//var hiers = hierscols.SelectMany(x => x);
//Debug.WriteLine(hiers.Count());
//var levelsTasks = hiers.Select(x => x.GetLevelsAsync()).ToArray();
//var levelscols = await Task.WhenAll(levelsTasks);
//var levels = levelscols.SelectMany(x => x);
//Debug.WriteLine(levels.Count());
var levels = cube.Dimensions.FindHierarchy("[Customer].[Customer Geography]")?
.Levels;
var memsTasks = levels.Select(x => x.GetMembersAsync()).ToArray();
var memscols = await Task.WhenAll(memsTasks);
var mems = memscols.SelectMany(x => x);
Debug.WriteLine(mems.Count());
Assert.IsTrue(mems.Count() > 0);
await connection.CloseAsync();
}
public MeasureInfo GetMeasure(String cubeName, String measureUniqueName)
{
try
{
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Measure '{1}' Started \r\n cubeName: '{2}'",
DateTime.Now.ToString(), measureUniqueName, cubeName);
if (String.IsNullOrEmpty(measureUniqueName))
{
return(null);
}
CubeDef cube = FindCube(cubeName);
if (cube != null)
{
foreach (Measure measure in cube.Measures)
{
MeasureInfo info = InfoHelper.CreateMeasureInfo(measure);
if (info != null)
{
if (info.UniqueName.ToLower() == measureUniqueName.ToLower())
{
return(info);
}
}
}
}
return(null);
}
finally
{
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Measure '{1}' Completed ",
DateTime.Now.ToString(), measureUniqueName, cubeName);
}
}
public string GetReportSchemaXml(string server, string database, string cube, ref string openNodesXml)
{
AdomdConnection conn = null;
try
{
// read openNodesXml
StringCollection openNodes = new StringCollection();
if (openNodesXml != null && openNodesXml != "")
{
XmlDocument openNodesDoc = new XmlDocument();
openNodesDoc.LoadXml(openNodesXml);
foreach (XmlElement el in openNodesDoc.GetElementsByTagName("ON"))
{
string s = el.GetAttribute("UN");
if (s != null && s != "" && !openNodes.Contains(s))
{
openNodes.Add(s);
}
}
}
// create connection
conn = this.GetConnection(server, database);
// create xml document
XmlDocument doc = new XmlDocument();
XmlElement rootEl = doc.CreateElement("SCHEMA");
doc.AppendChild(rootEl);
// get cube
CubeDef cubeDef = conn.Cubes[cube];
// dimensions
foreach (Dimension dim in cubeDef.Dimensions)
{
XmlElement dimEl = doc.CreateElement("D");
dimEl.SetAttribute("UN", dim.UniqueName);
dimEl.SetAttribute("N", dim.Name);
rootEl.AppendChild(dimEl);
// isopen attribute
if (openNodes.Contains(dim.UniqueName))
{
dimEl.SetAttribute("O", "1");
}
// hierarchies
foreach (Hierarchy hier in dim.Hierarchies)
{
XmlElement hierEl = doc.CreateElement("H");
hierEl.SetAttribute("UN", hier.UniqueName);
hierEl.SetAttribute("N", hier.Name);
dimEl.AppendChild(hierEl);
// levels
foreach (Level lev in hier.Levels)
{
XmlElement levEl = doc.CreateElement("L");
levEl.SetAttribute("UN", lev.UniqueName);
levEl.SetAttribute("N", lev.Name);
levEl.SetAttribute("LD", lev.LevelNumber.ToString());
hierEl.AppendChild(levEl);
// default member under highest level
if (lev.LevelNumber == 0)
{
// get default member by explicit property or as simply first member in level
Member defMem = null;
if (hier.DefaultMember != null && hier.DefaultMember != "")
{
defMem = (Member)this.GetSchemaObject(cubeDef, SchemaObjectType.ObjectTypeMember, hier.DefaultMember, false);
}
if (defMem == null && lev.MemberCount > 0)
{
defMem = lev.GetMembers(0, 1)[0];
}
if (defMem != null)
{
XmlElement defMemEl = doc.CreateElement("DM");
defMemEl.SetAttribute("UN", defMem.UniqueName);
defMemEl.SetAttribute("N", defMem.Name);
defMemEl.SetAttribute("CC", defMem.ChildCount.ToString());
defMemEl.SetAttribute("LD", defMem.LevelDepth.ToString());
levEl.AppendChild(defMemEl);
}
}
}
// hier isopen attibute
if (openNodes.Contains(hier.UniqueName))
{
hierEl.SetAttribute("O", "1");
}
// measures handled differently, always included regardless of isopen attribute
if (dim.DimensionType == DimensionTypeEnum.Measure)
{
MeasureCollection mems = cubeDef.Measures;
foreach (Measure m in mems)
{
AppendMeasure(hierEl, m, openNodes);
}
}
else // other
{
// only if hier is open
if (openNodes.Contains(hier.UniqueName))
{
// members recursive
MemberCollection mems = hier.Levels[0].GetMembers();
foreach (Member m in mems)
{
AppendMemberHierarchy(hierEl, m, openNodes);
}
}
}
}
}
// return
return(doc.OuterXml);
}
finally
{
// dispose resources
this.ReturnConnection();
}
}
/// <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 string GetTreeXML(CatalogClass cat, ConnectionClass con)
{
string cube = "PsiMedicaDemoCube";
StringBuilder strDimBldr = new StringBuilder("<ContentMenu>");
CubeDef cubeDef = cat.CubeDefs[cube];
Member mebr = cubeDef.Dimensions["Payor"].Hierarchies[0].Levels["(All)"].Members["HI-TECH CORP (HMO)"];
for (int i = 0; i < cubeDef.Dimensions.Count; i++)
{
// Write out the field or level names.
Dimension curDim = cubeDef.Dimensions[i];
string sFolderCaption = "";
for (int j = 0; j < curDim.Hierarchies.Count; j++)
{
sFolderCaption = curDim.Name;
if (curDim.Hierarchies[j].Name != "")
{
sFolderCaption += "." + curDim.Hierarchies[j].Name;
}
strDimBldr.Append("<MenuItem id=\"" + curDim.Hierarchies[j].UniqueName + "\" type=\"dimension\" dimension=\"" + curDim.UniqueName + "\" FieldList=\"1\" name=\"" + sFolderCaption + "\">");
// for (int l=0;l<curDim.Hierarchies[j].Levels.Count; l++)
// {
// strDimBldr.Append("<MenuItem id=\"" + curDim.Hierarchies[j].Levels[l].UniqueName + "\" type=\"level\" dimension=\"" + curDim.Hierarchies[j].UniqueName + "\" source=\"FieldList\" dragEnabled=\"1\" level=\"1\" name=\"" + curDim.Hierarchies[j].Levels[l].Caption + "\">");
// for (int k=0;k<curDim.Hierarchies[j].Levels[l].Members.Count; k++)
// {
// strDimBldr.Append("<MenuItem id=\"" + curDim.Hierarchies[j].Levels[l].Members[k].UniqueName + "\" type=\"member\" dimension=\"" + curDim.Hierarchies[j].UniqueName + "\" source=\"FieldList\" dragEnabled=\"1\" member=\"1\" name=\"" + curDim.Hierarchies[j].Levels[l].Members[k].Caption + "\" />");
// }
// strDimBldr.Append("</MenuItem>");
// }
strDimBldr.Append("</MenuItem>");
}
}
Recordset rst = null;
try
{
try
{
rst = (Recordset)con.GetType().InvokeMember(
"OpenSchema",
System.Reflection.BindingFlags.InvokeMethod,
null,
con,
new object[] { SchemaEnum.adSchemaSets, new object[] { "PsiMedcaCubes", null, cube } }
);
}
catch
{}
if (rst.RecordCount > 0)
{
// Write out the folder name for Named Sets
strDimBldr.Append("<MenuItem id=\"Named Sets\" name=\"Named Sets\">");
while (!rst.EOF)
{
// Write out the named set if the function CreateVirtualDimension is not included - this was the means
// by which 7.0 cubes create virtual dimensions and these should not be included twice.
if (rst.Fields["EXPRESSION"].Value.ToString().IndexOf("CreateVirtualDimension") < 0)
{
// The funny replace expression on the dimensions is to add square brackets for
// named sets with multiple dimensions.
strDimBldr.Append("<MenuItem id=\"set" + rst.Fields["SET_NAME"] + "\" type=\"set\" source=\"FieldList\" dragEnabled=\"1\" set=\"" + rst.Fields["EXPRESSION"] + "\" dimension=\"[" + Regex.Replace(rst.Fields["DIMENSIONS"].Value.ToString(), @",", "],[") + "]\" name=\"" + rst.Fields["SET_NAME"].Value.ToString() + "\"/>");
}
rst.MoveNext();
}
strDimBldr.Append("</MenuItem>");
}
rst = null;
}
catch (Exception ex)
{
// Reserved. Don\"t report the error for now. Most likely caused by a 7.0/2000 version problem.
strDimBldr.Append(ex.ToString());
}
strDimBldr.Append("</ContentMenu>");
System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
strDimBldr.Replace("&", "&");
return(strDimBldr.ToString());
}
private void FillTree(CubeDef cube)
{
this.Nodes.Clear();
try
{
if (cube != null)
{
TreeNode oCubeNode = this.Nodes.Add(cube.Caption);
oCubeNode.Tag = cube;
oCubeNode.ImageKey = "Cube";
oCubeNode.SelectedImageKey = "Cube";
if (cube.Dimensions == null) return;
AddMeasures(cube, oCubeNode);
foreach (Dimension oDim in cube.Dimensions)
{
TreeNode oDimNode = oCubeNode.Nodes.Add(oDim.Caption);
oDimNode.Tag = oDim;
{
oDimNode.ImageKey = "Dimension";
oDimNode.SelectedImageKey = "Dimension";
foreach (Hierarchy oHier in oDim.Hierarchies)
{
TreeNode oDisplayFolder = GetDisplayFolder(oDimNode, oHier.DisplayFolder);
if (oHier.HierarchyOrigin == HierarchyOrigin.AttributeHierarchy)
{
TreeNode oAttrHierNode = oDisplayFolder.Nodes.Add(oHier.Caption);
oAttrHierNode.Tag = oHier;
oAttrHierNode.ImageKey = "HierAttr";
oAttrHierNode.SelectedImageKey = "HierAttr";
LoadLevels(oHier, oAttrHierNode);
}
else
{
TreeNode oHierNode = oDisplayFolder.Nodes.Add(oHier.Caption);
oHierNode.Tag = oHier;
oHierNode.ImageKey = "Hier";
oHierNode.SelectedImageKey = "Hier";
LoadLevels(oHier, oHierNode);
}
}
}
}
TreeNode oSetsNode = oCubeNode.Nodes.Add("Named Sets");
oSetsNode.ImageKey = "Set";
oSetsNode.SelectedImageKey = "Set";
foreach (NamedSet oSet in cube.NamedSets)
{
TreeNode oNode = oSetsNode.Nodes.Add(oSet.Name);
oNode.Tag = oSet;
oNode.SelectedImageKey = "Set";
oNode.ImageKey = "Set";
}
TreeNode oKpiNode = oCubeNode.Nodes.Add("KPIs");
oKpiNode.ImageKey = "Kpi";
oKpiNode.SelectedImageKey = "Kpi";
foreach (Kpi okpi in cube.Kpis)
{
TreeNode oNode = oKpiNode.Nodes.Add(okpi.Caption);
oNode.Tag = okpi;
oNode.SelectedImageKey = "Kpi";
oNode.ImageKey = "Kpi";
TreeNode oKpiStatus = oNode.Nodes.Add("Status");
oKpiStatus.Tag = okpi;
oKpiStatus.SelectedImageKey = "Kpi";
oKpiStatus.ImageKey = "Kpi";
TreeNode oKpiValue = oNode.Nodes.Add("Value");
oKpiValue.Tag = okpi;
oKpiValue.SelectedImageKey = "Kpi";
oKpiValue.ImageKey = "Kpi";
TreeNode oKpiGoal = oNode.Nodes.Add("Goal");
oKpiGoal.Tag = okpi;
oKpiGoal.SelectedImageKey = "Kpi";
oKpiGoal.ImageKey = "Kpi";
}
oCubeNode.Expand();
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private static Dimension GetTimeDim(CubeDef cube)
{
foreach (Dimension oDim in cube.Dimensions)
{
if (oDim.DimensionType == DimensionTypeEnum.Time)
return oDim;
}
return null;
}
private static Dimension GetFirstNonTimeDim(CubeDef cube)
{
foreach (Dimension oDim in cube.Dimensions)
{
if ((oDim.DimensionType != DimensionTypeEnum.Time)
&& (oDim.DimensionType != DimensionTypeEnum.Measure))
return oDim;
}
return null;
}
// get cube dimensions
public void GetDimension(CubeDef objInputCube)
{
//Write out all dimensions, indented by a tab
foreach (Dimension objDim in objInputCube.Dimensions)
LDim.Add(objDim.Name);
}
public static object LookupAdomdSchemaObject(CubeDef cube, string objectType, string uniqueName)
{
SchemaObjectType type = GetSchemaObjectTypeFromString(objectType);
return(LookupAdomdSchemaObject(cube, type, uniqueName));
}
public string ValidateReportXml(string server, string database, string cube, ref string inReportXml)
{
AdomdConnection conn = null;
string uName = null;
object obj = null;
Hierarchy hier;
try
{
// read inReportXml
XmlDocument doc = new XmlDocument();
doc.LoadXml(inReportXml);
// create connection
conn = this.GetConnection(server, database);
// get cube
CubeDef cubeDef = conn.Cubes[cube];
// hierarchies
foreach (XmlElement hierEl in doc.GetElementsByTagName("H"))
{
bool checkHier = false;
uName = hierEl.GetAttribute("UN");
obj = this.GetSchemaObject(cubeDef, SchemaObjectType.ObjectTypeHierarchy, uName, false);
hier = obj as Hierarchy;
if (hier == null)
{
hierEl.SetAttribute("E", "1");
continue; // next hierarchy
}
// levels
foreach (XmlElement levEl in hierEl.GetElementsByTagName("L"))
{
uName = levEl.GetAttribute("UN");
checkHier = (levEl.GetAttribute("CH") == "1");
obj = this.GetSchemaObject(cubeDef, SchemaObjectType.ObjectTypeLevel, uName, false);
Level lev = (Level)obj;
if (lev == null || (checkHier && string.Compare(lev.ParentHierarchy.UniqueName, hier.UniqueName, true) != 0))
{
levEl.SetAttribute("E", "1");
}
}
// members
foreach (XmlElement memEl in hierEl.GetElementsByTagName("M"))
{
uName = memEl.GetAttribute("UN");
checkHier = (memEl.GetAttribute("CH") == "1");
bool isCalc = (memEl.GetAttribute("C") == "1");
// not checking calulated members
if (!isCalc)
{
obj = this.GetSchemaObject(cubeDef, SchemaObjectType.ObjectTypeMember, uName, false);
Member mem = (Member)obj;
if (mem == null || (checkHier && string.Compare(mem.ParentLevel.ParentHierarchy.UniqueName, hier.UniqueName, true) != 0))
{
memEl.SetAttribute("E", "1");
}
else
{
memEl.SetAttribute("N", mem.Name);
memEl.SetAttribute("CC", mem.ChildCount.ToString());
memEl.SetAttribute("LD", mem.LevelDepth.ToString());
}
}
}
// order tuple members
foreach (XmlElement otmEl in hierEl.GetElementsByTagName("OTM"))
{
uName = otmEl.GetAttribute("UN");
checkHier = (otmEl.GetAttribute("CH") == "1");
obj = this.GetSchemaObject(cubeDef, SchemaObjectType.ObjectTypeMember, uName, false);
Member mem = (Member)obj;
if (mem == null || (checkHier && string.Compare(mem.ParentLevel.ParentHierarchy.UniqueName, hier.UniqueName) != 0))
{
otmEl.SetAttribute("E", "1");
}
}
}
// return
return(doc.OuterXml);
}
finally
{
// dispose resources
this.ReturnConnection(false);
}
}
private void BindDimensionsForCube(TreeNode cubeNode, CubeDef cubeDef)
{
IEnumerable<Dimension> dimensions = cubeDef.Dimensions.Cast<Dimension>();
if (dimensions == null) return;
//删除除Measures之外的其他所有节点（即所有维度节点）
for (int i = cubeNode.Nodes.Count - 1; i >= 0; i--)
{
if (!cubeNode.Nodes[i].Name.Equals("Measures_", StringComparison.CurrentCultureIgnoreCase))
{
cubeNode.Nodes.RemoveAt(i);
}
}
foreach (var item in dimensions)
{
if (item.Name.Equals("Measures") || item.Caption.Equals("Measures")) continue;
string name = item.Name.Replace("$", "");
string caption = item.Caption.Replace("$", "");
TreeNode tempNode = new TreeNode(caption);
tempNode.Name = name;
bool visible = item.Properties["DIMENSION_IS_VISIBLE"].Value.Value<bool>(true);
tempNode.SelectedImageKey = tempNode.ImageKey = visible ? "Dim" : "Dim";
tempNode.ToolTipText = string.Format("Dimension Name:[{0}]Caption:[{1}]", item.Name, item.Caption);
tempNode.Tag = item;
BindHierarchies(tempNode, item.Hierarchies);
cubeNode.Nodes.Add(tempNode);
}
}
public override void Load_InitData()
{
if (Mode != ControlMode.New)
{
return;
}
ASPxComboBox cbbKeyField;
try
{
cbbKeyField = (ASPxComboBox)this.FindControl("cbbKeyField");
if (cbbKeyField == null)
{
return;
}
}
#if DEBUG
catch (Exception ex)
{
throw ex;
}
#else
catch ()
{
return;
}
#endif
// Load fields
String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["OLAPConnection"].ConnectionString;
using (AdomdConnection conn = new AdomdConnection(connectionString))
{
conn.Open();
// Find a cube
CubeDef cubeDef = null;
CubeCollection cubeCollection = conn.Cubes;
int numOfCube = cubeCollection.Count - 1;
for (; numOfCube > 0; numOfCube--)
{
cubeDef = cubeCollection[numOfCube];
if (cubeDef.Type == CubeType.Cube)
{
break;
}
}
if (this is wcNormalFilter ||
this is wcTimeFilter)
{
Olap.DimensionFieldInfoCollection fields = new Olap.DimensionFieldInfoCollection();
// Load dimension field
DimensionCollection dimCollection = cubeDef.Dimensions;
int numOfDim = dimCollection.Count;
for (int dimIndex = 0; dimIndex < numOfDim; dimIndex++)
{
Dimension dim = dimCollection[dimIndex];
if (this is wcNormalFilter)
{
if (dim.UniqueName == "[ARDimTime]" ||
dim.DimensionType == DimensionTypeEnum.Measure)
{
continue;
}
}
else if (this is wcNumFilter)
{
if (dim.DimensionType != DimensionTypeEnum.Measure)
{
continue;
}
}
else // Date
{
if (dim.UniqueName != "[ARDimTime]")
{
continue;
}
}
foreach (Hierarchy hier in dim.Hierarchies)
{
fields.Add(new Olap.DimensionFieldInfo(hier));
}
}
Helpers.SetDataSource(cbbKeyField, fields, "UniqueName", "Caption");
}
else // wcNumFilter
{
Olap.MeasureFieldInfoCollection fields = new Olap.MeasureFieldInfoCollection();
MeasureCollection measureCollection = cubeDef.Measures;
int numOfMeasure = measureCollection.Count;
for (int mIndex = 0; mIndex < numOfMeasure; mIndex++)
{
Measure m = measureCollection[mIndex];
fields.Add(new Olap.MeasureFieldInfo(m));
}
Helpers.SetDataSource(cbbKeyField, fields, "UniqueName", "Caption");
}
}
}
public string ValidateReportXml(string server, string database, string cube, ref string inReportXml)
{
AdomdConnection conn = null;
string uName = null;
object obj = null;
try
{
// read inReportXml
XmlDocument doc = new XmlDocument();
doc.LoadXml(inReportXml);
// create connection
conn = this.GetConnection(server, database);
// get cube
CubeDef cubeDef = conn.Cubes[cube];
// hierarchies
foreach (XmlElement hierEl in doc.GetElementsByTagName("H"))
{
uName = hierEl.GetAttribute("UN");
obj = this.GetSchemaObject(cubeDef, SchemaObjectType.ObjectTypeHierarchy, uName, false);
if (obj == null)
{
hierEl.SetAttribute("E", "1");
continue; // next hierarchy
}
// levels
foreach (XmlElement levEl in hierEl.GetElementsByTagName("L"))
{
uName = levEl.GetAttribute("UN");
obj = this.GetSchemaObject(cubeDef, SchemaObjectType.ObjectTypeLevel, uName, false);
if (obj == null)
{
levEl.SetAttribute("E", "1");
}
}
// members
foreach (XmlElement memEl in hierEl.GetElementsByTagName("M"))
{
uName = memEl.GetAttribute("UN");
string isCalc = memEl.GetAttribute("C");
// not checking calulated members
if (isCalc != "1")
{
obj = this.GetSchemaObject(cubeDef, SchemaObjectType.ObjectTypeMember, uName, false);
if (obj == null)
{
memEl.SetAttribute("E", "1");
}
else
{
Member mem = (Member)obj;
memEl.SetAttribute("N", mem.Name);
memEl.SetAttribute("CC", mem.ChildCount.ToString());
memEl.SetAttribute("LD", mem.LevelDepth.ToString());
}
}
}
// order tuple members
foreach (XmlElement otmEl in hierEl.GetElementsByTagName("OTM"))
{
uName = otmEl.GetAttribute("UN");
obj = this.GetSchemaObject(cubeDef, SchemaObjectType.ObjectTypeMember, uName, false);
if (obj == null)
{
otmEl.SetAttribute("E", "1");
}
}
}
// return
return(doc.OuterXml);
}
finally
{
// dispose resources
this.ReturnConnection();
}
}
private void Page_Load(object sender, System.EventArgs e)
{
AdomdConnection conn = new AdomdConnection(ConfigurationSettings.AppSettings["adomdConn"]);
conn.Open();
AdomdCommand comm = new AdomdCommand(@"SELECT {{[Age Range_Member].[Age Range].Members}*{[Gender_Employee].[Gender_Employee].Members}} on rows, {{[Measures].[Member Months],[Measures].[Allowed]} * {[Benefit Plan].[Benefit Plan Type].Members}} on columns from [RCBigMainCube]", conn);
CellSet cst = comm.ExecuteCellSet();
CubeDef def = conn.Cubes["RCBigMainCube"];
conn.Close();
Dimension dim = def.Dimensions["Age Range_Member"];
StringBuilder str = new StringBuilder(),
strTmp = new StringBuilder();
int axisRow = 1, axisCol = 0;
int i, j, k; // indexers
string tmpPrev, tmpCur; //temp string to hjold previous and current value
HierarchyCollection rowHierarchy = cst.Axes[axisRow].Set.Hierarchies,
colHierarchy = cst.Axes[axisCol].Set.Hierarchies;
TupleCollection rowTuples = cst.Axes[axisRow].Set.Tuples,
colTuples = cst.Axes[axisCol].Set.Tuples;
int rowHierCnt = rowHierarchy.Count,
colHierCnt = colHierarchy.Count,
rowTuplCnt = colTuples.Count,
colTuplCnt = colTuples.Count;
str.Append("<table class=\"tableStyle\" cellspacing=\"0\">");
/********************************************Write the column header*************************************************/
/***************Write col dimensions*****************/
str.Append("<tr nowrap class=\"trStyle\">");
for (j = 0; j < rowHierCnt; j++)
{
str.Append("<td nowrap class=\"tdStyle\"> </td>");
}
for (j = 0; j < colHierCnt; j++)
{
string dimName = colHierarchy[j].UniqueName;
str.Append("<td nowrap class=\"thStyle\"><b>");
if ("Measures".Equals(dimName))
{
str.Append(dimName);
}
else
{
str.Append(Regex.Match(colTuples[0].Members[j].LevelName, @"(?<=\]\.\[)[^\]]+(?=\]$)").Value);
}
str.Append("</b></td>");
}
str.Append("</tr>");
/***************Write col dimensions*****************/
for (i = 0; i < colHierCnt; i++)
{
str.Append("<tr nowrap class=\"trStyle\">");
for (j = 0; j < rowHierCnt; j++)
{
str.Append("<td nowrap class=\"tdStyle\"> </td>");
}
tmpPrev = string.Empty;
for (k = 0; k < colTuplCnt; k++)
{
tmpCur = colTuples[k].Members[i].Caption;
if (tmpPrev.Equals(tmpCur))
{
tmpCur = " ";
}
else
{
tmpPrev = tmpCur;
}
strTmp.Append("<td nowrap class=\"thStyle\"><b>");
strTmp.Append(tmpCur);
strTmp.Append("</b></td>");
}
str.Append("</tr>");
}
str.Append(strTmp.ToString());
/********************************************End of write the column header*************************************************/
for (i = 0; i < rowTuplCnt; i++)
{
str.Append("<tr nowrap class=\"trStyle\">");
tmpPrev = string.Empty;
for (j = 0; j < rowHierCnt; j++)
{
tmpCur = rowTuples[i].Members[j].Caption;
if (tmpPrev.Equals(tmpCur))
{
tmpCur = " ";
}
else
{
tmpPrev = tmpCur;
}
str.Append("<td nowrap class=\"thStyle\"><b>");
str.Append(tmpCur);
str.Append("</b></td>");
}
for (k = 0; k < colTuplCnt; k++)
{
tmpCur = cst.Cells[k, i].FormattedValue;
str.Append("<td nowrap class=\"tdStyle\">");
str.Append((tmpCur.Length == 0)?" " : tmpCur);
str.Append("</td>");
}
str.Append("</tr>");
}
str.Append("<table/>");
Response.Write(str.ToString());
// conn.Close();
}