SSAS Tabular model 解决多多关系非常容易,下面看个例子。
USE [ExcelExecise]
GO
/****** Object: Table [dbo].[产品表] Script Date: 08/19/2011 02:21:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[产品表](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[产品编码] [varchar](50) NULL,
CONSTRAINT [PK_产品表] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_产品表] UNIQUE NONCLUSTERED
(
[产品编码] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[产品表] ON
INSERT [dbo].[产品表] ([id], [产品编码]) VALUES (1, N'40001')
INSERT [dbo].[产品表] ([id], [产品编码]) VALUES (2, N'40002')
INSERT [dbo].[产品表] ([id], [产品编码]) VALUES (3, N'40003')
INSERT [dbo].[产品表] ([id], [产品编码]) VALUES (4, N'40004')
SET IDENTITY_INSERT [dbo].[产品表] OFF
/****** Object: Table [dbo].[订单主表] Script Date: 08/19/2011 02:21:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[订单主表](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[订单号] [varchar](50) NULL,
CONSTRAINT [PK_订单主表] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[订单主表] ON
INSERT [dbo].[订单主表] ([id], [订单号]) VALUES (1, N'A1')
INSERT [dbo].[订单主表] ([id], [订单号]) VALUES (2, N'A2')
INSERT [dbo].[订单主表] ([id], [订单号]) VALUES (3, N'A3')
INSERT [dbo].[订单主表] ([id], [订单号]) VALUES (4, N'A4')
SET IDENTITY_INSERT [dbo].[订单主表] OFF
/****** Object: Table [dbo].[订单明细表] Script Date: 08/19/2011 02:21:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[订单明细表](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[订单表ID] [bigint] NULL,
[产品编码] [varchar](50) NULL,
[添加时间] [datetime] NULL,
CONSTRAINT [PK_订单明细表] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[订单明细表] ON
INSERT [dbo].[订单明细表] ([id], [订单表ID], [产品编码], [添加时间]) VALUES (1, 1, N'40001', CAST(0x00009F3300000000 AS DateTime))
INSERT [dbo].[订单明细表] ([id], [订单表ID], [产品编码], [添加时间]) VALUES (2, 1, N'40002', CAST(0x00009F3B00000000 AS DateTime))
INSERT [dbo].[订单明细表] ([id], [订单表ID], [产品编码], [添加时间]) VALUES (3, 1, N'40003', CAST(0x00009F4000000000 AS DateTime))
INSERT [dbo].[订单明细表] ([id], [订单表ID], [产品编码], [添加时间]) VALUES (4, 1, N'40004', CAST(0x00009F4100000000 AS DateTime))
INSERT [dbo].[订单明细表] ([id], [订单表ID], [产品编码], [添加时间]) VALUES (5, 2, N'40001', CAST(0x00009F4100000000 AS DateTime))
INSERT [dbo].[订单明细表] ([id], [订单表ID], [产品编码], [添加时间]) VALUES (6, 2, N'40003', CAST(0x00009F4000000000 AS DateTime))
INSERT [dbo].[订单明细表] ([id], [订单表ID], [产品编码], [添加时间]) VALUES (7, 3, N'40004', CAST(0x00009F4300000000 AS DateTime))
SET IDENTITY_INSERT [dbo].[订单明细表] OFF
/****** Object: Table [dbo].[产品规格表] Script Date: 08/19/2011 02:21:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[产品规格表](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[产品编码] [varchar](50) NULL,
[长] [decimal](18, 4) NULL,
[宽] [decimal](18, 4) NULL,
[高] [decimal](18, 4) NULL,
[启用日期] [datetime] NULL,
CONSTRAINT [PK_产品规格表] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[产品规格表] ON
INSERT [dbo].[产品规格表] ([id], [产品编码], [长], [宽], [高], [启用日期]) VALUES (1, N'40001', CAST(15.0000 AS Decimal(18, 4)), CAST(12.0000 AS Decimal(18, 4)), CAST(16.0000 AS Decimal(18, 4)), CAST(0x00009F3200000000 AS DateTime))
INSERT [dbo].[产品规格表] ([id], [产品编码], [长], [宽], [高], [启用日期]) VALUES (2, N'40001', CAST(20.0000 AS Decimal(18, 4)), CAST(13.0000 AS Decimal(18, 4)), CAST(15.0000 AS Decimal(18, 4)), CAST(0x00009F4000000000 AS DateTime))
INSERT [dbo].[产品规格表] ([id], [产品编码], [长], [宽], [高], [启用日期]) VALUES (3, N'40002', CAST(16.0000 AS Decimal(18, 4)), CAST(17.0000 AS Decimal(18, 4)), CAST(18.0000 AS Decimal(18, 4)), CAST(0x00009F3B00000000 AS DateTime))
INSERT [dbo].[产品规格表] ([id], [产品编码], [长], [宽], [高], [启用日期]) VALUES (4, N'40002', CAST(20.0000 AS Decimal(18, 4)), CAST(21.0000 AS Decimal(18, 4)), CAST(14.0000 AS Decimal(18, 4)), CAST(0x00009F3E00000000 AS DateTime))
INSERT [dbo].[产品规格表] ([id], [产品编码], [长], [宽], [高], [启用日期]) VALUES (5, N'40003', CAST(12.0000 AS Decimal(18, 4)), CAST(11.0000 AS Decimal(18, 4)), CAST(19.0000 AS Decimal(18, 4)), CAST(0x00009E5E00000000 AS DateTime))
INSERT [dbo].[产品规格表] ([id], [产品编码], [长], [宽], [高], [启用日期]) VALUES (8, N'40004', CAST(2.0000 AS Decimal(18, 4)), CAST(2.0000 AS Decimal(18, 4)), CAST(2.0000 AS Decimal(18, 4)), CAST(0x0000A01200000000 AS DateTime))
SET IDENTITY_INSERT [dbo].[产品规格表] OFF
/****** Object: ForeignKey [FK_产品规格表_产品表] Script Date: 08/19/2011 02:21:03 ******/
ALTER TABLE [dbo].[产品规格表] WITH CHECK ADD CONSTRAINT [FK_产品规格表_产品表] FOREIGN KEY([产品编码])
REFERENCES [dbo].[产品表] ([产品编码])
GO
ALTER TABLE [dbo].[产品规格表] CHECK CONSTRAINT [FK_产品规格表_产品表]
GO
/****** Object: ForeignKey [FK_订单明细表_产品表] Script Date: 08/19/2011 02:21:03 ******/
ALTER TABLE [dbo].[订单明细表] WITH CHECK ADD CONSTRAINT [FK_订单明细表_产品表] FOREIGN KEY([产品编码])
REFERENCES [dbo].[产品表] ([产品编码])
GO
ALTER TABLE [dbo].[订单明细表] CHECK CONSTRAINT [FK_订单明细表_产品表]
GO
/****** Object: ForeignKey [FK_订单明细表_订单主表] Script Date: 08/19/2011 02:21:03 ******/
ALTER TABLE [dbo].[订单明细表] WITH CHECK ADD CONSTRAINT [FK_订单明细表_订单主表] FOREIGN KEY([订单表ID])
REFERENCES [dbo].[订单主表] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[订单明细表] CHECK CONSTRAINT [FK_订单明细表_订单主表]
GO
有四张表,分别是:订单主表、订单明细表、产品表、产品规格表。
说明:产品规格表记录的是产品在某个时间点启用某种规格的信息。
如:40001,有两套规格(单位厘米)。可以看出2011年8月1日到2011年8月15日之间用的是一套规格。2011年8月15日启用的规格则是最新的,之后的订单都按这个规格计算体积。
如果规格表里没有某个产品的规格,则长宽高都按0计算。
其他表就不用多说了,中文字段名,很容易看懂的。
想要的结果是:
每张订单的体积合计,格式如下:
订单号 体积
A1 10284
A2 6408
A3 0
A4 0
…… ……
需要注意的是:
1:订单A4没有产品明细,但是也要在结果里体现。
2:产品40004的规格起效日期已经不在订单明细时间的范围内,那么需要取出的规格都是0。
3:计算体积时,按照订单明细添加日期,看属于哪套规格的,就按哪套规格计算体积。
对于这个问题:
1,导入数据到SSAS,建立模型。
2,系统自动识别关系,但没有什么用处。
3,在产品明细计算体积。
4,在订单明细计算对应的体积。关键一点这里,不必考虑任何关系,只要正常逻辑过滤就行。
5,数据透视表展示。
分享到:
相关推荐
BI SSAS 通过配置表动态权限控制实例项目 来源:http://www.cxfeel.cn/blog/article/95.htm
DAT225x-SSAS_Tabular edX课程DAT225x的课程文件开发SQL Server Analysis Services表格模型
ssis,ssrs,ssas-6年使用笔记近期整理成册,适合新-初级BI技术和数据分析人员。全实战经验记录从技术选型到如何搭建企业数据平台bi。内容见目录: 开发方案选择 九大数据仓库方案特点 数据仓库方案选择 开发环境...
SSAS 最简单的入门教程,实例代码, 实例数据库AdventureWorksDW2008R2需要从微软官方网站下载。
SSAS 开发与部署Cube一个经典的开发教程
NULL 博文链接:https://sito.iteye.com/blog/1162946
SSAS(Sql Server Analysis Service)在许多不需要实时而具有海量或需要足够灵活的分析模型中,SSAS比传统的sql有很大的优势,比如性能和...),开发一个适合各类用户自由分析统计数据的展示工具比用sql语句成本要小的多。
VS2005 SSAS多维分析VS2005 SSAS多维分析
ssas 功具書 ssas2008 cube design reference
SSAS2008R2;微软SSAS案例(标准版);微软商业智能解决方案;
附件是介绍如何在Excel2007中使用SSAS这个OLAP。
SSAS MDX参考
SSAS的扩展性、性能与优化 SSAS的扩展性、性能与优化
SSAS-Analysis Services.rar
[Packt Publishing] MDX & SSAS 2012 开发技巧 (英文版) [Packt Publishing] MDX with SSAS 2012 Cookbook (E-Book) ☆ 图书概要:☆ 69 practical recipes to analyze multidimensional data stored in SSAS ...
该项目是用微软的SSAS做出来的,适合初学者了解微软的SSAS,以及做CUBE的流程。
Analysis Services 2008提供了一个高性能、低成本、易实现的联机事务处理(OLAP)引擎,内置更多的企业分析方法、高级的数据挖掘、提供一致访问接口以开放关键业务评价标准的业务特性。本课程通过快速的实际案例操作,...
SSAS实现商业分析实战,详细介绍了如何运用SSAS来进行数据仓库的建立,构建数据维度,进行数据挖掘
SSAS2008分析服务性能优化指南_CHS
SSAS 多维模型的最佳实践 维度设计 CUBE设计 聚合设计 分区设计