private DataTable CreateDataTable(IContext context, IEnumerable <Field> fields) { var schema = new DataSet("Schema"); var sql = SSAS.CreateQuery(_output); if (context.Entity.Filter.Any()) { sql += " WHERE " + context.ResolveFilter(_connectionFactory); } var adapter = new SqlDataAdapter(sql, new SqlConnection(_connectionFactory.GetConnectionString($"Transformalize ({_input.Process.Name})"))); adapter.FillSchema(schema, SchemaType.Source); var table = schema.Tables[0]; table.ExtendedProperties.Add("QueryDefinition", sql); table.ExtendedProperties.Add("DbTableName", context.Entity.Name); table.ExtendedProperties.Add("FriendlyName", context.Entity.Alias); table.ExtendedProperties.Add("TableType", "View"); table.TableName = context.Entity.Alias; foreach (var field in fields) { table.Columns[field.Alias].ExtendedProperties.Add("DbColumnName", field.Alias); if (field.Label == string.Empty || field.Label == field.Alias) { field.Label = field.Alias.Titleize(); } table.Columns[field.Alias].ExtendedProperties.Add("FriendlyName", field.Label); } return(table.Clone()); }
public void Write(IEnumerable <IRow> rows) { if (!_output.Entity.NeedsUpdate()) { return; } var ids = new SSASIdentifiers(_input, _output); using (var server = new Server()) { server.Connect($"Data Source={_output.Connection.Server};Catalog=;"); if (server.Databases.Contains(ids.DatabaseId)) { var database = server.Databases.Find(ids.DatabaseId); if (_output.Process.Mode == "init") { _output.Info($"Processing OLAP database {ids.DatabaseId}"); SSAS.Process(database, ProcessType.ProcessFull, _output); } else { if (database.Dimensions.Contains(_output.Entity.Alias)) { _output.Info($"Updating dimension {_output.Entity.Alias}"); var dimension = database.Dimensions.Find(_output.Entity.Alias); SSAS.Process(dimension, ProcessType.ProcessUpdate, _output); } else { _output.Error($"{_output.Entity.Alias} dimension does not exist!"); } if (database.Cubes.Contains(ids.CubeId)) { var cube = database.Cubes.Find(ids.CubeId); ProcessPartition(cube, ids.NormalMeasureGroupId); ProcessPartition(cube, ids.DistinctMeasureGroupId); } else { _output.Error($"{ids.CubeId} cube does not exist!"); } } } else { _output.Error($"{ids.DatabaseId} OLAP database does not exist!"); } server.Disconnect(); } }
private MeasureGroup GetMeasureGroup(Cube cube, Entity entity, string id, string dataSourceViewId) { MeasureGroup measureGroup; if (cube.MeasureGroups.Contains(id)) { measureGroup = cube.MeasureGroups.Find(id); } else { measureGroup = cube.MeasureGroups.Add(id); measureGroup.StorageMode = StorageMode.Molap; } Partition partition; if (measureGroup.Partitions.Contains(id)) { partition = measureGroup.Partitions.Find(id); } else { partition = measureGroup.Partitions.Add(id); } partition.Source = new DsvTableBinding(dataSourceViewId, entity.Alias); partition.StorageMode = StorageMode.Molap; measureGroup.Dimensions.Clear(); var measureGroupDimension = new DegenerateMeasureGroupDimension(entity.Alias); foreach (var key in entity.GetPrimaryKey()) { var attribute = measureGroupDimension.Attributes.Add(key.Alias); attribute.Type = MeasureGroupAttributeType.Granularity; attribute.KeyColumns.Add(entity.Alias, key.Alias, SSAS.GetOleDbType(key)); } measureGroup.Dimensions.Add(measureGroupDimension); return(measureGroup); }
public void ProcessPartition(Cube cube, string partitionId) { if (cube.MeasureGroups.Contains(partitionId)) { var measureGroup = cube.MeasureGroups.Find(partitionId); if (measureGroup.Partitions.Contains(partitionId)) { var partition = measureGroup.Partitions.Find(partitionId); _output.Info($"Updating partition {partitionId}"); if (SSAS.Process(partition, ProcessType.ProcessData, _output)) { SSAS.Process(partition, ProcessType.ProcessIndexes, _output); } } else { _output.Error($"{partitionId} partition does not exist!"); } } else { _output.Error($"{partitionId} measure group does not exist!"); } }
public ActionResponse Execute() { var ids = new SSASIdentifiers(_input, _output); using (_server) { Database database; _server.Connect($"Data Source={_output.Connection.Server};Catalog=;"); if (_server.Databases.Contains(ids.DatabaseId)) { database = _server.Databases.Find(ids.DatabaseId); _output.Info($"Updating existing OLAP database: {ids.DatabaseId}"); } else { database = new Database() { Name = ids.DatabaseId, ID = ids.DatabaseId, DataSourceImpersonationInfo = new ImpersonationInfo { ImpersonationMode = ImpersonationMode.Default } }; _server.Databases.Add(database); _output.Info($"Creating new OLAP database: {ids.DatabaseId}"); if (!SSAS.Save(_server, _output, database)) { return(new ActionResponse(500, $"Could not save OLAP database: {ids.DatabaseId}")); } } DataSource dataSource; if (database.DataSources.Contains(ids.DataSourceId)) { dataSource = database.DataSources.Find(ids.DataSourceId); _output.Info($"Updating existing data source: {ids.DataSourceId}"); } else { dataSource = database.DataSources.AddNew(ids.DataSourceId); _output.Info($"Creating new data source: {ids.DataSourceId}"); } dataSource.ConnectionString = $"Provider=SQLOLEDB.1;Data Source={_input.Connection.Server};Integrated Security=SSPI;Initial Catalog={_input.Connection.Database}"; dataSource.ImpersonationInfo = new ImpersonationInfo { ImpersonationMode = ImpersonationMode.ImpersonateServiceAccount }; dataSource.Timeout = System.TimeSpan.FromSeconds(_input.Connection.RequestTimeout); if (!SSAS.Save(_server, _output, dataSource)) { return(new ActionResponse(500, $"Could not save data source: {ids.DataSourceId}")); } // view var entity = _output.Entity; var fields = SSAS.GetFields(_output); DataSourceView dataSourceView; if (database.DataSourceViews.Contains(ids.DataSourceViewId)) { dataSourceView = database.DataSourceViews.Find(ids.DataSourceViewId); if (dataSourceView.Schema.Tables.Contains(entity.Alias)) { dataSourceView.Schema.Tables.Remove(entity.Alias); } dataSourceView.Schema.Tables.Add(CreateDataTable(_output, fields)); _output.Info($"Updating existing data source view: {ids.DataSourceViewId}"); } else { _output.Info($"Creating new data source view: {ids.DataSourceViewId}"); dataSourceView = database.DataSourceViews.AddNew(ids.DataSourceViewId); dataSourceView.DataSourceID = ids.DataSourceId; dataSourceView.Schema = new DataSet("Schema"); dataSourceView.Schema.Tables.Add(CreateDataTable(_output, fields)); if (!SSAS.Save(_server, _output, dataSourceView)) { return(new ActionResponse(500, $"Could not save data source view: {ids.DataSourceViewId}")); } } Dimension dimension; if (database.Dimensions.Contains(entity.Alias)) { dimension = database.Dimensions.Find(entity.Alias); _output.Info($"Updating existing dimension: {entity.Alias}"); } else { dimension = database.Dimensions.AddNew(entity.Alias, entity.Alias); dimension.WriteEnabled = false; dimension.StorageMode = DimensionStorageMode.Molap; _output.Info($"Creating new dimension: {entity.Alias}"); } dimension.Source = new DataSourceViewBinding(ids.DataSourceViewId); foreach (var field in fields) { DimensionAttribute dimensionAttribute; if (dimension.Attributes.Contains(field.Alias)) { dimensionAttribute = dimension.Attributes.Find(field.Alias); } else { dimensionAttribute = dimension.Attributes.Add(field.Label, field.Alias); dimensionAttribute.OrderBy = OrderBy.Key; // for now } var optimize = field.PrimaryKey || field.Dimension == "true" || field.Dimension == "default" && !field.IsNumeric(); dimensionAttribute.AttributeHierarchyEnabled = optimize; dimensionAttribute.IsAggregatable = optimize; dimensionAttribute.Usage = field.PrimaryKey ? AttributeUsage.Key : AttributeUsage.Regular; dimensionAttribute.KeyColumns.Clear(); DataItem keyColumn; if (field.Type == "string") { var length = field.Length == "max" ? int.MaxValue : Convert.ToInt32(field.Length); keyColumn = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field), length) { Trimming = Trimming.None }; } else { keyColumn = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field)); } dimensionAttribute.KeyColumns.Add(keyColumn); } if (!SSAS.Save(_server, _output, dimension)) { return(new ActionResponse(500, $"Could not save dimension: {entity.Alias}")); } Cube cube; if (database.Cubes.Contains(ids.CubeId)) { cube = database.Cubes.Find(ids.CubeId); _output.Info($"Updating existing cube: {ids.CubeId}"); } else { cube = database.Cubes.AddNew(ids.CubeId, ids.CubeId); _output.Info($"Creating new cube: {ids.CubeId}"); } cube.Source = new DataSourceViewBinding(ids.DataSourceViewId); CubeDimension cubeDimension; if (cube.Dimensions.Contains(entity.Alias)) { cubeDimension = cube.Dimensions.Find(entity.Alias); } else { cubeDimension = cube.Dimensions.Add(entity.Alias); } cubeDimension.Attributes.Clear(); foreach (DimensionAttribute attribute in database.Dimensions.Find(entity.Alias).Attributes) { cubeDimension.Attributes.Add(attribute.ID); } var normalMeasureGroup = GetMeasureGroup(cube, entity, ids.NormalMeasureGroupId, ids.DataSourceViewId); // standard count measure Measure countMeasure; if (normalMeasureGroup.Measures.Contains("Count")) { countMeasure = normalMeasureGroup.Measures.Find("Count"); } else { countMeasure = normalMeasureGroup.Measures.Add("Count", "Count"); } countMeasure.AggregateFunction = AggregationFunction.Count; countMeasure.Source = new DataItem(new RowBinding(entity.Alias), OleDbType.Integer); // version measures var versionField = entity.GetVersionField(); if (versionField != null) { Measure versionMeasure; if (normalMeasureGroup.Measures.Contains(ids.VersionId)) { versionMeasure = normalMeasureGroup.Measures.Find(ids.VersionId); } else { versionMeasure = normalMeasureGroup.Measures.Add(ids.VersionId); } versionMeasure.AggregateFunction = AggregationFunction.Max; versionMeasure.Source = new DataItem(entity.Alias, versionField.Alias, SSAS.GetOleDbType(versionField)); versionMeasure.Visible = false; } // defined measures var measureFields = entity.GetAllOutputFields().Where(f => f.Measure && f.Type != "byte[]"); // normal measures foreach (var field in measureFields.Where(f => f.AggregateFunction != "distinctcount")) { Measure measure; var function = (AggregationFunction)Enum.Parse(typeof(AggregationFunction), field.AggregateFunction, true); if (normalMeasureGroup.Measures.Contains(field.Alias)) { measure = normalMeasureGroup.Measures.Find(field.Alias); } else { measure = normalMeasureGroup.Measures.Add(field.Label, field.Alias); } measure.AggregateFunction = function; measure.FormatString = field.Format; measure.Source = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field)); } // distinct measures foreach (var field in measureFields.Where(f => f.AggregateFunction == "distinctcount")) { var type = SSAS.GetOleDbType(field); if (SSAS.CanDistinctCount(type)) { Measure measure; var distinctMeasureGroup = GetMeasureGroup(cube, entity, ids.DistinctMeasureGroupId, ids.DataSourceViewId); if (distinctMeasureGroup.Measures.Contains(field.Alias)) { measure = distinctMeasureGroup.Measures.Find(field.Alias); } else { measure = distinctMeasureGroup.Measures.Add(field.Label, field.Alias); } measure.AggregateFunction = AggregationFunction.DistinctCount; measure.FormatString = field.Format; measure.Source = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field)); } else { _output.Warn($"Can not distinct count {field.Label} using {field.Type} type."); } } if (!SSAS.Save(_server, _output, cube)) { return(new ActionResponse(500, $"Could not save cube: {ids.CubeId}")); } _server.Disconnect(); } return(new ActionResponse()); }