AggregateFunction=Null conflict with Parent-Children Dimension

i report strange thing. in parent-children dimension, if set aggregatefunction=null, can not select fact value associate the leaf-member. whenever select, null back.

 

please simple test this:

 

prepare:

 1. prepare fact table:

create table [dbo].[minitest](
 [tableid] [bigint] not null,
 [agentkey] [bigint] not null,
 [factvalue] [real] null,
 [sexkey] [bigint] null,
 constraint [pk_minitest] primary key clustered
(
 [tableid] asc
)with (pad_index  = off, ignore_dup_key = off) on [primary]
) on [primary]

 

2.prepare 2 dimension table:

dimension a:

create table [dbo].[olapagentdim](
 [olapkey] [bigint] not null,
 [fatherkey] [bigint] null,
 [maindemoname] [nvarchar](50) collate chinese_prc_ci_as null,
 [sourcekey] [varchar](50) collate chinese_prc_ci_as null,
 [starttime] [datetime] null,
 [endtime] [datetime] null,
 [edittime] [datetime] null,
 constraint [pk_agentdim] primary key clustered
(
 [olapkey] asc
)with (pad_index  = off, ignore_dup_key = off) on [primary]
) on [primary]

go

 

dimension b:

create table [dbo].[olapsexdim](
 [olapkey] [bigint] not null,
 [sourcekey] [varchar](50) collate chinese_prc_ci_as null,
 [maindemoname] [varchar](50) collate chinese_prc_ci_as null,
 constraint [pk_olapsexdim] primary key clustered
(
 [olapkey] asc
)with (pad_index  = off, ignore_dup_key = off) on [primary]
) on [primary]
go

 

3.insert data dimentiontable , facttable.

insert olapagentdim (olapkey, fatherkey,maindemoname) value (1,2,"drick")

insert olapagentdim (olapkey, fatherkey,maindemoname) value (2,null,2)

 

insert olapsexdim (olapkey, sourcekey,maindemoname) value (1,null,1)

insert olapsexdim (olapkey, sourcekey,maindemoname) value (2,null,2)

 

insert minitest(tableid, agentkey,sexkey,factvalue) value (1,1,1,123)

 

4.produce a father-children dimension based on dimentiontable a:  olapagentdim default property set.

5.produce a dimension based on dimentiontable b:  olapsexdim default property set.

6.produce cube based on facttable :minitest default property set. add 2 dimensions conect cube.

7 set measure factvalue's aggregatefunction = none.

8.process object.

 

now see problem.

problem:

1.to see more clearly, i only add dimension cube.

2.open explorer tab.

3.drag factvaue measure data field, , drag dimension a's child-member row field(it's "drick").now can see factvalue in data field=null. infact, null drag dimension.why?

 

could please reproduce it?

thanks!

anyone help?

SQL Server  >  SQL Server Analysis Services