April 17, 2014 at 11:24 am
Hi All!
I´m new to NAMED SETS.
I need help on performance for this scenario:
- I need to create a debt collection cube.
- My fact table contains around 14 million rows.
- I already have basic measures such as YTD and stuff like that...
THE PROBLEM:
- When browsing cube, I need to calculate at a specific date (inside a hierarchy), how many customers where "delayed in payments". To do this, I created a measure named "Accumulated Debt" which accumulates all credits and debts and compares if the accumulated value is more than 0 (this means that customer have a debt).
The problem is that I need to create a "dynamic named set" fast enough to calculate "Accumulated Debt" only for customer where their debt is more than 0 in a specific date, and then I need to SUM the "Accumulated Debt" for these customers.
So, I actually created a NAMED SET that filters the customers, and tested it. It works fine. The problems is performance.
Then, I created a calculated measures based on my NAMED SET and it works fine. It makes exactly what I need. I just need a different way to achieve this because performance issues.
Here are my MEASURES/SETS:
* "Accumulated Debt Measure" =
Aggregate(PERIODSTODATE([DATE_DIMENSION].[DATETIME].[(All)],[DATE_DIMENSION].[DATETIME].currentmember),[Measures].[AMOUNT])
* "Count Delayed Customers" =
COUNT(Filter([CUSTOMER_DIMENSION].[CUSTOMER_CODE].[CUSTOMER_CODE], [Measures].[Accumulated_Debt] > 0))
Please note that this measure is based on "Customer Dimension" (wich is a very large dimension with around 2 or 3 million different values; plus, it combines with prior measure wich accumulates debt to "detect delayed customers"
* And this is the most problematic measure in terms of performance (but it actually works as expected):
SUM(
CROSSJOIN(
PERIODSTODATE([DATE_DIMENSION].[DATETIME].[(All)],[DATE_DIMENSION].[DATETIME].currentmember)
, Filter([CUSTOMER_DIMENSION].[CUSTOMER_CODE].[CUSTOMER_CODE], [Measures].[ACCUMULATED_DEBT] > 0) )
, [Measures].[AMOUNT]
)
Please note that this measure makes a crossjoin taking Date Range and "crossjoin" it with delayed customers.
I think it is a bit difficult to understand, but I hope you guys can help me...
Thanks!
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply