sqlserver SSAS 笔记

最新推荐文章于 2024-07-11 03:29:48 发布

转载最新推荐文章于 2024-07-11 03:29:48 发布 · 364 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/xxvv/archive/2012/12/24/3648582.html

文章标签：

#数据库

本文介绍了SQL Server 2008 Analysis Services的学习过程，包括数据库实例的安装与使用，MDX查询语言的基础用法及高级技巧，并对比了不同聚合函数的实现效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

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

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

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

http://blog.youkuaiyun.com/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,314,977.53
Clothing	Black	$170,716.23
Components	Black	$1,122,092.81*/
--黑色的大类商品(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].[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].&[1],[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, 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
--)
,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)--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

转载于:https://www.cnblogs.com/xxvv/archive/2012/12/24/3648582.html