Mapping two measure groups with different dimension usage

Hello.

I am looking for a solution for the following situation:

I have a measure group for my business facts and a measure group for tolerances and targets.

The first measure group uses a date dimension, a site dimension, a project dimension and an employee dimension.

The second group shares all dimensions except the employee dimension.

Lets say I have the business fact sales per hour.
The target value for this fact is stored in the second measure group without the employee info. The targets are valid for all employees at that site, project and day.

When i browse the cube i want see the targets next to the actual data - even on employee level.

Therefore i have to set IgnoreUnrelatedDimensions in the properties of the second measure group to True.
But when i do this i get an entry for every employee present in the employee dimension, showing the tolerance and target.

I am lost at the moment and belive that there must be an elegant solution. Hopefully someone has an idea?

Thanks,
ThomasFrom your description, it's not clear in which cases the IgnoreUnrelatedDimensions doesn't meet your needs - could you describe some scenarios? You could selectively use the MDX ValidMeasure() function in calculations, instead of applying IgnoreUnrelatedDimensions.|||Hello Deepak.

Thanks for your reply.

I'll try to explain my problem better with an example.

Data in my Tolerance FactTable:

DateProjectSiteTolerance_Sales_per_Hour20070320Upsell1Hamburg220070321Upsell1Hamburg220070322Upsell1Hamburg3

Data in my Sales FactTable:

DateProjectSiteEmployeeSales_Per_Hour20070320Upsell1HamburgBob1.520070320Upsell1HamburgMike2.120070321Upsell1HamburgBob2.720070321Upsell1HamburgMike3.320070322Upsell1HamburgBob1.2

When I select 20070322 as Date in the Browser, i get a row for Mike, although he dind't work on 22th.

DateProjectSiteEmployeeSales_Per_HourTolerance_Sales_per_Hour20070322Upsell1HamburgBob1.2320070322Upsell1HamburgMike3

Thanks to a tip from Markus i am using Scope-Statements at the moment

SCOPE([Measures].[Tolerance_Sales_Per_Hour]);
THIS=IIF (ISEMPTY([Measures].[Sales_Per_Hour]),NULL,[Tolerance_Sales_Per_Hour]);
END SCOPE;

But we are not sure, if this is the best solution.

Best regards,
Thomas

Manually change report

Hello,

I use Reporting Services in my solution. The data i have as source is very uncertain. Sometimes i manually have to delete a record from the report (not the database itself). I woluld like to have a checkbox or something simular to delete from report and totals, grafs would be updated. At the same time I would like the report to make a comment that a record was taken away, alternative let the user make a comment. Is this possible? DO i have to make a own application for that?

Thank you for your help!

Best regards,

Luskan

If you want my personal opinion, I don't think RS would be the best application for this.

Personally I would return the data from SQL to something like Excel using ADO/VBA or even MSQuery

and base your report / charts on the data then in Excel.

Deleting/changing the data will then have no effect on the data in your SQL database and if

constructed correcly, your charts/reports in excel will be updated automatically...

|||

Well, thank you for your opinion!

But i think I got a Solution. Besides exporting to Excel you can use cascading report parameters in order to to achive the same thing. The only problem i have know is that "Value field" and "label field" does not work as it is inteded to do.

Best regards,

Luskan

Manual log shipping

