public ActionResult AddMeasureValues(MeasureValueModel model)
{
DatasetDataAccess dataAccess = new DatasetDataAccess();
dataAccess.createMeasureValues(model);
TempData["SuccessMessage"] = String.Format("The Measure - {0}, was successfully created", model.MeasureName);
return RedirectToAction("Index", "Staging");
}
public void createMeasureValues(MeasureValueModel model)
{
Geographical_NeedsEntities context = new Geographical_NeedsEntities();
var breakdowns = context.Measures.Single(x => x.MeasureName.Equals(model.MeasureName)).MeasureBreakdowns;
foreach (var breakdown in breakdowns)
{
var combinations = breakdown.DimensionSet.DimensionSetCombinations;
foreach (var combination in combinations)
{
var values = combination.DimensionSetCombinationMembers.Select(x => x.DimensionValue);
List<String> whereClauses = new List<string>();
foreach (var value in values)
{
var detail = model.MeasureValueDetails.Single(x => x.DimValueID.Equals(value.DimensionValueID));
whereClauses.Add(String.Format("[{0}] = '{1}'", detail.DimColumnInStaging, detail.DimValueInStaging.Replace("'", "''")));
}
String whereClause = String.Format("Where {0}", String.Join(" AND ", whereClauses));
InsertMeasureValuesTemplate template = new InsertMeasureValuesTemplate();
template.DimensionSetCombinationID = combination.DimensionSetCombinationID;
template.GeographyTypeID = model.GeographyTypeID;
template.MeasureBreakdownID = breakdown.MeasureBreakdownID;
template.MeasureColumnName = model.MeasureStagingColumnName;
template.StagingGeographyColumn = model.StagingGeographyColumn;
template.StagingTableName = model.StagingTableName;
template.UseMeasureColumn = model.UseMeasureColumn;
template.WhereClause = whereClause;
String output = template.TransformText();
context.Database.ExecuteSqlCommand(output);
}
}
context.Dispose();
}
/// <summary>
/// Method to return the model which is used to map the Staging Dimension Values to Dimension Values
/// in the database. This is used in the second stage in the Create Measure process.
/// </summary>
/// <param name="mappings">The mappings between the Staging Dimension Column and a Dimension in the Database. This is passed in as a Tuple where the first item is a Staging Column ID and the second item a Dimension ID</param>
/// <param name="stagingTableName">The name of the staging table being used</param>
/// <param name="measureName">The name of the measure to be created</param>
/// <param name="measureColumnStagingID">The ID of the column used for the Measure values in Staging</param>
/// <param name="geographyColumnID">The ID of the colum used for the Geography value in Staging</param>
/// <returns></returns>
public MeasureValueModel populateMeasureValueModel(IEnumerable<Tuple<int, int>> mappings, String stagingTableName, String measureName, int? measureColumnStagingID, int geographyColumnID, int geographyTypeID)
{
List<MeasureValueModel> retVal = new List<MeasureValueModel>();
Geographical_NeedsEntities context = new Geographical_NeedsEntities();
MeasureValueModel model = new MeasureValueModel();
if (measureColumnStagingID.HasValue)
{
model.MeasureStagingColumnName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(measureColumnStagingID.Value)).ColumnName;
model.UseMeasureColumn = true;
}
else
{
model.UseMeasureColumn = false;
}
model.GeographyTypeID = geographyTypeID;
model.StagingGeographyColumn = context.StagingColumns.Single(x => x.StagingColumnID.Equals(geographyColumnID)).ColumnName;
model.MeasureName = measureName;
model.StagingTableName = stagingTableName;
List<MeasureValueDetailModel> detailModels = new List<MeasureValueDetailModel>();
foreach (Tuple<int, int> tuple in mappings)
{
String dimColumnInStaging = context.StagingColumns.Single(x => x.StagingColumnID.Equals(tuple.Item1)).ColumnName;
String sqlQuery = String.Format("select distinct {0} from {1}", dimColumnInStaging, stagingTableName);
IEnumerable<String> stagingMeasureValues = context.Database.SqlQuery<String>(sqlQuery);
foreach (DimensionValue dim in context.DimensionValues.Where(x => x.DimensionID.Equals(tuple.Item2)))
{
MeasureValueDetailModel modelDetail = new MeasureValueDetailModel();
modelDetail.DimensionID = tuple.Item2;
modelDetail.StagingDimensionValues = stagingMeasureValues.Select(x => new SelectListItem { Text = x, Value = x, Selected = dim.DimensionValue1.Equals(x) });
modelDetail.DimValue = dim.DimensionValue1;
modelDetail.DimValueID = dim.DimensionValueID;
modelDetail.DimColumnInStaging = dimColumnInStaging;
detailModels.Add(modelDetail);
}
}
model.MeasureValueDetails = detailModels;
return model;
}