SSAS Tabular model 多多关系一个实例

原创 2012年03月30日 06:49:58

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,数据透视表展示。

相关文章推荐

BISM TABULAR MODEL 分集(EXCEPT)一个实例

use ExcelExercise go --===== Create the test table CREATE TABLE Purchase ( Purchas...

【SSAS】Model-First Cube 设计的最佳实践

【备注】这是最近在一个讲座中的例子,整理出来给大家参考   相关名词解释 SSAS: SQL Server Analysis Service。 这是微软SQL Server BI(商务智能)平台的一个...

5.1、SSAS-入门实例

入门实例     1          下载并附加数据仓库数据库:AdventureWorksDW2008 地址:http://msftdbprodsamples.codeplex.com/r...

.若A,B两个实体是一对多的包含关系。利用存储过程,实现数据访问层层的一个实例。

1.访问层代码如下: using System; using System.Collections.Generic; using System.Text; using System.Data; ...

TP5中Db类与Model类关系

在以 ThinkPHP 作为后台框架时,经常使用 Db 类操作和 Model 类继承,感觉两者密切相关,毕竟都是操作数据库,难道还能写两套代码? 那它们之间是什么关系呢? 花了些时间阅读了源码,...

ModelDriven背后的机制和Action,valueStack,Model,Context之间的关系图

ModelDriven背后的机制就是ValueStack。界面通过:username/age/address这样的名称,就能够被直接赋值给user对象,这证明user对象正是ValueStack中的一...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SSAS Tabular model 多多关系一个实例
举报原因:
原因补充:

(最多只允许输入30个字)