Hello,

I have one table that has the following columns:
AccountNum, AccountName, Date, Amount

I require a cube to have two measures: Debit and Credit...

Credit = Amount when <0
Debit = Amount when >0

So the cube will look something like this:

Dimension: [Date.Year].June

AccountNum AccountName Debit Credit
100 Revenue -500 400

Can anyone assist as I am stumped.

Thanks

Clint

Create 3 dimensions: AccountNum, AccountName, and Date joining to those
3 columns. Then define 2 measures (having Sum aggregation function) with
these Source Columns:

Credit>> case when Fact.Amount > 0
then Fact.Amount else 0 end

Debit>> case when Fact.Amount < 0
then Fact.Amount else 0 end


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks for your assistance, I have followed your instructions however, both
positive and negative values are being added to both Credit and Debit. Any
further ideas?
"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

You have to create a fact table that has 2 columns!

create a fact table with 2 select statements like:

select AccountNum, AccountName, Date, Amount as Debit, null as Credit
from table
where amount > 0

union all

select AccountNum, AccountName, Date, null as Debit, Amount as Credit
from table
where amount < 0


and this is your fact table, and you have 2 basic measures!

Simple as that!

hope it helps!

Peace,
Andrej



"AshVsAOD" <.> wrote

From what you are describing, you may not have defined the "Source
Column" SQL expressions as explained below.

For example, if "Source Column" expression for Credit is:

And if the "Source Column" expression for Debit is:

Of course, I'm assuming that you are using a relational data source
(such as SQL Server) which implements case.


You can follow the other suggestion, and create a new fact table/view
with 2 columns for Debit/Credit, if you prefer.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks very much for all your assistance. My knowledge of OLAP and
everything surrounding the tech is growing but still very light on. I
recreated the cube a second time using your described method with success.
I must have mispelt something or other. I really do appreciate your help.

Kind Regards

Clint
"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

That would work too... D'oh... sometimes the simplist methods are the
best... Thanks... I am already using UNION ALL elsewhere don't know why I
didn't think of unioning the same table.

Thanks!
"Andrej Hudoklin" <andrej.hudoklin (AT) add (DOT) si> wrote

I have discovered what I did wrong <whew>..

It appears that although the amounts in both debit and credit work fine,
using Drill to detail shows all records irrespective of whether or not it is
a credit or debit.

I will need to investigate this when I can source some time.
"AshVsAOD" <.> wrote