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);
}
}
private void AddColumnsToTable(XmlReader rdr
, ADOTabularTableCollection tables
, string eEntityType)
{
var eProperty = rdr.NameTable.Add("Property");
var eMeasure = rdr.NameTable.Add("Measure");
var eSummary = rdr.NameTable.Add("Summary");
var eStatistics = rdr.NameTable.Add("Statistics");
var eMinValue = rdr.NameTable.Add("MinValue");
var eMaxValue = rdr.NameTable.Add("MaxValue");
// this routine effectively processes and <EntityType> element and it's children
string caption = "";
string description = "";
bool isVisible = true;
string name = null;
string refName = "";
string tableId = "";
string dataType = "";
string contents = "";
string minValue = "";
string maxValue = "";
string formatString = "";
string keyRef = "";
long stringValueMaxLength = 0;
long distinctValueCount = 0;
bool nullable = true;
ADOTabularTable tab = null;
IFormatProvider invariantCulture = System.Globalization.CultureInfo.InvariantCulture;
List <ADOTabularVariation> _variations = new List <ADOTabularVariation>();
KpiDetails kpi = new KpiDetails();
var colType = ADOTabularObjectType.Column;
while (!(rdr.NodeType == XmlNodeType.EndElement &&
rdr.LocalName == eEntityType))
{
//while (rdr.NodeType == XmlNodeType.Whitespace)
//{
// rdr.Read();
//}
if (rdr.NodeType == XmlNodeType.Element &&
rdr.Name == eEntityType)
{
while (rdr.MoveToNextAttribute())
{
switch (rdr.LocalName)
{
case "Name":
tableId = rdr.Value;
tab = tables.GetById(tableId);
break;
}
}
}
if (rdr.NodeType == XmlNodeType.Element &&
rdr.LocalName == "Key")
{
// TODO - store table Key
keyRef = GetKeyReference(rdr);
}
if (rdr.NodeType == XmlNodeType.Element &&
rdr.LocalName == "Hierarchy")
{
ProcessHierarchy(rdr, tab);
}
if (rdr.NodeType == XmlNodeType.Element &&
rdr.Name == "bi:EntityType")
{
// rdr.MoveToAttribute("Contents");
var contentAttr = rdr.GetAttribute("Contents");
bool isDateTable = contentAttr == "Time";
tab.IsDateTable = isDateTable;
}
if (rdr.NodeType == XmlNodeType.Element &&
rdr.LocalName == "DisplayFolder")
{
Debug.WriteLine("FoundFolder");
ProcessDisplayFolder(rdr, tab, tab);
}
if (rdr.NodeType == XmlNodeType.Element &&
rdr.LocalName == "Kpi")
{
kpi = ProcessKpi(rdr);
}
string defaultAggregateFunction;
if (rdr.NodeType == XmlNodeType.Element &&
(rdr.LocalName == eProperty ||
rdr.LocalName == eMeasure ||
rdr.LocalName == eSummary ||
rdr.LocalName == eStatistics ||
rdr.LocalName == eMinValue ||
rdr.LocalName == eMaxValue))
{
if (rdr.LocalName == eMeasure)
{
colType = ADOTabularObjectType.Measure;
}
if (rdr.LocalName == eSummary)
{
description = rdr.ReadElementContentAsString();
}
while (rdr.MoveToNextAttribute())
{
switch (rdr.LocalName)
{
case "Name":
refName = rdr.Value;
break;
case "ReferenceName": // reference name will always come after the Name and will override it if present
name = rdr.Value;
break;
case "Type":
dataType = rdr.Value;
break;
case "Caption":
caption = rdr.Value;
break;
case "Contents":
contents = rdr.Value;
break;
case "Hidden":
isVisible = !bool.Parse(rdr.Value);
break;
case "Description":
description = rdr.Value;
break;
case "DistinctValueCount":
distinctValueCount = long.Parse(rdr.Value, invariantCulture);
break;
case "StringValueMaxLength":
stringValueMaxLength = long.Parse(rdr.Value, invariantCulture);
break;
case "FormatString":
formatString = rdr.Value;
break;
case "DefaultAggregateFunction":
defaultAggregateFunction = rdr.Value;
break;
case "Nullable":
nullable = bool.Parse(rdr.Value);
break;
// Precision Scale
//TODO - Add RowCount
}
}
}
if (rdr.NodeType == XmlNodeType.Element &&
rdr.LocalName == "Variations")
{
_variations = ProcessVariations(rdr);
}
if (rdr.NodeType == XmlNodeType.EndElement &&
rdr.LocalName == eProperty &&
rdr.LocalName == "Property")
{
if (caption.Length == 0)
{
caption = refName;
}
if (!string.IsNullOrWhiteSpace(caption))
{
if (kpi.IsBlank())
{
var col = new ADOTabularColumn(tab, refName, name, caption, description, isVisible, colType, contents)
{
DataType = Type.GetType($"System.{dataType}"),
Nullable = nullable,
MinValue = minValue,
MaxValue = maxValue,
DistinctValues = distinctValueCount,
FormatString = formatString,
StringValueMaxLength = stringValueMaxLength
};
col.Variations.AddRange(_variations);
tables.Model.AddRole(col);
tab.Columns.Add(col);
_conn.Columns.Add(col.OutputColumnName, col);
}
else
{
colType = ADOTabularObjectType.KPI;
var kpiCol = new ADOTabularKpi(tab, refName, name, caption, description, isVisible, colType, contents, kpi)
{
DataType = Type.GetType($"System.{dataType}")
};
tab.Columns.Add(kpiCol);
_conn.Columns.Add(kpiCol.OutputColumnName, kpiCol);
}
}
// reset temp column variables
kpi = new KpiDetails();
refName = "";
caption = "";
name = null;
description = "";
isVisible = true;
contents = "";
dataType = "";
stringValueMaxLength = -1;
formatString = "";
defaultAggregateFunction = "";
nullable = true;
colType = ADOTabularObjectType.Column;
_variations = new List <ADOTabularVariation>();
}
if (!rdr.Read())
{
break; // quit the read loop if there is no more data
}
}
// Set Key column
var keyCol = tab?.Columns.GetByPropertyRef(keyRef);
if (keyCol != null)
{
keyCol.IsKey = true;
}
//TODO - link up back reference to backing measures for KPIs
}
private void AddColumnsToTable(XmlReader rdr
, ADOTabularTableCollection tables
, string eEntityType)
{
var eProperty = rdr.NameTable.Add("Property");
var eMeasure = rdr.NameTable.Add("Measure");
var eSummary = rdr.NameTable.Add("Summary");
var eStatistics = rdr.NameTable.Add("Statistics");
var eMinValue = rdr.NameTable.Add("MinValue");
var eMaxValue = rdr.NameTable.Add("MaxValue");
// this routine effectively processes and <EntityType> element and it's children
string caption = "";
string description = "";
bool isVisible = true;
string name = null;
string refName = "";
string tableId = "";
string dataType = "";
string contents = "";
string minValue = "";
string maxValue = "";
string formatString = "";
string defaultAggregateFunction = "";
long stringValueMaxLength = 0;
long distinctValueCount = 0;
bool nullable = true;
KpiDetails kpi = new KpiDetails();
var colType = ADOTabularObjectType.Column;
while (!(rdr.NodeType == XmlNodeType.EndElement &&
rdr.LocalName == eEntityType))
{
if (rdr.NodeType == XmlNodeType.Element &&
rdr.LocalName == eEntityType)
{
while (rdr.MoveToNextAttribute())
{
switch (rdr.LocalName)
{
case "Name":
tableId = rdr.Value;
break;
}
}
}
if (rdr.NodeType == XmlNodeType.Element &&
rdr.LocalName == "Hierarchy")
{
ProcessHierarchy(rdr, tables.GetById(tableId), eEntityType);
}
if (rdr.NodeType == XmlNodeType.Element &&
rdr.LocalName == "DisplayFolder")
{
Debug.WriteLine("FoundFolder");
ProcessDiplayFolder(rdr, tables.GetById(tableId));
}
if (rdr.NodeType == XmlNodeType.Element &&
rdr.LocalName == "Kpi")
{
kpi = ProcessKpi(rdr, tables.GetById(tableId));
}
if (rdr.NodeType == XmlNodeType.Element &&
(rdr.LocalName == eProperty ||
rdr.LocalName == eMeasure ||
rdr.LocalName == eSummary ||
rdr.LocalName == eStatistics ||
rdr.LocalName == eMinValue ||
rdr.LocalName == eMaxValue))
{
if (rdr.LocalName == eMeasure)
{
colType = ADOTabularObjectType.Measure;
}
if (rdr.LocalName == eSummary)
{
description = rdr.ReadElementContentAsString();
}
while (rdr.MoveToNextAttribute())
{
switch (rdr.LocalName)
{
case "Name":
refName = rdr.Value;
break;
case "ReferenceName": // reference name will always come after the Name and will override it if present
name = rdr.Value;
break;
case "Type":
dataType = rdr.Value;
break;
case "Caption":
caption = rdr.Value;
break;
case "Contents":
contents = rdr.Value;
break;
case "Hidden":
isVisible = !bool.Parse(rdr.Value);
break;
case "Description":
description = rdr.Value;
break;
case "DistinctValueCount":
distinctValueCount = long.Parse(rdr.Value);
break;
case "StringValueMaxLength":
stringValueMaxLength = long.Parse(rdr.Value);
break;
case "FormatString":
formatString = rdr.Value;
break;
case "DefaultAggregateFunction":
defaultAggregateFunction = rdr.Value;
break;
case "Nullable":
nullable = bool.Parse(rdr.Value);
break;
// Precision Scale
//TODO - Add RowCount
}
}
}
if (rdr.NodeType == XmlNodeType.EndElement &&
rdr.LocalName == eProperty &&
rdr.LocalName == "Property")
{
if (caption.Length == 0)
{
caption = refName;
}
if (!string.IsNullOrWhiteSpace(caption))
{
var tab = tables.GetById(tableId);
if (kpi.IsBlank())
{
var col = new ADOTabularColumn(tab, refName, name, caption, description, isVisible, colType, contents);
col.DataType = Type.GetType(string.Format("System.{0}", dataType));
col.Nullable = nullable;
col.MinValue = minValue;
col.MaxValue = maxValue;
col.DistinctValues = distinctValueCount;
col.FormatString = formatString;
col.StringValueMaxLength = stringValueMaxLength;
tab.Columns.Add(col);
_conn.Columns.Add(col.OutputColumnName, col);
}
else
{
colType = ADOTabularObjectType.KPI;
var kpiCol = new ADOTabularKpi(tab, refName, name, caption, description, isVisible, colType, contents, kpi);
kpiCol.DataType = Type.GetType(string.Format("System.{0}", dataType));
tab.Columns.Add(kpiCol);
_conn.Columns.Add(kpiCol.OutputColumnName, kpiCol);
}
}
// reset temp variables
kpi = new KpiDetails();
refName = "";
caption = "";
name = null;
description = "";
isVisible = true;
contents = "";
dataType = "";
stringValueMaxLength = -1;
formatString = "";
defaultAggregateFunction = "";
nullable = true;
colType = ADOTabularObjectType.Column;
}
rdr.Read();
}
//TODO - link up back reference to backing measures for KPIs
}
public void GenerateTablesFromXmlReader(ADOTabularTableCollection tabs, XmlReader rdr)
{
if (tabs == null)
{
throw new ArgumentNullException(nameof(tabs));
}
if (rdr == null)
{
throw new ArgumentNullException(nameof(rdr));
}
// clear out the flat cache of column names
_conn.Columns.Clear();
if (rdr.NameTable == null)
{
return;
}
var eEntityContainer = rdr.NameTable.Add("EntityContainer");
var eEntitySet = rdr.NameTable.Add("EntitySet");
var eEntityType = rdr.NameTable.Add("EntityType");
var eAssociationSet = rdr.NameTable.Add("AssociationSet");
while (rdr.Read())
{
if (rdr.NodeType == XmlNodeType.Element)
{
switch (rdr.LocalName)
{
case "EntityContainer":
if (rdr.NamespaceURI == @"http://schemas.microsoft.com/sqlbi/2010/10/edm/extensions")
{
UpdateDatabaseAndModelFromEntityContainer(rdr, tabs, eEntityContainer);
}
break;
case "EntitySet":
var tab = BuildTableFromEntitySet(rdr, eEntitySet);
tabs.Add(tab);
break;
case "EntityType":
AddColumnsToTable(rdr, tabs, eEntityType);
break;
case "AssociationSet":
BuildRelationshipFromAssociationSet(rdr, tabs, eAssociationSet);
break;
case "Association":
UpdateRelationshipFromAssociation(rdr, tabs);
break;
}
}
}
// post processing of metadata
foreach (var t in tabs)
{
TagKpiComponentColumns(t);
}
}
