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 GenerateTablesFromXmlReader(ADOTabularTableCollection tabs, XmlReader rdr)
{
if (rdr.NameTable != null)
{
var eEntitySet = rdr.NameTable.Add("EntitySet");
var eEntityType = rdr.NameTable.Add("EntityType");
var eProperty = rdr.NameTable.Add("Property");
var eMeasure = rdr.NameTable.Add("Measure");
var eSummary = rdr.NameTable.Add("Summary");
var eKpi = rdr.NameTable.Add("Kpi");
while (rdr.Read())
{
if (rdr.NodeType == XmlNodeType.Element
&& rdr.LocalName == eEntitySet)
{
var tab = BuildTableFromEntitySet(rdr, eEntitySet);
tabs.Add(tab);
}
if (rdr.NodeType == XmlNodeType.Element
&& rdr.LocalName == eEntityType)
{
AddColumnsToTable(rdr, tabs, eEntityType, eProperty, eMeasure, eSummary);
}
}
foreach (var t in tabs)
{
TagKpiComponentColumns(t);
}
}
}
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 TestADOTabularCSDLVisitor()
{
ADOTabularConnection c = new ADOTabularConnection("Data Source=localhost", AdomdType.AnalysisServices);
MetaDataVisitorCSDL v = new MetaDataVisitorCSDL(c);
ADOTabularModel m = new ADOTabularModel(c, "Test","Test", "Test Description", "");
System.Xml.XmlReader xr = new System.Xml.XmlTextReader(@"..\..\data\csdl.xml");
var tabs = new ADOTabularTableCollection(c,m);
v.GenerateTablesFromXmlReader(tabs, xr);
Assert.AreEqual(4, tabs.Count);
Assert.AreEqual(8, tabs["Sales"].Columns.Count());
}
public void TestPowerBIModelCapabilities()
{
//ADOTabularConnection c = new ADOTabularConnection(ConnectionString, AdomdType.AnalysisServices);
//IADOTabularConnection c = new Mock<IADOTabularConnection>().Object;
MetaDataVisitorCSDL v = new MetaDataVisitorCSDL(connection);
ADOTabularDatabase db = new ADOTabularDatabase(connection, "Test", "Test", DateTime.Parse("2019-09-01 09:00:00"), "1200", "*");
ADOTabularModel m = new ADOTabularModel(connection, db, "Test", "Test", "Test Description", "");
System.Xml.XmlReader xr = new System.Xml.XmlTextReader(@"..\..\data\powerbi-csdl.xml");
var tabs = new ADOTabularTableCollection(connection, m);
v.GenerateTablesFromXmlReader(tabs, xr);
Assert.AreEqual(true, m.Capabilities.DAXFunctions.SubstituteWithIndex);
Assert.AreEqual(true, m.Capabilities.DAXFunctions.SummarizeColumns);
Assert.AreEqual(true, m.Capabilities.DAXFunctions.TreatAs);
Assert.AreEqual(true, m.Capabilities.Variables);
Assert.AreEqual(true, m.Capabilities.TableConstructor);
}
public void TestADOTabularCSDLVisitorKPI()
{
ADOTabularConnection c = new ADOTabularConnection(ConnectionString, AdomdType.AnalysisServices);
MetaDataVisitorCSDL v = new MetaDataVisitorCSDL(c);
System.Xml.XmlReader xr = new System.Xml.XmlTextReader(@"..\..\data\AdvWrks.xml");
ADOTabularModel m = new ADOTabularModel(c, "Test", "Test Caption", "Test Description", "");
var tabs = new ADOTabularTableCollection(c, m);
v.GenerateTablesFromXmlReader(tabs, xr);
Assert.AreEqual(15, tabs.Count);
Assert.AreEqual(24, tabs["Sales Territory"].Columns.Count());
Assert.AreEqual(1, tabs["Sales Territory"].Columns.Where((t) => t.ColumnType == ADOTabularColumnType.Hierarchy).Count());
var k = tabs["Sales Territory"].Columns["Total Current Quarter Sales Performance"] as ADOTabularKpi;
Assert.AreEqual("Total Current Quarter Sales Performance", k.Caption);
Assert.AreEqual("_Total Current Quarter Sales Performance Goal", k.Goal.Caption);
Assert.AreEqual("_Total Current Quarter Sales Performance Status", k.Status.Caption);
}
public void TestPowerBITomModel_CSDL_2_0()
{
//ADOTabularConnection c = new ADOTabularConnection(ConnectionString, AdomdType.AnalysisServices);
//IADOTabularConnection c = new Mock<IADOTabularConnection>().Object;
MetaDataVisitorCSDL v = new MetaDataVisitorCSDL(connection);
ADOTabularDatabase db = new ADOTabularDatabase(connection, "Test", "Test", DateTime.Parse("2019-09-01 09:00:00"), "1200", "*");
ADOTabularModel m = new ADOTabularModel(connection, db, "Test", "Test", "Test Description", "");
System.Xml.XmlReader xr = new System.Xml.XmlTextReader(@"..\..\data\powerbi-csdl.xml");
var tabs = new ADOTabularTableCollection(connection, m);
v.GenerateTablesFromXmlReader(tabs, xr);
Assert.IsNotNull(m.TOMModel);
Assert.AreEqual(13, m.TOMModel.Tables.Count, "Table Counts are equal");
Assert.AreEqual(tabs["ProductCategory"].Columns.Count, m.TOMModel.Tables["ProductCategory"].Columns.Count, "ProductCategory column counts are equal");
Assert.AreEqual(tabs["Sales"].Relationships.Count, m.TOMModel.Relationships.Count(r => r.FromTable.Name == "Sales"), "Sales table relationships are equal");
Assert.AreEqual(2, m.TOMModel.Tables["Sales"].Measures.Count, "Sales table measure counts are equal");
}
public void TestCSDLNestedMultipleDisplayFolders()
{
MetaDataVisitorCSDL v = new MetaDataVisitorCSDL(connection);
ADOTabularDatabase db = new ADOTabularDatabase(connection, "Test", "Test", DateTime.Parse("2019-09-01 09:00:00"), "1200", "*");
ADOTabularModel m = new ADOTabularModel(connection, db, "Test", "Test", "Test Description", "");
System.Xml.XmlReader xr = new System.Xml.XmlTextReader(@"..\..\data\NestedMultipleFoldersCsdl.xml");
var tabs = new ADOTabularTableCollection(connection, m);
v.GenerateTablesFromXmlReader(tabs, xr);
var cmpyTab = tabs["Table1"];
/* test folder structure should be:
*
* Folder 1
|- Measure
* Folder 2
+- Folder 3
+- Measure
*
*/
Assert.AreEqual("Table1", cmpyTab.Caption, "Table Name is correct");
Assert.AreEqual(2, cmpyTab.FolderItems.Count);
var folder1 = ((IADOTabularFolderReference)cmpyTab.FolderItems[0]);
Assert.AreEqual("Folder 1", folder1.Name);
Assert.AreEqual(1, folder1.FolderItems.Count);
Assert.AreEqual("Measure", folder1.FolderItems[0].InternalReference);
var folder2 = ((IADOTabularFolderReference)cmpyTab.FolderItems[1]);
Assert.AreEqual("Folder 2", folder2.Name);
var folder3 = ((IADOTabularFolderReference)folder2.FolderItems[0]);
Assert.AreEqual("Folder 3", folder3.Name);
Assert.AreEqual("Measure", folder3.FolderItems[0].InternalReference);
}
public void TestADOTabularCSDLVisitorHierarchies()
{
ADOTabularConnection c = new ADOTabularConnection(ConnectionString, AdomdType.AnalysisServices);
MetaDataVisitorCSDL v = new MetaDataVisitorCSDL(c);
System.Xml.XmlReader xr = new System.Xml.XmlTextReader(@"..\..\data\AdvWrks.xml");
ADOTabularModel m = new ADOTabularModel(c, "Test", "Test", "Test Description", "");
var tabs = new ADOTabularTableCollection(c, m);
v.GenerateTablesFromXmlReader(tabs, xr);
Assert.AreEqual(15, tabs.Count);
Assert.AreEqual(24, tabs["Sales Territory"].Columns.Count());
Assert.AreEqual(1, tabs["Sales Territory"].Columns.Where((t) => t.ColumnType == ADOTabularColumnType.Hierarchy).Count());
var h = (ADOTabularHierarchy)(tabs["Sales Territory"].Columns.Where((t) => t.ColumnType == ADOTabularColumnType.Hierarchy).First());
Assert.AreEqual(3, h.Levels.Count);
Assert.AreEqual("Group", h.Levels[0].LevelName);
Assert.AreEqual("Country", h.Levels[1].LevelName);
Assert.AreEqual("Region", h.Levels[2].LevelName);
}
public void TestPowerBIOrderByVisitor()
{
//ADOTabularConnection c = new ADOTabularConnection(ConnectionString, AdomdType.AnalysisServices);
//IADOTabularConnection c = new Mock<IADOTabularConnection>().Object;
MetaDataVisitorCSDL v = new MetaDataVisitorCSDL(connection);
ADOTabularDatabase db = new ADOTabularDatabase(connection, "Test", "Test", DateTime.Parse("2019-09-01 09:00:00"), "1200", "*");
ADOTabularModel m = new ADOTabularModel(connection, db, "Test", "Test", "Test Description", "");
System.Xml.XmlReader xr = new System.Xml.XmlTextReader(@"..\..\data\powerbi-csdl.xml");
var tabs = new ADOTabularTableCollection(connection, m);
v.GenerateTablesFromXmlReader(tabs, xr);
var localDateTable = tabs["LocalDateTable_697ceb23-7c16-46b1-a1ed-0100727de4c7"];
Assert.IsNotNull(localDateTable);
Assert.AreEqual(localDateTable.Columns["MonthNo"], localDateTable.Columns["Month"].OrderBy);
Assert.AreEqual(localDateTable.Columns["QuarterNo"], localDateTable.Columns["Quarter"].OrderBy);
Assert.IsNull(localDateTable.Columns["QuarterNo"].OrderBy);
Assert.IsNull(localDateTable.Columns["Year"].OrderBy);
}
public void TestADOTabularCSDLVisitorMeasures()
{
//var c = new ADOTabularConnection(ConnectionString, AdomdType.AnalysisServices);
var v = new MetaDataVisitorCSDL(connection);
ADOTabularDatabase db = GetTestDB();
var m = new ADOTabularModel(connection, db, "AdventureWorks", "AdventureWorks", "Test AdventureWorks", "");
var tabs = new ADOTabularTableCollection(connection, m);
System.Xml.XmlReader xr = new System.Xml.XmlTextReader(@"..\..\data\AdvWrks.xml");
//ADOTabularModel m = new ADOTabularModel(connection, "Test", "Test Caption", "Test Description", "");
//var tabs = new ADOTabularTableCollection(connection, m);
v.GenerateTablesFromXmlReader(tabs, xr);
//foreach (var table in tabs)
//{
// var measures = table.Measures;
//}
Assert.AreEqual(1, tabs["Reseller Sales"].Measures.Count, "There should be 1 measure populated by the mocks");
}
public void TestCSDLHierarchInDisplayFolders()
{
MetaDataVisitorCSDL v = new MetaDataVisitorCSDL(connection);
ADOTabularDatabase db = new ADOTabularDatabase(connection, "Test", "Test", DateTime.Parse("2019-09-01 09:00:00"), "1200", "*");
ADOTabularModel m = new ADOTabularModel(connection, db, "Test", "Test", "Test Description", "");
System.Xml.XmlReader xr = new System.Xml.XmlTextReader(@"..\..\data\HierInFolder.csdl");
var tabs = new ADOTabularTableCollection(connection, m);
v.GenerateTablesFromXmlReader(tabs, xr);
var tabDate = tabs["Date"];
//var cmpyCol = tabDate.FolderItems.Columns["Internet Current Quarter Sales"];
//var cmpyCol2 = cmpyTab.Columns["Internet Current Quarter Margin"];
Assert.AreEqual(1, tabDate.FolderItems.Count, "Table Name is correct");
Assert.AreEqual("Calendar Folder", tabDate.FolderItems[0].Name);
Assert.IsInstanceOfType(tabDate.Columns["Calendar"], typeof(ADOTabularHierarchy));
Assert.AreEqual(true, tabDate.Columns["Calendar"].IsInDisplayFolder);
//Assert.AreEqual(8, ((IADOTabularFolderReference)cmpyTab.FolderItems[0]).FolderItems.Count);
}
public void TestADOTabularCSDLVisitor()
{
//ADOTabularConnection c = new ADOTabularConnection(ConnectionString, AdomdType.AnalysisServices);
MetaDataVisitorCSDL v = new MetaDataVisitorCSDL(connection);
ADOTabularDatabase db = new ADOTabularDatabase(connection, "Test", "Test", DateTime.Parse("2019-09-01 09:00:00"), "1200", "*");
ADOTabularModel m = new ADOTabularModel(connection, db, "Test", "Test", "Test Description", "");
System.Xml.XmlReader xr = new System.Xml.XmlTextReader(@"..\..\data\csdl.xml");
var tabs = new ADOTabularTableCollection(connection, m);
v.GenerateTablesFromXmlReader(tabs, xr);
Assert.AreEqual(4, tabs.Count);
Assert.AreEqual(8, tabs["Sales"].Columns.Count()); // excludes internal rowcount column
Assert.AreEqual(0, tabs["Sales"].Columns[2].DistinctValues);
// Check TOM objects
Assert.AreEqual(4, m.TOMModel.Tables.Count, "Count of tables in TOM Model");
Assert.AreEqual(4, m.TOMModel.Tables["Sales"].Columns.Count, "Count of columns in TOM Model"); // includes external rowcount column
Assert.AreEqual(5, m.TOMModel.Tables["Sales"].Measures.Count, "Count of measures in TOM Model");
}
public void TestCSDLTablesWithSpaces()
{
ADOTabularConnection c = new ADOTabularConnection("Data Source=localhost", AdomdType.AnalysisServices);
MetaDataVisitorCSDL v = new MetaDataVisitorCSDL(c);
ADOTabularModel m = new ADOTabularModel(c, "Test","Test Caption", "Test Description", "");
System.Xml.XmlReader xr = new System.Xml.XmlTextReader(@"..\..\data\advwrkscsdl.xml");
var tabs = new ADOTabularTableCollection(c, m);
v.GenerateTablesFromXmlReader(tabs, xr);
var cmpyTab = tabs["Sales Quota"];
Assert.AreEqual("Sales Quota", cmpyTab.Caption, "Table Name is translated");
}
public void TestADOTabularCSDLVisitorKPI()
{
ADOTabularConnection c = new ADOTabularConnection("Data Source=localhost", AdomdType.AnalysisServices);
MetaDataVisitorCSDL v = new MetaDataVisitorCSDL(c);
System.Xml.XmlReader xr = new System.Xml.XmlTextReader(@"..\..\data\AdvWrks.xml");
ADOTabularModel m = new ADOTabularModel(c, "Test", "Test Caption","Test Description", "");
var tabs = new ADOTabularTableCollection(c, m);
v.GenerateTablesFromXmlReader(tabs, xr);
Assert.AreEqual(15, tabs.Count);
Assert.AreEqual(24, tabs["Sales Territory"].Columns.Count());
Assert.AreEqual(1, tabs["Sales Territory"].Columns.Where((t) => t.ColumnType == ADOTabularColumnType.Hierarchy).Count());
var k = tabs["Sales Territory"].Columns["Total Current Quarter Sales Performance"] as ADOTabularKpi;
Assert.AreEqual("Total Current Quarter Sales Performance", k.Caption);
Assert.AreEqual("_Total Current Quarter Sales Performance Goal", k.Goal.Caption);
Assert.AreEqual("_Total Current Quarter Sales Performance Status", k.Status.Caption);
}
public void TestADOTabularCSDLVisitorHierarchies()
{
ADOTabularConnection c = new ADOTabularConnection("Data Source=localhost", AdomdType.AnalysisServices);
MetaDataVisitorCSDL v = new MetaDataVisitorCSDL(c);
System.Xml.XmlReader xr = new System.Xml.XmlTextReader(@"..\..\data\AdvWrks.xml");
ADOTabularModel m = new ADOTabularModel(c, "Test","Test", "Test Description", "");
var tabs = new ADOTabularTableCollection(c,m);
v.GenerateTablesFromXmlReader(tabs, xr);
Assert.AreEqual(15, tabs.Count);
Assert.AreEqual(24, tabs["Sales Territory"].Columns.Count());
Assert.AreEqual(1, tabs["Sales Territory"].Columns.Where((t) => t.ColumnType == ADOTabularColumnType.Hierarchy).Count());
var h = (ADOTabularHierarchy) (tabs["Sales Territory"].Columns.Where((t) => t.ColumnType == ADOTabularColumnType.Hierarchy).First());
Assert.AreEqual(3, h.Levels.Count);
Assert.AreEqual("Group", h.Levels[0].LevelName);
Assert.AreEqual("Country", h.Levels[1].LevelName);
Assert.AreEqual("Region", h.Levels[2].LevelName);
}
private void AddColumnsToTable(XmlReader rdr
, ADOTabularTableCollection tables
, string eEntityType
, string eProperty
, string eMeasure
, string eSummary )
{
// 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 = "";
KpiDetails kpi = new KpiDetails();
var colType = ADOTabularColumnType.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 == "Kpi")
{
kpi = ProcessKpi(rdr, tables.GetById(tableId));
}
if (rdr.NodeType == XmlNodeType.Element
&& (rdr.LocalName == eProperty
|| rdr.LocalName == eMeasure
|| rdr.LocalName == eSummary))
{
if (rdr.LocalName == eMeasure)
colType = ADOTabularColumnType.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;
// Precision Scale FormatString
//DefaultAggregateFunction
}
}
}
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));
tab.Columns.Add(col);
}
else
{
colType = ADOTabularColumnType.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);
}
}
// reset temp variables
kpi = new KpiDetails();
refName = "";
caption = "";
name = null;
description = "";
isVisible = true;
contents = "";
dataType = "";
colType = ADOTabularColumnType.Column;
}
rdr.Read();
}
//TODO - link up back reference to backing measures for KPIs
}
