说明 #region 说明//该例以Sql Server 2005示例数据仓库AdventureWorksDW的FactInternetSales//事实表和DimCustomer维度表来演示创建多维数据库的的方法#endregion using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using analysis = Microsoft.AnalysisServices; namespace BuildOLAP ... { public partial class Form1 : Form ...{ /**//// <summary> /// 连接字符串 /// </summary> private const string strConnectionString = "Provider=SQLNCLI.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW"; /**//// <summary> /// 服务器名称 /// </summary> private const string strServerName = "DC"; /**//// <summary> /// 数据库名称 /// </summary> private const string strDataBaseName = "OlapSample"; /**//// <summary> /// 名称 /// 该名称用于数据源,数据源视图,CUBE /// </summary> private const string strName = "OlapSample"; /**//// <summary> /// 服务器的实例 /// </summary> analysis.Server serverAnalysis; /**//// <summary> /// 创建对象的类 /// </summary> Olap olap = new Olap(); public Form1() ...{ InitializeComponent(); } //生成多维数据 private void btnBuild_Click(object sender, EventArgs e) ...{ serverAnalysis = new Microsoft.AnalysisServices.Server(); try ...{ serverAnalysis.Connect("Data Source = " + strServerName); olap = new Olap(); analysis.Database db = serverAnalysis.Databases.FindByName(strDataBaseName); if (db != null) ...{ db.Drop(); } else ...{ db = serverAnalysis.Databases.Add(strDataBaseName); db.Update(); olap.CreateDataSource(db, strName, strConnectionString); olap.CreateDataSourceView(db, strName, strName); olap.CreateGeographyDimension(db, strName); olap.CreateCustomerDimension(db, strName); olap.CreateCube(db, strName); } } catch (analysis.AmoException ex) ...{ MessageBox.Show(ex.Message); } } }} using System; using System.Collections.Generic; using System.Text; using Microsoft.AnalysisServices; using System.Data; using System.Data.OleDb; using System.Globalization; namespace BuildOLAP ... { public class Olap ...{ public Olap() ...{ } 创建多维数据库#region 创建多维数据库 /**//// <summary> /// 创建数据源 /// </summary> /// <param name="parDatabase">数据库</param> /// <param name="parDataSourceName">数据源名</param> /// <param name="parConnString">连接字符</param> public void CreateDataSource(Database parDatabase, string parDataSourceName, string parConnString) ...{ try ...{ DataSource ds = parDatabase.DataSources.Add(parDataSourceName); ds.ConnectionString = parConnString; ds.Update(); } catch ...{ throw new AmoException("error!"); } } /**//// <summary> /// 创建数据源视图 /// </summary> /// <param name="parDatabase">数据库</param> /// <param name="parDataSourceName">数据源名</param> /// <param name="parDataSourceViewName">数据源视图名</param> public void CreateDataSourceView(Database parDatabase, string parDataSourceName, string parDataSourceViewName) ...{ DataSourceView dsv = parDatabase.DataSourceViews.Add(parDataSourceViewName); dsv.DataSourceID = parDataSourceName; dsv.Schema = new DataSet(); //Open a connection to the data source OleDbConnection connection = new OleDbConnection(dsv.DataSource.ConnectionString); connection.Open(); //增加 DimGeography表 AddTable(dsv, connection, "DimGeography"); //AddComputedColumn(dsv, connection, "DimCustomer", "FullName", "CASE WHEN MiddleName IS NULL THEN FirstName + ' ' + LastName ELSE FirstName + ' ' + MiddleName + ' ' + LastName END"); //AddComputedColumn(dsv, connection, "DimCustomer", "GenderDesc", "CASE WHEN Gender = 'M' THEN 'Male' ELSE 'Female' END"); //AddComputedColumn(dsv, connection, "DimCustomer", "MaritalStatusDesc", "CASE WHEN MaritalStatus = 'S' THEN 'Single' ELSE 'Married' END"); //增加 DimCustomer表 AddTable(dsv, connection, "DimCustomer"); AddRelation(dsv, "DimCustomer", "GeographyKey", "DimGeography", "GeographyKey"); //增加 FactInternetSales表 AddTable(dsv, connection, "FactInternetSales"); AddRelation(dsv, "FactInternetSales", "CustomerKey", "DimCustomer", "CustomerKey"); dsv.Update(); } /**//// <summary> /// 创建表 /// </summary> /// <param name="parDsv">数据源视图</param> /// <param name="parConn">连接到数据仓库</param> /// <param name="parTable">表名</param> public void AddTable(DataSourceView dsv, OleDbConnection connection, String tableName) ...{ OleDbDataAdapter adapter = new OleDbDataAdapter( "SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0", connection); DataTable[] dataTables = adapter.FillSchema(dsv.Schema, //将表的结构映射到数据源视图 SchemaType.Mapped, tableName); DataTable dataTable = dataTables[0]; dataTable.ExtendedProperties.Add("TableType", "Table"); //为表增加扩展属性 dataTable.ExtendedProperties.Add("DbSchemaName", "dbo"); dataTable.ExtendedProperties.Add("DbTableName", tableName); dataTable.ExtendedProperties.Add("FriendlyName", tableName); } /**//// <summary> /// 创建关系 /// </summary> /// <param name="parDsv">数据源视图</param> /// <param name="parFkTableName">外键表名</param> /// <param name="parFkColumnName">外键列名</param> /// <param name="parPkTableName">主键表名</param> /// <param name="parPkColumnName">主键列名</param> public void AddRelation(DataSourceView parDsv, string parFkTableName, string parFkColumnName, string parPkTableName, string parPkColumnName) ...{ DataColumn fkColumn = parDsv.Schema.Tables[parFkTableName].Columns[parFkColumnName]; DataColumn pkColumn = parDsv.Schema.Tables[parPkTableName].Columns[parPkColumnName]; parDsv.Schema.Relations.Add("FK_" + parFkTableName + "_" + parPkColumnName, pkColumn, fkColumn); } /**//// <summary> /// 创建项 /// </summary> /// <param name="parDsv">数据源视图</param> /// <param name="parTableName">表名</param> /// <param name="parColumnName">列名</param> /// <returns></returns> public DataItem CreateDataItem(DataSourceView parDsv, string parTableName, string parColumnName) ...{ DataTable dataTable = ((DataSourceView)parDsv).Schema.Tables[parTableName]; DataColumn dataColumn = dataTable.Columns[parColumnName]; return new DataItem(parTableName, parColumnName, OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType)); } public void AddComputedColumn(DataSourceView dsv, OleDbConnection connection, String tableName, String computedColumnName, String expression) ...{ DataSet tmpDataSet = new DataSet(); tmpDataSet.Locale = CultureInfo.CurrentCulture; OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT (" + expression + ") AS [" + computedColumnName + "] FROM [dbo].[" + tableName + "] WHERE 1=0", connection); DataTable[] dataTables = adapter.FillSchema(tmpDataSet, SchemaType.Mapped, tableName); DataTable dataTable = dataTables[0]; DataColumn dataColumn = dataTable.Columns[computedColumnName]; dataTable.Constraints.Clear(); dataTable.Columns.Remove(dataColumn); dataColumn.ExtendedProperties.Add("DbColumnName", computedColumnName); dataColumn.ExtendedProperties.Add("ComputedColumnExpression", expression); dataColumn.ExtendedProperties.Add("IsLogical", "True"); dsv.Schema.Tables[tableName].Columns.Add(dataColumn); } /**//// <summary> /// 创建Geography维度 /// </summary> /// <param name="db"></param> public void CreateGeographyDimension(Database db, string parDataSourceName) ...{ // Create the Geography dimension Dimension dim = db.Dimensions.Add("Geography"); dim.Type = DimensionType.Geography; dim.UnknownMember = UnknownMemberBehavior.Hidden; dim.AttributeAllMemberName = "All Geographies"; dim.Source = new DataSourceViewBinding(parDataSourceName); dim.StorageMode = DimensionStorageMode.Molap; Create attributes#region Create attributes DimensionAttribute attr; attr = dim.Attributes.Add("Geography Key"); attr.Usage = AttributeUsage.Key; attr.OrderBy = OrderBy.Name; attr.AttributeHierarchyVisible = false; attr.AttributeHierarchyOptimizedState = OptimizationType.NotOptimized; attr.AttributeHierarchyOrdered = false; attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "GeographyKey")); attr = dim.Attributes.Add("Country-Region"); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "EnglishCountryRegionName")); attr = dim.Attributes.Add("State-Province"); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "CountryRegionCode")); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "StateProvinceCode")); attr.NameColumn = CreateDataItem(db.DataSourceViews[0], "DimGeography", "StateProvinceName"); attr.AttributeRelationships.Add(new AttributeRelationship("Country-Region")); attr = dim.Attributes.Add("City"); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "StateProvinceCode")); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "City")); attr.NameColumn = CreateDataItem(db.DataSourceViews[0], "DimGeography", "City"); attr.AttributeRelationships.Add(new AttributeRelationship("State-Province")); attr = dim.Attributes.Add("Postal Code"); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "City")); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "PostalCode")); attr.NameColumn = CreateDataItem(db.DataSourceViews[0], "DimGeography", "PostalCode"); attr.AttributeRelationships.Add(new AttributeRelationship("City")); #endregion Create hierarchies#region Create hierarchies Hierarchy hier; hier = dim.Hierarchies.Add("Geography"); hier.AllMemberName = "All Geographies"; hier.Levels.Add("Country-Region").SourceAttributeID = "Country-Region"; hier.Levels.Add("State-Province").SourceAttributeID = "State-Province"; hier.Levels.Add("City").SourceAttributeID = "City"; hier.Levels.Add("Postal Code").SourceAttributeID = "Postal Code"; #endregion dim.Update(); } /**//// <summary> /// 创建维度 /// </summary> /// <param name="db"></param> public void CreateCustomerDimension(Database db,string parDataSourceName) ...{ // Create the Customer dimension Dimension dim = db.Dimensions.Add("Customer"); dim.Type = DimensionType.Customers; dim.UnknownMember = UnknownMemberBehavior.Hidden; dim.AttributeAllMemberName = "All Customers"; dim.Source = new DataSourceViewBinding(parDataSourceName); dim.StorageMode = DimensionStorageMode.Molap; Create attributes#region Create attributes DimensionAttribute attr; attr = dim.Attributes.Add("Full Name"); attr.Usage = AttributeUsage.Key; attr.Type = AttributeType.Customers; attr.OrderBy = OrderBy.Name; attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimCustomer", "CustomerKey")); //增加国家区域属性,从DimGeography表映射该字段 attr = dim.Attributes.Add("Country-Region"); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "EnglishCountryRegionName")); //增加国家,省,从DimGeography表映射该字段 //增加一个命名列 attr = dim.Attributes.Add("State-Province"); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "CountryRegionCode")); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "StateProvinceCode")); attr.NameColumn = CreateDataItem(db.DataSourceViews[0], "DimGeography", "StateProvinceName"); attr.AttributeRelationships.Add(new AttributeRelationship("Country-Region")); attr = dim.Attributes.Add("City"); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "StateProvinceCode")); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "City")); attr.NameColumn = CreateDataItem(db.DataSourceViews[0], "DimGeography", "City"); attr.AttributeRelationships.Add(new AttributeRelationship("State-Province")); attr = dim.Attributes.Add("Postal Code"); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "City")); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimGeography", "PostalCode")); attr.NameColumn = CreateDataItem(db.DataSourceViews[0], "DimGeography", "PostalCode"); attr.AttributeRelationships.Add(new AttributeRelationship("City")); attr = dim.Attributes.Add("Education"); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimCustomer", "EnglishEducation")); attr = dim.Attributes.Add("Email Address"); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimCustomer", "EmailAddress")); //attr = dim.Attributes.Add("Gender"); //attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimCustomer", "GenderDesc")); //attr = dim.Attributes.Add("Marital Status"); //attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimCustomer", "MaritalStatusDesc")); attr = dim.Attributes.Add("Occupation"); attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimCustomer", "EnglishOccupation")); attr = dim.Attributes.Add("Phone"); attr.AttributeHierarchyEnabled = false; attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "DimCustomer", "Phone")); #endregion Create hierarchies#region Create hierarchies Hierarchy hier; hier = dim.Hierarchies.Add("Customer Geography"); hier.AllMemberName = "All Customers"; hier.Levels.Add("Country-Region").SourceAttributeID = "Country-Region"; hier.Levels.Add("State-Province").SourceAttributeID = "State-Province"; hier.Levels.Add("City").SourceAttributeID = "City"; hier.Levels.Add("Postal Code").SourceAttributeID = "Postal Code"; hier.Levels.Add("Full Name").SourceAttributeID = "Full Name"; #endregion dim.Update(); } /**//// <summary> /// 创建Cube /// </summary> /// <param name="db">数据库</param> /// <param name="parDataSourceName">数据源</param> public void CreateCube(Database db,string parDataSourceName) ...{ // Create the Adventure Works cube Cube cube = db.Cubes.Add("Adventure Works"); cube.Source = new DataSourceViewBinding(parDataSourceName); cube.StorageMode = StorageMode.Molap; 将维度添加到Cube中#region 将维度添加到Cube中 Dimension dim; dim = db.Dimensions.GetByName("Customer"); cube.Dimensions.Add(dim.ID); #endregion 创建度量值组#region 创建度量值组 CreateInternetSalesMeasureGroup(cube); #endregion cube.Update(UpdateOptions.ExpandFull); } public void CreateInternetSalesMeasureGroup(Cube cube) ...{ // Create the Internet Sales measure group Database db = cube.Parent; MeasureGroup mg = cube.MeasureGroups.Add("Internet Sales"); mg.StorageMode = StorageMode.Molap; mg.ProcessingMode = ProcessingMode.LazyAggregations; mg.Type = MeasureGroupType.Sales; Create measures#region Create measures Measure meas; //meas = mg.Measures.Add("Internet Sales Amount"); //加一个维度 //meas.AggregateFunction = AggregationFunction.Sum; //维度的计算方法 //meas.MeasureExpression = "[Internet Sales Amount] * [Average Rate]"; //维度表达式 //meas.FormatString = "Currency"; //维度格式字符 //meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "SalesAmount"); // meas = mg.Measures.Add("Internet Order Quantity"); meas.AggregateFunction = AggregationFunction.Sum; meas.FormatString = "#,#"; meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "OrderQuantity"); meas = mg.Measures.Add("Internet Unit Price"); meas.AggregateFunction = AggregationFunction.Sum; meas.FormatString = "Currency"; meas.Visible = false; meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "UnitPrice"); //meas = mg.Measures.Add("Internet Total Product Cost"); //meas.AggregateFunction = AggregationFunction.Sum; //meas.MeasureExpression = "[Internet Total Product Cost] * [Average Rate]"; //meas.FormatString = "Currency"; //meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "TotalProductCost"); meas = mg.Measures.Add("Internet Order Count"); meas.AggregateFunction = AggregationFunction.Count; meas.FormatString = "#,#"; meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "ProductKey"); #endregion Create measure group dimensions#region Create measure group dimensions CubeDimension cubeDim; RegularMeasureGroupDimension regMgDim; ManyToManyMeasureGroupDimension mmMgDim; MeasureGroupAttribute mgAttr; //cubeDim = cube.Dimensions.GetByName("Date"); //regMgDim = new RegularMeasureGroupDimension(cubeDim.ID); //mg.Dimensions.Add(regMgDim); //mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Date").ID); //mgAttr.Type = MeasureGroupAttributeType.Granularity; //mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "OrderDateKey")); //cubeDim = cube.Dimensions.GetByName("Ship Date"); //regMgDim = new RegularMeasureGroupDimension(cubeDim.ID); //mg.Dimensions.Add(regMgDim); //mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Date").ID); //mgAttr.Type = MeasureGroupAttributeType.Granularity; //mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "ShipDateKey")); //cubeDim = cube.Dimensions.GetByName("Delivery Date"); //regMgDim = new RegularMeasureGroupDimension(cubeDim.ID); //mg.Dimensions.Add(regMgDim); //mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Date").ID); //mgAttr.Type = MeasureGroupAttributeType.Granularity; //mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "DueDateKey")); cubeDim = cube.Dimensions.GetByName("Customer"); regMgDim = new RegularMeasureGroupDimension(cubeDim.ID); mg.Dimensions.Add(regMgDim); mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Full Name").ID); mgAttr.Type = MeasureGroupAttributeType.Granularity; mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "CustomerKey")); //cubeDim = cube.Dimensions.GetByName("Product"); //regMgDim = new RegularMeasureGroupDimension(cubeDim.ID); //mg.Dimensions.Add(regMgDim); //mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Product Name").ID); //mgAttr.Type = MeasureGroupAttributeType.Granularity; //mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "ProductKey")); //cubeDim = cube.Dimensions.GetByName("Source Currency"); //regMgDim = new RegularMeasureGroupDimension(cubeDim.ID); //mg.Dimensions.Add(regMgDim); //mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Currency").ID); //mgAttr.Type = MeasureGroupAttributeType.Granularity; //mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "CurrencyKey")); //cubeDim = cube.Dimensions.GetByName("Sales Reason"); //mmMgDim = new ManyToManyMeasureGroupDimension(); //mmMgDim.CubeDimensionID = cubeDim.ID; //mmMgDim.MeasureGroupID = cube.MeasureGroups.GetByName("Sales Reasons").ID; //mg.Dimensions.Add(mmMgDim); //cubeDim = cube.Dimensions.GetByName("Internet Sales Order Details"); //regMgDim = new RegularMeasureGroupDimension(cubeDim.ID); //mg.Dimensions.Add(regMgDim); //mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Sales Order Key").ID); //mgAttr.Type = MeasureGroupAttributeType.Granularity; //mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "SalesOrderNumber")); //mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "SalesOrderLineNumber")); #endregion Create partitions#region Create partitions Partition part; part = mg.Partitions.Add("Internet_Sales_184"); part.StorageMode = StorageMode.Molap; part.Source = new QueryBinding(db.DataSources[0].ID, "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey <= '184'"); part.Annotations.Add("LastOrderDateKey", "184"); part = mg.Partitions.Add("Internet_Sales_549"); part.StorageMode = StorageMode.Molap; part.Source = new QueryBinding(db.DataSources[0].ID, "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey > '184' AND OrderDateKey <= '549'"); part.Annotations.Add("LastOrderDateKey", "549"); part = mg.Partitions.Add("Internet_Sales_914"); part.StorageMode = StorageMode.Molap; part.Source = new QueryBinding(db.DataSources[0].ID, "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey > '549' AND OrderDateKey <= '914'"); part.Annotations.Add("LastOrderDateKey", "914"); #endregion cube.Update(UpdateOptions.ExpandFull); } #endregion }}