Better way to filter dimension members?

I have a dimension hierarchy that has several thousand members (it's a geographical one). We often times want to answer questions for a particular city or zip code in the hierarchy - has anyone found a better filtration mechanism in either BI studio's OLAP browser or Excel 2007? Basically, I'd love to just type "Seattle" or "Seattle, WA" and have it choose the Seattle geographical member (instead of navigating the full hierarchy to select it).

Better design practice in case of having hierarchy containing many members is pretty simple, but is not often simple to implement. Unfortunately there arent that many alternatives.

Anyhow. You need to try and create additional levels in your hierarchy so your users can navigate down to the subset of members. For example Customer dimension should have Customer hierarchy where you get to particular customer navigating through Customer Country, Customer State, Customer City levels.

If you are unable to come up with such nice hierarchy, you should try and build another attribute in the dimension based on exactly the same column but set the DiscretizationMethod property for it, such it becomes a grouping level. You add this additional attribute on top of your original one to create artificial way of navigating. It is bit clugy but without a way of navigating to subset of members you are stuck with displaying nundreds or thousand of members for user and that is not a very good idea.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

best way to order results sequentially starting from somewhere in the middle

I'm working with SQL Server 2005, and I'm trying to sort the results based on a user selected letter. Say the user selects 'D' to filter his results. I'd like to return the results starting from D followed by E, F, G...Z, A, B, C. What I'm getting is the results for the D entries at the top of the result set, followed by A, B, C, E...Z.

A solution comes to mind that would be very long and db intensive, by querying on 'like 'D', followed by like 'E', followed by like 'F', etc, but I'm sure that there is a much more efficient way to do this. Below is the code that I'm using now.

' where @.SortString = 'd' and @.Test is a temp Table

BEGIN

Insert into @.Test

Select CompanyName,ContactId, CompanyId

from vContacts where CompanyName like @.SortString +'%'

Order by CompanyName

Insert into @.Test

Select CompanyName,ContactId, CompanyId

from vContacts where CompanyName not like @.SortString +'%'

Order by CompanyName

END

Thanks in advance for your help

Code Snippet

SELECT
OrderSet = 1,
CompanyName,
ContactId,
CompanyId
FROM vContacts
WHERE CompanyName >= @.SortString

UNION

Select
2
CompanyName,
ContactId,
CompanyId
FROM vContacts
WHERE CompanyName < @.SortString

ORDER BY
OrderSet,

CompanyName

|||Thank you Arnie. Right on with the answer

Best Way to find the difference between two dates

Hi,

Can some one tell me the best way to find the difference between two dates. I got a table with dates. I need to filter records with dates older than 3 years from today. This calculation should take leap years in to account as well.

Many Thanks

Regards,

-VJ

use the datediff...

Datediff(YY,YourColumn,Getdate()) >3

Sample,

Code Snippet

Create Table #data (

[dates] Varchar(100)

);

Insert Into #data Values('1/1/2000');

Insert Into #data Values('1/1/2005');

Insert Into #data Values('12/4/1999');

Insert Into #data Values('12/3/2006');

Select

*

from

#Data

Where

datediff(yy, dates, getdate()) > 3

Best way to filter out noise words from user entered search string

Hi there,
I'm implemeting a full text search on my company's site and am trying to get
around the 'Query contained only noise words' error. What's the best way of
stripping the text from the search string and then notifying the user that
the words were removed? (kind of like a google search). Is there a way to
compare the search string with the noise word file and remove all instances
of matching words?
Thanks!
-- are we all computer monkeys?
The best way to do this is to stop mssearch, replace the contents of your
noise word list with a single space and then restart MSSearch and rebuild
your catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mr. Roundhill" <MrRoundhill@.discussions.microsoft.com> wrote in message
news:21EE62C6-74C2-4E01-B3A7-79D3612E5584@.microsoft.com...
> Hi there,
> I'm implemeting a full text search on my company's site and am trying to
get
> around the 'Query contained only noise words' error. What's the best way
of
> stripping the text from the search string and then notifying the user that
> the words were removed? (kind of like a google search). Is there a way
to
> compare the search string with the noise word file and remove all
instances
> of matching words?
> Thanks!
> --
> -- are we all computer monkeys?