Hello:
I've implemented a stand-by server solution, where the
tran log backup from the primary server gets restored to
the secondary server at every 15-min interval.
I understand that there are some limitations with this
approach (could not implement MS SLS as our business unit
could not afford to purchase the Ent. Ed.), and was
wondering if anyone has encountered any other issues or
observations when implementing a similar manual log
shipping process, other than my own observations listed
below:
Log Shipping will fail if...
- ...there are any open connections to the database where
the transaction log files are restored to; though querying
tables using the fully qualified name is possible from
another database connection or via a linked server
connection.
I've also had one incident where my log shipping process
failed due to a LSN out of sync issue. This happened when
I ran a BCP IN operation. Other times, both BCP and BULK
INSERT operations ran successfully, funnelling changes to
the secondary server's database as expected.
Thank you for all your responses.
Regards,
- Rob.Your observations are correct. Log shipping will fail if there are =users connected to the database. I am wondering if perhaps someone =changed the dboptions when you had the log shipping fail after a BCP =import.
And no, I have not experienced any other issues. The custom log =shipping approach works very well.
You can find a script that will kill any connections to the specified =database here:
http://sqlguy.home.comcast.net/logship.htm
-- Keith
"Rob" <anonymous@.discussions.microsoft.com> wrote in message =news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
> Hello:
> > I've implemented a stand-by server solution, where the > tran log backup from the primary server gets restored to > the secondary server at every 15-min interval.
> > I understand that there are some limitations with this > approach (could not implement MS SLS as our business unit > could not afford to purchase the Ent. Ed.), and was > wondering if anyone has encountered any other issues or > observations when implementing a similar manual log > shipping process, other than my own observations listed > below:
> > Log Shipping will fail if...
> > - ...there are any open connections to the database where > the transaction log files are restored to; though querying > tables using the fully qualified name is possible from > another database connection or via a linked server > connection.
> > I've also had one incident where my log shipping process > failed due to a LSN out of sync issue. This happened when > I ran a BCP IN operation. Other times, both BCP and BULK > INSERT operations ran successfully, funnelling changes to > the secondary server's database as expected.
> > Thank you for all your responses.
> > Regards,
> > - Rob.|||Rather than doing a KILL command on each SPID, a cleaner
way to do it is to put the database in single user mode
with rollback immediate for the duration of the log
restore and then put it back in multi user mode. This
works very well. Here's an example:
alter database database_name set SINGLE_USER with rollback
immediate
restore log database_name from disk
= 'c:\database_name_log.bak' with standby
= 'c:\standby\database_name.bak'
alter database database_name set MULTI_USER
>--Original Message--
>Your observations are correct. Log shipping will fail if
there are users connected to the database. I am wondering
if perhaps someone changed the dboptions when you had the
log shipping fail after a BCP import.
>And no, I have not experienced any other issues. The
custom log shipping approach works very well.
>You can find a script that will kill any connections to
the specified database here:
>http://sqlguy.home.comcast.net/logship.htm
>--
>Keith
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
>> Hello:
>> I've implemented a stand-by server solution, where the
>> tran log backup from the primary server gets restored
to
>> the secondary server at every 15-min interval.
>> I understand that there are some limitations with this
>> approach (could not implement MS SLS as our business
unit
>> could not afford to purchase the Ent. Ed.), and was
>> wondering if anyone has encountered any other issues or
>> observations when implementing a similar manual log
>> shipping process, other than my own observations listed
>> below:
>> Log Shipping will fail if...
>> - ...there are any open connections to the database
where
>> the transaction log files are restored to; though
querying
>> tables using the fully qualified name is possible from
>> another database connection or via a linked server
>> connection.
>> I've also had one incident where my log shipping
process
>> failed due to a LSN out of sync issue. This happened
when
>> I ran a BCP IN operation. Other times, both BCP and
BULK
>> INSERT operations ran successfully, funnelling changes
to
>> the secondary server's database as expected.
>> Thank you for all your responses.
>> Regards,
>> - Rob.
>.
>|||Agreed. I need to update the web page.
-- Keith
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message =news:13bbf01c3f7d4$804fb2a0$a001280a@.phx.gbl...
> Rather than doing a KILL command on each SPID, a cleaner > way to do it is to put the database in single user mode > with rollback immediate for the duration of the log > restore and then put it back in multi user mode. This > works very well. Here's an example:
> > alter database database_name set SINGLE_USER with rollback > immediate
> > restore log database_name from disk > =3D 'c:\database_name_log.bak' with standby > =3D 'c:\standby\database_name.bak'
> > alter database database_name set MULTI_USER
> > >--Original Message--
> >Your observations are correct. Log shipping will fail if > there are users connected to the database. I am wondering > if perhaps someone changed the dboptions when you had the > log shipping fail after a BCP import. > >
> >And no, I have not experienced any other issues. The > custom log shipping approach works very well.
> >
> >You can find a script that will kill any connections to > the specified database here:
> >http://sqlguy.home.comcast.net/logship.htm
> >
> >-- > >Keith
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in > message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
> >> Hello:
> >> > >> I've implemented a stand-by server solution, where the > >> tran log backup from the primary server gets restored > to > >> the secondary server at every 15-min interval.
> >> > >> I understand that there are some limitations with this > >> approach (could not implement MS SLS as our business > unit > >> could not afford to purchase the Ent. Ed.), and was > >> wondering if anyone has encountered any other issues or > >> observations when implementing a similar manual log > >> shipping process, other than my own observations listed > >> below:
> >> > >> Log Shipping will fail if...
> >> > >> - ...there are any open connections to the database > where > >> the transaction log files are restored to; though > querying > >> tables using the fully qualified name is possible from > >> another database connection or via a linked server > >> connection.
> >> > >> I've also had one incident where my log shipping > process > >> failed due to a LSN out of sync issue. This happened > when > >> I ran a BCP IN operation. Other times, both BCP and > BULK > >> INSERT operations ran successfully, funnelling changes > to > >> the secondary server's database as expected.
> >> > >> Thank you for all your responses.
> >> > >> Regards,
> >> > >> - Rob.
> >.
> >|||But even in single user mode, there could be multiple
connections to the database, which can cause manual log
shipping failures. In this case, I find killing all user
connections more effective, to ensure no connections
exists prior to restoring either the full backup and/or
the tran log.
Thanks.
>--Original Message--
>Rather than doing a KILL command on each SPID, a cleaner
>way to do it is to put the database in single user mode
>with rollback immediate for the duration of the log
>restore and then put it back in multi user mode. This
>works very well. Here's an example:
>alter database database_name set SINGLE_USER with
rollback
>immediate
>restore log database_name from disk
>= 'c:\database_name_log.bak' with standby
>= 'c:\standby\database_name.bak'
>alter database database_name set MULTI_USER
>>--Original Message--
>>Your observations are correct. Log shipping will fail
if
>there are users connected to the database. I am
wondering
>if perhaps someone changed the dboptions when you had the
>log shipping fail after a BCP import.
>>And no, I have not experienced any other issues. The
>custom log shipping approach works very well.
>>You can find a script that will kill any connections to
>the specified database here:
>>http://sqlguy.home.comcast.net/logship.htm
>>--
>>Keith
>>
>>"Rob" <anonymous@.discussions.microsoft.com> wrote in
>message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
>> Hello:
>> I've implemented a stand-by server solution, where the
>> tran log backup from the primary server gets restored
>to
>> the secondary server at every 15-min interval.
>> I understand that there are some limitations with this
>> approach (could not implement MS SLS as our business
>unit
>> could not afford to purchase the Ent. Ed.), and was
>> wondering if anyone has encountered any other issues
or
>> observations when implementing a similar manual log
>> shipping process, other than my own observations
listed
>> below:
>> Log Shipping will fail if...
>> - ...there are any open connections to the database
>where
>> the transaction log files are restored to; though
>querying
>> tables using the fully qualified name is possible from
>> another database connection or via a linked server
>> connection.
>> I've also had one incident where my log shipping
>process
>> failed due to a LSN out of sync issue. This happened
>when
>> I ran a BCP IN operation. Other times, both BCP and
>BULK
>> INSERT operations ran successfully, funnelling changes
>to
>> the secondary server's database as expected.
>> Thank you for all your responses.
>> Regards,
>> - Rob.
>>.
>.
>|||Putting it in 'single user mode with rollback immediate'
will disconnect any currnet connections to the db and then
put it in single user mode for the process to restore the
log. Since it's in single user mode, only the process
that is restoring the log can connect. Once the restore
is done, just put it back into multi user mode.
>--Original Message--
>But even in single user mode, there could be multiple
>connections to the database, which can cause manual log
>shipping failures. In this case, I find killing all user
>connections more effective, to ensure no connections
>exists prior to restoring either the full backup and/or
>the tran log.
>Thanks.
>>--Original Message--
>>Rather than doing a KILL command on each SPID, a cleaner
>>way to do it is to put the database in single user mode
>>with rollback immediate for the duration of the log
>>restore and then put it back in multi user mode. This
>>works very well. Here's an example:
>>alter database database_name set SINGLE_USER with
>rollback
>>immediate
>>restore log database_name from disk
>>= 'c:\database_name_log.bak' with standby
>>= 'c:\standby\database_name.bak'
>>alter database database_name set MULTI_USER
>>--Original Message--
>>Your observations are correct. Log shipping will fail
>if
>>there are users connected to the database. I am
>wondering
>>if perhaps someone changed the dboptions when you had
the
>>log shipping fail after a BCP import.
>>And no, I have not experienced any other issues. The
>>custom log shipping approach works very well.
>>You can find a script that will kill any connections to
>>the specified database here:
>>http://sqlguy.home.comcast.net/logship.htm
>>--
>>Keith
>>
>>"Rob" <anonymous@.discussions.microsoft.com> wrote in
>>message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
>> Hello:
>> I've implemented a stand-by server solution, where
the
>> tran log backup from the primary server gets restored
>>to
>> the secondary server at every 15-min interval.
>> I understand that there are some limitations with
this
>> approach (could not implement MS SLS as our business
>>unit
>> could not afford to purchase the Ent. Ed.), and was
>> wondering if anyone has encountered any other issues
>or
>> observations when implementing a similar manual log
>> shipping process, other than my own observations
>listed
>> below:
>> Log Shipping will fail if...
>> - ...there are any open connections to the database
>>where
>> the transaction log files are restored to; though
>>querying
>> tables using the fully qualified name is possible
from
>> another database connection or via a linked server
>> connection.
>> I've also had one incident where my log shipping
>>process
>> failed due to a LSN out of sync issue. This happened
>>when
>> I ran a BCP IN operation. Other times, both BCP and
>>BULK
>> INSERT operations ran successfully, funnelling
changes
>>to
>> the secondary server's database as expected.
>> Thank you for all your responses.
>> Regards,
>> - Rob.
>>.
>>.
>.
>

