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); } }