A question on processing a cube with source table filter

Question

A question on processing a cube with source table filter

Del Piero

SSCrazy

Points: 2265
More actions
July 22, 2005 at 4:50 am

#152483

Hi again,
    My question is illustrated by the CUSTOMERS, PRODUCT and ORDERS tables :
    CUSTOMERS (around 200 records):
    cust_id, cust_name, credited (bit) , active (bit)
    PRODUCTS ( around 100 records): (storing product types)
    prod_id, prod_name
    ORDERS (around 20,000 records)
    ord_id, cust_id, prod_id, ord_date, price, discount
    Now I have a cube with "price" as the measure (forget about the discount now), and cust_id+custname as one dimension (used in the columns), while prod_id+prod_name as another dimension (used in the rows).
     I applied a Source Table Filter on the "cust_id+custname" dimension, to filter out customers who are NOT ACTIVE (i.e. only include customers with active = 1 ). I put this filter in the dimension, in the cube and also in the cube's default partition (actually I am not very sure where exactly I should apply the filter as many places have "source table filter" property ...)
     So now there remains only about 80 customers who are active and included in the cube.
      My problem is, when I updated the CUSTOMERS table, for example, to change 10 customers from "non-active" to "active", and then re-process the cube. I used all process modes (full, incremental, refresh), but the newly added 10 customers are not included in the cube, even with full process. I tried deleting the cube and dimensions and re-creating everything again, then that works, but not with re-processing the cube.
      Can anyone tell me what's wrong with my configuration?
Lots of thanks,
delpiero

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil.Nicholas SSCrazy Points: 2147 More actions · Answer 1

turning off drillthrough for the cube before processing can fix som consistency problems like this. if its a shared dimension this will need processing as well as the cube.

Phil Nicholas

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

Have you/did you optimise the schema? If so, the keys for the dimensions will be supplied by the fact table query, and it's possible that just because the customer is marked as active it may not have traded, so there'll be no records for it.

Steve.

Del Piero SSCrazy Points: 2265 More actions · Answer 3

Thanks Phil, I think what I missed out was to process the shared dimensions before processing the cubes. Now it seems OK.

Thanks,

delpiero