Manual log shipping

Hello:
I've implemented a stand-by server solution, where the
tran log backup from the primary server gets restored to
the secondary server at every 15-min interval.
I understand that there are some limitations with this
approach (could not implement MS SLS as our business unit
could not afford to purchase the Ent. Ed.), and was
wondering if anyone has encountered any other issues or
observations when implementing a similar manual log
shipping process, other than my own observations listed
below:
Log Shipping will fail if...
- ...there are any open connections to the database where
the transaction log files are restored to; though querying
tables using the fully qualified name is possible from
another database connection or via a linked server
connection.
I've also had one incident where my log shipping process
failed due to a LSN out of sync issue. This happened when
I ran a BCP IN operation. Other times, both BCP and BULK
INSERT operations ran successfully, funnelling changes to
the secondary server's database as expected.
Thank you for all your responses.
Regards,
- Rob.Your observations are correct. Log shipping will fail if there are =
users connected to the database. I am wondering if perhaps someone =
changed the dboptions when you had the log shipping fail after a BCP =
import. =20
And no, I have not experienced any other issues. The custom log =
shipping approach works very well.
You can find a script that will kill any connections to the specified =
database here:
http://sqlguy.home.comcast.net/logship.htm
--=20
Keith
"Rob" <anonymous@.discussions.microsoft.com> wrote in message =
news:1332101c3f7bf$64a958e0$a301280a@.phx
.gbl...
> Hello:
>=20
> I've implemented a stand-by server solution, where the=20
> tran log backup from the primary server gets restored to=20
> the secondary server at every 15-min interval.
>=20
> I understand that there are some limitations with this=20
> approach (could not implement MS SLS as our business unit=20
> could not afford to purchase the Ent. Ed.), and was=20
> wondering if anyone has encountered any other issues or=20
> observations when implementing a similar manual log=20
> shipping process, other than my own observations listed=20
> below:
>=20
> Log Shipping will fail if...
>=20
> - ...there are any open connections to the database where=20
> the transaction log files are restored to; though querying=20
> tables using the fully qualified name is possible from=20
> another database connection or via a linked server=20
> connection.
>=20
> I've also had one incident where my log shipping process=20
> failed due to a LSN out of sync issue. This happened when=20
> I ran a BCP IN operation. Other times, both BCP and BULK=20
> INSERT operations ran successfully, funnelling changes to=20
> the secondary server's database as expected.
>=20
> Thank you for all your responses.
>=20
> Regards,
>=20
> - Rob.|||Rather than doing a KILL command on each SPID, a cleaner
way to do it is to put the database in single user mode
with rollback immediate for the duration of the log
restore and then put it back in multi user mode. This
works very well. Here's an example:
alter database database_name set SINGLE_USER with rollback
immediate
restore log database_name from disk
= 'c:\database_name_log.bak' with standby
= 'c:\standby\database_name.bak'
alter database database_name set MULTI_USER

