INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Semi-additive issue...

Semi-additive issue...

(OP)
Semi additive problem...

I have an issue regarding semi additive behaviour which, as a relative newbie, has me stumped.

Take this scenario...

I have a very simple design which consists of a fact table with a single measure (balance), a time dimension and an account dimension.    Defined as follows:-



DimAccount (SCD)
AccountKey
AccountType (defines the semi additive behaviour)
Status  (can hold the value "Active" or "Inactive")
StartDate

FactBalances    
DateKey
AccountKey
CurrentBalance


The data held is as follows:-

CODE

DimAccount (5 accounts)
AccountKey        AccountType        AccountNumber        Status        StartDate
1        Balance        AC01        Active        2009/01/01
2        Balance        AC02        Active        2009/01/01
3        Balance        AC03        Active        2009/01/01
4        Balance        AC04        Active        2009/01/01
5        Balance        AC05        Active        2009/01/01


FactBalance (3 months of data)    
DateKey        AccountKey        CurrentBalance
20090101        1        1000
20090101        2        1000
20090101        3        1000
20090101        4        1000
20090101        5        1000
20090201        1        1000
20090201        2        1000
20090201        3        1000
20090201        4        1000
20090201        5        1000
20090301        1        1000
20090301        2        1000
20090301        3        1000
20090301        4        1000
20090301        5        1000

My design is configured to use semi additive behaviour for my current balance (LastNonEmpty).  So if I drag the "CurrentBalance" into my view window I am correctly told 5000.

Now my account dimension is slowly changing and here is where the problem starts.  Amending the data above to show a change in account status to AC01 during the 3 month period gives me the following data:-

CODE

DimAccount (5 accounts but with one historic change row)
AccountKey        AccountType        AccountNumber        Status        StartDate
1        Balance        AC01        Active        2009/01/01
2        Balance        AC02        Active        2009/01/01
3        Balance        AC03        Active        2009/01/01
4        Balance        AC04        Active        2009/01/01
5        Balance        AC05        Active        2009/01/01
6        Balance        AC01        Inactive        2009/02/01

FactBalance (3 months of data)    
DateKey    AccountKey        CurrentBalance
20090101        1        1000
20090101        2        1000
20090101        3        1000
20090101        4        1000
20090101        5        1000
20090201        6        1000
20090201        2        1000
20090201        3        1000
20090201        4        1000
20090201        5        1000
20090301        6        1000
20090301        2        1000
20090301        3        1000
20090301        4        1000
20090301        5        1000
If I now drag in the current balance I still  get 5000 (great) but if I drop the attribute "Status" as a row field I get:-

CODE

Active        5000
Inactive        1000
Grand total        5000

Not the behaviour I was seeking.  I am after the following result:-

CODE

Active        4000
Inactive        1000
Grand total        5000

Can anyone help me to achieve this?
 

RE: Semi-additive issue...

Can you post an image or more complete view of your resultset?  I'm not sure if you've got months and status on rows, or just status, or something else.

RE: Semi-additive issue...

(OP)
Hi Riverguy, thanks for the response.

sorry about the formating.  didn't come out to clear.  to explain...

the DimAccount table has five columns:
AccountKey
AccountType
AccountNumber
Status
StartDate


The [Status] field holds either "Active" or "Inactive"
[Startdate] holds the date the row was entered.

Hope that helps.

RE: Semi-additive issue...

I'm trying to visualize your query output.  Does your resultset look like this:

CODE

-------------------------------
20090201 |Active       |  5000 |
         ----------------------
         |Inactive     |  1000 |
         ----------------------
          Grand Total  |  5000 |
-------------------------------
20090301 |Active       |  5000 |
         ----------------------
         |Inactive     |  1000 |
         ----------------------
          Grand Total  |  5000 |
-------------------------------

or this

CODE

Active       |  5000 |
----------------------
Inactive     |  1000 |
----------------------
Grand Total  |  5000 |
----------------------

or something else?
 

RE: Semi-additive issue...

(OP)
the second one.  


i'm browsing the cube within SSAS and dragging [CurrentBalance] as the measure and DimAccount.Status as my row field.

as the measure is semi additive (byaccount) i was hoping that it would have returned just the most recent entry for the accounts but it has sum'd all entries including both row for the account (AC01).

anyway around this?  

RE: Semi-additive issue...

I couldn't reproduce your results.  I built a sample based on your schema above and ended up with 4000/1000/5000.  Using LastNonEmpty for the aggregate function.

RE: Semi-additive issue...

(OP)
Did you use the measure to use the "ByAccount" aggregate function?

To use this you need to configure the account table to be of type Accounts, the AccountReference field as type "Account" and the "AccountType" field as type AccountType.  You then also need to relate AccountType to AccountReference.

 

RE: Semi-additive issue...

(OP)
My end goal for this design is to have a cube that, for any question asked of it, will identify data based on the last/current account (DimAccount) – no double counting on the same account please.  I thought semi additive functions would be the route to achieve this but, as I hope I have described above, I don't think it quite achieves this.

If I ask for the balance by status for the month 20090101 I expect to have 5 accounts returned with their statuses.

If I ask for the balance by status regardless of date I expect to have 5 accounts returned with their statuses but "ByAccount" aggregation is picking up the historic change and returns 6 accounts.  It seems to return unique data within the context of the question asked and not distinct by the account reference.  

I wonder what the correct design route for me to take is.  I know very little about the power of MDX but maybe this is the answer.  Or should I lock down the design so I has restrictive query capability?  
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close