Help on Performance

• luis.gilbert

  Newbie

  Points: 7

  More actions

  April 17, 2014 at 11:24 am

  #148530

  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!