分区的存储模式修改为“ROLAP”报错?
0
资料:SSASSBS
第十六章
我将AdventureWorks.cube分区的存储模式全部修改为“ROLAP”后,处理cube。
错误消息:
OLE DB 错误: OLE DB 或 ODBC 错误 : 关键字 'AS' 附近有语法错误。; 42000; 在绑定到架构的对象中不允许使用语法 '*'。; 42000。
SQL查询:
CREATE
VIEW [Agg_Fact Reseller Sales_6_15_15_15_15_2] ( [OrderQuantity_0],[SalesAmount_1],[TotalProductCost_2],[_3],[OrderQuantity_4],[CalendarQuarterKey_5],[ProductLine_6], [COUNT_BIG_7673aff6-2445-4ef6-a4c9-7bf3d93bd42a] ) WITH SCHEMABINDING AS
(
SELECT SUM ( [dbo_FactResellerSales].[dbo_FactResellerSalesOrderQuantity0_0] )
AS [dbo_FactResellerSalesOrderQuantity0_0], SUM ( [dbo_FactResellerSales].[dbo_FactResellerSalesSalesAmount0_1] )
AS [dbo_FactResellerSalesSalesAmount0_1], SUM ( [dbo_FactResellerSales].[dbo_FactResellerSalesTotalProductCost0_2] )
AS [dbo_FactResellerSalesTotalProductCost0_2],
COUNT_BIG ( [dbo_FactResellerSales].[dbo_FactResellerSales0_3] )
AS [dbo_FactResellerSales0_3], MAX ( [dbo_FactResellerSales].[dbo_FactResellerSalesOrderQuantity0_4] )
AS [dbo_FactResellerSalesOrderQuantity0_4],[dbo_DimDate_5].[dbo_DimDateCalendarQuarterKey1_0] AS [dbo_DimDateCalendarQuarterKey1_0],[dbo_DimReseller_8].[ProductLine] AS [dbo_DimResellerProductLine2_0], COUNT_BIG(*) AS [COUNT_BIG_7673aff6-2445-4ef6-a4c9-7bf3d93bd42a]
FROM
(
SELECT [OrderQuantity] AS [dbo_FactResellerSalesOrderQuantity0_0],[SalesAmount] AS [dbo_FactResellerSalesSalesAmount0_1],[TotalProductCost] AS [dbo_FactResellerSalesTotalProductCost0_2],1 AS [dbo_FactResellerSales0_3],[OrderQuantity] AS [dbo_FactResellerSalesOrderQuantity0_4],[OrderDateKey] AS [dbo_FactResellerSalesOrderDateKey0_5],[ResellerKey] AS [dbo_FactResellerSalesResellerKey0_6]
FROM
(
SELECT * FROM [dbo].[FactResellerSales] WHERE [OrderDateKey] < 20100101
)
AS [FactResellerSales]
)
AS [dbo_FactResellerSales],
(
SELECT CalendarYear * 100 + CalendarQuarter AS [dbo_DimDateCalendarQuarterKey1_0],[DateKey] AS [dbo_DimDateDateKey1_1]
FROM [dbo].[DimDate]
)
AS [dbo_DimDate_5],[dbo].[DimReseller] AS [dbo_DimReseller_8]
WHERE
(
(
[dbo_FactResellerSales].[dbo_FactResellerSalesOrderDateKey0_5] = [dbo_DimDate_5].[dbo_DimDateDateKey1_1]
)
AND
(
[dbo_FactResellerSales].[dbo_FactResellerSalesResellerKey0_6] = [dbo_DimReseller_8].[ResellerKey]
)
)
GROUP BY [dbo_DimDate_5].[dbo_DimDateCalendarQuarterKey1_0],[dbo_DimReseller_8].[ProductLine]
)
第十六章
我将AdventureWorks.cube分区的存储模式全部修改为“ROLAP”后,处理cube。
错误消息:
OLE DB 错误: OLE DB 或 ODBC 错误 : 关键字 'AS' 附近有语法错误。; 42000; 在绑定到架构的对象中不允许使用语法 '*'。; 42000。
SQL查询:
CREATE
VIEW [Agg_Fact Reseller Sales_6_15_15_15_15_2] ( [OrderQuantity_0],[SalesAmount_1],[TotalProductCost_2],[_3],[OrderQuantity_4],[CalendarQuarterKey_5],[ProductLine_6], [COUNT_BIG_7673aff6-2445-4ef6-a4c9-7bf3d93bd42a] ) WITH SCHEMABINDING AS
(
SELECT SUM ( [dbo_FactResellerSales].[dbo_FactResellerSalesOrderQuantity0_0] )
AS [dbo_FactResellerSalesOrderQuantity0_0], SUM ( [dbo_FactResellerSales].[dbo_FactResellerSalesSalesAmount0_1] )
AS [dbo_FactResellerSalesSalesAmount0_1], SUM ( [dbo_FactResellerSales].[dbo_FactResellerSalesTotalProductCost0_2] )
AS [dbo_FactResellerSalesTotalProductCost0_2],
COUNT_BIG ( [dbo_FactResellerSales].[dbo_FactResellerSales0_3] )
AS [dbo_FactResellerSales0_3], MAX ( [dbo_FactResellerSales].[dbo_FactResellerSalesOrderQuantity0_4] )
AS [dbo_FactResellerSalesOrderQuantity0_4],[dbo_DimDate_5].[dbo_DimDateCalendarQuarterKey1_0] AS [dbo_DimDateCalendarQuarterKey1_0],[dbo_DimReseller_8].[ProductLine] AS [dbo_DimResellerProductLine2_0], COUNT_BIG(*) AS [COUNT_BIG_7673aff6-2445-4ef6-a4c9-7bf3d93bd42a]
FROM
(
SELECT [OrderQuantity] AS [dbo_FactResellerSalesOrderQuantity0_0],[SalesAmount] AS [dbo_FactResellerSalesSalesAmount0_1],[TotalProductCost] AS [dbo_FactResellerSalesTotalProductCost0_2],1 AS [dbo_FactResellerSales0_3],[OrderQuantity] AS [dbo_FactResellerSalesOrderQuantity0_4],[OrderDateKey] AS [dbo_FactResellerSalesOrderDateKey0_5],[ResellerKey] AS [dbo_FactResellerSalesResellerKey0_6]
FROM
(
SELECT * FROM [dbo].[FactResellerSales] WHERE [OrderDateKey] < 20100101
)
AS [FactResellerSales]
)
AS [dbo_FactResellerSales],
(
SELECT CalendarYear * 100 + CalendarQuarter AS [dbo_DimDateCalendarQuarterKey1_0],[DateKey] AS [dbo_DimDateDateKey1_1]
FROM [dbo].[DimDate]
)
AS [dbo_DimDate_5],[dbo].[DimReseller] AS [dbo_DimReseller_8]
WHERE
(
(
[dbo_FactResellerSales].[dbo_FactResellerSalesOrderDateKey0_5] = [dbo_DimDate_5].[dbo_DimDateDateKey1_1]
)
AND
(
[dbo_FactResellerSales].[dbo_FactResellerSalesResellerKey0_6] = [dbo_DimReseller_8].[ResellerKey]
)
)
GROUP BY [dbo_DimDate_5].[dbo_DimDateCalendarQuarterKey1_0],[dbo_DimReseller_8].[ProductLine]
)
没有找到相关结果
重要提示:提问者不能发表回复,可以通过评论与回答者沟通,沟通后可以通过编辑功能完善问题描述,以便后续其他人能够更容易理解问题.
3 个回复
choc - 终于找到组织了,学习BI中 2015-06-18 回答
赞同来自:
我有如下几点疑问:
(1)我了解到的情况:ROLAP的分区聚合位于“关系型数据的索引视图”,有谁举个例子看看吗?我所看到的案例都是MOLAP的存储模式?
(2)每个多维数据集项目有一个文件夹:
C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\AdventureWorks SSAS.0.db
对于这个db和abf的文件之间有何关系吗?abf恢复后就是这个db文件?
(3)MOLAP的模式下,表数据和聚合数据都会以文件的形式存储一遍,对吗?
(4)上述报错是怎么回事呢?为何存储模式为MOLAP的情况下没有报错,而修改为ROLAP报错了呢?
(5)ROLAP的多维数据集使用SQL查询还是MDX查询,有查询例子吗?
choc - 终于找到组织了,学习BI中 2015-06-23 回答
赞同来自:
choc - 终于找到组织了,学习BI中 2015-06-23 回答
赞同来自:
维度表、Cube、分区都有设置存储模式的地方
三者的存储模式设置间有何约束吗?
我全部设置为ROLAP,部署是ok的,但是如果只把分区设置为ROLAP,其余保持为MOLAP,则会报错。
维度表部署为ROLAP后,维度/Cube关系都保存在哪儿呢?