SSAS OLAP部分,这里,试验、小结书中开发一个SSAS(Analysis Services) Cube的基本知识:
- 多维数据模型
- 开发SSAS Cube的基本步骤:创建数据源 --> 创建数据源视图DSV --> 根据向导创建多维数据集Cube --> 修改Cube中的维度和度量 --> 部署 --> 测试
- 自定义数据源视图
- 配置或新增维度及维度属性
- 配置或新增度量组及度量
一般,SSAS Cube可以在Excel,SSRS,proclarity(这个不是很熟悉),自定义开发程序,第三方工具中提供最终用户使用。
书名:MCTS Self-Paced TrainingKit(Exam 70-448): Microsoft SQL Server 2008 - Business Intelligence Development and Maintenance
第五章 开发SSAS Cubes(多维数据集)
课程1:创建数据源和视图
1 多维数据模型,以AdventureWorksDW示例数据库为例,截图来源此书。
1) FactInternetSales为事实表(facts),存储数值数据,衡量每个销售信息。
2) Dim开始的表都是维度表(Dimensions),支持用户按各个主题查看销售数据。
3) 事实表(FactInternetSales)与多维数据表的连接属于星型模式。
4) 这种优化过的数据结构更利于数据分析和报表。
2 UDM由以下几个组件组成
1) 数据源(Data Source),连接源数据。
2) 数据源视图(Data source view, 简称:DSV),抽象底层数据库结构(Schema)。
3) 多维模型(Dimensional model),创建好一个DSV,下一步构建多维模型,定义cube的度量与维度(属性或多重层次结构)。
4) 计算(Calculations)可选。
5) 最终用户模型(End-user model)可选。
6) 管理配置(management settings)
3
4
1) 选择对象
2) 创建DSV主健与表关系
3) 使用named calculations或named queries重定义DSV
5 动手试验
练习1:创建一个SSAS项目
打开BIDS,新建项目,选择Analysis Services项目类型,输入“TK 70-448 SSAS Project”项目名。
练习2:创建数据源(Data Source)
右击数据源(Data Source),新建,根据向导,配置下列属性,创建好一个名为“Adventure Works DW2008R2.ds”的数据源。
Provider: Native OLE DB\SQL Server Native Client 10.0
Server name: (local)
database: 选AdventureWorksDW,实际选了AdventureWorksDW2008R2
练习3:创建一个DSV
1)右击DSV,新建,根据向导,创建一个名为“Adventure Works DW2008R2.dsv”的数据视图。
选择练习2创建好的数据源,并选中下列表。
FactInternetSales
DimProduct
DimSalesTerritory
DimCurrency
FactInternetSalesReason
DimDate
DimPromotion
DimCustomer
DimProudctCategory
DimProductSubcategory
DimSalesReason
主键及表关系根据源数据结构自动创建。
2)双击1)创建好的DSV,在“表”(Tables)视图中,对“DimCustomer”表添加一个FullName字段,右击DimCustomer表,选“new named calculation”,列名(column name)输入FullName,表达式(Expression)输入FirstName + ' ' + LastName。
3)同2),对“DimData”表添加4个新字段:
SimpleDate = datename(mm,FullDateAlternateKey) + ' ' + datename(dd,FullDateAlternateKey) + ',' +
MonthName = EnglishMonthName + ' ' + convert(char(4),CalendarYear)
CalendarQuarterOfYear = 'CY Q' + convert(char(1),CalendarQuarter)
FiscalQuarterOfYear = 'FY Q' + convert(char(1),fiscalQuarter)
课程2:创建和修改SSAS Cubes
1 使用Cube向导
2 使用Cube设计器修改一个Cube
1) Cube设计器的组成:
- cube structure
- dimension usage
- calculations
- KPIs
- actions
- partitions
- aggregations
- perspectives
- translations
- browser
2) cube结构(cube structure)
a. 度量属性
b. 维度属性
3 动手试验
练习1: 创建一个Cube。
1) 完成课程1练习1后,根据向导,创建一个名为“Adventure Works”的立方体(Cube)。
a. 创建方式选使用现有表(use existing tables)
b. 创建度量组(meaure group talbes)步骤,选
c. 创建度量(measures)步骤,清除不适合用于组合的字段“revision number”和“unit price discount Pct”。
重命名FactInternetSales度量组为Internet Sales;重命名FactInternetSalesReason度量组为Internet Sales Reason;
重命名“Fact Internet Sales Count”度量为“Internet Sales Count”,重命名“Fact Internet Sales Reason Count”度量为“Internet Sales Reason Count”
d. 创建维度(Dimensions)步骤,不选Dim Product,重命名所有度量,清除Dim前缀。
2)完成向导后,名为“Adventure Works”的Cube以及维度就自动创建好了。接下来是部署该Cube。
3)将“Adventure Works”Cube部署到本地服务器:
a.右击项目,属性(Property)--> Deployment,确保Server属性是localhost。
b.右击项目,单击部署(Deploy)。
部署成功后,在Cube设计器中的“Browser”页面,使用类似数据透视表(PivotTable)的功能应用该Cube。
实际部署中,遇到一个权限访问的问题,通过修改数据源“Adventure Works DW2008R2.ds”中的“Impersonation Information”属性解决。
练习2:运用Cube设计器修改一个Cube的结构
1)双击“Adventure Works”Cube,显示该Cube设计器,Cube结构(cube structure)页面。
2)删除4个度量字段:将“度量”(Measures)部分,更改成Grid视图,默认Tree,选中以下4个字段,删除。
product standard cost total
product cost
tax amt
freight measures
3)选中以下4个字段,将其FormatString 属性更改成Currency。
unit price
extended amount
discound amount
sales amount
3)选中以下3个字段,将其FormatString 属性更改成#,#。
internet sales count
internet sales reson count
5)在Cube设计器的“浏览”页面,单击“Reconnect”,应用最新的Cube。
课程3:创建和修改维度(Dimensions)
1 创建一个维度
2 修改维度属性
重要的几个属性配置(Attribute Properties)
- name
- keyColumns
- nameColumn
- usage;attributeHierarchyEnable
- isAggregatable
- orderBy;oderByAttribute
- type
3 将维度添加至Cubes
4 动手试验
练习1:根据维度(Dimension)向导生成一个新维度。
1)生成一个名为“Product”的新维度,右击“Dimensions”,新维度。
a.生成方法:选使用现有表(use an existing table)
b.源数据信息配置:
- data source view:
- main table:
- Key column:
- Name column:
c.有关联的表
d.维度属性(Dimension attributes),除默认选中的字段外,选中下列字段:
color
listPrice
sizeRange
modelName
EnglishDescription
EnglishProductCategoryNa
EnglishProductSubcategor
e.维度命名为Product。
2)保存并部署。
练习2:修改维度(Dimensions)及属性(Attribute Properties)
1 更改Currency维度
1) 将“Currency Key”属性重命名为“Currency”,NameColumn设为CurrencyName。
2 更改Customer维度
1)将“Customer Key:属性重命名为“Customer”,NameColumn = fullName,OrderBy = name。
2)在“Data Source view”中通过拖拽下列字段的方式添加属性(attributes)。
addressline1 addressline2 birthdate commutedistance datefirstpurchase emailaddress
englisheducation englishoccupation gender houseownerflag maritalstatus
numbercarsowned numberchildrenathome phone title totalchildren yearlyIncome
3)重命名两个属性名
englisheducation -> education
englishoccupation -> occupation
4) 通过文件夹组织Customer维度属性
选中下列属性,设attibuteHierarchyDisplay
addressline1 addressline2 emailaddress phone
选中下列属性,设attibuteHierarchyDisplay
commutedistance education gender maritalstatus houseownerflag numbercarsowned
numberchildrenathome occupation totalchildren yearlyIncome
设好的效果:
1)将Date维度的type属性设为Time,该维度是Time Dimension。
2)将“Date Key”属性重命名为Date,NameColumn = simpleDate,type = Date -> Calendar -> Date。
3)添加下列几个属性
MonthNumberOfYear CalendarQuarter CalendarYear FiscalQuarter FiscalYear
4)CalendarQuarter属性,NameColumn = CalendarQuarterOfYear;Type = Date -> Canlendar -> quarters
5) FiscalQuarter属性,NameColumn = FiscalQuarterOfYear;Type = Date -> Fiscal -> Fiscalquarters
6)MonthNumberOfYear属性重命名为Month Name,NameColumn =
7)CalendarYear属性,Type = Date -> Calendar -> years
8)FiscalYear属性,Type = Date -> fiscal -> fiscalYears
9)Process该cube,下列错误出现,由于部分属性(例如:month name)name column有重复的键值,需添加字段更新主键。
10)Month Name属性KeyColumns
11)CalendarQuarter属性KeyColumns
12)FiscalQuarter属性KeyColumns 添加FiscalYear。
设好的效果:
4 更改Pomotion维度
1)将Promotion Key属性重命名为Promotiion,NameColumn = EnglishPromotionName,OrderBy = name。
2)添加下列几个属性:
DiscountPct StartDate EndDate EnglishPromotionType EnglishPromotionCategory
5 更改Sales Reason维度
1)将Sales Reason Key属性重命名为Sales Reason,NameColumn = SalesReasonName,OrderBy = Name。
6 更改Sales Territory维度
1)将Sales Territory Key属性重命名为Sales Territory Region,NameColumn = SalesTerritoryRegion,OrderBy = Name。
2)添加下列2个属性:
SalesTerritoryCountry
SalesTerritoryGroup
7 更改Product维度
1)将Poduct Key属性重命名为Product。
2)将English Description属性重命名为Description。
3)将Product Category Key属性重命名为Category,nameColumn = EnglishProductCaegoryNam
4)将Subcategory Key属性重名为Subcategory,nameColumn = EnglishProductSubcategor
5)由于设了3)及4),删除下列2个属性。
English Product Subcategory Name
English Product Category Name
8 保存更新并部署
练习3:将维度添加至现有Cube。
将Product维度添加至Adventure Works Cube。
课程3:创建度量组(Measure Group)和度量(Measures)
1 创建一个度量组
2 创建度量并配置其属性
几个主要的度量属性:
- aggregateFunction
- dataType
- displayFolder
- formatString
- measureExpression
- source
- visible
练习1:添加度量组
1)由于新增的度量不在现有的DSV中,第一步将下列两张事实表添加至之前创建好的DSV中。
- FactResellerSales
- FactCurrencyRates
2)打开“Adventure Works cube”,新建一个度量组(new measures group),选FactResellerSales表。
3)2)完成后,删除下列不适合作为度量的字段
reseller Key, employee key,revision number
为减少度量的数量,删除下列度量:
product standard cost, total product cost, tax amt, freight,unit price discount Pct
4)同2)-3),添加Fact currency rate度量组。
练习2:配置度量组及度量
1)重命名Fact Reseller Sales度量组为Reseller Sales,重命名Fact Currency Rate度量组为Currency Rate。
2)重命名Internect Sales度量组中的度量,全部添加Internet前缀。
3)重命名Reseller sales度量组中的度量,删除后缀,全部添加Reseller前缀。
4)配置下列度量FormatString = currency
reseller unit price
reseller extended amount
reseller discount amount
reseller sales amount
5)配置下列度量FormatString = #,#
reseller order quantity
reseller sales count
6)将reseller sales amount度量值转换为美元,配置MeasureExpression属性。
MeasureExpression = [Reseller Sales Amount]/[Average Rate]
练习3:创建一个Distinct Count分组的度量
1)右击Internet Sales度量组,新建一个新度量,选CustomerKey列,组合方式(usage)选
完成后,自动生成一个度量组,将组名更名为Internet Customers,将度量“Customer Key Distinct Count”更名为Customer Count。