>--Original Message--
>Your observations are correct. Log shipping will fail if
there are users connected to the database. I am wondering
if perhaps someone changed the dboptions when you had the
log shipping fail after a BCP import.
>And no, I have not experienced any other issues. The
custom log shipping approach works very well.
>You can find a script that will kill any connections to
the specified database here:
>http://sqlguy.home.comcast.net/logship.htm
>--
>Keith
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:1332101c3f7bf$64a958e0$a301280a@.phx
.gbl...
to
unit
where
querying
process
when
BULK
to
>.
>|||Agreed. I need to update the web page.
--=20
Keith
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message =
news:13bbf01c3f7d4$804fb2a0$a001280a@.phx
.gbl...
> Rather than doing a KILL command on each SPID, a cleaner=20
> way to do it is to put the database in single user mode=20
> with rollback immediate for the duration of the log=20
> restore and then put it back in multi user mode. This=20
> works very well. Here's an example:
>=20
> alter database database_name set SINGLE_USER with rollback=20
> immediate
>=20
> restore log database_name from disk=20
> =3D 'c:\database_name_log.bak' with standby=20
> =3D 'c:\standby\database_name.bak'
>=20
> alter database database_name set MULTI_USER
>=20
> there are users connected to the database. I am wondering=20
> if perhaps someone changed the dboptions when you had the=20
> log shipping fail after a BCP import. =20
> custom log shipping approach works very well.
> the specified database here:
> message news:1332101c3f7bf$64a958e0$a301280a@.phx
.gbl...
> to=20
> unit=20
> where=20
> querying=20
> process=20
> when=20
> BULK=20
> to=20|||But even in single user mode, there could be multiple
connections to the database, which can cause manual log
shipping failures. In this case, I find killing all user
connections more effective, to ensure no connections
exists prior to restoring either the full backup and/or
the tran log.
Thanks.

