Post reply

can my limiting query be based on dimension data or does it have to be fact data?

  • June 14, 2006 at 3:45 am

    #112517

    i am trying to alter a cube so as to cut down on the amount of data it contains. I feel my knowledge of anlaysis services

    is a bit lacking and need a few things clarified.

    What i am trying to do is to add limiting query to the sourcetablefilter property in the cube editor ( im using sql 2000 and

    analysis services 2000).

    the query im using is this:

    "PSDimensionInvoice"."createdDate" between '2006-04-27 07:50:37.293' and getDate()

    My question is this: does the table and column in the limiting query have to be from the fact table or can it be from

    a dimension table?

    if i want to limit based on a date and the date is not in the fact table will i have to add that column to the fact

    table before i can use it?

     

  • June 19, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • June 28, 2006 at 8:07 am

    #646366

    Hey Fintan,

    I just ran a v.quick test against foodmart and it looks like your filter can be from a dimensional table.  One thing to watch out for here though is if you optimize the cube then add tis filter it may 'de-optimize' it again because the buold can't run solely against the fact table any more.

    You may want to consider/try using a view for the fact table as with judicious use of indexing you may get as good performance in the build and possibly a cleaner/easier to interpret model for others (ie the fact table/view is named My_invoice_fact_July01_thru_Jun02 rather than making them search for the advanced tab filter setting).

    Cheers,

     

    Steve.

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

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