none
Problem with Dimension of type Account

    Question

  • Hi,

    I have a simple fact table where I have a dimension time and a dimension of type acount.

    The fact table looks like this

    FinanceKey	AccountKey	ProfitCenterKey	DateKey	DateAlternateKey	ScenarioKey	Amount
    1	11	5	7308	2010-01-01	1	131
    2	11	3	7308	2010-01-01	1	62115
    3	11	0	7308	2010-01-01	1	27626
    4	11	6	7308	2010-01-01	1	12
    5	11	2	7308	2010-01-01	1	74316
    6	11	7	7308	2010-01-01	1	14
    7	11	5	7339	2010-02-01	1	147

    The account dimension looks like this

    AccountKey	AccountAlternateKey	ParentAccountKey	AccountName_en	AccountName_de	AccountType	Operator	CustomMembers	ValueType	CustomMemberOptions
    1	1	NULL	Statistical Accounts	Statistische Kennzahlen	Statistical	~	NULL	Units	NULL
    11	11	1	Customer Count	Anz. Kunden	Balances	~	NULL	Units	NULL

    If I browse my cube I get no data for the measure amount. When I remove hte parent Account "Statistical Accounts" and ste the parent of "Customer Count" to NULL I will get the correct Customer count.

    Any idea?

    Thursday, September 02, 2010 3:26 PM

Answers

  • Found that in the calculated member script

    // Assignment of constant for Statistical Accounts account member.
    // This will allow parent member to be visible by default.

    ( [Account].[Accounts].&[95], [Measures].[Amount] ) = "NA" ;

    That will do the trick.

    Tuesday, September 14, 2010 6:46 AM

All replies

  • ".. If I browse my cube I get no data for the measure amount. .." - are you browsing Time [All], Statistical Accounts? There are a couple of issues in that case:

    - The unary operator for AccountKey 11 is "~", which means its data doesn't roll up to the parent, Statistical Accounts. But the fact data is all at the AccountKey 11 level.

    - AccountKey 1 is of AccountType Statistical, which means that it doesn't aggregate over time:

    Adding Account Intelligence to a Dimension

    ...

    Server account type

    Aggregation

    Description

    Statistical

    None

    A calculated ratio of something, or a count of something that does not aggregate over time. This type of account does not convert across currencies with conversion rules.


    - Deepak
    Thursday, September 02, 2010 11:05 PM
    Moderator
  • Thanks a lot for the answer. I had a look at the adventure works example and there was the same constellation. I would expect, that I get for AccountKey 1 NA as amount and that I can drill down to the account 11.
    Friday, September 03, 2010 7:31 AM
  • Is there someone who can help to add just a dummy hierarchy to group some accounts?
    Sunday, September 05, 2010 11:00 AM
  • Found that in the calculated member script

    // Assignment of constant for Statistical Accounts account member.
    // This will allow parent member to be visible by default.

    ( [Account].[Accounts].&[95], [Measures].[Amount] ) = "NA" ;

    That will do the trick.

    Tuesday, September 14, 2010 6:46 AM
  • As Andreas recognized, that = "NA" assignment is the way Adventure Works does it. I've blogged about this topic and critiqued the way Adventure Works accomplishes setting subtotals to N/A. I think there's a better way, for a number of reasons:
    http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=24


    http://artisconsulting.com/Blogs/GregGalloway

    Friday, June 08, 2012 3:20 PM
    Moderator