>--Original Message--
>Rather than doing a KILL command on each SPID, a cleaner
>way to do it is to put the database in single user mode
>with rollback immediate for the duration of the log
>restore and then put it back in multi user mode. This
>works very well. Here's an example:
>alter database database_name set SINGLE_USER with
rollback
>immediate
>restore log database_name from disk
>= 'c:\database_name_log.bak' with standby
>= 'c:\standby\database_name.bak'
>alter database database_name set MULTI_USER
>
if
>there are users connected to the database. I am
wondering
>if perhaps someone changed the dboptions when you had the
>log shipping fail after a BCP import.
>custom log shipping approach works very well.
>the specified database here:
>message news:1332101c3f7bf$64a958e0$a301280a@.phx
.gbl...
>to
>unit
or
listed
>where
>querying
>process
>when
>BULK
>to
>.
>|||Putting it in 'single user mode with rollback immediate'
will disconnect any currnet connections to the db and then
put it in single user mode for the process to restore the
log. Since it's in single user mode, only the process
that is restoring the log can connect. Once the restore
is done, just put it back into multi user mode.

>--Original Message--
>But even in single user mode, there could be multiple
>connections to the database, which can cause manual log
>shipping failures. In this case, I find killing all user
>connections more effective, to ensure no connections
>exists prior to restoring either the full backup and/or
>the tran log.
>Thanks.
>
>rollback
>if
>wondering
the
the
this
>or
>listed
from
changes
>.
>

Managing Table Size

I agree with Bobs solution, but here is some SQL that will
find values greater than 4 months rather than 120 days
(although I am being very picky)
delete
FROM order
WHERE DATEDIFF(month, orderdate, getdate()) > 4
J

>--Original Message--
>I have a table in my DB that I would like to restrict to
recent records
>only. Recent being the last 4 months. Can someone help me
determine the
>easiest and most maintenance free way to accomplish this?
>Thank You
>
>.
>
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:1a22a01c41d4d$1f22acf0$a101280a@.phx.gbl...
> I agree with Bobs solution, but here is some SQL that will
> find values greater than 4 months rather than 120 days
> (although I am being very picky)
> delete
> FROM order
> WHERE DATEDIFF(month, orderdate, getdate()) > 4
LOL I was going to post that, but had a sudden doubt as to whether DATEDIFF
was TSQL or VB, couldn't check, so I chickened out.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004

Managing Table Size

I agree with Bobs solution, but here is some SQL that will
find values greater than 4 months rather than 120 days
(although I am being very picky)
delete
FROM order
WHERE DATEDIFF(month, orderdate, getdate()) > 4
J

>--Original Message--
>I have a table in my DB that I would like to restrict to
recent records
>only. Recent being the last 4 months. Can someone help me
determine the
>easiest and most maintenance free way to accomplish this?
>Thank You
>
>.
>"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:1a22a01c41d4d$1f22acf0$a101280a@.phx
.gbl...
> I agree with Bobs solution, but here is some SQL that will
> find values greater than 4 months rather than 120 days
> (although I am being very picky)
> delete
> FROM order
> WHERE DATEDIFF(month, orderdate, getdate()) > 4
LOL I was going to post that, but had a sudden doubt as to whether DATEDIFF
was TSQL or VB, couldn't check, so I chickened out.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004

Managing Scheduled Jobs from Management Studio Express

I'm working on a backup solution for my company. Right now we have three servers in three locations running SQL Server 2000. For testing purposes, I created a scheduled job on one of these servers. It worked fine but I'd like to tweak the job some, tinker with the timing and save locations. I'm using Management Studio Express on my laptop to remotely work with these databases but I can't seem to find a decent way to work with existing jobs. Am I missing something or does SSMSE lack the "manage jobs" functionality?

SSMSE only exposes functionality that is available in SQL Express. Since Express doesn't include Agent, SSMSE doesn't include the ability to manage Agent jobs.

The SQL 2005 Feature Pack includes some DTS 2000 add-in that may meet your needs. I've not worked with them, but they may work for you.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Managing Permissions

I am having trouble managing permission and am looking for a solution. My
databases has 150 tables, 5 Roles and ~40 logins. And I have a 2 inch by 3
inch window to check and uncheck table/Column permissions. This is
frustrating!!! Is this data held in one of the system tables? Is there som
e
additional software to handle this?Hi,
See "GRANT" and "REVOKE" commands in SQL Server Books online.
Thanks
Hari
SQL Server MVP
"Matt Sonic" <MattSonic@.discussions.microsoft.com> wrote in message
news:3F3DE2FD-C2F3-4DA3-849F-1F7BC167B87F@.microsoft.com...
>I am having trouble managing permission and am looking for a solution. My
> databases has 150 tables, 5 Roles and ~40 logins. And I have a 2 inch by
> 3
> inch window to check and uncheck table/Column permissions. This is
> frustrating!!! Is this data held in one of the system tables? Is there
> some
> additional software to handle this?|||Check out http://www.agileinfollc.com DataStudio
"Matt Sonic" <MattSonic@.discussions.microsoft.com> wrote in message
news:3F3DE2FD-C2F3-4DA3-849F-1F7BC167B87F@.microsoft.com...
>I am having trouble managing permission and am looking for a solution. My
> databases has 150 tables, 5 Roles and ~40 logins. And I have a 2 inch by
> 3
> inch window to check and uncheck table/Column permissions. This is
> frustrating!!! Is this data held in one of the system tables? Is there
> some
> additional software to handle this?

