SSAS(1)

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） 这种优化过的数据结构更利于数据分析和报表。

    多维数据结构建立后，就可以创建统一多维模型（unified dimensional model，简称UDM），通常称作SSAS Cube。立方体（Cube）是一个逻辑的存储对象，用来组织维度和度量，以一个多维的视图呈现数据。

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 定义一个数据源视图（DSV）
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) + ',' +
      datename(yy,FullDateAlternateKey)
MonthName = EnglishMonthName + ' ' + convert(char(4),CalendarYear)
CalendarQuarterOfYear = 'CY Q' + convert(char(1),CalendarQuarter)

FiscalQuarterOfYear = 'FY Q' + convert(char(1),fiscalQuarter)

 

课程2：创建和修改SSAS Cubes
   构建底层数据结构的数据源和DSV创建后，接下来的任务是创建UDM中的多维层次，包括设计cubes、维度、度量组(measure groups)和度量值。一般，在BIDS，根据“SSAS Cube”向导，设计出一个Cube，再使用BIDS Cube设计器，修改该Cube的定义。
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）步骤，选 FactInternetSales和FactInternetSalesReason事实表。
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 属性更改成#,#。

order quantity
internet sales count
internet sales reson count

4) 保存修改，将更改重新部署到本地服务器上。

5）在Cube设计器的“浏览”页面，单击“Reconnect”，应用最新的Cube。

 

课程3：创建和修改维度（Dimensions）
1 创建一个维度
2 修改维度属性

重要的几个属性配置（Attribute Properties）

- name
- keyColumns
- nameColumn
- usage;attributeHierarchyEnabled;attributeHierarchyVisible
- isAggregatable
- orderBy;oderByAttribute
- type
3 将维度添加至Cubes
4 动手试验
练习1：根据维度（Dimension）向导生成一个新维度。

1）生成一个名为“Product”的新维度，右击“Dimensions”，新维度。

a.生成方法：选使用现有表（use an existing table）
b.源数据信息配置：
- data source view: adventure Works DW2008R2
- main table:  选DimProduct
- Key column:  ProductKey（默认）
- Name column:  EnglishProductName
c.有关联的表   DimProductSubcategory和DimProductCategory默认选中
d.维度属性（Dimension attributes），除默认选中的字段外，选中下列字段：
color
listPrice
sizeRange
modelName
EnglishDescription
EnglishProductCategoryName
EnglishProductSubcategoryName

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维度属性

选中下列属性，设attibuteHierarchyDisplayFolder = Contact

addressline1 addressline2 emailaddress phone

选中下列属性，设attibuteHierarchyDisplayFolder = Demographic

commutedistance education gender maritalstatus houseownerflag numbercarsowned
numberchildrenathome occupation totalchildren yearlyIncome

设好的效果：

3 更改Date维度

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 =  MonthName；Type = Date -> Calendar -> months

7）CalendarYear属性，Type = Date -> Calendar -> years

8）FiscalYear属性，Type = Date -> fiscal -> fiscalYears

9）Process该cube，下列错误出现，由于部分属性（例如：month name）name column有重复的键值，需添加字段更新主键。
 Errors in the OLAP storage engine: A duplicate attribute key  has been found when processing: Table: 'dbo_DimDate', Column: 'MonthNumberOfYear', Value: '10'. The attribute is 'Month Name'.

10）Month Name属性KeyColumns 添加 CalendarYear。

11）CalendarQuarter属性KeyColumns 添加 CalendarYear。

12）FiscalQuarter属性KeyColumns 添加FiscalYear。

设好的效果：

4 更改Pomotion维度
1）将Promotion Key属性重命名为Promotiion，NameColumn = EnglishPromotionName，OrderBy = name。

2）添加下列几个属性：

DiscountPct StartDate EndDate EnglishPromotionType EnglishPromotionCategory MinQty MaxQty

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 = EnglishProductCaegoryName，orderBy = name。

4）将Subcategory Key属性重名为Subcategory，nameColumn = EnglishProductSubcategoryName，OderBy = name。

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）选 DistinctCount。

完成后，自动生成一个度量组，将组名更名为Internet Customers，将度量“Customer Key Distinct Count”更名为Customer Count。