Esempio n. 1
0
        /// <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();
        }
Esempio n. 2
0
        /// <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();
        }
Esempio n. 3
0
        /// <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();
        }
Esempio n. 4
0
        /// <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();
        }
Esempio n. 10
0
        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();
        }