Managing Large Views on Large Tables

An indexed view appears to be a solution to managing huge tables, but my
efforts for this have missed the mark and there may be better solutions
anyhow. In a table of roughly 500 million records, 50 million may belong to
a given month of a year. Without creating 12 tables of each month, can this
be done somehow without using an indexed view? For that matter, it may not
hurt to try the indexed view again. What is the best solution for handling
queries against enormous amounts of data (Windows 2000 Advanced Server with 4
Gig RAM - not /3GB switching, no /PAE).
Regards,
Jamie
If your main concern is performance, index tuning is paramount. Indexed
views can greatly improve performance of certain types of queries,
especially aggregations. You may need different indexes to support
different types of queries.
However, you still need to consider manageability since it will take a while
to rebuild indexes on large tables/views such as this. If possible,
consider SQL 2005 since it provides partitioning to better facilitate
managing large tables.
-
Hope this helps.
Dan Guzman
SQL Server MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:E18AA5C1-AC1E-4E82-8B2E-9E2282D81A53@.microsoft.com...
> An indexed view appears to be a solution to managing huge tables, but my
> efforts for this have missed the mark and there may be better solutions
> anyhow. In a table of roughly 500 million records, 50 million may belong
> to
> a given month of a year. Without creating 12 tables of each month, can
> this
> be done somehow without using an indexed view? For that matter, it may
> not
> hurt to try the indexed view again. What is the best solution for
> handling
> queries against enormous amounts of data (Windows 2000 Advanced Server
> with 4
> Gig RAM - not /3GB switching, no /PAE).
> --
> Regards,
> Jamie
|||A truly great help at this point would be an example of an indexed view using
an aggregate.
Regards,
Jamie
"Dan Guzman" wrote:

> If your main concern is performance, index tuning is paramount. Indexed
> views can greatly improve performance of certain types of queries,
> especially aggregations. You may need different indexes to support
> different types of queries.
> However, you still need to consider manageability since it will take a while
> to rebuild indexes on large tables/views such as this. If possible,
> consider SQL 2005 since it provides partitioning to better facilitate
> managing large tables.
>
> -
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:E18AA5C1-AC1E-4E82-8B2E-9E2282D81A53@.microsoft.com...
>
|||>A truly great help at this point would be an example of an indexed view
>using
> an aggregate.
Below is an example using the Northwind [Order Details] table:
CREATE VIEW dbo.Product_Order_Summary
WITH SCHEMABINDING
AS
SELECT
ProductID,
SUM(UnitPrice * Quantity) AS GrossTotal,
SUM((UnitPrice * Quantity) - Discount) AS NetTotal,
SUM(Quantity) AS OrderQuantity,
COUNT_BIG(*) AS Orders
FROM dbo.[Order Details]
GROUP BY
ProductID
GO
CREATE UNIQUE CLUSTERED INDEX cdx_Product_Order_Summary
ON Product_Order_Summary(ProductID)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:D8E0434F-A220-497F-8012-6AD38B42F4EF@.microsoft.com...[vbcol=seagreen]
>A truly great help at this point would be an example of an indexed view
>using
> an aggregate.
> --
> Regards,
> Jamie
>
> "Dan Guzman" wrote:
|||Thank you.
Regards,
Jamie
"Dan Guzman" wrote:

> Below is an example using the Northwind [Order Details] table:
> CREATE VIEW dbo.Product_Order_Summary
> WITH SCHEMABINDING
> AS
> SELECT
> ProductID,
> SUM(UnitPrice * Quantity) AS GrossTotal,
> SUM((UnitPrice * Quantity) - Discount) AS NetTotal,
> SUM(Quantity) AS OrderQuantity,
> COUNT_BIG(*) AS Orders
> FROM dbo.[Order Details]
> GROUP BY
> ProductID
> GO
> CREATE UNIQUE CLUSTERED INDEX cdx_Product_Order_Summary
> ON Product_Order_Summary(ProductID)
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:D8E0434F-A220-497F-8012-6AD38B42F4EF@.microsoft.com...
>

Managing Large Views on Large Tables

