public static Boolean HandleLogicalColumns(XmlNodeList trueColumns, CubeTable currentTable)
{
//
// Summary:
// Determines if a cube column
// from the DSV is based on the
// database (non logical) or based
// on a calculation (logical).
// Adds the column to the input
// table if logical.
//
if (trueColumns.Item(0).Attributes["msprop:IsLogical"] != null)
{
if (trueColumns.Item(0).Attributes.GetNamedItem("msprop:IsLogical").Value.ToString() == "True")
{
CubeColumn myLogicalColumn = new CubeColumn
{
CubeColumnName =
trueColumns.Item(0).Attributes.GetNamedItem("msprop:DbColumnName").Value.ToString()
};
currentTable.LogicalColumns.Add(myLogicalColumn);
// Return true to indicate the column is logical
return(true);
}
}
// Return false to indicate the column is not logical
return(false);
}
private static Tuple <CubeTable, Table> MatchTables(CubeTable cubeTable,
List <Database> databases)
{
//
// Summary:
// Returns matched cube and database
// tables or view for futher analysis.
Tuple <CubeTable, Table> tableTuple = null;
Tuple <CubeTable, Table> viewTuple = null;
foreach (Database database in databases)
{
// Find all corresponding tables based
// on the table name and schema name
// of both ref types
foreach (Table table in database._databaseTables)
{
if (table.Equals(cubeTable))
{
tableTuple = Tuple.Create(cubeTable, table);
}
}
// Find all corresponding views based
// on the table name and schema name of
// both ref types
foreach (Table table in database._databaseViews)
{
if (table.Equals(cubeTable))
{
viewTuple = Tuple.Create(cubeTable, table);
}
}
}
// Database name spaces do not allow
// tables or views with identical names within a
// single database. So no check is installed.
if (tableTuple != null)
{
return(tableTuple);
}
return(viewTuple);
}
private List <CubeTable> GetCubeTables(string cubePath)
{
List <CubeTable> foundTables = new List <CubeTable>();
// Create, configure and load xml items and values
XmlDocument myXmlCube = new XmlDocument();
myXmlCube = loadCube(cubePath);
// Determine xpath to search for tables
XmlNodeList nodes;
string xDimPath = "/~ns~:Batch/~ns~:Alter/~ns~:ObjectDefinition/~ns~:Database/" +
"~ns~:Dimensions/~ns~:Dimension";
string xPath = "/~ns~:Attributes/~ns~:Attribute/~ns~:KeyColumns/~ns~:KeyColumn/~ns~:Source";
// Read the cube's nodes based on the xPath expression
nodes = ArtifactReader.getArtifactNodes(xPath, myXmlCube, xDimPath);
string checkNode = null;
foreach (XmlNode node in nodes)
{
CubeTable currentTable = foundTables.Find(x => x.CubeTableName.Equals(node.FirstChild.InnerText));
// Check if a new table is presented
if (currentTable is null)
{
// Add cube table
currentTable = new CubeTable();
currentTable.CubeTableName = node.FirstChild.InnerText;
checkNode = currentTable.CubeTableName;
// Add db ref table
// Find db table name and schema
XmlNodeList trueTables;
xPath = $"/~ns~:Batch/~ns~:Alter/~ns~:ObjectDefinition/~ns~:Database/~ns~:DataSourceViews/" +
$"~ns~:DataSourceView/~ns~:Schema/xs:schema/xs:element/xs:complexType/xs:choice/" +
$"xs:element[@name=\'{node.FirstChild.InnerText}\']";
trueTables = ArtifactReader.getArtifactNodes(xPath, myXmlCube);
// Add findings to property list
string trueTableName, trueSchemaName;
trueTableName = trueTables.Item(0).Attributes.GetNamedItem("msprop:DbTableName").Value.ToString();
trueSchemaName = trueTables.Item(0).Attributes.GetNamedItem("msprop:DbSchemaName").Value.ToString();
// Concat the schema and table name and store it as table name
currentTable.TableName = trueSchemaName + "." + trueTableName;
// Add table to output
foundTables.Add(currentTable);
}
// Find matching db column name
XmlNodeList trueColumns;
xPath = $"/~ns~:Batch/~ns~:Alter/~ns~:ObjectDefinition/~ns~:Database/~ns~:DataSourceViews/" +
$"~ns~:DataSourceView/~ns~:Schema/xs:schema/xs:element/xs:complexType/xs:choice/" +
$"xs:element[@name=\'{currentTable.CubeTableName}\']/xs:complexType/xs:sequence/" +
$"xs:element[@name=\'{node.LastChild.InnerText}\']";
trueColumns = ArtifactReader.getArtifactNodes(xPath, myXmlCube);
// Do not include logical columns. These columns
// are computed in the data source view and do not
// directly correspond to database column.
// Add them to a special logical column list for
// later use.
if (CubeColumn.HandleLogicalColumns(trueColumns, currentTable))
{
break;
}
// Get cube column data type from the cube
Tuple <string, string> dataType = CubeColumn.GetCubeColumnDataType(trueColumns);
// TODO: Get database column data type from the dacpac
// Add cube columns to table
CubeColumn myColumn = new CubeColumn()
{
ColumnName = trueColumns.Item(0).Attributes.GetNamedItem("msprop:DbColumnName").Value.ToString(),
CubeColumnName = node.LastChild.InnerText,
CubeColumnDataType = dataType.Item1,
CubeColumnDataLength = dataType.Item2
};
currentTable.AddColumn(myColumn);
}
return(foundTables);
}
public void CheckForColumns(Match match)
{
// Summary:
// checks if every column in the
// matched cube is represented in the
// matched database(s).
//
// Parameters:
// match:
// Reference type containing one Cube
// and a list of Databases
string checkName = "101. Cube vs Database column test";
Console.WriteLine($"Running check {checkName}: \nChecking if all cube table columns " +
$"have corresponding database columns.");
List <CubeTable> nonPresentColumnTables = new List <CubeTable>();
foreach (CubeTable cubeTable in match.MatchingCube._cubeTables)
{
// Match the cube table to the database table
Tuple <CubeTable, Table> tuple = MatchTables(cubeTable, match.MatchedDatabases);
// Create a list to accomodate all unfound
// columns.
List <Column> unfoundColumns = new List <Column>();
foreach (CubeColumn cubeColumn in tuple.Item1.ColumnList)
{
// Search for the cube column in
// every matched database's table
// column list.
Boolean isFound =
tuple.Item2.ColumnList.Any(
x => x.ColumnName.ToLower() == cubeColumn.ColumnName.ToLower());
// If the cube column is not found
// add it to the unfound list.
if (!isFound)
{
unfoundColumns.Add(cubeColumn);
}
}
// Collect all the columns that cannot be
// matched with a database column
if (unfoundColumns.Count > 0)
{
CubeTable tableWithMissingColumns = new CubeTable();
tableWithMissingColumns.TableName = cubeTable.TableName;
tableWithMissingColumns.ColumnList = unfoundColumns;
nonPresentColumnTables.Add(tableWithMissingColumns);
}
}
// Print results back to user
// First; do the non statisfied conditions
if (nonPresentColumnTables.Count > 0)
{
foreach (CubeTable cubeTable in nonPresentColumnTables)
{
foreach (CubeColumn cubeColumn in cubeTable.ColumnList)
{
string message = $"The cube column {cubeColumn.CubeColumnName}" +
$" from cube table {cubeTable.TableName} cannot be found in" +
$" it's corresponding databases.";
WriteFailedConditions(message);
}
}
// Next; throw an exception to
// halt the execution of the program
//throw new MatchException($"{checkName} failed");
}
// If everything looks fine, pass the check!
else
{
WriteTestSuccess(checkName);
}
}
