Filtering data ??

Question

Filtering data ??

BEIJCKM

Mr or Mrs. 500

Points: 583
More actions
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

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

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

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 1

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

BEIJCKM Mr or Mrs. 500 Points: 583 More actions · Answer 2

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

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

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.

BEIJCKM Mr or Mrs. 500 Points: 583 More actions · Answer 4

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

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

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.

BEIJCKM Mr or Mrs. 500 Points: 583 More actions · Answer 6

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...

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

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

steve

Steve.