An indexed view appears to be a solution to managing huge tables, but my
efforts for this have missed the mark and there may be better solutions
anyhow. In a table of roughly 500 million records, 50 million may belong to
a given month of a year. Without creating 12 tables of each month, can thi
s
be done somehow without using an indexed view? For that matter, it may not
hurt to try the indexed view again. What is the best solution for handling
queries against enormous amounts of data (Windows 2000 Advanced Server with
4
Gig RAM - not /3GB switching, no /PAE).
--
Regards,
JamieIf your main concern is performance, index tuning is paramount. Indexed
views can greatly improve performance of certain types of queries,
especially aggregations. You may need different indexes to support
different types of queries.
However, you still need to consider manageability since it will take a while
to rebuild indexes on large tables/views such as this. If possible,
consider SQL 2005 since it provides partitioning to better facilitate
managing large tables.
-
Hope this helps.
Dan Guzman
SQL Server MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:E18AA5C1-AC1E-4E82-8B2E-9E2282D81A53@.microsoft.com...
> An indexed view appears to be a solution to managing huge tables, but my
> efforts for this have missed the mark and there may be better solutions
> anyhow. In a table of roughly 500 million records, 50 million may belong
> to
> a given month of a year. Without creating 12 tables of each month, can
> this
> be done somehow without using an indexed view? For that matter, it may
> not
> hurt to try the indexed view again. What is the best solution for
> handling
> queries against enormous amounts of data (Windows 2000 Advanced Server
> with 4
> Gig RAM - not /3GB switching, no /PAE).
> --
> Regards,
> Jamie|||A truly great help at this point would be an example of an indexed view usin
g
an aggregate.
--
Regards,
Jamie
"Dan Guzman" wrote:

> If your main concern is performance, index tuning is paramount. Indexed
> views can greatly improve performance of certain types of queries,
> especially aggregations. You may need different indexes to support
> different types of queries.
> However, you still need to consider manageability since it will take a whi
le
> to rebuild indexes on large tables/views such as this. If possible,
> consider SQL 2005 since it provides partitioning to better facilitate
> managing large tables.
>
> -
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:E18AA5C1-AC1E-4E82-8B2E-9E2282D81A53@.microsoft.com...
>|||>A truly great help at this point would be an example of an indexed view
>using
> an aggregate.
Below is an example using the Northwind [Order Details] table:
CREATE VIEW dbo.Product_Order_Summary
WITH SCHEMABINDING
AS
SELECT
ProductID,
SUM(UnitPrice * Quantity) AS GrossTotal,
SUM((UnitPrice * Quantity) - Discount) AS NetTotal,
SUM(Quantity) AS OrderQuantity,
COUNT_BIG(*) AS Orders
FROM dbo.[Order Details]
GROUP BY
ProductID
GO
CREATE UNIQUE CLUSTERED INDEX cdx_Product_Order_Summary
ON Product_Order_Summary(ProductID)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:D8E0434F-A220-497F-8012-6AD38B42F4EF@.microsoft.com...[vbcol=seagreen]
>A truly great help at this point would be an example of an indexed view
>using
> an aggregate.
> --
> Regards,
> Jamie
>
> "Dan Guzman" wrote:
>|||Thank you.
--
Regards,
Jamie
"Dan Guzman" wrote:

> Below is an example using the Northwind [Order Details] table:
> CREATE VIEW dbo.Product_Order_Summary
> WITH SCHEMABINDING
> AS
> SELECT
> ProductID,
> SUM(UnitPrice * Quantity) AS GrossTotal,
> SUM((UnitPrice * Quantity) - Discount) AS NetTotal,
> SUM(Quantity) AS OrderQuantity,
> COUNT_BIG(*) AS Orders
> FROM dbo.[Order Details]
> GROUP BY
> ProductID
> GO
> CREATE UNIQUE CLUSTERED INDEX cdx_Product_Order_Summary
> ON Product_Order_Summary(ProductID)
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:D8E0434F-A220-497F-8012-6AD38B42F4EF@.microsoft.com...
>

Management Studio Solution user-defined project folders?

When you create a Solution/Project in Management Studio, it creates 3 folders for you - Connections, Queries, Miscellaneous.

It does not appear that there exists the ability to create your own set of folders, either under the project or any of the 3 provided folders. Does anyone know of a way to do this?

If you are working on a project that has hundreds, perhaps thousands of stored procedures, views, etc., there currently seems to be no way to organize them. If this is true, this is an incredible MS oversight!

Thanks!

You have found the growing pain problems with the tool...maybe future versions will correct it.|||I would hope so! Since Mgmt Studio is based upon the Visual Studio shell, it doesn't seem like it would be that hard. In fact, I would think they would have had to specifically disable that functionality in this version, it seems so basic and fundamental to an IDE.|||

This is one of our most requested features. You can track the progress of this at http://connect.microsoft.com.

Here is the direct link to the issue: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124787

Vote on it and let us know that it is important to you.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/

|||

I already did. I also started my own post to this issue a long time ago.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127202

|||

Ahh, nice... even though your solution is not solved, can you mark the post as answered?

Management Studio Solution user-defined project folders?

When you create a Solution/Project in Management Studio, it creates 3 folders for you - Connections, Queries, Miscellaneous.

It does not appear that there exists the ability to create your own set of folders, either under the project or any of the 3 provided folders. Does anyone know of a way to do this?

