locked
Can't get AggregateFunction=None to work. RRS feed

  • Question

  • Hi,friends, please look this:

     

    I am having problems getting the Aggregate Function None to work on my cube. I'm running AS2005 SP2 developer edition.

    My cube has 8 dimensions.  It has one fact table and 8 dimension tables.  The dimension tables each contain a member_id column and a parent_member_id column.  The fact table has one column per dimension, which are foreign keys of the member_id columns of the dimension tables.

     

    I have set up a data source view with all 8 tables, constraints on each dimension table between member_id and parent_member_id, and one constraint linking each dimension table to the fact table.

    I've set up 8 parent-child dimensions, with two attributes each. All attributes' AttributeHierarchyVisible are set to True. IsAggregatable are set to True. The cube itself uses the 8 dimensions. It has one measure group called FACTS, which contains a single measure called Data Value. I create this measure from my AMO application. Data Value is linked to a Double source column in the fact table.

     

    The problem I have is with the AggregationFunction option. By default it is set to Sum.  As my fact table contains pre-calculated values for every member, I don't want to do this. I want the behaviour that is described in the help text for the None option:"No aggregation is performed, and all values for leaf and nonleaf members in a dimension are supplied directly from the fact table for the measure group that contains the measure. If no value can be read from the fact table for a member, the value for that member is set to null."

     

    However, when I set the AggregateFunction to None, it always return a null result WHENEVER I select MDX or drag-drop on the SSAS management tool. I know from the the help text that when I set AggregateFunction to None, it will not aggregate sum, but it SHOULD return value if I filter the right dimension records. I really filter the right dimension record correctly, for example, there is a data row in the fact table which I set all dimension key columns's value to 1, of cause there is a record 1 in every dimension. And I process the cube successfully.

     

    But, whenever I select MDX or drag-drop on the SSAS management tool, I filter the dimension by the 1 record, the select result is always null.

    I don't know what happens. If I set the AggregateFunction=SUM, I can select the measure value correctly.

    Could anyone help me?

    Thanks!
    Sunday, January 13, 2008 11:22 AM

Answers

  • Nope, I think this problem can't be solved, beacause this is the MS SQL SERVER's attribute. I changed to use the SQL Query Smile

    Thursday, July 10, 2008 2:46 AM

