How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base table
You can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:
>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>
Showing posts with label version. Show all posts
Showing posts with label version. Show all posts
Tuesday, March 20, 2012
Another version of SQL Permission Question
How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base tableYou can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:
>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>
table. I'm trying to use a view and limit access to the base tableYou can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:
>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>
Another version of SQL Permission Question
How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base tableThis is a multi-part message in MIME format.
--000602000304030604090506
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
You can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:
>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>
--000602000304030604090506
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You can "update" a view in certain restricted conditions (like
there can't be any joins in the view for instance - see BOL for the
complete list of restrictions). Alternately, you can write an INSTEAD
OF trigger on the view so that the trigger code handles the updating of
the underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.<br>
<br>
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).<br>
<br>
Cheers,<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
KathyV wrote:
<blockquote cite="mid282DBEFC-5100-4936-A1EA-A11561D1DDD4@.microsoft.com"
type="cite">
<pre wrap="">How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base table
</pre>
</blockquote>
</body>
</html>
--000602000304030604090506--
table. I'm trying to use a view and limit access to the base tableThis is a multi-part message in MIME format.
--000602000304030604090506
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
You can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:
>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>
--000602000304030604090506
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You can "update" a view in certain restricted conditions (like
there can't be any joins in the view for instance - see BOL for the
complete list of restrictions). Alternately, you can write an INSTEAD
OF trigger on the view so that the trigger code handles the updating of
the underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.<br>
<br>
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).<br>
<br>
Cheers,<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
KathyV wrote:
<blockquote cite="mid282DBEFC-5100-4936-A1EA-A11561D1DDD4@.microsoft.com"
type="cite">
<pre wrap="">How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base table
</pre>
</blockquote>
</body>
</html>
--000602000304030604090506--
Friday, February 24, 2012
ANN: Updated version of SQL Server 2005 Books Online is available for download
The September 2007 update to SQL Server 2005 Books Online (English version)
is now available for download at
http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en.
It is also offered as an Optional update via Microsoft Update.
See the topic "New and Updated Books Online"
(ms-help://sql90/sqlwhtn9/html/0f2f37d8-fc81-40c5-9976-cab8bd0b44e9.htm) for
links to the new and revised topics.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
On Dec 1, 5:53 am, "Gail Erickson [MS]" <ga...@.online.microsoft.com>
wrote:
> The September 2007 update to SQL Server 2005 Books Online (English version)
> is now available for download athttp://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00d...
> It is also offered as an Optional update via Microsoft Update.
> See the topic "New and Updated Books Online"
> (ms-help://sql90/sqlwhtn9/html/0f2f37d8-fc81-40c5-9976-cab8bd0b44e9.htm) for
> links to the new and revised topics.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online fromhttp://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
Thanks for the updation
is now available for download at
http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en.
It is also offered as an Optional update via Microsoft Update.
See the topic "New and Updated Books Online"
(ms-help://sql90/sqlwhtn9/html/0f2f37d8-fc81-40c5-9976-cab8bd0b44e9.htm) for
links to the new and revised topics.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
On Dec 1, 5:53 am, "Gail Erickson [MS]" <ga...@.online.microsoft.com>
wrote:
> The September 2007 update to SQL Server 2005 Books Online (English version)
> is now available for download athttp://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00d...
> It is also offered as an Optional update via Microsoft Update.
> See the topic "New and Updated Books Online"
> (ms-help://sql90/sqlwhtn9/html/0f2f37d8-fc81-40c5-9976-cab8bd0b44e9.htm) for
> links to the new and revised topics.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online fromhttp://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
Thanks for the updation
ANN: Updated version of SQL Server 2005 Books Online is available for download
The September 2007 update to SQL Server 2005 Books Online (English version)
is now available for download at
http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en.
It is also offered as an Optional update via Microsoft Update.
See the topic "New and Updated Books Online"
(ms-help://sql90/sqlwhtn9/html/0f2f37d8-fc81-40c5-9976-cab8bd0b44e9.htm) for
links to the new and revised topics.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspxOn Dec 1, 5:53 am, "Gail Erickson [MS]" <ga...@.online.microsoft.com>
wrote:
> The September 2007 update to SQL Server 2005 Books Online (English version)
> is now available for download athttp://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00d...
> It is also offered as an Optional update via Microsoft Update.
> See the topic "New and Updated Books Online"
> (ms-help://sql90/sqlwhtn9/html/0f2f37d8-fc81-40c5-9976-cab8bd0b44e9.htm) for
> links to the new and revised topics.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online fromhttp://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
Thanks for the updation
is now available for download at
http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en.
It is also offered as an Optional update via Microsoft Update.
See the topic "New and Updated Books Online"
(ms-help://sql90/sqlwhtn9/html/0f2f37d8-fc81-40c5-9976-cab8bd0b44e9.htm) for
links to the new and revised topics.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspxOn Dec 1, 5:53 am, "Gail Erickson [MS]" <ga...@.online.microsoft.com>
wrote:
> The September 2007 update to SQL Server 2005 Books Online (English version)
> is now available for download athttp://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00d...
> It is also offered as an Optional update via Microsoft Update.
> See the topic "New and Updated Books Online"
> (ms-help://sql90/sqlwhtn9/html/0f2f37d8-fc81-40c5-9976-cab8bd0b44e9.htm) for
> links to the new and revised topics.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online fromhttp://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
Thanks for the updation
ANN: SQLMatcher 2.1 Beta Special Offer
The first 5 beta testers to register by sending an e-mail to beta AT
berryware.com will receive the final version of SQLMatcher 2.1
Professional for free when it ships next month.
Note: Sending the e-mail is only required for those who wish to receive
free copies of SQLMatcher 2.1. To obtain the Beta software in the
meantime you can use the following URL:
http://www.berryware.com/sqlmatcher/beta/
SQLMatcher is an advanced schema comparison and synchronization tool for
Microsoft SQL Server. It is an essential tool that should be a part
of every SQL Developer and DBA's toolkits.
Have fun evaluating this BETA version. We'd appreciate your feedback at
beta AT berryware.com (or support AT berryware.com).
Thanks,
Kevin Berry
Berryware
Kevin Berry [Berryware] wrote:
> To obtain the Beta software in the
> meantime you can use the following URL:
> http://www.berryware.com/sqlmatcher/beta/
We've had reports of problems with the .MSI downloads. In the meantime
please download the SQLMatcher 2.1 Beta 4 .EXE instead. The .MSI
download has been disabled until this issue has been addressed. We
apologize for any inconvenience.
Regards,
Kevin Berry
Berryware
berryware.com will receive the final version of SQLMatcher 2.1
Professional for free when it ships next month.
Note: Sending the e-mail is only required for those who wish to receive
free copies of SQLMatcher 2.1. To obtain the Beta software in the
meantime you can use the following URL:
http://www.berryware.com/sqlmatcher/beta/
SQLMatcher is an advanced schema comparison and synchronization tool for
Microsoft SQL Server. It is an essential tool that should be a part
of every SQL Developer and DBA's toolkits.
Have fun evaluating this BETA version. We'd appreciate your feedback at
beta AT berryware.com (or support AT berryware.com).
Thanks,
Kevin Berry
Berryware
Kevin Berry [Berryware] wrote:
> To obtain the Beta software in the
> meantime you can use the following URL:
> http://www.berryware.com/sqlmatcher/beta/
We've had reports of problems with the .MSI downloads. In the meantime
please download the SQLMatcher 2.1 Beta 4 .EXE instead. The .MSI
download has been disabled until this issue has been addressed. We
apologize for any inconvenience.
Regards,
Kevin Berry
Berryware
ANN: SQL Monitor Version 2 has just been released
SQL Monitor version 2.0 has just been released.
Ops. lol.
http://dbmonitor.tripod.com
SQL Monitor is an application that displays performance statistics for
sessions running on SQL Server databases. It has a very small
footprint on the server yet displays all the information required for
a DBA or database developer to see the impact individual sessions have
on a database server.
Features include:
Email alerts for performance issues or unexpected disconnects on
database servers
Logging of performance figures and individual session connects/
disconnects
Multi-server graph display showing all connected servers on the one
screen
Process information broken down into database/ user or session lists
Locking panel to display locking information
Simple one click server login
Adjustable server refresh from 1 second to two minutes
No time limit on trial (Restricted to max of 2 servers concurrently
connected)
Ability to sort the session lists based on activity between sessions
NOTE: This is a total rewrite of the previous version of DBMonitor.
All the excellent features of the previous application have been
incorporated and expanded into the new application.
The link is down.
Other links?|||On May 29, 7:13 pm, Trevize <jbg...@.gmail.comwrote:
Quote:
Originally Posted by
The link is down.
Other links?
Ops. lol.
The download link is now working.
ANN: Multi DBMS tool "Database Workbench" version 2.8.7 released
Ladies, gentlemen,
Upscene Productions is proud to announce the next
version of the popular database development tool:
Database Workbench 2.8.7 has been released today!
Download a trial at: http://www.upscene.com
What's new?: http://www.upscene.com/products/dbw/whatsnew.htm
Full list of features and fixes: http://www.upscene.com/news/20060614.htm
Database Workbench supports:
- Borland InterBase ( 4.x - 7.x )
- Firebird ( 1.x, 2.0 )
- MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 & 2, SQL Express )
- MySQL 4, 4.1, 5.0
- Oracle Database ( 8i, 9i, 10g )
- NexusDB ( 2.05 )
If you experience any problems with this new version, don't
hestitate and either go to the website and send a support email
or email directly to support@.upscene.com
New/improved
--
- NexusDB 2 support
- Microsoft SQL 2005, Service Pack 1 support
- MySQL 5 support
- Two-way Visual Query Builder
- Increased Oracle support
- New SQL Insight
- Create INSERT script from ODBC datasource
- TIFF support in BLOB Editor
Enhancements and bugfixes related to Import/Export, DataPump,
Code Editors, SQL Insight, NexusDB module performance and much
more.
Thank you for your support,
Martijn Tonies
Database Workbench - the database developer tool for professionals
Upscene Productions
http://www.upscene.comModerator please delete this spam.
"Martijn Tonies" wrote:
> Ladies, gentlemen,
> Upscene Productions is proud to announce the next
> version of the popular database development tool:
> Database Workbench 2.8.7 has been released today!
>
> Download a trial at: http://www.upscene.com
> What's new?: http://www.upscene.com/products/dbw/whatsnew.htm
> Full list of features and fixes: http://www.upscene.com/news/20060614.htm
>
> Database Workbench supports:
> - Borland InterBase ( 4.x - 7.x )
> - Firebird ( 1.x, 2.0 )
> - MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 & 2, SQL Express )
> - MySQL 4, 4.1, 5.0
> - Oracle Database ( 8i, 9i, 10g )
> - NexusDB ( 2.05 )
> If you experience any problems with this new version, don't
> hestitate and either go to the website and send a support email
> or email directly to support@.upscene.com
> New/improved
> --
> - NexusDB 2 support
> - Microsoft SQL 2005, Service Pack 1 support
> - MySQL 5 support
> - Two-way Visual Query Builder
> - Increased Oracle support
> - New SQL Insight
> - Create INSERT script from ODBC datasource
> - TIFF support in BLOB Editor
>
> Enhancements and bugfixes related to Import/Export, DataPump,
> Code Editors, SQL Insight, NexusDB module performance and much
> more.
>
> Thank you for your support,
> Martijn Tonies
> Database Workbench - the database developer tool for professionals
> Upscene Productions
> http://www.upscene.com
>
>
Upscene Productions is proud to announce the next
version of the popular database development tool:
Database Workbench 2.8.7 has been released today!
Download a trial at: http://www.upscene.com
What's new?: http://www.upscene.com/products/dbw/whatsnew.htm
Full list of features and fixes: http://www.upscene.com/news/20060614.htm
Database Workbench supports:
- Borland InterBase ( 4.x - 7.x )
- Firebird ( 1.x, 2.0 )
- MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 & 2, SQL Express )
- MySQL 4, 4.1, 5.0
- Oracle Database ( 8i, 9i, 10g )
- NexusDB ( 2.05 )
If you experience any problems with this new version, don't
hestitate and either go to the website and send a support email
or email directly to support@.upscene.com
New/improved
--
- NexusDB 2 support
- Microsoft SQL 2005, Service Pack 1 support
- MySQL 5 support
- Two-way Visual Query Builder
- Increased Oracle support
- New SQL Insight
- Create INSERT script from ODBC datasource
- TIFF support in BLOB Editor
Enhancements and bugfixes related to Import/Export, DataPump,
Code Editors, SQL Insight, NexusDB module performance and much
more.
Thank you for your support,
Martijn Tonies
Database Workbench - the database developer tool for professionals
Upscene Productions
http://www.upscene.comModerator please delete this spam.
"Martijn Tonies" wrote:
> Ladies, gentlemen,
> Upscene Productions is proud to announce the next
> version of the popular database development tool:
> Database Workbench 2.8.7 has been released today!
>
> Download a trial at: http://www.upscene.com
> What's new?: http://www.upscene.com/products/dbw/whatsnew.htm
> Full list of features and fixes: http://www.upscene.com/news/20060614.htm
>
> Database Workbench supports:
> - Borland InterBase ( 4.x - 7.x )
> - Firebird ( 1.x, 2.0 )
> - MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 & 2, SQL Express )
> - MySQL 4, 4.1, 5.0
> - Oracle Database ( 8i, 9i, 10g )
> - NexusDB ( 2.05 )
> If you experience any problems with this new version, don't
> hestitate and either go to the website and send a support email
> or email directly to support@.upscene.com
> New/improved
> --
> - NexusDB 2 support
> - Microsoft SQL 2005, Service Pack 1 support
> - MySQL 5 support
> - Two-way Visual Query Builder
> - Increased Oracle support
> - New SQL Insight
> - Create INSERT script from ODBC datasource
> - TIFF support in BLOB Editor
>
> Enhancements and bugfixes related to Import/Export, DataPump,
> Code Editors, SQL Insight, NexusDB module performance and much
> more.
>
> Thank you for your support,
> Martijn Tonies
> Database Workbench - the database developer tool for professionals
> Upscene Productions
> http://www.upscene.com
>
>
ANN: Database Workbench 2.8.5 released
Ladies, gentlemen,
Upscene Productions is proud to announce the next
version of the popular database development tool:
Database Workbench 2.8.5 has been released today!
Download a trial at: http://www.upscene.com
What's new?: http://www.upscene.com/products/dbw/whatsnew.htm
Full list of features and fixes: http://www.upscene.com/news/20060327.htm
Database Workbench supports:
- Borland InterBase ( 4.x - 7.x )
- Firebird ( 1.x, 2.0 )
- MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 & 2, SQL Express )
- MySQL 4, 4.1, 5.0
- Oracle Database ( 8i, 9i, 10g )
- NexusDB ( 2.05 )
If you experience any problems with this new version, don't
hestitate and either go to the website and send a support email
or email directly to support@.upscene.com
New
--
- NexusDB 2 support
- Microsoft SQL 2005 support
- MySQL 5 support
- Two-way Visual Query Builder
- Increased Oracle support
- New SQL Insight
- Create INSERT script from ODBC datasource
- TIFF support in BLOB Editor
Enhancements
--
- Code/SQL Editor enhancements
- More complte Schema Compare/Migration
- Automatic image-type recognition in BLOB Editor
- many user interface improvements
- MySQL "explain" support in SQL Editor
Thank you for your support,
Martijn Tonies
Database Workbench - the database developer tool for professionals
Upscene Productions
http://www.upscene.comModerator please delete this spam.
"Martijn Tonies" wrote:
> Ladies, gentlemen,
> Upscene Productions is proud to announce the next
> version of the popular database development tool:
> Database Workbench 2.8.5 has been released today!
>
> Download a trial at: http://www.upscene.com
> What's new?: http://www.upscene.com/products/dbw/whatsnew.htm
> Full list of features and fixes: http://www.upscene.com/news/20060327.htm
>
> Database Workbench supports:
> - Borland InterBase ( 4.x - 7.x )
> - Firebird ( 1.x, 2.0 )
> - MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 & 2, SQL Express )
> - MySQL 4, 4.1, 5.0
> - Oracle Database ( 8i, 9i, 10g )
> - NexusDB ( 2.05 )
> If you experience any problems with this new version, don't
> hestitate and either go to the website and send a support email
> or email directly to support@.upscene.com
> New
> --
> - NexusDB 2 support
> - Microsoft SQL 2005 support
> - MySQL 5 support
> - Two-way Visual Query Builder
> - Increased Oracle support
> - New SQL Insight
> - Create INSERT script from ODBC datasource
> - TIFF support in BLOB Editor
>
> Enhancements
> --
> - Code/SQL Editor enhancements
> - More complte Schema Compare/Migration
> - Automatic image-type recognition in BLOB Editor
> - many user interface improvements
> - MySQL "explain" support in SQL Editor
>
> Thank you for your support,
> Martijn Tonies
> Database Workbench - the database developer tool for professionals
> Upscene Productions
> http://www.upscene.com
>
>|||Users,
> Moderator please delete this spam.
Many third party developers post here and those postings, or ours, never
received
any negative comments.
Has the stance on this changed?
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
> "Martijn Tonies" wrote:
> > Ladies, gentlemen,
> >
> > Upscene Productions is proud to announce the next
> > version of the popular database development tool:
> >
> > Database Workbench 2.8.5 has been released today!
> >
> >
> >
> > Download a trial at: http://www.upscene.com
> > What's new?: http://www.upscene.com/products/dbw/whatsnew.htm
> > Full list of features and fixes:
http://www.upscene.com/news/20060327.htm
> >
> >
> >
> > Database Workbench supports:
> > - Borland InterBase ( 4.x - 7.x )
> > - Firebird ( 1.x, 2.0 )
> > - MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 & 2, SQL Express )
> > - MySQL 4, 4.1, 5.0
> > - Oracle Database ( 8i, 9i, 10g )
> > - NexusDB ( 2.05 )
> >
> > If you experience any problems with this new version, don't
> > hestitate and either go to the website and send a support email
> > or email directly to support@.upscene.com
> >
> > New
> > --
> > - NexusDB 2 support
> > - Microsoft SQL 2005 support
> > - MySQL 5 support
> > - Two-way Visual Query Builder
> > - Increased Oracle support
> > - New SQL Insight
> > - Create INSERT script from ODBC datasource
> > - TIFF support in BLOB Editor
> >
> >
> > Enhancements
> > --
> > - Code/SQL Editor enhancements
> > - More complte Schema Compare/Migration
> > - Automatic image-type recognition in BLOB Editor
> > - many user interface improvements
> > - MySQL "explain" support in SQL Editor
> >
> >
> > Thank you for your support,
> >
> > Martijn Tonies
> > Database Workbench - the database developer tool for professionals
> > Upscene Productions
> > http://www.upscene.com
> >
> >
> >|||I think not. Remember Forbes:
'We spare our readers unimportant advertisers and we spare our
advertisers unimportant "readers" ' :)
Check out RAC @.:
www.rac4sql.net
"Martijn Tonies" <m.tonies@.upscene.removethis.com> wrote in message
news:%23wIwCMRkGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Users,
>> Moderator please delete this spam.
> Many third party developers post here and those postings, or ours, never
> received
> any negative comments.
> Has the stance on this changed?
Upscene Productions is proud to announce the next
version of the popular database development tool:
Database Workbench 2.8.5 has been released today!
Download a trial at: http://www.upscene.com
What's new?: http://www.upscene.com/products/dbw/whatsnew.htm
Full list of features and fixes: http://www.upscene.com/news/20060327.htm
Database Workbench supports:
- Borland InterBase ( 4.x - 7.x )
- Firebird ( 1.x, 2.0 )
- MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 & 2, SQL Express )
- MySQL 4, 4.1, 5.0
- Oracle Database ( 8i, 9i, 10g )
- NexusDB ( 2.05 )
If you experience any problems with this new version, don't
hestitate and either go to the website and send a support email
or email directly to support@.upscene.com
New
--
- NexusDB 2 support
- Microsoft SQL 2005 support
- MySQL 5 support
- Two-way Visual Query Builder
- Increased Oracle support
- New SQL Insight
- Create INSERT script from ODBC datasource
- TIFF support in BLOB Editor
Enhancements
--
- Code/SQL Editor enhancements
- More complte Schema Compare/Migration
- Automatic image-type recognition in BLOB Editor
- many user interface improvements
- MySQL "explain" support in SQL Editor
Thank you for your support,
Martijn Tonies
Database Workbench - the database developer tool for professionals
Upscene Productions
http://www.upscene.comModerator please delete this spam.
"Martijn Tonies" wrote:
> Ladies, gentlemen,
> Upscene Productions is proud to announce the next
> version of the popular database development tool:
> Database Workbench 2.8.5 has been released today!
>
> Download a trial at: http://www.upscene.com
> What's new?: http://www.upscene.com/products/dbw/whatsnew.htm
> Full list of features and fixes: http://www.upscene.com/news/20060327.htm
>
> Database Workbench supports:
> - Borland InterBase ( 4.x - 7.x )
> - Firebird ( 1.x, 2.0 )
> - MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 & 2, SQL Express )
> - MySQL 4, 4.1, 5.0
> - Oracle Database ( 8i, 9i, 10g )
> - NexusDB ( 2.05 )
> If you experience any problems with this new version, don't
> hestitate and either go to the website and send a support email
> or email directly to support@.upscene.com
> New
> --
> - NexusDB 2 support
> - Microsoft SQL 2005 support
> - MySQL 5 support
> - Two-way Visual Query Builder
> - Increased Oracle support
> - New SQL Insight
> - Create INSERT script from ODBC datasource
> - TIFF support in BLOB Editor
>
> Enhancements
> --
> - Code/SQL Editor enhancements
> - More complte Schema Compare/Migration
> - Automatic image-type recognition in BLOB Editor
> - many user interface improvements
> - MySQL "explain" support in SQL Editor
>
> Thank you for your support,
> Martijn Tonies
> Database Workbench - the database developer tool for professionals
> Upscene Productions
> http://www.upscene.com
>
>|||Users,
> Moderator please delete this spam.
Many third party developers post here and those postings, or ours, never
received
any negative comments.
Has the stance on this changed?
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
> "Martijn Tonies" wrote:
> > Ladies, gentlemen,
> >
> > Upscene Productions is proud to announce the next
> > version of the popular database development tool:
> >
> > Database Workbench 2.8.5 has been released today!
> >
> >
> >
> > Download a trial at: http://www.upscene.com
> > What's new?: http://www.upscene.com/products/dbw/whatsnew.htm
> > Full list of features and fixes:
http://www.upscene.com/news/20060327.htm
> >
> >
> >
> > Database Workbench supports:
> > - Borland InterBase ( 4.x - 7.x )
> > - Firebird ( 1.x, 2.0 )
> > - MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 & 2, SQL Express )
> > - MySQL 4, 4.1, 5.0
> > - Oracle Database ( 8i, 9i, 10g )
> > - NexusDB ( 2.05 )
> >
> > If you experience any problems with this new version, don't
> > hestitate and either go to the website and send a support email
> > or email directly to support@.upscene.com
> >
> > New
> > --
> > - NexusDB 2 support
> > - Microsoft SQL 2005 support
> > - MySQL 5 support
> > - Two-way Visual Query Builder
> > - Increased Oracle support
> > - New SQL Insight
> > - Create INSERT script from ODBC datasource
> > - TIFF support in BLOB Editor
> >
> >
> > Enhancements
> > --
> > - Code/SQL Editor enhancements
> > - More complte Schema Compare/Migration
> > - Automatic image-type recognition in BLOB Editor
> > - many user interface improvements
> > - MySQL "explain" support in SQL Editor
> >
> >
> > Thank you for your support,
> >
> > Martijn Tonies
> > Database Workbench - the database developer tool for professionals
> > Upscene Productions
> > http://www.upscene.com
> >
> >
> >|||I think not. Remember Forbes:
'We spare our readers unimportant advertisers and we spare our
advertisers unimportant "readers" ' :)
Check out RAC @.:
www.rac4sql.net
"Martijn Tonies" <m.tonies@.upscene.removethis.com> wrote in message
news:%23wIwCMRkGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Users,
>> Moderator please delete this spam.
> Many third party developers post here and those postings, or ours, never
> received
> any negative comments.
> Has the stance on this changed?
Monday, February 13, 2012
Analysis Services Lock-ups
I have a general comment and then a question related to Analysis Services 2005. It seems to me that AS2K (the previous version) scaled out better - because a significant amount of CPU-intensive calculation was done on the client machine. This was especially nice when user queries were:
Poorly formed / unreasonable / just-plain-stupid Very intensive but we-must-have-the-dataIn the case of these types of user queries, the client machine's CPU would become swamped but the Analysis Services (AS2K) server could remain responsive in serving data to other users.
In Analysis Services 2005, however, a couple of bad user queries can quickly lock up the OLAP cubes tighter than Alcatraz. (Even when SSAS is running on a high performance 64 bit server.) It is especially bad when some partition update processing is trying to commit at the same time.
I was hoping the performance guide would discuss some ideas on how to scale out Analysis Services 2005 to a large number of users (ie. 10s or 100s). Lets face it, if you get a lot of users hitting these OLAP cubes then you can bet a good percentage of them won't know the difference between a reasonable and an unreasonable query.
Any thoughts or references to documention about this? Are there ways of limiting the negative impact that novice users have on the queries that other people are trying to run?
Thanks in advance, David
You are right, AS 2005 has centralized all of the calculations and as a result may not scale out as well as AS2K.
AS 2005 is pretty much version 1 of a new architecture and there is not too much that can be done about this.
There is a Timeout option which you can add to the connection string to force queries to timeout after a given amount of time, but this could be hard to administer.
The only other option I can think of is that you can synchronize a database across more that one server and set them up in an NLB cluster, but this would be an expensive option if you were only adding the second server in order to be able to cater for runaway queries. Even then I am not sure if the NLB software is smart enough to not still have some clients being "locked out" by a runaway query.
|||Thanks for the reply. What is an NLB cluster?
We have a growing set of mission-critical, canned SSRS reports that read data from our new SSAS cubes. These reports are developed within the I.S. department and have well-defined (pre-defined) data requirements. In the very least I'd like these reports to remain available and responsive. Does anybody have ideas on preventing general OLAP users from being able to negatively imact our canned reports?
Could I back up the entire SSAS database after nightly processing and re-deploy it to a different SSAS server which only canned reports have access to? Does anybody have experience scaling SSAS in this way?
Thanks, David
|||NLB - Network Load Balanced
You can set up SSAS in a configuration similar to a web farm where you can have mulitple SSAS servers all with an identical copy of the same database. I think the default is that requests are just handled in a round robin fashion, with each node taking turns at servicing an incoming session. There may be 3rd party solutions that are more sophisticated and allocate connections based on the load that each of the servers is under, but I'm not familiar with anything like this myself.
You could do a back up and re-deploy. SSAS actually has a built-in feature called synchronization which does this, see details here http://msdn2.microsoft.com/en-us/library/ms174928.aspx.
|||Thanks for the help. Do you know how licensing would work in this kind of environment with sychronized SSAS databases?
I'm thinking that the extra SSAS services should be free. Users are telling me that these new SSAS cubes max out at 2 or 3 concurrent users. Depending on the types of data they are pulling, they are constantly locking each other up.
(No, don't blame my cube design. I was already following those best practices in Oct '05. 
No, I'm by no means an expert on licensing, but in this case the extra instances of SSAS would require full SQL Server licenses. That said you should be getting way more than 2-3 concurrent users. I though we were talking about scaling out to 100's of users
(No, don't blame my cube design. I was already following those best practices in Oct '05.
)
Are you saying that your design matches the best practices that were being promoted in Oct '05 or the ones that are being promoted now? Back in '05 Microsoft was saying to put everything into one big cube and let SSAS sort it out, now they are saying that we are better off breaking off logically related groups of measure groups into separate cubes.
Unfortunately sometimes the perfect logical models do not perform well and you may need to compromise on some aspects of the design in order to achieve your performance requirements.
Do you have any idea where the bottlenecks are? Is the server disk, cpu or memory bound? Do you know if it is specific calculations that are slow (some of the calcs added by the time intelligence wizard are not optimal - searching on this forum should turn up some helpful hints if this is a potential issue)? Have you tried applying usage based aggregtions? Do you have many parent child dimensions?|||I was following the current best practices back then. (Aren't I modest. 
We want to be able to scale out but SSAS is very bound up on both CPU and data processing concurrency problems. Normally 2 or 3 concurrent users is all we can get (ie. users running queries at exactly the same time) .
In general our CPU problems occur in queries when dicing calculated members on large dimensions and also when using time intelligence. SP2 helped a little bit with time intelligence - but query execution still reverts to cell-by-cell evaluation when the MDX query optimization engine gets a little confused. I expect these types of execution plans are a fall-back and are unavoidable at times. The main problem that I'd like to solve is this architecture which forces everyone else to suffer along with the user who is running the demanding query.
|||Interesting thread.
We're experiencing the exact same issues over here - it's nice to know we're not alone in the behavior we're seeing.
We process data every hour (both fact and dimensional) as it's a mandatory business requirement (no if's, and's, or but's). Two of these dimensions are in excess of two million members so they take a couple minutes to process. To make matters worse, our aggregations are mainly flexible rather than rigid - data floats around a lot. Our total size is appx 500GB.
At any given time, within a minute we have about 10-15 users querying the cube. We've found that a single user can throw everything out of wack, especially when processing is going on.
We looked at synchronization but were not impressed by the performance. Since our aggs are mainly flexible a big chunk of files have to be moved.
I'm curious as to how people are scaling out to handle complex queries and rapidly changing data.
|||Have you had a look at the ForceCommitLock setting. I did a post about this recently here http://geekswithblogs.net/darrengosbell/archive/2007/04/24/SSAS-Processing-ForceCommitTimeout-and-quotthe-operation-has-been-cancelledquot.aspx
With the default setting though you should not see any more than 30 seconds added to the front of the query where it is waiting for the pending commit lock to be released before it even starts. But maybe if you get enough queries queued up that it is thrashing a bit after the lock is released. It would be interesting to run some profiler traces and see if there is a definite correlation here.
|||We are having some success with distributed cubes. It ultimately doesn't help with the final version of this problem - tons of people with bad queries will still bring the system down - but I think is better than round-robin nlb servers because 1) cube processing is cut down significantly, and 2) for few users they see a benefit to the scaling vs no beneift with nlb.
For #2, what I mean is that if you have a 60-second MDX query, and have 6 servers it will take maybe 10 seconds (assuming perfect spread, we are seeing 'almost perfect' spread in practice, so its close enough for now) for a single user - and if you had 6 servers with identical copies of the cubes when there aren't tens or hundres of users that single query still takes 60 seconds. Of course this is also mitigated by how you spread the data - if it is by week or month and most queries are last 5 days only, then the other method would be better as in a distributed environment these would all go to a single server.
Not a final answer, just one more piece of the puzzle.
Oh - and we had so many problems with data transmission across bad corporate networks and slow laptops with low memory that even in MSAS 2000 we had to basically disable client side processing anyhow - it wasn't practical at *any* of the sites we've developed for! We used provider string parameters to force processing to the server.
|||Hi,
I'm a BI consultant. In recent days I did a migration from SQL Server Analysis Services 2000 to 2005 and did completely redesign the cubes by following the guidelines promoted in Oct'05. I was not really amused reading the exact opposite in the olap best practices in March 2007. This sounds really like a kind of joke.
I am very angry about the situation and NOT satisfied by the product SQL Server 2005 at all. And none of the "cool guys" from MS really seems to have an opinion about the paradigm change and about all the still existing bugs, especially in SSAS. Maybe they discovered those bugs in their software and were to lazy or simply not able to solve them. Then they decided to recommend a completely other architecture. Maybe the bugs would not occur any more and all the problems are solved?
I'm not sure if they have only Micky Mouse cubes there in Redmond containing 2 rows of data to test their software, but maybe this is the case. But this is a very very bad style of software development and delivering.
This is just my opinion after about 1 year experience with SQL Server 2005. (having about 6 years BI experience with SQL Server 2000)
Stefoon
Analysis Services Lock-ups
I have a general comment and then a question related to Analysis Services 2005. It seems to me that AS2K (the previous version) scaled out better - because a significant amount of CPU-intensive calculation was done on the client machine. This was especially nice when user queries were:
Poorly formed / unreasonable / just-plain-stupid Very intensive but we-must-have-the-dataIn the case of these types of user queries, the client machine's CPU would become swamped but the Analysis Services (AS2K) server could remain responsive in serving data to other users.
In Analysis Services 2005, however, a couple of bad user queries can quickly lock up the OLAP cubes tighter than Alcatraz. (Even when SSAS is running on a high performance 64 bit server.) It is especially bad when some partition update processing is trying to commit at the same time.
I was hoping the performance guide would discuss some ideas on how to scale out Analysis Services 2005 to a large number of users (ie. 10s or 100s). Lets face it, if you get a lot of users hitting these OLAP cubes then you can bet a good percentage of them won't know the difference between a reasonable and an unreasonable query.
Any thoughts or references to documention about this? Are there ways of limiting the negative impact that novice users have on the queries that other people are trying to run?
Thanks in advance, David
You are right, AS 2005 has centralized all of the calculations and as a result may not scale out as well as AS2K.
AS 2005 is pretty much version 1 of a new architecture and there is not too much that can be done about this.
There is a Timeout option which you can add to the connection string to force queries to timeout after a given amount of time, but this could be hard to administer.
The only other option I can think of is that you can synchronize a database across more that one server and set them up in an NLB cluster, but this would be an expensive option if you were only adding the second server in order to be able to cater for runaway queries. Even then I am not sure if the NLB software is smart enough to not still have some clients being "locked out" by a runaway query.
|||Thanks for the reply. What is an NLB cluster?
We have a growing set of mission-critical, canned SSRS reports that read data from our new SSAS cubes. These reports are developed within the I.S. department and have well-defined (pre-defined) data requirements. In the very least I'd like these reports to remain available and responsive. Does anybody have ideas on preventing general OLAP users from being able to negatively imact our canned reports?
Could I back up the entire SSAS database after nightly processing and re-deploy it to a different SSAS server which only canned reports have access to? Does anybody have experience scaling SSAS in this way?
Thanks, David
|||NLB - Network Load Balanced
You can set up SSAS in a configuration similar to a web farm where you can have mulitple SSAS servers all with an identical copy of the same database. I think the default is that requests are just handled in a round robin fashion, with each node taking turns at servicing an incoming session. There may be 3rd party solutions that are more sophisticated and allocate connections based on the load that each of the servers is under, but I'm not familiar with anything like this myself.
You could do a back up and re-deploy. SSAS actually has a built-in feature called synchronization which does this, see details here http://msdn2.microsoft.com/en-us/library/ms174928.aspx.
|||Thanks for the help. Do you know how licensing would work in this kind of environment with sychronized SSAS databases?
I'm thinking that the extra SSAS services should be free. Users are telling me that these new SSAS cubes max out at 2 or 3 concurrent users. Depending on the types of data they are pulling, they are constantly locking each other up.
(No, don't blame my cube design. I was already following those best practices in Oct '05.
No, I'm by no means an expert on licensing, but in this case the extra instances of SSAS would require full SQL Server licenses. That said you should be getting way more than 2-3 concurrent users. I though we were talking about scaling out to 100's of users
(No, don't blame my cube design. I was already following those best practices in Oct '05.
Are you saying that your design matches the best practices that were being promoted in Oct '05 or the ones that are being promoted now? Back in '05 Microsoft was saying to put everything into one big cube and let SSAS sort it out, now they are saying that we are better off breaking off logically related groups of measure groups into separate cubes.
Unfortunately sometimes the perfect logical models do not perform well and you may need to compromise on some aspects of the design in order to achieve your performance requirements.
Do you have any idea where the bottlenecks are? Is the server disk, cpu or memory bound? Do you know if it is specific calculations that are slow (some of the calcs added by the time intelligence wizard are not optimal - searching on this forum should turn up some helpful hints if this is a potential issue)? Have you tried applying usage based aggregtions? Do you have many parent child dimensions?|||I was following the current best practices back then. (Aren't I modest.
We want to be able to scale out but SSAS is very bound up on both CPU and data processing concurrency problems. Normally 2 or 3 concurrent users is all we can get (ie. users running queries at exactly the same time) .
In general our CPU problems occur in queries when dicing calculated members on large dimensions and also when using time intelligence. SP2 helped a little bit with time intelligence - but query execution still reverts to cell-by-cell evaluation when the MDX query optimization engine gets a little confused. I expect these types of execution plans are a fall-back and are unavoidable at times. The main problem that I'd like to solve is this architecture which forces everyone else to suffer along with the user who is running the demanding query.
|||Interesting thread.
We're experiencing the exact same issues over here - it's nice to know we're not alone in the behavior we're seeing.
We process data every hour (both fact and dimensional) as it's a mandatory business requirement (no if's, and's, or but's). Two of these dimensions are in excess of two million members so they take a couple minutes to process. To make matters worse, our aggregations are mainly flexible rather than rigid - data floats around a lot. Our total size is appx 500GB.
At any given time, within a minute we have about 10-15 users querying the cube. We've found that a single user can throw everything out of wack, especially when processing is going on.
We looked at synchronization but were not impressed by the performance. Since our aggs are mainly flexible a big chunk of files have to be moved.
I'm curious as to how people are scaling out to handle complex queries and rapidly changing data.
|||Have you had a look at the ForceCommitLock setting. I did a post about this recently here http://geekswithblogs.net/darrengosbell/archive/2007/04/24/SSAS-Processing-ForceCommitTimeout-and-quotthe-operation-has-been-cancelledquot.aspx
With the default setting though you should not see any more than 30 seconds added to the front of the query where it is waiting for the pending commit lock to be released before it even starts. But maybe if you get enough queries queued up that it is thrashing a bit after the lock is released. It would be interesting to run some profiler traces and see if there is a definite correlation here.
|||We are having some success with distributed cubes. It ultimately doesn't help with the final version of this problem - tons of people with bad queries will still bring the system down - but I think is better than round-robin nlb servers because 1) cube processing is cut down significantly, and 2) for few users they see a benefit to the scaling vs no beneift with nlb.
For #2, what I mean is that if you have a 60-second MDX query, and have 6 servers it will take maybe 10 seconds (assuming perfect spread, we are seeing 'almost perfect' spread in practice, so its close enough for now) for a single user - and if you had 6 servers with identical copies of the cubes when there aren't tens or hundres of users that single query still takes 60 seconds. Of course this is also mitigated by how you spread the data - if it is by week or month and most queries are last 5 days only, then the other method would be better as in a distributed environment these would all go to a single server.
Not a final answer, just one more piece of the puzzle.
Oh - and we had so many problems with data transmission across bad corporate networks and slow laptops with low memory that even in MSAS 2000 we had to basically disable client side processing anyhow - it wasn't practical at *any* of the sites we've developed for! We used provider string parameters to force processing to the server.
|||Hi,
I'm a BI consultant. In recent days I did a migration from SQL Server Analysis Services 2000 to 2005 and did completely redesign the cubes by following the guidelines promoted in Oct'05. I was not really amused reading the exact opposite in the olap best practices in March 2007. This sounds really like a kind of joke.
I am very angry about the situation and NOT satisfied by the product SQL Server 2005 at all. And none of the "cool guys" from MS really seems to have an opinion about the paradigm change and about all the still existing bugs, especially in SSAS. Maybe they discovered those bugs in their software and were to lazy or simply not able to solve them. Then they decided to recommend a completely other architecture. Maybe the bugs would not occur any more and all the problems are solved?
I'm not sure if they have only Micky Mouse cubes there in Redmond containing 2 rows of data to test their software, but maybe this is the case. But this is a very very bad style of software development and delivering.
This is just my opinion after about 1 year experience with SQL Server 2005. (having about 6 years BI experience with SQL Server 2000)
Stefoon
Subscribe to:
Posts (Atom)