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 delete the reporting services jobs in sqlserver

I have six thousand reporting services jobs in sqlserver, which were
scheduled to run only once and their execution date is older than a month.
how do I know which of the jobs are useful out of these 6000.
Can I write a job, to purge the reporting services jobs older than a month
and schedule it to run every week, basically how do I manage these ever
growing jobs ?
Thanks in advance..GAis it okay to delete the subscriptions like below '
delete from reportserver..subscriptions
WHERE ModifiedDate < dateadd(day, -7, getdate()) AND Description LIKE 'SRS%'
TIA
"DallasBlue" wrote:
> I have six thousand reporting services jobs in sqlserver, which were
> scheduled to run only once and their execution date is older than a month.
> how do I know which of the jobs are useful out of these 6000.
> Can I write a job, to purge the reporting services jobs older than a month
> and schedule it to run every week, basically how do I manage these ever
> growing jobs ?
>
> Thanks in advance..GA
>|||I have deleted subscriptions using this method. There are cascading deletes
when to remove the related records in ReportSchedule and Schedule tables.
Though, bulk deleting a lot of subscriptions at the same time might not work
with that statement. I know for me I've gotten timeouts when trying a direct
delete. So instead, I create a memory table (DECLARE @.x TABLE...) and put in
all the SubscriptionID for the ones I want to delete. Then I delete from the
Subscription table using a join to the memory table. I know it might seem
strange to do it that way, but because of the way the tables are indexed, it
works.
Once you the records are deleted from the tables, the Reporting Service will
automatically delete the actual SQL Agent jobs when it does its maintenance,
I'm not sure of the frequency that it does that, but at minimum its once a
day.
Carmine
"DallasBlue" wrote:
> is it okay to delete the subscriptions like below '
> delete from reportserver..subscriptions
> WHERE ModifiedDate < dateadd(day, -7, getdate()) AND Description LIKE 'SRS%'
> TIA
> "DallasBlue" wrote:
> > I have six thousand reporting services jobs in sqlserver, which were
> > scheduled to run only once and their execution date is older than a month.
> > how do I know which of the jobs are useful out of these 6000.
> > Can I write a job, to purge the reporting services jobs older than a month
> > and schedule it to run every week, basically how do I manage these ever
> > growing jobs ?
> >
> >
> > Thanks in advance..GA
> >
> >