Post reply

Filtering data ??

  • April 29, 2003 at 7:05 am

    #159806

    Hi all,

    What's the best way to get only specific data in the cube.

    Only Records with a specificic value (3655)

    are allowed. Do I need to do the filtering true the dimension ??

    Thanks,

    Bart

  • April 29, 2003 at 8:35 am

    #455692

    In Analysis Services Manager, click on your cube, choose Edit and set the Source Table Filter property...that should do it!

    hth,

    Michael

    Michael Weiss


    Michael Weiss

  • April 29, 2003 at 11:31 pm

    #455764

    Thanks Michael,

    The only problem now is that I get errors

    when I'm processing the cube.

    Record with key '3155'found in fact table but was not found in the level 'ACOMPANY_ID' of the dimension 'ACOMPANY'.

    Do you know how I can ommit this problem

    Regards,

    Bart

  • April 29, 2003 at 11:51 pm

    #455765

    Hi bart,

    It looks like you've filtered only the dimension source query, you'll need to filter the transaction query also.

    If you're using views as the queries that feed your cube(s), you could just add filters (where clauses) to the dimension and fact/transaction views to limit the data being returned from the data source.

    Steve.

    Steve.

  • April 30, 2003 at 12:26 am

    #455767

    Hi Steve,

    How can I filter the transaction query (in Analysis service ?)because this new to me ?

    Can you give me some more information how to add some filters (where clauses) to the dimension and fact/transaction views to limit the data being returned from the data source ?

    It would be a great help.

    Thanks in advance.

    Regards,m

    Bart

  • April 30, 2003 at 1:01 am

    #455768

    Hi Bart,

    Two answers contained below... I wrote nearly all of the SQL bit, and then thought you may be doing a proof of concept/small test etc, so then wrote the AS bit. Either way should work, we normally go with the views as these can be run outside of AS for testing purposes, but both methods create the same effect - a WHERE clause that limits the rows being used from a given table. Obviously there's a few different ways to do your where clause when going the SQL way.

    Analysis Services Bit:

    Based on your earlier response, it looks like you've gotten the source table filter to work as required for the dimension. Now you just need to do the same thing for the transaction table filter. Within the cube editor, click on the cube icon in the left hand pane (NB all other items come off the cube icon, eg dimensions measures etc). After clicking on the cube, change the properties page below it to show the advanced properties rather than basic. In here, you will find the SOurce Table Filter property again. Set this as you did with your dimension filter. You should now be able to build your cube, fingers crossed!

    SQL Bit:

    Assuming your using SQL Server as the data source, you may be using tables as the datasource(s) for dimensions and the fact table directly. E.g. your Companies dimension may be being fed by a table called tblCompany. When you go to build your cube, Analysis Services creates a query along the lines of 'Select col1, col2, col3 from tblCompany' to build this dimension. What you can do is change the data source for this dimension by creating a view within the database, a view that returns only the data you want in the cube. So your Company view may read something like - SELECT col1, col2, col3 FROM tblCompany WHERE col1 = 'myfavouritevalue' . If this query is used to populate the dimension, any rows in the table tblCompany that do not have 'myfavouritevalue' as the value for col1, will not be used in the dimension.

    So to filter your fact table query, you do basically the same thing. Say your fact table is currently named tblCompanySales, you may create a view called vwCompanySalesRestricted (use a name that preferably means something, or will in 6 months time), and this view could look like - SELECT col1, col2, col3 FROM tblCompanySales WHERE col1 = 'myfavouritevalue' .

    HTH,

    Steve.

    Steve.

  • April 30, 2003 at 1:12 am

    #455771

    Hi Steve,

    This is realy great what you did...

    Thank you, Thank you...

    Your information was a great help in fact I didn't have to keep my fingers crossed...

    I saw that the level I was filtering on (deminsion) was a bit to low, so I changed it

    to the level of the source table. Now I avoid the records which have no meaning to me.

    Regards,

    Bart

    PS On my next Belgian beer I will bring a toast to you...

  • May 1, 2003 at 6:02 pm

    #455958

    I can taste the Chamay Bleu ou Rouge from here... Maybe a delerium tremens?

    steve

    Steve.

Viewing 8 posts - 1 through 7 (of 7 total)

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