All replies

  • Hi,

     

    To expose the pre-calculated value, you need to set the MembersWithData property for the parent attribute to NonLeafDataVisible in addition to setting AggregateFunction to None.

     

    http://msdn2.microsoft.com/en-us/library/ms174581.aspx

     

    HTH

     

     

     

     

    Sunday, January 13, 2008 7:13 PM
    Answerer
  •  

    So that means I need to set ALL the related father-children dimensions' Memberswithdata property to NonLeafDataVisible, if I want to select the right pre-calculated value?

     

    Thanks.

    Monday, January 14, 2008 2:38 AM
  • And I found another property: IsAggregatable. I need to set IsAggregatable to True if  I need to select the right pre-calculated value. Now I set the parent's IsAggregatable to False, because I don't want to show the automaticlly genarated 'All' node, in my father-children dimension, I have a root node.

     

    I need to set IsAggregatable to True?

    Thanks!

    Monday, January 14, 2008 5:36 AM
  • "So that means I need to set ALL the related father-children dimensions' Memberswithdata property to NonLeafDataVisible, if I want to select the right pre-calculated value?"

     

    Yes.

     

    "I need to set IsAggregatable to True?"

     

    It should not matter since your aggregation function is none.

     

     

    Monday, January 14, 2008 4:57 PM
    Answerer
  • Sorry, I still could not get it to work.

     

    To make things clearly, I start a new project to do a simple test, now it's very clear, perhaps you could reproduce what I do like this:

     

    Prepare:

     1. Prepare a 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 some data into the dimentiontable and facttable.

    insert OLAPAgentDim (OLAPKey, FatherKey,MainDemoName) value (1,2,1)

    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 by default property set.

    5.Produce a dimension based on Dimentiontable B:  OLAPSexDim by default property set.

    6.Produce a cube based on facttable :MiniTest by default property set. Add the two dimensions conect to the cube.

    7 Set the measure FactValue's AggregateFunction = None.

    8.Process all the object.

     

    Now to see the problem.

    Problem:

    1.Open the Explore tab, drag the measure FactValue into the Total Data Field(at center). You will see the FactValue is null.Drag any dimentions into the Column Data Field or Row Data Field or Filter Data Field, the FactValue is always null. Why?

     

    2.To see what happened exactly, I remove the dimension A(the father-children dim) from the cube and process. After process, I drag the measure FactValue into the Total Data Field again, and it's null still.  Why?

     

    3.I drag the dimension B into the Row Data Field, the FactValue =123. It shows the result! Why?

    4.I keep the Row Data Field and Column Data Field emplty, and drag the dimension B into the Filter Field, the FactValue is null again. Why?

     

    5.To see more clearly, I remove the dimension B from the cube, and add the dimension A to it.This time, the FactVaue is always null wherever I drag the dimension.Why?

     

    Could you please reproduce it?

    Thanks!

    Tuesday, January 15, 2008 9:51 AM
  • Hi,

     

    I did some brief test and here are my observations (sorry I didn't have much time so far)

     

    1.  There is no fact associated with the father key (which is 2), this might cause why there is no value for dimension A.

     

    2. The no aggregation function=none caused the dimension B alone case to be null most of the time since the browser tried to return the total value and it is null (since we told it not to aggregate).

     

    I would also suggest that you go through some tutorial about creating parent-child dimension if you have time.

     

     

     

     

    Wednesday, January 16, 2008 4:12 PM
    Answerer
  • Hi,

    Thanks very much for your help. I will go through some tutorial about the parent-child dimension. About the 1 essue you told me, " There is no fact associated with the father key (which is 2), this might cause why there is no value for dimension A.". I can't understand what you really mean. Yes, there is no fact associated with the father key 2. You mean on this condition, I could not select the associated fact value of the child key record?  If like this, that means the parent-child dimension don't support the aggregation function=NULL.

     

    Thanks.

    Thursday, January 17, 2008 1:16 AM
  •  

    Hi,

     

    What I meant is that you won't get the fact value on the father key level if you don't have fact associated with the father key.  I did a bit more test, maybe you could build on that.  This is what I did.

     

    1.  Set aggregation function back to sum (I could not get it work properly in the browser when it is set to NULL)

    2.  Create a calculated member [My fact value] = ([OLAP Agent Dim].[Father Key].DataMember, [Measures].[Fact Value]) 

    (That is the catch, browser will not automatically display the pre existing parent level value unless you override your measure or provide a new measure)

    3.  Process the olap database, browse both [Fact Value] and [my Fact Value] with Father Key hierarchy to see the difference.

     

    4. Now add a record in the fact table with key =2, reprocess the cube, now you should be able to see the pre stored value on father key level in [my Fact Value]

     

    Hope this helps !  Good luck !

    Thursday, January 17, 2008 4:34 AM
    Answerer
  • Thank you very much, but I thought maybe you don't understand me.

     

    I don't want to see the calculated member based on FatherKey.DataMember. My problem is NOT to show the fatherkey's datamember.

     

    In my case, the parent member is 2, and the childkey member is 1. please see my INSERT SQL. member 1 is the child of member 2. I have fact value connected to member 1, NOT TO member 2. I can't get the fact value result crorrectly in the explorer tab: Drag the ChildKey level member 1 to the Row field, and drag the FactValue into the data field, you will see the FactValue is null.

     

    You think my fact value is connected to key =2, BUT, my fact value is connected to key =1(child member). I'm not asking how to show the datamember of parent key. I'm asking how to show the value connected to the child key.

     

    Could you reproduce my case step by step?

    Thanks.

    Thursday, January 17, 2008 5:48 AM
  • Did someone find a solution for his problem, because I have almost the same one, I don't want to aggregate one of my measure.

     

    For information, it is working with the unary operator but for my two measures, actually I want to have one measure aggregatable and the other one no.

     

    Thanks in advance

     

    Wednesday, July 9, 2008 1:03 PM
  • Nope, I think this problem can't be solved, beacause this is the MS SQL SERVER's attribute. I changed to use the SQL Query Smile

    Thursday, July 10, 2008 2:46 AM
  • Hi,


    Anyone got Solution for this. i am facing this proplem for somany measures in different cubes

    Jai
    Thursday, October 29, 2009 2:20 PM