February 5, 2016 at 10:18 am
This seems like a pretty basic question, which probably will tell you about my level of expertise with SSAS, but I haven't been able to find a good answer otherwise, perhaps because I don't know how to phrase the question more simply!
I'm trying to build a cube that will allow users to analyse sales that have not been paid in full.
I have 2 fact tables (and dimensions date, customer and supplier):
fact-Sales
----------
CustomerID (fk)
SupplierID (fk)
SaleDate (fk)
SaleAmount
fact-Payments
-------------
CustomerID (fk)
SupplierID (fk)
PaymentDate (fk)
PaymentAmount
In my (tabular) cube, I'm needing to have a "balance" measure that is something to the effect of:
Balance := fact-Sales[SalesAmount] - SUM(RELATED(fact-Payments[PaymentAmount]))
Which doesn't seem to work, I suspect since the relationship is through the dimensions, not directly from fact table to fact table.
I also tried to create a calculated column [Total Payments]:= SUM(RELATED(fact-Payments[PaymentAmount])), which also fails.
So am I modeling this correctly? Should I explicitly define relationships between the fact tables, event though they have different levels of granularity? Perhaps I could simply increase the granularity of the sales table by adding the payment info so I have a single fact table, or should I use a bridge table? Should I just create an SQL view on sales with the aggregated column from the payments table?
I'm obviously just getting started with this, and I'm wanting to "do it right", so anything can be changed at this point.
February 11, 2016 at 3:59 am
I mostly work with multidimensional but since no one else has answered. From an OLAP standpoint your design is absolutely correct, do not think about changing it or mixing the grains on your fact tables, nothing good will come of it.
Calculating measures across dimensions in this fashion is exactly what analysis services is supposed to do so those relationships are correct.
In these situations you need to use the CALCULATE function which will allow you to bring in and aggregate a measure from another fact table. It would look like the below if you were adding it to your sales measure group.
Balance := SUM(fact-Sales[SalesAmount]) - CALCULATE(SUM(fact-Payments[PaymentAmount]))
March 24, 2016 at 12:56 pm
Yes, that was the answer I was looking for. I ended up being able to tweak my formulas to come up with the calculated columns I needed:
TotalPayments:=CALCULATE(SUM(Payment[PaymentAmount]),FILTER(Payment,Payment[CustomerID] = Sales[CustomerID])))
BalanceDue:=Sales[SaleAmount] - Sales[TotalPayments]
Thanks for the help!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply