sqlserver SSAS 笔记

发布时间：2020-12-12 14:13:17 所属栏目：MsSql教程来源：网络整理

导读：学习 ?SQL Server 2008 Analysis Services ：第一步：下载 SQL Server 2008 ?的所有数据库实例(sample) ： http://www.cnblogs.com/bepare/archive/2009/04/30/1447179.html 默认安装之后到下去 C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDat

学习 ?SQL Server 2008 Analysis Services ：
第一步：下载 SQL Server 2008 ?的所有数据库实例(sample) ：

http://www.cnblogs.com/bepare/archive/2009/04/30/1447179.html
默认安装之后到下去 C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData 下找 AdventureWorksDW的数据库，然后再sql server2008上附加一下就ok了。
第二步：到下面

http://technet.microsoft.com/zh-TW/sqlserver/cc510300.aspx

---------------------------------------------------------------------------------mdx

《Microsoft SQL Server 2008 MDX Step by Step》学习笔记

http://blog.csdn.net/downmoon/article/details/6539747 ?

WITH member xxx as
( [Product].[Product Categories].[All],--[Product].[Product Categories].[All Products]
? ? ? ? ? ?[Measures].[Internet Sales-Sales Amount] )
--/////////////////////////////////
--WITH member xxx as
--( [Measures].[Internet Sales-Sales Amount]
--,[Product].[Product Categories].[All] --[Product].[Product Categories].[All Products]
-- ? )
--跟未注释的结果一样。
--//////////////////////////
SELECT ?
? ?--[Date].[Calendar Year].AllMembers ON COLUMNS,?
? ?xxx on columns,?
? ?[Product].[Model Name].Children ON ROWS ?
FROM [Analysis Services Tutorial] ?
等价于：
select [Product].[Model Name].&[All-Purpose Bike Stand] on columns,
[Measures].[Internet Sales-Sales Amount] on rows?
FROM [Analysis Services Tutorial]

结果都为￥39,591.00

------------------------------------

SELECT
{[Date].[Calendar].[CY 2002]}
*
{[Measures].[Reseller Sales Amount]}
ON COLUMNS,--{[Product].[Subcategory].[Mountain Bikes]}*{[Product].[Color].&[Black]}
EXISTS (
{[Product].[Category].[Category].Members} 
* {[Product].[Color].&[Black]},{([Product].[Subcategory].[Mountain Bikes])}
)
/*EXISTS (
{[Product].[Category].[Category].Members},{[Product].[Subcategory].[Mountain Bikes]}*{[Product].[Color].&[Black]})*/
ON ROWS
FROM [Step-by-Step]
where [Geography].[Country].[United States] --$6,314,977.53
--黑色的所有大类型商品(Bikes,Acessories,Clothing,Components,ikes&Acessories)
--列表中存在山地车的大类型

SELECT
{[Date].[Calendar].[CY 2002]}
*
{[Measures].[Reseller Sales Amount]}
ON COLUMNS,--{[Product].[Subcategory].[Mountain Bikes]}*{[Product].[Color].&[Black]}
{[Product].[Category].[Category].Members} * {[Product].[Color].&[Black]}
ON ROWS
FROM [Step-by-Step]
where [Geography].[Country].[United States] 
/*		CY 2002
		Reseller Sales Amount
Accessories	Black	$16,302.48
Bikes	Black	$6,977.53
Clothing	Black	$170,716.23
Components	Black	$1,122,092.81*/
--黑色的大类商品(Bikes,ikes&Acessories)


SELECT
{[Date].[Calendar].[CY 2002]}
*
{[Measures].[Reseller Sales Amount]}
ON COLUMNS,--{[Product].[Subcategory].[Mountain Bikes]}*{[Product].[Color].&[Black]}
{[Product].[Subcategory].[Mountain Bikes]}*{[Product].[Color].&[Black]}
ON ROWS
FROM [Step-by-Step]
where [Geography].[Country].[United States] --$3,927,415.56
--黑色的山地车

----------------------------------

mdx 聚合函数http://

http://www.cnblogs.com/jianjialin/archive/2012/09/19/2694108.html

一个简单的MDX案例及说明
http://www.cnblogs.com/SmartBizSoft/archive/2008/11/27/1341913.html

--------------------------------aggregate函数的说明:

对于[Measures].[Reseller Order Count]这个度量值在设计的时候为下图所示：

WITH
MEMBER [Product].[Subcategory].[AGG|SUM] AS
AGGREGATE(
{[Product].[Subcategory].&[1],[Product].[Subcategory].&[12]},
([Measures].[Reseller Order Count])
)
SELECT
[Measures].[Reseller Order Count] ON COLUMNS,
{[Product].[Subcategory].&[1],[Product].[Subcategory].&[12]} +
([Product].[Subcategory].[AGG|SUM]) ON ROWS
FROM [Step-by-Step]
上面这个statement执行的结果为：

WITH
MEMBER [Product].[Subcategory].[AGG|SUM] AS
sum(
{[Product].[Subcategory].&[1],[Product].[Subcategory].&[12]}
,([Measures].[Reseller Order Count])
)
SELECT
[Measures].[Reseller Order Count] ON COLUMNS,[Product].[Subcategory].&[12]} +
([Product].[Subcategory].[AGG|SUM]) ON ROWS
FROM [Step-by-Step]

上面这个statement执行的结果为：

因为[Measures].[Reseller Order Count] 度量值是“非重复计数”。也就是count(distinct SalesOrderNumber)。

那么上面这个sum是直接将得到的结果相加。而aggregate是使用非重复计数进行计算整个sets（集）的。具体解释如下：

select count(distinct SalesOrderNumber)
? from dbo.FactResellerSales
?where ProductKey in
? ? ? ?(288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,409,426,427,428,511,512,513,524,525,526,527,531,532,533,534,549,550,551)--860
select count(distinct SalesOrderNumber)
? from dbo.FactResellerSales
?where ProductKey in
? ? ? ?(344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,587,588,589,590,591,592,593,594,595,596,597,598,599,600)--1215
select count(distinct SalesOrderNumber)
? from dbo.FactResellerSales
?where ProductKey in
? ? ? ?(288,551
? ? ? ?--)
? ? ? ?,344,600)--1325

----------------------GENERATE ：

下面是返回“集”的解释；对于返回字符串的就不解释了，看MSDN文档就能够明白。

Set expression syntax
Generate( Set_Expression1,( Set_Expression2 [,ALL ] ) )

使用集表达式1的每一项对集表达式2进行执行。相当于：

for (int i=0;i<100;i++)

{

count<<i<<endl;

}

上面的“i” 相当于 “集表达式1”；“count<<i<<endl”相当于“集表达式2”。

如果“count<<i<<endl”变成“count<<123<<endl”的话就是空循环100次，输出100行“123”，就跟“i”没半毛钱关系。

----------------------------------------------------------------------------------------------------

使用asp.net访问?Analysis Services

http://forums.asp.net/t/1185897.aspx/1

http://www.codeproject.com/Articles/6562/Cubes-MDX-Analysis-Services-and-ADOMD-in-C

使用excel访问?Analysis Services

http://www.cnblogs.com/downmoon/archive/2011/06/02/2067519.html

---------------------------------------------Analysis Services 如何处理数据源的变更

http://database.51cto.com/art/201107/279661.htm

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!