//例子 static DataSourceView CreateDataSourceView(Database db, string strDataSourceName) { // Create the data source view DataSourceView dsv = db.DataSourceViews.FindByName(strDataSourceName); if (dsv != null) { dsv.Drop(); } dsv = db.DataSourceViews.Add(strDataSourceName, strDataSourceName); dsv.DataSourceID = strDataSourceName; dsv.Schema = new DataSet(); dsv.Schema.Locale = CultureInfo.CurrentCulture; // Open a connection to the data source OleDbConnection connection = new OleDbConnection(dsv.DataSource.ConnectionString); connection.Open(); #region Create tables // Add the DimTime table AddTable(dsv, connection, "DimTime"); AddComputedColumn(dsv, connection, "DimTime", "SimpleDate", "DATENAME(mm, FullDateAlternateKey) + ' ' + DATENAME(dd, FullDateAlternateKey) + ',' + ' ' + DATENAME(yy, FullDateAlternateKey)"); AddComputedColumn(dsv, connection, "DimTime", "CalendarYearDesc", "'CY' + ' ' + CalendarYear"); AddComputedColumn(dsv, connection, "DimTime", "CalendarSemesterDesc", "CASE WHEN CalendarSemester = 1 THEN 'H1'+' '+ 'CY' +' '+ CONVERT(CHAR (4), CalendarYear) ELSE 'H2'+' '+ 'CY' +' '+ CONVERT(CHAR (4), CalendarYear) END"); AddComputedColumn(dsv, connection, "DimTime", "CalendarQuarterDesc", "'Q' + CONVERT(CHAR (1), CalendarQuarter) +' '+ 'CY' +' '+ CONVERT(CHAR (4), CalendarYear)"); AddComputedColumn(dsv, connection, "DimTime", "MonthName", "EnglishMonthName+' '+ CONVERT(CHAR (4), CalendarYear)"); AddComputedColumn(dsv, connection, "DimTime", "FiscalYearDesc", "'FY' + ' ' + FiscalYear"); AddComputedColumn(dsv, connection, "DimTime", "FiscalSemesterDesc", "CASE WHEN FiscalSemester = 1 THEN 'H1'+' '+ 'FY' +' '+ CONVERT(CHAR (4), FiscalYear) ELSE 'H2'+' '+ 'FY' +' '+ CONVERT(CHAR (4), FiscalYear) END"); AddComputedColumn(dsv, connection, "DimTime", "FiscalQuarterDesc", "'Q' + CONVERT(CHAR (1), FiscalQuarter) +' '+ 'FY' +' '+ CONVERT(CHAR (4), FiscalYear)"); AddComputedColumn(dsv, connection, "DimTime", "FiscalMonthNumberOfYear", "CASE WHEN MonthNumberOfYear = '1' THEN CONVERT(int,'7') WHEN MonthNumberOfYear = '2' THEN CONVERT(int,'8') WHEN MonthNumberOfYear = '3' THEN CONVERT(int,'9') WHEN MonthNumberOfYear = '4' THEN CONVERT(int,'10') WHEN MonthNumberOfYear = '5' THEN CONVERT(int,'11') WHEN MonthNumberOfYear = '6' THEN CONVERT(int,'12') WHEN MonthNumberOfYear = '7' THEN CONVERT(int,'1') WHEN MonthNumberOfYear = '8' THEN CONVERT(int,'2') WHEN MonthNumberOfYear = '9' THEN CONVERT(int,'3') WHEN MonthNumberOfYear = '10' THEN CONVERT(int,'4') WHEN MonthNumberOfYear = '11' THEN CONVERT(int,'5') WHEN MonthNumberOfYear = '12' THEN CONVERT(int,'6') END"); dsv.Update(); // Add the DimGeography table AddTable(dsv, connection, "DimGeography"); // Add the DimProductCategory table AddTable(dsv, connection, "DimProductCategory"); // Add the DimProductSubcategory table AddTable(dsv, connection, "DimProductSubcategory"); AddRelation(dsv, "DimProductSubcategory", "ProductCategoryKey", "DimProductCategory", "ProductCategoryKey"); // Add the DimProduct table AddTable(dsv, connection, "DimProduct"); AddComputedColumn(dsv, connection, "DimProduct", "ProductLineName", "CASE ProductLine WHEN 'M' THEN 'Mountain' WHEN 'R' THEN 'Road' WHEN 'S' THEN 'Accessory' WHEN 'T' THEN 'Touring' ELSE 'Components' END"); AddRelation(dsv, "DimProduct", "ProductSubcategoryKey", "DimProductSubcategory", "ProductSubcategoryKey"); dsv.Update(); // Add the DimCustomer table AddTable(dsv, connection, "DimCustomer"); 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"); AddRelation(dsv, "DimCustomer", "GeographyKey", "DimGeography", "GeographyKey"); // Add the DimReseller table AddTable(dsv, connection, "DimReseller"); AddComputedColumn(dsv, connection, "DimReseller", "OrderFrequencyDesc", "CASE WHEN OrderFrequency = 'A' THEN 'Annual' WHEN OrderFrequency = 'S' THEN 'Bi-Annual' ELSE 'Quarterly' END"); AddComputedColumn(dsv, connection, "DimReseller", "OrderMonthDesc", "CASE WHEN OrderMonth = '1' THEN 'January' WHEN OrderMonth = '2' THEN 'February' WHEN OrderMonth = '3' THEN 'March' WHEN OrderMonth = '4' THEN 'April' WHEN OrderMonth = '5' THEN 'May' WHEN OrderMonth = '6' THEN 'June' WHEN OrderMonth = '7' THEN 'July' WHEN OrderMonth = '8' THEN 'August' WHEN OrderMonth = '9' THEN 'September' WHEN OrderMonth = '10' THEN 'October' WHEN OrderMonth = '11' THEN 'November' WHEN OrderMonth = '12' THEN 'December' ELSE 'Never Ordered' END"); // Add the DimCurrency table AddTable(dsv, connection, "DimCurrency"); dsv.Update(); // Add the DimSalesReason table AddTable(dsv, connection, "DimSalesReason"); // Add the FactInternetSales table AddTable(dsv, connection, "FactInternetSales"); AddRelation(dsv, "FactInternetSales", "ProductKey", "DimProduct", "ProductKey"); AddRelation(dsv, "FactInternetSales", "CustomerKey", "DimCustomer", "CustomerKey"); AddRelation(dsv, "FactInternetSales", "OrderDateKey", "DimTime", "TimeKey"); AddRelation(dsv, "FactInternetSales", "ShipDateKey", "DimTime", "TimeKey"); AddRelation(dsv, "FactInternetSales", "DueDateKey", "DimTime", "TimeKey"); AddRelation(dsv, "FactInternetSales", "CurrencyKey", "DimCurrency", "CurrencyKey"); dsv.Update(); // Add the FactResellerSales table AddTable(dsv, connection, "FactResellerSales"); AddRelation(dsv, "FactResellerSales", "ProductKey", "DimProduct", "ProductKey"); AddRelation(dsv, "FactResellerSales", "ResellerKey", "DimReseller", "ResellerKey"); AddRelation(dsv, "FactResellerSales", "OrderDateKey", "DimTime", "TimeKey"); AddRelation(dsv, "FactResellerSales", "ShipDateKey", "DimTime", "TimeKey"); AddRelation(dsv, "FactResellerSales", "DueDateKey", "DimTime", "TimeKey"); AddRelation(dsv, "FactResellerSales", "CurrencyKey", "DimCurrency", "CurrencyKey"); // Add the FactInternetSalesReason table AddTable(dsv, connection, "FactInternetSalesReason"); AddCompositeRelation(dsv, "FactInternetSalesReason", "FactInternetSales", "SalesOrderNumber", "SalesOrderLineNumber"); dsv.Update(); // Add the FactCurrencyRate table AddTable(dsv, connection, "FactCurrencyRate"); AddRelation(dsv, "FactCurrencyRate", "CurrencyKey", "DimCurrency", "CurrencyKey"); AddRelation(dsv, "FactCurrencyRate", "TimeKey", "DimTime", "TimeKey"); #endregion // Send the data source view definition to the server dsv.Update(); return(dsv); }