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 do a dynamic bulk insert to a table

My current project is creating a social network for the university I work for. One of the features allows members of a group to send a message to all other group members. Currently, I run a foreach loop over each of the group members, and run a separate INSERT statement to insert a message into my messages table. Once the group has several hundreds members, everybody starts getting timeout errors. What is the best way to do this?

Here are two suggestions I've received: construct one sql statement that would contain multiple INSERT statements. It would be a large statement like:

INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user, @.to_user, @.subject, @.body); INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user2, @.to_user2, @.subject2, @.body2); INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user3, @.to_user3, @.subject3, @.body3);

etc...

Or, do the foreach loop in a stored procedure. I know the pros and cons of sprocs versus dynamic sql is a sticky subject, and, personally, I'd prefer to keep my logic in the C# code-behind file. What is the best way to do this is an efficient manner? I'd be happy to share some code, if that would help. Thanks for your input!

I think what you want is something like this. 1 single query.

INSERT into [messages] (from_user, to_user, subject, body)
SELECT @.from_user,userid,@.subject,@.body FROM groupmembers WHERE groupid=@.groupid AND userid<>@.from_user

Your input parameters:

@.from_user = the sending user
@.subject = the subject
@.body = the body
@.groupid=the recieving groupid

|||

If you going to insert data to a table that alreay has some data, I suggest you to use whatgunteman wrote.

If not, I suggest you to use:

1SELECT *2INTO MyNewTable-- it will be created automatically here3FROM MyTable1 t14INNERJOIN5 MyTable2 t26ON (t1.rid = t2.rid)7

Good luck.

|||

Thanks that worked like a charm. I can't believe I never thought of putting a subquery in an insert statement.