sql server 2005 analysis service step by step(三):创建父子维度 1.什么是父子维度。the parent-child relationship in the table that the wizard detected by the self-referencing join on the table.例如在Dim.Employee表中有个外键是ParentEmployeeKey,她关联的主键就是.Employee表的EmployKey建。2. 创建父子维度1. Open SSAS Step by Step DW.dsv. 2. Right-click the background of the Data Source View Diagram pane, and then click Add/ Remove Tables. 3. In the Add/Remove Tables dialog box, double-click dbo.DimEmployee in the Available Objects list to move this table to the Included Objects list, and then click OK. Notice the self-referencing relationship for the DimEmployee table in the DSV. This relationship is indicated by the arrow leading from the DimEmployee table back to itself. 4. In Solution Explorer, right-click the Dimensions folder, click New Dimension, click Next, clear the Auto Build check box, click Next, click the SSAS Step by Step DW DSV, and then click Next twice. 5. Click dbo.DimEmployee in the Main Table drop-down list, click EmployeeKey in the Key Columns list, click LastName in the Column Containing The Member Name dropdown list, and then click Next. 6. On the Select Dimension Attributes page of the wizard, select the check box next to ParentEmployeeKey. You must include the attribute that defines the foreign key column of a record in a parentchild dimension. 7. Click Next twice.Click Next, change the name of the dimension from Dim Employee to Employee, and then click Finish. The Employee parent-child dimension is now added to your project. 9. In the Attributes pane of the Dimension Structure tab, right-click Dim Employee, click Rename, and then type Employee. 10. Repeat the previous step to rename Parent Employee Key as Employees. 11. Keeping Employees selected in the Attributes pane, open the Properties window and locate the Usage property.3.父子维度的数据聚合。4.管理父子维度的级别
--测试数据 CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10)) INSERT tb SELECT '001',NULL ,'山东省' UNION ALL SELECT '002','001','烟台市' UNION ALL SELECT '004','002','招远市' UNION ALL SELECT '003','001','青岛市' UNION ALL SELECT '005',NULL ,'四会市' UNION ALL SELECT '006','005','清远市' UNION ALL SELECT '007','006','小分市' GO--查询指定节点及其所有子节点的函数 CREATE FUNCTION f_Cid(@ID char(3)) RETURNS @t_Level TABLE(ID char(3),Level int) AS BEGIN DECLARE @Level int SET @Level=1 INSERT @t_Level SELECT @ID,@Level WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 INSERT @t_Level SELECT a.ID,@Level FROM tb a,@t_Level b WHERE a.PID=b.ID AND b.Level=@Level-1 END RETURN END -- 2005 及之后的版本 create table treeList( nodeID int IDENTITY, --节点ID nodeName varchar(20), --节点名称 partId int --父节点 ) insert into treeList(nodeName,partID) select '节点1' ,0 --一级节点 union all select '节点2',1 --二级节点 union all select '节点3',1 --二级节点 union all select '节点4',2 --三级节点 union all select '节点5',2 --三级节点 union all select '节点6',3 --三级节点 union all select '节点7',4 --四级节点 union all select '节点8',4 --四级节点 union all select '节点9',5 --四级节点 GODECLARE @nodeID int; SET @nodeID = 1; -- 要查询的节点 WITH TREE AS( SELECT *, level = 0, path = CONVERT(varchar(8000), RIGHT(10000 + nodeID, 4)) FROM treeList WHERE nodeID = @nodeID UNION ALL SELECT A.*, level = B.level + 1, path = B.path + CONVERT(varchar(8000), RIGHT(10000 + A.nodeID, 4)) FROM treeList A, TREE B WHERE A.partId = B.nodeID ) SELECT SPACE(3 * level) + N'|-- ' + nodeName, * FROM TREE ORDER BY path; GODROP TABLE treeList;
sql server 2005 analysis service step by step(三):创建父子维度
1.什么是父子维度。the parent-child relationship in the table that the wizard detected by the self-referencing join
on the table.例如在Dim.Employee表中有个外键是ParentEmployeeKey,她关联的主键就是.Employee表的EmployKey建。2. 创建父子维度1. Open SSAS Step by Step DW.dsv.
2. Right-click the background of the Data Source View Diagram pane, and then click Add/
Remove Tables.
3. In the Add/Remove Tables dialog box, double-click dbo.DimEmployee in the Available
Objects list to move this table to the Included Objects list, and then click OK.
Notice the self-referencing relationship for the DimEmployee table in the DSV. This relationship
is indicated by the arrow leading from the DimEmployee table back to itself.
4. In Solution Explorer, right-click the Dimensions folder, click New Dimension, click
Next, clear the Auto Build check box, click Next, click the SSAS Step by Step DW DSV,
and then click Next twice.
5. Click dbo.DimEmployee in the Main Table drop-down list, click EmployeeKey in the
Key Columns list, click LastName in the Column Containing The Member Name dropdown
list, and then click Next.
6. On the Select Dimension Attributes page of the wizard, select the check box next to
ParentEmployeeKey.
You must include the attribute that defines the foreign key column of a record in a parentchild
dimension.
7. Click Next twice.Click Next, change the name of the dimension from Dim Employee to Employee, and
then click Finish.
The Employee parent-child dimension is now added to your project.
9. In the Attributes pane of the Dimension Structure tab, right-click Dim Employee, click
Rename, and then type Employee.
10. Repeat the previous step to rename Parent Employee Key as Employees.
11. Keeping Employees selected in the Attributes pane, open the Properties window and
locate the Usage property.3.父子维度的数据聚合。4.管理父子维度的级别
以一个商品字典表(Dic_Commodity)为例:
Dic_Commodity(第一种情况):
表结构:
CommodityID nvarchar(20)
CommodityName nvarchar(60)
price numeric(18,2)
Other nvarchar(120)
表数据:
01 电器
0101 电视
010101 彩色电视
02 服装
0201 男装
020101 男夏装
020102 男冬装
0202 女装
Dic_Commodity(第二种情况):
表结构:
CommodityID nvarchar(20)
CommodityName nvarchar(60)
TypeID nvarchar(20)
price numeric(18,2)
Other nvarchar(120)
表数据:
01 电器 01
0101 电视 01
010101 彩色电视 0101
02 服装 02
0201 男装 02
020101 男夏装 0201
020102 男冬装 0201
0202 女装 02
首先你的Dic_Commodity中的记录应该存在父子关系,就象第二种情况那样,如果是第一钟
情况呢,那样就自己生成一个视图,搞成类似情况二的那样,即对于一行记录,存在在其父
级编码的列。
然后是在你的ANALYSIS SERVICES中的事情了:启动维度向导,选择父子维度,然后选择你
的表商品信息字典表(或视图),下一步,成员键是CommodityID,父键是TypeID,成员名
称是CommodityName,下一步,下一步,起名子,ok。
当然,第二种情况的CommodityID本身没有级次特征也可以,如:
1000 电器 0050
1001 电视 1000
1002 彩色电视 1001
1100 服装 0050
1101 男装 1100
1111 男夏装 1101
1112 男冬装 1101
1105 女装 1100
这里有具体教怎么操作的
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
-- 2005 及之后的版本
create table treeList(
nodeID int IDENTITY, --节点ID
nodeName varchar(20), --节点名称
partId int --父节点
)
insert into treeList(nodeName,partID)
select '节点1' ,0 --一级节点
union all select '节点2',1 --二级节点
union all select '节点3',1 --二级节点
union all select '节点4',2 --三级节点
union all select '节点5',2 --三级节点
union all select '节点6',3 --三级节点
union all select '节点7',4 --四级节点
union all select '节点8',4 --四级节点
union all select '节点9',5 --四级节点
GODECLARE
@nodeID int;
SET @nodeID = 1; -- 要查询的节点
WITH
TREE AS(
SELECT
*,
level = 0,
path = CONVERT(varchar(8000), RIGHT(10000 + nodeID, 4))
FROM treeList
WHERE nodeID = @nodeID
UNION ALL
SELECT
A.*,
level = B.level + 1,
path = B.path
+ CONVERT(varchar(8000), RIGHT(10000 + A.nodeID, 4))
FROM treeList A,
TREE B
WHERE A.partId = B.nodeID
)
SELECT
SPACE(3 * level) + N'|-- ' + nodeName,
*
FROM TREE
ORDER BY path;
GODROP TABLE treeList;