/// <summary> /// Method to create the measure in the database. This just creates the basic Measure information and /// does not add values under the Measure. This process is based off creating SQL dynamically based on /// a template file. /// </summary> /// <param name="model">The populated model containing the data needed to create the Measure</param> public void createMeasure(CreateMeasureModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); Measure newMeasure = context.Measures.Create(); newMeasure.MeasureName = model.MeasureName; createDimSets(model.selectedDimensions); IEnumerable <DimensionSet> dimSets = context.Dimensions.Where(x => model.selectedDimensions.Contains(x.DimensionID)).SelectMany(x => x.DimensionSetMembers).Select(x => x.DimensionSet).ToList(); IEnumerable <DimensionSet> dimSetsForExclusion = context.Dimensions.Where(x => !model.selectedDimensions.Contains(x.DimensionID)).SelectMany(x => x.DimensionSetMembers).Select(x => x.DimensionSet).ToList(); IEnumerable <DimensionSet> dimSetsFinal = dimSets.Where(x => !dimSetsForExclusion.Select(y => y.DimensionSetID).Contains(x.DimensionSetID)).Distinct(); foreach (DimensionSet set in dimSetsFinal) { MeasureBreakdown newBreakdown = context.MeasureBreakdowns.Create(); newBreakdown.MeasureBreakdownName = String.Format("{0} {1}", model.MeasureName, set.DimensionSetName); newBreakdown.DimensionSetID = set.DimensionSetID; newMeasure.MeasureBreakdowns.Add(newBreakdown); } context.Measures.Add(newMeasure); context.SaveChanges(); context.Dispose(); }
/// <summary> /// A method to add Columns to a Staging Table. This adds them to both the real table /// and the meta data about the table. The columns to add are based from the Data /// Table provided. /// </summary> /// <param name="dt">The Data Table to use to get the Columns to be added</param> /// <param name="tableID">The ID of the Staging Table to be added to.</param> private void addColumnsToTables(DataTable dt, int tableID) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); string datasetName = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(tableID)).DatasetName; foreach (DataColumn column in dt.Columns) { if (column.ColumnName != "UploadRef") { SqlParameter param1 = new SqlParameter("@tableName", datasetName); SqlParameter param2 = new SqlParameter("@columnName", column.ColumnName); context.Database.ExecuteSqlCommand("AddColumnToTable @tableName, @columnName", param1, param2); } StagingColumn newColumn = new StagingColumn(); newColumn.ColumnName = column.ColumnName; newColumn.StagingDatasetID = tableID; context.StagingColumns.Add(newColumn); } context.SaveChanges(); context.Dispose(); }
/// <summary> /// Method to create a Staging Table initially. This intial creation creates /// a record of the table in the meta data tables and then a trigger creates the /// actual table. Initially there are no columns associated with the table /// as these are added later when the data is uploaded. /// </summary> /// <param name="model"> /// The populated model containing the name of the table to be created. /// </param> public void createStagingTable(GeneralStagingModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); StagingDataset staging = context.StagingDatasets.Create(); staging.DatasetName = model.TableName; context.StagingDatasets.Add(staging); context.SaveChanges(); context.Dispose(); }
/// <summary> /// Method to create a Dimension based off the model returned from a View. This creates a Dimension /// and then populates values with those found in the specified Staging Table column /// </summary> /// <param name="model">The populated model passed back from the View</param> public void CreateDimension(CreateDimensionModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); Dimension newDim = context.Dimensions.Create(); newDim.DimensionName = model.DimensionName; //DimensionSet newSet = context.DimensionSets.Create(); //newSet.DimensionSetName = String.Format("By {0}", model.DimensionName); //DimensionSetMember newMember = context.DimensionSetMembers.Create(); //newDim.DimensionSetMembers.Add(newMember); //newSet.DimensionSetMembers.Add(newMember); String stagingColumnName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(model.DimColumnInStaging)).ColumnName; String stagingTableName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(model.DimColumnInStaging)).StagingDataset.DatasetName; String sqlQuery = String.Format("select distinct [{0}] from [{1}]", stagingColumnName, stagingTableName); IEnumerable <String> stagingDimensionValues = context.Database.SqlQuery <String>(sqlQuery); foreach (String dimValue in stagingDimensionValues) { DimensionValue value = context.DimensionValues.Create(); value.DimensionValue1 = dimValue; newDim.DimensionValues.Add(value); } context.Dimensions.Add(newDim); //context.DimensionSets.Add(newSet); //context.DimensionSetMembers.Add(newMember); context.SaveChanges(); context.Dispose(); }
/// <summary> /// A method to create the View from the user entered data that /// has been specified through the web interface. It uses the template /// for a new View to generate the necessary SQL. /// </summary> /// <param name="model">The fully created model</param> public void CreateView(CreateViewModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); DataView newView = context.DataViews.Create(); newView.ViewName = model.ViewName; DataViewColumn geogColumn = context.DataViewColumns.Create(); geogColumn.ColumnName = context.GeographyTypes.Single(x => x.GeographyTypeID.Equals(model.SelectedGeographyType)).GeographyType1; geogColumn.DataView = newView; context.DataViewColumns.Add(geogColumn); foreach (var column in model.Columns) { DataViewColumn newColumn = context.DataViewColumns.Create(); newColumn.ColumnName = column.ColumnName; newColumn.DataView = newView; context.DataViewColumns.Add(newColumn); } context.DataViews.Add(newView); CreateViewTemplate template = new CreateViewTemplate(); template.Model = model; String output = template.TransformText(); context.Database.ExecuteSqlCommand(output); context.SaveChanges(); context.Dispose(); }
/// <summary> /// Method to create a Dimension based off the model returned from a View. This creates a Dimension /// and then populates values with those found in the specified Staging Table column /// </summary> /// <param name="model">The populated model passed back from the View</param> public void CreateDimension(CreateDimensionModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); Dimension newDim = context.Dimensions.Create(); newDim.DimensionName = model.DimensionName; //DimensionSet newSet = context.DimensionSets.Create(); //newSet.DimensionSetName = String.Format("By {0}", model.DimensionName); //DimensionSetMember newMember = context.DimensionSetMembers.Create(); //newDim.DimensionSetMembers.Add(newMember); //newSet.DimensionSetMembers.Add(newMember); String stagingColumnName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(model.DimColumnInStaging)).ColumnName; String stagingTableName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(model.DimColumnInStaging)).StagingDataset.DatasetName; String sqlQuery = String.Format("select distinct [{0}] from [{1}]", stagingColumnName, stagingTableName); IEnumerable<String> stagingDimensionValues = context.Database.SqlQuery<String>(sqlQuery); foreach (String dimValue in stagingDimensionValues) { DimensionValue value = context.DimensionValues.Create(); value.DimensionValue1 = dimValue; newDim.DimensionValues.Add(value); } context.Dimensions.Add(newDim); //context.DimensionSets.Add(newSet); //context.DimensionSetMembers.Add(newMember); context.SaveChanges(); context.Dispose(); }
/// <summary> /// A method to create the View from the user entered data that /// has been specified through the web interface. It uses the template /// for a new View to generate the necessary SQL. /// </summary> /// <param name="model">The fully created model</param> public void CreateView(CreateViewModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); DataView newView = context.DataViews.Create(); newView.ViewName = model.ViewName; DataViewColumn geogColumn = context.DataViewColumns.Create(); geogColumn.ColumnName = context.GeographyTypes.Single(x => x.GeographyTypeID.Equals(model.SelectedGeographyType)).GeographyType1; geogColumn.DataView = newView; context.DataViewColumns.Add(geogColumn); foreach (var column in model.Columns) { DataViewColumn newColumn = context.DataViewColumns.Create(); newColumn.ColumnName = column.ColumnName; newColumn.DataView = newView; context.DataViewColumns.Add(newColumn); } context.DataViews.Add(newView); CreateViewTemplate template = new CreateViewTemplate(); template.Model = model; String output = template.TransformText(); context.Database.ExecuteSqlCommand(output); context.SaveChanges(); context.Dispose(); }
/// <summary> /// Method to create the measure in the database. This just creates the basic Measure information and /// does not add values under the Measure. This process is based off creating SQL dynamically based on /// a template file. /// </summary> /// <param name="model">The populated model containing the data needed to create the Measure</param> public void createMeasure(CreateMeasureModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); Measure newMeasure = context.Measures.Create(); newMeasure.MeasureName = model.MeasureName; createDimSets(model.selectedDimensions); IEnumerable<DimensionSet> dimSets = context.Dimensions.Where(x => model.selectedDimensions.Contains(x.DimensionID)).SelectMany(x => x.DimensionSetMembers).Select(x => x.DimensionSet).ToList(); IEnumerable<DimensionSet> dimSetsForExclusion = context.Dimensions.Where(x => !model.selectedDimensions.Contains(x.DimensionID)).SelectMany(x => x.DimensionSetMembers).Select(x => x.DimensionSet).ToList(); IEnumerable<DimensionSet> dimSetsFinal = dimSets.Where(x => !dimSetsForExclusion.Select(y => y.DimensionSetID).Contains(x.DimensionSetID)).Distinct(); foreach (DimensionSet set in dimSetsFinal) { MeasureBreakdown newBreakdown = context.MeasureBreakdowns.Create(); newBreakdown.MeasureBreakdownName = String.Format("{0} {1}", model.MeasureName, set.DimensionSetName); newBreakdown.DimensionSetID = set.DimensionSetID; newMeasure.MeasureBreakdowns.Add(newBreakdown); } context.Measures.Add(newMeasure); context.SaveChanges(); context.Dispose(); }
private void createDimSets(IEnumerable<int> dimIDs) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); List<DimensionSet> allNewSets = new List<DimensionSet>(); foreach (int dimID in dimIDs) { Dimension dim = context.Dimensions.Single(x => x.DimensionID.Equals(dimID)); foreach (DimensionSet set in allNewSets) { DimensionSet derivedSet = context.DimensionSets.Create(); derivedSet.DimensionSetName = String.Format("{0} and {1}", set.DimensionSetName, dim.DimensionName); context.DimensionSets.Add(derivedSet); DimensionSetMember newDimMember = context.DimensionSetMembers.Create(); newDimMember.DimensionSet = derivedSet; newDimMember.Dimension = dim; context.DimensionSetMembers.Add(newDimMember); foreach (DimensionSetMember dimSetMember in set.DimensionSetMembers) { DimensionSetMember derivedMember = context.DimensionSetMembers.Create(); derivedMember.Dimension = dimSetMember.Dimension; derivedSet.DimensionSetMembers.Add(derivedMember); context.DimensionSetMembers.Add(derivedMember); } foreach (DimensionSetCombination combination in set.DimensionSetCombinations) { foreach (DimensionValue dimValue in dim.DimensionValues) { DimensionSetCombination derivedCombination = context.DimensionSetCombinations.Create(); derivedCombination.DimensionSetCombinationName = String.Format("{0} | {1}: {2}", combination.DimensionSetCombinationName, dim.DimensionName, dimValue.DimensionValue1); derivedCombination.DimensionSet = derivedSet; context.DimensionSetCombinations.Add(derivedCombination); DimensionSetCombinationMember newCombinationMember = context.DimensionSetCombinationMembers.Create(); newCombinationMember.DimensionSetCombination = derivedCombination; newCombinationMember.DimensionValue = dimValue; context.DimensionSetCombinationMembers.Add(newCombinationMember); foreach (DimensionSetCombinationMember dimSetCombinationMember in combination.DimensionSetCombinationMembers) { DimensionSetCombinationMember derivedCombinationMember = context.DimensionSetCombinationMembers.Create(); derivedCombinationMember.DimensionSetCombination = derivedCombination; derivedCombinationMember.DimensionValue = dimSetCombinationMember.DimensionValue; context.DimensionSetCombinationMembers.Add(derivedCombinationMember); } } } } String newDimSetName = String.Format("By {0}", dim.DimensionName); if (context.DimensionSets.Count(x => x.DimensionSetName.Equals(newDimSetName)) == 0) { DimensionSet newSet = context.DimensionSets.Create(); newSet.DimensionSetName = String.Format("By {0}", dim.DimensionName); DimensionSetMember newMember = context.DimensionSetMembers.Create(); dim.DimensionSetMembers.Add(newMember); newSet.DimensionSetMembers.Add(newMember); foreach (DimensionValue dimValue in dim.DimensionValues) { DimensionSetCombination newCombination = context.DimensionSetCombinations.Create(); newCombination.DimensionSet = newSet; newCombination.DimensionSetCombinationName = String.Format("{0}: {1}", dim.DimensionName, dimValue.DimensionValue1); DimensionSetCombinationMember newCombinationMember = context.DimensionSetCombinationMembers.Create(); newCombinationMember.DimensionValue = dimValue; newCombinationMember.DimensionSetCombination = newCombination; context.DimensionSetCombinations.Add(newCombination); context.DimensionSetCombinationMembers.Add(newCombinationMember); } allNewSets.Add(newSet); } } context.SaveChanges(); context.Dispose(); }
private void createDimSets(IEnumerable <int> dimIDs) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); List <DimensionSet> allNewSets = new List <DimensionSet>(); foreach (int dimID in dimIDs) { Dimension dim = context.Dimensions.Single(x => x.DimensionID.Equals(dimID)); foreach (DimensionSet set in allNewSets) { DimensionSet derivedSet = context.DimensionSets.Create(); derivedSet.DimensionSetName = String.Format("{0} and {1}", set.DimensionSetName, dim.DimensionName); context.DimensionSets.Add(derivedSet); DimensionSetMember newDimMember = context.DimensionSetMembers.Create(); newDimMember.DimensionSet = derivedSet; newDimMember.Dimension = dim; context.DimensionSetMembers.Add(newDimMember); foreach (DimensionSetMember dimSetMember in set.DimensionSetMembers) { DimensionSetMember derivedMember = context.DimensionSetMembers.Create(); derivedMember.Dimension = dimSetMember.Dimension; derivedSet.DimensionSetMembers.Add(derivedMember); context.DimensionSetMembers.Add(derivedMember); } foreach (DimensionSetCombination combination in set.DimensionSetCombinations) { foreach (DimensionValue dimValue in dim.DimensionValues) { DimensionSetCombination derivedCombination = context.DimensionSetCombinations.Create(); derivedCombination.DimensionSetCombinationName = String.Format("{0} | {1}: {2}", combination.DimensionSetCombinationName, dim.DimensionName, dimValue.DimensionValue1); derivedCombination.DimensionSet = derivedSet; context.DimensionSetCombinations.Add(derivedCombination); DimensionSetCombinationMember newCombinationMember = context.DimensionSetCombinationMembers.Create(); newCombinationMember.DimensionSetCombination = derivedCombination; newCombinationMember.DimensionValue = dimValue; context.DimensionSetCombinationMembers.Add(newCombinationMember); foreach (DimensionSetCombinationMember dimSetCombinationMember in combination.DimensionSetCombinationMembers) { DimensionSetCombinationMember derivedCombinationMember = context.DimensionSetCombinationMembers.Create(); derivedCombinationMember.DimensionSetCombination = derivedCombination; derivedCombinationMember.DimensionValue = dimSetCombinationMember.DimensionValue; context.DimensionSetCombinationMembers.Add(derivedCombinationMember); } } } } String newDimSetName = String.Format("By {0}", dim.DimensionName); if (context.DimensionSets.Count(x => x.DimensionSetName.Equals(newDimSetName)) == 0) { DimensionSet newSet = context.DimensionSets.Create(); newSet.DimensionSetName = String.Format("By {0}", dim.DimensionName); DimensionSetMember newMember = context.DimensionSetMembers.Create(); dim.DimensionSetMembers.Add(newMember); newSet.DimensionSetMembers.Add(newMember); foreach (DimensionValue dimValue in dim.DimensionValues) { DimensionSetCombination newCombination = context.DimensionSetCombinations.Create(); newCombination.DimensionSet = newSet; newCombination.DimensionSetCombinationName = String.Format("{0}: {1}", dim.DimensionName, dimValue.DimensionValue1); DimensionSetCombinationMember newCombinationMember = context.DimensionSetCombinationMembers.Create(); newCombinationMember.DimensionValue = dimValue; newCombinationMember.DimensionSetCombination = newCombination; context.DimensionSetCombinations.Add(newCombination); context.DimensionSetCombinationMembers.Add(newCombinationMember); } allNewSets.Add(newSet); } } context.SaveChanges(); context.Dispose(); }