If you are working on a project that has hundreds, perhaps thousands of stored procedures, views, etc., there currently seems to be no way to organize them. If this is true, this is an incredible MS oversight!

Thanks!

You have found the growing pain problems with the tool...maybe future versions will correct it.|||I would hope so! Since Mgmt Studio is based upon the Visual Studio shell, it doesn't seem like it would be that hard. In fact, I would think they would have had to specifically disable that functionality in this version, it seems so basic and fundamental to an IDE.|||

This is one of our most requested features. You can track the progress of this at http://connect.microsoft.com.

Here is the direct link to the issue: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124787

Vote on it and let us know that it is important to you.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/

|||

I already did. I also started my own post to this issue a long time ago.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127202

|||

Ahh, nice... even though your solution is not solved, can you mark the post as answered?

|||I agree it is a huge MS oversight... fortunately theres a 3rd party solution available... Here's the link SQL Server Management Studio 2005 Project Plugin|||I agree it is a huge MS oversight... fortunately theres a 3rd party solution available... Here's the link SQL Server Management Studio 2005 Project Plugin

Management Studio Solution Explorer

Is it possible to set up management studio so the solution explorer is on a
shared network directory?
Exactly what do you mean by "the solution explorer is on a shared network directory"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:BF538172-1FB0-40BF-91E6-BDAC02ADA867@.microsoft.com...
> Is it possible to set up management studio so the solution explorer is on a
> shared network directory?
|||I mean that the solution explorer is set to a shared network directory so a
team can share the sql solutions. Assuming of course that there is no
compatible version control software.
"Tibor Karaszi" wrote:

> Exactly what do you mean by "the solution explorer is on a shared network directory"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:BF538172-1FB0-40BF-91E6-BDAC02ADA867@.microsoft.com...
>
|||You can open a solution on a network drive (I just tried it). Whether you can have several users
working on the same solution at the same time, I'm afraid I don't know.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:7448AB4A-57AD-40D5-9A39-20C7BF70C72B@.microsoft.com...[vbcol=seagreen]
>I mean that the solution explorer is set to a shared network directory so a
> team can share the sql solutions. Assuming of course that there is no
> compatible version control software.
> "Tibor Karaszi" wrote:

Management Studio Solution Explorer

Is it possible to set up management studio so the solution explorer is on a
shared network directory?Exactly what do you mean by "the solution explorer is on a shared network di
rectory"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:BF538172-1FB0-40BF-91E6-BDAC02ADA867@.microsoft.com...
> Is it possible to set up management studio so the solution explorer is on
a
> shared network directory?|||I mean that the solution explorer is set to a shared network directory so a
team can share the sql solutions. Assuming of course that there is no
compatible version control software.
"Tibor Karaszi" wrote:

> Exactly what do you mean by "the solution explorer is on a shared network
directory"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:BF538172-1FB0-40BF-91E6-BDAC02ADA867@.microsoft.com...
>|||You can open a solution on a network drive (I just tried it). Whether you ca
n have several users
working on the same solution at the same time, I'm afraid I don't know.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:7448AB4A-57AD-40D5-9A39-20C7BF70C72B@.microsoft.com...[vbcol=seagreen]
>I mean that the solution explorer is set to a shared network directory so a
> team can share the sql solutions. Assuming of course that there is no
> compatible version control software.
> "Tibor Karaszi" wrote:
>

Management Studio Solution Explorer

Is it possible to set up management studio so the solution explorer is on a
shared network directory?Exactly what do you mean by "the solution explorer is on a shared network directory"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:BF538172-1FB0-40BF-91E6-BDAC02ADA867@.microsoft.com...
> Is it possible to set up management studio so the solution explorer is on a
> shared network directory?|||I mean that the solution explorer is set to a shared network directory so a
team can share the sql solutions. Assuming of course that there is no
compatible version control software.
"Tibor Karaszi" wrote:
> Exactly what do you mean by "the solution explorer is on a shared network directory"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:BF538172-1FB0-40BF-91E6-BDAC02ADA867@.microsoft.com...
> > Is it possible to set up management studio so the solution explorer is on a
> > shared network directory?
>|||You can open a solution on a network drive (I just tried it). Whether you can have several users
working on the same solution at the same time, I'm afraid I don't know.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:7448AB4A-57AD-40D5-9A39-20C7BF70C72B@.microsoft.com...
>I mean that the solution explorer is set to a shared network directory so a
> team can share the sql solutions. Assuming of course that there is no
> compatible version control software.
> "Tibor Karaszi" wrote:
>> Exactly what do you mean by "the solution explorer is on a shared network directory"?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Mark" <Mark@.discussions.microsoft.com> wrote in message
>> news:BF538172-1FB0-40BF-91E6-BDAC02ADA867@.microsoft.com...
>> > Is it possible to set up management studio so the solution explorer is on a
>> > shared network directory?
>>