Getting measures from parent table using child table dimensions

I have a cube built in SSAS2008 that is built upon 2 tables - Accounts and Payments.

Both tables have dimensions and measures, and it appears to be working relatively well - I can get payments for accounts broken down into dimensions for either payments or accounts, and account measures broken down into account dimensions.

What I can't do is view measures for accounts where a relationship exis开发者_JAVA百科ts with the child payments table. For example, see the balance of all accounts that have at least 1 payment.

I understand I may need a separate cube for this, but I still can't see how my data source would need to be configured.

I'd ideally not like/have to completely reformat the data into a fact / dimension snowflake schema, as I'm not entirely sure how to do this with the relational data I have, however any suggestions on this would be welcome.

Thanks.

Update: Bounty added due to lack of interest...

My answer takes into account that you don't want to reformat your data into a traditional data warehouse schema. If it gets you further down the road then good for you but I suspect you'll run into more of these problems as you grow your project. It might be worth tinkering with how you might transform the data into a star schema before you need it.

I can suggest a few options. The first that comes to mind is to make a degenerate dimension in the accounts cube that is based on the payments fact table. The following example answers your "all accounts that have a payment" problem but this should work for similar questions. I assumed an account statement date as the last day of each calendar month so you'll want to count payments made in each calendar month.

create table accounts_fact
(   account_id int not null,
    statement_date datetime not null,
    bal int not null,
    constraint acc_pk primary key (account_id, statement_date)
)

create table payments_fact
(   account_id int not null,
    payment_date datetime not null,
    amount money not null
)

insert into accounts_fact values (1, '20100131', 100)
insert into accounts_fact values (1, '20100228', 120)
insert into accounts_fact values (1, '20100331', 0)
insert into accounts_fact values (2, '20100131', 100)
insert into accounts_fact values (2, '20100228', 20)
insert into accounts_fact values (2, '20100331', 50)
insert into accounts_fact values (3, '20100131', 10)
insert into accounts_fact values (3, '20100228', 30)
insert into accounts_fact values (3, '20100331', 50)

insert into payments_fact values (1, '20100112', 50)
insert into payments_fact values (1, '20100118', 60)
insert into payments_fact values (1, '20100215', 70)
insert into payments_fact values (1, '20100318', 80)
insert into payments_fact values (1, '20100331', 90)

insert into payments_fact values (2, '20100112', 50)
insert into payments_fact values (2, '20100215', 60)
insert into payments_fact values (2, '20100320', 70)

insert into payments_fact values (3, '20100101', 50)
insert into payments_fact values (3, '20100118', 60)
insert into payments_fact values (3, '20100318', 70)

create view dim_AccountPayments
as
select  acc.account_id, acc.statement_date,
        sum(case when pay.payment_date IS NULL THEN 0
             else 1
        end) as payment_count
from    accounts_fact acc
  left outer join payments_fact pay on acc.account_id = pay.account_id
                     and pay.payment_date >= dateadd(mm, -1, dateadd(dd, 1, acc.statement_date))
                     and pay.payment_date <= acc.statement_date
group by acc.account_id, acc.statement_date

select * from dim_AccountPayments

This produces the following results:

account_id  statement_date  payment_count
1   2010-01-31 00:00:00.000 2
1   2010-02-28 00:00:00.000 1
1   2010-03-31 00:00:00.000 2
2   2010-01-31 00:00:00.000 1
2   2010-02-28 00:00:00.000 1
2   2010-03-31 00:00:00.000 1
3   2010-01-31 00:00:00.000 2
3   2010-02-28 00:00:00.000 0
3   2010-03-31 00:00:00.000 1

It should now be a doddle to make a payments count dimension in your accounts cube. For extra points, remove the group by and sum in the view to let SSAS do the aggregation; it suited me to show the results table above. Use the view's SQL in your data source view you don't have create view permission in the source database.

Option 2 would be to make payment count from the view above a measure in the accounts cube. You can do this similarly to the above solution except make your accounts fact use a view similar to dim_AccountPayments. This time you must group by all key fields and aggregate the measures in the database... Very ugly. I don't recommend it but it is possible.

If you go with option 1 then it's simple enough to make a named set in the account payments dimension called 'Made a payment this month' which is children of all filtered to remove 0.

I hope I understood your question. I did have to make quite a few assumptions about your data structures but I hope it's useful.

Good luck.