Separate and independent counts from the same accumulating fact table

Question

So, I have a Sales Fact table, that is based on the transaction lifecycle model (or accumulating fact snapshot table), with very many different date key columns, (like sales date, refunded date, etc). I made different measures for each of the date columns, i.e. Sum of Non-Empty sales date key column is the [Number of Sales], and Sum of Non-Empty refunded date key column is the [Number of Refunds], etc. Each of the date key columns are related to different date key dimensions. The sales date dimension and the refunded date dimension are role playing dimensions, all based on the same DimDate table. There are other non-date based dimensions as well, but for the sake of this example, I will keep it simple - there is another additional dimension for storeType (retail, ecommerce, etc)

When I browse the cube (as most users will browse and explore the cube via excel) I can drag the [Number of Sales] and [Number of Refunds] to the columns section, and then I can drag the StoreType dimension in the rows section, and it displays the data properly as such:


                 Number of Sales   Number of Refunds
----------------------------------------------------
Retail           10                8
eCommerce        5                 2

This is fine, because I haven't applied any date filters so it displays everything. I've confirned the data in the Fact table, and indeed, the numbers are right.

But then I want to apply the same date filter to both the [Number of Sales] and [Number of Refunds] - so I drag both those dimensions to the filter area, and apply the same date filter to both - and of course, the numbers are all the same for both the columns:


                 Number of Sales   Number of Refunds
----------------------------------------------------
Retail           5                 4
eCommerce        5                 4

.. because I think by effectively applying one date towards both the date dimensions, I am filtering down to the same set of rows (being that they are from the same table). I know this is incorrect, because I can query the individual rows from the Fact table and see that they have different values.

Basically, what I want is to show the two columns together, but really they don't have anything in common. I could possibly even have two different date filters on each column, i.e. show all sales for fiscal year 2010 and show all refunds for fiscal year 2011. This is again, completely browsable by the user, so the measures must be made available without having to do complicated MDX queries.

I guess I can create another fact table to house the same data in a transactional fact table, and I can then count it individually - but that makes no sense to have two separate fact tables to more or less count the same thing.

Any idea how to do this? help!!

Rob Paller Rob Paller · Accepted Answer · 2011-09-25T16:53:18

It sounds like you may need to apply a case statement to your aggregate columns. Something along the lines of this:

SELECT StoreType
     , SUM(CASE WHEN SalesCol IS NOT NULL THEN 1 ELSE NULL END) AS NumSales
     , SUM(CASE WHEN RefundCol IS NOT NULL THEN 1 ELSE NULL END) AS NumRefunds
FROM FactTable
WHERE SaleDate BETWEEN Date1 AND Date2
GROUP BY 1

Separate and independent counts from the same accumulating fact table

1 Answers